MySQLNotes

第 14 章：排序、分页与临时表

zjc 于 2026-01-14 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 很多慢查询不是找不到数据，而是找到之后还要排序、去重、临时存储和传输。本章分析 filesort、GROUP BY、DISTINCT、UNION、深分页和磁盘临时表的治理方法。

14.1 排序发生在哪里

如果 ORDER BY 能复用索引顺序，MySQL 可以按索引读取并边读边输出：

Index ordered scan -> 直接返回

如果不能复用索引，需要额外排序：

读取数据 -> 保存排序键和行指针 -> 排序 -> 返回结果

执行计划通常显示：

Using filesort

filesort 不一定真的使用磁盘文件，小结果可能在内存完成。真正要关注的是参与排序的行数和行大小。

14.2 ORDER BY 使用索引的条件

索引：

KEY idx_user_created (user_id, created_at)

可复用：

SELECT id, user_id, created_at
FROM orders
WHERE user_id = 1001
ORDER BY created_at;

不能稳定复用：

SELECT *
FROM orders
ORDER BY created_at;

SELECT *
FROM orders
WHERE user_id > 1000
ORDER BY created_at;

SELECT *
FROM orders
WHERE user_id = 1001
ORDER BY created_at, status;

条件：

  1. ORDER BY 列是索引连续后缀；
  2. 前导列有等值条件；
  3. 排序方向一致；
  4. 范围条件可能影响后续列复用；
  5. 查询列不能破坏可用路径；
  6. 需要唯一列补足稳定排序。

14.3 filesort 过程

查看排序参数：

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'max_length_for_sort_data';
SHOW STATUS LIKE 'Sort%';

常见状态：

状态 含义
Sort_merge_passes 多路归并次数
Sort_range 范围扫描后排序次数
Sort_scan 全表或索引扫描后排序次数
Sort_rows 参与排序行数

如果 Sort_merge_passes 持续高，说明排序超过内存缓冲并发生归并。优先减少排序行数，再考虑调整 sort_buffer_size。

不建议无依据地把 sort_buffer_size 设得极大：

  1. 每个会话可能分配；
  2. 占用内存；
  3. 影响并发；
  4. 收益取决于查询模式；
  5. 应通过压测确定。

14.4 深分页问题

慢 SQL：

SELECT *
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1000000;

MySQL 需要产生并丢弃前 100 万行。

游标分页：

SELECT *
FROM orders
WHERE (created_at, id) < ('2026-08-25 10:00:00', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 20;

行构造器写法等价于：

SELECT *
FROM orders
WHERE created_at < '2026-08-25 10:00:00'
   OR (
       created_at = '2026-08-25 10:00:00'
       AND id < 123456
   )
ORDER BY created_at DESC, id DESC
LIMIT 20;

要求有排序索引：

KEY idx_created_id (created_at, id)

14.5 延迟关联

当深分页无法使用游标时，可以先在索引中定位主键，再回表：

SELECT o.*
FROM orders o
JOIN (
  SELECT id
  FROM orders
  WHERE user_id = 1001
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 100000
) p ON p.id = o.id;

它仍然要扫描偏移行，但排序和偏移阶段使用更窄的索引行，减少回表和大字段传输。适合后台导出或兜底方案，不能替代游标分页。

14.6 LIMIT 与 COUNT

LIMIT 不改变扫描前的数据规模，只限制返回：

SELECT *
FROM orders
WHERE status = 'CREATED'
LIMIT 10;

如果符合条件的行很多，找到前 10 条可能很快；如果没有匹配，仍可能扫描完整个范围。

统计数量：

SELECT COUNT(*)
FROM orders
WHERE status = 'CREATED';

大表实时 COUNT 需要评估。常见方案：

  1. 分页展示“下一页”，不展示总数；
  2. 使用近似值；
  3. 预聚合计数表；
  4. 只统计最近时间窗；
  5. 历史统计进入报表库；
  6. 使用缓存并定期校准。

14.7 GROUP BY 与临时表

可能使用临时表：

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

是否能走松散索引扫描取决于索引和统计方式：

KEY idx_status_created (status, created_at)

相关算法：

算法 说明
Loose Index Scan 利用索引跳跃读取分组起始点
Tight Index Scan 按索引范围扫描并顺序聚合
Temporary Table 无法按索引顺序分组时构建临时表

查看：

EXPLAIN
SELECT status, COUNT(*)
FROM orders
GROUP BY status;

优化：

  1. 过滤条件前置；
  2. 分组列使用索引前缀；
  3. 减少 SELECT 字段；
  4. 拆分复杂统计；
  5. 预聚合；
  6. 迁移分析库。

MySQL 8.0 的 GROUP BY 不再默认隐式排序。如果业务需要输出有序，必须显式 ORDER BY。

14.8 DISTINCT 与去重

示例：

SELECT DISTINCT merchant_id
FROM orders
WHERE status = 'PAID';

优化器可能用索引或临时表去重。不要把 DISTINCT 当成数据质量工具。如果业务要求唯一，应通过唯一索引和清洗任务保证。

窗口去重：

WITH ranked AS (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY order_no
           ORDER BY id DESC
         ) AS rn
  FROM payment_records t
)
SELECT *
FROM ranked
WHERE rn = 1;

适合保留一条重复记录的查询，不能替代唯一约束。

14.9 UNION 与临时表

SELECT user_id FROM orders
UNION
SELECT id FROM users;

UNION 需要去重，可能使用临时表。没有去重需求时使用：

SELECT user_id, 'ORDER' AS source FROM orders
UNION ALL
SELECT id, 'USER' AS source FROM users;

外层 ORDER BY 作用于 UNION 结果，不是只作用于最后一个分支：

SELECT order_no FROM orders
WHERE user_id = 1001
UNION ALL
SELECT payment_no FROM payment_records
WHERE order_no IN (
  SELECT order_no FROM orders WHERE user_id = 1001
)
ORDER BY order_no
LIMIT 20;

14.10 临时表机制

查看临时表使用：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

常见来源：

  1. GROUP BY 无法按索引顺序完成；
  2. DISTINCT 去重；
  3. UNION 去重；
  4. 派生表物化；
  5. 子查询物化；
  6. IN 优化；
  7. 复杂表达式缓存；
  8. 窗口函数中间结果。

内存限制：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'temptable_max_ram';

磁盘临时表增多的治理顺序：

1. 减少参与行数；
2. 减少参与列宽；
3. 让 GROUP BY / ORDER BY 复用索引；
4. 拆分复杂 SQL；
5. 预聚合；
6. 迁移分析负载；
7. 再评估参数和硬件。

14.11 排序分页案例

案例一：列表接口

原 SQL：

SELECT *
FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20 OFFSET 5000;

游标：

SELECT *
FROM orders
WHERE user_id = 1001
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

索引：

KEY idx_user_created (user_id, created_at)

案例二：后台导出

不要一次查询百万行。任务化处理：

1. 按时间或主键分片；
2. 每片 1000 到 5000 行；
3. 使用游标条件；
4. 写入文件或对象存储；
5. 记录任务进度；
6. 支持断点续跑；
7. 控制对在线库的影响。

案例三：复杂报表

原 SQL 同时做：

十表 JOIN + 多级 GROUP BY + DISTINCT + ORDER BY + OFFSET

改造：

  1. 明确指标口径；
  2. 抽取基础明细宽表；
  3. 分钟级或小时级预聚合；
  4. 查询预聚合表；
  5. 历史数据进入 OLAP；
  6. 在线库只保留近期热点查询。

本章小结

排序优化的关键是让 ORDER BY 和 GROUP BY 复用索引顺序，无法复用时要减少参与排序的行数和列宽。深分页优先使用稳定游标，延迟关联只是兜底。UNION、DISTINCT、派生表和聚合都可能触发临时表，磁盘临时表持续增长时应先优化查询结构，再评估参数和硬件。

思考题

  1. 什么条件下 ORDER BY 可以避免 filesort？
  2. Using filesort 是否一定代表性能问题？
  3. 深分页为什么慢？游标分页为什么必须包含唯一列？
  4. 延迟关联适合什么场景？它有什么局限？
  5. 排查一个磁盘临时表增长问题，写出完整优化流程。