分页、动态筛选与 N+1 查询
分页限制一次返回的数据量;筛选把请求条件转换为受控查询;N+1 指先执行一次父查询,再为 N 个结果逐个查询关联数据,使数据库往返次数随结果数增长。三者必须一起设计,因为排序、筛选和加载关联共同决定结果正确性与性能。
复合行比较、数组参数、显式类型转换、JSON 聚合和覆盖索引示例采用 PostgreSQL 18.4 语法。
分页必须先有全序
没有 ORDER BY 时,SQL 结果没有承诺顺序。只按非唯一列排序也不构成稳定全序:多行拥有同一 created_at 时,数据库可以用任意相对顺序返回。
ORDER BY created_at DESC, id DESC唯一 id 是 tie-breaker,使任意两行都能比较先后。排序方向、NULLS FIRST/LAST、表达式和排序规则都属于分页协议,必须在下一页保持一致。
OFFSET/LIMIT 分页
SELECT id, created_at, total
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT $2 OFFSET $3;优点
- 接口容易表达页码,可直接跳到第 N 页。
- 在小结果集、后台管理和变化不频繁的数据上足够实用。
- 可配合单独
count(*)展示总页数。
成本与漂移
数据库仍需定位并丢弃 offset 之前的行。即使索引支持顺序,深页也要经过大量索引项;若还需排序,成本更高。
并发写入会让页边界漂移。第一次请求后若头部插入一行,第二页的 OFFSET 20 可能再次包含第一页末行;若头部删除一行,原第二页首行可能被跳过。单条语句内部有一致快照,但多次 HTTP 请求通常不共享同一数据库快照。
LIMIT/OFFSET 每页都必须使用同一确定性排序。不同 LIMIT 值可能产生不同计划和无序输出,不能依赖当前观察到的物理行顺序。
Keyset/Cursor 分页
Keyset 分页不计算“跳过多少行”,而是表达“从上一页最后一个排序键之后继续”。对于降序 (created_at, id):
SELECT id, created_at, total
FROM orders
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;PostgreSQL 行构造器按从左到右的词典序比较:先比较 created_at;相等时比较 id。游标必须包含两个值,漏掉 id 会在相同时间戳处漏行或重复。
升序则使用 >;上一页需要反转比较和数据库查询排序,再在服务端恢复显示方向。不要只改一个方向。
游标内容
游标通常编码为不透明字符串,内部至少包含:
{
"v": 1,
"createdAt": "2026-07-17T08:30:00.123456Z",
"id": 9021,
"sort": "created_at_desc",
"filterHash": "sha256:..."
}v支持协议演进。- 时间保留数据库所需精度,不能经毫秒格式化后丢失微秒。
sort防止把一种排序的游标用于另一种排序。filterHash绑定筛选条件,防止中途更换范围造成含义不一致。- 编码不是防篡改;需要信任游标内容时用服务器密钥签名或使用服务端游标存储,并限制长度和版本。
Keyset 的边界
- 不能高效任意跳到第 1000 页。
- 若排序键会修改,一行可能移动到游标前后,仍可能漏读或重复。适合使用不可变
created_at和唯一 ID。 - 新插入到第一页之前的数据不会出现在沿旧游标向后的遍历中,这通常符合时间线语义。
- 要获得固定快照,需要增加不可变上界,例如首次请求记录
snapshot_max_id或业务时间截止点,并让后续页都带上界。 - 可空排序键需要显式
NULLS规则和游标编码;初学实现宜优先选择非空键。
索引要匹配筛选与排序
常用查询:
WHERE tenant_id = $1
AND status = $2
AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT $5对应索引候选:
CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC, id DESC)
INCLUDE (total);前两列为等值范围,后两列支持游标边界和排序。INCLUDE 是否值得取决于返回列、表更新频率和 visibility map;它不保证一定出现 index-only scan。
若 status 是可选筛选,单个复合索引未必同时最佳支持“有 status”和“无 status”。应从真实查询矩阵、频率和 EXPLAIN 决定是增加 (tenant_id, created_at, id)、依赖 skip scan/bitmap 组合,还是限制接口组合。
动态筛选的安全构建
值使用参数,查询结构使用白名单。请求:
{
"statuses": ["paid", "refunded"],
"createdFrom": "2026-07-01T00:00:00Z",
"createdTo": "2026-08-01T00:00:00Z",
"minTotal": "100.00",
"sort": "newest",
"pageSize": 50
}服务端步骤:
- 验证枚举、时间、金额精度和页大小上限。
- 从可信身份得到
tenant_id,不取请求体租户值。 - 只为存在的筛选添加谓词和参数。
- 把
sort映射到固定 SQL 片段。 - 解码并验证游标版本、签名和筛选绑定。
生成的 SQL 可以是:
SELECT id, status, created_at, total
FROM orders
WHERE tenant_id = $1
AND status = ANY($2::text[])
AND created_at >= $3::timestamptz
AND created_at < $4::timestamptz
AND total >= $5::numeric
AND (created_at, id) < ($6::timestamptz, $7::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $8;时间范围常用半开区间 [from, to),相邻区间不重叠且无需制造“当天最后一微秒”。金额不要用浮点解析后再拼字符串。
可选条件的两种写法
固定 SQL 有时写成:
WHERE ($2::text IS NULL OR status = $2)它减少 SQL 形态,但 OR、通用计划和参数分布可能影响索引选择。动态加入 status = $2 会产生多个受控 SQL 形态,却更容易让规划器看到直接谓词。没有一种写法总是更快,使用代表性参数比较计划。
不可直接参数化的结构
下面是错误意图:
ORDER BY $1 $2普通参数代表值,不能代表列标识符或 ASC/DESC 关键字。应做白名单映射:
newest -> ORDER BY created_at DESC, id DESC
oldest -> ORDER BY created_at ASC, id ASC
total_high -> ORDER BY total DESC, id DESC每种排序都需要对应游标字段和索引评估。不要把客户端字符串直接插入 SQL。
N+1 查询怎样发生
父查询返回 50 张订单:
SELECT id, customer_id, created_at
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;随后代码循环 50 次:
SELECT product_name, quantity, unit_price
FROM order_items
WHERE order_id = $1;总查询数为 1 + N = 51。即使每条查询只耗 2ms,串行网络往返、连接占用、解析/执行开销和不同快照都被放大。开发环境数据少、数据库在本机时往往不明显。
解决 N+1 的三种方式
JOIN 一次读取
SELECT
o.id,
o.created_at,
i.product_name,
i.quantity,
i.unit_price
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.tenant_id = $1
AND o.id = ANY($2::bigint[])
ORDER BY o.created_at DESC, o.id DESC, i.product_name;优点是一次往返;缺点是父列在每条明细重复,多个一对多关联同时连接会产生笛卡尔乘积。必须在分页父 ID 之后再连接,否则 LIMIT 50 限制的是明细行而不是订单。
两次批量查询
先取一页订单,再用 ANY 批量取所有明细:
SELECT order_id, product_name, quantity, unit_price
FROM order_items
WHERE order_id = ANY($1::bigint[])
ORDER BY order_id, product_name;应用以 order_id 分组,固定为两次查询。它避免父行重复,适合 ORM DataLoader 和多个一对多集合。需要处理空 ID 列表,并限制批量大小。
数据库聚合子对象
PostgreSQL 可用 jsonb_agg 把子对象聚合为每个父行一个 JSON 数组:
SELECT
o.id,
o.created_at,
coalesce(
jsonb_agg(
jsonb_build_object(
'productName', i.product_name,
'quantity', i.quantity,
'unitPrice', i.unit_price
) ORDER BY i.product_name
) FILTER (WHERE i.order_id IS NOT NULL),
'[]'::jsonb
) AS items
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.tenant_id = $1
AND o.id = ANY($2::bigint[])
GROUP BY o.id, o.created_at
ORDER BY o.created_at DESC, o.id DESC;这减少应用组装,但把 JSON 构建 CPU 和响应体内存放到数据库。大型子集合仍需独立分页,不能无限聚合。
完整案例:订单检索接口
输入与数据
请求租户 42 的已支付订单,每页 2 条,按新到旧:
orders:
id=105, created_at=10:05, status=paid
id=104, created_at=10:04, status=paid
id=103, created_at=10:03, status=draft
id=102, created_at=10:02, status=paid
id=101, created_at=10:01, status=paid
order_items:
order_id=105, product_name=Keyboard, quantity=1
order_id=104, no rows第一步:请求第一页时多取一条
SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 3;返回 105, 104, 102。接口只输出前两条,把第三条作为 hasNextPage = true 的证据;下一游标编码输出页最后一条 104, 10:04,不是多取的 102。
第二步:批量加载两张订单的明细
SELECT order_id, product_name, quantity, unit_price
FROM order_items
WHERE order_id = ANY(ARRAY[105, 104]::bigint[])
ORDER BY order_id, product_name;应用按订单 ID 组装,整个页面固定两次数据库查询。
第三步:请求下一页
SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
AND (created_at, id) < ('2026-07-17 10:04:00+00', 104)
ORDER BY created_at DESC, id DESC
LIMIT 3;返回 102, 101,数量不超过页大小,因此 hasNextPage = false。
输出
{
"items": [
{"id": 105, "items": [{"productName": "Keyboard", "quantity": 1}]},
{"id": 104, "items": []}
],
"pageInfo": {
"hasNextPage": true,
"endCursor": "eyJ2IjoxLCJjcmVhdGVkQXQiOiIyMDI2LTA3LTE3VDEwOjA0OjAwWiIsImlkIjoxMDQsInNvcnQiOiJjcmVhdGVkX2F0X2Rlc2MiLCJmaWx0ZXJIYXNoIjoic2hhMjU2OmQ1MTQxMjEzYjVjOGZkMGZkY2ZmMmQyOTUxZDM2MGVlMTFkNzRlZTgwMzlhYTQ0NDFjYzJjMTkxNzgxMWEyMjMifQ.kLt5W6NJnPzQ6flXmW7AUa_HWVAcITeukSVdbG-BZk8"
}
}游标是无空格 JSON 的 base64url 编码与 HMAC-SHA-256 签名,示例密钥为 demo-only-key;生产密钥必须由密钥管理系统生成和轮换,不能写入代码仓库。游标解码后最后位置是 createdAt=2026-07-17T10:04:00Z, id=104,筛选摘要绑定租户 42 与 paid 状态。实际金额和其他字段由查询结果填充;示例只展示分页和关联结构。
验证
- 连续遍历所有页,收集 ID,确认无重复且等于同一筛选下的基准 ID 集合。
- 插入一条比
105更新的数据后继续旧游标,确认旧遍历不会重复现有页。 - 为一页 1、2、50 条分别断言查询数固定为 2,而不是
N+1。 - 解码游标时改变筛选或排序,确认服务拒绝。
- 用
EXPLAIN (ANALYZE, BUFFERS)检查 keyset 条件读取行数不会随页深度线性增加。
失败分支
- 只把
created_at放进游标:两条相同时间的订单可能被跳过。修复为复合键。 - 先 JOIN 明细再
LIMIT 2:一张有两条明细的订单占满结果,页面不是两张订单。修复为先分页订单 ID。 - 接受
sort=created_at desc; drop table...并拼接:产生 SQL 注入。修复为固定白名单映射。 - ORM 序列化时懒加载
items:测试断言查询数立刻发现从 2 增至 51。
计数和缓存边界
精确 count(*) 可能扫描大量可见行和索引项,不能因为 UI 有“总页数”就无条件每页计算。可选方案:
- 只返回
hasNextPage,不返回总数。 - 用户明确请求时计算精确总数。
- 使用有更新时间的预计算计数,并标注它是近似或延迟值。
- 对后台小范围筛选继续使用精确计数。
不能把 PostgreSQL pg_class.reltuples 的估算值包装成精确业务总数。
调试与测试清单
ORDER BY是否构成全序。- 游标是否包含全部排序键、方向、空值规则和筛选绑定。
- 首尾页、空页、相同排序值、删除和插入是否有测试。
- page size 是否有服务端最大值。
- 动态结构是否白名单,值是否参数化。
- 关联加载查询数是否由测试或 tracing 记录。
- 深 offset 与 keyset 是否用真实分布比较计划、延迟和缓冲区。
- 响应体和子集合是否有独立上限。
练习:工单列表
实现按 priority DESC, created_at DESC, id DESC 排序的租户工单列表,支持状态数组、负责人、半开时间范围,并批量加载标签。
完成标准:
- cursor 包含三个排序键、协议版本和筛选摘要。
- SQL 使用正确的复合行比较;若 priority 可空,定义空值顺序和游标处理。
- 给出支持主查询的候选索引并说明可选筛选的取舍。
- 标签加载总查询数固定,不随工单数增长。
- 用包含相同 priority 和 created_at 的数据验证无重复、无遗漏。
- 注入篡改游标、超大 page size 和非法排序时得到受控错误。
来源
- PostgreSQL 18:LIMIT and OFFSET(访问日期:2026-07-17)
- PostgreSQL 18:Row and Array Comparisons(访问日期:2026-07-17)
- PostgreSQL 18:Indexes and ORDER BY(访问日期:2026-07-17)
- PostgreSQL 18:Using EXPLAIN(访问日期:2026-07-17)