阿里P7也不過如此，被一個簡單的SQL查詢難住！
最近工作上遇到一個“神奇”的問題，或許對大家有幫助，因此形成本文。
圖片來自 Pexels
問題大概是，我有兩個表 TableA，TableB，其中 TableA 表大概百萬行級別(存量業務數據)，TableB 表幾行(新業務場景，數據還未膨脹起來)。
語義上 TableA.columnA=TableB.columnA，其中 columnA 上建立了索引，但查詢的時候確巨慢無比，基本上到 5-6 秒，明顯跟預期不符合。
下面我以一個具體的例子來說明，模擬其中的 SQL 查詢場景。
場景重現
user_info 表，為了場景盡量簡單，我只 mock 了其中的三列數據。user_score 表，其中 uid 和 user_info.uid 語義一致。
其中數據情況如下, 都是很常見的場景：
索引情況如下圖：
查詢業務場景：已知 user_score.id，需要關聯查詢對應 user_info 的信息，(大家先忽略這個具體業務場景是否合理哈)。
那么對應的 SQL 很自然的如下：
請忽略其中的數據，我剛開始 mock 了 100W，然后又重復導入了兩遍，因此數據有一些重復。
300W 數據，最后查詢出來也是 1.18 秒，按道理應該更快的，老規矩 explain 看看啥情況?
發現 user_info 表沒用上索引，全表掃描近 300W 數據?現象是這樣，為什么呢?
你不妨思考一下，如果你遇到這種場景，應該怎么去排查?
我當時也是“一頓操作猛如虎”，然并卵?嘗試了什么多種 SQL 寫法來完成這個操作。
比如更換 Join 表的順序(驅動表/被驅動表)，再比如用子查詢。最終，還是沒有結果。但直接單表查詢寫 SQL 確能用上索引。
問題解決
嘗試更換檢索條件，比如更換 uid 直接關聯查詢，索引仍然用不上，差點放棄了都。
在準備求助 DBA 前，我看了下表的建表語句：
完全有理由懷疑因為字符集不一致的問題導致索引失效的問題。
于是修改了小表(真實線上環境可別亂操作)的字符集與大表一致，再測試下：
- 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 了。
挖掘根因
其實深究原因，就是網上各種 MySQL 軍規/規約所提到的， “索引列不要參與計算”。
這次這個 case，如果知道 explain extended+show warnings 這個工具的話，(以前都不知道 explain 后面還能加 extended 參數)，可能就盡早“恍然大悟”了。(最新的 MySQL 8.0 版本貌似不需要另外加這個關鍵字)
看下效果：(啊，我還得把字符集改回去)
- 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)
索引列參與計算了，每次都要根據字符集去轉換，全表掃描，你說能快得起來么?
至于這個問題為什么會發生?綜合來看，就是因為歷史原因，老業務場景中的原表是假 utf8，新業務新表采用了真 utf8mb4。
①考慮新表的時候，忽略和原庫字符集的比較。其實，發現庫里面的不同表可能都有不同的字符集，不同人建的時候可能都依據個人喜好去選擇了不同的字符集。由此可見，開發規范有多重要。
②雖然知道索引列不能參與計算，但這個場景下都是相同的類型，varchar(64) 最終查詢過程中仍然發生了類型轉換。因此需要把字段字符集不一致等同于字段類型不一致。
③如果這個 case，利用 fail-fast 的理念的話，發現不一致，直接不讓 join 會不會更好?(就像 char v.s varchar 不能 join 一樣)
說明：本文測試場景基于 MySQL 5.6，另外，本文案例只是為了說明問題，其中的 SQL 并不規范(例如盡量別用 select * 之類的)，請勿模仿(模仿了我也不負責)。
最后留一個思考題供討論，歡迎留言說出你的看法。
你能解釋如下情況嗎?查詢結果表現為何不一致?注意一下 SQL 的執行順序，查詢優化器工作流程，以及其中的 Using join buffer(Block Nested Loop)。
可以多看看 MySQL 官方手冊深入了解背后的過程和原理：
- https://dev.mysql.com/doc/refman/5.6/en/
作者：唐磊
簡介：清華學渣，目前就職阿里云，曾就職于大疆，宜信，Tencent，友盟。
編輯：陶家龍
出處：轉載自公眾號程序猿石頭(ID：tangleithu)
