PostgreSQL / 系统课时

治理排序与分页查询

治理排序与分页查询

JSONB 字段参与排序时,性能瓶颈通常不在取值语法,而在排序表达式缺少可复用的有序访问路径。本课用 B-tree 表达式索引支撑数值排序,再用唯一键与 Keyset Pagination 保证分页顺序稳定,并避免深分页持续扫描和丢弃大量前置记录。

学习目标

  • 为 JSONB 路径上的可排序值建立与查询表达式一致的 B-tree 索引
  • 使用唯一键打破并列值,得到确定且可继续定位的分页顺序
  • 用 Keyset Pagination 降低高页码 OFFSET 带来的扫描成本

让排序表达式具备有序访问路径

假设商品动态属性保存在 attributes 中,其中 score 统一使用 JSON number。GIN 索引适合包含、键存在等 JSONB 条件,但不能替代按业务数值顺序读取记录。若查询长期按该路径排序,应直接为实际排序表达式建立 B-tree 表达式索引。

下面是片段示例,依赖已经存在的 product_catalog(id bigint primary key, attributes jsonb) 表。部分索引只纳入存在 score 且类型为 JSON number 的记录,因此查询必须保留与索引谓词一致、可被优化器证明满足的条件。

sql
CREATE INDEX IF NOT EXISTS idx_product_catalog_score_id
ON product_catalog (
    ((attributes ->> 'score')::numeric) DESC,
    id DESC
)
WHERE attributes ? 'score'
  AND jsonb_typeof(attributes -> 'score') = 'number';

-- 第一页:score 与唯一主键共同形成确定顺序
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, attributes ->> 'score' AS score
FROM product_catalog
WHERE attributes ? 'score'
  AND jsonb_typeof(attributes -> 'score') = 'number'
ORDER BY ((attributes ->> 'score')::numeric) DESC, id DESC
LIMIT 20;

-- 下一页:以上一页末行的两个排序键作为游标
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, attributes ->> 'score' AS score
FROM product_catalog
WHERE attributes ? 'score'
  AND jsonb_typeof(attributes -> 'score') = 'number'
  AND (((attributes ->> 'score')::numeric), id) < (87.5, 42000)
ORDER BY ((attributes ->> 'score')::numeric) DESC, id DESC
LIMIT 20;

预期结果

text
CREATE INDEX
Limit (actual time=<TIME> rows=<VALUE> loops=1)
  ->  Index Scan using idx_product_catalog_score_id on product_catalog
        (actual time=<TIME> rows=<VALUE> loops=1)
...
Limit (actual time=<TIME> rows=<VALUE> loops=1)
  ->  Index Scan using idx_product_catalog_score_id on product_catalog
        (actual time=<TIME> rows=<VALUE> loops=1)
...

具体执行计划会受数据量、统计信息和缓存状态影响,小表上优化器仍可能选择顺序扫描。验证重点不是机械要求出现某一种计划,而是确认数据规模足够时能够沿 idx_product_catalog_score_id 按目标顺序读取,并避免为满足 ORDER BY 再对大量候选记录执行独立 Sort。

用唯一键固定分页顺序

只按 score 排序并不确定,因为多条商品可能具有相同评分。加入唯一主键 id 后,顺序固定为先比较 score,评分相同时再比较 id,因此游标也必须同时保存这两个值。

这仍不能消除 OFFSET 的深分页成本。即使索引已经按目标顺序组织,执行 OFFSET 20000 LIMIT 20 时,PostgreSQL 通常仍需访问并跳过前面的 20000 个候选项。表达式索引解决的是“如何按顺序读取”,不是“如何瞬间跳到第 N 页”。

用 Keyset Pagination 代替深分页

对于“下一页”或“继续加载”,更适合以上一页末行为边界。示例中的游标 (87.5, 42000) 表示上一页最后一条记录;由于两个排序键都使用 DESC,行值比较 < 会继续寻找排序位置更靠后的组合。

这种方式不需要先读取并丢弃固定数量的前置记录,分页成本主要与继续定位和下一页实际读取的数据量相关。接口应把 score 和 id 一起编码进游标,只保存 score 会在并列评分处造成边界不确定。

处理缺失值、类型漂移与并发变化

JSONB 的灵活性会直接影响排序索引。若 score 有时是数字、有时是不可转换的字符串,直接执行 ::numeric 可能失败。示例中的部分索引和查询都使用完整条件 jsonb_typeof(attributes -> 'score') = 'number',只让 JSON 数字进入排序范围,避免错误类型参与转换。

还应明确缺失值和 JSON null 是否参与结果。需要参与时,应显式设计 NULLS FIRST 或 NULLS LAST,并让索引与查询保持一致;根本不展示无评分商品时,部分索引通常更节省空间。

唯一 id 能保证同一组排序值具有确定次序,但如果翻页期间 score 发生更新,记录仍可能跨越游标边界。要求跨多页看到严格一致快照时,需要在事务快照或业务版本字段层面额外保证一致性。治理这类查询的核心,是让表达式索引负责有序访问,让稳定复合键和 Keyset Pagination 负责确定边界并控制深分页成本。

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

目录与阅读设置

记录一个想法

治理排序与分页查询

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

登录哪都通