亲宝软件园·资讯

展开

MySQL隐式类型转换导致索引失效 MySQL隐式类型转换导致索引失效的解决

CodeGo 人气:0
想了解MySQL隐式类型转换导致索引失效的解决的相关内容吗,CodeGo在本文为您仔细讲解MySQL隐式类型转换导致索引失效的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL隐式类型导致索引失效,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会发生类型转换以使操作数兼容,这些转换是隐式发生的。下面描述了比较操作的隐式转换:

根据上述规则的最后一条,在前面的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|

总结

1、条件列是字符串时,如果传入的条件参数是整数,会先转换成浮点数,再全表扫描,导致索引失效;
2、条件参数要尽可能与列的类型相同,避免隐式转换,或者在传入的参数上执行转换函数,转换成与索引列相同的类型。

参考

1、浅析 MySQL 的隐式转换

加载全部内容

相关教程
猜你喜欢
用户评论