AI 时代重新学 PostgreSQL(四):索引系统,不只是 B-tree

用 MySQL 的时候，索引基本就是 B-tree，偶尔用一下全文索引。到了 PostgreSQL，发现索引的世界比想象的大得多。

这篇文章的目标不是把每种索引的底层数据结构都讲一遍（那是教科书干的事），而是让你知道：什么场景该用什么索引，以及怎么验证你的索引是否生效。

在聊索引之前，必须先会用 EXPLAIN ANALYZE。它是 PostgreSQL 的”透视镜”，让你看到查询到底是怎么执行的。

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';                                    QUERY PLAN--------------------------------------------------------------------------- Seq Scan on users  (cost=0.00..25.00 rows=1 width=64)                    (actual time=0.150..0.320 loops=1)   Filter: (email = 'test@example.com'::text)   Rows Removed by Filter: 999 Planning Time: 0.080 ms Execution Time: 0.350 ms

• • Seq Scan = 全表扫描，没有用到索引
• • cost = 估算的查询成本
• • actual time = 实际执行时间（毫秒）
• • Rows Removed by Filter = 被过滤掉的行数

加上索引后：

CREATE INDEX idx_users_email ON users (email);EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';                                    QUERY PLAN--------------------------------------------------------------------------- Index Scan using idx_users_email on users  (cost=0.28..8.29 rows=1 width=64)                                            (actual time=0.025..0.026 loops=1)   Index Cond: (email = 'test@example.com'::text) Planning Time: 0.100 ms Execution Time: 0.045 ms

从 Seq Scan 变成了 Index Scan，执行时间从 0.35ms 降到 0.045ms。后面每讲一种索引，都会用 EXPLAIN ANALYZE 来验证。

B-tree：默认索引

B-tree 是 PostgreSQL 的默认索引类型，也是最通用的。适用于等值查询和范围查询。

-- 不指定类型时默认就是 B-treeCREATE INDEX idx_users_age ON users (age);-- 等值查询SELECT * FROM users WHERE age = 25;-- 范围查询SELECT * FROM users WHERE age BETWEEN 20 AND 30;-- 排序（B-tree 索引天然有序，可以避免额外的排序操作）SELECT * FROM users ORDER BY age LIMIT 10;

复合索引

CREATE INDEX idx_users_city_age ON users (city, age);-- 能用到索引（匹配最左前缀）SELECT * FROM users WHERE city = '北京';SELECT * FROM users WHERE city = '北京' AND age > 25;-- 用不到索引（跳过了第一个列）SELECT * FROM users WHERE age > 25;

复合索引的列顺序很重要，遵循”最左前缀”原则。这一点和 MySQL 是一样的。

GIN：全文搜索和 JSONB 的好搭档

GIN（Generalized Inverted Index，通用倒排索引）适合处理包含多个元素的值——数组、JSONB、全文搜索向量。

JSONB 查询加速

CREATE TABLE products (    id SERIAL PRIMARY KEY,    name TEXT,    attrs JSONB);-- 假设有 10 万条数据-- 不加索引的 JSONB 查询EXPLAIN ANALYZESELECT * FROM products WHERE attrs @> '{"color": "black"}';-- Seq Scan ... actual time=45.000..45.500-- 加上 GIN 索引CREATE INDEX idx_products_attrs ON products USING GIN (attrs);EXPLAIN ANALYZESELECT * FROM products WHERE attrs @> '{"color": "black"}';-- Bitmap Index Scan using idx_products_attrs ... actual time=0.100..0.100

GIN 索引支持的 JSONB 操作符：@>（包含）、?（存在键）、?|（存在任意键）、?&（存在所有键）。

数组查询加速

CREATE TABLE articles (    id SERIAL PRIMARY KEY,    title TEXT,    tags TEXT[]);CREATE INDEX idx_articles_tags ON articles USING GIN (tags);-- 包含某个标签SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];-- 包含任一标签SELECT * FROM articles WHERE tags && ARRAY['go', 'redis'];

全文搜索

-- 添加全文搜索向量列ALTER TABLE articles ADD COLUMN search_vector tsvector;-- 填充搜索向量UPDATE articles SET search_vector = to_tsvector('simple', title);-- 建 GIN 索引CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);-- 全文搜索查询SELECT title FROM articlesWHERE search_vector @@ to_tsquery('simple', 'postgresql');

PostgreSQL 的全文搜索不如 Elasticsearch 强大，但对于中小规模的搜索需求已经够用，而且不需要额外部署服务。

GIN 的代价

GIN 索引的写入开销比 B-tree 大得多。每次 INSERT 或 UPDATE 都需要更新倒排索引。如果你的表写入非常频繁，GIN 索引可能会成为瓶颈。

可以通过 fastupdate 参数缓解：

-- 开启延迟更新（默认就是开启的）CREATE INDEX idx_products_attrs ON products USING GIN (attrs) WITH (fastupdate = on);

开启后，GIN 不会立即更新索引，而是先把变更攒在一个待处理列表里，达到一定量后批量合并。代价是查询时可能需要额外扫描待处理列表。

GiST：几何、范围和最近邻

GiST（Generalized Search Tree，通用搜索树）适合处理”非精确匹配”的查询——重叠、包含、距离等。

范围类型索引

CREATE TABLE reservations (    id SERIAL PRIMARY KEY,    room TEXT,    during TSRANGE);CREATE INDEX idx_reservations_during ON reservations USING GIST (during);-- 查找与某个时间段重叠的预约EXPLAIN ANALYZESELECT * FROM reservationsWHERE during && '[2023-07-20 14:00, 2023-07-20 16:00)';-- Index Scan using idx_reservations_during

最近邻搜索

-- 假设有一个存储地理坐标的表CREATE TABLE shops (    id SERIAL PRIMARY KEY,    name TEXT,    location POINT);CREATE INDEX idx_shops_location ON shops USING GIST (location);-- 查找离某个坐标最近的 5 家店SELECT name, location <-> point(116.4, 39.9) AS distanceFROM shopsORDER BY location <-> point(116.4, 39.9)LIMIT 5;

<-> 是距离操作符。配合 GiST 索引，这个查询不需要计算所有店铺的距离再排序，而是直接通过索引找到最近的。

BRIN：大表的省空间利器

BRIN（Block Range Index）是一种非常紧凑的索引类型，适合数据按物理顺序排列的大表。

典型场景是时序数据：日志表、事件表这类按时间递增插入的数据。

CREATE TABLE logs (    id SERIAL PRIMARY KEY,    created_at TIMESTAMP NOT NULL DEFAULT NOW(),    level TEXT,    message TEXT);-- 假设有千万级数据，按时间顺序插入-- B-tree 索引可能占几百 MB-- BRIN 索引只需要几 MBCREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at);EXPLAIN ANALYZESELECT * FROM logsWHERE created_at BETWEEN '2023-07-01' AND '2023-07-02';-- Bitmap Index Scan using idx_logs_created_at

BRIN 的原理是：它不记录每一行数据的索引条目，而是记录每个数据块（默认 128 个页面为一组）的最小值和最大值。查询时先通过 BRIN 排除不可能包含目标数据的块，再在候选块内扫描。

好处是索引极小，维护开销也小。代价是精度不如 B-tree——如果数据的物理顺序和逻辑顺序不一致（比如频繁 UPDATE 导致行位置变化），BRIN 的效果会大打折扣。

部分索引

只对满足条件的行建索引。MySQL 没有这个功能。

-- 只对活跃用户建索引CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';-- 这个查询会用到部分索引SELECT * FROM users WHERE email = 'test@example.com' AND status = 'active';-- 这个查询不会（status 不匹配）SELECT * FROM users WHERE email = 'test@example.com' AND status = 'inactive';

部分索引的好处是：如果你的表有 1000 万行，但只有 10 万行是活跃用户，索引只需要覆盖这 10 万行，体积小、维护开销也小。

实际项目中非常有用的场景：

-- 只对未删除的记录建唯一索引CREATE UNIQUE INDEX idx_unique_email ON users (email) WHERE deleted_at IS NULL;-- 这样同一个邮箱可以有多条已删除的记录，但活跃记录只能有一条

表达式索引

对计算结果建索引，MySQL 8.0 以前做不到。

-- 对 email 的小写形式建索引CREATE INDEX idx_users_email_lower ON users (LOWER(email));-- 这个查询会用到索引SELECT * FROM users WHERE LOWER(email) = 'test@example.com';-- 对 JSONB 的某个路径建 B-tree 索引（比 GIN 更精确）CREATE INDEX idx_products_color ON products ((attrs->>'color'));SELECT * FROM products WHERE attrs->>'color' = 'black';

表达式索引的典型用途：不区分大小写的搜索、日期的年/月提取、JSONB 特定字段的精确查询。

索引选择速查表

小结

PostgreSQL 的索引系统给了你精细化调优的能力。核心原则：

1. 1. 先有查询，再建索引 — 不要凭感觉建，用 EXPLAIN ANALYZE 验证
2. 2. B-tree 是默认选择，只在有明确需求时才用其他类型
3. 3. GIN 适合”多对多”的查询（JSONB、数组、全文搜索），但写入开销大
4. 4. GiST 适合”空间性”的查询（范围、距离、重叠）
5. 5. BRIN 适合大表 + 有序数据，索引极小但精度有限
6. 6. 部分索引和表达式索引是 PostgreSQL 的独特武器，善用能省很多空间和性能

下一篇我们聊高级查询——CTE、窗口函数、LATERAL JOIN 这些 MySQL 用户不太熟悉的查询方式。