深分页实测:OFFSET 翻到 90 万行慢 1000 倍,游标分页为何恒定 0.015ms
一个没人愿意承认的问题
后台列表页翻到第 3000 页时,接口从 12ms 掉到 900ms。日志里 SQL 没变,索引也在,唯一的变化是 OFFSET 后面的数字变大了。这不是数据库"变慢",而是分页写法在大偏移量下暴露出的固有代价:数据库必须先把前 N 行读出来丢掉,才能给你第 N+1 行。
本文用 SQLite 造 100 万行数据,把三种分页写法放在同一张表上对比,给出可复现的数字和一份选型边界。
实测环境与方法
- 表
events(id, user_id, created_at, kind, payload),100 万行,payload120 字节,模拟带大字段的业务表 - 索引:
CREATE INDEX idx_created ON events(created_at, id) - 计时:
time.perf_counter(),每档重复 5 次取中位数,首次查询做预热 - 内存库、
journal_mode=OFF,排除磁盘 I/O 干扰,只看执行器开销
-- A 传统 OFFSET
SELECT * FROM events ORDER BY created_at LIMIT 20 OFFSET ?;
-- B 延迟关联:先在覆盖索引上翻页,再回表取宽列
SELECT e.* FROM events e JOIN
(SELECT id FROM events ORDER BY created_at LIMIT 20 OFFSET ?) t
ON e.id = t.id ORDER BY e.created_at;
-- C 游标(keyset)
SELECT * FROM events WHERE created_at > ? ORDER BY created_at LIMIT 20;
数据一:单页耗时的斜率
| offset | OFFSET | 延迟关联 | 游标 |
|---|---|---|---|
| 0 | 0.015 ms | 0.022 ms | 0.015 ms |
| 1 万 | 0.170 ms | 0.106 ms | 0.015 ms |
| 10 万 | 1.896 ms | 0.813 ms | 0.015 ms |
| 50 万 | 8.410 ms | 3.899 ms | 0.015 ms |
| 90 万 | 15.495 ms | 5.769 ms | 0.015 ms |
OFFSET 的耗时与偏移量近似线性:90 万行时是首页的 1033 倍;游标分页五档全是 0.015ms,因为它的代价只取决于页大小,与位置无关。连续翻 30 页(从第 44 万页起):OFFSET 累计 475.1ms,游标 0.5ms,相差 986 倍。
延迟关联把宽列回表推迟到最后一页,所以降到 OFFSET 的 1/3 到 1/2.7,但斜率依旧存在——它只是把"每行搬 5 列"变成"每行搬 1 列"。
数据二:为什么是线性
用 set_progress_handler 统计 VM 指令步数(近似扫描行数):
| offset | OFFSET 步数 | 延迟关联步数 | 游标步数 |
|---|---|---|---|
| 0 | 211 | 498 | 193 |
| 10 万 | 300 211 | 200 498 | 191 |
| 90 万 | 2 700 211 | 1 800 498 | 191 |
规律非常干净:OFFSET 每跳过一个 offset 就要花 3 条 VM 指令,延迟关联是 2 条(少了一次回表取值),游标恒定为 191 条。90 万偏移时两者相差 14138 倍。结论:OFFSET 的成本不是"读了多少行",而是"丢了多少行",这部分在 SQL 层面无法被优化器消除。
游标分页的三个工程约束
游标快,但它不是免费替换:
- 不能跳页。只能上一页/下一页,产品上的"跳到第 37 页"必须改成"加载更多"或按时间区间筛选。
- 排序键必须唯一且稳定。
created_at若有重复,WHERE created_at > 上一页末值会漏行。正确写法是复合游标,把主键带上:
本次数据集WHERE (created_at, id) > (?, ?) ORDER BY created_at, id LIMIT 20created_at无重复(重复组数 0),所以单列游标安全;线上务必先验证唯一性。 - 不支持随机深跳。想"回到第 500 页"只能靠服务端保存快照,或者用
created_at区间重新定位。
另外,游标可以双向:WHERE created_at < ? ORDER BY created_at DESC LIMIT 20 就是上一页;OFFSET 在数据持续写入时会漂移(第 3000 页的内容每刷新一次都不同),这是很多人忽略的一致性 bug。
选型建议
- 面向用户的列表、无限滚动、导出流式读取:直接用游标,代价恒定
- 必须保留页码的后台系统:浅页(offset < 1 万)用 OFFSET 完全够用;深页改延迟关联,或加
created_at区间条件把 offset 压小 - 总数统计单独做:
COUNT(*)在百万行表上本身就要几百毫秒,和分页解耦,用近似值或缓存 - 索引体积也要算账:本次 100 万行表 135.0MB,
created_at索引 16.2MB,多带一列排序键的复合索引会让写入放大
一句话总结:分页的复杂度不该由"用户翻到第几页"决定。把 offset 从查询里拿掉,等于把 O(offset) 的隐藏成本直接删掉。
(本文数据由本地实跑 pagination_bench.py / pagination_bench2.py 得到,环境 Python 3.13 + SQLite 3.53.1)
- 点赞
- 收藏
- 关注作者
评论(0)