mysql执行流程中的排序优化与内存调整

ORDER BY 用 filesort 不一定慢，关键看索引是否覆盖排序与查询字段；sort_buffer_size 并非越大越好，需结合 Sort_merge_passes 调整至 2M–4M；ORDER BY + LIMIT 并不自动优化排序，深分页应改用游标分页。

ORDER BY 用了 filesort 就一定慢？

不一定。MySQL 在执行 ORDER BY 时是否走 filesort，取决于是否有可用的索引覆盖排序字段和查询字段。如果 WHERE 条件 + ORDER BY 字段能被联合索引完全覆盖（且顺序匹配），就可能避免 filesort；否则即使有单列索引，也可能触发 filesort。

常见误判场景：

• 写了 INDEX(a)，但查询是 WHERE b = ? ORDER BY a → 无法利用索引排序
• ORDER BY a ASC, b DESC，但索引是 INDEX(a, b)（MySQL 8.0 前不支持混合方向索引）→ 强制 filesort
• 查询中含 SELECT * 且索引未包含所有列 → 即使排序走索引，仍需回表，优化器可能放弃索引排序

sort_buffer_size 设置多大才合适？

sort_buffer_size 是每个连接独占的内存缓冲区，用于在内存中完成排序。它不是越大越好，设置过大会导致：

• 并发高时内存暴涨，触发系统 OOM 或 MySQL 被 kill
• 超过 max_length_for_sort_data 阈值后，MySQL 自动降级为“双路排序”（rowid sort），反而增加 I/O
• 分配过大但实际未用满，纯属浪费

建议做法：

• 先通过 SHOW PROFILE FOR QUERY N 或 EXPLAIN FORMAT=JSON 确认是否真卡在 sorting 阶段
• 观察 Sort_merge_passes 状态变量：持续 > 0 表示频繁归并，说明 sort_buffer_size 不足
• 线上一般设为 2M–4M（默认 256K 太小），单次连接最大不超过 32M；切勿全局设成 128M

如何判断排序是否用了临时文件？

当 sort_buffer_size 不足以容纳全部排序数据时，MySQL 会将中间结果写入磁盘临时文件，再做归并排序。这不是错误，但意味着性能拐点已到。

关键指标和验证方式：

• 查看 SHOW STATUS LIKE 'Sort_%'：Sort_merge_passes 上升 → 有磁盘归并；Sort_scan / Sort_range 表示扫描驱动的排序次数
• EXPLAIN 中出现 Using filesort 不代表一定落盘，只是表示用了排序算法；要结合 Sort_merge_passes 判断
• 开启 optimizer_trace 后查 sort_buffer_size_used 和 examined_rows，可估算实际内存占用

SET optimizer_trace="enabled=on";
SELECT * FROM t ORDER BY c1;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

ORDER BY + LIMIT 的优化陷阱

很多人以为加了 LIMIT 就能跳过全量排序，但 MySQL 5.7 及以前版本中，ORDER BY ... LIMIT N 仍会先排完整结果集，再取前 N 行 —— 除非满足“索引覆盖 + 无回表”条件。

真正有效的优化路径：

• 确保 ORDER BY 字段有前导索引，且 LIMIT 值较小时，优化器才可能使用“index dive”快速定位
• 避免 SELECT *，只查必要字段，减少排序单元大小，提升 sort_buffer 利用率
• 对分页深翻（如 LIMIT 10000, 20），优先考虑游标分页（WHERE id > ? ORDER BY id LIMIT 20），彻底绕过排序

最易被忽略的一点：即使加了索引，若 WHERE 条件选择率极低（例如 WHERE status = 0 只有 0.1% 数据），优化器仍可能放弃索引排序而选全表扫描 + filesort —— 这时候需要直方图或强制索引，而不是调内存。