极客工场

Appearance

共 926 字预计阅读约 3 分钟

MySQL 索引为什么失效？ ​

更新时间：2026-09-18。问题驱动短答页。「明明建了索引为什么还全表扫」是高频面试题也是线上真坑。判断方法统一是 EXPLAIN 看 key 是否为 NULL、type 是否 ALL。

一句话回答 ​

索引失效本质是：你的查询条件让 B+ 树无法按有序键定位（对列做了运算/函数、破坏最左前缀、前导模糊匹配），或者优化器估算后认为走索引还不如全表扫（回表太多、区分度太低）。

常见失效场景 ​

场景反例（不走索引）正确写法
列上做函数/运算WHERE YEAR(ctime)=2026、WHERE age+1=20WHERE ctime BETWEEN '2026-01-01' AND ...，把计算挪到等号右边/常量
隐式类型转换字符串列传数字 WHERE phone=138...类型对齐：WHERE phone='138...'
前导通配 LIKEWHERE name LIKE '%张'前置可匹配：LIKE '张%'；含前后模糊用全文/搜索引擎
违反最左前缀索引 (a,b,c) 却只查 b/c查询列从索引最左列开始连续用
OR 含无索引列WHERE a=1 OR d=2（d 无索引）给 d 也建索引，或改 UNION ALL
隐式字符集转换两表字符集/排序规则不同做 JOIN统一字符集与 collation
优化器弃用区分度极低且要回表大量行看统计信息，必要时覆盖索引

联合索引 (a,b,c) 的最左前缀：能用 → a、a,b、a,b,c；不能用 → b、c、b,c。跳过中间列会导致后面的列只能做索引内过滤而非精确定位（key_len 可看实际用了几列）。

两个容易误判的点 ​

  1. 「范围之后全失效」：WHERE a=? AND b>? AND c=? 中，b 是范围后，c 通常无法再用于定位（但覆盖索引时仍可能在索引内过滤）。建索引时把等值列放前、范围列放后。
  2. 不走索引不一定是 bug：当查询要返回表中大部分行时，走二级索引再大量回表比顺序全表扫更慢，优化器正确地选择了全表扫。小表也常被直接全表扫。

排查与修法 ​

sql
EXPLAIN SELECT ...;        -- key=NULL/type=ALL 即没走索引
SHOW WARNINGS;             -- EXPLAIN 后执行，能看到隐式转换等改写提示
ANALYZE TABLE t;           -- 统计信息过旧时刷新，帮优化器做正确选择

修法：满足最左前缀、条件列保持「裸列」、字符串列传引号、前后模糊交给全文索引或 ES、用覆盖索引避免回表；MySQL 8 还可用函数索引（CREATE INDEX ... ON t((DATE(ctime)))）正面支持函数条件。

坑 ​

  • 以为「建了联合索引，随便哪列都能走」：只查非最左列仍会全表扫。
  • varchar 列传数字：MySQL 会把字符串隐式转数字，等于在列上套函数，索引必失效（数字列传字符串通常没事，别记反）。
  • 改完不验证：用 EXPLAIN 确认 key 真的变了、rows 真的降了，而不是想当然。

执行计划各列含义见 MySQL EXPLAIN 深挖；索引为什么用 B+ 树见 MySQL 索引原理；大 OFFSET 的慢查询见 深分页优化。

总结 ​

MySQL 索引失效要么是条件破坏了 B+ 树的有序定位——列上套函数/运算、字符串列传数字的隐式转换、LIKE '%x' 前导通配、跳过联合索引最左列、OR 带无索引列、跨字符集 JOIN；要么是优化器认为回表太多/区分度太低而主动选全表扫；排查固定用 EXPLAIN 看 key 和 type、用 SHOW WARNINGS 看隐式改写，修法是保持裸列、对齐类型与 collation、遵守最左前缀、必要时用覆盖索引或函数索引，改完务必再 EXPLAIN 验证。