MySQL索引失效之隐式转换的问题

吾爱主题阅读：152 2024-04-01 23:21:50 评论：0

常见索引失效：

1. 条件索引字段"不干净"：函数操作、运算操作

2. 隐式类型转换：字符串转数值；其他类型转换

3. 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取

一、常见索引失效场景

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 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的值有序组织索引树），因此优化器就决定放弃走索引树搜索功能。

但是，条件字段函数操作下，也并非完全的走全表扫描，优化器并非完全的放弃该字段索引。

如上，由于ix_c1索引树是根节点c1和叶子节点id构造的，虽然因为c1上的函数操作导致放弃索引定位，但优化器可以选择遍历该索引树，使用覆盖索引（Using index），无需回表，将所需的id和c1数据返回Server层后进行后续的abs()和where过滤。

2、条件字段运算操作

如上，虽然“+1”的操作并没有破坏c1索引的有序性，但优化器仍然没有使用该索引快速定位。因此，等号左边，注意优化掉索引字段上的运算操作。

3、隐式类型转换

如上，c2字段是varchar类型，是字符串和数值的比较，此时，MySQL是将字符串转换成数字，即此处的c2被CAST(c2 AS signed int)，这就相当于对条件字段做了函数操作，优化器放弃走树索引定位。

4、隐式字符编码转换

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 # 创建一个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 的超集，再做隐式自动类型转换时，为了避免数据在转换过程中由于截断导致数据错误，会“按数据长度增加的方向”进行转换。

表连接过程中，被驱动表的索引字段上加函数操作，会导致对被驱动表做全表扫描。

优化手法：

修改统一join字段的字符集

对驱动表下手，将连接字段的字符集转换成被驱动表连接字段的字符集

1 2 3 4 5 6 7 root@test 18:09 > explain select n.* from t_num n join t_cou c on convert (n.c2 using utf8) = 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_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | ref | ix_c2 | ix_c2 | 32 | func | 1 | 100.00 | Using where ; Using index | + ----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+

二、类型转换

1、字符串转整型

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 # 字符开头的一律为0 root@test 18:44 > select convert ( "abc" , unsigned integer ); + ----------------------------------+ | convert ( "abc" , unsigned integer ) | + ----------------------------------+ | 0 | + ----------------------------------+ # 'abc' = 0是成立的，因此查询时等号右边使用对应的类型很重要，0匹配出字段字符开头数据， '0' 只匹配0 root@test 18:44 > select 'abc' = 0; + -----------+ | 'abc' = 0 | + -----------+ | 1 | + -----------+ # 数字开头的，直接截取到第一个不是字符的位置 root@test 18:45 > select convert ( "123abc" , unsigned integer ); + -------------------------------------+ | convert ( "123abc" , unsigned integer ) | + -------------------------------------+ | 123 | + -------------------------------------+

2、时间类型转换

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 root@test 19:11 > show create table time_demo\G *************************** 1. row *************************** Table : time_demo Create Table : CREATE TABLE `time_demo` ( `id` int (11) NOT NULL AUTO_INCREMENT, `c1` datetime DEFAULT NULL , `c2` date DEFAULT NULL , PRIMARY KEY (`id`), KEY `ix_c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 root@test 19:15 > select count (*) from time_demo; + ----------+ | count (*) | + ----------+ | 11 | + ----------+ root@test 19:16 > select * from time_demo limit 4; + ----+---------------------+------------+ | id | c1 | c2 | + ----+---------------------+------------+ | 1 | 2022-01-08 00:01:01 | 2022-01-08 | | 2 | 2022-01-06 23:01:01 | 2022-01-06 | | 3 | 2022-01-06 00:00:00 | 2022-01-06 | | 4 | 2022-01-08 00:00:00 | 2022-01-08 | + ----+---------------------+------------+ # 1. date 转datetime：末尾追加 00:00:00 root@test 19:11 > select * from time_demo where c1 between "2022-01-06" and "2022-01-08" ; + ----+---------------------+------------+ | id | c1 | c2 | + ----+---------------------+------------+ | 2 | 2022-01-06 23:01:01 | 2022-01-06 | | 3 | 2022-01-06 00:00:00 | 2022-01-06 | | 4 | 2022-01-08 00:00:00 | 2022-01-08 | + ----+---------------------+------------+ # 结果分析：c1是datetime类型，进行比较时， between and 中的 date 类型会转换成datetime # 即 where c1 between "2022-01-06 00:00:00" and "2022-01-08 00:00:00" ; # 同 where c1 >= "2022-01-06 00:00:00" and c1 <= "2022-01-08 00:00:00" ; root@test 19:42 > explain select * from time_demo where c1 between "2022-01-06" and "2022-01-08" ; + ----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | time_demo | NULL | range | ix_c1 | ix_c1 | 6 | NULL | 3 | 100.00 | Using index condition | + ----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+ # 格式化 date 转datetime root@test 19:23 > select date_format( "2022-01-08" , "%Y-%m-%d %H:%i:%s" ); + -----------------------------------------------+ | date_format( "2022-01-08" , "%Y-%m-%d %H:%i:%s" ) | + -----------------------------------------------+ | 2022-01-06 00:00:00 | + -----------------------------------------------+ # 2.datetime转 date ：直接截取 date 部分 root@test 19:47 > select date (c1) from time_demo limit 1; + ------------+ | date (c1) | + ------------+ | 2022-01-06 | + ------------+ # 3. date 转 time ，没有意义，直接变成 00:00:00

到此这篇关于MySQL索引失效之隐式转换的问题的文章就介绍到这了,更多相关MySQL 隐式转换内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/geaozhang/p/15779520.html

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。