MySQL 索引失效的 12 宗罪：一条 SQL 是怎样把索引踢出执行计划的？

“明明建了索引，为什么 EXPLAIN 说没用？”
如果你曾在凌晨 3 点对着监控曲线怀疑人生，这篇文章就是为你写的。
下文将带你深入 MySQL 优化器、存储引擎与执行器三界，用 12 个真实场景复盘“索引失效”的全链路原因，并给出可落地的诊断与修复方案。读完即可建立自己的“索引失效排查 SOP”。

---

一、先建立统一语言：如何判断“没有命中索引”

1. 拿到执行计划

   EXPLAIN FORMAT=JSON SELECT …\G
   

2. 看 key 列
   • NULL：优化器认为无可用索引
   • 有值但 rows 很大：用了索引但过滤性几乎为零（半失效）
3. 再看 type 列
   • ALL 全表扫描 → 100% 失效
   • index 全索引扫描 → 比 ALL 略好，仍是失效
   • range/ref/eq_ref → 正常命中
4. 用 optimizer_trace 翻优化器黑盒

   SET optimizer_trace="enabled=on";
   SELECT …;
   SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
   

---

二、12 宗罪全景图

编号	失效场景	责任方	快速识别	修复手段
1	对列做运算/函数	开发者	WHERE DATE(create_time)=…	改写成范围
2	隐式类型转换	开发者/DBA	varchar_col=123	保持类型一致
3	左前缀中断	开发者	LIKE '%abc'	改右模糊或 ES
4	OR 条件跨列	开发者	a=1 OR b=2	拆 UNION
5	范围列后断复合索引	开发者	(a,b,c) 中 a>… 后 b 不可用	调整顺序
6	回表成本过大	优化器	SELECT * 且过滤性低	覆盖索引
7	统计信息失真	优化器	SHOW INDEX Cardinality≈1	ANALYZE
8	数据分布倾斜	数据	某值占 90%	加提示或过滤
9	索引失效于 NULL	存储引擎	IS NULL 走全表	改可空策略
10	锁冲突导致优化器退缩	并发	FOR UPDATE 全表锁	拆分事务
11	小表全表更快	优化器	rows<~20	忽略即可
12	参数嗅探/查询缓存	应用层	同 SQL 不同参数	强制 plan

下面逐条展开。

---

三、详细复盘

宗罪 1：在索引列上“包一层函数”

-- 例：求当天订单
SELECT * FROM orders WHERE DATE(create_time) = CURDATE();

• 原理：B+ 树存储的是原始值，对列做运算后无法走树搜索。
• 诊断：EXPLAIN 的 key=NULL，Extra=Using where。
• 修复：

  WHERE create_time >= CURDATE()
    AND create_time < CURDATE()+INTERVAL 1 DAY;
  

宗罪 2：隐式类型转换（字符串→数字）

-- 表：phone char(11) PK
SELECT * FROM user WHERE phone = 13800138000;

• 原理：字符串列收到数字参数，MySQL 会隐式 CAST(phone AS DECIMAL)，等价于对列加函数。
• 诊断：SHOW WARNINGS 会出现 Truncated wrong DOUBLE value。
• 修复：参数加引号。

宗罪 3：LIKE 左模糊或两端模糊

SELECT * FROM sku WHERE name LIKE '%iPhone%';

• 原理：B+ 树只能利用“最左前缀”。
• 修复：
  • 业务改为“右模糊” iPhone%
  • 或走全文索引（FULLTEXT）/ES
  • 或生成列 reverse_name 并建索引，再 WHERE reverse_name LIKE REVERSE('%iPhone')

宗罪 4：OR 跨不同列

SELECT * FROM t WHERE a=1 OR b=2;

• 原理：MySQL 5.7 以前只能用一个索引，优化器选择“合并”或“全表”。
• 修复：

  SELECT * FROM t WHERE a=1
  UNION
  SELECT * FROM t WHERE b=2;
  

  或 5.7 开启 index_merge+union 并分别建 (a)、(b) 单列索引。

宗罪 5：复合索引遇到“范围”后断裂

-- 索引 (a,b,c)
SELECT * FROM t WHERE a>100 AND b=1 AND c=2;

• 原理：a>100 是范围，b、c 在 B+ 树层面已无序，只能用到 a。
• 修复：把过滤性最高的列放最左；或把范围条件改为 IN()（等值）。

宗罪 6：回表成本 > 全表成本

-- 表 1 亿行，idx(status)，status 只有 0/1
SELECT * FROM t WHERE status=1;

• 原理：二级索引→主键→回表，随机 IO 放大；优化器估算 rows=5kw 直接选 ALL。
• 修复：
  • 只查需要的列（覆盖索引）
  • 或把状态列拆成枚举分区/分表
  • 或加 FORCE INDEX 提示（谨慎）

宗罪 7：统计信息过期

ANALYZE TABLE t;  -- 一键治愈

• 场景：大量 DELETE 后 Cardinality 掉到 1，优化器认为“全表差不多”。
• 诊断：

  SHOW INDEX FROM t\G  -- 看 Cardinality
  SELECT * FROM mysql.innodb_index_stats WHERE table_name='t';
  

• 修复：定期 ANALYZE，或开启 innodb_stats_auto_recalc=1。

宗罪 8：数据倾斜——“热点值”

-- 索引 (city)，90% 订单在 "Shanghai"
SELECT * FROM orders WHERE city='Shanghai' LIMIT 10;

• 原理：MySQL 5.7 没有直方图，按平均分布估算，发现“回表 90%”→全表。
• 修复：
  • 8.0 建直方图 ANALYZE TABLE orders UPDATE HISTOGRAM ON city;
  • 或业务层加 AND create_time>... 缩小区间
  • 或拆分区表

宗罪 9：索引列允许 NULL

-- 索引 (expire_time)
SELECT * FROM coupon WHERE expire_time IS NULL;

• 原理：InnoDB 把 NULL 放在 B+ 树最小端，范围太大；优化器选 ALL。
• 修复：设计时尽量 NOT NULL DEFAULT '1970-01-01' 并建伪值索引。

宗罪 10：锁冲突导致优化器退缩

BEGIN;
SELECT * FROM t WHERE a=1 FOR UPDATE;
-- 同时另一会话
SELECT * FROM t WHERE a=2 FOR UPDATE;

• 场景：大量行锁等待，优化器为减少锁范围直接升级表锁→全表。
• 修复：拆分事务、降低隔离级别、用 SELECT ... LOCK IN SHARE MODE 或乐观锁。

宗罪 11：小表全表更快

• 原理：< ~20 行，优化器估算全表顺序读一次完成。
• 处理：忽略即可，命中索引反而慢。

宗罪 12：参数嗅探（Plan Cache）

-- 预编译语句
SELECT * FROM t WHERE status=? ;

• 场景：第一次传 status=0（0 占 99%），Plan Cache 把“全表”缓存下来，后续传 status=2 也复用。
• 修复：
  • 8.0 已取消 Query Cache，可开 histogram+reanalyze
  • 或 FORCE INDEX
  • 或 SQL 层拆分两条语句

---

四、一张思维导图（文字版）

EXPLAIN 显示未走索引
├─ 开发者层
│  ├─ 列运算/函数
│  ├─ 隐式转换
│  ├─ LIKE '%xx'
│  ├─ OR 跨列
│  └─ 复合索引断档
├─ 数据层
│  ├─ 倾斜热点
│  ├─ NULL 过多
│  └─ 小表
├─ 优化器层
│  ├─ 统计信息旧
│  ├─ 回表成本高
│  └─ 锁退缩
└─ 应用层
   ├─ 参数嗅探
   └─ 预编译复用坏计划

---

五、10 分钟定位 SOP（checklist）

1. 跑 EXPLAIN + type=ALL → 确认全表
2. 跑 SHOW WARNINGS → 看隐式转换
3. 跑 SHOW INDEX → 看 Cardinality
4. 跑 ANALYZE → 刷新统计信息
5. 跑 OPTIMIZER_TRACE → 看优化器怎么算 cost
6. 检查 SQL 是否有 DATE()、LIKE '%'、OR、IS NULL
7. 检查表行数 < 20？
8. 检查最左前缀是否断裂
9. 检查是否需要覆盖索引
10. 检查是否锁等待/长事务

---

六、总结一句话

索引不是“建了就永远有用”，它会在统计信息、数据分布、查询写法、锁冲突、成本估算等多条链路被“踢出”执行计划。
掌握 12 宗罪 + 10 分钟 SOP，你就能在凌晨 3 点快速说出：“别慌，是第 7 条，ANALYZE 一下就好。”

把这张 checklist 贴在工位，下次 DBA 说“没走索引”时，你可以自信地回复：“我帮你把 12 条都扫了一遍，是第 3 条，LIKE 左模糊，已改右模糊，QPS 提升 37 倍。”