为何MySQL查询时未使用基于varchar字段的c_no索引？

问题背景 #

创建表的SQL：

create table test (
    id    int primary key auto_increment,
    c_no  varchar(11),
    c2    varchar(2),
    
    key c_no(c_no)
);

执行的查询语句：

select
    *
from
    test
where
    c_no = 100

使用explain分析的结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test | NULL       | ALL  | c_no          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

原因分析 #

• 隐式类型转换导致索引失效：c_no字段是varchar字符串类型，但查询条件中使用了数值100。MySQL会对每一行的c_no值执行隐式类型转换，将字符串转为数值后再与100比较。这个转换操作破坏了索引的可用性——索引是基于字符串的存储顺序构建的，转换后的数值无法匹配索引的结构，因此MySQL无法直接使用c_no上的索引。
• 小数据量的优化选择：从explain结果可以看到表中仅7行数据，MySQL优化器会判定全表扫描的成本远低于走索引的成本，因此即使索引理论上可用，也会选择全表扫描的执行计划。

解决方法 #

将查询条件中的数值改为字符串形式，避免隐式类型转换：

select
    *
from
    test
where
    c_no = '100'

此时再执行explain，就能看到MySQL使用c_no索引进行查询。

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