MySQL:两张表编码方式不一致,关联查询一定会导致索引失效吗?

数据库 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的习惯,可以在问题发生前避免很多问题。

责任编辑:姜华 来源: 今日头条
相关推荐

2021-05-27 18:06:30

MySQL编码数据

2010-06-02 10:53:28

MySQL版本

2024-06-20 12:38:07

2013-12-13 14:46:55

OSPFMTU邻接关系

2024-05-11 07:37:43

数据Redis策略

2017-06-20 09:42:52

网络安全法数据隐私法网络安全

2024-04-07 09:00:00

MySQL

2023-09-15 10:29:32

Java接口

2013-03-29 11:16:17

2017-08-25 17:59:41

浮点运算C语言

2022-03-16 15:54:52

MySQL数据format

2018-07-15 08:18:44

缓存数据库数据

2019-01-17 08:16:05

MySQL规范数据库

2024-06-20 08:03:35

MySQL数据表版本

2023-09-13 13:05:01

Java项目

2018-07-08 07:38:28

数据库缓存数据

2022-03-18 10:53:49

数据系统架构

2020-07-20 14:06:38

数据库主从同步服务

2021-12-26 14:32:11

缓存数据库数据

2021-01-19 10:39:03

Redis缓存数据
点赞
收藏

51CTO技术栈公众号