分页（LIMIT / OFFSET）

分页是列表类业务的刚需。本节讲解 LIMIT 与 OFFSET 语法、LIMIT ALL、标准语法 FETCH FIRST ... ROWS ONLY、OFFSET 深分页的性能问题及原理、键集分页（Keyset Pagination） 方案、用 ctid 或唯一键做游标、分页总数统计方案，以及 LIMIT 必须与 ORDER BY 配合的原因。学完本节，你将能设计出在千万级数据上依然稳定的分页方案。

前置知识

阅读本节前，建议先了解：排序（ORDER BY）

基础概念

分页的本质是"在确定的顺序下，取第 N 段数据"。因此它天然依赖排序，而分页方案的选择直接取决于如何定位起始位置。

术语	英文	说明
分页	Pagination	把结果集切分为多页返回
偏移量	Offset	跳过前面多少行
键集分页	Keyset Pagination	用上一页末尾的键值定位下一页起点
游标	Cursor	指代"上次读到哪里"的标记
深分页	Deep Pagination	页码很大、OFFSET 很大的分页
行值比较	Row Value Comparison	元组比较语法 (a, b) < (x, y)
顺序扫描	Seq Scan	逐行读取，OFFSET 大时的主要代价来源

OFFSET 的性能问题源于执行方式：它必须先产生前面所有行，再逐行丢弃。因此 OFFSET 1000000 意味着要先扫描并丢弃 100 万行。

分页方式	定位方式	时间复杂度	能否跳页
LIMIT + OFFSET	从头数到第 N 行	O(offset + limit)	可以
键集分页	从索引定位到键值位置	O(log n + limit)	只能顺序翻
游标（DECLARE CURSOR）	服务端保持位置	摊薄为 O(总行数)	只能顺序
ctid 游标	用物理位置定位	O(log n + limit)	只能顺序

语法与用法

-- 写法一：MySQL 风格的 LIMIT / OFFSET
SELECT ... FROM ... [ORDER BY ...]
LIMIT 行数 [OFFSET 偏移量];

-- 写法二：SQL 标准语法
SELECT ... FROM ... [ORDER BY ...]
OFFSET 偏移量 ROWS
FETCH { FIRST | NEXT } 行数 { ROW | ROWS } ONLY;

准备本节贯穿使用的示例表：

-- 订单表：用 created_at + id 组合作为稳定的分页排序键
CREATE TABLE orders (
  id         INTEGER       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT          NOT NULL,
  customer   TEXT          NOT NULL,
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT          NOT NULL,
  created_at TIMESTAMPTZ   NOT NULL
);

-- 造一批数据便于观察分页行为
INSERT INTO orders (order_no, customer, amount, status, created_at)
SELECT
  'SO-' || lpad(i::text, 6, '0'),
  'user_' || (i % 100),
  round((random() * 5000)::numeric, 2),
  (ARRAY['pending','paid','shipped','closed'])[1 + (i % 4)],
  TIMESTAMPTZ '2026-01-01' + (i || ' minutes')::interval
FROM generate_series(1, 100000) AS g(i);

-- 分页必备索引（排序键顺序与查询一致）
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

三种等价的写法：

-- 1. MySQL 风格（PostgreSQL 支持）
SELECT id, order_no, amount
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

-- 2. SQL 标准语法（推荐，可移植性更好）
SELECT id, order_no, amount
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

-- 3. LIMIT ALL：不限制行数，等价于省略 LIMIT
SELECT id, order_no, amount
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT ALL OFFSET 100;

详细说明

LIMIT 与 OFFSET 语法

语法形式	含义	备注
LIMIT n	最多返回 n 行	不是"一定返回 n 行"
LIMIT 0	不返回数据	可用于只取列结构
LIMIT ALL	无行数限制	等价于省略
OFFSET n	跳过前 n 行	必须配 ORDER BY
OFFSET n ROWS	同上（标准写法）	可带 ROWS 关键字
FETCH FIRST n ROWS ONLY	标准语法的 LIMIT n	FIRST / NEXT 等价
FETCH FIRST ROW ONLY	只取一行	单数形式

-- 只取一行（存在性检查常用）
SELECT id, order_no FROM orders ORDER BY id LIMIT 1;

-- 只取列结构，不返回数据
SELECT id, order_no, amount FROM orders LIMIT 0;

-- 取前 10 行之后的全部
SELECT id FROM orders ORDER BY id LIMIT ALL OFFSET 10;

-- 标准语法：取第 2 页（每页 20）
SELECT id, order_no FROM orders
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

FETCH FIRST ... ROWS ONLY 标准语法

FETCH FIRST 是 SQL 标准写法，PostgreSQL 同时支持它与 LIMIT。二者在功能上完全等价，选择哪一种取决于可移植性需求。

-- 语义完全等价的三种写法
SELECT id FROM orders ORDER BY id LIMIT 5;                          -- 最常用
SELECT id FROM orders ORDER BY id FETCH FIRST 5 ROWS ONLY;         -- SQL 标准
SELECT id FROM orders ORDER BY id OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;  -- 显式 OFFSET

特性	LIMIT / OFFSET	FETCH FIRST / OFFSET
所属标准	PostgreSQL / MySQL 扩展	SQL:2008 标准
与 OFFSET 顺序	必须先 LIMIT 后 OFFSET	必须先 OFFSET 后 FETCH
可移植性	高（MySQL、SQLite）	高（Oracle、DB2、SQL Server 2012+）
支持 LIMIT ALL	是	无对应写法（用 FETCH FIRST ... 大数）

-- 错误！关键字顺序不能颠倒
SELECT id FROM orders ORDER BY id FETCH NEXT 10 ROWS ONLY OFFSET 20 ROWS;

-- 正确
SELECT id FROM orders ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

OFFSET 的性能问题

OFFSET 的根本代价在于：需要先生成并丢弃前面 N 行。执行计划中会出现 Limit 节点上方的 Offset 逻辑，实际由 Limit 节点内部的 offset 参数完成丢弃。

-- 首页：只读 20 行
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

                                         QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.42..1.80 rows=20 width=32) (actual time=0.020..0.031 rows=20 loops=1)
   Buffers: shared hit=24
   ->  Index Scan using idx_orders_created_id on orders
         (cost=0.42..6903.42 rows=100000 width=32) (actual time=0.018..0.028 rows=20 loops=1)
         Index Cond: true
         Buffers: shared hit=24
 Planning Time: 0.156 ms
 Execution Time: 0.052 ms
(7 rows)

-- 深分页：OFFSET 50000，必须读出并丢弃 5 万行
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

                                          QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=1381.09..1382.47 rows=20 width=32) (actual time=34.512..34.539 rows=20 loops=1)
   Buffers: shared hit=1674
   ->  Index Scan using idx_orders_created_id on orders
         (cost=0.42..6903.42 rows=100000 width=32) (actual time=0.019..29.417 rows=50020 loops=1)
         Index Cond: true
         Buffers: shared hit=1674
 Planning Time: 0.168 ms
 Execution Time: 34.622 ms
(7 rows)

关键差异在于 rows=50020：索引扫描实际读出了 50020 行，其中 50000 行被丢弃。Execution Time 从 0.052 ms 增长到 34.622 ms，并且偏移量越大增长越接近线性。

-- 偏移量与耗时的近似关系（10 万行表）
 OFFSET   | 实际读取行数 | 执行时间（示意）
----------+--------------+------------------
 0        | 20           | 0.05 ms
 1000     | 1020         | 0.7 ms
 10000    | 10020        | 6.9 ms
 50000    | 50020        | 34.6 ms
 90000    | 90020        | 62.3 ms

即便走了索引，代价仍随 OFFSET 线性增长，因为索引也要逐条读取、逐条丢弃。这就是深分页"越翻越慢"的原因。

键集分页（Keyset Pagination）

键集分页的核心思想：不用"跳过多少行"，而用"从哪个位置继续"。把上一页最后一行的排序键记下来，下一页用 WHERE 条件直接定位。

-- 第 1 页
SELECT id, order_no, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

 id | order_no | amount  |      created_at
----+----------+---------+-----------------------
  ...
---- 假设最后一行是 (created_at = 2026-03-11 10:39:00+08, id = 99980) ----

-- 第 2 页：用行值比较直接定位，无需扫描丢弃
SELECT id, order_no, amount, created_at
FROM orders
WHERE (created_at, id) < (TIMESTAMPTZ '2026-03-11 10:39:00+08', 99980)
ORDER BY created_at DESC, id DESC
LIMIT 20;

行值比较（Row Value Comparison）是 PostgreSQL 支持的元组比较语法，(a, b) < (x, y) 等价于 a < x OR (a = x AND b < y)，但能直接用索引定位：

-- 两者逻辑等价，但行值比较写法更简洁、更不易出错
SELECT id FROM orders WHERE created_at < '2026-03-11 10:39:00+08'
   OR (created_at = '2026-03-11 10:39:00+08' AND id < 99980)
ORDER BY created_at DESC, id DESC LIMIT 20;

-- 推荐：行值比较
SELECT id FROM orders
WHERE (created_at, id) < (TIMESTAMPTZ '2026-03-11 10:39:00+08', 99980)
ORDER BY created_at DESC, id DESC LIMIT 20;

性能差异是数量级的：

-- 键集分页的执行计划：只读 20 行
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount FROM orders
WHERE (created_at, id) < (TIMESTAMPTZ '2026-03-11 10:39:00+08', 99980)
ORDER BY created_at DESC, id DESC
LIMIT 20;

                                          QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..1.80 rows=20 width=32) (actual time=0.019..0.028 rows=20 loops=1)
   Buffers: shared hit=25
   ->  Index Scan using idx_orders_created_id on orders
         (cost=0.42..1911.02 rows=27778 width=32) (actual time=0.017..0.026 rows=20 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-03-11 10:39:00+08'::timestamptz, 99980))
         Buffers: shared hit=25
 Planning Time: 0.142 ms
 Execution Time: 0.048 ms
(7 rows)

无论翻到第几页，实际读取行数都稳定在 20 行附近，执行时间基本恒定。两种分页方案的完整对比：

对比项	LIMIT + OFFSET	键集分页（Keyset）
定位方式	从头数 N 行	从键值直接定位
大偏移性能	差，线性劣化	好，恒定
执行代价	O(offset + limit)	O(log n + limit)
能否跳页	可以任意页	只能顺序翻
总数信息	可算总页数	无法直接得知
数据变动影响	可能重复或遗漏	相对稳定
实现复杂度	低	中（需传递游标）
适用场景	后台管理少量页	信息流、无限滚动、导出

用唯一键或 ctid 做游标

游标必须有唯一性，否则边界处会重复或遗漏。几种常见的游标选择：

游标类型	示例	优点	缺点
自增主键	id > 1000	简单、高效、稳定	仅适用于按 id 排序
时间 + 主键	(created_at, id) < (?, ?)	支持时间序、稳定	需要复合索引
业务唯一键	(status, order_no) > (?, ?)	语义直观	需保证唯一
ctid	ctid > '(0,100)'	无需额外列	不稳定（更新会变）、跨表不能比

-- 单列游标：最简单
SELECT id, order_no FROM orders
WHERE id > 1000
ORDER BY id
LIMIT 20;

-- 时间 + 主键游标：支持时间序
SELECT id, order_no, created_at FROM orders
WHERE (created_at, id) > (TIMESTAMPTZ '2026-01-02 10:00:00+08', 1500)
ORDER BY created_at, id
LIMIT 20;

ctid 是行的物理位置，可作为快速游标，但有明显局限：

-- ctid 游标：无需额外排序键
SELECT ctid, id, order_no
FROM orders
ORDER BY ctid
LIMIT 5;

  ctid   |  id   | order_no
---------+-------+-----------
 (0,1)   |     1 | SO-000001
 (0,2)   |     2 | SO-000002
 (0,3)   |     3 | SO-000003
 (0,4)   |     4 | SO-000004
 (0,5)   |     5 | SO-000005
(5 rows)

-- 用 ctid 做游标翻页
SELECT ctid, id, order_no FROM orders
WHERE ctid > '(0,5)'
ORDER BY ctid
LIMIT 5;

-- 不推荐：ctid 作为对外游标暴露给前端
-- 原因 1：行更新后 ctid 会变化（MVCC 会写入新版本）
UPDATE orders SET amount = amount + 1 WHERE id = 3;
-- 该行的 ctid 已改变，之前记录的 ctid 指向旧版本甚至无效位置

-- 原因 2：VACUUM FULL / 分区/集群操作会重排物理位置，游标失效
VACUUM FULL orders;

结论：ctid 只适合单次会话内的批量处理（如后台批处理脚本），不适合作为跨请求的持久游标。对外 API 一律用业务唯一键。

LIMIT 必须配合 ORDER BY

不带 ORDER BY 的 LIMIT 返回行是任意的，取决于扫描路径。这会导致极其隐蔽的 bug：同一查询两次执行可能返回不同结果，分页时大量重复或遗漏。

-- 错误！没有 ORDER BY，返回哪些行不确定
SELECT id, order_no FROM orders LIMIT 20 OFFSET 0;
SELECT id, order_no FROM orders LIMIT 20 OFFSET 20;   -- 可能与前页重复

-- 正确
SELECT id, order_no FROM orders ORDER BY id LIMIT 20 OFFSET 0;
SELECT id, order_no FROM orders ORDER BY id LIMIT 20 OFFSET 20;

即使加了 ORDER BY，如果排序键不唯一，分页仍然不稳定：

-- 不稳定：status 只有 4 个取值，大量行顺序不确定
SELECT id, status FROM orders ORDER BY status LIMIT 20 OFFSET 20;

-- 稳定：追加唯一键
SELECT id, status FROM orders ORDER BY status, id LIMIT 20 OFFSET 20;

分页总数统计方案

分页界面常需要"共 N 条 / 第 x 页"。精确统计代价可能很高。

方案	语句	精确性	代价
精确 count(*)	SELECT count(*) FROM t WHERE ...	精确	高，需扫描全部匹配行
估算（优化器）	见下方 EXPLAIN 技巧	估算	极低
系统统计表	pg_class.reltuples	粗估	极低
增量计数	维护计数表 / 触发器	精确	写放大
只算页数上限	LIMIT N+1 探测	部分	低

-- 方案一：精确统计（配合索引可走 Index Only Scan）
SELECT count(*) FROM orders WHERE status = 'paid';

-- 方案二：用优化器估算行数（无需真正执行）
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- 输出中的 rows=25000 即为估算值，精度通常足够展示"约 2.5 万条"

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..1791.00 rows=25000 width=52)
   Filter: (status = 'paid'::text)
(2 rows)

-- 方案三：用 EXPLAIN (FORMAT JSON) 在程序里取出估算值
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE status = 'paid';

-- 方案四：N+1 探测法，判断是否还有下一页
SELECT id, order_no FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 21;      -- 期望取 20 条，多取 1 条用于判断
-- 若返回 21 条 → 还有下一页；若 ≤ 20 条 → 已到末页

-- 方案五：估算全表行数（VACUUM/ANALYZE 后较准）
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname = 'orders';

方案四（N+1 探测）是无限滚动场景的最佳选择：不需要总数，只需知道"有没有下一页"，代价仅比正常查询多读一行。

实战示例

下面用"订单列表的完整分页接口"串联本节要点。

-- 步骤 1：准备场景——按状态筛选、按时间倒序分页
-- 页大小 20；排序键 created_at DESC, id DESC
CREATE INDEX idx_orders_status_created_id ON orders (status, created_at DESC, id DESC);

-- 步骤 2：第一页（OFFSET 方式，适合页数少的后台）
SELECT id, order_no, customer, amount, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

  id   | order_no | customer | amount  | status |      created_at
-------+----------+----------+---------+--------+-----------------------
 99997 | SO-099997| user_97  | 3421.50 | paid   | 2026-03-11 10:39:00+08
 99993 | SO-099993| user_93  |  890.00 | paid   | 2026-03-11 10:38:00+08
  ...
(20 rows)

-- 步骤 3：深分页对比 —— OFFSET 10000（慢）
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

                                          QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=345.60..347.00 rows=20 width=28) (actual time=6.812..6.828 rows=20 loops=1)
   Buffers: shared hit=418
   ->  Index Scan using idx_orders_status_created_id on orders
         (cost=0.42..3455.42 rows=50000 width=28) (actual time=0.024..5.731 rows=10020 loops=1)
         Index Cond: (status = 'paid'::text)
         Buffers: shared hit=418
 Planning Time: 0.170 ms
 Execution Time: 6.876 ms
(7 rows)

-- 步骤 4：同样深度改用键集分页（快且恒定）
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount FROM orders
WHERE status = 'paid'
  AND (created_at, id) < (TIMESTAMPTZ '2026-03-08 00:00:00+08', 40000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

                                          QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..6.02 rows=20 width=28) (actual time=0.022..0.038 rows=20 loops=1)
   Buffers: shared hit=25
   ->  Index Scan using idx_orders_status_created_id on orders
         (cost=0.42..1709.02 rows=6625 width=28) (actual time=0.020..0.036 rows=20 loops=1)
         Index Cond: ((status = 'paid'::text) AND
                      (ROW(created_at, id) < ROW('2026-03-08 00:00:00+08'::timestamptz, 40000)))
         Buffers: shared hit=25
 Planning Time: 0.155 ms
 Execution Time: 0.061 ms
(7 rows)

rows=20 与 rows=10020 的对比清晰展示了两种方案的差别。

-- 步骤 5：带总数的分页响应（估算总数，避免全表 count）
SELECT
  (SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders') AS estimated_total,
  count(*) FILTER (WHERE status = 'paid')                           AS paid_total
FROM orders;

 estimated_total | paid_total
-----------------+------------
          100000 |      25000
(1 row)

-- 步骤 6：N+1 探测判断是否有下一页（无限滚动首选）
-- 前端传 cursor = (created_at, id)；不传则从第一页开始
SELECT id, order_no, created_at
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < (TIMESTAMPTZ '2026-03-08 00:00:00+08', 40000)
ORDER BY created_at DESC, id DESC
LIMIT 21;      -- 多取一行用于判断

-- 应用层处理逻辑
-- rows.length > 20 → 有下一页，返回前 20 条，cursor 取第 20 条的 (created_at, id)
-- rows.length <= 20 → 已到末页

-- 步骤 7：导出场景——用 DECLARE CURSOR 流式读取，避免一次性载入内存
BEGIN;
DECLARE order_cur NO SCROLL CURSOR FOR
  SELECT id, order_no, amount FROM orders ORDER BY id;

-- 每次取 1000 行，循环直到取不到数据
FETCH FORWARD 1000 FROM order_cur;
FETCH FORWARD 1000 FROM order_cur;

CLOSE order_cur;
COMMIT;

-- 步骤 8：验证分页结果无重复无遗漏（对账查询）
-- 分别取第 1、2 页的 id 集合，检查交集为空
WITH page1 AS (
  SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 0
),
page2 AS (
  SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 20
)
SELECT
  (SELECT count(*) FROM page1)                          AS p1_cnt,
  (SELECT count(*) FROM page2)                          AS p2_cnt,
  (SELECT count(*) FROM page1 JOIN page2 USING (id))    AS overlap;

 p1_cnt | p2_cnt | overlap
--------+--------+---------
     20 |     20 |       0
(1 row)

注意事项

• LIMIT 不带 ORDER BY 时结果不确定。分页查询必须同时写 ORDER BY，且排序键必须唯一（或用唯一键兜底）。

-- 错误！
SELECT id FROM orders LIMIT 20 OFFSET 20;

-- 正确
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 20;

• OFFSET 代价随偏移量线性增长。OFFSET 100000 需要扫描并丢弃 10 万行，即使走了索引也一样慢。深分页改用键集分页。

-- 慢：OFFSET 深分页
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- 快：键集分页
SELECT id FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

• 排序键不唯一会导致分页重复或遗漏。这类 bug 在数据量小时难以复现，上线后才暴露。

-- 不稳定：status 有大量重复
SELECT id, status FROM orders ORDER BY status LIMIT 20 OFFSET 20;

-- 稳定：追加唯一键
SELECT id, status FROM orders ORDER BY status, id LIMIT 20 OFFSET 20;

• ctid 不适合作为对外游标。行更新后 ctid 会变化，VACUUM FULL 会重排物理位置。

-- 不推荐：把 ctid 暴露给前端做游标
SELECT ctid, id FROM orders ORDER BY ctid LIMIT 20;

• LIMIT 与 OFFSET 的顺序是固定的。LIMIT 在前、OFFSET 在后；标准语法则相反，OFFSET 在前、FETCH 在后。

-- 错误！语法顺序颠倒
SELECT id FROM orders ORDER BY id OFFSET 10 LIMIT 5;

-- 正确（LIMIT 风格）
SELECT id FROM orders ORDER BY id LIMIT 5 OFFSET 10;

-- 正确（标准风格）
SELECT id FROM orders ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

• 精确 count(*) 在宽条件或大表上代价高。它需要扫描所有匹配行。分页界面可改用估算值或 N+1 探测。

-- 昂贵：大表上带复杂条件的精确计数
SELECT count(*) FROM orders WHERE amount > 100 AND status IN ('paid', 'shipped');

-- 便宜：只判断是否存在下一页
SELECT id FROM orders WHERE amount > 100 ORDER BY id LIMIT 21;

• OFFSET 分页在数据变动时结果不稳定。翻页过程中若有新数据插入到前面，会导致重复行出现；有删除则可能遗漏。键集分页同样受影响较小但仍需注意有序键不可变。

-- 场景：用户翻页时新人下单，created_at 更大（排在最前）
-- 第 2 页用 OFFSET 20 读到的实际是"原第 19 行"，导致第 1 页末行重复出现

• LIMIT 不是"保证返回 n 行"。匹配行数不足时返回更少行，应用层必须容忍。

-- 表中只有 5 行时，以下返回 5 行而非 100 行
SELECT id FROM orders LIMIT 100;

• FETCH FIRST ... ROWS ONLY 与 LIMIT 在 OFFSET 位置上有区别。混用风格时容易写错顺序。

-- 混用风格：PostgreSQL 允许，但可读性差，不推荐
SELECT id FROM orders ORDER BY id OFFSET 10 LIMIT 5;

最佳实践

1. 分页排序键 = 业务排序键 + 唯一键。推荐 created_at DESC, id DESC，并建立对应复合索引。

-- 索引列顺序与方向都要与 ORDER BY 一致
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

2. 默认使用键集分页。前台列表、信息流、导出等场景一律用游标，只有后台管理的小页码场景才用 OFFSET。

-- 键集分页模板
SELECT id, order_no, created_at
FROM orders
WHERE (:last_created_at IS NULL OR (created_at, id) < (:last_created_at, :last_id))
ORDER BY created_at DESC, id DESC
LIMIT :page_size + 1;     -- 多取一行用于判断下一页

3. 无限滚动用 N+1 探测判断下一页，避免精确 count(*)。

-- 期望 20 条 → LIMIT 21
SELECT id, order_no FROM orders
WHERE (:cursor_created_at IS NULL OR (created_at, id) < (:cursor_created_at, :cursor_id))
ORDER BY created_at DESC, id DESC
LIMIT 21;

4. 必须展示总数时用估算值，并明确标注"约"。

-- 从 pg_class 取估算行数（VACUUM/ANALYZE 后精度足够展示用途）
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- 带条件的估算：用 EXPLAIN (FORMAT JSON) 在应用层解析
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE status = 'paid';

5. 为分页查询建立"覆盖"条件的索引，让过滤与排序在同一个索引内完成。

-- 支撑 WHERE status = ? ORDER BY created_at DESC, id DESC
CREATE INDEX idx_orders_status_created_id ON orders (status, created_at DESC, id DESC);

6. 导出大数据用服务端游标（DECLARE CURSOR），避免一次性把结果集载入内存。

BEGIN;
DECLARE c NO SCROLL CURSOR FOR SELECT id, order_no FROM orders ORDER BY id;
FETCH FORWARD 1000 FROM c;    -- 循环调用
CLOSE c;
COMMIT;

7. 分页参数做上限校验。LIMIT 必须设最大页大小（如 200），防止客户端传 LIMIT 1000000 拖垮数据库。

-- 应用层约束示例（伪代码）
-- page_size = min(requested_size, 200)
SELECT id FROM orders ORDER BY id LIMIT :page_size;

8. 用 EXPLAIN (ANALYZE, BUFFERS) 验证分页计划。关注实际读取行数（rows）是否远大于 LIMIT 值，若是则说明发生了扫描丢弃。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- 若 rows 表示约 10020，说明丢弃了 1 万行，应改用键集分页

下一节

继续学习：去重（DISTINCT）

参考链接

• LIMIT and OFFSET
• SELECT
• Row and Array Comparisons
• Cursor Operations