后台翻到第 5000 页就超时：深分页与改写
运营后台的订单列表翻到第 5000 页直接超时。你治好了自己的 OFFSET 恐惧症。
学 · 40 min
01SELECT * 的三项代价
场景深分页优化的第一步不是改分页，是先把 select * 扫掉。
① 传输：列表页要 20 行 8 列，select * 把整行几十列全传；② 回表：所有列都要回堆取，Index Only Scan 的机会被掐死；③ 扩展性：表加一列，这个接口的流量悄悄变大。只取需要的列是成本最低的优化。
-- 反面 select * from orders order by created_at desc offset 100000 limit 20; -- 正面 select id, user_id, status, total_amount, created_at from orders order by created_at desc offset 100000 limit 20;易错select * 还会破坏 prepare 语句的缓存效果（结构一变计划全重算）。
02谓词下推与提前过滤：能早过滤就别晚过滤
场景「先 join 宽表再过滤」和「先过滤再 join」结果一样、速度差十倍。
数据越早变小，后面每一步越便宜：过滤条件尽量推到扫描层（走索引），聚合前先收窄范围。深分页的「延迟关联」就是它的应用：第一步只在索引里拿 20 个 id（窄而快），第二步再拿 id 回表取全部列。
-- 延迟关联：先 id 后详情 select o.* from ( select id from orders where user_id = '...' order by created_at desc offset 100000 limit 20 -- 只在索引层翻页 ) page join orders o on o.id = page.id; -- 20 次回表易错外层的排序和过滤条件别丢：内层不排序，外层顺序就是乱的。
03OFFSET 深分页为什么越翻越慢（要先扫过并丢弃前 N 行）
场景第 1 页 5ms，第 5000 页 8 秒--OFFSET 的工作方式决定了它。
offset 100000 limit 20的执行 = 扫过前 100000 行、全部丢掉，再取 20 行。翻得越深，丢弃越多，耗时线性增长。OFFSET 的本意是「跳过 N 行」，而跳过也是要一行行数过去的。-- 耗时曲线实验 \timing on select id from orders order by id offset 0 limit 20; select id from orders order by id offset 10000 limit 20; select id from orders order by id offset 500000 limit 20; -- 记录三档耗时，画出来就是一条上升直线易错愿意翻到 5000 页的多半是爬虫不是用户--限页深 + 返回条数上限，本身就是防爬设计。
04keyset 分页（游标分页）的写法与限制
场景治本方案：让「下一页」的代价和「第一页」一样。
记住上一页最后一行的排序键，下一页从它接着取：
where (排序键) < :last_value order by 排序键 desc limit 20。无论第几页，都只扫 20 行。排序键必须唯一且有序（复合排序加 id 兜底），否则边界行会漏或重。-- 下一页：拿着上一页最后的 (created_at, id) 来 select id, created_at, total_amount from orders where (created_at, id) < (:last_created_at, :last_id) -- 行比较语法 order by created_at desc, id desc limit 20;易错限制要主动说：不能随机跳页（只支持上一页 / 下一页）、排序键中途不能变。产品要跳页就和「延迟关联」组合用。
05精确 count 的替代方案
场景列表页要显示「共 1,234,567 条」--这个数字本身就要扫几秒。
PG 的
count(*)要真数（MVCC 下不能用索引元数据偷懒），百万级秒起。替代：①pg_class.reltuples估算（毫秒级，误差百分之几）；② 计数表 + 触发器维护（精确、有写放大）；③ 前端展示「约 123 万条」。绝大多数列表页，用户根本不在乎精确总数。-- 估算版（毫秒） select reltuples::bigint from pg_class where relname = 'orders'; -- 精确版（慢） select count(*) from orders;易错reltuples 依赖统计新鲜度（D41）；号称「精确」的功能页（对账、结算）别用估算。
练 · 65 min
-
OFFSET 0 / 10000 / 500000各跑一次，记录耗时曲线参考答案
预期：三档耗时近似线性增长（第 0 页毫秒级，50 万页慢一到两个量级）--OFFSET 不是「跳到第 N 行」，是把前 N 行扫出来再丢掉。把三档数字记进 notes.md，画出来就是一条上升直线。
\timing on select id from orders order by id offset 0 limit 20; select id from orders order by id offset 10000 limit 20; select id from orders order by id offset 500000 limit 20; - 把同一分页改写成 keyset 分页（
WHERE id < :last_id ORDER BY id DESC LIMIT 20）参考答案
(a, b) < (x, y) 的行比较是 PG 的标准写法。无论翻到第几页，耗时都和第一页同量级（只扫 20 行）--和第 1 题的曲线对比就是本天的核心结论。限制主动说：只能上一页/下一页，不能随机跳页，排序键中途不能换。
-- 第一页（按 id 降序的极简版） select id, created_at, total_amount from orders order by id desc limit 20; -- 下一页：把上一页最后一行的 id 代进来 select id, created_at, total_amount from orders where id < '上一页最后一行的id'::uuid order by id desc limit 20; -- 业务排序版（按 created_at desc）：复合排序键必须带 id 兜底 select id, created_at, total_amount from orders where (created_at, id) < ('上一页末行的created_at', '上一页末行的id'::uuid) order by created_at desc, id desc limit 20; - 用延迟关联优化：先取 id 再回表取详情
参考答案
谓词下推的应用：内层只取 id（窄、可走索引），外层用 20 个 id 回表取全部列。对比直接 offset 500000 的版本，深页耗时明显下降。产品非要跳页时用这招，能顺序翻页就用 keyset。
-- 前提：created_at 上有索引（没有就先补一个） create index concurrently if not exists idx_orders_created on orders (created_at); select o.id, o.user_id, o.status, o.total_amount, o.created_at from ( select id from orders order by created_at desc offset 500000 limit 20 -- 只在索引层翻页，先拿 20 个 id ) page join orders o on o.id = page.id order by o.created_at desc; - 用
pg_class.reltuples做近似总数，对比精确 count 的耗时参考答案
预期：精确版慢两三个量级。reltuples 的误差取决于上次 analyze（D41），一般百分之几以内；对账、结算这类号称「精确」的页面别用估算。
\timing on -- 精确版：百万行逐行可见性检查 select count(*) from orders; -- 估算版：读统计信息 select reltuples::bigint as 估算行数 from pg_class where relname = 'orders'; - 做一张三种分页方案的对比表（耗时、能否跳页、适用场景）
参考答案
对比表三行：OFFSET--能随机跳页，但越翻越慢，只适合浅分页；keyset--耗时恒定最优，但只能上一页/下一页、排序键不能中途变，适合信息流和无限滚动；延迟关联--仍用 OFFSET 但只在索引层翻页，深分页救急用。列建议：方案 / 耗时（用本天实测数字填）/ 能否跳页 / 适用场景。