PostgreSQL JSONB索引：高效查询文档数据

可视化演示

展示GIN倒排索引键值对到文档映射、表达式索引B-tree路径查询优化、索引膨胀与VACUUM清理过程。切换模式查看JSONB索引核心技术。

查询基础：读懂谜之盒子的语言

青云仓库的盒子里装的东西完全没规律：有的盒子贴满了标签，有的只有一张纸条。如果要求所有盒子都按统一货架摆放，几乎不可能。PostgreSQL 的 JSONB 数据类型就是为这种“没有规矩”的数据准备的——它把 JSON 文档压缩成二进制格式存进一个字段，就像给盒子装上了透明侧窗：写入时数据库会自动把键排序、去掉重复键、压缩空白，这样虽然写的时候多花一点点整理时间，但后续查询时内容稳定可预测，不会出现同一个 JSON 两种写法。

有了这种盒子，查询语言也要跟上。JSONB 最常用的有四个操作符：@> 表示“左文档包含右子文档”，? 问“顶层有没有这个键”，?| 问“这些键至少有一个吗”，?& 问“这些键是不是全都有”。比如老板问“哪些盒子带 red 标签”，SQL 就是 WHERE data @> '{"tags":["red"]}'——把 target 条件写成一个小 JSON 模板，让数据库去匹配整只盒子内部。

小明明白了：老板的问题花样百出，而 JSONB 提供了丰富的问题语法。但问题来了——没有目录的话，再好的问法也得翻遍所有盒子才能回答。于是小明决定做一本真正的“登记册”。

GIN索引原理：物品总登记册

小明想到一个妙招：与其每次开箱，不如把所有盒子里出现过的东西都抄进一本大册子。他看到 B1 盒有红色，就在“red“那行写下 B1；看到 B5 盒也有红色，再补上 B5。这本册子就是”物品总登记册“——数据库里的 GIN 索引（Generalized Inverted Index，通用倒排索引）。GIN 会把 JSONB 文档拆成一个个”词条“（键、值、键值对），为每个词条维护一张”出现在哪些行的清单”，这就是倒排思想。

GIN 天生适合 @>、?、?|、?& 这类“存在性/包含性”查询。建索引只需要一条命令：

查询时，数据库先查每个词条的倒排清单，再把多张清单做交集或并集，得到候选行号，最后回表把完整行读出来。整个过程不需要打开任何“不含条件的盒子”。

有了这本总登记册，老板问“哪些盒子有红标签”，小明翻一页就能念出 B1、B5、B8，再也不用开箱。但登记册只回答“有没有”，如果老板要“价格从低到高”，就得换一本按数字排好的流水账了。

CREATE INDEX idx_attrs_gin ON products USING GIN (attrs);

BTREE索引：价格流水账

登记册按物品名字排，管不了价格高低。小明又做了一本“价格流水账”：他把每个盒子上标的价格撕下来，从小到大排好，旁边注明盒号。这就是 BTREE 索引的用法。PostgreSQL 默认的 BTREE 索引虽然能直接建在 JSONB 列上，但它比较的是整个 JSONB 值的排序规则（键个数、字符串排序），对“盒子里某个标量”的业务查询几乎没有帮助。真正有用的做法是：先把盒子里那个值抽出来，再对它排序。

抽值用 ->> 操作符，它把 JSON 里的键值转成 SQL 文本：

但这里有个隐蔽的坑：attrs ->> 'price' 出来的是 text 类型，'89' > '100' 按字符串比较会返回 true（因为字符 '8' 大于 '1'）！所以更严谨的做法是先转成数字再建索引：

价格流水账让范围查询如虎添翼，叶子节点只需翻几页就能定位。可如果老板问“名字带 pottery 的盒子”，而盒子标签上写的是"Pottery"大小写不一，那流水账还差点意思——这就轮到表达式索引上场了。

CREATE INDEX idx_price ON products USING BTREE ((attrs ->> 'price'));

CREATE INDEX idx_price_num ON products USING BTREE (((attrs ->> 'price')::numeric));SELECT * FROM products WHERE (attrs ->> 'price')::numeric > 100;

表达式索引：预处理标签架

老板想查名字叫 pottery 的盒子，可盒子上贴的是 “Pottery“。小明索性先把所有名字都转成小写，再做一本”小写名册“：名字统一按小写登记，旁边标注盒号。数据库里这叫表达式索引（Expression Index）——把”某个函数处理后的结果”建成索引，而不是直接索引原始列：

这样做有两个铁律必须记住。第一，查询里的表达式必须和索引表达式“长得一模一样”，数据库不会帮你做代数化简，你写 lower(attrs ->> 'name')，查询里就得原封不动地写这个表达式，少一个括号、多一个类型转换，索引都会“隐身”。第二，表达式里的函数必须是 immutable（不可变）的：同样的输入永远得到同样的输出。lower() 满足，now() 这种随时间变化的函数就不行，因为索引排序的前提是“同一个值永远排在同一位置”。

小明的“小写名册”竣工后，查询时也必须用同样的“小写处理”才能翻动名册。现在册子有了，但老板怎么知道该翻哪一本？仓库里其实还站着一位调度员——下一节就看他怎么派活。

CREATE INDEX idx_lower ON products USING BTREE ((lower(attrs ->> 'name')));

查询规划：调度员的抉择

仓库里有个聪明的调度员（查询规划器），他的职责是决定：老板这个问题，是直接翻盒子快，还是翻登记册快？他手上有两本账：一本是仓库总览（表的统计信息，行数、数据分布），一本是成本清单（顺序读一页多少钱、随机读一页多少钱）。他会估算两种方案的“总工时”，然后选便宜的。

想偷看调度员怎么想，就用 EXPLAIN：

如果看到 Seq Scan，说明调度员决定全表扫；看到 Bitmap Index Scan on idx_attrs_gin，说明索引被用上了。有时候调度员放着索引不用，可能是因为表太小（几万个盒子翻一遍也就几秒）、或者条件选择性太差（80% 的盒子都有 red 标签，翻索引再回表反而更贵）。可以用 ANALYZE products; 让统计信息保持新鲜；SET enable_seqscan = off; 只能用来做实验、验证索引是否生效，生产环境乱关就像把调度员绑起来，可能弄巧成拙。

调度员不是死脑筋：小仓库直接翻反而快，别盲目迷信索引。可日子一久，小明发现登记册越来越厚，翻起来竟比以前还慢——册子出了问题，得“整理”了。

EXPLAIN ANALYZE SELECT * FROM products WHERE attrs @> '{"tags":["red"]}';

膨胀维护：登记册变厚怎么办

登记册用久了，上面贴满撕掉的便签、涂改的痕迹，甚至整页纸只写了半行字就空着。册子页数翻了一倍，有价值的内容却没变多。这就是索引膨胀（bloat）。PostgreSQL 的多版本并发控制（MVCC）是罪魁祸首：每次 UPDATE 或 DELETE 不会真的抹掉旧数据，而是把旧版本标成“死元组”，留着给正在执行的其他事务看。指向这些死元组的索引项也暂时不能删，于是索引页里堆满了“死项空位”。

清理工作分两档。VACUUM (ANALYZE) 是常规保洁：把死元组占用的空间标为可复用，顺便更新统计信息，但它不压缩索引页。真正让册子变薄的是 REINDEX INDEX idx_attrs_gin;——把索引整个重建一遍，物理压缩到最小。生产中通常交给 autovacuum 自动打理，触发阈值由 autovacuum_vacuum_scale_factor 控制（默认 0.2，即表里 20% 是死元组时启动）。

定期整理册子是仓管员的必修课。VACUUM 像撕掉明显废纸，REINDEX 像重新抄一本干净的新册子。但比“册子变厚”更让小明头疼的是：盒子一改，所有册子都得跟着改，这账怎么算？

更新成本：一改全册动

小明把 B1 盒里的价格从 199 改成 299，然后傻眼了：整只盒子被重新抄了一份，GIN 登记册、价格流水账、小写名册，每一本都得新记一笔。PostgreSQL 的 MVCC 机制决定了 UPDATE 不是“原地涂改”，而是生成一个全新的行版本，旧版本留给旧事务看。行上建了多少个索引，新版本就要在多少个索引里插入新指针——这种“一份更新、多处记账”的放大效应，就是写放大（Write Amplification）。

JSONB 的更新更是雪上加霜：即使只用 jsonb_set 改其中一个键，底层仍然要把整份 JSON 序列化、重写进新的行版本，不可能只改某个字节。于是针对高频更新的大 JSONB，聪明的方法是“拆家”：把庞大但低频修改的 JSON 详情字段移到一张单独的副表，主表只保留 id、状态、创建时间等高频率查询/更新的小字段，更新主表小字段时就不再拖累大 JSONB 了。

仓库管理的智慧，不在于把什么都塞进一只大盒子，而在于把常动的放面前、把大而沉的放角落。小明把这些心得整理成册，青云仓库从此井井有条。

AI 应用场景

Python 演示

import psycopg2 def main():    conn = psycopg2.connect(        dbname="postgres",        user="postgres",        password="postgres",        host="localhost",        port="5432"    )    conn.autocommit = True    cur = conn.cursor()     cur.execute("DROP TABLE IF EXISTS products")    cur.execute("""        CREATE TABLE products (            id serial PRIMARY KEY,            name text,            attrs jsonb        )    """)    cur.execute("CREATE INDEX idx_attrs_gin ON products USING GIN (attrs)")     rows = [        ("红背包", '{"tags":["red","bag"],"price":199}'),        ("蓝水杯", '{"tags":["blue","cup"],"price":89}'),        ("红水杯", '{"tags":["red","cup"],"price":99}'),    ]    for name, attrs in rows:        cur.execute(            "INSERT INTO products (name, attrs) VALUES (%s, %s)",            (name, attrs)        )     cur.execute("""        EXPLAIN (COSTS OFF)        SELECT id, name FROM products        WHERE attrs @> '{"tags":["red"]}'    """)    print("执行计划:")    for plan_line, in cur.fetchall():        print("  " + plan_line)     cur.execute("""        SELECT id, name FROM products        WHERE attrs @> '{"tags":["red"]}'    """)    print("命中结果:")    for pid, name in cur.fetchall():        print("  id=%s name=%s" % (pid, name))     cur.close()    conn.close() if __name__ == "__main__":    main()

1. attrs jsonb把 attrs 列声明为 JSONB 类型，插入时自动校验并规范化 JSON。
2. CREATE INDEX idx_attrs_gin ON products USING GIN (attrs)给 JSONB 字段建 GIN 索引，相当于给仓库做物品登记册。
3. WHERE attrs @> '{"tags":["red"]}'JSONB 包含查询：找所有 tags 数组里含 red 的文档。
4. EXPLAIN (COSTS OFF)查看执行计划，确认是否出现 Bitmap Index Scan。
5. VALUES (%s, %s)参数化插入，避免 SQL 注入，JSON 以字符串传入自动转换。

常见误区

面试题