MySQL索引失效的场景总结

上一篇讲执行器的时候说过，有没有索引，决定了执行器是"一行行取回来自己判断"还是"让引擎直接定位到目标行"。索引能起作用的前提只有一条：条件能拿来定位。

InnoDB 的索引是一棵按列值排好序的 B+ 树，靠的就是这个顺序去缩小查找范围。只要写法破坏了这个顺序，或者让某一列的值没法按原样比较，索引就定位不了，只能退回全表扫描。另外还有一类情况是条件本身没问题，但优化器算下来觉得全表扫更便宜，主动不用索引。这篇我们把这两类都整理一遍。

索引列被加工了

用函数或运算包住索引列

— 失效：条件列被 date() 包住
select * from t where date(created_at) = '2024-01-01';

— 失效：条件列做了运算
select * from t where id + 1 = 10;

索引树里存的是 created_at 的原始值，本来可以按原值直接定位。套上 date() 之后，得把每一行的值都算一遍才知道和 '2024-01-01' 等不等，树上的顺序完全用不上。

改写方向是把运算挪到等号右边，让条件列保持"干净"：

select * from t where created_at >= '2024-01-01' and created_at < '2024-01-02';
select * from t where id = 9;

隐式类型转换

— phone 是 varchar 类型
select * from t where phone = 13800000000; — 失效

MySQL 在比较字符串和数字时，会把字符串转成数字。这里 phone 是 varchar，就变成了 CAST(phone AS DOUBLE) = 13800000000，等于给列套了一个函数，和上面一种情况本质相同。

反过来的方向不影响：

— id 是 int 类型
select * from t where id = '10'; — 仍走索引

因为字符串 '10' 被转成数字 10，等价于 id = 10，列本身没被碰。所以规律是：字符串列传数字会失效，数字列传字符串不影响。

这个坑还常出现在 join 上：两张表关联的字段一个是 varchar、一个是 int，关联时同样会触发转换，索引也用不上。

需要注意的是，隐式转换不报错、也不会有警告，EXPLAIN 里才看得出来。参数类型和列类型保持一致，是避免它的唯一办法。

条件的顺序用不上索引

联合索引与最左前缀

联合索引 (a, b, c) 的排序规则是：先按 a 排，a 相同再按 b 排，b 相同再按 c 排。所以要用上它，条件必须从最左边的列开始、连续地用：

原因还是那个顺序：跳过 a 就没法定位，b、c 单独的排序在整棵树里是乱的。

注意条件的书写顺序无所谓，优化器会自动调整成最左前缀的顺序，where b = 2 and a = 1 和 where a = 1 and b = 2 效果一样。关键是用到的是不是最左边那连续几列。

另外，MySQL 8.0 引入了 skip scan，在 a 的取值种类很少（区分度极低）时，会尝试跳过 a 直接按 b 找。这是特定条件下的特例，不能依赖。

范围条件之后的列

— 索引 (a, b, c)
select * from t where a = 1 and b > 2 and c = 3; — c 用不上

a 和 b 能用上（b 是范围查询），但 c 用不上。因为 b 一旦是范围，在 b 命中的这一段里，c 已经不再有序了，没法再拿它去定位。

所以我们排联合索引的列顺序时，等值查询的列放前面，范围查询的列放后面。

like 以 % 开头

select * from t where name like '%abc'; — 失效
select * from t where name like '%abc%'; — 失效
select * from t where name like 'abc%'; — 有效

abc% 是前缀确定，能在有序的树里定位到 abc 开头的区间。一旦 % 跑到前面，前缀就不定了，只能挨个比较。必须做中间匹配的话，得换成全文索引或者专门的搜索引擎，普通 B+ 树索引帮不上忙。

选择性差，优化器主动放弃

前面的情况是"条件没法定位"，这一类不太一样：条件写法是对的，索引也能用，但优化器算了一笔账，觉得走索引更亏，于是主动放弃。

or 两边不都有索引

— a 有索引，b 没有
select * from t where a = 1 or b = 2;

or 的含义是"满足任意一个即可"，要拿到完整结果，b = 2 那部分也得查一遍。既然 b 上没有索引，索性整条语句全表扫描。

如果 a、b 上都有索引，MySQL 可能会用 index merge，分别走两个索引再合并结果，EXPLAIN 里 type 会显示 index_merge。改写方式是把两个条件拆开用 union，或者给缺索引的那列也补上索引。

否定条件

select * from t where name != 'zs';
select * from t where id not in (1, 2, 3);

!=、<>、not in、not exists 这些是"排除式"的条件，满足条件的行可能占全表的一大半。走索引意味着先扫索引、再回表，回表的行数一多，还不如直接顺序扫全表。所以优化器通常放弃索引——但注意这是"通常"，不是绝对，选择性高的否定条件仍有可能走索引。

IS NULL 到底走不走

这里我们单独说一条，因为流传的说法有问题："索引不存 NULL 值，所以 IS NULL 不走索引"是错的。InnoDB 的二级索引是存 NULL 值的，MySQL 官方手册里也写明了 IS NULL 可以用索引和区间查找。

select * from t where address is null; — 可以用索引
select * from t where address is not null; — 也可以用，但看数据分布

具体走不走，还是回到优化器那笔账：符合条件的行少，就用索引；占了大半张表，就全表扫。网上那条口诀在 InnoDB 里不成立，别背。

优化器判断全表更划算

除了上面几种，还有几种常见情形会让优化器直接选全表扫描：

第三点里最常见的诱因是 select *。如果查询用到的列恰好都在索引里（覆盖索引），就省掉了回表，优化器往往就愿意走索引了。前面一条 SQL 那篇里讲回表 的时候提过这一点。

怎么确认到底走没走

上面很多"失效"归根到底是优化器的取舍，光看 SQL 猜不准，我们直接看执行计划：

explain select * from t where a = 1 and b > 2;

重点看三列：

type = ALL 或者 key = NULL，就是没走索引。这个判断接的就是上一篇里优化器输出的那份执行计划。

如果确认优化器选错了索引（统计信息不准导致的），可以用 analyze table 重新收集统计信息，或者用 force index 强制指定，但后者属于打补丁，先想清楚优化器为什么不用它。

速查表