上一篇讲执行器的时候说过有没有索引决定了执行器是一行行取回来自己判断还是让引擎直接定位到目标行。索引能起作用的前提只有一条条件能拿来定位。InnoDB 的索引是一棵按列值排好序的 B 树靠的就是这个顺序去缩小查找范围。只要写法破坏了这个顺序或者让某一列的值没法按原样比较索引就定位不了只能退回全表扫描。另外还有一类情况是条件本身没问题但优化器算下来觉得全表扫更便宜主动不用索引。这篇我们把这两类都整理一遍。索引列被加工了用函数或运算包住索引列-- 失效条件列被 date() 包住select*fromtwheredate(created_at)2024-01-01;-- 失效条件列做了运算select*fromtwhereid110;索引树里存的是created_at的原始值本来可以按原值直接定位。套上date()之后得把每一行的值都算一遍才知道和2024-01-01等不等树上的顺序完全用不上。改写方向是把运算挪到等号右边让条件列保持干净select*fromtwherecreated_at2024-01-01andcreated_at2024-01-02;select*fromtwhereid9;隐式类型转换-- phone 是 varchar 类型select*fromtwherephone13800000000;-- 失效MySQL 在比较字符串和数字时会把字符串转成数字。这里phone是 varchar就变成了CAST(phone AS DOUBLE) 13800000000等于给列套了一个函数和上面一种情况本质相同。反过来的方向不影响-- id 是 int 类型select*fromtwhereid10;-- 仍走索引因为字符串10被转成数字10等价于id 10列本身没被碰。所以规律是字符串列传数字会失效数字列传字符串不影响。这个坑还常出现在 join 上两张表关联的字段一个是 varchar、一个是 int关联时同样会触发转换索引也用不上。需要注意的是隐式转换不报错、也不会有警告EXPLAIN里才看得出来。参数类型和列类型保持一致是避免它的唯一办法。条件的顺序用不上索引联合索引与最左前缀联合索引(a, b, c)的排序规则是先按a排a相同再按b排b相同再按c排。所以要用上它条件必须从最左边的列开始、连续地用条件用到的索引列where a 1awhere a 1 and b 2a, bwhere a 1 and b 2 and c 3a, b, cwhere a 1 and c 3只有ac用不上where b 2用不上where c 3用不上原因还是那个顺序跳过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*fromtwherea1andb2andc3;-- c 用不上a和b能用上b是范围查询但c用不上。因为b一旦是范围在b命中的这一段里c已经不再有序了没法再拿它去定位。所以我们排联合索引的列顺序时等值查询的列放前面范围查询的列放后面。like 以 % 开头select*fromtwherenamelike%abc;-- 失效select*fromtwherenamelike%abc%;-- 失效select*fromtwherenamelikeabc%;-- 有效abc%是前缀确定能在有序的树里定位到abc开头的区间。一旦%跑到前面前缀就不定了只能挨个比较。必须做中间匹配的话得换成全文索引或者专门的搜索引擎普通 B 树索引帮不上忙。选择性差优化器主动放弃前面的情况是条件没法定位这一类不太一样条件写法是对的索引也能用但优化器算了一笔账觉得走索引更亏于是主动放弃。or 两边不都有索引-- a 有索引b 没有select*fromtwherea1orb2;or的含义是满足任意一个即可要拿到完整结果b 2那部分也得查一遍。既然b上没有索引索性整条语句全表扫描。如果a、b上都有索引MySQL 可能会用index merge分别走两个索引再合并结果EXPLAIN里type会显示index_merge。改写方式是把两个条件拆开用union或者给缺索引的那列也补上索引。否定条件select*fromtwherename!zs;select*fromtwhereidnotin(1,2,3);!、、not in、not exists这些是排除式的条件满足条件的行可能占全表的一大半。走索引意味着先扫索引、再回表回表的行数一多还不如直接顺序扫全表。所以优化器通常放弃索引——但注意这是通常不是绝对选择性高的否定条件仍有可能走索引。IS NULL 到底走不走这里我们单独说一条因为流传的说法有问题索引不存 NULL 值所以 IS NULL 不走索引是错的。InnoDB 的二级索引是存 NULL 值的MySQL 官方手册里也写明了IS NULL可以用索引和区间查找。select*fromtwhereaddressisnull;-- 可以用索引select*fromtwhereaddressisnotnull;-- 也可以用但看数据分布具体走不走还是回到优化器那笔账符合条件的行少就用索引占了大半张表就全表扫。网上那条口诀在 InnoDB 里不成立别背。优化器判断全表更划算除了上面几种还有几种常见情形会让优化器直接选全表扫描情形原因表本身很小走索引要先查索引再回表比顺序扫全表还慢列的区分度低如性别几乎每行都要读索引没起到过滤作用查的列多、命中行多每行都要回表随机 IO不如顺序扫第三点里最常见的诱因是select *。如果查询用到的列恰好都在索引里覆盖索引就省掉了回表优化器往往就愿意走索引了。前面一条 SQL 那篇里讲回表 的时候提过这一点。怎么确认到底走没走上面很多失效归根到底是优化器的取舍光看 SQL 猜不准我们直接看执行计划explainselect*fromtwherea1andb2;重点看三列列看什么type访问方式ALL就是全表扫描range、ref、eq_ref、const是由差到好key实际用到的索引NULL表示没走索引rows预估要扫描的行数越小越好type ALL或者key NULL就是没走索引。这个判断接的就是上一篇里优化器输出的那份执行计划。如果确认优化器选错了索引统计信息不准导致的可以用analyze table重新收集统计信息或者用force index强制指定但后者属于打补丁先想清楚优化器为什么不用它。速查表场景例子为什么失效列上有函数where date(t) 2024-01-01索引存的是原值算过的值没法定位列上有运算where id 1 10同上隐式类型转换varchar 列 13800000000列被隐式转成数字相当于套了函数不满足最左前缀索引(a,b)where b 1跳过了排序的第一列范围之后的列a1 and b2 and c3范围内c不再有序前导模糊like %abc前缀不定没法定位区间or 一侧无索引a 1 or b 2另一侧得全表扫索性整体全表否定条件!、not in命中行比例高回表不划算区分度低 / 表小性别列、小表走索引不划算优化器主动放弃 
长河君 · 2026/10/11 1:28:20 · 阅读 1.2k MySQL索引失效的场景总结
上一篇讲执行器的时候说过有没有索引决定了执行器是一行行取回来自己判断还是让引擎直接定位到目标行。索引能起作用的前提只有一条条件能拿来定位。InnoDB 的索引是一棵按列值排好序的 B 树靠的就是这个顺序去缩小查找范围。只要写法破坏了这个顺序或者让某一列的值没法按原样比较索引就定位不了只能退回全表扫描。另外还有一类情况是条件本身没问题但优化器算下来觉得全表扫更便宜主动不用索引。这篇我们把这两类都整理一遍。索引列被加工了用函数或运算包住索引列-- 失效条件列被 date() 包住select*fromtwheredate(created_at)2024-01-01;-- 失效条件列做了运算select*fromtwhereid110;索引树里存的是created_at的原始值本来可以按原值直接定位。套上date()之后得把每一行的值都算一遍才知道和2024-01-01等不等树上的顺序完全用不上。改写方向是把运算挪到等号右边让条件列保持干净select*fromtwherecreated_at2024-01-01andcreated_at2024-01-02;select*fromtwhereid9;隐式类型转换-- phone 是 varchar 类型select*fromtwherephone13800000000;-- 失效MySQL 在比较字符串和数字时会把字符串转成数字。这里phone是 varchar就变成了CAST(phone AS DOUBLE) 13800000000等于给列套了一个函数和上面一种情况本质相同。反过来的方向不影响-- id 是 int 类型select*fromtwhereid10;-- 仍走索引因为字符串10被转成数字10等价于id 10列本身没被碰。所以规律是字符串列传数字会失效数字列传字符串不影响。这个坑还常出现在 join 上两张表关联的字段一个是 varchar、一个是 int关联时同样会触发转换索引也用不上。需要注意的是隐式转换不报错、也不会有警告EXPLAIN里才看得出来。参数类型和列类型保持一致是避免它的唯一办法。条件的顺序用不上索引联合索引与最左前缀联合索引(a, b, c)的排序规则是先按a排a相同再按b排b相同再按c排。所以要用上它条件必须从最左边的列开始、连续地用条件用到的索引列where a 1awhere a 1 and b 2a, bwhere a 1 and b 2 and c 3a, b, cwhere a 1 and c 3只有ac用不上where b 2用不上where c 3用不上原因还是那个顺序跳过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*fromtwherea1andb2andc3;-- c 用不上a和b能用上b是范围查询但c用不上。因为b一旦是范围在b命中的这一段里c已经不再有序了没法再拿它去定位。所以我们排联合索引的列顺序时等值查询的列放前面范围查询的列放后面。like 以 % 开头select*fromtwherenamelike%abc;-- 失效select*fromtwherenamelike%abc%;-- 失效select*fromtwherenamelikeabc%;-- 有效abc%是前缀确定能在有序的树里定位到abc开头的区间。一旦%跑到前面前缀就不定了只能挨个比较。必须做中间匹配的话得换成全文索引或者专门的搜索引擎普通 B 树索引帮不上忙。选择性差优化器主动放弃前面的情况是条件没法定位这一类不太一样条件写法是对的索引也能用但优化器算了一笔账觉得走索引更亏于是主动放弃。or 两边不都有索引-- a 有索引b 没有select*fromtwherea1orb2;or的含义是满足任意一个即可要拿到完整结果b 2那部分也得查一遍。既然b上没有索引索性整条语句全表扫描。如果a、b上都有索引MySQL 可能会用index merge分别走两个索引再合并结果EXPLAIN里type会显示index_merge。改写方式是把两个条件拆开用union或者给缺索引的那列也补上索引。否定条件select*fromtwherename!zs;select*fromtwhereidnotin(1,2,3);!、、not in、not exists这些是排除式的条件满足条件的行可能占全表的一大半。走索引意味着先扫索引、再回表回表的行数一多还不如直接顺序扫全表。所以优化器通常放弃索引——但注意这是通常不是绝对选择性高的否定条件仍有可能走索引。IS NULL 到底走不走这里我们单独说一条因为流传的说法有问题索引不存 NULL 值所以 IS NULL 不走索引是错的。InnoDB 的二级索引是存 NULL 值的MySQL 官方手册里也写明了IS NULL可以用索引和区间查找。select*fromtwhereaddressisnull;-- 可以用索引select*fromtwhereaddressisnotnull;-- 也可以用但看数据分布具体走不走还是回到优化器那笔账符合条件的行少就用索引占了大半张表就全表扫。网上那条口诀在 InnoDB 里不成立别背。优化器判断全表更划算除了上面几种还有几种常见情形会让优化器直接选全表扫描情形原因表本身很小走索引要先查索引再回表比顺序扫全表还慢列的区分度低如性别几乎每行都要读索引没起到过滤作用查的列多、命中行多每行都要回表随机 IO不如顺序扫第三点里最常见的诱因是select *。如果查询用到的列恰好都在索引里覆盖索引就省掉了回表优化器往往就愿意走索引了。前面一条 SQL 那篇里讲回表 的时候提过这一点。怎么确认到底走没走上面很多失效归根到底是优化器的取舍光看 SQL 猜不准我们直接看执行计划explainselect*fromtwherea1andb2;重点看三列列看什么type访问方式ALL就是全表扫描range、ref、eq_ref、const是由差到好key实际用到的索引NULL表示没走索引rows预估要扫描的行数越小越好type ALL或者key NULL就是没走索引。这个判断接的就是上一篇里优化器输出的那份执行计划。如果确认优化器选错了索引统计信息不准导致的可以用analyze table重新收集统计信息或者用force index强制指定但后者属于打补丁先想清楚优化器为什么不用它。速查表场景例子为什么失效列上有函数where date(t) 2024-01-01索引存的是原值算过的值没法定位列上有运算where id 1 10同上隐式类型转换varchar 列 13800000000列被隐式转成数字相当于套了函数不满足最左前缀索引(a,b)where b 1跳过了排序的第一列范围之后的列a1 and b2 and c3范围内c不再有序前导模糊like %abc前缀不定没法定位区间or 一侧无索引a 1 or b 2另一侧得全表扫索性整体全表否定条件!、not in命中行比例高回表不划算区分度低 / 表小性别列、小表走索引不划算优化器主动放弃