PostgreSQL 索引

索引是数据库性能优化的第一神器。没有索引,数据库要扫描每一行(全表扫描,Seq Scan);有了索引,数据库能像查字典一样快速定位。但索引不是越多越好——它会占空间、拖慢写入。本篇讲透 Postgres 六种索引类型的适用场景,以及部分索引、表达式索引、CONCURRENTLY 等实战技巧。

1. 索引的本质

索引是一种额外的数据结构,贴在表上,让查找变快。本质是"用空间换时间":每加一个索引,磁盘多占一份、写入多一次维护,但相应查询能跳过全表扫描。

类比:表像一本厚厚的字典,索引像书后面的"拼音索引"——找"王"字不用从第一页翻到最后,先查索引知道在第 523 页,直接翻过去。

Postgres 支持六种索引类型,各有适用场景:

B-tree(默认):等值、范围、排序。99% 场景用它。
Hash:只支持等值。很少用。
GIN:JSONB、数组、全文搜索的倒排索引。
GiST:范围、几何、地理、最近邻。
BRIN:超大表的自然有序列(如时间序列)。
SP-GiST:空间分区,如 IP 路由、电话区号。

2. B-tree 索引(默认 / 最常用)

B-tree 是平衡多叉树,查找复杂度 O(log N)。支持等值、范围、排序三类查询,以及 UNIQUE 约束。日常加索引不加 USING 关键字,默认就是 B-tree。

-- === B-tree 索引(默认,最常用)===
-- 适用于:等值查询、范围查询、排序、UNIQUE 约束
-- 数据结构:平衡多叉树,查找复杂度 O(log N)

-- 创建(默认就是 B-tree)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age   ON users(age);

-- 命中索引的查询
SELECT * FROM users WHERE email = 'xm@example.com';   -- 等值
SELECT * FROM users WHERE age BETWEEN 18 AND 60;     -- 范围
SELECT * FROM users ORDER BY age;                    -- 排序
SELECT * FROM users WHERE age > 18 AND age < 60;     -- 复合范围

-- 联合索引(多列)
-- 列顺序很重要!遵循"最左前缀"原则
CREATE INDEX idx_users_role_age ON users(role, age);
SELECT * FROM users WHERE role = 'admin';              -- ✅ 命中
SELECT * FROM users WHERE role = 'admin' AND age > 20; -- ✅ 命中(完全)
SELECT * FROM users WHERE age > 20;                    -- ❌ 不命中(缺最左列)

-- 唯一索引(等价于 UNIQUE 约束)
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);

-- 让 CREATE INDEX 不阻塞写入(生产必备)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- 注意:CONCURRENTLY 不能在事务块里使用,且耗时更长

三个高频考点:

联合索引的"最左前缀":(role, age) 联合索引,只查 age 不命中(缺最左列 role)。
唯一索引 = UNIQUE 约束:加 UNIQUE 约束时 Postgres 自动建唯一 B-tree 索引。
CONCURRENTLY 不阻塞写入:生产环境建索引必须加这个关键字,否则整张表会被锁住。

3. Hash 索引

Hash 索引用哈希表存储,只支持等值查询。理论上比 B-tree 略快,但实际场景几乎不用——B-tree 已经覆盖等值查询,且更通用。除非特殊调优,不必考虑。

-- === Hash 索引 ===
-- 只支持等值查询(=),不支持范围/排序
-- 旧版本(10 之前)的 Hash 索引不 WAL,崩溃会丢,新版已修复
-- 实际很少用,B-tree 已经覆盖等值场景且更通用

CREATE INDEX idx_users_email_hash ON users USING HASH (email);
SELECT * FROM users WHERE email = 'xm@example.com';  -- 命中
SELECT * FROM users WHERE email > 'a';               -- ❌ 不命中

4. GIN 索引(JSONB / 数组 / 全文搜索的杀手)

GIN = Generalized Inverted Index(倒排索引)。它适合"一个行对应多个键值"的场景:JSONB 内部有多个 key、数组有多个元素、文档有多个词。JSONB 章节已重点讲过,这里汇总:

-- === GIN 索引(全文搜索 / JSONB / 数组的利器)===
-- GIN = Generalized Inverted Index(倒排索引)
-- 适用:一个行对应多个"键值"的场景

-- 1. JSONB 索引(@> ? ?| ?& 操作符全支持)
CREATE INDEX idx_events_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"type":"click"}';   -- ✅ 命中

-- 2. 数组索引
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE 'db' = ANY(tags);     -- ✅ 命中
SELECT * FROM posts WHERE tags @> ARRAY['db'];  -- ✅ 命中

-- 3. 全文搜索索引(tsvector + GIN)
CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('simple', body));
SELECT * FROM articles WHERE to_tsvector('simple', body) @@ to_tsquery('simple', '数据库');

-- 4. jsonb_path_ops:更小更快的 GIN(只支持 @>)
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);
-- 体积小一半以上,适合纯 @> 查询场景

-- GIN 索引的代价:写入慢(需更新倒排表)、体积大、构建耗时
-- 适合"读多写少"的查询场景

GIN 的代价:写入慢(需更新倒排表)、体积大、构建耗时。适合"读多写少"的场景。写密集型表慎用。

5. GiST 索引(范围 / 几何 / 地理)

GiST 是一种可扩展的索引框架,支持范围类型、几何类型、地理(PostGIS)、最近邻(KNN)查询。配合扩展能用得很深:

-- === GiST 索引 ===
-- GiST = Generalized Search Tree
-- 适用于:范围类型、几何类型、地理(PostGIS)、最近邻(KNN)

-- 1. 范围类型重叠查询
CREATE TABLE bookings (room INT, during TSTZRANGE);
CREATE INDEX idx_bookings_during ON bookings USING GIST (during);
SELECT * FROM bookings
WHERE during && tstzrange('2026-08-05 14:00+08', '2026-08-05 15:00+08');

-- 2. 地理点(配合 PostGIS)
CREATE EXTENSION postgis;
CREATE TABLE stores (id INT, loc GEOGRAPHY);
CREATE INDEX idx_stores_loc ON stores USING GIST (loc);
-- 查找附近的店
SELECT * FROM stores
WHERE ST_DWithin(loc, ST_MakePoint(116.40, 39.90)::geography, 1000);

-- 3. 排除约束(EXCLUDE)内部也用 GiST 索引
CREATE TABLE room_bookings (
    room_id INT,
    during  TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 4. trigram 模糊查询(pg_trgm 扩展)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name % 'johnson';   -- 模糊匹配(相似度)

两个高频场景:

会议室预约冲突检测:用 TSTZRANGE 范围类型 + GiST 索引 + 排除约束。
附近的人 / 店:配合 PostGIS,几十亿地理点毫秒级查询。

6. BRIN 索引(超大表的轻量选择)

BRIN = Block Range Index。它不存每行,而是把数据按块分组,只记录每个块的范围(min/max)。体积比 B-tree 小几个数量级——一个 1TB 的表,B-tree 索引可能要 50GB,BRIN 只要 50MB。

-- === BRIN 索引(超大数据的轻量选择)===
-- BRIN = Block Range Index(块范围索引)
-- 适用:超大表(GB ~ TB 级)且数据"自然有序"(如按时间插入的日志)
-- 原理:只记录每个数据块的范围(min/max),体积比 B-tree 小几个数量级

-- 场景:几十亿行的日志表,B-tree 索引动辄几十 GB,BRIN 只要几 MB
CREATE TABLE access_log (
    id BIGSERIAL,
    ts TIMESTAMPTZ DEFAULT now(),
    path TEXT
) PARTITION BY RANGE (ts);

CREATE INDEX idx_log_ts_brin ON access_log USING BRIN (ts);
SELECT * FROM access_log WHERE ts > '2026-08-01';

-- BRIN 的代价:精度比 B-tree 低,可能"漏查"(返回比实际多的行,然后过滤)
-- 适合 OLAP 场景,不适合 OLTP 高精度查询

BRIN 的核心前提:数据"自然有序"。比如按时间插入的日志,文件块内的 ts 是近似有序的。如果数据随机分布(没有空间局部性),BRIN 就退化为几乎无用。

7. 高级索引技巧

除基础索引外,Postgres 还有几个高级玩法,能进一步压缩索引体积、提升命中率:

-- === 部分索引(Partial Index)===
-- 只为满足条件的子集建索引,体积更小、维护更快
CREATE INDEX idx_active_users ON users (id) WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NULL AND id > 100;   -- ✅ 命中

-- 经典场景:只为"未完成订单"建索引
CREATE INDEX idx_pending_orders ON orders (user_id)
WHERE status = 'pending';

-- === 表达式索引(Index on Expression)===
-- 索引"函数结果"而不是原始列
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'xm@example.com';   -- ✅ 命中

-- 索引"提取 JSONB 字段"
CREATE INDEX idx_events_type ON events ((data->>'type'));
SELECT * FROM events WHERE data->>'type' = 'click';          -- ✅ 命中

-- === 覆盖索引(Covering Index,Postgres 11+)==
-- INCLUDE 子句:把"经常一起查"的列加进索引,避免回表
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, age);
SELECT email, name, age FROM users WHERE email = 'xm@example.com';
-- 上面查询可以"只用索引就拿到全部数据",不必回表

-- === 删除索引 ===
DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_users_email;   -- 不存在时不报错(常用于脚本)

三大技巧的适用场景:

部分索引:只为高频子集建索引(如未完成订单、活跃用户)。
表达式索引:索引函数结果(如 lower(email)),用于函数包裹的查询。
覆盖索引:把"经常一起查"的列加进 INCLUDE,避免回表(Index-Only Scan)。

8. EXPLAIN:验证索引是否真的命中

建了索引不代表一定会用——优化器会根据统计信息选择"走索引还是全表扫描"。验证方法:

Seq Scan:全表扫描,没走索引(可能该建索引,或索引不适配查询)。
Index Scan:索引扫描,先查索引拿位置,再回表取数据。
Bitmap Heap Scan:批量取(先索引拿一批位置,再统一取数据)。
Index Only Scan:只用索引就拿到所有数据(覆盖索引命中),最快。

如果建了索引但 EXPLAIN 显示 Seq Scan,常见原因:表太小(全表扫更快)、数据分布不均、统计信息过期(跑 ANALYZE 表名 更新)、查询条件不适配索引类型。

9. 何时建索引 / 何时别建

该建索引的信号:

WHERE、JOIN、ORDER BY 涉及的列(尤其大表)。
外键列(Postgres 不会自动给外键建索引!)。
查询慢,EXPLAIN 显示 Seq Scan。

不该建索引的场景:

小表(几百行,全表扫更快)。
写多读少的表(每次写入都要更新所有索引,代价大)。
字段区分度低(如性别,只有男女两值,索引帮不上忙)。
查询中使用函数包裹的列(除非建了对应的表达式索引)。

10. 索引维护

VACUUM:回收已删除行的空间,让索引更紧凑。Postgres 会自动跑,但大表建议手动 VACUUM ANALYZE。
REINDEX:重建索引,清理碎片。REINDEX INDEX CONCURRENTLY 名字(Postgres 12+)不阻塞写入。
ANALYZE:更新统计信息,让优化器选对执行计划。大表数据大量变更后务必跑。
pg_stat_user_indexes:查询每个索引的使用次数,识别"建了但从不查"的索引,可以删掉。

小结

这一章你掌握了 Postgres 的完整索引体系:B-tree(默认)、GIN(JSONB/全文)、GiST(范围/地理)、BRIN(超大表),以及部分索引、表达式索引、覆盖索引三大高级技巧。下一篇进入数据库的可靠性核心——事务与 MVCC。

← 上一篇 PostgreSQL JSONB

下一篇 PostgreSQL 事务与 MVCC →

✈️💬