MySQL：两张表编码方式不一致，关联查询一定会导致索引失效吗？

最近同事接手了一个老项目，张表在简单的编码做了几个小需求后，经过自测没问题就发布上线了，关联没想的查询是，上线没一会监控平台就报警有全表扫描的定会导慢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的习惯，可以在问题发生前避免很多问题。