MySQL索引失效后隐式转换的问题这么解决

MySQL索引失效后隐式转换的问题这么解决，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

常见索引失效：

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

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

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

一、常见索引失效场景

root@test 10:50 > show create table t_num\G*************************** 1. row ***************************       Table: t_numCreate 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=utf8mb4root@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=utf8root@test 14:02 > show create table t_cou\G*************************** 1. row ***************************       Table: t_couCreate 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=utf8root@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字段的字符集

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

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、字符串转整型

# 字符开头的一律为0root@test 18:44 > select convert("abc", unsigned integer);+----------------------------------+| convert("abc", unsigned integer) |+----------------------------------+|                                0 |+----------------------------------+# 'abc' = 0是成立的，因此查询时等号右边使用对应的类型很重要，0匹配出字段字符开头数据，'0'只匹配0root@test 18:44 > select 'abc' = 0;+-----------+| 'abc' = 0 |+-----------+|         1 |+-----------+# 数字开头的，直接截取到第一个不是字符的位置root@test 18:45 > select convert("123abc", unsigned integer);+-------------------------------------+| convert("123abc", unsigned integer) |+-------------------------------------+|                                 123 |+-------------------------------------+

2、时间类型转换

root@test 19:11 > show create table time_demo\G*************************** 1. row ***************************       Table: time_demoCreate 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=utf8mb4root@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:00root@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转datetimeroot@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索引失效后隐式转换的问题这么解决问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注行业资讯频道了解更多相关知识。