分页通常是一个很安静的接口参数：page_no、page_size，或者 limit、offset。数据量小时，它几乎没有存在感；等列表长到几十万、几百万行之后，同一条 SQL 只是把 OFFSET 往后挪了一点，延迟却会突然变得难看。

问题不在“翻页”这个动作本身，而在系统被要求从一个有序结果集里跳过很长的前缀。RDBMS、Elasticsearch、Hive 的表现各不相同，底层代价却很接近：先把前面的候选算出来，再把它们丢掉。

这也是深翻页最容易被误解的地方。它不只是 SQL 写法问题，还牵涉访问路径、排序稳定性、产品交互，以及底层系统是否需要跨节点合并结果。一个可用的分页方案，往往先从产品语义开始，而不是从 LIMIT 990000, 1000 开始。

深翻页的成本来自“计算前缀再丢弃前缀”。优化要么把前缀变窄，要么把“第 N 页”改成“从某个锚点继续”，要么提前把结果集物化成可跳转目录。

一个大表实验

有一个很典型的大表实验：表里大约 1000 万行，按 status 过滤后命中约 100 万行。测试环境是本地硬盘，慢查询阈值按 100ms 估算。几条查询的结果很有代表性：

查询形态	典型 SQL	耗时	主要现象
直接深翻页	`where status = '1' limit 990000, 1000`	3.563s	命中 `idx_status`，但要跳过 99 万行
加 `id` 范围（反例）	`where status = '1' and id >= 9000000 limit 990000, 1000`	1.572s	窄化后候选集远不足 99 万行，OFFSET 走完直接返空；耗时主要花在扫完整个窄化候选集，并不是真的"跳过 99 万行"
只取 `id`	`select id ... limit 990000, 1000`	0.194s	覆盖索引，行变窄
先取 `id` 再回表	`JOIN (select id ... limit 990000, 1000)` 形式的延迟关联	0.199s	比直接查全列快很多，但仍超过 100ms。不要写成 `IN (subquery)`——MySQL 5.6 之前的版本不支持子查询带 `LIMIT`

这个实验给出的信号很直接：跳过同样多的逻辑行时，跳过窄索引比跳过整行便宜。换成更一般的说法，就是先沿着窄结构走到目标窗口，再用目标窗口里的主键回表。

延迟关联、覆盖索引、游标分页这几种常见写法，分处不同层次。用上面实验的 1000 万行表逐个对照：

技巧	做法举例	减掉的成本	没有减掉的成本
覆盖索引	`SELECT id FROM t WHERE status='1' LIMIT 990000, 1000`——只查 `id`，走 `(status, id)` 索引，不回表	回表与大字段读取。实验中从 3.5s 降到 0.19s	仍要跳过前面的索引项。第 1000 页和第 1 页的扫描量差了 99 万行
延迟关联	子查询先在覆盖索引上取 1000 个 `id`，外层再 `SELECT * FROM t WHERE id IN (...)` 回表	深页阶段只处理窄键，回表只发生在目标窗口的 1000 行。实验中 0.199s	仍要按偏移计算目标窗口。数据库必须先数过前 990000 个索引项
游标分页	`WHERE status='1' AND id < :last_id ORDER BY id LIMIT 1000`——用上一页末尾的 `id` 直接定位	不再按偏移跳过。无论第几页，扫描行数恒等于 page_size	只能自然连续翻页。用户不能直接跳到第 500 页

效果排序：游标分页 > 延迟关联 > 覆盖索引 > 裸 OFFSET。三层的语义差异、选型路径与"是否需要随机跳页"的取舍在下一节展开。

`OFFSET` 慢在哪里

SQL 里的 LIMIT/OFFSET 语义很朴素：先得到查询结果的一段有序序列，再跳过前 OFFSET 行，返回后面的 LIMIT 行。PostgreSQL 官方文档写得很直白：被 OFFSET 跳过的行仍然要在服务端计算出来，大 OFFSET 可能低效。这个说法足够解释绝大多数 RDBMS 深分页问题。

MySQL 的 LIMIT 优化文档也给了两个关键提醒。第一，LIMIT row_count 可以让优化器在某些场景更早停止，例如 ORDER BY + LIMIT 在找到足够多的有序结果后可以停止。第二，如果 ORDER BY 列不唯一，带不带 LIMIT 可能得到不同的行内顺序；要获得稳定分页，必须在排序列后补一个唯一的 tie-breaker，例如 id。

因此，一个看似普通的分页 SQL 至少有三层成本：

SELECT *
FROM orders
WHERE status = 'PAID'
ORDER BY paid_at DESC, id DESC
LIMIT 990000, 1000;

第一层是过滤成本。status = 'PAID' 能不能走到选择性足够好的索引，决定候选集有多大。

第二层是排序成本。ORDER BY paid_at DESC, id DESC 能不能沿索引天然输出，决定是否需要额外排序。

第三层是跳过成本。哪怕前两层都命中索引，数据库仍要沿着有序结果走过 99 万个位置，才能返回后面的 1000 行。

深分页优化的基本动作，就是分别处理这三层成本。

模式一：先翻窄索引，再回表

“延迟关联”适合保留页码语义，又希望把大偏移的伤害降下来。它让内层子查询只扫描覆盖索引，拿到目标窗口的主键后，外层再回表取完整行：

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    WHERE status = 'PAID'
    ORDER BY paid_at DESC, id DESC
    LIMIT 990000, 1000
) page ON page.id = o.id
ORDER BY o.paid_at DESC, o.id DESC;

对应的索引应该贴合过滤与排序：

1 2	`CREATE INDEX idx_orders_paid_page ON orders (status, paid_at DESC, id DESC);`

降序索引语法假设 MySQL 8.0+。MySQL 8.0 之前 DESC 关键字会被解析但实际仍按升序构建索引；在更老版本上写 (status, paid_at, id) 即可，优化器会反向扫描索引拿到等价效果。

这个方案的价值在于把“跳过 99 万行完整记录”变成“跳过 99 万个索引项”。索引项包含的列少，页更紧凑，缓存命中率更高，回表只发生在最后 1000 个目标行上。

边界也很清楚。偏移本身没有消失，只是被压到更便宜的结构上。如果业务要求 100ms 内返回第 1000 页，旧实验里 0.19s 的结果已经说明：延迟关联可以把 3 秒级查询拉回亚秒级，但不保证进入高并发接口的 SLA。

可迁移模式是“瘦身后定位”：目标窗口很深时，先用只包含过滤列、排序列和主键的窄结构定位窗口，再把窗口里的键扩展成完整对象。

场景	窄结构	扩展动作
MySQL 深页	覆盖索引 `(where, order, id)`	按主键回表
ES 搜索页	每个 shard 的 top docs	coordinator 合并后取 `_source`
Hive 离线页	物化出的行号表	查询行号范围后读宽表

用一个订单后台的例子把三层拆开看。假设 orders 表有 200 万行，每行包含 id、status、paid_at、amount、buyer_name、shipping_address、items_json 等字段，其中 items_json 平均 4KB。业务需要按 status = 'PAID' 过滤、按 paid_at DESC, id DESC 排序，每页 1000 条，翻到第 991 页（即 OFFSET 990000）。

裸 OFFSET 的代价：数据库沿索引扫描 991000 个索引项。如果执行计划选择对每个索引项都回表读取完整行（包括 4KB 的 items_json），无效 IO 会达到 GB 量级；即使优化器选择了 index range scan + late row lookup，扫描近百万个索引项本身的 CPU 和缓存压力仍然显著。这是深翻页慢的主因。

覆盖索引做了什么：建一个 (status, paid_at, id) 的联合索引，索引项只包含这三个窄列，不含 items_json。扫描 991000 个索引项时不再回表，IO 从"读完整行"降级为"只读索引页"。但注意：扫描 991000 个索引项这个动作本身没有消失，只是变便宜了。如果索引前缀超出缓冲池容量、或者 B+Tree 层级较深导致随机 IO 增多，深页依然会比浅页慢一个数量级。

延迟关联在覆盖索引之上再压一层：子查询只在覆盖索引上完成 LIMIT 990000, 1000，拿到 1000 个 id；外层再用这 1000 个 id 回表取完整行。相比纯覆盖索引方案，它的优势在于子查询的执行计划可以更激进地选择 index-only scan + early termination，减少持有大量中间结果的内存压力。在 offset 达到百万级时，这种写法通常能观察到明显的性能改善，但改善幅度高度依赖表结构、索引紧凑度和缓冲池大小，不存在通用的百分比收益。它仍然依赖 OFFSET 语义：数据库必须先数过前 990000 个索引项才能定位到第 990001 个。如果业务要求 50ms 内返回第 1000 页，延迟关联通常做不到。

游标分页彻底消灭了偏移：上一页最后一条记录的 (paid_at, id) 就是下一页的起点。查询变成：

SELECT *
FROM orders
WHERE status = 'PAID'
  AND (
      paid_at < :last_paid_at
      OR (paid_at = :last_paid_at AND id < :last_id)
  )
ORDER BY paid_at DESC, id DESC
LIMIT 1000;

数据库直接从锚点位置开始向后扫描 1000 个索引项，不需要数过任何前缀。无论当前是第 2 页还是第 2000 页，单次查询的代价恒定。代价是失去了"跳到第 N 页"的能力：用户不能直接输入页码，只能从已知位置连续移动。

三层的关系可以这样记：覆盖索引解决的是"读得太宽"，延迟关联解决的是"持有多余中间状态"，游标分页解决的是"数前缀本身就不该发生"。前两层是在 OFFSET 语义内做优化，第三层是换掉语义。选型时先判断产品是否真的需要随机跳页；如果不需要，直接走游标分页，不要在前两层上反复调优。

模式二：用锚点替代偏移

如果产品只需要连续向后翻页，OFFSET 可以完全消失。上一页最后一条记录就是下一页的锚点：

SELECT *
FROM orders
WHERE status = 'PAID'
  AND (
      paid_at < :last_paid_at
      OR (paid_at = :last_paid_at AND id < :last_id)
  )
ORDER BY paid_at DESC, id DESC
LIMIT 1000;

这类写法常被叫作 keyset pagination、seek pagination 或 cursor pagination。核心动作是使用完整排序键，而不只是使用 id。如果列表按 paid_at DESC, id DESC 排序，游标里就必须带 paid_at 和 id。只带 id 等于假设 id 与排序完全一致；这个假设在按发布时间、热度、最后回复时间排序的业务里通常不成立。

游标建议做成不透明 token，而不是把 id 裸露在 URL 里：

{
  "query_hash": "paid-orders:v3:status=PAID",
  "sort": ["2026-06-13T09:30:12.123Z", 918273645],
  "direction": "next",
  "page_size": 1000,
  "snapshot": "optional-snapshot-or-pit-id",
  "expires_at": "2026-06-13T10:30:12Z",
  "signature": "hmac..."
}

query_hash 防止拿 A 查询的游标翻 B 查询。sort 存排序锚点。snapshot 用来绑定一致性视图。signature 防止客户端篡改游标内容。

这个模式的读法是“锚点续走”：听到“无限滚动”“下一页”“消息流”“时间线”这类需求，优先考虑基于排序键的 seek，而不是页码。

`id` 稀疏包含三个不同问题

id 稀疏经常被拿来解释深分页，但这里面混着三个问题，需要拆开看。

如果 SQL 写成：

1
2
3

WHERE id > :last_id
ORDER BY id
LIMIT 1000

id 中间缺号并不会影响返回 1000 条记录。数据库会沿 B+Tree 继续向后扫描，直到收集到 1000 条存在的记录或结果集耗尽。id 从 1000 跳到 9000000 并不表示中间必须返回 8999000 行。

麻烦来自三处。

第一，id 不一定是业务排序。论坛按 last_reply_at 排，商品按销量或相关性排，订单后台按 paid_at 排。此时 id > last_id 不是“下一页”，只是“主键更大的另一批行”。

第二，id 与过滤条件可能不对齐。如果 status = 'PAID' 的数据在主键空间里很稀疏，id > last_id 之后可能要扫很远才能凑够一页。更好的修正方向是让索引以过滤列和排序列开头，例如 (status, paid_at, id)。

第三，id 对用户没有解释力。用户看见第 13 页的 id 从 1000 跳到 9000000，会误以为漏了大量数据。接口不应该把游标解释成“页码”。游标是机器用的锚点，不是人的位置编号。

跨页和乱翻页需要目录

keyset pagination 最擅长连续翻页。它天然不擅长三个需求：

需求	keyset 的困难	更合适的方案
跳到第 100 页	没有第 100 页的锚点	锚点目录或物化结果
一次取第 100 到 120 页	需要顺序走 99 次	后台任务生成结果集
前后乱翻	需要保存双向锚点	游标栈、PIT、锚点目录

双向翻页可以通过“前后两个锚点”解决。当前页有第一条和最后一条记录，向后翻用最后一条，向前翻用第一条并反向排序：

-- 当前页之前的一页
SELECT *
FROM orders
WHERE status = 'PAID'
  AND (
      paid_at > :first_paid_at
      OR (paid_at = :first_paid_at AND id > :first_id)
  )
ORDER BY paid_at ASC, id ASC
LIMIT 1000;

客户端拿到结果后再反转成 DESC 展示。这个方案适合“上一页 / 下一页”，不适合任意页码跳转。

任意跳转要引入目录。目录可以每隔固定行数保存一个锚点：

query_hash | snapshot_id | page_no | anchor_paid_at | anchor_id
-----------+-------------+---------+----------------+----------
Q1         | S1          | 1       | 2026-06-13 ... | 9981
Q1         | S1          | 10      | 2026-06-12 ... | 7162
Q1         | S1          | 20      | 2026-06-10 ... | 4319

跳到第 23 页时，先找到第 20 页锚点，再顺序 seek 3 页。目录越密，跳转越快，维护成本越高。目录越稀，存储更省，跳转时要补走更多页。

更重的做法是物化整个结果集（语法以 PostgreSQL / MySQL 8.0+ 为例，老版本 MySQL 没有 row_number() 窗口函数）：

CREATE TABLE export_orders_page_result AS
SELECT
    row_number() OVER (ORDER BY paid_at DESC, id DESC) AS rn,
    id
FROM orders
WHERE status = 'PAID'
  AND paid_at >= '2026-01-01';

这种方案适合后台报表、审计导出、运营后台复杂筛选，不适合普通用户每次打开列表都即时生成。

可迁移模式是“目录化跳页”：当产品坚持页码、跳页、跨页批量操作时，单靠游标不够，需要把“页号到锚点”的映射显式存下来。

产品语义先于技术方案

分页接口最常见的失败，是把所有交互都压成 page_no + page_size。不同交互要的是不同语义：

产品交互	用户真正需要	推荐实现
首页和前几页	快速看到最新结果	`OFFSET` 或浅层 `from/size`
无限滚动	从当前看到的位置继续	keyset / `search_after`
论坛上一页下一页	稳定地前后移动	双向 cursor + 稳定排序键
跳到第 N 页	页号有可解释含义	锚点目录或物化结果
导出全部结果	完整遍历，不是交互翻页	批处理游标、scroll、离线任务
运营后台复杂筛选	可重复、可审计	固定快照 + 物化结果

如果产品说“必须跳到第 1000 页”，技术方案不应该强行套游标。更合理的问题是：第 1000 页是否真的有业务意义；如果有，页码目录由谁维护；如果没有，交互是否可以改成时间范围、筛选条件或导出任务。

Elasticsearch 的深分页

Elasticsearch 把 RDBMS 的问题放大到了分布式环境里。官方分页文档直接提醒：不要用 from 和 size 翻得太深。搜索请求通常跨多个 shard，每个 shard 都要把本 shard 的当前页和之前页命中加载到内存里；深页会显著增加内存和 CPU，甚至导致节点故障。默认情况下，from + size 不能超过 10000，这个限制来自 index.max_result_window。

一个五分片索引执行：

{
  "from": 990000,
  "size": 1000,
  "sort": [
    { "created_at": "desc" },
    { "tie_breaker_id": "desc" }
  ]
}

直觉上只要 1000 条结果。实际每个 shard 都可能需要拿出接近 from + size 的候选，再交给协调节点做全局归并。RDBMS 是“单机跳过长前缀”，ES 是“每个 shard 都跳过长前缀，再集中归并”。

ES 的交互式深翻页方案是 search_after：

{
  "size": 1000,
  "query": {
    "term": { "status": "published" }
  },
  "sort": [
    { "created_at": "desc" },
    { "tie_breaker_id": "desc" }
  ],
  "search_after": ["2026-06-13T08:00:00.000Z", "post_918273"]
}

它和 RDBMS keyset pagination 是同一个模式：用上一页最后一条命中的 sort 值作为下一页锚点。官方文档还建议在需要稳定索引视图时配合 PIT（ES 7.10+ 引入的 point in time）。在 PIT 上下文里（ES 7.12+），排序末尾会被隐式追加 _shard_doc 作为 tie-breaker；继续翻页时要使用上一页返回的最新 PIT ID 和最后一条命中的 sort values。

scroll 不是交互深分页的现代答案。ES 的 scroll API 文档已经明确提示：scroll 不再推荐用于交互式深分页；如果要在 10000 条之后继续翻，并且需要保留索引状态，应该使用 search_after + PIT。scroll 更适合批量遍历和重建索引一类任务。

ES 侧的模式表可以这样记：

需求	ES 方案	边界
前几页搜索结果	`from + size`	不要突破 `max_result_window` 当作常规方案
连续深翻页	`search_after + sort`	查询和排序必须保持不变
稳定视图	`PIT + search_after`	PIT 有保留时间和资源成本
全量导出	scroll 或异步任务	不用于实时用户翻页
聚合桶分页	composite aggregation	解决的是桶分页，不是文档列表分页

Hive 的深分页

Hive 也支持 LIMIT [offset,] rows。官方 Select 手册说明，两个参数时第一个是 offset，第二个是最大返回行数。语法看起来像 MySQL，但执行语义完全不同。

Hive 面向批处理和大规模分析，不是面向毫秒级交互查询。深分页在 Hive 里通常意味着三个重成本叠在一起：

扫描大量文件，除非分区裁剪能提前排除数据。
构造全局顺序，尤其是 ORDER BY。
丢弃 offset 前面的结果。

ORDER BY 与 SORT BY 的区别尤其重要。Hive 官方文档说明，ORDER BY 保证全局有序；SORT BY 只保证每个 reducer 内部有序，多 reducer 时最终结果只是局部有序。换言之，要得到“第 N 页”这种全局位置，SORT BY 不够，必须付出全局排序或物化结果的代价。

Hive 里通常应该改变问题形态，不适合把在线深翻页当成默认路径：

目标	更合适的做法
看样例	分区过滤 + 小 `LIMIT`，不承诺页码
查某个时间段	用分区列和排序键缩小范围
多次跳页分析	CTAS 物化结果集，加 `row_number()`
导出明细	后台任务写入文件或下游表
Dashboard	预聚合、预排序、物化视图

一个典型离线物化方案是：

CREATE TABLE paid_order_result_20260613 AS
SELECT
    row_number() OVER (ORDER BY paid_at DESC, id DESC) AS rn,
    id,
    paid_at,
    amount
FROM dwd_order
WHERE ds BETWEEN '2026-06-01' AND '2026-06-13'
  AND status = 'PAID';

SELECT *
FROM paid_order_result_20260613
WHERE rn BETWEEN 990001 AND 991000;

这个方案不神奇。窗口函数 row_number() OVER (ORDER BY ...) 在 Hive 里会被规划成单 reducer 的全局排序，第一次生成 rn 的代价不亚于一次纯 ORDER BY。它的价值在于把一次昂贵的全局排序，换成后续多次可复用的稳定结果集。对于 Hive，这通常比每次交互都即时翻深页更符合系统定位。

一致性：页不能只谈性能

分页结果不稳定时，用户会看到重复、漏行、上一页消失、下一页跳动。性能优化不能绕过一致性语义。

稳定分页至少需要三个条件：

条件	说明
稳定排序	`ORDER BY` 后补唯一 tie-breaker，例如 `(created_at, id)`
稳定过滤	翻页期间查询条件不能被客户端悄悄改变
稳定视图	翻页期间新增、删除、更新是否可见，要有明确策略

常见策略有三种。

实时列表允许漂移。社交信息流、论坛最新回复、监控告警列表可以接受新数据插入导致列表变化。此时 cursor 只保证“从刚才的位置继续”，不承诺全局快照。

固定快照保证一致。审计、对账、导出、复杂后台筛选更适合固定一个 snapshot。RDBMS 可以用事务一致性视图或物化结果；ES 可以用 PIT；Hive 通常直接物化一张结果表。

短期缓存折中。对普通后台列表，可以把某次查询的 cursor 链或锚点目录缓存几分钟。过期后要求重新搜索。这个方案减少资源占用，也避免长事务或长 PIT 压住系统。

方案怎么落地

设计分页接口时，可以按这组问题把方案逐步收敛下来。

问题	如果答案是“是”	方案
只访问前几页吗	是	`OFFSET/LIMIT` 或 `from/size` 足够
只需要连续向后翻吗	是	keyset / `search_after`
需要上一页吗	是	保存当前页首尾锚点，做双向 cursor
需要任意页码跳转吗	是	锚点目录或物化结果集
需要跨页批量处理吗	是	后台任务，不要走实时分页接口
需要强一致结果吗	是	snapshot / PIT / 物化表
排序字段可能重复吗	是	补唯一 tie-breaker
查询条件变化频繁吗	是	cursor 绑定 query hash

索引设计也有一条简单规则：把“过滤列、排序列、唯一键”合成同一条访问路径。比如订单后台常见查询：

1
2
3

WHERE tenant_id = ?
  AND status = ?
ORDER BY paid_at DESC, id DESC

索引优先考虑：

1 2	`CREATE INDEX idx_order_page ON orders (tenant_id, status, paid_at DESC, id DESC);`

如果查询还有范围条件，例如 paid_at >= ?，需要评估范围列之后的排序列还能不能有效利用索引。索引设计不能照着 SQL 字段全部塞进去；目标是让数据库沿一条尽量窄、尽量有序的路径拿到目标窗口。

几种常见形态

听到的需求关键词	对应模式	典型方案	主要代价
大 offset 慢	瘦身后定位	覆盖索引 + 延迟关联	仍要跳过前缀
无限滚动	锚点续走	keyset / `search_after`	不支持任意跳页
上一页下一页	双向锚点	保存 first/last sort values	游标状态更复杂
跳到第 N 页	目录化跳页	每隔 K 页保存锚点	维护目录
复杂筛选后反复查看	结果集物化	snapshot table / PIT / cache	资源占用和过期策略
ES 超过 10000	分布式锚点	`search_after + PIT`	查询、排序、PIT 必须稳定
Hive 明细翻页	离线物化	CTAS + `row_number()`	首次生成昂贵

深翻页没有免费的通用解法。OFFSET 保留了页码语义，也把长前缀成本留给数据库；keyset 消灭了长前缀，却牺牲了随机跳页；物化结果支持跳页和一致性，但要提前支付存储和后台任务成本。分页方案最后拼的不是某个 SQL 技巧，而是产品愿意保留哪种语义、系统愿意承担哪种代价。

参考资料

PostgreSQL Documentation: LIMIT and OFFSET

MySQL 8.4 Reference Manual: LIMIT Query Optimization

Elasticsearch Reference: Paginate search results

Elasticsearch API documentation: Run a scrolling search

Apache Hive LanguageManual Select: LIMIT Clause

Apache Hive LanguageManual SortBy: Difference between Sort By and Order By