索引

索引是「用空间换查询时间」的结构，是数据库调优的第一杠杆。但要记住：索引不是越多越好——它拖慢写入、占用磁盘。本章讲清 PG 有哪些索引、各自适用什么。

1. B-tree：默认且万能

CREATE INDEX idx_orders_user ON core.orders (user_id);

B-tree 适合 =、>、<、BETWEEN、排序、IN，是 90% 场景的默认选择。PRIMARY KEY 和 UNIQUE 约束背后都是唯一 B-tree。

2. 组合索引（多列）

CREATE INDEX idx_orders_user_status ON core.orders (user_id, status);

遵循最左前缀：能加速 (user_id)、（user_id, status） 的查询，但单独按 status 查用不到该索引。把等值条件列放前面、范围列放后面。

3. 部分索引（只索引子集）

只对「经常查询的子集」建索引，更小更快：

CREATE INDEX idx_orders_pending ON core.orders (created_at)
WHERE status = 'pending';   -- 只索引待支付订单

特别适合「热数据」查询（如未支付、未删除）。

4. 表达式索引

查询里常对「表达式」过滤时，对表达式建索引：

CREATE INDEX idx_products_lower_name ON core.products (lower(name));
-- 这样 WHERE lower(name) = 'iphone' 才能走索引

注意：只有当查询里的表达式与索引定义逐字一致时才会命中。搭配第 7 章，ILIKE 'abc%'（前缀）也能用 b-tree，但 ILIKE '%abc%' 需要 pg_trgm 的 GIN 索引。

5. 唯一索引

CREATE UNIQUE INDEX idx_users_email ON core.users (email);

除了保证唯一，还顺带提供查询加速。CREATE UNIQUE INDEX 与 UNIQUE 约束等价，但索引名更可控。

6. 特殊索引方法

CREATE INDEX idx_p_gin ON core.products USING gin (attrs jsonb_path_ops);
CREATE INDEX idx_o_brin ON core.orders USING brin (created_at);

7. 索引的代价与维护

• 写入变慢（每行 INSERT/UPDATE/DELETE 都要维护索引）。
• 磁盘/内存占用。
• 索引会膨胀，需要 REINDEX 或 autovacuum 维护。