MySQL 降序索引什么时候能避免 filesort

MySQL 降序索引能否避免 filesort，关键不在索引名字里有没有 DESC，而在索引列的顺序和方向是否能完整满足 ORDER BY。同向排序通常可以通过正向或反向扫描使用一个多列索引；混合排序则要让索引键段保持相同的方向组合。即使索引结构匹配，优化器也可能因为代价选择排序，因此必须用 EXPLAIN 复核。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/descending-indexes.html

先把 ORDER BY 和索引方向对齐

假设查询按 score 降序、created_at 升序排列：

-- 混合方向必须在索引中表达出来，才能尝试直接按索引顺序读取。
CREATE INDEX idx_score_desc_created_asc
    ON exam_result (score DESC, created_at ASC);

-- 这个查询的排序方向与索引键段一一对应。
SELECT id, score, created_at
FROM exam_result
ORDER BY score DESC, created_at ASC
LIMIT 20;

在 MySQL 8.4 手册的规则里，DESC 不再只是被忽略的装饰，而会影响键值存储方向。对应的反向组合也可能通过 backward scan 工作，例如索引为 (score ASC, created_at DESC) 时，优化器可以反向读取来满足 score DESC, created_at ASC。重点是两个键段的方向关系要匹配，不能只给第一列加降序。

前缀常量有时能放宽索引匹配

多列索引不要求把所有索引列都写进 ORDER BY。如果未出现在排序里的前缀列在 WHERE 中被固定为常量，剩余键段仍可能提供有序输出：

-- tenant_id 被固定后，索引的 created_at 顺序可以直接服务排序。
CREATE INDEX idx_tenant_created
    ON audit_log (tenant_id ASC, created_at DESC);

-- 不需要把 tenant_id 再写进 ORDER BY。
SELECT id, action, created_at
FROM audit_log
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

这不是“有索引就一定没有 filesort”。过滤条件要足够有选择性，优化器还要认为索引范围扫描比表扫描再排序更便宜。若排序列经过函数、表达式或隐式类型转换，普通索引也不能直接提供表达式结果的顺序。

哪些情况会让降序索引仍然出现 filesort

• 排序列顺序不连续：索引是 (a, b)，查询却跳过 a，且 a 没有被常量固定。
• 跨表排序：连接后的排序列来自不同表，单表索引无法直接提供最终行序。
• 表达式或别名指向计算结果：ORDER BY ABS(score) 与普通 score 索引不是一回事。
• 索引前缀过短：字符串只索引了前几个字节，无法区分完整排序值时仍需额外排序。
• 引擎或索引类型不支持：降序索引主要面向 InnoDB 的 B-Tree；HASH、FULLTEXT、SPATIAL 不能套用同一规则。

用 EXPLAIN 判断是否真的避开 filesort

不要因为 possible_keys 出现了索引名，就认定排序被索引解决。最直接的信号是传统 EXPLAIN 的 Extra：没有 Using filesort 才说明没有额外 filesort；出现它则表示仍发生了排序阶段。

-- 先观察访问类型、实际 key 和额外排序阶段。
EXPLAIN
SELECT id, score, created_at
FROM exam_result
ORDER BY score DESC, created_at ASC
LIMIT 20;

-- 需要更清晰的扫描方向时查看树形计划。
EXPLAIN FORMAT=TREE
SELECT id, score, created_at
FROM exam_result
ORDER BY score DESC, created_at ASC
LIMIT 20;

树形计划里，使用降序索引时可能显示索引名和 (reverse)；传统计划也可能在 Extra 中显示 Backward index scan。这类信息比“我刚建了一个 DESC 索引”更可靠。带 LIMIT 时还要注意优化器可能偏好有序索引，也可能认为其他路径更便宜，最终仍选择内存中的 filesort。

把索引设计成可解释的工程决策

降序索引会增加存储、写入和维护成本，不应为每个排序组合都建立一份。优先为稳定的列表页、时间线、排行榜和“取前 N 条”查询设计；确认排序方向、过滤前缀和分页方式后，再通过真实数据量的 EXPLAIN 观察计划。若多个排序只是同向反转，一个索引可能已足够；若确实存在两种混合方向，就比较两个组合的读收益是否值得额外写放大。

如果结果中排序键可能相同，建议追加唯一键作为最后的稳定排序列，例如 ORDER BY score DESC, created_at ASC, id ASC，并把它纳入索引设计。这样能减少分页时同分行顺序漂移，但也会让索引更宽，仍需结合查询覆盖范围取舍。

结论与常见追问

MySQL 降序索引能避免 filesort 的前提是：排序列顺序连续、方向关系匹配、过滤条件没有破坏索引顺序，且优化器最终选择该扫描路径。混合 ASC/DESC 场景尤其要把每个键段写清楚，最后以 EXPLAIN 的实际计划为准。

只建立第一列 DESC 可以解决混合排序吗？ 通常不够，第二列方向同样决定索引能否提供完整顺序。

看到 Using filesort 就代表用了磁盘吗？ 不一定，filesort 可能在内存中完成；它表示发生了额外排序阶段。

为什么索引匹配了，优化器仍不用？ 代价模型可能认为扫描大量索引再回表比其他路径排序更贵，应结合行数、选择性和 LIMIT 判断。