您现在的位置是：网站首页> 编程资料编程资料
MySQL索引失效之隐式转换的问题_Mysql_
2023-05-26
333人已围观
简介 MySQL索引失效之隐式转换的问题_Mysql_
常见索引失效：
1. 条件索引字段"不干净"：函数操作、运算操作
2. 隐式类型转换：字符串转数值；其他类型转换
3. 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取
一、常见索引失效场景
root@test 10:50 > show create table t_num\G *************************** 1. row *************************** Table: t_num Create Table: CREATE TABLE `t_num` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL, `c2` varchar(11) NOT NULL, PRIMARY KEY (`id`), KEY `ix_c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 root@test 10:51 > select * from t_num; +----+----+----+ | id | c1 | c2 | +----+----+----+ | 1 | -2 | -2 | | 2 | -1 | -1 | | 3 | 0 | 0 | | 4 | 1 | 1 | | 5 | 2 | 2 | +----+----+----+ # 在c1字段上加上索引 root@test 10:52 > alter table t_num add index ix_c1(c1); # 标准使用情况下，索引有效 root@test 10:55 > explain select * from t_num where c1 = -1; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_num | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1、条件字段函数操作
# 在where中c1上加上abs()绝对值函数，可以看到type=ALL，全表扫描，在Server层进行绝对值处理后进行比较 root@test 10:58 > explain select * from t_num where abs(c1) = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_num | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，对索引字段做函数操作，即where条件列上不干净时，可能会破坏索引值的有序性（按照c1的值有序组织索引树），因此优化器就决定放弃走索引树搜索功能。
但是，条件字段函数操作下，也并非完全的走全表扫描，优化器并非完全的放弃该字段索引。
# 选择查询的数据，只有id和c1字段，可以看到type=index，使用到了ix_c1索引 root@test 10:59 > explain select id,c1 from t_num where abs(c1) = 1; +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_num | NULL | index | NULL | ix_c1 | 4 | NULL | 5 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
如上，由于ix_c1索引树是根节点c1和叶子节点id构造的，虽然因为c1上的函数操作导致放弃索引定位，但优化器可以选择遍历该索引树，使用覆盖索引（Using index），无需回表，将所需的id和c1数据返回Server层后进行后续的abs()和where过滤。
2、条件字段运算操作
# where条件里，对c1进行运算操作 root@test 11:03 > explain select * from t_num where c1 + 1 = 2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_num | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，虽然“+1”的操作并没有破坏c1索引的有序性，但优化器仍然没有使用该索引快速定位。因此，等号左边，注意优化掉索引字段上的运算操作。
3、隐式类型转换
# 在c2字段上加上索引 root@test 12:30 > alter table t_num add index ix_c2(c2); # 标准使用情况下（注：c2是varchar类型的），索引有效 root@test 12:30 > explain select * from t_num where c2 = "2"; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_num | NULL | ref | ix_c2 | ix_c2 | 42 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ # 去掉等号右边值的引号，即字符串和数值进行比较，索引失效 root@test 12:30 > explain select * from t_num where c2 = 2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_num | NULL | ALL | ix_c2 | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，c2字段是varchar类型，是字符串和数值的比较，此时，MySQL是将字符串转换成数字，即此处的c2被CAST(c2 AS signed int)，这就相当于对条件字段做了函数操作，优化器放弃走树索引定位。
4、隐式字符编码转换
# 创建一个t_cou表，表结构基本和前面的t_num相同，唯一不同的设置是表字符集CHARSET=utf8 root@test 14:02 > show create table t_cou\G *************************** 1. row *************************** Table: t_cou Create Table: CREATE TABLE `t_cou` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL, `c2` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `ix_c1` (`c1`), KEY `ix_c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 root@test 14:02 > insert into t_cou select * from t_num; # join表，t_num和t_cou通过c2字段进行关联查询 root@test 14:03 > select n.* from t_num n -> join t_cou c -> on n.c2 = c.c2 -> where n.c1 = 1; +----+----+----+ | id | c1 | c2 | +----+----+----+ | 4 | 1 | 1 | +----+----+----+ root@test 14:23 > explain select n.* from t_num n join t_cou c on n.c2 = c.c2 where c.c1 = 1; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | c | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | n | NULL | ref | ix_c2 | ix_c2 | 42 | func | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ # 执行计划分析: # 1.操作的c表，使用了ix_c1定位到一行数据 # 2.从c表定位到的行数据，拿到c2字段去操作n表，t_cou称为驱动表，t_num称为被驱动表 # 3.ref=func说明使用了函数操作，指的是n.c2=CONVERT(c.c2 USING utf8mb4) # 4.同时Using index condition，ix_c2读取查询时，使用被下推的条件过滤，满足条件的才回表 root@test 14:23 > explain select n.* from t_num n join t_cou c on n.c2 = c.c2 where n.c1 = 1; +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ | 1 | SIMPLE | n | NULL | ref | ix_c1,ix_c2 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | index | NULL | ix_c2 | 32 | NULL | 5 | 100.00 | Using where; Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ # 执行计划分析: # 1.操作的n表，使用了ix_c1定位到一行数据 # 2.从n表定位到的行数据，拿到c2字段去操作c表，t_num称为驱动表，t_cou称为被驱动表 # 3.同样的n.c2=c.c2，会将c.c2的字符集进行转换，即被驱动表的索引字段上加函数操作，索引失效 # 4.BNL，表join时，驱动表数据读入join buffer，被驱动表连接字段无索引则全表扫，每取一行和join buffer数据对比判断，作为结果集返回
如上，分别对t_num、 t_cou作为驱动表和被驱动表的执行计划分析，总结：
utf8mb4和utf8两种不同字符集（编码）类型的字符串在做比较时，MySQL会先把 utf8 字符串转成 utf8mb4 字符集，再做比较。为什么？字符集 utf8mb4 是 utf8 的超集，再做隐式自动类型转换时，为了避免数据在转换过程中由于截断导致数据错误，会“按数据长度增加的方向”进行转
相关内容
- MYSQL插入数据时检查字段值是否重复的方法详解_Mysql_
- MySQL 字符串转换为数字的方法小结_Mysql_
- 一个 20 秒 SQL 慢查询优化处理方案_Mysql_
- 详解MySQL如何有效的存储IP地址及字符串IP和数值之间如何转换_Mysql_
- SQL去重方法汇总_Mysql_
- MySQL数据库终端—常用操作指令代码_Mysql_
- SQL查询至少连续n天登录的用户_Mysql_
- SQL查询至少连续七天下单的用户_Mysql_
- mysql+mybatis实现存储过程+事务 + 多并发流水号获取_Mysql_
- Ubuntu 18.04.4安装mysql的过程详解 亲测可用_Mysql_
