祥哥的技术博客

主题

📅 2025年1月15日 🏷️ MySQL ⏱️ 10 分钟阅读 MySQL

深分页与 ORDER BY 优化：文件排序 vs 索引排序 ​

问题 ​

后端开发中，SELECT * FROM t ORDER BY create_time DESC LIMIT 100000, 20 这条 SQL 为什么越到后面越慢？MySQL 的 ORDER BY 什么时候走索引排序、什么时候走文件排序（filesort）？深分页 + 排序的场景到底怎么优化？

分析 ​

ORDER BY 的两种排序方式 ​

MySQL 的排序有两种实现路径：

1. 索引排序（Using index）

当 ORDER BY 的字段恰好是索引的组成部分，且满足最左前缀时，MySQL 可以直接按索引顺序扫描数据，无需额外排序。EXPLAIN 的 Extra 字段会显示 Using index。

sql
-- 假设有联合索引 (status, create_time)
-- 这个查询可以直接走索引排序
EXPLAIN SELECT id, status, create_time 
FROM t_order 
WHERE status = 1 
ORDER BY create_time DESC;

2. 文件排序（Using filesort）

当 ORDER BY 字段无法利用索引时，MySQL 需要先把数据读取到内存（sort_buffer）或临时文件中排序，再返回结果：

sql
-- ORDER BY 的字段在索引中不存在，必须文件排序
EXPLAIN SELECT * FROM t_order 
ORDER BY amount DESC 
LIMIT 100;

文件排序内部原理：两种算法 ​

文件排序这个名字容易误导——它不一定把数据写到磁盘文件。filesort 是 MySQL 内部排序器的统称，排序过程完全在内存中完成时，Extra 仍然显示 Using filesort。

单行排序（Single-Pass / 全字段排序）

把查询需要的所有字段都读入 sort_buffer，排序后直接返回。MySQL 8.0 默认走这个路径。

内存布局（sort_buffer）：
┌─────────┬──────┬──────────┬───────────┬──────────┐
│ amount  │ id   │ order_no │ create_ts │ status   │
├─────────┼──────┼──────────┼───────────┼──────────┤
│ 299.00  │ 1001 │ ORD001   │ 2026-07-01│ 1        │
│ 150.00  │ 1002 │ ORD002   │ 2026-07-02│ 2        │
│ ...     │ ...  │ ...      │ ...       │ ...      │
└─────────┴──────┴──────────┴───────────┴──────────┘
→ 按 amount 快排 → 直接返回

优点：不回表。缺点：一行数据占用的 sort_buffer 空间大，同样大小的 buffer 能装的行数少。

双行排序（Two-Pass / 排序字段 + 主键）

当一行数据超过 max_length_for_sort_data（MySQL 8.0 已废弃此参数，改用排序字段总长度自动判断）时，只读入排序字段和主键到 sort_buffer，排序后再回表取完整数据。

第一趟（sort_buffer）：
┌─────────┬──────┐
│ amount  │ id   │
├─────────┼──────┤
│ 299.00  │ 1001 │
│ 150.00  │ 1002 │
│ ...     │ ...  │
└─────────┴──────┘
→ 按 amount 快排 → 得到有序的 id 列表

第二趟：
→ 按有序 id 回表取完整行（每行一次随机 IO）

优点：sort_buffer 能装更多行，减少磁盘临时文件。缺点：多一次回表 IO。

什么时候触发磁盘临时文件？

当 sort_buffer 装不下所有排序数据时，MySQL 把数据分片写到磁盘临时文件，归并排序：

sql
-- 查看是否使用了磁盘临时文件
SHOW STATUS LIKE 'Sort_merge_passes';
-- Sort_merge_passes > 0 说明 sort_buffer 不够大，触发了一轮或多轮归并

每条记录大小 = 排序字段长度 + 行指针(主键)长度 + 额外字段。sort_buffer 默认 256KB，如果每条记录 200 字节，约能装 1310 行。500 万行排序时，需要 5000000 / 1310 ≈ 3817 个分片，每个分片排序后写磁盘，再归并。

实战经验： 我见过一个定时报表导出任务，ORDER BY create_time LIMIT 500000, 10000，sort_buffer 256KB 导致 Sort_merge_passes 飙升到 3000+，每次跑 47 秒。把 sort_buffer_size 调到 4MB 后降到 12 秒——没有减少扫描行数，但减少了归并轮次。

深分页 LIMIT 为什么越来越慢 ​

LIMIT 100000, 20 的本质是：MySQL 会扫描 100020 行，然后丢弃前 100000 行，只返回最后 20 行。扫描行数随偏移量线性增长：

sql
-- 偏移量越大，扫描行数越多
-- 100 万行数据，各种偏移量下的扫描行数实测：
-- LIMIT 0, 20     → 扫描 20 行    → 0.3ms
-- LIMIT 10000, 20 → 扫描 10020 行 → 12ms
-- LIMIT 100000, 20 → 扫描 100020 行 → 120ms
-- LIMIT 500000, 20 → 扫描 500020 行 → 620ms

-- 每多一次偏移量，就多一次无意义的 IO
SELECT * FROM t_order 
ORDER BY id 
LIMIT 100000, 20;

如果再加上 ORDER BY 非索引字段，不仅扫描行数多，还要额外做文件排序，性能雪上加霜。

代码示例 ​

场景：订单列表分页，按创建时间倒序 ​

假设 t_order 表有 500 万行，业务需要按创建时间倒序分页显示。

不优化的写法（深分页 + 文件排序）：

sql
-- 第 10001 页，每页 20 条
-- 偏移量 200000，扫描 200020 行，再文件排序
-- 实测：500 万行数据，耗时约 8-12 秒
SELECT * FROM t_order 
ORDER BY create_time DESC 
LIMIT 200000, 20;

优化方案一：延迟关联（Deferred Join）

先通过覆盖索引快速定位主键，再回表取完整数据：

sql
-- 1. 覆盖索引扫描，只返回主键（不需要回表）
-- 2. 再关联回表取完整数据（只取 20 行）
SELECT t.* 
FROM t_order t 
INNER JOIN (
    SELECT id 
    FROM t_order 
    ORDER BY create_time DESC 
    LIMIT 200000, 20
) AS tmp ON t.id = tmp.id
ORDER BY t.create_time DESC;
执行流程时序：
┌──────────────────────────────────────────────────┐
│ 客户端                                              │
│  SELECT t.* FROM t_order t JOIN (子查询) ON t.id=id │
└──────────────┬───────────────────────────────────┘
               │
               ▼
┌──────────────────────────────────────────────────┐
│ 子查询：SELECT id FROM t_order ORDER BY create    │
│   走 (create_time,id) 覆盖索引                    │
│   扫描 200020 个索引条目（全在内存级索引页）       │
│   返回 20 个 id                                   │
└──────────────┬───────────────────────────────────┘
               │ 20 个 id
               ▼
┌──────────────────────────────────────────────────┐
│ 外层：回表 20 次（聚簇索引精确查找，每次 1 次 IO）   │
└──────────────────────────────────────────────────┘

核心原理：子查询的 SELECT id 可以利用 (create_time, id) 覆盖索引，索引扫描 200020 行（但只需扫描索引页，不需要回表），外层只回表 20 行。相比原始写法减少 99.99% 的回表 IO。

实测对比（500 万行数据，SSD，MySQL 8.0）：

查询方式偏移量 100000偏移量 500000偏移量 1000000
原始写法规避120ms620ms1.4s
延迟关联8ms12ms15ms
提升倍数15x51x93x

优化方案二：游标分页（Cursor Pagination）

基于上一页的最后一条记录做条件，彻底消除偏移量：

sql
-- 第一页：正常查询
SELECT id, create_time, order_no, amount 
FROM t_order 
ORDER BY create_time DESC, id DESC 
LIMIT 20;

-- 第二页：传入上一页最后一条记录的 create_time 和 id
-- 利用 (create_time, id) 联合索引直接定位
SELECT id, create_time, order_no, amount 
FROM t_order 
WHERE (create_time, id) < ('2026-07-19 22:30:00', 1000456) 
ORDER BY create_time DESC, id DESC 
LIMIT 20;

游标分页的排序稳定性：

如果排序字段有重复值（如相同的时间戳），必须加主键作为第二排序字段。

踩坑案例： 某次上线后，用户反馈"翻页时明明看到第 1 页有条记录，第 2 页又出现了，第 3 页又没了"。排查发现 ORDER BY 只用了 create_time，而当时并发批量导入导致 3 秒内创建了 500 条记录的 create_time 完全相同。游标分页按 create_time < last_value 定位，相同时间的记录会被"漏"掉一些，也会被重复包含。

sql
-- 错误：create_time 有重复值时，分页结果可能重复或丢失
WHERE create_time < '2026-07-19 22:30:00' 
ORDER BY create_time DESC 
LIMIT 20;

-- 正确：加上主键作为第二排序字段，保证结果唯一且稳定
WHERE (create_time, id) < ('2026-07-19 22:30:00', 1000456) 
ORDER BY create_time DESC, id DESC 
LIMIT 20;

优化方案三：子查询定位起始点

sql
-- 先用子查询找到第 200001 行的 id，再从这个 id 往后取 20 行
SELECT * FROM t_order 
WHERE id >= (
    SELECT id 
    FROM t_order 
    ORDER BY create_time DESC, id DESC 
    LIMIT 200000, 1
)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

子查询利用覆盖索引定位起始 id，主查询从该 id 开始扫描 20 行，完全跳过前 200000 行的扫描。

文件排序调优参数 ​

sql
-- 查看当前 sort_buffer 大小
SHOW VARIABLES LIKE 'sort_buffer_size';  -- 默认 256KB

-- 查看文件排序的触发次数
SHOW STATUS LIKE 'Sort_merge_passes';    -- 该值持续增长说明 sort_buffer 太小

-- 查看排序方式
EXPLAIN SELECT * FROM t_order ORDER BY amount DESC LIMIT 100;
-- Extra 列显示 Using filesort 即文件排序

sort_buffer_size 调多大合适？

  • 256KB：默认值，适合简单查询，500 万行排序会触发几千次归并
  • 1MB-4MB：大多数 OLTP 场景的平衡点，256KB→4MB 可减少 90%+ 的归并次数
  • 8MB-16MB：适合报表/定时任务场景，但注意每个连接都会分配一个 sort_buffer（并发 100 连接 × 16MB = 1.6GB 内存）
sql
-- 会话级别调整（只影响当前连接）
SET SESSION sort_buffer_size = 4 * 1024 * 1024;  -- 4MB

-- 导出任务专用连接，跑完即释放

索引设计：排序不走索引的常见误区 ​

误区 1：ORDER BY 字段和 WHERE 字段不在同一个索引里

sql
-- 索引 (status, create_time) 存在
-- 但 WHERE 条件用了另一个字段，导致 ORDER BY 走的不是索引
EXPLAIN SELECT * FROM t_order 
WHERE amount > 100            -- amount 不在 (status,create_time) 索引中
ORDER BY create_time DESC      -- 排序字段在索引中，但 WHERE 不满足最左前缀
LIMIT 20;
-- Extra: Using where; Using filesort

误区 2：ORDER BY 方向与索引定义不一致

sql
-- 索引 (status ASC, create_time ASC)
-- ORDER BY 部分字段 DESC 会导致 filesort（除非 MySQL 8.0 支持 DESC 索引）
EXPLAIN SELECT * FROM t_order 
WHERE status = 1 
ORDER BY status ASC, create_time DESC  -- create_time 方向与索引相反
LIMIT 20;
-- Extra: Using where; Using filesort

MySQL 8.0 引入 DESCENDING INDEX 可以解决这个问题：

sql
-- 8.0 创建降序索引
ALTER TABLE t_order ADD INDEX idx_status_ct_desc (status ASC, create_time DESC);

误区 3：SELECT * 导致无法走覆盖索引排序

sql
-- 索引 (create_time, id) 覆盖不了 SELECT * 的所有字段
-- 引擎必须回表拿数据，即使 ORDER BY 走的索引，排序后还要回表
-- 回表行数 = LIMIT 偏移量 + LIMIT 行数，深分页时回表量巨大

总结 ​

优化方案适用场景优点缺点
延迟关联传统页码分页兼容页码跳转，减少回表 IO子查询写法稍复杂，子查询仍扫了偏移量
游标分页信息流/App 列表每次扫描固定行数，性能最优不支持跳页
子查询定位需要页码跳转，且排序字段稳定不依赖上一页数据，语句简单子查询仍扫了偏移量，依赖排序字段唯一性
降序索引8.0 以上，ORDER BY 方向固定从根上消除 filesort需要 8.0，索引增加写负担

面试重点：

  1. 深分页的本质是扫描行数浪费，不是排序本身慢——偏移量 100000 时扫描 100020 行，99.98% 的行被丢弃
  2. 文件排序不一定是磁盘操作，sort_buffer 够大就在内存中完成，Extra 的 Using filesort 只是说明没走索引排序
  3. 游标分页必须加第二排序字段处理重复值，这是面试高频追问点
  4. 延迟关联的收益在深分页场景下随偏移量增大而增大——偏移量越大，相对原始写法的提升倍数越高
  5. 监控入口：performance_schema.events_statements_summary_by_digest 中 rows_examined >> rows_sent 的 SQL 是深分页高发区

生产建议：

  1. 优先用游标分页——如果业务是"下一页"模式（App 列表、信息流），游标分页性能最优，且不受数据量增长影响
  2. 需要跳页时用延迟关联——传统页码分页且无法改为游标时，延迟关联能大幅减少回表 IO
  3. 加索引是最便宜的手段——ORDER BY create_time DESC, id DESC 加上联合索引 (status, create_time, id)，能让排序走索引，避免文件排序。如果排序字段本身就在索引里，延迟关联的收益更大
  4. 不要盲目调大 sort_buffer_size——每个连接独享一份，并发高时内存占用线性增长
  5. 监控预警——在 performance_schema.events_statements_summary_by_digest 中监控 rows_examined 远大于 rows_sent 的 SQL，特别是 rows_examined > 10000 且 rows_sent < 100 的，基本就是深分页问题

参考资料

手撕 → 框架 → 生产化，一步步把 AI Agent 工程化搞透。

Copyright © 2026 祥哥