MySQL整型与字符串类型比较 触发隐式转换 导致索引失效的问题


问题描述

公司五一做了场线上活动,用户参与火爆。随着活动用户数量的不断增加,页面加载变的逐渐缓慢。在排查优化的时候,发现查询用户信息的接口尤其的慢,这个接口没有太多的业务处理,仅仅是查询一下用户当前的活动积分信息,不应该这么慢。于是进一步排查,发现是因为查询操作没走索引,由此,延伸出MySQL整型与字符串类型隐式转导致索引失效的问题。

问题重现

首先,我们创建一张用户表test_user,其中USER_ID我们设置为varchar类型并加上唯一索引。

CREATE TABLE `test_user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(11) DEFAULT NULL COMMENT '用户账号',
  `USER_NAME` varchar(255) DEFAULT NULL COMMENT '用户名',
  `AGE` int(5) DEFAULT NULL COMMENT '年龄',
  `COMMENT` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UNIQUE_USER_ID` (`USER_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入几条测试数据:

然后我们执行EXPLAIN SQL 看一下索引命中情况:

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 1111;

得到如下结果:

我们会发现,虽然USER_ID字段加了索引,但是当我们使用数值类型进行查询时,没走索引,而是进行了全表扫描。

问题成因

这里设计到一个【MySQL隐式类型转换】的概念。
官方文档是这么说的:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.

翻译过来意思就是:

当不同类型的数据一起使用时,MySQL会根据需要自动将数字转换为字符串,将字符串转换数字。

所以我们这里出现的这个问题,就是因为我们将数字类型的 1111 和数据库中voucher类型的 USER_ID 进行查询比较,导致触发了隐式转换,进而索引没有生效。

解决办法

在这里,只需要根据实际情况,选择把 USER_ID 修改为 “int”类型,或者在程序的时候,将 1111 两端加引号,变成"1111"即可。
还有一种办法,是使用CAST函数进行显示转换。
如下所示:

mysql> SELECT 38.8, CAST(38.8 AS CHAR);

拓展

其他会触发隐式转换的情况

The following rules describe how conversion occurs for comparison operations:
If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

翻译过来就是:

  • 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
  • 两个参数都是字符串,会按照字符串来比较,不做类型转换
  • 两个参数都是整数,按照整数来比较,不做类型转换
  • 十六进制的值和非数字做比较时,会被当做二进制串
  • 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
  • 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
  • 所有其他情况下,两个参数都会被转换为浮点数再进行比较

参考链接

MySQL :: MySQL 5.7 Reference Manual :: 12.2 Type Conversion in Expression Evaluation
谈谈MySQL隐式类型转换

本文发表于2020年05月06日 15:55
阅读 147 讨论 0 喜欢 1

抢先体验

扫码体验
趣味小程序
文字表情生成器

闪念胶囊

又是一年五一,祝我们工人阶级劳动节快乐! 今年被困在北京了,离境再入境需要隔离十五天。只能京津冀周边走一走了,想出去玩啊啊啊啊啊~

人活一辈子,不是一年两年。时间是有连续性的,做抉择的时候要多看几步。保持警惕,大丈夫有所为,有所不为。

跟人接触,不要想:我能从你身上得到什么,要想:我能给你什么。 想通了,内核就稳了。

这个世界上,别人只会看你现在的样子而不是以后的样子。你以后的样子只有自己才相信。如果没有执行力,一切都是虚妄。

对普通人来说,人和人相处其实最重要的是感觉。感觉不好,你说什么都没用,怎么解释都没用,越说越错,反正最后不好的锅都往你身上扣。所谓“说你行你就行,不行也行。说你不行,你就不行,行也不行”就是这个意思。狼要吃人根本不需要理由,你也同样叫不醒装睡的人。遇到这种情况,早点闪人才是上策。不过大部分人的问题是没有闪人的心态,能力,和资源。

快捷链接
网站地图
提交友链
Copyright © 2016 - 2020 Cion.
All Rights Reserved.
ICP备案:鲁ICP备19012333号-4.

鲁公网安备 37061302000383号.