导读：列表页用
LIMIT 1000000, 20翻到第 10 万页，接口就从几毫秒变成几百毫秒，这种慢叫"深分页"。本文讲它为什么慢，并用 300 万行的表实测三种改法，告诉你每种什么时候能用。
版本说明：MySQL 8.2.0（macOS arm64，InnoDB，buffer pool 1 GB），在本机的临时实例上跑，端口 3307，没有碰任何业务库。数据是脚本随机生成的 300 万行。下面的耗时是我这台机器上的数字，不是通用基准，拿它看相对差距就够了。
一、深分页是什么，为什么越往后越慢
LIMIT 1000000, 20 的意思是"跳过前 100 万行，取 20 行"。MySQL 没有办法直接跳到第 100 万行的位置，它得从索引头开始，把前 100 万行一行行读出来，再把它们丢掉，最后才返回这 20 行。
所以代价跟"页码"成正比，跟"返回多少行"关系不大。第 1 页读 20 行，第 10 万页要读 200 万行，返回的数据量一样，扫描量差了 10 万倍。
这就是为什么列表页前几页很快，翻到后面就卡。接口本身没问题，问题在 OFFSET 这个写法。
二、先搭一个能复现的表
用递归 CTE 生成 300 万行，表结构尽量接近真实订单表：
CREATE TABLE t_order (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
status TINYINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
remark VARCHAR(120) NOT NULL,
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 3000000;
INSERT INTO t_order (user_id, status, amount, created_at, remark)
WITH RECURSIVE g(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM g WHERE n < 3000000)
SELECT FLOOR(RAND(n)*100000), n % 4, ROUND(RAND(n+7)*1000,2),
NOW() - INTERVAL FLOOR(RAND(n+3)*365*86400) SECOND,
CONCAT('order-remark-', n, REPEAT('x', 40))
FROM g;
status 按 n % 4 分布，所以 status = 1 正好是 75 万行，占 25%。
三、三种写法的耗时
每条 SQL 用 mysql 客户端跑 5 次取中位数（含客户端启动的几毫秒开销），结果如下：
| 写法 | 中位数 | 最快 | 最慢 |
|---|---|---|---|
无过滤 ORDER BY id LIMIT 0,20 | 14 ms | 13 ms | 21 ms |
无过滤 LIMIT 1000000,20 | 207 ms | 203 ms | 239 ms |
无过滤 LIMIT 2500000,20 | 499 ms | 487 ms | 544 ms |
延迟关联 LIMIT 2500000,20（见第四节） | 276 ms | 227 ms | 315 ms |
keyset WHERE id > 2500000 ORDER BY id LIMIT 20 | 14 ms | 14 ms | 21 ms |
加过滤 status = 1 + LIMIT 200000,20 | 1943 ms | 1787 ms | 1978 ms |
加过滤 status = 1 + WHERE id > 200000 ORDER BY id LIMIT 20 | 14 ms | 13 ms | 15 ms |
几个读法：
- 偏移量从 0 到 250 万，耗时从 14 毫秒涨到约 500 毫秒，基本是线性的。
- keyset 在我测过的两个位置（主键 250 万、过滤后的 20 万）都是 14 毫秒，它从游标位置直接开始读，前面的行不碰。
- 加了过滤条件以后，
status = 1 LIMIT 200000,20要扫过的行更多，已经到将近 2 秒。这是最容易被线上忽略的情况：查询一加条件，慢得更厉害。 - 我没有测
ORDER BY created_at的情况，所以不对它下结论。
四、延迟关联：不改接口，只改 SQL
如果业务上必须支持"跳到第 N 页"，keyset 做不到，就只能继续用 OFFSET。这时可以用延迟关联：先在索引上只取主键，最后再回表取整行。
SELECT o.id, o.user_id, o.status, o.amount, o.created_at, o.remark
FROM t_order o
JOIN (
SELECT id FROM t_order
ORDER BY id
LIMIT 2500000, 20
) x ON o.id = x.id
ORDER BY o.id;
子查询只取主键，外层只对 20 个主键回表。实测 276 毫秒，比直接 OFFSET 的 499 毫秒快将近一半。我没有把执行计划逐步拆开看，快的那部分具体来自哪一步，这里只写实测结果。
⚠️ 这里只测了按主键排序。如果排序字段是非主键、外层还要回表，收益会怎样我没有测。
延迟关联的耗时仍然随偏移量增长，只是比直接 OFFSET 小。我只测了 250 万这一个偏移点，没有测它的增长曲线。
五、keyset（游标分页）：快，但有代价
keyset 的做法是记住上一页最后一条的主键，下一页从它后面开始取：
-- 第一页
SELECT id, user_id, status, amount, created_at, remark
FROM t_order ORDER BY id LIMIT 20;
-- 下一页：把上一页最后一条的 id 带过来
SELECT id, user_id, status, amount, created_at, remark
FROM t_order WHERE id > ? ORDER BY id LIMIT 20;
它快，因为每次都是索引的范围定位。代价有三个：
- 不能跳页。 用户要直接跳到第 5000 页，keyset 做不到，只能一页一页往后走。
- 排序字段必须唯一或者能组成唯一。 如果按
created_at排，同一秒有多条，就要用(created_at, id)作为游标，SQL 也会变复杂。 - 数据有增删时，翻页结果更稳定，但"第几页"的概念没了。 管理后台常要求显示总页数，这种场景还是要 OFFSET 或者单独统计。
我的建议很直接：给用户看的无限下拉、导出、批处理，用 keyset；必须有页码的后台管理页，先看真实翻页深度。 如果后台的页码深度只有几十页，OFFSET 一般够用，不必为了少数深页改成游标。这是我的判断，没有统计数据支撑。
六、怎么判断自己的接口需不需要改
不用凭感觉，先看两件事。注意：偏移量小的时候，OFFSET 和 keyset 的差距几乎看不出来，问题只会在深页出现，所以判断要看深页的请求，而不是平均耗时。
-- 在有 performance_schema 的实例上，找出耗时最高的 LIMIT 语句（只是思路，线上以慢查询日志为准）
SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%LIMIT%'
ORDER BY avg_timer_wait DESC LIMIT 10;
然后看接口日志里 offset 参数的分布。如果 99% 的请求 offset 不超过 1000，你不需要改；如果有大量请求超过 10 万，就该动手了。
七、速查表
| 场景 | 推荐写法 | 本机实测（300 万行） |
|---|---|---|
| 前几十页、后台管理 | 普通 OFFSET | 偏移 0 时 14 毫秒 |
| 必须跳页、偏移量很大 | 延迟关联 | 偏移 250 万时 276 毫秒 |
| 无限下拉、导出、遍历 | keyset | 任意位置 14 毫秒 |
| 带过滤条件的深分页 | keyset，并保证过滤列在索引里 | 加过滤的 OFFSET 20 万时 1943 毫秒 |
八、常见问题
Q：能不能只靠加索引解决？
不能完全靠。本文的索引是 (status, created_at)，而查询按 id 排序，索引帮不上翻页本身。想让深分页快，要么换游标，要么换成能覆盖排序的索引，两者都要结合查询来设计。
Q：前端要怎么配合 keyset？
接口要把上一页最后一条的主键回传给前端，前端下一次请求带上它。原来按页码请求的接口，要改成按游标请求，返回值里多带一个 nextCursor。
Q：总条数怎么办？
COUNT(*) 在 InnoDB 上同样要扫描，表越大越慢。这是原理层面的说法，我没有测。如果页面必须显示总数，建议单独缓存或做异步统计，不要和列表查询放在同一个请求里。
九、几个没有覆盖的地方
- 只测了
ORDER BY id。按created_at排序、带多列索引的情况没测。 - 数据是随机生成的，真实订单表的分布（比如大部分订单集中在某段时间）会影响索引效率。
- 只测了 MySQL 8.2 的 InnoDB。8.0、5.7 和 TiDB 的行为可能不同，没有验证。
- 没有做缓存冷启动的对比，所有数字都是缓冲池预热之后的。
你们线上的列表页，现在最深能翻到第几页？如果超过 10 万，是靠 OFFSET 硬扛，还是已经在用游标或延迟关联了？我挑几个典型回复，下一篇补进来。
觉得有用的话点个赞、收个藏，下次改分页的时候翻得到。
