营销咨询公司招聘,windows优化大师是电脑自带的吗,零食类营销网站怎么做,高性能网站开发一句话导读
ORDER BY 不能走索引时#xff0c;MySQL 会在 Server 层做一次 filesort。内部实现分 单路#xff08;全字段#xff09; 与 双路#xff08;rowid#xff09; 两种#xff1b;了解它们的触发条件、判别方法与调优思路#xff0c;是 SQL 性能优化的必修课。一…
一句话导读
ORDER BY 不能走索引时MySQL 会在 Server 层做一次 filesort。内部实现分 单路全字段 与 双路rowid 两种了解它们的触发条件、判别方法与调优思路是 SQL 性能优化的必修课。一、为什么会有 filesort当查询无法利用 覆盖索引 或 索引顺序 满足 ORDER BY 时MySQL 需要把结果集读出来再排序。这个排序逻辑统称 filesort但它未必落盘绝大多数情况下在内存完成。
二、单路 vs 双路一张图看懂差异阶段单路排序 (Single-Pass)双路排序 (Two-Pass)读取列所有查询列一次性读入 sort buffer只读 排序键 rowid排序对象完整记录排序键, rowid 二元组回表不需要按 rowid 二次回表取整行内存消耗高存整行低只存键idI/O 特征顺序读一次随机读两次典型触发查询列总字节 ≤ max_length_for_sort_data超过阈值或含大 TEXT/BLOB
三、内部流程拆解单路排序扫表/索引 → 把需要的 所有列 拷进 sort_buffer在内存(或磁盘临时文件)里按排序键快排/归并直接返回结果给客户端双路排序只取 排序键 聚簇主键(rowid) 进 sort buffer排序后得到“排好序的 rowid 列表”按 rowid 顺序回表 取其余列 → 返回
四、如何查看 MySQL 使用了哪一种MySQL 不直接写“单路/双路”字样而是把信息藏在 optimizer trace 与 EXPLAIN FORMATjson 里。方法 1EXPLAIN FORMATjsonMySQL 8.0 推荐
EXPLAIN FORMATjson
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 */ -- 单路}
]sort_key, rowid → 双路sort_key, additional_fields 或 sort_key, packed_additional_fields → 单路方法 2optimizer trace所有版本通用
-- 会话级开启
SET optimizer_traceenabledon;
-- 执行目标 SQL
SELECT ... ORDER BY ...;
-- 查看 trace
SELECT * FROM information_schema.optimizer_trace\G搜索关键字
filesort_summary: {sort_mode: sort_key, rowid
}含义同上。方法 3慢查询日志 / performance_schema线上无侵入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 备注字段。初始 SQL
SELECT * FROM orders
WHERE order_date 2025-01-01
ORDER BY total_amount DESC
LIMIT 20;EXPLAIN FORMATjson 看到 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 msCPU 降 90%。
七、结论单路排序 用内存换 I/O适合小字段双路排序 用 I/O 换内存适合大字段通过 EXPLAIN FORMATjson 或 optimizer_trace 查看 sort_mode 即可判定真正的高性能优化是 让排序走索引彻底告别 filesort。一句话看不到 Using filesort才是 ORDER BY 的终极答案。
