MySQL 的 ORDER BY 是怎么排序的？Using filesort 一定会写磁盘吗？

知识点详解：筛选顺序和输出顺序，怎样放在一起？

索引为什么有机会省掉排序？

假设有索引 (status, created_at, id)。在 status 固定为同一个值的区域里，记录可以继续按 created_at、id 的索引顺序读取。

如果查询条件和输出顺序匹配，就可能沿这段索引得到需要的结果，不必把所有候选重新排列。

但 status 不是单个固定值时，各个 status 区域里的 created_at 顺序，不等于整个结果按 created_at 全局有序。索引里“有这个列”和“可以直接得到所需顺序”不同。

下面是示意查询，不代表优化器在任意数据分布下必然选择该索引：

SELECT id, created_at
FROM task
WHERE status = 'done'
ORDER BY created_at ASC, id ASC
LIMIT 20;

这里表示索引键的扫描顺序，不表示记录在磁盘上必须连续存放。

filesort 具体多做了什么？

它增加了一次排序阶段。MySQL 读取相应候选记录，准备排序信息，再组织出符合 ORDER BY 的结果。

小排序可以在内存里完成。数据超出相应内存条件时，才可能产生临时文件与归并工作。EXPLAIN 的 Using filesort 本身不能区分它是否完全在内存中完成。MySQL 8.4 ORDER BY 文档

因此看到这个词，下一步是调查实际排序规模与成本，不是直接判定磁盘瓶颈。

为什么排序索引未必更快？

假设一个筛选条件非常有选择性，只留下少量候选。先用筛选索引拿到候选，再排序，可能很便宜。

另一种计划沿排序索引一路扫描，每读到一行再判断是否满足条件。即使没有额外排序，也可能要读大量无关记录才能凑够 LIMIT。

同样，有些索引顺序满足需求，但需要大量回表。不能只看计划中一个标记消失，就认为整条查询更省资源。

排序方向和稳定顺序也要看

多个字段都同向排序时，合适索引可能通过正向或反向扫描使用。混合 ASC、DESC 则需要对应的索引顺序条件，不能背成“出现 DESC 就不能使用索引”。

如果 created_at 有重复值，只按它排序并不保证同时间记录的顺序稳定。需要稳定分页时，可以增加符合语义的唯一排序键，如 id，并配合分页方案检查。

ORDER BY 表达式、前缀索引等也可能让现有索引无法完整提供所需顺序，应对照实际查询判断。

应该怎样验证优化？

保留典型与极端筛选条件，检查执行计划、实际扫描与返回行数、延迟和排序临时文件等信息。支持时可以在合适环境使用 EXPLAIN ANALYZE，但它会实际执行语句，不能在生产中不加判断地运行。

调整 sort_buffer_size 也要考虑并发内存占用。单个排序更宽裕，不等于整个数据库在高并发下更安全。

面试官继续追问

加 LIMIT 20，就只需要检查二十行吗？

不一定。它限制返回结果，不自动限制候选读取量。过滤选择性与排序计划都会影响扫描规模。

Using index 就说明没有 filesort 吗？

不能混淆两个标记。Using index 通常涉及覆盖索引，是否额外排序要看排序相关计划。

所有分页都必须消除 filesort 吗？

不必。先看用户需要的排序与查询规模，再比较实际成本。为某个低频查询增加昂贵索引，也可能得不偿失。

面试速记卡

• 索引排序：利用满足条件的现成索引顺序。
• filesort：额外排序阶段，不保证会写磁盘。
• 联合索引：字段顺序、固定前置列和排序方向共同影响。
• 优化目标：降低整体成本，不只消除计划标记。
• 验证重点：扫描规模、回表、排序工作和代表性条件。