最近工作上遇到一個「神奇」的問題，或許對大家有幫助，因此形成本文。
問題大概是，我有兩個表 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)