MySQL中为何int列匹配字符串能用索引，字符串列匹配int却不能？

1 创建数据表tb1 #

create table tb1 (t1 int not null, t2 varchar(20) not null);

2 为t1、t2添加索引 #

CREATE INDEX index_t1 ON tb1(t1);
CREATE INDEX index_t2 ON tb1(t2);

3 使用explain测试索引 #

3.1 explain select * from tb1 where t1 = 1\G #

3.2 explain select * from tb1 where t1 = '1'\G #

3.3 explain select * from tb1 where t2 = '1'\G #

3.4 explain select * from tb1 where t2 = 1\G #

测试现象总结 #

• t1为int类型，匹配int值1，3.1可正常使用索引
• t1为int类型，匹配字符串'1'，3.2仍可使用索引
• t2为字符串类型，匹配字符串'1'，3.3可正常使用索引
• t2为字符串类型，匹配int值1，3.4无法使用索引

问题解答：为何3.1可以使用索引，而3.4却无法使用？ #

核心原因是MySQL的隐式类型转换规则在两种场景下的表现完全不同：

• 对于3.1的t1 = 1：
  t1是int类型，查询条件里的1也是int类型，类型完全匹配，数据库直接拿着这个值去t1的B+树索引里做等值查找，索引自然能正常生效。

• 辅助理解3.2的t1 = '1'：
  这里MySQL会自动把字符串'1'转换成int类型的1，再和t1的值对比。这个转换只针对查询条件里的常量，转换后的结果依然能匹配索引的结构，所以索引依然可以使用。

• 对于3.4的t2 = 1：
  t2是varchar字符串类型，查询条件里的1是int类型。此时MySQL会把表中所有t2字段的值都转换成int类型，再和常量1比较。这就要求数据库必须全表扫描每一行，对t2的值做类型转换后再判断，完全用不上t2上的索引——因为t2的索引是按字符串字典序排序的，转成int后的顺序和原索引顺序完全不匹配，索引的排序查找优势发挥不了，自然就失效了。

简单总结：当MySQL只需要转换查询条件的常量时，不影响索引使用；但需要转换表字段的值时，会触发全表扫描，索引直接失效。

内容的提问来源于stack exchange，提问作者Yunbin Liu