likes
comments
collection
share

MySQL-隐式类型转换导致索引失效

作者站长头像
站长
· 阅读数 41

问题描述

在工作中开发了一个查询接口，功能是：根据用户ID，在数据表（有大约1000万条数据）查询用户的记录，用户ID（user_id）是唯一索引。

在自测时发现，接口响应很慢。因为接口中没有其他逻辑，只执行了该查询操作，所以使用 MySQL 的 EXPLAINN 来查看 SQL 的执行计划，发现并没有使用到主键索引，而是进行了全表扫描。

问题复现过程如下：

1、数据表DDL如下，使用 user_id 作为主键索引，模拟插入10000条记录：

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;

2、EXPLAIN 查询 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|          |index|PRIMARY      |PRIMARY|206    |   |10000|    10.0|Using where; Using index|

可以看到，虽然 key 列显示使用了主键索引，但是 rows显示扫描了全表。MySQL Server 调用 InnoDB 引擎依次取出了每条数据，比较主键值是否相等，但查询并没有使用到主键索引。

经过仔细比较发现，数据表中 user_id 字段是 VARCHAR 类型，SQL语句中 user_id是INT 类型。MySQL 在执行语句时会对类型做转换，导致了索引失效。

隐式转换

MySQL 的官方文档：dev.mysql.com/doc/refman/…，介绍了 MySQL类型隐式转换的规则：

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

  • 如果一个或两个参数均为NULL，则比较结果为NULL；但是 <=> 相等比较运算符除外，对于NULL <=> NULL，结果为true，无需转换。
  • 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
  • 如果两个参数都是整数，则将它们作为整数进行比较。
  • 如果十六进制不是和数字作比较，它会被视作是二进制字符串。
  • 如果参数之一是TIMESTAMP或DATETIME列，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳，但对于IN() 内的参数不执行此操作。为了安全起见，在进行比较时，请始终使用完整的时间、日期或时间字符串。例如，要在日期和时间参数上使用 BETWEEN 函数时，最好使用 CAST() 函数把参数显示转换成所需的数据类型。
  • 一个或多个表中的单行子查询不视为常量。例如，如果子查询返回的整数要与DATETIME值进行比较，则比较将作为两个整数完成，子查询返回的整数不转换为时间值。参见上一条，这种情况下请使用CAST()将子查询的结果整数值转换为DATETIME。
  • 如果参数之一是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较；如果另一个参数是浮点值，则将参数作为浮点值进行比较。
  • 在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。

按照上述规则的最后一条，我们的查询SQL中，字符串与整数的比较会被转换成两个浮点数比较，左边是字符串类型 "1" 转换成浮点数为1.0，右边 INT类型的 1 转换成浮点数 1.0 。

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

MySQL在执行我们的查询SQL时，会 CAST 函数把每一行主键列的值转换成浮点数，然后再与条件参数做比较。而 InnoDB 存储引擎中，在索引列上使用函数会导致索引失效，所以最后导致了全表扫描。

我们只需要把 SQL 中 WHERE 条件改成字符串，就可以使用到主键索引了：

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 的隐式转换

附录

1、MySQL 中字符串转浮点型时的规则如下：

  • 不以数字开头的字符串都将转换为0：
SELECT CAST('abc' AS UNSIGNED)
​
CAST('abc' AS UNSIGNED)|
-----------------------+
                      0|
  • 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止：
SELECT CAST(' 0123abc' AS UNSIGNED)
​
CAST(' 0123abc' AS UNSIGNED)|
----------------------------+
                         123|

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

原文链接：mp.weixin.qq.com/s/DGEvqiHPh…

转载自:https://juejin.cn/post/6994006962870419470
评论
请登录