第五章：索引 —— 档案目录卡不是越多越好 - 现代前后端完全指南：软件城市宇宙

没有索引时，数据库像馆员从头翻完整排档案柜。

有索引时，数据库像先查目录卡，再去拿那几份档案。

text

没有索引
SELECT -> 从第 1 行翻到最后 1 行

有索引
SELECT -> 先查目录卡 -> 定位页 -> 取目标行

最常用的是 B-tree，默认 CREATE INDEX 就是它。它适合：

例如：

sql

CREATE INDEX idx_citizens_national_id ON citizens(national_id);
CREATE INDEX idx_citizens_district_status ON citizens(district_id, status);

多列索引不是“把几个单列索引拼一起”，而是有顺序的。

比如 (district_id, status) 更适合这类查询：

sql

SELECT *
FROM citizens
WHERE district_id = 1 AND status = 'active';

但如果你常查的是：

sql

SELECT *
FROM citizens
WHERE status = 'active';

这个复合索引未必是最佳答案。

PostgreSQL 还有几个很实用的高级索引：

1. 表达式索引

sql

CREATE INDEX idx_citizens_lower_name
ON citizens (lower(full_name));

这样下面的查询就能吃到索引：

sql

SELECT *
FROM citizens
WHERE lower(full_name) = '林远';

2. 部分索引

只给“部分行”建索引。

sql

CREATE INDEX idx_permits_active
ON permits (citizen_id, valid_to)
WHERE status = 'active';

如果你 90% 的查询都只关心有效证件，这比给全表建索引更小、更快。

3. 部分唯一索引

“只对部分行要求唯一”。

sql

CREATE UNIQUE INDEX uq_permits_one_active_type
ON permits (citizen_id, permit_type)
WHERE status = 'active';

这表示：同一个人，同一种证件，同时只能有一张 active。

这类规则不能写成普通 UNIQUE 约束，但很适合写成部分唯一索引。

4. 线上大表建索引要小心锁

普通建索引：

sql

CREATE INDEX idx_permits_created_at ON permits(created_at);

默认允许读，但会阻塞写。

线上通常更常见的是：

sql

CREATE INDEX CONCURRENTLY idx_permits_created_at
ON permits(created_at);

CONCURRENTLY 能减少对线上写入的影响，但代价是更慢、限制更多，也不能放进普通事务块里随便包。

一句话记忆：

索引是用读性能换写成本、换存储空间。 不是“慢了就加索引”，而是“先看查询，再决定建什么索引”。