MySQL 索引失效的 10 种场景与 EXPLAIN 实战

索引失效这件事，本质上不是"MySQL 有 bug"，而是优化器判断"用索引比全表扫更贵"，于是主动放弃了索引。理解这句话，比背十条规则有用得多。

下面这 10 个场景都来自真实排查，每条给出可复现的 SQL 和原因。先说清楚 EXPLAIN 怎么读。

一、EXPLAIN 该看哪几列

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

输出十几列，实际只需要盯这 5 个：

type = ALL 或者 key = NULL 是明确的信号。但要注意：type = index 也是全索引扫描，跟全表扫描一样读所有行，只是走索引顺序读，别把它当好指标。

二、10 个失效场景

准备一张典型表：

CREATE TABLE `orders` (
  `id`          bigint       NOT NULL AUTO_INCREMENT,
  `order_no`    varchar(32)  NOT NULL,
  `user_id`     bigint       NOT NULL,
  `status`      tinyint      NOT NULL DEFAULT 0,
  `amount`      decimal(10,2) NOT NULL,
  `remark`      varchar(255) DEFAULT NULL,
  `created_at`  datetime     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB;

场景 1：隐式类型转换

这是最隐蔽、也最常见的一个。

-- order_no 是 varchar，却用数字比较
SELECT * FROM orders WHERE order_no = 20231105001;

key 直接变成 NULL。原因是 MySQL 在比较 varchar 和数字时会把列转成数字再比——CAST(order_no AS DOUBLE)，一旦对列做函数运算，索引顺序就废了。

反向的情况（数字列用字符串比较）不会失效，MySQL 会转字符串常量：

-- user_id 是 bigint，传字符串，索引仍然有效
SELECT * FROM orders WHERE user_id = '100';

判断依据：看表结构和 Java 实体类型是否一致。这类问题多出现在"接口传参是 String，数据库是 bigint"或者反过来。

场景 2：在索引列上做运算

-- 错
SELECT * FROM orders WHERE DATE(created_at) = '2023-11-05';
SELECT * FROM orders WHERE id + 1 = 100;
SELECT * FROM orders WHERE amount * 2 > 200;

三句全表扫。改成对常量做运算：

-- 对
SELECT * FROM orders WHERE created_at >= '2023-11-05 00:00:00'
                       AND created_at <  '2023-11-06 00:00:00';
SELECT * FROM orders WHERE id = 99;
SELECT * FROM orders WHERE amount > 100;

时间范围查询这个改写特别值得记住——DATE(created_at) = ? 是博客、报表系统里出现频率最高的索引杀手，而且改写成本极低。

场景 3：前导模糊匹配

SELECT * FROM orders WHERE order_no LIKE '%2023%';    -- 全表扫
SELECT * FROM orders WHERE order_no LIKE '2023%';     -- 走 range，OK

LIKE 的通配符在开头时，B+ 树无法定位起点，只能从头扫。

真要做全文模糊搜索，方案有三个：

• 数据量小（几万行）：接受全表扫，或者加个 ES
• 需要前缀+后缀：建反向索引列，或者 MySQL 8.0 的函数索引
• 需要分词：上全文索引 FULLTEXT 或外部搜索引擎

场景 4：OR 连接了未索引列

-- status 有索引（在复合索引第二列），remark 没有
SELECT * FROM orders WHERE status = 1 OR remark LIKE '%紧急%';

优化器判断：要满足这个 OR，必须两边的行都扫一遍，不如直接全表扫。key = NULL。

修复：给 remark 也加索引（如果 remark 是长文本就不合适），或者拆成两条 SQL 用 UNION：

SELECT * FROM orders WHERE status = 1
UNION
SELECT * FROM orders WHERE remark LIKE '%紧急%';

注意 UNION 会去重，如果不需要去重用 UNION ALL，性能更好。

如果 OR 两边的列都有索引，MySQL 5.7+ 可能会用 index_merge 优化，type 显示 index_merge，这时也算走了索引——但它常常不如单索引高效，因为要合并两个结果集。

场景 5：复合索引不满足最左前缀

idx_user_status (user_id, status)：

SELECT * FROM orders WHERE user_id = 100 AND status = 1;   -- 走索引，key_len 全用
SELECT * FROM orders WHERE user_id = 100;                  -- 走索引，只用 user_id 部分
SELECT * FROM orders WHERE status = 1;                     -- 不走索引（key = NULL）

复合索引的 B+ 树是按 (user_id, status) 排序的。只查 status 相当于要跳过第一列，无法定位。

key_len 能看出用了几个字段。user_id 是 bigint（8 字节，允许 NULL 再加 1），status 是 tinyint（1 字节，允许 NULL 加 1）。所以：

• key_len = 9：只用了 user_id（8 + 1）
• key_len = 10：两个字段都用上了

不精确但能反推。这个技巧在排查"到底命中了几列"时非常好用。

场景 6：范围查询截断了后面的列

-- 复合索引 (user_id, status)，但 status 用于范围
SELECT * FROM orders WHERE user_id = 100 AND status > 0;

user_id 仍能用索引定位，但 status > 0 之后就无法继续用索引做精确匹配了——因为范围扫描后，后续列的顺序不再有序。这在只有两列时影响不大，但有三列时很致命：

-- 索引 (a, b, c)
WHERE a = 1 AND b > 10 AND c = 5

这里 c 用不上索引。索引设计上要注意：把等值条件的列放前面，范围条件的列放最后。

场景 7：!= 和 NOT IN

SELECT * FROM orders WHERE status != 1;
SELECT * FROM orders WHERE status NOT IN (1, 2);

这两句不一定失效，取决于优化器估算的命中比例。如果 status != 1 排除了 90% 的行，索引很有价值；如果只排除了 10%，优化器会选全表扫。

NOT IN 还有个额外陷阱：子查询里有 NULL 会导致结果集为空。

-- 如果子查询返回 NULL，整句查不出数据
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM blacklist);

改成 NOT EXISTS 或者给子查询加 WHERE user_id IS NOT NULL。

场景 8：IS NULL / IS NOT NULL

这个说法要纠正一下：IS NULL 是可以走索引的（InnoDB 会把 NULL 值存在索引里）。但 IS NOT NULL 通常不走——因为它要返回"除 NULL 之外的全部行"，命中率太高，优化器倾向全表扫。

实践建议：尽量避免用 NULL 表达业务语义。status = 0 比 status IS NULL 好，deleted = 0 比 deleted IS NULL 好。给列加 NOT NULL DEFAULT 是最省心的做法。

场景 9：排序与索引顺序不一致

-- 索引 (user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY status;
-- Extra: 无 Using filesort，说明索引顺序正好满足排序

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- Extra: Using filesort，需要额外排序

Using filesort 不等于用了磁盘文件，它只是"无法利用索引顺序，需要额外排序"。数据量小时影响不大，但几十万行时会明显变慢，还会吃 sort_buffer_size。

修复思路：让索引顺序跟 ORDER BY 顺序一致。上例中建 (user_id, created_at) 就能消除 filesort。

ORDER BY 混用 ASC / DESC 是另一个坑。MySQL 8.0 之前不支持降序索引，ORDER BY a ASC, b DESC 一定 filesort。8.0 支持 KEY idx (a ASC, b DESC)，但要求排序方向和索引定义完全一致。

场景 10：优化器主动放弃

这是最容易被误判为"索引失效"的场景。MySQL 的优化器会算成本：

• 走索引：随机 I/O（回表），每行一次
• 全表扫：顺序 I/O

如果 WHERE status = 1 命中了 80% 的行，走索引要回表 80 万次（随机读），全表扫只要顺序读 100 万行——后者更快。此时 key = NULL 是正确的决策，不是 bug。

判断方法：看 rows 和表的实际行数。如果 rows / 总行数 > 0.3，优化器放弃索引是合理的。

对应的优化方向不是"强制走索引"，而是：

• 加覆盖索引，消除回表（Extra: Using index）
• 调整索引设计，让条件更有区分度
• 分库分表或者引入缓存

三、覆盖索引：唯一能让 ALL 变 index 的银弹

-- 表有 (user_id, status) 索引
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;
-- Extra: Using index   <- 覆盖索引，只读索引不回表

EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 100;
-- Extra: Using index condition（MySQL 5.6+）或 NULL
-- 因为 amount 不在索引里，必须回表

覆盖索引的价值在于省掉回表。一次查询从"索引查找 + 每行一次随机读"变成"只读索引树"，性能差距是数量级的。

所以有个反直觉的设计原则：索引列不要盲目"越多越好"，而要看查询能不能被覆盖。 对高频查询，把 SELECT 用到的少量列加进索引，比多加几个 WHERE 条件更值。

代价是索引变大、写入变慢。这个取舍要按读写比来定。

四、EXPLAIN ANALYZE：看真实执行而非预估

EXPLAIN 输出的是优化器的估算，rows 可能差几个数量级。MySQL 8.0.18 起支持真正执行并统计：

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 100 AND status = 1;

输出是树形结构，带实际耗时和实际行数：

-> Index lookup on orders using idx_user_status (user_id=100, status=1)
   (cost=0.35 rows=1) (actual time=0.045..0.046 rows=3 loops=1)

对比 rows=1（预估）和 rows=3（实际），就能判断统计信息准不准。如果预估和实际差得远，先跑 ANALYZE TABLE orders; 更新统计信息再测——很多时候"索引用错了"就是统计信息过期导致的。

五、索引设计的几条实用原则

1. 区分度优先。status 只有几个值，单独建索引意义不大；user_id 区分度高，才值得放前面。
2. 复合索引按"等值 -> 范围 -> 排序"排列。等值条件放前，范围条件放后，排序字段跟索引顺序对齐。
3. 单表索引数量控制在 5 个以内。每个索引都要在写入时维护 B+ 树，写多读少的表加索引要谨慎。
4. 长字符串用前缀索引。KEY idx_url (url(32)) 比全列索引小得多，前提是前 32 字符区分度够。
5. 定期用 sys.schema_unused_indexes 找无用索引：

sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

没人用的索引纯属负担，删掉能提升写入性能。

6. 慢查询日志才是起点，不是 EXPLAIN。先开 slow_query_log，找到真正慢的 SQL，再去 EXPLAIN。对着一堆不慢的 SQL 做索引优化是浪费。

sql SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

小结

回到开头那句话：索引"失效"绝大多数是优化器的理性选择。

真正需要改代码的，是那几类结构性问题：隐式类型转换、索引列上做运算、前导模糊、复合索引顺序错误。这些场景下索引再好也用不上，必须改写 SQL 或调整索引。

而"命中了大量行导致放弃索引"这类，改 SQL 没用，方向是覆盖索引、缓存或者架构调整。

排查顺序建议固定下来：慢查询日志定位 -> EXPLAIN 看 key/type/rows -> 对比表总行数判断合理性 -> EXPLAIN ANALYZE 验证真实执行 -> 再决定改 SQL 还是改索引。 跳过任何一步，都容易在错误的方向上花时间。