7 LIMIT 与 OFFSET

LIMIT n 至多返回 n 行。OFFSET m 跳过结果中的前 m 行。两者结合，就能实现传统的按页码分页：每页 p 行的第 k 页写作 LIMIT p OFFSET (k - 1) * p。没有 ORDER BY，“前”就没有意义；使用 LIMIT/OFFSET 时，一定要明确指定顺序。

本章要点

• 用 LIMIT 限制结果大小
• 用 OFFSET 跳过行
• 分页方式及其代价
• 为什么偏移量分页在大表上难以扩展
• 更好的替代方式：键集分页（也称游标分页）

示例

-- First two rows, ordered by id
SELECT * FROM fruits ORDER BY id LIMIT 2;

-- Skip one, then take two: rows 2 and 3
SELECT * FROM fruits ORDER BY id LIMIT 2 OFFSET 1;

-- Page 2 of 3 per page
SELECT * FROM fruits ORDER BY id LIMIT 3 OFFSET 3;

-- Keyset pagination: rows with id > last_seen_id
SELECT * FROM fruits WHERE id > 2 ORDER BY id LIMIT 2;

LIMIT 2 OFFSET 1 可以读作“跳过一行，再取两行”。在内部，PostgreSQL 仍然必须计算直到 OFFSET + LIMIT 为止的所有行，再丢弃偏移量对应的那些行。如果页码从 1 开始，每页 50 行，获取第 10,000 页就意味着要处理 500,000 行。偏移量分页用于前几页还不错，但用于深度分页就很糟糕。

键集分页（也称游标分页或 seek 分页）使用上次看到的值作为过滤条件：WHERE id > $last_id ORDER BY id LIMIT 50。无论已经翻到多后面的页，查询都只访问接下来的 50 行。这需要有索引的列（通常是主键，或 created_at, id），但也是生产系统中的标准做法。

运行示例

运行方式（需要前面章节的 fruits 表）：

$ psql -f source/limit-offset.sql postgres
 id |  name
----+--------
  1 | apple
  2 | banana
(2 rows)

 id |  name
----+--------
  2 | banana
  3 | cherry
(2 rows)
 ...

常见陷阱

• 没有 ORDER BY 的 LIMIT 10 返回的十行只是某些行，每次执行选中的行还可能不同。一定要排序。
• 排序键相同的两行可能在不同页之间交换位置。增加一个用于打破并列的排序键（ORDER BY created_at DESC, id DESC），让分页保持稳定。
• 很大的偏移量会很慢。对于“无限滚动”信息流的第 1,000 页，应改用键集分页。
• OFFSET 也会出现在 SELECT ... FOR UPDATE 这类用法中，与行锁配合使用时可能产生问题；如果需要分批处理，基于主键的键集分页同样是更安全的方式。

下载

SQL 文件： limit-offset.sql · 全部示例文件 · 准备工作