如何利用ROW_NUMBER和游标在PostgreSQL中高效实现分页查询？

本文共计978个文字，预计阅读时间需要4分钟。

由于 PostgreSQL 必须从排序结果的开头逐行扫描，才能跳过 `OFFSET` 指定的全部行，才能获取到目标数据。当 `OFFSET` 是 100 万时，数据库实际上读取了 100 万 + `LIMIT` 行，时间复杂度是 O(OFFSET + LIMIT)。如果已有索引，则无需使用跳过前 N+ 行的能力，其效率仍然是一遍扫描。

ROW_NUMBER() 真的比 OFFSET 快吗

不一定快，甚至可能更慢——尤其在没加合适索引、或返回字段多、或排序列不唯一时。ROW_NUMBER() 需要先对全表（或满足 WHERE 条件的部分）完成完整排序并编号，再过滤，本质仍是“全量计算 + 截断”。

• 只适合中小数据集（比如
• 必须确保 ORDER BY 列有索引，否则性能雪崩；推荐组合索引，例如 CREATE INDEX idx_orders_created_at_id ON orders(created_at DESC, id DESC)
• 避免在 OVER() 里用函数或表达式排序，比如 ORDER BY lower(name)，这会让索引失效
• 如果排序列存在重复值，ROW_NUMBER() 生成的序号是不确定的（除非补上唯一列保序），可能导致同一页数据重复或遗漏

游标分页（Keyset Pagination）怎么写才有效

这是真正能解决深分页性能问题的方法：它不依赖“第几页”，而是记住上一页最后一条记录的排序键值，用 WHERE 直接定位下一页起点。查询复杂度稳定在 O(log N)，和页码无关。

• 基础写法（按 created_at DESC 分页）：

  SELECT id, name, created_at FROM orders WHERE created_at < '2024-05-20 14:30:00' ORDER BY created_at DESC LIMIT 20;

• 必须保证排序字段有索引，且方向一致（如 created_at DESC 对应 WHERE created_at < ?）
• 如果排序字段可能重复（比如多个订单同一秒创建），一定要追加一个唯一列（如 id）作为第二排序条件，并在 WHERE 中一并约束：

  WHERE (created_at, id) < ('2024-05-20 14:30:00', 98765)

• 前端不能传 page=1000，只能传上一页最后一条的 cursor（通常是 base64 编码的排序键值组合）
• 无法直接跳转中间页；但对 API 场景（如无限滚动、Feed 流）完全够用，且更稳定——不会因新数据插入导致页偏移

什么时候该选哪种分页方式

没有银弹。选错的核心代价不是慢，而是“你以为它快，结果线上崩了”。

• 用户可随意输入页码（如后台系统）、数据量 ≤ 10 万行 → 用 LIMIT OFFSET，加好索引就行
• 需要跳转任意页，且数据量 > 100 万 → ROW_NUMBER() + 物化 CTE 或临时表缓存编号，但要做好超时和并发控制
• 面向用户端的列表接口（App/网页 Feed）、数据持续写入、页码深度不可控 → 只用游标分页，且服务端必须校验 cursor 格式与有效性
• 别在游标分页里混用 OFFSET（比如“第一页用游标，后面用 OFFSET”），这会破坏一致性，也失去性能优势

最容易被忽略的一点：游标值必须来自数据库真实返回的字段值，不能前端拼、不能 JS new Date() 伪造、不能四舍五入时间戳——哪怕差 1 毫秒，都可能漏掉或重复一条记录。