JSONB数据的索引：使用 GIN和 BTREE索引

在PostgreSQL里，索引就是让你能更快查到数据库里数据的办法。如果你把表里的数据想象成一本本书，那索引就像图书馆的目录，让你能按书名或作者秒查到想要的书。JSONB就稍微麻烦点，因为它的数据是结构化的，不是单纯的行和列。

当你的JSONB数据膨胀到“哈利波特小说那么厚（没插图那种）”时，在里面查找就会变慢。比如你想找所有"status"等于"delivered"的订单，PostgreSQL得把所有记录都翻一遍。这活儿你肯定不想手动干，对吧？

这时候GIN和BTREE索引就像英雄一样来救场，让你不用等到天荒地老！

JSONB的索引类型

GIN（Generalized Inverted Index）

GIN索引就是专门为结构化数据（比如数组和对象）设计的，所以用在JSONB上特别合适。它不是给整个对象建索引，而是给里面的每个key和value都建索引。这样你就能很快查到包含某些key、value或者它们组合的记录。

想象一下有个JSONB列，数据长这样：

{"name": "Alice", "age": 25, "city": "Berlin"}

GIN索引会在内部建个结构，把"name"、"age"、"city"这些key和它们的值都关联起来。所以当你查"name": "Alice"时，PostgreSQL已经知道去哪找了——不用全表乱翻。

BTREE

BTREE索引就比较传统了。它会建一个有序结构，让你能按具体的值快速查找。用在JSONB时，BTREE索引适合查找完全匹配的数据，或者你有固定的key（比如你想整个JSONB对象都比一下）。

比如你的列里有这样的JSONB对象：

{"name": "Bob", "age": 30}

如果你要查整个对象完全一样的记录，BTREE索引就很有用。

{"name": "Bob", "age": 30}

给JSONB建索引

先来看怎么建GIN索引。 你只需要一条神奇的CREATE INDEX命令。长这样：

-- 给JSONB列建GIN索引
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

这里：

• idx_jsonb_data — 索引的名字。
• orders — 表名。
• data — 存JSONB数据的列。

建好索引后，查JSONB里key或value的SQL会快很多。

假设我们有个orders表，data列存的是JSONB：

没有索引时的查询：

-- 查找所有status为"delivered"的订单
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

如果表很大，这个查询会很慢。但有了GIN索引后，速度会快很多。

怎么建BTREE索引

建BTREE索引时，方法稍微不一样。大多数情况下，你要指定只给JSONB对象的某个部分建索引。比如：

-- 给某个key建BTREE索引
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

注意(data->>'total')，它是把JSONB对象里的total值取出来，然后给这个值建索引。这样你查total = 100的订单时，PostgreSQL就能用这个索引了。

还是用上面的数据举例：

查询：

-- 查找所有total为100的订单
SELECT * FROM orders WHERE data->>'total' = '100';

有了data->>'total'的BTREE索引，这个查询会快很多。

GIN和BTREE对比

如果你的JSONB结构很复杂，经常用@>或?这些操作符，选GIN。如果你查的是固定的值或key，BTREE可能更合适。

JSONB索引常见坑和错误

用JSONB索引很强大，但有些坑要注意。

1. 该建索引没建。 如果你经常在WHERE里用JSONB数据，但没建索引，查询会很慢。
2. 索引太多。 如果你每个可能的key都建索引，插入和更新会变慢。
3. 选错索引类型。 如果你的查询很复杂，用了@>或?，但你建的是BTREE索引，那性能不会提升。
4. 不懂路径。 如果你老查嵌套的值，但没给具体路径（比如data->>'some_key'）建索引，查询还是慢。

总结：什么时候用哪种索引

• 有数组或复杂对象，经常查key和value时用GIN。
• 查完全匹配或经常查某个key时用BTREE。