MySQL 案例：类型转换也会用上索引？
原创MySQL 案例：类型转换也会用上索引？
原创
王文安@DBA
修改于 2022-04-11 16:14:53
修改于 2022-04-11 16:14:53
背景
作为大多数 MySQL DBA 都有的常识，当 MySQL 的查询中出现隐式数据类型转换，比如 int 类型的列使用字符串类型的内容作为查询条件时，会出现索引失效的问题，导致查询可能会变成全表扫描，导致数据库出现性能问题，影响业务。
问题描述
本着上面描述的常识，在做一次慢查询优化的工作当中，发现了一个比较奇怪的现象，此现象在测试环境可以构造和重现，效果如下：
mysql> explain select * from stu where math = '100';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | ref | idx_m | idx_m | 4 | const | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| sname | varchar(16) | NO | MUL | NULL | |
| cname | tinyint | YES | | NULL | |
| math | int | NO | PRI | NULL | |
| eng | int | YES | | NULL | |
| his | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>可以从示例上看到，int 列的 math 用字符串的内容作为查询条件时，依旧能用到索引。这与常规的认知出现了相反的现象。
原因简析
首先可以通过以下 SQL 语句构造一个简单的环境，数据库使用了 MySQL 官方的 8.0.28 版本，当然使用腾讯云数据库 MySQL 也可以，效果是一样的。
CREATE TABLE `stu` (
`id` int NOT NULL,
`sname` varchar(16) COLLATE utf8mb4_general_ci NOT NULL,
`cname` tinyint DEFAULT NULL,
`math` int NOT NULL,
`eng` int DEFAULT NULL,
`his` int DEFAULT NULL,
PRIMARY KEY (`id`,`math`),
KEY `idx_m` (`math`),
KEY `idx_e` (`eng`),
KEY `idx_h` (`his`),
KEY `idx_s` (`sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into stu values(1,'100',0,100,100,100),(2,'101',0,90,88,92),(3,'102',0,76,90,100),(4,'103',0,100,90,80),(5,'104',0,90,90,90),(6,'105',0,80,80,80),(7,'106',0,100,100,100);搭建完环境，那么就来实际试试看，因为 math 本身也是联合主键的一部分，那么可能会有人提出疑问：这个现象是不是和联合主键有关？
mysql> explain select * from stu where eng = '100';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | ref | idx_e | idx_e | 5 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from stu where his = '100';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | ref | idx_h | idx_h | 5 | const | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)可以看到其他两个 int 列也会用到索引，因此并没有因为联合主键有什么特殊的影响。
PS：NULL 和 NOT NULL 也有可能会被提出来是否会有影响，此处不再做更多细节的验证了。
那么是不是 MySQL 的索引真的就支持"隐式转换"的这种场景呢？再换一个场景来尝试一下。
mysql> explain select * from stu where sname = '101';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | ref | idx_s | idx_s | 66 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from stu where sname = 101;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | stu | NULL | ALL | idx_s | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)可以看到换一种方式之后，索引的使用情况和预期一致，隐式转换导致了索引失效。那么为什么之前的字符串数据查询数字列的时候可以用上索引呢？
依旧查阅官方文档，可以在类型转换的章节中找到一部分关于类型转换的介绍，摘抄部分内容如下：
In all other cases, the arguments are compared as
floating-point (double-precision) numbers.
For example, a comparison of string and numeric operands
takes place as a comparison of floating-point numbers.简单来说，就是在做对比的时候，MySQL 自身会有一套基本的规则来对应不同类型数据的比较，而字符串与数字的对比中，字符串会被转换成双精度浮点型数字之后再进行对比。
那么稍微做一些简单的测试来看看效果：
mysql> select 0='0';
+-------+
| 0='0' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select 0<'01';
+--------+
| 0<'01' |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> select 0<'1';
+-------+
| 0<'1' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select 0>'1';
+-------+
| 0>'1' |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> select 0<'0.01';
+----------+
| 0<'0.01' |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)可以看到确实如文档所描述，不管字符串内容是普通的数字还是小数，在于数字的对比中都正确的判断出来了大小关系，因此可以得出这么一个结论：
当查询条件中，使用纯数字的字符串内容来查询数字列的时候，由于 MySQL 本身类型转换的特殊性，仍旧可以使用到索引。
总结一下
对于“隐式数据类型转换会导致索引失效”这一说法在绝大多数情况下都是正确的，但是严格的来说，仍旧存在特殊情况，可能会误打误撞使用上索引。
这种“美丽的误会”虽然不会引发索引失效的问题，但是本身仍旧属于 SQL 编写不规范，很容易引发一些其他的问题，因此建议这一类问题被发现的时候能得到重视，及时修复，防患于未然。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。