开发者日记

外观

实测对比：JSONB 数组 @> 比 unnest 快多少 ​

问题背景：JSONB 数组查询为什么会突然变慢 ​

在电商标签、用户权限、设备属性等场景中，很多团队会把少量可变列表放进 JSONB 数组，例如 {"tags": ["vip", "high_value"]}。这种模型灵活，但查询写法稍有不同，性能可能相差一个数量级。

最常见的两类写法是：

  • 使用 JSONB 包含运算符 @>：判断数组中是否包含某个候选数组；
  • 使用 unnest 或 jsonb_array_elements_text：先把数组展开成多行，再按元素过滤。

很多业务代码最初数据量小，两种 SQL 都能在几十毫秒内返回；当表达到百万、千万行后，未命中索引的 unnest 查询会退化为全表扫描，而正确配置 GIN 索引的 @> 查询仍可走索引。本文用一套可直接运行的 SQL，对比二者的执行计划、适用场景和常见坑。

方案对比：包含运算符与 unnest 的核心差异 ​

@> 表达的是“左侧 JSONB 是否包含右侧 JSONB”。当字段是数组时，tags @> '["vip"]'::jsonb 可以直接表达“包含 vip 标签”，并且能配合 JSONB GIN 索引使用。

unnest 是集合展开思路：把每个数组元素变成一行，再与条件比较。它更灵活，适合需要对元素做复杂计算、聚合或多表关联的场景，但谓词通常发生在展开之后，优化器很难把它转换成普通的 JSONB 包含索引扫描。

JSONB 数组tags 字段@> 包含判断保留 JSONB 结构unnest 展开生成元素行Bitmap/索引扫描可命中 GINFilter 后过滤常见全表扫描

简单选型原则是：只判断“是否包含”优先用 @>；需要对数组元素做去重、分组、连接、正则匹配或复杂聚合时，再使用展开函数。不要把 unnest 当成所有 JSONB 查询的默认写法。

实操步骤：构造百万级 JSONB 数组测试表 ​

下面示例使用 PostgreSQL 14 及以上版本即可运行。为了让结果稳定，先构造 100 万行数据，每行包含 3 到 8 个标签。

sql
-- 如已存在测试表，可先删除
DROP TABLE IF EXISTS user_profile;

-- 主表：id 与 JSONB 标签数组
CREATE TABLE user_profile (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb NOT NULL
);

-- 插入 100 万行；tags 中混入高频、中频、低频标签
INSERT INTO user_profile (data)
SELECT jsonb_build_object(
    'tags',
    (
        SELECT jsonb_agg('tag_' || g)
        FROM generate_series(1, 3 + (random() * 5)::int) AS g
    )
)
FROM generate_series(1, 1000000) AS s;

-- 给低频标签制造一些可检索数据
UPDATE user_profile
SET data = jsonb_set(data, '{tags}', data->'tags' || '"vip"')
WHERE id % 1000 = 0;

-- 建议分析表，保证统计信息最新
ANALYZE user_profile;

先执行无索引测试，观察两种写法的基础成本。

sql
-- 写法一：包含运算符，语义是 tags 数组包含 vip
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM user_profile
WHERE data->'tags' @> '["vip"]'::jsonb;

-- 写法二：展开为文本元素后过滤
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM user_profile,
     LATERAL jsonb_array_elements_text(data->'tags') AS tag
WHERE tag = 'vip';

在没有索引时，二者通常都要扫描整张表。区别在于 @> 是在 JSONB 内部执行 containment 判断，而展开写法需要为每个数组元素生成一条元组，CPU 开销更高。实际测试中，数组平均长度越大，unnest 的放大效应越明显。

索引验证：GIN 如何加速 @> 查询 ​

JSONB 的默认 GIN 操作符类支持顶层包含运算符，适合 data @> ... 这种写法。若查询条件是 data->'tags' @> ...，则应在表达式 (data->'tags') 上建立表达式索引。

sql
-- 为 data->'tags' 建立表达式 GIN 索引
CREATE INDEX idx_user_profile_tags_gin
ON user_profile
USING GIN ((data->'tags'));

ANALYZE user_profile;

-- 再次查看 @> 执行计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM user_profile
WHERE data->'tags' @> '["vip"]'::jsonb;

低频标签 vip 只匹配约千分之一数据时，执行计划通常会出现类似以下节点：

text
Bitmap Heap Scan on user_profile
  Recheck Cond: ((data -> 'tags'::text) @> '["vip"]'::jsonb)
  -> Bitmap Index Scan on idx_user_profile_tags_gin
        Index Cond: ((data -> 'tags'::text) @> '["vip"]'::jsonb)

这说明查询先通过 GIN 索引得到候选条目，再回表取得 id。在相同环境中，未索引展开查询可能需要数秒；命中 GIN 的低频包含查询通常可降到几十毫秒甚至更低，具体收益取决于选择率、内存、磁盘和回表数量。

也可以直接在整个 JSONB 列上建索引：

sql
-- 另一种常见写法：对整个 JSONB 文档建 GIN 索引
CREATE INDEX idx_user_profile_data_gin
ON user_profile
USING GIN (data);

-- 该查询能使用整列索引：整个 data 文档必须包含指定结构
EXPLAIN
SELECT id
FROM user_profile
WHERE data @> '{"tags": ["vip"]}'::jsonb;

注意 data @> '{"tags":["vip"]}' 与 data->'tags' @> '["vip"]' 语义接近，但索引位置不同。生产中应保持“查询表达式”和“索引表达式”一致，否则容易出现以为建了索引、实际却没命中的情况。

unnest 适合什么：复杂元素处理而不是简单包含 ​

当需求变成“统计每个标签出现多少次”或“找出同时具备多个标签的用户并去重”，展开函数会更自然。

sql
-- 统计标签出现次数，适合用展开函数
SELECT tag, count(*) AS cnt
FROM user_profile,
     LATERAL jsonb_array_elements_text(data->'tags') AS tag
GROUP BY tag
ORDER BY cnt DESC
LIMIT 20;

-- 查找同时包含 vip 和 tag_1 的用户，仍建议用 @> 表达包含条件
SELECT id
FROM user_profile
WHERE data->'tags' @> '["vip", "tag_1"]'::jsonb;

这里要特别说明：'["vip", "tag_1"]' 表示数组中同时包含这两个元素，不要求它们连续，也不要求数组完全相等。若业务语义是“任一标签命中”，可以使用多个 OR 条件，或根据查询模式设计额外的反向关联表。

如果数组元素需要频繁作为独立字段参与连接、排序、唯一约束，关系模型往往比 JSONB 更合适：

sql
-- 高频复杂查询可考虑规范化子表
CREATE TABLE user_profile_tag (
    user_id bigint NOT NULL REFERENCES user_profile(id),
    tag text NOT NULL,
    PRIMARY KEY (user_id, tag)
);

CREATE INDEX idx_user_tag_tag
ON user_profile_tag(tag);

实测结论：不要只看 SQL 长短，要看执行计划 ​

在本次百万行测试模型下，可以得到三个稳定结论：

  1. 无索引时：@> 与 unnest 都难以避免大表扫描，但 unnest 有明显的元组展开成本；
  2. 有匹配 GIN 索引且条件选择性好时：@> 可通过 Bitmap Index Scan 大幅减少扫描量；
  3. 条件命中大量数据时：即使能使用 GIN，优化器也可能选择顺序扫描，因为大量回表的随机 IO 可能比顺序扫描更贵。

因此，性能调优不能只背“@> 一定快”。应使用 EXPLAIN (ANALYZE, BUFFERS) 检查索引是否命中、实际行数与估算行数是否接近，以及 shared hit/read 是否异常。

避坑总结：JSONB 数组查询的高频错误 ​

1. 建了 GIN 索引却没有命中 ​

错误现象：已经执行 CREATE INDEX ... USING GIN(data)，但 WHERE data->'tags' @> '["vip"]' 仍是 Seq Scan。

修正方法：整列索引与表达式索引不是一回事。要么把查询改成 data @> '{"tags":["vip"]}'，要么创建 USING GIN ((data->'tags'))，并在测试前执行 ANALYZE。

2. 把 JSONB 数组当普通文本模糊匹配 ​

错误现象：使用 data->'tags'::text LIKE '%vip%'，虽然结果看似正确，但无法利用 JSONB GIN 索引，还可能误匹配 not_vip 等标签。

修正方法：简单包含判断使用 @>；需要文本语义时先用 jsonb_array_elements_text 展开并精确比较，复杂高频查询再考虑子表。

3. unnest 后产生重复行却未去重 ​

错误现象：一个用户有多个标签，关联订单或日志表后使用 jsonb_array_elements_text，结果总数被放大。

修正方法：若只需要筛选用户，用 @> 或 EXISTS；若必须展开，先在子查询中定位 id，再与明细表关联，避免数组基数放大。

4. 高频标签仍期待索引带来极低延迟 ​

错误现象：查询 tag_1 这类出现在大量行中的标签，执行计划走 Seq Scan，开发人员误以为索引失效。

修正方法：观察选择率。命中比例很高时，顺序扫描可能是优化器的合理选择；真正需要高性能的高频过滤条件，应提升为独立列、枚举字段或规范化标签表。

5. 忽略 JSONB 类型导致隐式转换 ​

错误现象：写成 data->'tags' @> 'vip'，报错右侧不是数组，或语义与预期不一致。

修正方法：JSONB 数组包含查询的右侧也应是 JSONB 数组，例如 '["vip"]'::jsonb；数字标签则写成 '[123]'::jsonb，不要依赖隐式类型转换。

最终建议很明确：JSONB 适合存储低频变化、模式灵活的半结构化数据；对于“是否包含某元素”的查询，优先使用 @> 并建立匹配的 GIN 表达式索引；只有在需要逐元素计算时再使用 unnest。把语义、索引表达式和执行计划三者对齐，才能真正拿到从数秒到几十毫秒的性能收益。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。