PHP小码农

Appearance

分页方案（OFFSET vs Keyset） ​

分页（pagination）大概是所有业务系统里用得最频繁、却最容易被想当然的数据库访问模式。列表页、搜索结果、消息流、后台报表……几乎每个系统都要「把一大堆数据按页吐给前端」。PostgreSQL 提供的最直觉写法就是 LIMIT n OFFSET m：跳过前 m 行、取 n 行。在数据量小、页码靠前的场景里，它工作得很好；但当页码不断加深（OFFSET 1000000）时，这条查询会越来越慢，慢到把数据库 CPU 和 I/O 全部吃掉。

深分页只是 OFFSET 的一半问题，另一半是页漂移（page drift）：OFFSET 是「按位置」取数而不是「按内容」取数。在分页过程中如果有新行插入或旧行删除，用户翻到第 3 页时会看到第 2 页已经看过的行，或者漏掉一部分行——因为每一页都是重新排序后重新数位置的。对一个订单列表或消息流来说，这种体验缺陷往往比慢更难容忍。

本页系统讲两类主流方案：传统的 LIMIT/OFFSET（offset 分页）与基于「从上一页最后一行继续」的 keyset 分页（又称游标分页、cursor-based pagination），以及第三种武器——服务端游标 DECLARE ... CURSOR WITH HOLD。我们会分析它们的性能差异（用 EXPLAIN ANALYZE 验证）、一致性语义差异，并落到 API 设计与 PHP 实战上。

前置知识

建议先阅读：Rust 驱动 与 EXPLAIN ANALYZE。

基础概念 ​

先把本页涉及的术语与两种方案的定位摆清楚。

概念英文 / 记法说明
偏移分页offset paginationLIMIT n OFFSET m，按位置跳过 m 行取 n 行
键集分页keyset pagination以上一页最后一行的排序键为起点继续向后取
游标cursorkeyset 分页中承载「上次取到哪里」的定位值，与 SQL 游标不同
深分页deep pagination页码很大（OFFSET 很大）的分页请求
页漂移page drift并发写入时，OFFSET 分页各页之间出现重复行或漏行
服务端游标server-side cursorDECLARE ... CURSOR 在服务端保存查询状态、逐批取数
WITH HOLDholdable cursor事务提交后仍可继续取数的服务端游标
行值比较row value comparison(a, b) < (x, y) 的元组比较语法，keyset 分页的核心
排序稳定性sort stability排序键组合能唯一确定行序，避免同键行的顺序抖动
估算行数estimated row count从 pg_class.reltuples 等统计信息获得的近似总数

两种方案一句话对比 ​

  • OFFSET 分页：WHERE ... ORDER BY created_at DESC LIMIT 20 OFFSET 40。回答的问题是「排序后的第 41~60 行是谁」。实现简单、支持跳页，但深度越深越慢，且各页之间不保证不重不漏。
  • Keyset 分页：WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20。回答的问题是「比上一页最后一行更靠后的 20 行是谁」。只与定位值有关、与页码无关，任何一页的代价都一样，且天然不重不漏。

一句话总结：OFFSET 是「第几页」，keyset 是「从哪之后」。前者面向「浏览目录」的人，后者面向「追增量」的流。

为什么深分页天然是 O(N) ​

OFFSET 1000000 LIMIT 20 并不是「直接跳到第 1000001 行」。PostgreSQL 必须按照排序要求真正取出并处理前 1000020 行，把前 1000000 行丢掉，再返回 20 行。如果排序走索引，就是沿着 B-tree 叶子链扫过 100 万个条目；如果排序走排序节点，那就是把 100 万行排序（或在 work_mem 中缓冲）之后丢掉 100 万行。无论哪种路径，代价都随 OFFSET 线性增长——第 1 页 1ms，第 1000 页可能就要 1s。

而 keyset 分页的每一条查询都是从定位值开始的索引范围扫描，走 (created_at, id) 复合索引时，无论定位值在索引的哪个位置，都只读出需要的 n 行。代价恒定，这就是「深分页自由」。

页漂移是怎么发生的 ​

假设商品列表共 100 行，每页 10 行。用户看第 1 页期间，管理员插入了一条新商品（按 created_at DESC 排它会出现在最前面）。用户翻到「第 2 页」时，服务端执行 OFFSET 10 LIMIT 10：新插入的行占掉了第一行位置，原来第 2 页的第一行被挤到第 11 行，于是用户的「第 2 页」从原来的第 11~20 行变成了原来的第 10~19 行——他看到了一条已经在第 1 页看过的行，同时漏掉了一条本该看到的行。

删除同理：删掉一行后，后面的行整体前移，翻页时会漏掉一行。keyset 分页不依赖全局位置，定位值锚定的是具体某一行，因此插入到前面的新行不会影响后续页的内容（新行只会出现在第 1 页），已看过的行不会被重复看到。

语法与用法 ​

OFFSET 分页的基础语法 ​

sql
-- 最普通的分页：按创建时间倒序，取第 3 页（每页 20 条）
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

LIMIT / OFFSET 是 PostgreSQL 的方言写法（MySQL 也兼容）；SQL 标准写法是 FETCH FIRST n ROWS ONLY 与 OFFSET m ROWS，PostgreSQL 同样支持：

sql
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 40 ROWS
FETCH FIRST 20 ROWS ONLY;

两种写法等价。需要注意的是：没有 ORDER BY 的分页是没有意义的——PostgreSQL 不保证无序查询的返回顺序，两次执行同一查询的行序可能不同，分页会彻底乱掉。

Keyset 分页的基础语法 ​

以「每页 20 条、按 created_at DESC 排序」为例，第一页：

sql
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

记录最后一行的 (created_at, id)，下一页：

sql
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这里 WHERE (created_at, id) < (:last_created_at, :last_id) 是 PostgreSQL 的行值比较（row value comparison）语法：先比 created_at，相等时再比 id。它与手写的展开形式完全等价：

sql
-- 行值比较的展开等价形式（效果相同，但行值比较更简洁且优化器同样能使用复合索引）
WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)

在生产中请使用行值比较：它不易写错，且 PostgreSQL 优化器对两种形式都能规划索引扫描，但行值形式的执行计划往往更稳定、更易读。

服务端游标语法 ​

当需要「顺序翻阅大量数据」而不是「按页随机跳转」时，服务端游标是第三种选择：

sql
-- 在事务内声明游标
BEGIN;
DECLARE articles_cursor CURSOR FOR
    SELECT id, title, created_at FROM articles ORDER BY created_at DESC;

-- 分批取数
FETCH FORWARD 1000 FROM articles_cursor;

-- 事务提交后仍可继续取（需要 WITH HOLD）
COMMIT;

-- WITH HOLD 游标：声明时指定，提交后依旧可用
DECLARE articles_cursor CURSOR WITH HOLD FOR
    SELECT id, title, created_at FROM articles ORDER BY created_at DESC;

FETCH FORWARD 1000 FROM articles_cursor;
CLOSE articles_cursor;

WITH HOLD 游标在事务提交后仍存活，直到会话结束或显式 CLOSE。服务端游标的更多细节（包括存储过程内游标与异常处理）见游标与异常处理。

详细说明 ​

OFFSET 慢在哪：一个可以亲手验证的实验 ​

建一张 100 万行的表，分别对比浅分页与深分页：

sql
CREATE TABLE articles (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO articles (title, created_at)
SELECT
    'article ' || g,
    now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) AS g;

CREATE INDEX ON articles (created_at DESC, id DESC);

ANALYZE articles;

浅分页（第 1 页）与深分页（第 5 万页，OFFSET 1000000）的计划对比：

sql
EXPLAIN ANALYZE
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
text
Limit (cost=0.42..1.11 rows=20 width=27) (actual time=0.037..0.068 rows=20 loops=1)
  ->  Index Scan using articles_created_at_id_idx on articles
       (actual time=0.035..0.057 rows=20 loops=1)
Planning Time: 0.224 ms
Execution Time: 0.096 ms
sql
EXPLAIN ANALYZE
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1000000;
text
Limit (cost=35026.53..35027.22 rows=20 width=27) (actual time=185.241..185.260 rows=20 loops=1)
  ->  Index Scan using articles_created_at_id_idx on articles
       (actual time=0.028..168.657 rows=1000020 loops=1)
Planning Time: 0.175 ms
Execution Time: 185.290 ms

即使走了索引，OFFSET 深分页也必须真实扫过 1000020 个索引条目才能丢弃前 100 万行——这就是 O(N) 的直接证据。OFFSET 继续加大，时间继续线性变长；而同样的 keyset 查询无论从哪一行开始，都是 0.1ms 量级。

不要只看「走了索引」

EXPLAIN ANALYZE 里出现了 Index Scan 不代表查询快。OFFSET 深分页走索引恰恰是最迷惑人的场景：计划看起来「完美」，但 rows=1000020 的实际扫描量暴露了真相。判断深分页问题，要看实际处理的行数而不是索引名称。

行值比较与复合索引的配合 ​

keyset 查询要快，前提是排序键上有匹配的复合索引。规则是：索引列的顺序与方向必须和 ORDER BY 一致。

sql
-- 排序：ORDER BY created_at DESC, id DESC
CREATE INDEX ON articles (created_at DESC, id DESC);

有了这个索引后，keyset 查询的计划是纯索引范围扫描：

sql
EXPLAIN ANALYZE
SELECT id, title, created_at FROM articles
WHERE (created_at, id) < ('2026-09-01 00:00:00+00', 900000)
ORDER BY created_at DESC, id DESC
LIMIT 20;
text
Limit (cost=0.42..1.11 rows=20 width=27) (actual time=0.041..0.084 rows=20 loops=1)
  ->  Index Scan using articles_created_at_id_idx on articles
       Index Cond: (ROW(created_at, id) < ROW('2026-09-01 00:00:00+00', 900000))
       (actual time=0.039..0.071 rows=20 loops=1)
Planning Time: 0.198 ms
Execution Time: 0.118 ms

注意 Index Cond: (ROW(created_at, id) < ROW(...))：PostgreSQL 把行值比较直接转成了索引条件，这就是每页代价恒定的原因。关于 B-tree 索引与多列排序的匹配规则，见B-tree 索引概述。

复合游标与排序稳定性 ​

为什么 keyset 游标要带上 id？因为 created_at 不唯一。如果只用 WHERE created_at < :last_created_at，同一秒内产生的多行会被整批跳过或整批重复——上一页最后一行的 created_at 是 12:00:05，同秒还有 30 行没有展示，而 created_at < '12:00:05' 会把它们全部跳过。

解决办法是把一个唯一且单调的列（通常就是主键 id）追加为次级排序键：

sql
ORDER BY created_at DESC, id DESC

这样排序结果完全稳定：任何两行都能比出先后，游标 (created_at, id) 能精确定位到「上一页最后一行之后」。经验法则：

  1. 排序键 = 业务排序键（created_at、score 等）+ 唯一键（id）；
  2. 唯一键永远放在最后一级；
  3. 索引与排序一一对应，多一个常用排序方向就多一个索引。

ASC 与 DESC 混合排序时的游标写法 ​

当排序是 ORDER BY score DESC, created_at ASC 这种混合方向时，行值比较写起来要小心。PostgreSQL 的行值比较内部使用默认的「升序」比较语义，混合方向时直接写 (score, created_at) < (...) 语义会与 ORDER BY 不一致。此时要么改用展开形式并按方向逐列处理：

sql
-- ORDER BY score DESC, created_at ASC 的下一页条件
WHERE (score < :last_score)
   OR (score = :last_score AND created_at > :last_created_at)
   OR (score = :last_score AND created_at = :last_created_at AND id > :last_id)

要么把「倒序列」改造成「升序列」存一个可比较的反向值（例如对数值取负）。混合方向 keyset 容易出错，设计阶段就应尽量让列表排序方向一致，或把排序方向编码进 cursor token，由服务端按方向生成对应的 WHERE 条件。

游标编码与 API 设计 ​

对外 API 中，keyset 定位值不应该直接暴露原始值，而是编码成一个不透明字符串（opaque cursor token）：

text
# 一次 API 响应的结构（示意）
{
  "data": [ ... 20 items ... ],
  "next_cursor": "eyJjIjoiMjAyNi0wOS0wMVQwMDowMDowMCswMDowMCIsImlkIjo5MDAwMDB9",
  "has_more": true
}

常见做法是 base64(JSON)：

php
<?php
// 生成 cursor（下一页定位值 = 本页最后一行的排序键）
function encodeCursor(array $row): string
{
    return base64_encode(json_encode([
        'c' => $row['created_at'],   // created_at 原样字符串
        'id' => $row['id'],
    ]));
}

// 解析 cursor；非法 cursor 直接 400，不要默默当第一页处理
function decodeCursor(?string $cursor): ?array
{
    if ($cursor === null || $cursor === '') {
        return null;
    }
    $data = json_decode(base64_decode($cursor, true), true);
    if (!is_array($data) || !isset($data['c'], $data['id'])) {
        throw new InvalidArgumentException('invalid cursor');
    }
    return $data;
}

设计要点：

  • 不透明性：客户端只原样回传 next_cursor，不解析、不构造，服务端随时可以改编码格式或加签。
  • 防篡改：对安全敏感的接口可对 payload 加 HMAC 签名（JSON Web Token 风格），防止客户端伪造定位值翻越权限边界。
  • 失败要显式：无效 cursor 返回 4xx，而不是静默回第一页——否则客户端一次 bug 就会「从头翻一遍」。
  • 只能向后翻：keyset 天然是「下一页」语义；「上一页」需要额外携带本页第一行的定位值与方向标记，或干脆不支持（信息流场景通常不需要回翻）。

服务端游标：什么时候用它 ​

DECLARE ... CURSOR 把查询状态保存在服务端，客户端用 FETCH n 分批拉取。与 keyset 相比它有自己的位置：

维度keyset 分页服务端游标
状态保存无（状态在 cursor token 里）服务端为游标保留状态
占用连接可池化、无状态游标生命周期绑定连接
随机跳页不支持（只能顺序）不支持（只能顺序）
复杂查询需要唯一排序键任意查询（聚合、JOIN 均可）
中断恢复可恢复（重发 cursor 即可）连接断开即丢失
典型场景Web/API 分页批处理、大结果导出

WITH HOLD 让游标跨事务存活，但它是把游标结果物化到临时文件来实现的——大结果集会占用磁盘与内存。因此服务端游标更适合「后台任务顺序处理一批数据」，而不是在线 API 的分页。另外，在 PgBouncer transaction 模式下，游标跨事务使用与连接复用冲突，基本不可用；keyset 无状态分页才是池化环境的首选。

总数统计：COUNT(*) 的代价 ​

OFFSET 分页的列表页通常还要渲染「共 N 条，第 x/y 页」。这个 N 若用 SELECT count(*) 现算，代价与全表扫描同阶：

sql
-- 精确计数：条件复杂时要扫全表或扫大索引，千万级表上秒级起步
SELECT count(*) FROM articles WHERE status = 'published';

PostgreSQL 的统计信息提供了廉价的估算：

sql
-- 估算总行数（reltuples 来自 ANALYZE 的统计，可能有偏差但零扫描成本）
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE oid = 'articles'::regclass;
text
 estimated_rows
----------------
        1000000

两者的取舍：

方式精度成本适用
count(*)（带条件）精确与扫描量同阶小表、后台、必须精确的场景
pg_class.reltuples估算近似零大表、展示「约 N 条」
count(*) 缓存（Redis 等）稍旧低高频展示、容忍秒级误差
keyset + 无总数无零信息流（根本没有页码概念）

对于 keyset 分页的信息流 UI，业界通行做法是不显示总数与页码，只显示「加载更多」——总数这个数字本身在并发写入下就是瞬时值，为它付出 O(N) 成本并不划算。

LIMIT 与 OFFSET 的参数化陷阱 ​

LIMIT / OFFSET 的值可以参数化，但要注意某些驱动与连接池组合下，非 ASCII 排序或 LIMIT $1 的计划缓存问题。另外 LIMIT 0 是合法的（返回空集，可用于 schema 探测），OFFSET 为负会报错，LIMIT ALL 等价于不写 LIMIT。对来自用户的 page / page_size 参数必须做上限与下限校验：

php
<?php
$pageSize = min(max((int)($_GET['page_size'] ?? 20), 1), 100);  // 夹在 [1, 100]
$offset   = min(max((int)($_GET['page'] ?? 1) - 1, 0) * $pageSize, 1_000_000);

不给 page_size 设上限，一个 ?page_size=1000000 就能把数据库打穿——这是真实事故里最常见的分页漏洞之一。

与 ORM / 查询构建器的结合 ​

主流框架都已内建两种分页。以 Laravel 为例：

php
<?php
// OFFSET 分页（默认）：返回 LengthAwarePaginator，附总条数
$users = DB::table('users')->orderBy('id')->paginate(20);

// keyset 分页（Laravel 8+ 内建）：返回 CursorPaginator
$users = DB::table('users')
    ->orderBy('created_at')
    ->orderBy('id')
    ->cursorPaginate(20);

// cursorPaginate 生成的 SQL 形如：
// select * from users
//   where (created_at < ?) or (created_at = ? and id < ?)
//   order by created_at desc, id desc
//   limit 21   -- 多取 1 行用于判断 has_more

注意 Laravel 的 cursorPaginate 实际取 limit + 1 行来判断 has_more，返回给前端的是第 n 行。多取一行的技巧在自己的实现里同样适用，比 count 一次判断是否还有下一页便宜得多。

选型决策：三类场景对照 ​

场景推荐理由
信息流、消息列表、无限滚动keyset无跳页需求，性能恒定，不重不漏
后台管理列表（有页码、跳页、总数）OFFSET交互需要随机页码；数据量可控时成本可接受
大结果导出、批处理服务端游标（或 keyset 分批）顺序处理，避免一次性载入内存
手机端下拉刷新 + 上拉加载keyset与「从上次位置继续」的交互天然匹配
管理端审计/账单（必须精确总数）OFFSET + 缓存计数精确性优先，配合缓存压低成本

一个常见组合是：对外 API 全部 keyset，对内管理端保留 OFFSET 并限制最大 OFFSET。前者享受性能与一致性，后者保住「跳到第 N 页」的传统交互。

实战示例 ​

示例一：用 EXPLAIN ANALYZE 对比两种方案 ​

数据沿用前文 100 万行的 articles 表。

bash
# 1. OFFSET 深分页（第 5 万页）
psql -c "\timing on" -c "
SELECT count(*) FROM (
  SELECT id FROM articles
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 1000000
) t;"
text
 count
-------
    20
(1 row)

Time: 189.502 ms
bash
# 2. 等价的 keyset 查询（定位值取自 OFFSET 结果的最后一行）
psql -c "\timing on" -c "
SELECT id FROM articles
WHERE (created_at, id) < ('2025-11-19 16:26:40+00', 980001)
ORDER BY created_at DESC, id DESC
LIMIT 20;"
text
Time: 0.412 ms

近 500 倍的差距，而且这个差距随深度继续扩大。OFFSET 的时间随页码线性增长，keyset 的时间永远是一条平线。

示例二：PHP 实现 keyset 分页 API ​

php
<?php
// Pagination/cursor.php —— 完整的 keyset 分页封装（PDO + PostgreSQL）
class CursorPagination
{
    public function __construct(
        private PDO $pdo,
        private int $defaultPageSize = 20,
        private int $maxPageSize = 100,
    ) {
    }

    /**
     * @param string $cursor 上一页响应中的 next_cursor（base64），首页传 null
     * @return array{items: array, next_cursor: ?string, has_more: bool}
     */
    public function paginate(string $cursor = null): array
    {
        $pageSize = min(max($_GET['page_size'] ?? $this->defaultPageSize, 1), $this->maxPageSize);

        // 多取 1 行用于判断 has_more
        $sql = <<<SQL
            SELECT id, title, created_at
            FROM articles
            WHERE (:cursor_created_at IS NULL
                   OR (created_at, id) < (:cursor_created_at::timestamptz, :cursor_id::bigint))
            ORDER BY created_at DESC, id DESC
            LIMIT :limit
        SQL;

        $decoded = $cursor ? $this->decodeCursor($cursor) : null;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':cursor_created_at' => $decoded['c'] ?? null,
            ':cursor_id'         => $decoded['id'] ?? null,
            ':limit'             => $pageSize + 1,
        ]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $hasMore = count($rows) > $pageSize;
        if ($hasMore) {
            array_pop($rows);   // 丢弃多取的那一行
        }

        $last = end($rows) ?: null;

        return [
            'items'       => $rows,
            'has_more'    => $hasMore,
            'next_cursor' => $hasMore && $last ? $this->encodeCursor($last) : null,
        ];
    }

    private function encodeCursor(array $row): string
    {
        return rtrim(strtr(base64_encode(json_encode([
            'c'  => $row['created_at'],
            'id' => $row['id'],
        ])), '+/', '-_'), '=');   // URL 安全的 base64
    }

    private function decodeCursor(string $cursor): array
    {
        $json = base64_decode(strtr($cursor, '-_', '+/'), true);
        $data = is_string($json) ? json_decode($json, true) : null;

        if (!is_array($data) || !isset($data['c'], $data['id'])) {
            throw new InvalidArgumentException('invalid cursor', 400);
        }
        return $data;
    }
}

使用方式：

php
<?php
$pages = new CursorPagination($pdo);

$result = $pages->paginate($_GET['cursor'] ?? null);

header('Content-Type: application/json');
echo json_encode([
    'data'        => $result['items'],
    'has_more'    => $result['has_more'],
    'next_cursor' => $result['next_cursor'],
]);

要点回顾：游标只带 (created_at, id)；非法游标抛 400；每页多取一行判断 has_more；page_size 强制夹在 [1, 100]。

示例三：psql 演示 WITH HOLD 服务端游标 ​

bash
psql <<'SQL'
BEGIN;
DECLARE cur CURSOR WITH HOLD FOR
    SELECT id, title FROM articles ORDER BY created_at DESC, id DESC;

FETCH FORWARD 5 FROM cur;

-- 提交事务后游标仍然可用
COMMIT;

FETCH FORWARD 5 FROM cur;

CLOSE cur;
SQL
text
DECLARE CURSOR
 id |   title
----+-----------
  1 | article 1
  2 | article 2
  3 | article 3
  4 | article 4
  5 | article 5
(5 rows)

COMMIT
 id |    title
----+-------------
  6 | article 6
  7 | article 7
  8 | article 8
  9 | article 9
 10 | article 10
(5 rows)

CLOSE CURSOR

WITH HOLD 游标提交后照常 FETCH——这就是它与服务端状态绑定的体现。用完务必 CLOSE，否则游标与其物化结果会留到会话结束。

注意事项 ​

没有 ORDER BY 的分页随时会乱。 PostgreSQL 不保证无序查询的行序，跨页重复、漏行、顺序跳变都会发生。任何分页查询都必须显式 ORDER BY，且排序键组合必须能构成全序（末位补唯一键）。

keyset 分页无法跳页，先确认交互再选型。 产品如果坚持「跳到第 137 页」，就只能用 OFFSET 或「缓存前 N 页偏移」等折中方案。把选型标准提前讲清楚，能省掉大量返工。

created_at 一秒多行是常态，别用单列游标。 只按时间戳定位会整批跳过或重复同一时间戳的行。游标必须包含唯一键。

OFFSET 深分页走索引也慢，不要被执行计划迷惑。 看 EXPLAIN ANALYZE 的实际扫描行数，不要只看有没有 Index Scan。

page_size 必须设上限。 不设上限的 page_size 参数等于给攻击者一个 O(N) 拖库接口。上限之外返回 422 或静默截断到上限，任选其一，但要固定。

游标值来自用户输入，解析必须严格。 非法游标返回 4xx 而不是当第一页处理；带权限语义的数据建议对 cursor 加签，防止伪造定位值绕过过滤条件。

WITH HOLD 游标会物化结果到临时文件。 大结果集下它会占用磁盘，长期不 CLOSE 的游标会持续占资源。它也不兼容 PgBouncer transaction 模式——池化环境在线链路请用无状态的 keyset。

估算行数不是精确行数。 pg_class.reltuples 在多次写入后可能明显过时（偏差可达两位数百分比），展示「约 1.2 万条」可以，用于对账、计费不行。对账场景老老实实 count(*) 或维护计数器表。

多租户与软删除过滤要一并纳入游标条件。 keyset 的 WHERE 只有游标条件时，别把 tenant_id = ?、deleted_at IS NULL 弄丢——游标条件是 AND 关系，不是替代关系。

最佳实践 ​

  1. 对外信息流类 API 一律 keyset 分页。 代价恒定、不重不漏、天然支持「从上次位置继续」，是信息流、消息、日志类接口的标准答案。
  2. 管理后台允许 OFFSET，但限制最大偏移。 例如最多允许 OFFSET 10000，超出引导用户细化筛选条件。既保住跳页交互，又封住 O(N) 拖库入口。
  3. 排序键 = 业务键 + 唯一键，索引与排序一一对应。 ORDER BY created_at DESC, id DESC 必须有 (created_at DESC, id DESC) 索引支撑；每个常用的排序方向建一个索引。
  4. 游标用不透明 token 编码并校验。 base64(JSON) 起步，敏感场景加 HMAC 签名；解析失败返回 4xx，绝不静默回第一页。
  5. 判断 has_more 用多取一行，不要 count。 LIMIT n+1 的代价是 O(n)，count 的代价可能是 O(N)。
  6. 大表总数用 pg_class.reltuples 或缓存估算，UI 文案写「约 N 条」。 为一个瞬时数字付出全表扫描成本不值得；真正需要精确值的报表走离线统计。
  7. page_size 夹在固定区间并写进 API 文档。 [1, 100] 是常见的合理区间，配合网关层的全局限流。
  8. 导出与批处理用服务端游标或 keyset 分批，禁用「一次查全量」。 前者控制内存与连接占用，后者给客户端自由控制批次大小。
  9. 在 CI 里用 EXPLAIN ANALYZE 回归核心分页查询。 把第 1 页与「最深一页」的执行时间写进基准测试，深分页退化在上线前就能被发现。
  10. 把分页策略写进 API 设计规范。 「列表类接口默认 cursor，管理端允许 offset」成为团队约定后，接口风格一致，性能预期一致，新人也不会再纠结。

下一节

继续学习：软删除设计

参考链接 ​