背景
作为大多数 MySQL DBA 都有的常识，当 MySQL 的查询中出现隐式数据类型转换，比如 int 类型的列使用字符串类型的内容作为查询条件时，会出现索引失效的问题，导致查询可能会变成全表扫描，导致数据库出现性能问题，影响业务。
问题描述
本着上面描述的常识，在做一次慢查询优化的工作当中，发现了一个比较奇怪的现象，此现象在测试环境可以构造和重现，效果如下：
代码语言：txt复制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 也可以，效果是一样的。
代码语言：txt复制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 本身也是联合主键的一部分，那么可能会有人提出疑问：这个现象是不是和联合主键有关？
代码语言：txt复制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 的索引真的就支持"隐式转换"的这种场景呢？再换一个场景来尝试一下。
代码语言：txt复制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)可以看到换一种方式之后，索引的使用情况和预期一致，隐式转换导致了索引失效。那么为什么之前的字符串数据查询数字列的时候可以用上索引呢？
依旧查阅官方文档，可以在类型转换的章节中找到一部分关于类型转换的介绍，摘抄部分内容如下：
代码语言：txt复制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 自身会有一套基本的规则来对应不同类型数据的比较，而字符串与数字的对比中，字符串会被转换成双精度浮点型数字之后再进行对比。
那么稍微做一些简单的测试来看看效果：
代码语言：txt复制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 编写不规范，很容易引发一些其他的问题，因此建议这一类问题被发现的时候能得到重视，及时修复，防患于未然。
