5.3 排序与分组优化

5.3 排序与分组优化

本节摘要：ORDER BY 与 GROUP BY 是报表类查询的性能枢纽：吃到索引就是顺序读，吃不到就要 filesort 或临时表。本节讲清 MySQL 两种排序实现的机理、索引消化排序的条件、以及分组查询的改写手法。位置：WHERE 优化之后的第二个专项，EXPLAIN 的 Extra 两个信号词在这里集中销账。

filesort 不是"文件排序"那么简单

先破一个望文生义的误解：Using filesort 不一定写文件，它是"需要额外排序步骤"的统称。排序有两种实现。方案一：索引序——如果排序列恰好能吃到索引的有序性，MySQL 沿索引顺序读，零排序成本。方案二：排序算法——把待排序的行集放进 sort_buffer，按 sort_key 排好再返回；buffer 装不下就分批排、写临时文件、再归并，这时才是真磁盘动作。

sort_buffer 有两种模式。老版本的双向搬运是"回表取排序列 + 主键，排序后再逐个回表取整行"（两次扫描）；单次搬运则把整行读进 buffer 一次排完（一次扫描但 buffer 吃内存更多）。优化器按行宽自动选择，你能控制的是别让排序集合变大——WHERE 先过滤、LIMIT 早截断、SELECT 列别贪多。

索引怎么消化排序

延续 4.3 的推导：索引 (customer_id, created_at) 的叶子按"先客户、再时间"排好，因此：

-- 能消化：等值前缀 + 排序列在尾部，天然有序
SELECT order_id FROM orders
WHERE customer_id = 10086
ORDER BY created_at DESC LIMIT 20;
-- Extra: Backward index scan（倒序走索引）无 filesort

-- 能消化：ORDER BY 就是索引列本身
SELECT order_id FROM orders ORDER BY order_id LIMIT 20;

-- 不能消化：排序列与索引序脱节
SELECT order_id FROM orders
WHERE status = 2
ORDER BY created_at DESC LIMIT 20;
-- status 命中的行在 (customer_id, created_at) 树上散布 → filesort

-- 不能消化：排序方向不一致（老版本限制）
ORDER BY created_at ASC, amount DESC
​

第三例的修法在 4.3 演练过：建 (status, created_at) 让排序列接在等值列后。第四例（混合方向）在 8.0 之后有了新武器——降序索引：CREATE INDEX idx_c ON orders (created_at DESC, amount DESC)，索引物理上就按这个方向排，混合方向排序也能吃到索引序。

演练：报表分组查询的两次瘦身

背景：按天统计各仓库入库量的报表接口 6 秒。语句：

SELECT DATE(created_at) AS stat_day, warehouse_id, SUM(quantity)
FROM inbound
WHERE created_at >= '2026-08-01'
GROUP BY DATE(created_at), warehouse_id;
​

第一刀，EXPLAIN 显示 Using temporary; Using filesort：按函数结果分组，索引帮不上，临时表born。改写——加一列冗余的 stat_day（DATE 类型，写入时定格，属第 2 章反范式的"当时事实"），并对它和 warehouse_id 建复合索引 (stat_day, warehouse_id)：

ALTER TABLE inbound
    ADD COLUMN stat_day DATE NOT NULL,
    ADD INDEX idx_stat (stat_day, warehouse_id);
-- 回填历史：UPDATE inbound SET stat_day = DATE(created_at);（分批）

SELECT stat_day, warehouse_id, SUM(quantity)
FROM inbound
WHERE stat_day >= '2026-08-01'
GROUP BY stat_day, warehouse_id;
​

结果：分组的两列正好是索引前缀，分组即索引序遍历，temporary 与 filesort 双双消失，接口降到 400ms。解读：GROUP BY 吃索引的条件与 ORDER BY 同源——分组列要构成索引前缀；条件允许时把"按表达式分组"转成"按列分组"，是报表表设计的常规动作。变式：若维度组合爆炸（几十个维度的 GROUP BY），单表索引顶不住，那是数据仓库和预聚合表的领地，别在业务库硬扛。

DISTINCT 与 LIMIT 的位置

DISTINCT 本质是分组去重，同样可能触发临时表；SELECT DISTINCT customer_id FROM orders 若有 (customer_id) 索引则直接扫索引去重，代价很小。LIMIT 在排序场景的关键作用是提前截断：带 LIMIT 的 ORDER BY 只需维护前 N 名（优先队列），sort_buffer 压力骤降。所以导出接口再大也别裸奔，分页常驻。

易错点与评审清单

• 为了去重随手加 DISTINCT：先想清楚为什么会出现重复行——通常是 JOIN 放大（3.4 的行数放大陷阱），修 JOIN 比盖 DISTINCT 更治本；
• HAVING 当 WHERE 用：HAVING 在分组后执行，先能 WHERE 过滤的别留到 HAVING，白白扩大分组集合；
• 排序列 NULL 值掺和：NULL 在排序中的位置各数据库不同（MySQL 排最前或最后视方向），业务排序需求要显式测试，别赌；
• sort_buffer 调大万能论：它是连接级参数，调大会放大并发内存占用（第 7 章参数课细讲），优先减数据量而不是加内存。

要点回顾：filesort 是"额外排序"统称，装不进 buffer 才落盘；索引消化排序的条件是排序列接在等值前缀后；8.0 降序索引解决混合方向；GROUP BY 吃索引需构成前缀，表达式分组转列分组；LIMIT 提前截断是免费午餐。专项病灶清完，下一节把散点的优化纳入慢查询日志的长效监控。