PostgreSQL / 系统课时
优化分页查询
优化分页查询
本课解决页码越大查询越慢的问题。你将理解 OFFSET 必须扫描并丢弃前置行的代价,并使用“上一页最后一条记录”作为游标,实现稳定且可扩展的翻页。
学习目标
- 解释
LIMIT ... OFFSET ...的深分页成本 - 使用复合排序键实现游标分页
- 正确处理同一时间多条记录和翻页边界
OFFSET 为什么会变慢
OFFSET 100000 LIMIT 20 并不会直接跳到第 100001 行。数据库通常仍需按排序规则找到前 100020 行,再丢弃前 100000 行。页码越深,扫描、排序和丢弃的工作越多。
OFFSET 适合数据量较小、允许任意跳页的后台列表。面向时间线、订单流或无限滚动时,应优先考虑游标分页。
使用复合游标翻页
下面按 created_at DESC, id DESC 排序。第一页结束后,把最后一行的时间和主键作为下一页游标;主键用于处理多行时间相同的情况。
sql
CREATE TEMP TABLE posts (
id bigint PRIMARY KEY,
created_at timestamp NOT NULL,
title text NOT NULL
);
INSERT INTO posts (id, created_at, title) VALUES
(1, '2026-07-30 09:00:00', 'A'),
(2, '2026-07-30 09:10:00', 'B'),
(3, '2026-07-30 09:20:00', 'C'),
(4, '2026-07-30 09:30:00', 'D'),
(5, '2026-07-30 10:00:00', 'E'),
(6, '2026-07-30 10:00:00', 'F');
CREATE INDEX idx_posts_created_id
ON posts (created_at DESC, id DESC);
-- 第一页
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2;
-- 下一页:游标来自第一页最后一行,即 2026-07-30 10:00:00、id=5
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (TIMESTAMP '2026-07-30 10:00:00', 5)
ORDER BY created_at DESC, id DESC
LIMIT 2;预期结果
text
第一页:
6 | 2026-07-30 10:00:00 | F
5 | 2026-07-30 10:00:00 | E
下一页:
4 | 2026-07-30 09:30:00 | D
3 | 2026-07-30 09:20:00 | C游标条件必须匹配排序方向
倒序排序使用“小于上一页末行”的条件;正序排序则使用“大于”。比较字段、字段顺序和排序方向必须完全一致,否则会出现重复、漏行或顺序跳动。
接口通常把 created_at 和 id 编码成一个不透明游标返回给客户端。服务端解码后参数化查询,不要直接拼接客户端字符串。
选择页码还是游标
游标分页性能稳定,也能较好应对翻页期间的新数据插入,但不能自然跳到任意页。页码分页便于定位第几页,却会随偏移量增大而变慢。
需要显示总页数时,可把总数统计与列表查询分开处理,并根据业务允许程度使用缓存或异步统计,避免每次翻页都执行高成本 COUNT(*)。
按自己的节奏,确认真正理解。