十八岁的不忘初心 成长笔记

MySQL 笔记：order by 与 Using filesort 的优化思路

· Mysql笔记 MySQL 索引 SQL优化 · 2 阅读 · 约 5 分钟

前言

执行计划里看到 Extra: Using filesort，第一反应通常是"要读写磁盘临时文件了"。其实不是——这个名字只说明"要靠排序算法把行排出来，没直接借用索引顺序"，数据量小的时候全程在内存里完成，根本不碰磁盘。

两种取数策略

优化器面对带 ORDER BY 的查询有两条路：

  1. 按索引顺序读：索引里的有序性正好是需要的顺序，读完就是排好的，没有额外排序
  2. 先取行再排序：走别的索引或全表扫描取数据，然后在 sort buffer 里排

第 2 条就是 filesort。它内部分两种算法：

  • 全字段排序：把要返回的列一起放进 sort buffer 排序，排完直接输出。字段少时最快
  • rowid 排序：sort buffer 里只放排序列加主键，排完序再按主键回表取其余列。行很宽（有大字段）时用它减少内存占用，代价是多一轮随机 IO

走哪种由 max_length_for_sort_data 和字段类型决定（本机 5.7 该值 1024 字节）。缓冲不够就落盘做归并，这时才真的产生临时文件：

SHOW VARIABLES WHERE Variable_name IN
    ('sort_buffer_size', 'max_length_for_sort_data', 'read_rnd_buffer_size', 'tmpdir');

sort_buffer_size 是会话级、按排序操作分配的，不要盲目调大，几百 KB 到 1、2 MB 量级比较常见。

什么情况下索引能免排序

以 KEY idx_s_c_v (status, category, views) 为例：

-- 能：WHERE 用掉最左列，ORDER BY 接着后面的列
EXPLAIN SELECT id FROM article WHERE status = 1 ORDER BY category, views;

-- 能：单表扫描直接用索引顺序
EXPLAIN SELECT id FROM article FORCE INDEX (idx_s_c_v) ORDER BY status, category;

-- 不能：跳过了中间列
EXPLAIN SELECT id FROM article WHERE status = 1 ORDER BY views;

-- 不能：排序列不在任何合适索引里
EXPLAIN SELECT id FROM article ORDER BY title;

-- 不能：5.7 索引只能单向遍历，升降序混用
EXPLAIN SELECT id FROM article WHERE status = 1 ORDER BY category ASC, views DESC;

规律和最左前缀完全一致：WHERE 的等值条件加 ORDER BY 的列，必须构成索引列的连续前缀，方向也要一致。5.7 还保留 prefer_ordering_index 这个开关，优化器有时宁可扫一个能免排序的索引而放弃更精确的过滤索引，数据量大的时候这个选择未必划算。

排序失效的几种写法

ORDER BY YEAR(created_at) DESC        -- 列上套函数
ORDER BY views + 1 DESC               -- 表达式
ORDER BY CONCAT(a, b)                 -- 表达式
ORDER BY RAND()                       -- 随机取几条，只能全排
SELECT ... FROM a JOIN b ORDER BY b.name   -- 跨表排序

前三条要么建生成列加索引（5.7 支持），要么把函数挪到条件侧；ORDER BY RAND() 换成先取主键范围再应用层随机；跨表排序基本只能靠驱动表按被排序表的索引顺序走，通常做不到，接受它或者把排序挪到业务层。

观测与优化

排序是否走了磁盘，看状态计数器：

SHOW GLOBAL STATUS LIKE 'sort%';

Sort_merge_passes 持续增长说明归并次数多，即排序数据装不进 sort buffer；Sort_scan、Sort_range 记录由扫描和范围查询触发的排序次数，Sort_rows 是参与排序的总行数。

优化顺序一般是：

  1. 建一个把 WHERE 等值列和 ORDER BY 列串起来的联合索引
  2. 让查询列被索引覆盖，减小排序时的行宽
  3. 分页场景把大字段延后取（先查 id，再按 id 取内容）
  4. 只在确认是缓冲不够时才动 sort_buffer_size

小结

  • Using filesort 是"额外排序"，不等于磁盘临时文件
  • 索引免排序的条件与最左前缀同源，方向也必须一致
  • 行很宽时 MySQL 会用 rowid 排序换内存，多一轮回表
  • 先加对索引，再谈缓冲参数，顺序别反

同分类其他笔记