对 JSONB类型数据的索引：用 GIN和 BTREE索引的玩法

第一个问题：为啥我们要用JSONB？JSONB能让你用JSON格式存数据，结构超灵活。特别适合那种数据结构很复杂、嵌套很多的场景（比如用户profile里有一堆地址或者设置啥的）。跟普通JSON比，JSONB是二进制存储，查找和过滤快多了。

但如果没索引，查JSONB会很慢，尤其是表里有成千上万行的时候。比如你有个用户表，settings字段用JSONB存每个用户的设置。想查所有设置里有某个值的用户，如果没索引，数据库得全表扫描，超级耗资源。这时候索引就派上用场啦！

JSONB索引：重点来了

PostgreSQL支持两种主要方式给JSONB加索引：

1. GIN (Generalized Inverted Index) —— 用来查JSONB里的key和value。
2. BTREE —— 用来简单查找和排序。

它们各有特点，咱们详细聊聊。

给JSONB用GIN索引

GIN是个很强的索引，能搞定数组、文本，还有JSONB。它会把JSONB对象里的key和value都拆出来，建个专门的结构，查起来飞快。

GIN用于JSONB的优点：

• 能按key查，也能按value查。
• 支持嵌套结构。
• 配合@>、?、?|、?&这些操作符，查key和value超快。

假设有个users表，settings字段用JSONB存用户设置。数据示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    settings JSONB
);

INSERT INTO users (name, settings) VALUES
('Alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}}'),
('Bob', '{"theme": "light", "notifications": {"email": false, "sms": true}}'),
('Charlie', '{"theme": "dark", "notifications": {"email": true, "sms": true}}');

现在我们想快速找出所有用暗色主题（theme: dark）的用户。先建个索引：

CREATE INDEX idx_users_settings_gin ON users USING GIN (settings);

然后用@>操作符查（按值查）：

SELECT name
FROM users
WHERE settings @> '{"theme": "dark"}';

这样PostgreSQL就会用GIN索引，查得飞快。

原理是啥？你在JSONB字段上建GIN索引时，PostgreSQL会建个“倒排索引”，把所有JSON的key和value都单独存起来。比如这个对象：

{"theme": "dark", "notifications": {"email": true, "sms": false}}

它会给theme、notifications.email、notifications.sms这些key和它们的值都建索引。这样查单个元素就很快了。

给JSONB用BTREE索引

BTREE是最常见的索引类型。如果你要整体比较JSONB对象，或者要排序，就用它。但和GIN不一样，BTREE不会拆解JSON对象内容。

BTREE用于JSONB的优点：

• 很适合排序和整体比较对象。
• 如果JSONB当成“整体”用（比如直接比较等于某个对象，或者查JSONB等于某值），速度很快。

举个BTREE索引的例子。 假如你经常要把users表的settings字段和某个对象比较：

{"theme": "dark", "notifications": {"email": true, "sms": false}}

先建索引：

CREATE INDEX idx_users_settings_btree ON users USING BTREE (settings);

现在可以直接查对象是否相等：

SELECT name
FROM users
WHERE settings = '{"theme": "dark", "notifications": {"email": true, "sms": false}}';

这个查询会用BTREE索引加速。

GIN和BTREE对比

所以，GIN适合查复杂结构，BTREE适合整体比较或排序。

怎么选索引？

• 想查JSONB里的key或value，就用GIN。
• 要整体比较或排序JSONB对象，就用BTREE。

不过其实你可以两个都建！比如同一个字段既要查key又要整体比较，就GIN和BTREE索引都加上，满足各种需求。

给JSONB建索引时常见的坑

乱建没用的索引：不是每个JSONB字段都值得建索引。索引会占空间，还会拖慢插入和更新。

给很少用的操作符建索引：别因为“感觉对”就建索引。先分析下你的查询，真有用再加。

忽略GIN的特点：GIN建索引比BTREE慢，表很大时要注意。

实际应用场景

用JSONB很适合那种数据结构灵活、经常变的项目。比如：

• Web应用里的用户自定义设置。
• 存日志，不同事件字段不一样。
• 用JSON格式做缓存。

用GIN和BTREE索引这些数据，能大大提升查询性能。比如面试时你可以吹一波，自己怎么给复杂结构加索引让系统飞起来。

PostgreSQL官方关于JSON索引的文档在这里。有啥细节和例子，记得多翻翻。