最近同事接手了一個(gè)老項(xiàng)目，在簡(jiǎn)單的做了幾個(gè)小需求后，經(jīng)過(guò)自測(cè)沒(méi)問(wèn)題就發(fā)布上線(xiàn)了，沒(méi)想的是，上線(xiàn)沒(méi)一會(huì)監(jiān)控平臺(tái)就報(bào)警有全表掃描的慢SQL。

因?yàn)樯暇€(xiàn)的幾個(gè)功能使用頻率也不高，所以也只是告訴同事慢SQL的情況，讓該同事先檢查優(yōu)化。

結(jié)果直到快下班，才收到同事提交的新版本。一問(wèn)，才知道竟然是一個(gè)多表關(guān)聯(lián)查詢(xún)中的兩張表的編碼方式不一致，導(dǎo)致出現(xiàn)了隱式類(lèi)型轉(zhuǎn)換，從而去掃描全表了。

而之所以該同事在測(cè)試環(huán)境使用了各種手段都沒(méi)有復(fù)現(xiàn)線(xiàn)上的場(chǎng)景，是因?yàn)闇y(cè)試環(huán)境的表編碼是一致的，果然老項(xiàng)目處處是坑啊。

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

首先，我們新建兩張表復(fù)現(xiàn)一下現(xiàn)場(chǎng)。

-- 創(chuàng)建table1，并對(duì)key1設(shè)置二級(jí)索引
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;

-- 創(chuàng)建table2，并對(duì)key2設(shè)置二級(jí)索引
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;

請(qǐng)注意table1的字符集編碼是utf8，而table2的字符集編碼是utf8mb4。

我們執(zhí)行一條普通的左關(guān)聯(lián)sql：

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

通過(guò)explain查看一下執(zhí)行計(jì)劃：

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

那真的是因?yàn)樽址D(zhuǎn)換導(dǎo)致的索引失效嗎？

口說(shuō)無(wú)憑，我們看一下MySQL經(jīng)過(guò)優(yōu)化器優(yōu)化的sql：

執(zhí)行explain select ...之后，再執(zhí)行show warnings即可看到優(yōu)化后的sql。

可以清楚的看到，經(jīng)過(guò)優(yōu)化后的sql，其實(shí)是對(duì)table1的key1字段做了convert轉(zhuǎn)換，即從utf8轉(zhuǎn)換為utf8mb4。

那有的朋友可能要問(wèn)了， 明明是對(duì)key1字段做的convert，怎么導(dǎo)致table2無(wú)法走索引了呢？

其實(shí)這是因?yàn)榇颂幰詔able1為驅(qū)動(dòng)表，table2為被驅(qū)動(dòng)表，從table1中查出數(shù)據(jù)，然后去table2中匹配，但是table1查出來(lái)的數(shù)據(jù)要做類(lèi)型轉(zhuǎn)換，對(duì)于table2來(lái)說(shuō)，無(wú)論是索引的等值匹配，還是范圍匹配，都需要確定值才行。值不確定，干脆走全表掃描一條條的匹配。

換句話(huà)說(shuō)，相當(dāng)于執(zhí)行了下面的sql：

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

看到這，大家是否回憶起我們經(jīng)常說(shuō)的sql優(yōu)化：

不要在索引字段上函數(shù)操作。

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

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

自然是把表的字符集修改為一致，當(dāng)然如果數(shù)據(jù)量很大無(wú)法做到online ddl的話(huà)，那就嘗試改寫(xiě)sql，避免索引字段出現(xiàn)函數(shù)操作。當(dāng)然改寫(xiě)sql不一定能滿(mǎn)足所有情況，需要根據(jù)實(shí)際情況來(lái)判斷。

我們?cè)倩氐介_(kāi)頭，為什么說(shuō)字符集編碼不一致可能會(huì)發(fā)生隱私類(lèi)型轉(zhuǎn)換，而不是一定會(huì)發(fā)生呢？

這是因?yàn)镸ySQL在背后做了很多的優(yōu)化工作，幫助我們提前把坑給填上了。

還是上面的sql為例，我們稍微改動(dòng)一下：

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

我們修改一下查詢(xún)條件，將原本條件中的t1.id改為t2.id，再來(lái)看一下優(yōu)化后的sql：

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

這是因?yàn)椋ㄟ^(guò)判斷條件中的t2.id=1，已經(jīng)可以通過(guò)主鍵唯一定位到一條記錄了，所以可以直接使用table2的主鍵索引。當(dāng)然，table2的key2索引還是用不了的。

一般來(lái)說(shuō)，對(duì)索引字段做顯示的函數(shù)操作，是很容易發(fā)現(xiàn)和修正的。

這種字符集編碼不一樣的情況，確實(shí)是防不勝防，只能建議從建表初始，就確定良好的編碼規(guī)范，統(tǒng)一字符集來(lái)避免了。

另外建議大家養(yǎng)成隨手explain的習(xí)慣，可以在問(wèn)題發(fā)生前避免很多問(wèn)題。