阿里P7也不過(guò)如此，被一個(gè)簡(jiǎn)單的SQL查詢難住！
最近工作上遇到一個(gè)“神奇”的問(wèn)題，或許對(duì)大家有幫助，因此形成本文。
圖片來(lái)自 Pexels
問(wèn)題大概是，我有兩個(gè)表 TableA，TableB，其中 TableA 表大概百萬(wàn)行級(jí)別(存量業(yè)務(wù)數(shù)據(jù))，TableB 表幾行(新業(yè)務(wù)場(chǎng)景，數(shù)據(jù)還未膨脹起來(lái))。
語(yǔ)義上 TableA.columnA=TableB.columnA，其中 columnA 上建立了索引，但查詢的時(shí)候確巨慢無(wú)比，基本上到 5-6 秒，明顯跟預(yù)期不符合。
下面我以一個(gè)具體的例子來(lái)說(shuō)明，模擬其中的 SQL 查詢場(chǎng)景。
場(chǎng)景重現(xiàn)
user_info 表，為了場(chǎng)景盡量簡(jiǎn)單，我只 mock 了其中的三列數(shù)據(jù)。user_score 表，其中 uid 和 user_info.uid 語(yǔ)義一致。
其中數(shù)據(jù)情況如下, 都是很常見的場(chǎng)景：
索引情況如下圖：
查詢業(yè)務(wù)場(chǎng)景：已知 user_score.id，需要關(guān)聯(lián)查詢對(duì)應(yīng) user_info 的信息，(大家先忽略這個(gè)具體業(yè)務(wù)場(chǎng)景是否合理哈)。
那么對(duì)應(yīng)的 SQL 很自然的如下：
請(qǐng)忽略其中的數(shù)據(jù)，我剛開始 mock 了 100W，然后又重復(fù)導(dǎo)入了兩遍，因此數(shù)據(jù)有一些重復(fù)。
300W 數(shù)據(jù)，最后查詢出來(lái)也是 1.18 秒，按道理應(yīng)該更快的，老規(guī)矩 explain 看看啥情況?
發(fā)現(xiàn) user_info 表沒(méi)用上索引，全表掃描近 300W 數(shù)據(jù)?現(xiàn)象是這樣，為什么呢?
你不妨思考一下，如果你遇到這種場(chǎng)景，應(yīng)該怎么去排查?
我當(dāng)時(shí)也是“一頓操作猛如虎”，然并卵?嘗試了什么多種 SQL 寫法來(lái)完成這個(gè)操作。
比如更換 Join 表的順序(驅(qū)動(dòng)表/被驅(qū)動(dòng)表)，再比如用子查詢。最終，還是沒(méi)有結(jié)果。但直接單表查詢寫 SQL 確能用上索引。
問(wèn)題解決
嘗試更換檢索條件，比如更換 uid 直接關(guān)聯(lián)查詢，索引仍然用不上，差點(diǎn)放棄了都。
在準(zhǔn)備求助 DBA 前，我看了下表的建表語(yǔ)句：
完全有理由懷疑因?yàn)樽址灰恢碌膯?wèn)題導(dǎo)致索引失效的問(wèn)題。
于是修改了小表(真實(shí)線上環(huán)境可別亂操作)的字符集與大表一致，再測(cè)試下：
- mysql> select * from user_score us
- -> inner join user_info ui on us.uid = ui.uid
- -> where us.id = 5;
- +----+-----------+-------+---------+-----------+---------+
- | id | uid | score | id | uid | name |
- +----+-----------+-------+---------+-----------+---------+
- | 5 | 111111111 | 100 | 1 | 111111111 | tanglei |
- | 5 | 111111111 | 100 | 3685399 | 111111111 | tanglei |
- | 5 | 111111111 | 100 | 3685400 | 111111111 | tanglei |
- | 5 | 111111111 | 100 | 3685401 | 111111111 | tanglei |
- | 5 | 111111111 | 100 | 3685402 | 111111111 | tanglei |
- | 5 | 111111111 | 100 | 3685403 | 111111111 | tanglei |
- +----+-----------+-------+---------+-----------+---------+
- 6 rows in set (0.00 sec)
- mysql> explain
- -> select * from user_score us
- -> inner join user_info ui on us.uid = ui.uid
- -> where us.id = 5;
- +----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
- | 1 | SIMPLE | us | const | PRIMARY,index_uid | PRIMARY | 4 | const | 1 | NULL |
- | 1 | SIMPLE | ui | ref | index_uid | index_uid | 194 | const | 6 | NULL |
- +----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
- 2 rows in set (0.00 sec)
果然 Work 了。
挖掘根因
其實(shí)深究原因，就是網(wǎng)上各種 MySQL 軍規(guī)/規(guī)約所提到的， “索引列不要參與計(jì)算”。
這次這個(gè) case，如果知道 explain extended+show warnings 這個(gè)工具的話，(以前都不知道 explain 后面還能加 extended 參數(shù))，可能就盡早“恍然大悟”了。(最新的 MySQL 8.0 版本貌似不需要另外加這個(gè)關(guān)鍵字)
看下效果：(啊，我還得把字符集改回去)
- mysql> explain extended select * from user_score us inner join user_info ui on us.uid = ui.uid where us.id = 5;
- +----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
- | 1 | SIMPLE | us | const | PRIMARY,index_uid | PRIMARY | 4 | const | 1 | 100.00 | NULL |
- | 1 | SIMPLE | ui | ALL | NULL | NULL | NULL | NULL | 2989934 | 100.00 | Using where |
- +----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
- 2 rows in set, 1 warning (0.00 sec)
- mysql> show warnings;
- +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Level | Code | Message |
- +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Note | 1003 | /* select#1 */ select '5' AS `id`,'111111111' AS `uid`,'100' AS `score`,`test`.`ui`.`id` AS `id`,`test`.`ui`.`uid` AS `uid`,`test`.`ui`.`name` AS `name` from `test`.`user_score` `us` join `test`.`user_info` `ui` where (('111111111' = convert(`test`.`ui`.`uid` using utf8mb4))) |
- +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
索引列參與計(jì)算了，每次都要根據(jù)字符集去轉(zhuǎn)換，全表掃描，你說(shuō)能快得起來(lái)么?
至于這個(gè)問(wèn)題為什么會(huì)發(fā)生?綜合來(lái)看，就是因?yàn)闅v史原因，老業(yè)務(wù)場(chǎng)景中的原表是假 utf8，新業(yè)務(wù)新表采用了真 utf8mb4。
①考慮新表的時(shí)候，忽略和原庫(kù)字符集的比較。其實(shí)，發(fā)現(xiàn)庫(kù)里面的不同表可能都有不同的字符集，不同人建的時(shí)候可能都依據(jù)個(gè)人喜好去選擇了不同的字符集。由此可見，開發(fā)規(guī)范有多重要。
②雖然知道索引列不能參與計(jì)算，但這個(gè)場(chǎng)景下都是相同的類型，varchar(64) 最終查詢過(guò)程中仍然發(fā)生了類型轉(zhuǎn)換。因此需要把字段字符集不一致等同于字段類型不一致。
③如果這個(gè) case，利用 fail-fast 的理念的話，發(fā)現(xiàn)不一致，直接不讓 join 會(huì)不會(huì)更好?(就像 char v.s varchar 不能 join 一樣)
說(shuō)明：本文測(cè)試場(chǎng)景基于 MySQL 5.6，另外，本文案例只是為了說(shuō)明問(wèn)題，其中的 SQL 并不規(guī)范(例如盡量別用 select * 之類的)，請(qǐng)勿模仿(模仿了我也不負(fù)責(zé))。
最后留一個(gè)思考題供討論，歡迎留言說(shuō)出你的看法。
你能解釋如下情況嗎?查詢結(jié)果表現(xiàn)為何不一致?注意一下 SQL 的執(zhí)行順序，查詢優(yōu)化器工作流程，以及其中的 Using join buffer(Block Nested Loop)。
可以多看看 MySQL 官方手冊(cè)深入了解背后的過(guò)程和原理：
- https://dev.mysql.com/doc/refman/5.6/en/
作者：唐磊
簡(jiǎn)介：清華學(xué)渣，目前就職阿里云，曾就職于大疆，宜信，Tencent，友盟。
編輯：陶家龍
出處：轉(zhuǎn)載自公眾號(hào)程序猿石頭(ID：tangleithu)
