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 的官方文档：，介绍了 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|

总结

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

参考

1、浅析 mysql 的隐式转换

到此这篇关于mysql隐式类型转换导致索引失效的解决的文章就介绍到这了,更多相关mysql隐式类型转换导致索引失效内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

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

目录

问题

复现

隐式转换

总结

参考

相关推荐