MySQL — 排序落盘：Creating tmp table to disk

修复 ORDER BY 与 GROUP BY 因排序与临时表落盘而变慢的问题，合理设定 sort_buffer 与 tmp_table，并为排序顺序建索引。

症状

　　慢日志直接标注了机制：Query_time 4.9 秒、Rows_examined 214 万、附注 Creating sort index——为了发送 100 行，排序跑遍全表，因为没有任何索引承载这个顺序。状态计数器给出凭证：Sort_merge_passes 大于零说明内存排序经磁盘归并；Created_tmp_disk_tables 增长跟踪 GROUP BY 临时表的落盘转换。EXPLAIN 的 Using filesort 与 Using temporary、tmpdir 文件系统的 IO 尖峰随报表查询出现，共同构成完整画像。

诊断思路

　　先读 EXPLAIN FORMAT=JSON 拿到 using_filesort、待排序行数、是否内存放得下——一次输出讲完落盘故事；再查 Sort_merge_passes 与 Created_tmp_disk_tables 两个计数器确认溢出频率；最后用平均行宽 × 行数对比 sort_buffer_size 做算术预测。LIMIT 高偏移仍要排序整个前缀，是“明明有 LIMIT 还慢”的答案。

根因

　　当查询必须排序的行超过每连接排序缓冲时，MySQL 把归并段写盘并在 tmpdir 归并；GROUP BY 中间表超过内存临时表上限同样转成磁盘表。查询每次执行都付磁盘 IO，延迟随数据规模线性增长。

修复与验证

　　按 ORDER BY（含 WHERE 前缀）建覆盖索引让排序彻底消失；收缩投影列宽（延迟连接：先只排序 id 再回表）让排序成本按字节下降；sort_buffer_size 与 tmp_table_size 按实测工作集设定；高偏移分页换成 keyset 形式（(created_at, id) < 上次游标）零额外排序。验证时 EXPLAIN 无 filesort、负载下 Sort_merge_passes 保持为零、慢日志不再标记该语句。sort_buffer_size 是每连接的——全局调大乘以 max_connections 可能耗尽内存，必须按实测排序尺寸来。

代码示例

# Query_time: 4.9  Lock_time: 0.000  Rows_sent: 100  Rows_examined: 2,140,882
# Creating sort index
SELECT id, title, body FROM articles ORDER BY created_at DESC LIMIT 100;

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

CREATE INDEX articles_created ON articles (created_at DESC, id);
-- WHERE + ORDER BY sharing the index prefix:
-- SELECT ... WHERE status = 'published' ORDER BY created_at DESC
CREATE INDEX articles_status_created ON articles (status, created_at DESC, id);

-- deferred join: sort only ids, then join back:
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 100) t
  ON a.id = t.id;

sort_buffer_size = 8M        # row_width x rows for your measured sorts
tmp_table_size = 256M        # with max_heap_table_size matched

WHERE (created_at, id) < (last_seen_created, last_seen_id)
ORDER BY created_at DESC, id DESC LIMIT 100;