数据库分页:游标 vs OFFSET 深入

某天下午,监控群里炸了——某接口 P99 飙到 8 秒。排查下来不是慢 SQL,不是没加索引,而是一个再普通不过的列表接口:SELECT ... ORDER BY id LIMIT 20 OFFSET 100000。前端埋点显示,有个用户(很可能是爬虫)直接跳到了第 5000 页。

这就是 OFFSET 分页的经典陷阱。它看起来天经地义,实际上在大数据量下是个隐藏的性能炸弹。本文把 OFFSET 为什么慢、游标分页为什么快、游标怎么设计、什么时候只能用 OFFSET 一次讲清楚。

一、OFFSET 的性能陷阱:它真的扫了前 N 行

先看一个最朴素的分页 SQL:

-- 第 5000 页,每页 20 条
SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 20 OFFSET 100000;

很多人以为 OFFSET 100000 是"跳到第 100000 行",数据库会魔法般地直接定位。真相是:它老老实实地从头扫起,扫够 100000 行,然后丢掉,再返回接下来的 20 行。

执行过程大致是:

1. 沿着 id 索引(或全表)从第一行开始
2. 一行一行读、判断、计数
3. 前 100000 行全部读出来然后扔掉
4. 最后返回第 100001 到 100020 行

也就是说,OFFSET N LIMIT M 的实际成本约等于扫 N+M 行,而不是 M 行。N 越大,越慢。

性能曲线大致是这样:

这是 O(N) 的扫描成本,N 是 OFFSET 值。深翻页 = 接近查全表。

更深的问题是 IO。OFFSET 扫描的那些行不是免费的——它们要么从索引里读,要么回表读实际数据。即使最后被丢掉,中间的随机 IO 已经发生了。在内存命中时它"只"是 CPU 浪费,一旦超出 innodb_buffer_pool_size,就会变成磁盘 IO,性能断崖式下跌。

EXPLAIN 里你会看到 rows 列显示一个很大的数字,而 Extra 里可能有 Using filesort。这是数据库在告诉你:我做了很多功。

二、游标分页(keyset pagination):O(1) 定位

游标分页的思路完全不同。不要告诉数据库"跳过多少行",而是告诉它"从哪里开始"。

-- 第一页
SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 21;   -- 多取 1 条用于判断有没有下一页

-- 第二页及之后(假设上一页最后一条 id = 12345)
SELECT id, title, created_at
FROM articles
WHERE id > 12345
ORDER BY id
LIMIT 21;

这里的 12345 就是游标(cursor)。它本质是"上一页最后一条记录的排序键值"。

为什么快?因为 WHERE id > 12345 可以直接走索引的区间扫描。B+Tree 上,数据库一次定位到 id = 12345 的位置,然后往后顺扫 21 条,结束。前面的 12345 行压根没碰过。

成本只跟返回的行数(页大小)有关,跟 OFFSET 无关。无论翻到第几页,耗时基本恒定——O(页大小),近似 O(1)。

同样的数据量,游标分页的耗时曲线:

这就是数量级的差异。OFFSET 是线性退化,游标是恒定。

三、为什么游标这么快:索引直接定位

深入一点看为什么游标能 O(1)。

id 列通常有主键索引(聚簇索引)。B+Tree 的查找是 O(log N),然后顺序扫描叶子节点是 O(M)(M 是返回行数)。所以整个查询是 O(log N) + O(M)。

WHERE id > 12345 在 B+Tree 上的执行:

1. 从根节点往下,二分定位到 id = 12345(或最近的较大值)—— O(log N),约 3-4 次比较就能在千万级数据里定位
2. 从该位置向后顺扫叶子链表,取 21 条 —— O(M)
3. 返回

而 OFFSET 100000 是:

1. 从根节点定位到第一行 —— O(log N)
2. 顺扫叶子链表 100000 行,边扫边丢 —— O(N)
3. 再扫 20 行返回 —— O(M)

差异全在第 2 步。游标把"扫前 100000 行"换成了"一次 B+Tree 定位",这就是质变。

前提是:WHERE 子句用到的列必须有索引,而且排序方向和索引顺序一致。如果 id 上没索引,WHERE id > 12345 也会退化成全表扫描——游标不是魔法,它依赖索引。

四、游标的核心限制:不能跳页

天下没有免费的午餐。游标分页的代价是失去了"随机访问"的能力。

游标是连续的:你必须知道"上一页最后一条"才能取下一页。它不知道"第 50 页"在哪,因为没有"第 50 页"这个概念——只有"游标 X 之后的 20 条"。

具体限制:

1. 只能"上一页/下一页",不能直接跳到第 50 页
2. 不能显示"共 N 页 / 第 X 页",因为没有页码概念
3. 不支持跳页 UI(那种 1 2 3 ... 50 51 52 的分页条)
4. 不能分享某个特定页的链接(因为游标是一串不透明的值,且会过期)

这意味着如果你的产品 UI 是传统的分页条(用户可以点"第 7 页"),游标分页用不了——至少前端那一套页码逻辑得改。

适合游标的场景:

• 无限滚动(Feed、时间线、聊天记录)
• "加载更多"按钮
• 导出/批处理(后台一直翻到底)
• 移动端列表

不适合的:

• 后台管理表格(运营要跳到第 87 页看某条数据)
• 搜索结果(用户习惯点页码)
• 任何需要"共 N 页"展示的地方

五、游标设计:单列、多列与行值比较

游标设计的关键是:游标的值必须能唯一确定排序中的位置。

单列游标(最简单)

排序键是唯一的(比如自增主键 id 或 UUID):

-- ORDER BY id
WHERE id > :last_id

多列游标(更常见)

实际业务中排序往往不止一列,比如 ORDER BY created_at DESC, id DESC。为什么 created_at 不够?因为时间精度问题,同一秒可能有多条记录,只靠 created_at 在边界处会漏数据或重复。

正确写法是用行值比较(row value comparison):

-- 上一页最后一条: created_at = '2026-07-17 10:00:00', id = 12345
-- 倒序,所以是 "小于"
WHERE (created_at, id) < ('2026-07-17 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 21;

这等价于:

WHERE created_at < '2026-07-17 10:00:00'
   OR (created_at = '2026-07-17 10:00:00' AND id < 12345)

但行值写法更简洁,而且 PostgreSQL、MySQL 8.0+、SQLite 都支持。注意 MySQL 5.7 及以下对行值比较的优化不好,可能用不上索引,要么升级要么拆成 OR 写法。

设计要点

1. 排序键必须稳定可比较(数值、时间戳、字符串都行,但不能是 NULL 混着)
2. 必须包含一个唯一列(通常是 id)作为 tiebreaker,否则边界处会漏/重
3. 游标值编码:通常把 (created_at, id) Base64 编码后传给前端,前端原样回传。不要让前端拼 SQL——SQL 注入风险
4. 游标过期处理:如果游标指向的记录被删了,WHERE id > X 依然有效(因为是范围比较,不是等值);但如果中间插入了新数据,游标位置会"漂移",这是游标分页的固有特性

六、排序方向:正序、倒序与双向游标

倒序(Timeline 场景)

大多数列表是倒序的(最新的在前):

WHERE id < :last_id        -- 单列倒序
WHERE (created_at, id) < (:last_created, :last_id)  -- 多列倒序

游标是 <,因为越往后值越小。

双向游标(既要上一页又要下一页)

比如聊天记录,用户可以往上翻历史(取更早的),也可以往下回到最新(取更晚的)。这时需要两个游标:一个 before(取更早),一个 after(取更新)。

-- 往上翻(更早的消息)
WHERE id < :before_cursor ORDER BY id DESC LIMIT 20;

-- 往下翻(更晚的消息)
WHERE id > :after_cursor ORDER BY id ASC LIMIT 20;

前端需要分别维护这两个游标。典型的 API 设计:

GET /messages?before=xxx   → 返回 { data, has_more_before, next_before }
GET /messages?after=yyy    → 返回 { data, has_more_after, next_after }

这也是 Slack、Discord 这类聊天产品的标准做法。

七、什么时候只能用 OFFSET:跳页与总数

说了游标这么多好,但有些场景 OFFSET 不可替代。

1. 需要跳页

用户在分页条上点"第 50 页"。你不知道第 50 页对应的游标是多少,只能 OFFSET (50-1)*20 LIMIT 20。

这种交互式跳页(尤其后台管理、报表),OFFSET 是唯一选择。

2. 需要"共 N 页 / 共 N 条"

显示 共 320 页 需要知道 total count,这又是一个坑——SELECT COUNT(*) 在大表上同样慢。

实际工程里的妥协:

• 后台/管理端:接受 OFFSET + COUNT 的慢,反正用户量小
• 前台/C 端:不显示总数,只显示"还有更多"或近似值(约 1000+ 条)
• 用 Redis/缓存维护近似总数,异步更新

3. 需要稳定排序的分页

OFFSET 有一个游标做不到的特性:在数据不变时,OFFSET 给出的页是稳定的、可复现的。游标在数据插入/删除时会漂移,OFFSET 不会(只要数据集不变)。

这对测试、对账、报表导出有时是刚需。

八、混合策略:鱼与熊掌

实战中很少纯游标或纯 OFFSET,更多是混合。

策略 A:前几页 OFFSET,深翻切游标

用户行为统计有个规律:95% 的访问集中在前 5 页。深翻页的是少数(且往往是爬虫)。

所以可以:前 N 页用 OFFSET(快,因为 OFFSET 小),超过 N 页后强制切换成"加载更多"(游标)或直接限制最大 OFFSET。

-- 第 1-10 页:OFFSET
LIMIT 20 OFFSET 180;   -- 还能接受

-- 第 11 页之后:拒绝跳页,引导用游标
-- 或返回 400: "请使用 cursor 参数"

很多大厂(知乎、掘金、Google 搜索)就是这么干的——搜索结果最多翻 10-20 页,再往后就不让翻了。

策略 B:无限滚动 + 游标

如果是 Feed 流、瀑布流、聊天记录这种天然不需要跳页的场景,直接全量用游标。UI 上没有页码,只有"加载更多",用户心智模型也匹配。

这是游标的最佳战场。

策略 C:ID 游标 + LIMIT 兜底

对于不确定要不要跳页的接口,可以同时支持 offset 和 cursor 参数:

GET /articles?offset=0&limit=20          # OFFSET 模式
GET /articles?cursor=eyJpZCI6MTIzNDV9&limit=20  # 游标模式

让前端按场景选择。这是一种务实的工程妥协——不追求纯粹,追求好用。

九、性能对比实测

给个真实场景的参考(本地 PG,500 万行 articles 表,id 主键):

游标的耗时是一条水平线,OFFSET 是一条斜向上的直线。当 OFFSET 大到一定程度,接口实际上已经不可用。

EXPLAIN 也能看出差异。OFFSET 查询的 rows 估算值是 OFFSET + LIMIT,游标查询是 LIMIT。

小结

1. OFFSET 不是免费的:它要扫掉前 N 行才返回 M 行,深翻页是 O(N) 扫描,接近查全表
2. 游标分页 O(1):WHERE id > last_id 直接走索引区间扫描,无论翻多深都恒定耗时
3. 游标依赖索引:排序键必须有索引,否则同样退化;多列排序要用行值比较 (a, b) > (x, y),并带上唯一列做 tiebreaker
4. 游标不能跳页:只能前/后翻,不能直接跳到第 N 页,也不支持"共 N 页"展示;Timeline 用 < 游标,双向翻页需要两个游标
5. 混合策略最实用:前 N 页用 OFFSET、深翻切游标;Feed/聊天直接全游标;需要跳页和总数时 OFFSET 不可替代

一句话总结:OFFSET 是"跳过 N 行",游标是"从 X 开始"。前者按行数付钱,越翻越贵;后者按索引付钱,一视同仁。能不用 OFFSET 就别用——除非你真的需要跳页。