建站
咨詢
售后
建站咨詢
新聞中心
最近同事接手了一個老項目，在簡單的做了幾個小需求后，經(jīng)過自測沒問題就發(fā)布上線了，沒想的是，上線沒一會監(jiān)控平臺就報警有全表掃描的慢SQL。
因為上線的幾個功能使用頻率也不高，所以也只是告訴同事慢SQL的情況，讓該同事先檢查優(yōu)化。
結果直到快下班，才收到同事提交的新版本。一問，才知道竟然是一個多表關聯(lián)查詢中的兩張表的編碼方式不一致，導致出現(xiàn)了隱式類型轉換，從而去掃描全表了。
而之所以該同事在測試環(huán)境使用了各種手段都沒有復現(xiàn)線上的場景，是因為測試環(huán)境的表編碼是一致的，果然老項目處處是坑啊。
今天借著這個問題，帶大家了解一下，為什么字符集編碼不一致(可能)會發(fā)生不走索引掃描全表的問題。（注意，是可能，并非一定）。
首先，我們新建兩張表復現(xiàn)一下現(xiàn)場。
-- 創(chuàng)建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;
-- 創(chuàng)建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。
我們執(zhí)行一條普通的左關聯(lián)sql：
SELECT
*
FROM
table1 t1
LEFT JOIN table2 t2 ON t1.key1 = t2.key2
AND t1.id = 1;
通過explain查看一下執(zhí)行計劃：
可以看到，table1使用了索引idx_key1，但是table2卻沒有命中索引，反而執(zhí)行了全表掃描。
那真的是因為字符集轉換導致的索引失效嗎？
口說無憑，我們看一下MySQL經(jīng)過優(yōu)化器優(yōu)化的sql：
執(zhí)行explain select ...之后，再執(zhí)行show warnings即可看到優(yōu)化后的sql。
可以清楚的看到，經(jīng)過優(yōu)化后的sql，其實是對table1的key1字段做了convert轉換，即從utf8轉換為utf8mb4。
那有的朋友可能要問了， 明明是對key1字段做的convert，怎么導致table2無法走索引了呢？
其實這是因為此處以table1為驅動表，table2為被驅動表，從table1中查出數(shù)據(jù)，然后去table2中匹配，但是table1查出來的數(shù)據(jù)要做類型轉換，對于table2來說，無論是索引的等值匹配，還是范圍匹配，都需要確定值才行。值不確定，干脆走全表掃描一條條的匹配。
換句話說，相當于執(zhí)行了下面的sql：
SELECT
*
FROM
table2
WHERE
CONVERT ( key2 USING utf8mb4 ) = 'abc';
看到這，大家是否回憶起我們經(jīng)常說的sql優(yōu)化：
不要在索引字段上函數(shù)操作。
這才是索引失效的真正原因。
那這種情況該怎么解決呢？
自然是把表的字符集修改為一致，當然如果數(shù)據(jù)量很大無法做到online ddl的話，那就嘗試改寫sql，避免索引字段出現(xiàn)函數(shù)操作。當然改寫sql不一定能滿足所有情況，需要根據(jù)實際情況來判斷。
我們再回到開頭，為什么說字符集編碼不一致可能會發(fā)生隱私類型轉換，而不是一定會發(fā)生呢？
這是因為MySQL在背后做了很多的優(yōu)化工作，幫助我們提前把坑給填上了。
還是上面的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，再來看一下優(yōu)化后的sql：
可以看到，table2可以用到主鍵索引了。
這是因為，通過判斷條件中的t2.id=1，已經(jīng)可以通過主鍵唯一定位到一條記錄了，所以可以直接使用table2的主鍵索引。當然，table2的key2索引還是用不了的。
一般來說，對索引字段做顯示的函數(shù)操作，是很容易發(fā)現(xiàn)和修正的。
這種字符集編碼不一樣的情況，確實是防不勝防，只能建議從建表初始，就確定良好的編碼規(guī)范，統(tǒng)一字符集來避免了。
另外建議大家養(yǎng)成隨手explain的習慣，可以在問題發(fā)生前避免很多問題。
網(wǎng)站標題：MySQL：兩張表編碼方式不一致，關聯(lián)查詢一定會導致索引失效嗎？
標題來源：http://www.fisionsoft.com.cn/article/djcoegj.html
