MySQL索引失效有哪些原因？常见错误与执行计划分析

给字段建了索引，查询却还是很慢，是很多人都会遇到的困惑。明明 WHERE user_id = 123 上就有索引，可 EXPLAIN 一看，type 是 ALL，也就是全表扫描，索引没被用上。这种情况我们通常说"索引失效"。严格来说并不是索引消失了，而是优化器认为这条 SQL 走索引不划算，或者写法的限制让它根本用不了索引。

这篇文章把索引失效的常见原因梳理一遍，从"索引列上做运算"到"统计信息过期"，每一类都配上可复现的例子、EXPLAIN 的关键列怎么读，以及修复方法。看懂之后，遇到慢查询可以先自己判断到底是哪种问题，而不是一味加索引。文中的执行计划输出是基于常见 MySQL 5.7 与 8.0 版本的示意，实际列名和取值以自己环境为准。

先学会看 EXPLAIN

在排查之前，得先知道怎么看执行计划。最常用的就是 EXPLAIN，MySQL 8.0 还提供了 EXPLAIN ANALYZE 可以看到实际执行耗时。

# 基本用法，只需要在 SQL 前面加 EXPLAIN
mysql -uroot -p -e "EXPLAIN SELECT * FROM orders WHERE user_id = 123\G"

# MySQL 8.0 可以看真实执行信息（会真正执行语句）
mysql -uroot -p -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123\G"

# 查看表上的索引
mysql -uroot -p -e "SHOW INDEX FROM orders\G"

EXPLAIN 里最需要关注的几列含义如下。

type 的效率从好到差大致是：system > const > eq_ref > ref > range > index > ALL。看到 ALL 基本可以确定是全表扫描，看到 index 表示扫描了整个索引树。

索引列上做了函数或运算

这是最常见的失效原因。只要在 WHERE 的索引列上套了函数、做了运算，索引通常就用不了。

-- 失效：对索引列 created_at 做了 DATE() 函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-01';

-- 改写：改成范围查询，索引可用
SELECT * FROM orders
WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at <  '2026-03-02 00:00:00';

-- 失效：索引列参与运算
SELECT * FROM orders WHERE total_price + 10 > 200;

-- 改写：把运算移到右边
SELECT * FROM orders WHERE total_price > 190;

原理很简单：B+ 树索引里存的是列的原值，一旦对列做函数变换，每个值都会变形，索引的有序性就不成立了，优化器无法用它定位。修复办法是保持列"干净"，把函数或运算挪到常量一侧。

隐式类型转换

字段类型和查询值的类型不一致时，MySQL 会尝试转换，一旦把索引列转成其他类型，索引同样失效。

-- 假设 phone 是 VARCHAR(20)
-- 失效：传入数字，MySQL 把 phone 转成数字再比较
SELECT * FROM users WHERE phone = 13800000000;

-- 正确：用字符串去匹配
SELECT * FROM users WHERE phone = '13800000000';

这类问题在字符串字段存数字时特别容易发生。整数转字符串通常不会破坏索引（因为是常量转换），而字符串列遇到数字常量时，往往变成"列转数字"，索引就废了。排查时可以用 EXPLAIN 的 key 为 NULL 来发现，配合 SHOW WARNINGS 看优化器改写后的语句：

mysql -uroot -p -e "EXPLAIN SELECT * FROM users WHERE phone = 13800000000\G"
mysql -uroot -p -e "SHOW WARNINGS\G"

以百分号开头的 LIKE

LIKE 的前置通配符会让索引无法定位，只能全表扫描。

-- 失效：以 % 开头
SELECT * FROM products WHERE name LIKE '%手机%';

-- 可用：前缀匹配
SELECT * FROM products WHERE name LIKE '手机%';

如果业务确实需要模糊的中缀或后缀匹配，可以考虑全文索引（FULLTEXT）、外部搜索引擎，或者专门做一张倒排/分词表。不要指望普通 B+ 树索引能解决中缀匹配。

OR 连接的字段没有索引

OR 两侧的条件如果有一侧没有索引，整个查询就可能退化为全表扫描。

-- 假设 user_id 有索引，status 没有索引
-- 可能失效
SELECT * FROM orders WHERE user_id = 123 OR status = 1;

-- 改写一：给 status 也建索引后，用 union 明确分开
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE status = 1;

-- 改写二：如果确实要合并，给两个列都建索引让优化器用 index merge

另一种情况是"能走索引却不划算"。如果 OR 两侧命中的行占总行数比例过高（经验上超过百分之二三十），优化器会认为走索引再回表还不如直接全表扫描，于是主动放弃索引。

不满足最左前缀原则

联合索引 (a, b, c) 的匹配必须从最左列开始连续匹配。

-- 索引：(user_id, status, created_at)

-- 可用：从最左列开始
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 2;
SELECT * FROM orders WHERE user_id = 1 AND status = 2 AND created_at > '2026-01-01';

-- 部分可用：跳过中间列，只能用上 user_id
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01';

-- 失效：没有从最左列开始
SELECT * FROM orders WHERE status = 2;
SELECT * FROM orders WHERE created_at > '2026-01-01';

还有一种容易忽略的情况：范围查询会中断后续列的索引使用。在 (a, b, c) 上，WHERE a = 1 AND b > 5 AND c = 3 里，c 用不上索引，因为 b 是范围。这时可以考虑调整索引顺序，或改写为等值条件。

范围条件和不等号

!=、<>、NOT IN 这类否定条件通常只能走范围扫描，效率不高，甚至直接全表扫描。

-- 效率低：否定条件，选择性差的概率大
SELECT * FROM orders WHERE status != 2;

-- 如果"非 2"的行数很少，可以枚举有效值
SELECT * FROM orders WHERE status IN (0, 1, 3, 4);

IS NULL 和 IS NOT NULL 也常被误解。实际上 MySQL 的索引是可以存 NULL 值的，IS NULL 可以使用索引；真正的问题在于当 NULL 行占比很高时，优化器觉得用索引不划算，于是选择全表扫描。要判断是不是这个原因，可以先用 EXPLAIN 看 rows 的预估行数，如果它接近表的总行数，说明条件几乎没有过滤能力，走不走索引都慢，此时更该考虑的是业务上能不能改用其他条件，或者降低 NULL 值的比例。

同一个道理适用于所有"选择性差"的条件。索引真正有价值的前提是条件能把行数大幅缩小，比如从一百万行筛到几百行。如果一个条件筛完之后还剩几十万行，就算走了索引，回表读几十万次的开销也可能超过全表扫描，优化器放弃索引其实是理性的。所以看到索引失效，先别急着加索引或 FORCE INDEX，而是想想这个条件的区分度是不是本来就不够。

统计信息过期

优化器是根据统计信息来估算"走索引要读多少行、全表扫描要读多少行"的，统计信息不准，就可能选错执行计划。

# 查看统计信息
mysql -uroot -p -e "SHOW INDEX FROM orders\G"

# 手动更新（MySQL 8.0）
mysql -uroot -p -e "ANALYZE TABLE orders;"

# 查看优化器开关，定位是否被某些参数影响
mysql -uroot -p -e "SELECT @@optimizer_switch\G"

当表发生大量增删改后，统计信息可能滞后，导致优化器误判。对写入频繁的大表，可以在业务低峰期定期执行 ANALYZE TABLE。需要注意的是，ANALYZE TABLE 会短暂加锁，在线大表要评估影响，尽量避开高峰。

索引列参与比较但类型不一致

除了前面说的隐式类型转换，还有一种情况是两个表的连接列类型不同，或者列与列直接比较。

-- 连接条件类型不一致，可能无法高效用索引
SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_code;  -- 一个 INT 一个 VARCHAR

-- 列与列比较，索引通常帮不上忙
SELECT * FROM t WHERE col_a > col_b;

修正方法是统一表结构设计，让关联列类型、字符集、排序规则都一致。

常见失效原因速查表

常见问题

建了索引就一定会走索引吗？

不一定。优化器会根据成本估算选择执行计划，如果走索引要回表读的行太多，它可能直接全表扫描。选择性低的列即使建了索引也可能不被使用。

为什么同一条 SQL 昨天走索引今天不走了？

常见原因是数据量变化导致统计信息改变，或者优化器对成本估算发生了变化。可以对比两次的 EXPLAIN，并对表执行 ANALYZE TABLE 后观察是否恢复。

force index 能解决问题吗？

FORCE INDEX 能强制指定索引，但它是一种有副作用的兜底手段。数据分布变化后强制索引可能反而更慢，优先还是找出失效的根本原因并修正写法或索引。

联合索引的列顺序怎么定？

一般把等值条件中区分度高的列放在前面，范围条件列放在最后。同时要考虑哪些查询最频繁，让最常用的查询能完整命中索引前缀。

加了索引反而更慢是怎么回事？

写入时需要维护索引，索引越多，插入、更新和删除的开销越大。另外索引占用缓冲池空间，可能挤掉数据页缓存。所以索引要按查询需求精简，不是越多越好。

总结

• 索引失效的本质是"用不了"或"用得不划算"，前者是写法问题，后者是优化器成本选择。
• 在索引列上套函数、做运算、隐式类型转换、前置百分号 LIKE 是最常见的几类失效原因。
• 联合索引要遵守最左前缀，范围条件会中断后续列的使用，索引列顺序要按查询模式设计。
• EXPLAIN 的 type、key、rows、Extra 是关键信号，type=ALL、key=NULL 基本可判定没走索引。
• 统计信息过期会让优化器选错执行计划，对写入频繁的大表要定期 ANALYZE TABLE。
• 修复顺序是：先改写法，再调索引，FORCE INDEX 只作为最后手段，并持续用监控验证效果。