最近同事接手了一個老項目，在簡單的做了幾個小需求后，經過自測沒問題就發布上線了，沒想的是，上線沒一會監控平臺就報警有全表掃描的慢SQL。

因為上線的幾個功能使用頻率也不高，所以也只是告訴同事慢SQL的情況，讓該同事先檢查優化。

結果直到快下班，才收到同事提交的新版本。一問，才知道竟然是一個多表關聯查詢中的兩張表的編碼方式不一致，導致出現了隱式類型轉換，從而去掃描全表了。

而之所以該同事在測試環境使用了各種手段都沒有復現線上的場景，是因為測試環境的表編碼是一致的，果然老項目處處是坑啊。

今天借著這個問題，帶大家了解一下，為什么字符集編碼不一致(可能)會發生不走索引掃描全表的問題。（注意，是可能，并非一定）。

首先，我們新建兩張表復現一下現場。

-- 創建table1，并對key1設置二級索引
CREATE TABLE table1 (
  `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  `key1` VARCHAR ( 255 ) CHARACTER 
  SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY ( `id` ) USING BTREE,
  INDEX `idx_key1` ( `key1` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 創建table2，并對key2設置二級索引
CREATE TABLE table2 (
  `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  `key2` VARCHAR ( 255 ) CHARACTER 
  SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY ( `id` ) USING BTREE,
  INDEX `idx_key2` ( `key2` ( 191 ) ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

請注意table1的字符集編碼是utf8，而table2的字符集編碼是utf8mb4。

我們執行一條普通的左關聯sql：

SELECT
  * 
FROM
  table1 t1
  LEFT JOIN table2 t2 ON t1.key1 = t2.key2 
  AND t1.id = 1;

通過explain查看一下執行計劃：

可以看到，table1使用了索引idx_key1，但是table2卻沒有命中索引，反而執行了全表掃描。

那真的是因為字符集轉換導致的索引失效嗎？

口說無憑，我們看一下MySQL經過優化器優化的sql：

執行explain select ...之后，再執行show warnings即可看到優化后的sql。

可以清楚的看到，經過優化后的sql，其實是對table1的key1字段做了convert轉換，即從utf8轉換為utf8mb4。

那有的朋友可能要問了， 明明是對key1字段做的convert，怎么導致table2無法走索引了呢？

其實這是因為此處以table1為驅動表，table2為被驅動表，從table1中查出數據，然后去table2中匹配，但是table1查出來的數據要做類型轉換，對于table2來說，無論是索引的等值匹配，還是范圍匹配，都需要確定值才行。值不確定，干脆走全表掃描一條條的匹配。

換句話說，相當于執行了下面的sql：

SELECT
  * 
FROM
  table2 
WHERE
  CONVERT ( key2 USING utf8mb4 ) = 'abc';

看到這，大家是否回憶起我們經常說的sql優化：

不要在索引字段上函數操作。

這才是索引失效的真正原因。

那這種情況該怎么解決呢？

自然是把表的字符集修改為一致，當然如果數據量很大無法做到online ddl的話，那就嘗試改寫sql，避免索引字段出現函數操作。當然改寫sql不一定能滿足所有情況，需要根據實際情況來判斷。

我們再回到開頭，為什么說字符集編碼不一致可能會發生隱私類型轉換，而不是一定會發生呢？

這是因為MySQL在背后做了很多的優化工作，幫助我們提前把坑給填上了。

還是上面的sql為例，我們稍微改動一下：

SELECT
  * 
FROM
  table1 t1
  LEFT JOIN table2 t2 ON t1.key1 = t2.key2 
  --這里將t1.id改成t2.id
  AND t2.id = 1;

我們修改一下查詢條件，將原本條件中的t1.id改為t2.id，再來看一下優化后的sql：

可以看到，table2可以用到主鍵索引了。

這是因為，通過判斷條件中的t2.id=1，已經可以通過主鍵唯一定位到一條記錄了，所以可以直接使用table2的主鍵索引。當然，table2的key2索引還是用不了的。

一般來說，對索引字段做顯示的函數操作，是很容易發現和修正的。

這種字符集編碼不一樣的情況，確實是防不勝防，只能建議從建表初始，就確定良好的編碼規范，統一字符集來避免了。

另外建議大家養成隨手explain的習慣，可以在問題發生前避免很多問題。