SQL隐式转换导致索引失效_字符集不一致

梦莱3年前 (2023-04-14)技术文章1968

3.字符集不一致导致索引失效

示例 SQL 如下，通过查看执行计划发现 XXX 和 XXXX 表在进行表关联的时候没有走索引，导致 SQL 扫描数量较大。核实表结构发现表关联对应列都存在索引，最终查看字符集发现，两个表的字符集不同存在隐式转换。

--当前SQL
--DB:lefit_live
SELECT ........
FROM XX lu
LEFT JOIN (
SELECT r.*
FROM XXXXX r
WHERE r.is_delete != 1
GROUP BY r.phone
) luw
ON lu.phone = luw.phone
LEFT JOIN XXX luc ON luc.user_id = lu.user_id
LEFT JOIN XXXX lcc ON lcc.channel_code = luc.channel_code
WHERE 1 = 1
AND lu.user_id = 11111
LIMIT 0, 2

查看表结构发现 lcc 表字符集为 utf8，但是 luc 表字符集为 utf8mb4。utf8mb4和utf8两种不同字符集（编码）类型的字符串在做⽐较时，MySQL会先把utf8 字符串转成utf8mb4 字符集，再做⽐较，这就导致了隐式转换的出现。

utf8mb4 是 utf8 的超集，在做隐式⾃动类型转换时，为了避免数据在转换过程中由于截断导致数据错误，会“按数据长度增加的⽅向”进⾏转换。

该情况可以通过两种方法进行优化。1.修改表字符集。ALTER table 表名 CHARACTER SET 字符集名称；但该方法一般不推荐，首先需要保证该表可以改为 utf8 字符集；其次还要保证修改后，其他使用该表进行表连接的 SQL 不会出现隐式转换问题；且本身 DDL 就是一个比较消耗资源的操作。2.通过修改 SQL 完成优化。可以通过给驱动表关联列加 convert() 函数，使其临时修改成一致的字符集，进行优化。具体改写 SQL 如下：

--改写后
SELECT lu.user_id, lu.phone, lu.nick_name, lu.head_img, lu.gender
, lu.birthday, lu.status, lu.ctime AS reg_time, luc.ctype, luc.channel_code
, lcc.name AS channel_name, luc.source_id, luw.nickname AS wx_nick_name, luw.unionid
FROM XX lu
LEFT JOIN (
SELECT r.*
FROM XXXXX r
WHERE r.is_delete != 1
GROUP BY r.phone
) luw
ON lu.phone = luw.phone
LEFT JOIN XXX luc ON luc.user_id = lu.user_id
LEFT JOIN XXXX lcc ON lcc.channel_code =convert(luc.channel_code USING utf8)
WHERE 1 = 1
AND lu.user_id = 11111
LIMIT 0, 2