大数据量分页慢查询优化：从OFFSET到Keyset与延迟关联

如果你维护过任何一个带列表页的后台系统，大概率经历过这种事：用户说“翻到第两百页，页面直接卡死”，你登上数据库一看，CPU 打满，慢查询日志里躺着一句ORDER BY create_time DESC LIMIT 20 OFFSET 4000。

我之前接手某个模拟订单管理后台时，订单表已经接近八百万行。列表页每页二十条，前几十页还算正常，翻过五十页之后，接口耗时开始从两三百毫秒往三四秒爬升。一开始以为是服务器规格不够，后来才发现，真正的问题出在“分页”这个看起来最简单的查询设计上——数据量一大，几个字面很短的 SQL 会变成扫表级的灾难。

这篇就完整梳理一下我在大数据量分页上踩过的坑和验证过的优化方案：先说清 OFFSET 慢的根源，再聊索引、Keyset 分页、延迟关联，以及业务侧的兜底手段。适合同样在写管理后台、报表查询、Feed 流这类场景的后端同学参考，读完可以直接在自己的库里先验证一轮。

1. 分页慢的病根：LIMIT OFFSET 到底在背后做了什么

1.1 你以为跳过了一万行，实际是一行行数过去的

很多人在写分页时会把 OFFSET 理解成“跳过前 N 条”，但数据库没有这么方便的操作语义。执行LIMIT 20 OFFSET 10000的时候，存储引擎做的事情是：找到满足条件的记录起点，然后沿着索引或者表数据一条一条地读，数到第一万条之后，再继续取二十条。被跳过去的那一万条数据，数据库同样要读一遍，只是不返回给你而已。

这就好比你去图书馆找一本放在第四排书架上的书，管理员不会直接带你到第四排，他得从入口开始，路过一排又一排书架，最终才能定位你要的位置。数据量小的时候，这一路走过去的代价可以忽略；但当你翻到一千页，数据库就要处理几万甚至几十万条记录，才能拿到最后那二十条。分页之所以越翻越慢，本质上是你用 OFFSET 把“扫描量”线性放大了。

1.2 排序会把这个问题进一步放大

如果查询还带ORDER BY，代价会更夸张。数据库必须基于过滤后的结果集做一次完整排序，才能决定哪些是“第 10001 到 10020 条”。排序的数据量一旦超过内存缓冲区，就会借用磁盘临时文件，也就是执行计划里常见的Using filesort。你翻得越深，参与排序的行越多，耗时就越是不可控。

这一点很容易被忽略。很多人排查慢查询，看到条件字段有索引就放心了，却没意识到ORDER BY本身也可能是瓶颈。尤其当排序字段和被过滤字段没有构成合适的联合索引时，数据库即便能用索引定位行，也无法避免额外的排序步骤。这是分页优化里最典型的“隐性开销”。

1.3 一次对照实验：页数越深，耗时越离谱

我在一个本地测试环境里模拟过一张约五百万行的订单表，字段很简单：id、user_id、status、amount、created_at，索引只有主键和 user_id 上的普通索引。用三个不同深度的分页查询对比执行时间，结果大致如下：

看到 OFFSET 200000 时你可能会想：也不过几万条，怎么就三秒？因为在真实执行时，数据库扫描的行数大约是二十万行，中间还要排序、落临时表，最终只返回二十行。也就是说，你为一次“翻页”支付了二十万行的处理和存储成本。生产环境里再加上其他查询条件、更宽的字段、更大的表，接口超时和连接池被打满就都来了。

1.4 别忘了 COUNT(*) 是同一个炸弹

很多分页接口在返回列表之前，还要执行一句SELECT COUNT(*) FROM orders WHERE ...，用来计算总页数。没有合适索引时，这句 COUNT 一样是全表扫描，而且比列表查询更可怕——列表至少还只差最终不返回的临时数据，COUNT 却要把所有匹配行都数一遍才能给出答案。

我在接手那个后台时，慢查询日志里最刺眼的往往是 COUNT，而不是列表页面本身。所以后面业务兜底那一节，我会专门讲怎么处理它。这里先提醒一句：如果分页慢，先看是不是每次请求都顺带查了一次全量 COUNT，这种需求往往是产品层面的惯性设计，并不需要每次都实时计算。

2. 先检查索引：一部分“分页慢”是查询设计的问题

2.1 索引先解决“排序”而不是“跳过”

分页优化第一步不是上 Keyset，而是先看索引合不合理。索引在分页里最大的价值首先是承接 ORDER BY——数据库返回按某字段降序排列的记录，最理想的情况是索引本身已经按这个字段排好序，数据库只需要在索引上正序或倒序扫描，完全不用额外排序。

但如果排序字段没有索引，数据库就只能拿到过滤后的数据后在内存或磁盘里自己排一遍，一旦数据量大就会出现Using filesort。所以第一步永远是看 EXPLAIN 的输出：有没有Using filesort，有没有走上预期索引，扫描行数 rows 是否合理。这些问题不解决，后面什么高级分页方案都救不回来。

2.2 联合索引怎么建：跟着 WHERE 和 ORDER BY 一起走

假设典型查询是这样：

SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 2000;

最合适的索引不是单独在 status 上建，也不是单独在 created_at 上建，而是建一个联合索引：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

为什么？因为在(status, created_at)这个联合索引里，数据先按 status 分组，同一 status 内部再按 created_at 排序。查询条件是status = 'paid'，数据库定位到 paid 这部分索引后，created_at 已经天然有序，排序动作可以直接省掉。分页时只需要沿着索引段向前移动，就能拿到目标位置的记录。

这个是很多性能事故的根源：只给 status 建了单列索引，没有把排序字段带进去，导致数据库每次都要对几十万行做 filesort。一个联合索引的调整，耗时能差出几十倍。

2.3 覆盖索引：让查询彻底不回表

另一个容易被忽略的优化是覆盖索引。当一个索引包含了查询需要的所有列时，数据库可以直接从索引叶子节点返回数据，不需要再用主键回表读取整行记录。对分页查询来说，回表次数等于“查询结果集大小”，而覆盖索引能把这部分 I/O 直接消掉。

以上面的 SQL 为例，如果列表只展示 id、user_id、amount、created_at，可以建一个更大一点的覆盖索引：

ALTER TABLE orders ADD INDEX idx_status_created_cover (status, created_at, id, user_id, amount);

这种情况下，整个查询的数据都能从索引里拿到。索引一般比表数据小很多，更容易被数据库缓存在内存缓冲池里，I/O 次数大幅度下降。

但覆盖索引也不是越宽越好。如果表里有很多字段，甚至包含大文本字段，盲目把列塞进索引会让索引体积膨胀，写入开销和磁盘占用都会上升。我的习惯是只覆盖高频查询里真正需要的列，不会把整张大宽表放进一个索引。

2.4 一次 EXPLAIN 的对照结果

同样一条查询，在两种索引方案下的执行计划差别巨大：

rows 的差距一目了然。前者要把二十万行捞出来排序再丢弃；后者直接在索引上走有序扫描，目标位置就在那里。这也是我一直强调的：看分页慢，先看索引，因为这里的优化成本最低、收益最直接。

3. 主力方案：Keyset 分页，用“记住位置”代替“跳过”

3.1 核心思想：每次都从上一条记录接着查

索引调完了，如果还是需要处理很深的翻页，就该上 Keyset 分页了。它也叫“游标分页”或“基于索引分页”，思路很简单：不再告诉数据库“跳过多少条”，而是直接告诉它“从我上次看到的那条记录后面继续取”。数据库只需要沿着索引，从某一条之后取二十条，完全省掉了从头开始数一万条的过程。

以上面的订单列表为例，第一页用：

SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20;

拿到这一页最后一条记录后，把它保存为游标。假设这一条是created_at = '2024-01-01 10:30:00', id = 12345，第二页就变成：

SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' AND (created_at < '2024-01-01 10:30:00' OR (created_at = '2024-01-01 10:30:00' AND id < 12345)) ORDER BY created_at DESC, id DESC LIMIT 20;

在联合索引(status, created_at, id)的帮助下，数据库的扫描范围从“从头数几千条”变成了“从某一条之后取二十条”。单页查询成本基本恒定，翻到第 1000 页也不会变慢。这个特性让 Keyset 成了大数据量下最值得优先考虑的分页方案。

3.2 为什么要用 created_at + id 双字段

只用 created_at 做游标最大的问题是时间戳可能重复。同一秒内下两笔订单很常见，如果这两笔恰好分布在上一页和下一页的边界，只靠created_at < '2024-01-01 10:30:00'就会把其中一笔漏掉，或者下一页重复显示。

所以 Keyset 分页的排序键必须“唯一且稳定”。一般表里天然就有一个这样的字段——自增主键。如果你能直接按 id 排序，那是最省事的，因为 id 本身唯一，查询条件只需要WHERE id < ?。如果需要按时间排序，就用created_at + id这个组合，用 id 打破同秒并列的情况。查询条件也要写成上面那种二段式：先比时间，时间相同的再比 id。

3.3 游标的封装思路与代码示例

游标不用做得很复杂，本质就是上一页最后一条记录的排序键值。前端下次请求时，把游标原样传回来，后端可以把它编码成一个字符串，比如把created_at和id拼接成"2024-01-01 10:30:00|12345"，再交给调用方。

这里给一个框架无关的伪代码示例：

def get_order_page(session, cursor=None, page_size=20): if cursor is None: sql = """ SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT :limit """ params = {"limit": page_size} else: last_created_at, last_id = cursor.split("|") sql = """ SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' AND (created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id)) ORDER BY created_at DESC, id DESC LIMIT :limit """ params = { "last_created_at": last_created_at, "last_id": int(last_id), "limit": page_size, } rows = session.execute(sql, params).fetchall() next_cursor = None if len(rows) == page_size: last_row = rows[-1] next_cursor = f"{last_row.created_at}|{last_row.id}" return rows, next_cursor

注意到next_cursor = None是一个重要的边界信号：当返回行数小于 page_size 时，说明已经到底，没有下一页了。前端拿到 null 之后可以自动把“加载更多”按钮禁用掉，避免无意义的请求。

3.4 Keyset 的适用边界：要认清楚它的限制

Keyset 分页最大的局限是“不支持随机跳页”。你只能从游标处往前或往后翻，没法直接跳到第 500 页。这对管理后台某些场景是个硬伤，比如商品搜索、订单审核列表里，用户就是想知道“第 556 页有什么”。这种时候只能回到 OFFSET，或者搭配下一节讲的延迟关联。

另外，如果排序字段本身频繁变化，Keyset 写起来会更费劲。比如用户能按“金额”“创建时间”“更新时间”任意排序，游标字段就不确定了，需要针对每种排序单独设计游标格式。好在大部分列表的默认排序就是 id 或时间，Keyset 在绝大多数场景都能用得很顺手。

我整理了一张两套方案的对比表：

我后来把“加载更多”类接口全部改成了 Keyset，线上查询耗时波动直接消失。这里唯一需要调整的是接口设计：前端不能再传 pageNo，而要传“上一页最后一条记录”的游标值。改动不大，收益却非常明显。

4. 延迟关联：必须支持页码跳转时的深页优化

4.1 为什么会存在“不得不跳页”的场景

Keyset 很好用，但有的产品就是无法放弃页码。比如审核后台要按编号区间翻查，或者运营同学要跳到某一天的记录附近，页码跳转是刚需。这种情况下 OFFSET 无法避免，那就得想另一个办法：让 OFFSET 的扫描发生在“尽量窄的数据”上。

这时就轮到延迟关联出场。它也叫 Deferred Join，核心思路是把“取目标页的完整数据”拆成两步：第一步只查主键，第二步用主键回表取完整行。听起来多了一次查询，实际却省下了大量无效回表。

4.2 先取主键，再回表的写法

以status = 'paid'分页为例，原本一条 SQL 直接写完是这样的：

SELECT id, user_id, amount, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 10000;

换成延迟关联，先在内层查主键：

SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 10000;

再到外层把完整行捞出来：

SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 10000 ) AS t ON o.id = t.id ORDER BY o.created_at DESC, o.id DESC;

看到没有，内层查询只返回二十个 id，而且完全依赖联合索引(status, created_at, id)在索引里完成，不回表。外层虽然回表，但只回二十行。对比直接用第一条 SQL，数据库为了确定哪二十行是目标，可能需要把前面一万多行的完整记录都从聚簇索引里取出来再丢弃，I/O 差距在这时候会被拉得非常大。

4.3 延迟关联的边界在哪里

延迟关联并不是万能药。当 OFFSET 到十万条时，内层子查询仍然要沿着索引数出十万条记录。区别在于，这次数的是索引节点而不是整行数据。索引节点比行数据小得多，I/O 少得多，可以理解为把一次“全行扫描”缩小成了“索引扫描”，但绝对耗时仍然会随偏移量增长。

所以我的经验是：延迟关联适合那种“页码不能砍、数据量级中等、用户不会翻得太离谱”的场景。如果产品真的允许用户翻到几万页深，那无论怎么优化 OFFSET，成本都会慢慢垒起来，最终只能靠第 5 节的业务侧限制来解决。

另外，延迟关联还可以和 Keyset 组合使用。比如某些系统需要“从某条数据开始，但前端还是想显示页码”，可以先用 Keyset 拿到目标位置的 id，再换算成 OFFSET 的近似值，不过这种玩法复杂度偏高，一般场景用不上。

5. 别忘了业务兜底：页数限制、缓存与冷热数据分离

5.1 限制最大翻页深度，不是所有用户都需要第 1000 页

很多技术方案走到最后会发现，纯靠数据库侧优化是有限的，还得在业务规则上做兜底。最实用的一个动作就是限制最大翻页深度。真实用户里 99.9% 都不会翻到第 1000 页，所谓“用户要翻到底”的说法，大多数时候只是产品经理的一种想象。

做法分两层。前端可以只展示有限页码按钮，并提供“去第几页”输入框，同时校验页号范围；后端更要在 SQL 组装之前判断 OFFSET 是否超过阈值，超过就直接返回一个提示：“数据量较大，请缩小时间范围或使用筛选条件”。这不算伤害体验，反而是保护整个系统不被少数异常点击拖垮。

5.2 给 COUNT(*) 也做缓存或干脆去掉

前面提过 COUNT 往往比列表查询更伤。处理方式有几档，看业务容忍度：

• 如果总条数只是用来展示，可以走一个定期刷新的缓存值，比如每分钟统计一次，落到 Redis 之类的缓存里；
• 如果业务能接受近似值，直接展示“约 XXX 条”或“超过 1000 条”；
• 更彻底的方式是：把 COUNT 和列表查询拆成两个接口，列表先渲染，总条数异步加载，用户看不到卡顿。

我见过有的系统为了一个“总记录数”，每次请求都扫一遍几千万行的表，这真的很浪费。统计类的数字在绝大多数产品里都不需要百分百实时，对中间加一层缓存，慢查询能去掉一大片。

5.3 冷热数据分离，让主表先瘦下来

分页慢了，最根本、最有效的手段其实是让主表干脆没那么大。很多业务都有明显的冷热分层：活跃数据集中在最近三个月，历史数据可能囤了一两年。把历史数据挪走，主表的体量降下来了，任何分页方案的扫描成本都会跟着下降。

具体落地方案有这么几种：

• 把超过一年的历史订单迁移到单独的历史表或归档库，主表只留活跃数据，这是最干净的方案；
• 如果不想迁移数据，可以按 created_at 做分区表，让查询尽量命中最近几个分区；
• 更轻量的方式是在查询条件里强制带一个默认时间范围，比如“最近 90 天”，用户不主动展开就查不到历史深页。

数据分层听着像是运维团队的事，实际上对应用层的收益非常直接：数据量减半，同一条 OFFSET 查询的扫描成本就接近减半。分页优化的终点往往就是控制数据规模，而不是一味在 SQL 上打转。

最后说点个人体会。我处理过的大数据量分页慢查询里，真正属于“一招就能解决”的很少，大多是把索引、查询结构、业务约束放在一起调整。先看 EXPLAIN，再决定要不要上 Keyset 或延迟关联；页数太深的问题，很多时候最后靠的是限制翻页范围和冷热数据分离。如果你正在被某个分页慢查询折磨，可以先从慢查询日志里找出那条耗时最长的 SQL，把上面这几招挨个试一遍。欢迎试完回来分享你的实测数据，一起把方案磨得更稳。