MySQL 中的“双路排序”与“单路排序”：原理、判别与实战调优

一句话导读
ORDER BY 不能走索引时，MySQL 会在 Server 层做一次 filesort。内部实现分 单路（全字段） 与 双路（rowid） 两种；了解它们的触发条件、判别方法与调优思路，是 SQL 性能优化的必修课。

阶段	单路排序 (Single-Pass)	双路排序 (Two-Pass)
读取列	所有查询列一次性读入 sort buffer	只读排序键 + rowid
排序对象	完整记录	`<排序键, rowid>` 二元组
回表	不需要	按 rowid 二次回表取整行
内存消耗	高（存整行）	低（只存键+id）
I/O 特征	顺序读一次	随机读两次
典型触发	查询列总字节 ≤ `max_length_for_sort_data`	超过阈值或含大 `TEXT/BLOB`

单路排序
1. 扫表/索引 → 把需要的 所有列 拷进 sort_buffer
2. 在内存(或磁盘临时文件)里按排序键快排/归并
3. 直接返回结果给客户端
双路排序
1. 只取 排序键 + 聚簇主键(rowid) 进 sort buffer
2. 排序后得到“排好序的 rowid 列表”
3. 按 rowid 顺序回表 取其余列 → 返回

MySQL 不直接写“单路/双路”字样，而是把信息藏在 optimizer trace 与 EXPLAIN FORMAT=json 里。

EXPLAIN FORMAT=json
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
ORDER BY total_amount DESC LIMIT 20\G

在输出里查找：

"filesort_information": [{"sort_mode": "<sort_key, rowid>"          <-- 双路/* 或 "<sort_key, additional_fields>" */  <-- 单路}
]

-- 会话级开启
SET optimizer_trace="enabled=on";
-- 执行目标 SQL
SELECT ... ORDER BY ...;
-- 查看 trace
SELECT * FROM information_schema.optimizer_trace\G

搜索关键字：

"filesort_summary": {"sort_mode": "<sort_key, rowid>"
}

含义同上。

MySQL 8.0.13+ 的 慢日志 JSON 会记录 "sort_mode" 字段。
performance_schema 表 events_statements_history_long 中：
- SUM_SORT_ROWS 累计排序行数
- SUM_SORT_ROW_ID > 0 可侧面反映双路排序

目标	手段
避免 filesort	建立覆盖索引 `(order_col, ...)`，使 `EXPLAIN` 出现 `Using index`
保持单路	减少查询列宽度；避免 `SELECT *`；调大 `max_length_for_sort_data`
降低内存压力	若列过大，可接受双路；或把大 `TEXT/BLOB` 拆子表延迟加载
加速排序	调大 sort_buffer_size（会话级）；确保 tmp_table_size/max_heap_table_size 足够

示例调优：

-- 会话级只对当前连接生效
SET sort_buffer_size = 4*1024*1024;          -- 4 MB
SET max_length_for_sort_data = 4096;         -- 允许更长列走单路

场景：订单宽表 orders 30+ 列，含 TEXT 备注字段。

SELECT * FROM orders
WHERE order_date >= '2025-01-01'
ORDER BY total_amount DESC
LIMIT 20;

EXPLAIN FORMAT=json 看到 "sort_mode": "<sort_key, rowid>"，慢日志显示 Sort_row_id: 125000。
→ 触发双路+大量回表，耗时 1.2 s。

优化：
- 去掉 * 只取需要的 5 列，列宽 < 3 KB
- 新建复合索引 (order_date, total_amount DESC) 并 覆盖查询列
结果：
- EXPLAIN 出现 Using index; Using filesort 消失
- 查询降至 12 ms，CPU 降 90%。