一聚教程网:一个值得你收藏的教程网站

热门教程

MySQL隐式类型转换导致索引失效代码解决方法

时间:2022-06-29 08:40:27 编辑:袖梨 来源:一聚教程网

本篇文章小编给大家分享一下MySQL隐式类型转换导致索引失效代码解决方法,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

问题

在工作中发现,有一个接口只执行一条SQL查询语句,并且SQL明明使用了主键列,但是速度很慢。

在MySQL中EXPLAINN后发现,执行时并没有使用主键索引,而是进行了全表扫描。

复现

数据表DDL如下,使用 user_id 作为主键索引:

 CREATE TABLE `user_message` (
   `user_id` varchar(50) NOT NULL COMMENT '用户ID',
   `msg_id` int(11) NOT NULL COMMENT '消息ID',
   PRIMARY KEY (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行下面的查询语句,发现虽然 key 显示使用了主键索引,但是 rows显示扫描了全表,主键索引并没有起作用:

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;
 
 id|select_type|table       |partitions|type |possible_keys|key    |key_len|ref|rows |filtered|Extra                   |
 --+-----------+------------+----------+-----+-------------+-------+-------+---+-----+--------+------------------------+
  1|SIMPLE     |user_message|          |index|PRIMARY      |PRIMARY|206    |   |10000|    10.0|Using where; Using index|

经过排查发现,数据表中 user_id 字段是 VARCHAR 类型,SQL语句中 user_id是INT 类型。MySQL 在执行语句时会对类型做转换,应该是在类型转换后导致主键索引失效。

隐式转换

MySQL 的官方文档:https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html,介绍了 MySQL类型隐式转换的规则:

当算子两边的操作数类型不一致时,MySQL会发生类型转换以使操作数兼容,这些转换是隐式发生的。下面描述了比较操作的隐式转换:

如果一个或两个参数均为NULL,则比较结果为NULL;但是 <=> 相等比较运算符除外,对于NULL <=> NULL,结果为true,无需转换。

如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。

如果两个参数都是整数,则将它们作为整数进行比较。

如果不将十六进制值与数字进行比较,则将其视为二进制字符串。

如果参数之一是TIMESTAMP或DATETIME列,而另一个参数是常量,则在执行比较之前,该常量将转换为时间戳。对于IN() 的参数不执行此操作。为了安全起见,在进行比较时,请始终使用完整的日期时间,日期或时间字符串。例如,要在将BETWEEN与日期或时间值一起使用时获得最佳结果,请使用CAST()将这些值显式转换为所需的数据类型。

一个或多个表中的单行子查询不视为常量。例如,如果子查询返回的整数要与DATETIME值进行比较,则比较将作为两个整数完成,整数不转换为时间值。参见上一条,这种情况下请使用CAST()将子查询的结果整数值转换为DATETIME。

如果参数之一是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整数值,则将参数作为十进制值进行比较;如果另一个参数是浮点值,则将参数作为浮点值进行比较。

在所有其他情况下,将参数作为浮点数(实数)进行比较。例如,将字符串和数字操作数进行比较,将其作为浮点数的比较。

根据上述规则的最后一条,在前面的SQL语句中,字符串与整数的比较会被转换成两个浮点数比较,左边是字符串类型 "1" 转换成浮点数为1.0,右边 INT类型的 1 转换成浮点数 1.0 。

按理说,两边都是浮点数,那么应该能使用索引,为什么执行时没有使用到?

原因在于,MySQL 中字符串转浮点型时的转换规则,规则如下:

1、不以数字开头的字符串都将转换为0:

 SELECT CAST('abc' AS UNSIGNED)
 
 CAST('abc' AS UNSIGNED)|
 -----------------------+
                       0|

2、以数字开头的字符串转换时会进行截取,从第一个字符截取到第一个非数字内容为止:

 SELECT CAST(' 0123abc' AS UNSIGNED)
 
 CAST(' 0123abc' AS UNSIGNED)|
 ----------------------------+
                          123|

所以,在 MySQL 里 "1"、 " 1"、"1a" 、"01"这样的字符串转成数字后都是 1 。

MySQL在执行上面的SQL语句时,会把每一行主键列的值转换成浮点数(在主键上执行了函数CAST),再与条件参数做比较。在索引列上使用函数,会导致索引失效,所以最后导致了全表扫描。

我们只需要把前面SQL中传入的参数改为字符串,就可以使用到主键索引:

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = '1';
 
 id|select_type|table       |partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra      |
 --+-----------+------------+----------+----+-------------+-------+-------+-----+----+--------+-----------+
  1|SIMPLE     |user_message|          |ref |PRIMARY      |PRIMARY|202    |const| 135|   100.0|Using index|

热门栏目