深分页实测:OFFSET 翻到 90 万行慢 1000 倍,游标分页为何恒定 0.015ms

举报
yd_243452805 发表于 2026/11/12 07:37:57 2026/11/12
【摘要】 100 万行数据实测三种分页写法:OFFSET 在 90 万偏移处单页 15.5ms、需扫 270 万条 VM 指令,而游标分页恒定 0.015ms,相差 14138 倍。文中给出延迟关联收益、复合游标写法与选型边界。

一个没人愿意承认的问题

后台列表页翻到第 3000 页时,接口从 12ms 掉到 900ms。日志里 SQL 没变,索引也在,唯一的变化是 OFFSET 后面的数字变大了。这不是数据库"变慢",而是分页写法在大偏移量下暴露出的固有代价:数据库必须先把前 N 行读出来丢掉,才能给你第 N+1 行。

本文用 SQLite 造 100 万行数据,把三种分页写法放在同一张表上对比,给出可复现的数字和一份选型边界。

实测环境与方法

  • 表 events(id, user_id, created_at, kind, payload),100 万行,payload 120 字节,模拟带大字段的业务表
  • 索引: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 层面无法被优化器消除。

游标分页的三个工程约束

游标快,但它不是免费替换:

  1. 不能跳页。只能上一页/下一页,产品上的"跳到第 37 页"必须改成"加载更多"或按时间区间筛选。
  2. 排序键必须唯一且稳定。created_at 若有重复,WHERE created_at > 上一页末值 会漏行。正确写法是复合游标,把主键带上:
    WHERE (created_at, id) > (?, ?) ORDER BY created_at, id LIMIT 20
    
    本次数据集 created_at 无重复(重复组数 0),所以单列游标安全;线上务必先验证唯一性。
  3. 不支持随机深跳。想"回到第 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)

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。