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(*)。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

优化分页查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通