17370845950

filesort 是 MySQL 对无法利用索引排序时触发的内部排序流程统称，实际策略依数据量和配置动态选择单路或双路排序，核心是通过索引优化避免其触发。

MySQL 中的 filesort 并不是一种特定的排序算法名称，而是 MySQL 优化器对“无法利用索引完成排序”时所触发的**内部排序流程的统称**。它背后实际使用的排序策略取决于数据量、系统配置和字段类型，核心目标是尽可能高效地完成 ORDER BY 或 GROUP BY 所需的排序。

什么时候会触发 filesort？

当查询中包含 ORDER BY（或 GROUP BY）且没有可用的索引完全覆盖排序列+查询所需列时，MySQL 就必须额外排序。常见触发场景：

• 排序字段无索引，或索引不匹配排序方向（如索引是 ASC，却用 DESC）
• 复合索引前导列未出现在 WHERE 条件中（例如索引是 (a,b,c)，但排序是 ORDER BY b,c）
• 排序字段包含函数或表达式（如 ORDER BY UPPER(name)）
• 多表 JOIN 后对非驱动表字段排序，且无合适索引

filesort 的两种主要执行模式

MySQL 5.7 及以后默认采用“双路排序”（two-pass sort）的变体，但实际行为由 sort_buffer_size 和数据特征动态决定：

• 单路排序（original filesort algorithm）：把查询需要的所有字段（SELECT 列 + ORDER BY 列）一次性读入 sort buffer，排序后直接按序输出。优点是只需一次 I/O 读取；缺点是容易超出 sort_buffer_size，导致磁盘临时文件（/tmp 下的 #sql_*.MYD），性能骤降。
• 双路排序（modified filesort algorithm）：先读取排序字段 + 主键（或 rowid），在 sort buffer 中仅对这些“轻量信息”排序；排序完成后，再根据排好序的主键回表（retrieval）读取其余字段。内存压力小，但需要二次随机 I/O，对 SSD 影响较小，对 HDD 可能变慢。

MySQL 会根据 max_length_for_sort_data 参数权衡：若预计单行总长度 ≤ 该值，倾向单路；否则退回到双路。可通过 EXPLAIN 输出中的 Extra 字段观察：Using filesort 表示触发，但不区分单/双路；Using index for group-by 或 Using index 则说明未触发 filesort。

影响 filesort 性能的关键参数

这些参数不改变算法本质，但显著影响是否落盘、用时长短：

• sort_buffer_size：每个连接独享的内存缓冲区，默认 256KB。太小 → 频繁磁盘排序；太大 → 内存浪费甚至 OOM。建议按并发连接数 × 平均大小预估，线上一般设为 2MB–8MB。
• read_rnd_buffer_size：配合双路排序使用，控制回表读取时的随机读缓存大小。增大可减少回表 I/O 次数。
• tmp_table_size 和 max_heap_table_size：限制内存临时表上限。filesort 中若需建临时表（如 DISTINCT + ORDER BY 组合），超限会转磁盘（MYI/MYD），大幅拖慢速度。

如何避免或优化 filesort？

根本思路是让排序“走索引”，消除额外排序步骤：

• 为 ORDER BY 字段建立单独索引，或设计复合索引使其最左前缀匹配排序条件（注意 ASC/DESC 一致性）
• 避免 SELECT *，只查真正需要的列 —— 减少单路排序的数据体积，也利于覆盖索引
• 对字符串排序字段，考虑前缀索引或规范化存储（如城市名用 code 替代全名）
• 大分页场景（OFFSET 很大）慎用 ORDER BY ... LIMIT m,n，改用游标分页（记录上一页最大 ID）
• 监控 Sort_merge_passes 状态变量：持续增长说明频繁磁盘归并，需调参或优化索引

理解 filesort 不是为了手动干预其内部排序逻辑（比如选 quicksort 还是 mergesort），而是看清它何时出现、为何变慢，并通过索引与查询重构把它“消灭”在源头。