MySQL filesort 是什么？如何优化排序？

简化版

filesort 表示 MySQL 不能直接按索引顺序得到排序结果，需要额外排序；优化思路是让 WHERE 和 ORDER BY 匹配合适的联合索引，减少排序数据量，并避免返回过多列和深分页。

详细版

filesort 名字里有 file，但不一定真的落磁盘，它表示额外排序算法。数据量小可能在内存中排，数据量大或排序字段很宽时可能使用磁盘临时文件。

例如：

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

如果有 (status, created_at) 索引，MySQL 可以先定位 status，再按 created_at 索引顺序取前 20 条；如果没有合适索引，就可能扫描大量行后 filesort。

面试中要强调：filesort 不一定是坏事，但高频大结果集排序要尽量用索引顺序；排序优化通常和过滤条件、返回列、分页方式一起设计。

完整版教学

一、filesort 不是简单的文件排序

Using filesort 是 MySQL EXPLAIN 中常见的 Extra 信息，表示需要额外排序。

EXPLAIN
SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

如果 Extra 出现 Using filesort，说明 MySQL 没有直接利用索引顺序完成排序。它可能在内存中排序，也可能在数据量大时借助磁盘临时文件。

记忆钩子：filesort 的重点是“额外排序”，不是一定写文件。

二、为什么索引能避免排序

B+ 树索引本身有序。如果查询条件和排序字段匹配索引顺序，数据库可以沿索引扫描，天然得到有序结果。

CREATE INDEX idx_status_created
ON articles(status, created_at);

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY created_at
LIMIT 20;

索引顺序近似是：

status='draft'     -> created_at 有序
status='published' -> created_at 有序

当 status 是等值条件时，published 这一段内部按 created_at 有序，MySQL 可以直接取前 20 行。

三、联合索引顺序很关键

排序优化常见索引不是只给排序字段建索引，而是把过滤字段和排序字段组合起来。

查询	更可能合适的索引	原因
`WHERE status=? ORDER BY created_at`	`(status, created_at)`	先过滤再按时间有序
`WHERE user_id=? ORDER BY id`	`(user_id, id)`	用户范围内按 id 有序
`WHERE status=? AND type=? ORDER BY created_at`	`(status, type, created_at)`	等值列在前，排序列在后

如果写成 (created_at, status)，虽然可以按时间排序，但过滤 status 可能无法高效定位范围，扫描行数会变多。

优化排序时要同时看过滤选择性和排序顺序。

四、范围条件可能截断排序利用

联合索引中遇到范围条件后，后续列通常很难继续用于全局有序排序。

CREATE INDEX idx_a_b_c ON t(a, b, c);

SELECT *
FROM t
WHERE a = 1 AND b > 10
ORDER BY c;

a=1 是等值，b>10 是范围。对于每个不同的 b 值，c 内部有序，但整体 c 不一定全局有序，因此可能仍需要 filesort。

简化示意：

b=11: c=1,2,3
b=12: c=1,2,3
合并后按索引是 b 优先，不是 c 全局有序

这也是很多排序索引“看起来有字段但还是 filesort”的原因。

五、减少参与排序的数据量

即使无法完全避免 filesort，也可以让排序更轻。

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

比下面更轻：

SELECT *
FROM articles
ORDER BY created_at DESC;

优化方向包括：增加过滤条件、只返回必要列、先取主键再回表、避免超大 OFFSET、合理设置排序缓冲但不迷信参数。

如果排序 100 行，filesort 成本通常可以接受；如果排序 1000 万行，再好的参数也救不了糟糕查询模式。

六、排序和分页要一起看

排序后分页是高频组合。ORDER BY created_at DESC LIMIT 20 OFFSET 100000 即使有索引，也可能需要走过大量记录。

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

深分页更适合 keyset pagination：

SELECT id, title, created_at
FROM articles
WHERE status = 'published'
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;

索引可以设计为 (status, created_at, id)。这样既服务过滤，又服务排序和翻页边界。

七、常见误区与追问

误区：Using filesort 一定表示排序写磁盘。 它表示额外排序，是否落盘取决于数据量和内存等因素。
误区：给 ORDER BY 字段单独建索引就一定够。 过滤条件和排序字段要一起考虑联合索引。
误区：出现 filesort 就必须优化掉。 小数据量、低频查询可以接受，关键看成本和场景。
误区：调大 sort_buffer_size 就能解决排序慢。 参数只能缓解局部问题，索引和数据量才是根本。
追问：为什么范围条件后排序字段可能用不上？ 范围列会让后续列只在局部有序，不能保证整体排序。
追问：如何优化排序分页？ 使用匹配索引、稳定排序键、减少返回列，深分页改 keyset。

八、加强记忆

filesort 优化按“三件事”想：索引能不能给出顺序，参与排序的数据能不能减少，分页方式会不会跳过太多。看到 Using filesort 不要慌，先判断排序规模和频率，再决定是否用联合索引重写。

简化版

详细版