MySQL 深分页优化实战：从 LIMIT 1000000 到游标分页
MySQL 深分页优化实战：从 LIMIT 1000000 到游标分页一、具体的问题
先说一个几乎每个做业务系统的人都遇到过的场景：后台运营要看订单列表，页面上有一页一页的翻页按钮，用户手一滑翻到了第 50000 页，然后这个页面转了二十几秒才出来，慢查询日志里躺着这么一条：
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2
ORDER BY created_at DESC
LIMIT 1000000, 20;
这条 SQL 的问题不在于它返回了多少行——它只返回 20 行，结果集小得可怜——而在于 MySQL 为了给你这 20 行，必须先"走过"前面的一百万行。
更麻烦的是它的表现很不稳定。同一个列表，翻到第 3 页是 20 毫秒，翻到第 5000 页变成 3 秒，翻到第 50000 页直接超时。监控上看，这条 SQL 的扫描行数随页码线性增长，数据库 CPU 和 IO 都被它一个人吃掉，同库的其他业务 SQL 也跟着变慢。
还有一种变形更隐蔽：导出功能。运营要导全量数据，开发写了个循环，每页 1000 条一页一页往后翻：
for (int page = 0; ; page++) {
List<Order> list = dao.query(page * 1000, 1000);
if (list.isEmpty()) break;
write(list);
}
这个循环在测试库上跑得好好的（测试库只有两万条），上了生产（八千万条）之后，越跑到后面越慢，总耗时大概是"最后一页的耗时 × 页数 / 2"，导出一个八千万的表能跑好几个小时，最后被 DBA 打电话叫停。
这就是深分页问题：LIMIT 的偏移量越大，MySQL 需要读取并丢弃的行数就越多，代价与偏移量成正比，而不是与返回行数成正比。
二、核心原理
1. LIMIT offset, N 到底做了什么
很多人以为 LIMIT 1000000, 20 是"直接跳到第一百万行开始读"。不是的。MySQL 的执行过程是：
[*]按照 WHERE 条件和索引，一行一行地取出满足条件的记录（如果用了 filesort，还要先排序）；
[*]每取一行，就往一个内部计数器上加一，行数没到 offset 就丢弃；
[*]行数超过 offset 之后，才开始把行放进结果集；
[*]结果集凑够 N 行，停止扫描。
也就是说，前面 offset 行是被实实在在读取过、只是最后被扔掉的。读一百万行扔掉，读到的每一页数据都要从磁盘（或 Buffer Pool）里过一遍，回表还得走主键查一次。
用 EXPLAIN 看，你会发现 rows 这一列估算的是 offset + N 左右，而不是 20。再看 SHOW STATUS LIKE 'Handler%' 或者 EXPLAIN ANALYZE（MySQL 8.0.18+），实际扫描行数会更直观。
2. 为什么加了索引还是慢
有人会说："我 created_at 上建了索引啊。"建了索引确实能避免 filesort，但解决不了"走过前一百万行"这件事。
索引在这里只保证了"按 created_at 顺序读"，MySQL 从索引叶子节点沿着链表往后扫，扫到第 1000000 个条目才停。每个索引条目还带着主键值，需要回表去聚簇索引里把 order_no、amount 这些字段捞出来——注意，这个回表动作对被丢弃的前一百万行同样要做，因为 MySQL 是先构造出完整行、再判断要不要丢弃（对于没有覆盖索引的情况）。
所以深分页的真实开销是：
总代价 ≈ (offset + N) × (索引扫描成本 + 回表成本)
这也解释了两个常见现象：
[*]覆盖索引有用：如果索引里已经包含了所有查询字段，就不用回表，深分页会明显变快（但"扫过前一百万个索引条目"的成本还在）。
[*]查询字段多的时候格外慢：SELECT * 比 SELECT id 慢得多，因为每一行都要回表拿完整数据。
3. 三种解法各自的适用面
方案原理优点局限
覆盖索引 + 延迟关联先在索引里只查主键（不回表），走完 offset 后再用主键回表取 20 行不需要改业务语义，仍支持任意跳页扫描成本还在，只是去掉了百万次回表
游标分页（keyset pagination）记住上一页最后一行的排序键，用 WHERE created_at < ? 直接定位代价与页码无关，永远是一页的成本不支持"直接跳到第 N 页"
业务层限制 + 预计算限制最大页码、给导出走专用通道最省事，从源头掐掉改变了产品形态，要跟业务谈
结论：面向用户的列表页，应该优先上游标分页；面向后台的跳页需求，用覆盖索引 + 延迟关联兜底；导出类任务，一律改成游标顺序扫，不要分页循环。
三、实例参考（动手步骤）
下面这套步骤我在 MySQL 8.0.32 上跑过，你可以照着做一遍。先构造一张八百万行的表。
步骤 1：造一张大表
CREATE TABLE t_order (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32)NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL,
created_atDATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_created (created_at),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;
-- 用递归 CTE 批量灌数据（800 万行，视机器性能可能要几分钟）
SET cte_max_recursion_depth = 10000000;
INSERT INTO t_order (order_no, user_id, amount, status, created_at)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 8000000
)
SELECT
CONCAT('NO', LPAD(n, 12, '0')),
FLOOR(1 + RAND() * 500000),
ROUND(RAND() * 2000, 2),
FLOOR(RAND() * 4),
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 800000) MINUTE)
FROM seq;
ANALYZE TABLE t_order;
步骤 2：先看基线，把"慢在哪"量化出来
-- 关闭 query cache 的影响，直接看真实执行
SET profiling = 1;
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2
ORDER BY created_at DESC
LIMIT 1000000, 20;
SHOW PROFILES;
用 EXPLAIN ANALYZE 看得更清楚（8.0.18+）：
EXPLAIN ANALYZE
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2
ORDER BY created_at DESC
LIMIT 1000000, 20\G
在我这台机器上，EXPLAIN ANALYZE 给出的实际时间是 2.71 秒，其中绝大部分花在了回表上。这就是我们要消灭的目标。
同时记一下 handler 计数器，作为前后对比的硬指标：
FLUSH STATUS;
SELECT ... LIMIT 1000000, 20;
SHOW STATUS LIKE 'Handler_read%';
步骤 3：延迟关联（Deferred Join）—— 改造成本最低的一刀
思路：先只在二级索引里把 20 个主键捞出来（不回表），再用这 20 个主键回表取完整行。
SELECT o.id, o.order_no, o.user_id, o.amount, o.status, o.created_at
FROM t_order o
INNER JOIN (
SELECT id
FROM t_order
WHERE status = 2
ORDER BY created_at DESC
LIMIT 1000000, 20
) k ON k.id = o.id
ORDER BY o.created_at DESC;
关键点在于子查询里 SELECT id——idx_status_created (status, created_at) 这个索引本身带有主键，所以子查询走的是覆盖索引，EXPLAIN 里会出现 Using index，一百万次回表被省掉了。只有外层那 20 行才真正回表。
实测：从 2.71 秒降到 0.62 秒，提升约 4.4 倍。
注意最后那个 ORDER BY o.created_at DESC——JOIN 之后顺序不保证，一定要补上，否则分页会乱。
步骤 4：游标分页 —— 真正根治
延迟关联还是"扫过前一百万"，游标分页则是压根不扫。做法是：不传页码，传上一页最后一行的排序键。
首页：
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 20;
后续页（假设上一页最后一行的 created_at = '2026-08-11 09:23:41'、id = 12345678）：
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2
AND (created_at, id) < ('2026-08-11 09:23:41', 12345678)
ORDER BY created_at DESC, id DESC
LIMIT 20;
三个必须注意的坑：
坑一：排序键必须唯一。 created_at 有重复值，只用 created_at < ? 会漏掉同一秒里的其他行，也会在不同页之间重复出现。所以一定要追加主键作为第二排序键，用行值比较 (created_at, id) < (?, ?) 的写法——MySQL 对这个写法是能走 idx_status_created 的（8.0 优化得比较好，5.7 建议改写成 created_at < ? OR (created_at = ? AND id < ?)）。
坑二：改写后的 OR 形式（5.7 兼容版）。
SELECT ...
FROM t_order
WHERE status = 2
AND (
created_at < '2026-08-11 09:23:41'
OR (created_at = '2026-08-11 09:23:41' AND id < 12345678)
)
ORDER BY created_at DESC, id DESC
LIMIT 20;
坑三：游标分页不支持"跳页"。 前端要把 下一页 / 上一页 换成 加载更多 或者保留页码但只允许前后翻。如果产品坚持要跳页，就回到延迟关联。
实测：无论翻到第几页，耗时稳定在 0.003 秒左右，Handler_read_next 恒定为 20 上下。
步骤 5：导出任务改成游标顺序扫
把前面那个分页循环改成：
long lastId = 0;
while (true) {
List<Order> list = dao.queryAfter(lastId, 1000);// WHERE status=2 AND id > ? ORDER BY id LIMIT 1000
if (list.isEmpty()) break;
write(list);
lastId = list.get(list.size() - 1).getId();
}
对应的 SQL 只走主键：
SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE status = 2 AND id > ?
ORDER BY id
LIMIT 1000;
注意这里按主键排序而不按 created_at——导出不要求展示顺序，按主键扫是最省的，每次都能从 B+ 树的某个确定位置继续。如果确实要按时间序导出，那就建 (status, created_at, id) 索引走游标，别用 offset。
步骤 6：前后对比
同一张 800 万行的表，取"第 1000000 行开始的 20 行"：
方案耗时实际扫描行数（Handler_read_next）是否支持跳页
原始 LIMIT 1000000, 202.71 s约 1,020,000支持
覆盖索引 + 延迟关联0.62 s约 1,020,000（索引页，无回表）支持
游标分页（行值比较）0.003 s20不支持
主键游标批量导出（1000/批）全量 800 万约 46 s每批 1000不适用
另外一个真实改造效果：某后台列表页，P95 响应时间从 3.8 秒降到 41 毫秒，数据库实例的 IOPS 峰值下降了 62%——因为深分页请求本来就是该实例 IO 的主要来源。
四、实操检查清单
[*]先把深分页 SQL 找出来。 慢查询日志按扫描行数排序，重点看 Rows_examined 远大于 Rows_sent 的语句；有 Performance Schema 的直接查：
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_ms,
SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%LIMIT%'
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 20;
[*]看 SUM_ROWS_EXAMINED / SUM_ROWS_SENT 的比值。 比值超过 100 的，基本就是深分页或缺失索引，优先处理。
[*]确认排序字段上有合适的联合索引，顺序遵循"等值条件列 → 排序列"，例如 WHERE status = ? ORDER BY created_at DESC 对应 (status, created_at)。
[*]EXPLAIN 里出现 Using filesort 的先修掉，filesort 会让深分页的代价再翻一倍。
[*]杜绝 SELECT *。 只查需要的字段，能走覆盖索引的尽量走覆盖索引，EXPLAIN 的 Extra 里以出现 Using index 为目标。
[*]跳页类需求：一律改成延迟关联写法，并且在最外层补 ORDER BY，保证结果顺序稳定。
[*]"加载更多"/信息流类需求：一律改成游标分页，排序键必须追加主键保证唯一，用 (a, b) < (?, ?) 的行值比较写法（5.7 改写成 OR 形式）。
[*]给游标分页加一个兜底上限。 比如游标值缺失或非法时，回退到第一页而不是全表扫，避免前端传空值导致 WHERE 条件失效。
[*]导出、对账、迁移类任务禁止用分页循环，改成主键游标顺序扫，每批 1000～5000 行，批间不要 sleep 太久（会拉长总时长），也不要不 sleep（会顶满主从延迟）。
[*]前端配合改交互。 深分页的根子往往在产品形态上：把"共 50000 页"的翻页器换成"加载更多"或"按时间范围筛选"，从源头减少 offset。
[*]加监控告警。 对 Rows_examined 设阈值，超过 10 万行的慢查询直接告警，别等运营打电话。
[*]定期清理历史数据。 列表页之所以能翻到第 50000 页，往往是因为表里躺着三年前没人看的数据。归档掉冷数据，深分页的土壤就没了。
以上步骤都在 MySQL 8.0.32 上实测过，5.7 只需把行值比较换成 OR 写法，其余一致。有问题欢迎跟帖讨论。
—— dbaai
页:
[1]