彻底搞清楚MySQL字符编码不一致造成索引失效的问题

2024-06-04 02:35| 来源: 网络整理| 查看: 265

前言

我相信你一定听说过，如果两张表字符编码不一致，索引字段在进行join时会导致索引失效，但一定是这样的吗？本文就来一起仔细分析一下这个问题。

准备

我们先准备两个表，一个采用utf8的字符集，一个采用utf8mb4的字符集，并分别插入一条数据。

-- 建表采用utf8字符集 CREATE TABLE t_utf8 ( id INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id', age INT ( 11 ) COMMENT '年龄', NAME VARCHAR ( 32 ) COMMENT '姓名', PRIMARY KEY ( id ), KEY idx_name ( NAME ), KEY idx_age ( age ) ) ENGINE = INNODB DEFAULT CHARSET = utf8; -- 插入一条数据 INSERT into t_utf8 (age,name) VALUES (1,'zz'); -- 建表采用utf8mb4字符集 CREATE TABLE t_utf8m ( id INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id', age INT ( 11 ) COMMENT '年龄', NAME VARCHAR ( 32 ) COMMENT '姓名', PRIMARY KEY ( id ), KEY idx_name ( NAME ), KEY idx_age ( age ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; -- 插入一条数据 INSERT into t_utf8m (age,name) VALUES (1,'zz');

现在两个表的name和age字段上都建立了索引，对于下面4条SQL语句，你能分析出a.age = b.age，a.name = b.name都会走索引吗？

-- 下面4条SQL，a.age = b.age，a.name = b.name会走索引吗？ EXPLAIN select * from t_utf8m a, t_utf8 b where a.age = b.age and a.age = 1; EXPLAIN select * from t_utf8m a, t_utf8 b where a.age = b.age and b.age = 1; EXPLAIN select * from t_utf8m a, t_utf8 b where a.name = b.name and a.name = 'zz'; EXPLAIN select * from t_utf8m a, t_utf8 b where a.name = b.name and b.name = 'zz';

你可以直接运行下试试，结果是a.age = b.age会走索引，而name中，下面这一句a.name = b.name不会走索引。

EXPLAIN select * from t_utf8m a, t_utf8 b where a.name = b.name and a.name = 'zz'; 分析

我们先来分析一下a.name = b.name吧，为什么会出现一个走索引，一个不走索引呢？

解析SQL执行

通过执行计划我们可以看出，先执行a表的name条件过滤，走了idx_name索引，然后再与b表进行匹配。在这里插入图片描述

我们称a表为驱动表，b表为被驱动表，最终SQL语句可以被理解为如下：

第一步先执行a表的条件过滤 select * from t_utf8m a where a.name = 'zz'; 第二步把结果集带到b中 select * from t_utf8 b where b.name = 'a表过滤出来的值';

好了，现在我们清楚了SQL语句的执行过滤，再来看看两张表的字符集不一致时，是怎么处理的。

字符集转换

我们知道，字符集utf8mb4是utf8的超集，所以按照子集向超集转换的原则（超集向子集转换，可能会丢失精度），utf8会被转换成utf8mb4。

了解这个规则后，接着上面那条SQL语句就能分析出如下形式：

select * from t_utf8 b where CONVERT(b.name USING utf8mb4) = 'a表过滤出来的值';

看到这，你应该清楚到底是什么原因导致索引失效了，没错，就是索引列上执行了函数操作。

为什么另一个可以走索引 EXPLAIN select * from t_utf8m a, t_utf8 b where a.name = b.name and b.name = 'zz';

在这里插入图片描述这里我们也通过执行计划可以看出，先进行了b的过滤，然后再和a进行匹配。

所以SQL执行过程如下：

第一步先执行b表的条件过滤 select * from t_utf8 b where b.name = 'zz'; 第二步把结果集带到a中 select * from t_utf8m a where a.name = 'b表过滤出来的值'; 按照转换原则，分析出最终执行： select * from t_utf8m a where a.name = CONVERT('b表过滤出来的值' USING utf8mb4);

很明显索引列上没有发生函数操作，所以可以走索引。

总结

我想你现在应该明白了字符编码不一致导致索引失效的真实原因了吧，实际上就是索引列上发生了函数转换，如果你要解决这个问题，可以手动让utf8mb4向utf8进行转换，比如：

-- 不走索引 EXPLAIN select * from t_utf8m a, t_utf8 b where a.name = b.name and a.name = 'zz'; -- 改成这样，走索引 EXPLAIN select * from t_utf8m a, t_utf8 b where CONVERT(a.name USING utf8) = b.name and a.name = 'zz';

当然你也可以直接让两张表的字符集变的一致。

而int类型之所以不受影响，我想应该是无论utf8还是utf8mb4对于int类型来说都是一致的不需要转换吧。另外，如果再遇到列字段类型不一致的情况，实际上也是同样的道理。

【本文地址】

公司简介

联系我们