MySQL ORDER BY 有索引却不用？揭秘 `EXPLAIN` 中的“索引失效”真相

在日常的 MySQL 性能优化中，我们常常会遇到一个令人困惑的现象：明明为 ORDER BY 的字段创建了索引，但使用 EXPLAIN 查看执行计划时，却发现 key 为 NULL，Extra 中出现了 Using filesort，索引似乎“失效”了。

这究竟是怎么回事？是索引没建好？还是 MySQL 优化器“抽风”了？今天，我们就来深入剖析这一现象背后的真正原因，并提供切实可行的解决方案。

一、问题重现

假设我们有一个用户表 users，并为 created_at 字段创建了索引：

1	`CREATE INDEX idx_created_at ON users(created_at)`

执行查询：

1	`EXPLAIN SELECT * FROM users ORDER BY created_at;`

你可能会惊讶地发现，执行计划中并未使用 idx_created_at 索引，而是进行了全表扫描和 filesort。

这到底是为什么？

二、核心原因：回表成本过高

要理解这个问题，我们必须先了解 MySQL 索引的底层机制。

二级索引的结构：在 InnoDB 存储引擎中，除了主键索引（聚簇索引）外，其他索引都是二级索引。二级索引的叶子节点存储的是索引字段的值 + 主键值，而不是完整的数据行。
SELECT * 带来的“回表”问题：
- 当执行 SELECT * FROM users ORDER BY created_at 时，MySQL 可以利用 idx_created_at 索引快速获取按 created_at 排序的主键列表。
- 但为了获取 * 所代表的所有字段数据，MySQL 必须拿着这些主键，逐个回到主键索引中查找完整的数据行。这个过程称为“回表”（Bookmark Lookup）。
优化器的权衡：
- 如果表中有成千上万条数据，ORDER BY 又需要返回大部分或全部数据，那么这个“回表”过程就会产生大量的随机 I/O 操作。
- 随机 I/O 的成本远高于顺序 I/O。
- 因此，MySQL 优化器会进行成本估算：与其进行上万次随机 I/O（回表），不如直接进行一次全表扫描（顺序 I/O），然后在内存中对结果集进行排序（filesort）。
- 在大多数情况下，全表扫描 + 内存排序的总成本低于遍历二级索引 + 大量回表的成本。

这就是为什么 EXPLAIN 显示未使用索引的根本原因——优化器认为全表扫描更高效。

三、常见场景与解决方案

除了上述核心原因，还有其他几种常见情况会导致 ORDER BY 索引未被使用：

1. `WHERE` 条件优先级更高

场景：SELECT * FROM users WHERE status = 'active' ORDER BY created_at;
原因：如果 status 字段的过滤性很强（如只有少量用户是 ‘active’），优化器可能优先选择 status 上的索引，然后对少量结果进行 filesort。
方案：创建复合索引 idx_status_created(status, created_at)，让查询既能高效过滤，又能避免排序。

2. 范围查询阻断索引排序

场景：WHERE age > 18 ORDER BY created_at，索引为 (age, created_at)。
原因：“最左前缀原则”下，age > 18 是范围查询，其后的 created_at 无法再利用索引排序。
方案：调整查询逻辑，或接受 filesort 的存在。

3. 数据量过大或分布不均

场景：表数据量极大，或 ORDER BY 字段值重复率高。
原因：索引优势减弱，优化器可能选择全表扫描。
方案：优化查询条件，减少结果集。

四、终极解决方案：覆盖索引

要让 ORDER BY 索引真正发挥作用，最有效的方法是避免“回表”。

✅ 方案一：避免 `SELECT *`

永远不要在生产环境使用 SELECT *！只查询必要的字段：

1 2	`-- 好的做法 SELECT id, name, created_at FROM users ORDER BY created_at;</code></pre>`

如果查询的字段恰好都在索引中，就可能实现“覆盖索引”。

✅ 方案二：创建覆盖索引（Covering Index）

将查询中用到的所有字段都包含在索引中，使索引“覆盖”查询需求。

1 2	`-- 创建覆盖索引 CREATE INDEX idx_cover ON users(created_at, id, name, email, status);`

此时，EXPLAIN 的 Extra 列会显示 Using index，表示索引覆盖，无需回表，性能极佳。

注意：覆盖索引会增加索引大小和写入开销，需根据读写比和业务场景权衡。

五、如何验证？

使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 观察执行计划：

key：实际使用的索引。
Extra：
- Using index：覆盖索引，最佳。
- Using filesort：进行了排序，可能未用索引。
- Using index condition：索引条件下推，部分优化。

六、总结

原因	解决方案
`SELECT *` 导致回表成本高	避免 `SELECT *`，只查必要字段
未使用复合索引	创建 `(where_col, order_by_col)` 复合索引
无法避免回表	创建覆盖索引，包含所有查询字段
优化器误判	使用 `ANALYZE TABLE` 更新统计信息，或 `FORCE INDEX`（慎用）

核心思想：MySQL 优化器的目标是总成本最低，而不是“必须用索引”。理解索引结构（尤其是回表机制）和优化器的决策逻辑，才能写出真正高效的 SQL。

MySQL ORDER BY 有索引却不用？揭秘 EXPLAIN 中的“索引失效”真相