39. 索引基础（B-tree/唯一/多列）

为什么用索引

索引（INDEX）是一份”排好序的指针清单”。打个比方：查字典时翻拼音目录比一页页翻快得多，索引就是数据库的”目录”。

没有索引时，PostgreSQL 只能做”全表扫描”（Sequential Scan），一行行看过去。表越大越慢。有了索引，它能快速定位到目标行，再做一次”索引扫描”（Index Scan）。

默认索引类型：B-tree

PostgreSQL 默认用 B-tree 索引，适合等值和范围查询（=、 >、 <、 BETWEEN、 ORDER BY）。如果不写 USING，就是 B-tree。

B-tree 是一种”平衡树”结构，数据按顺序排好，既能快速”点查”某个值，也能顺着叶子节点”扫一段范围”。所以它能同时服务等值条件和排序。

先建示例表 orders：

postgres=# CREATE TABLE orders (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   user_id    INT,
postgres=#   amount     NUMERIC(10,2),
postgres=#   status     TEXT,
postgres=#   created_at TIMESTAMP DEFAULT now()
postgres=# );

在 amount 上建一个普通索引：

postgres=# CREATE INDEX idx_orders_amount ON orders (amount);

先准备好全书统一的示例表 users（本章的 orders 见上文）：

postgres=# CREATE TABLE users (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   username   TEXT NOT NULL,
postgres=#   email      TEXT,
postgres=#   age        INT,
postgres=#   created_at TIMESTAMP DEFAULT now()
postgres=# );

唯一索引

唯一索引（Unique Index）既加速查询，又强制列值不重复，和 UNIQUE 约束效果一致。常用于邮箱、用户名这类不能重复的字段。

postgres=# CREATE UNIQUE INDEX idx_users_email ON users (email);

如果插入重复邮箱，会直接报错。

部分索引（Partial Index）

部分索引只对表里”一部分行”建索引，用 WHERE 子句限定。它体积更小、维护更轻，适合只频繁查某类数据的场景。

比如订单表大多已完结，你只常查”未支付”（status = ‘unpaid’）的订单：

postgres=# CREATE INDEX idx_orders_unpaid ON orders (created_at)
postgres=# WHERE status = 'unpaid';

这个索引只包含未支付订单，查询时同样能命中：

postgres=# SELECT * FROM orders
postgres=# WHERE status = 'unpaid'
postgres=# ORDER BY created_at;  -- 走 idx_orders_unpaid

外键列也该建索引

当你用 user_id 这种外键去 JOIN 或过滤时，给它建个索引能明显提速：

postgres=# CREATE INDEX idx_orders_user_id ON orders (user_id);

多列索引

多列索引（Multicolumn Index）把多列合在一个索引里，适合经常一起出现在 WHERE 里的组合条件。

postgres=# CREATE INDEX idx_orders_user_status ON orders (user_id, status);

这种索引遵循”最左前缀”原则：它能加速 (user_id) 或 (user_id, status) 的查询，但单独用 status 做条件时，通常派不上用场。

postgres=# -- 能用上索引：最左列 user_id 在条件里
postgres=# SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
postgres=# -- 用不上索引：缺少最左列 user_id
postgres=# SELECT * FROM orders WHERE status = 'paid';

查看与删除索引

用 psql 元命令查看表上的索引：

postgres=# \d orders

列出数据库中全部索引：

postgres=# \di

删除索引：

postgres=# DROP INDEX IF EXISTS idx_orders_amount;

主键和唯一约束会自动建隐式索引，不需要你手动再建。删掉约束时，对应的索引也会跟着删。

用 EXPLAIN 验证索引是否生效

别凭感觉建索引，用 EXPLAIN 看执行计划。有索引时是 Index Scan 或 Bitmap Heap Scan，没索引时是 Seq Scan：

postgres=# EXPLAIN SELECT * FROM orders WHERE amount = 99.00;

想看真实耗时，加 ANALYZE（它会真的执行一次查询）：

postgres=# EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

什么时候不该建索引

1. 表很小（几百行以内），全表扫描本来就快。
2. 写多读少，每次写入都要维护索引，得不偿失。
3. 列的选择性很低，比如”性别”只有两个值，建索引意义不大。

不阻塞建索引：CONCURRENTLY

默认 CREATE INDEX 会锁表，建索引期间不能写入。生产环境的大表不能直接这么干。加 CONCURRENTLY 可以在建索引的同时继续读写：

postgres=# CREATE INDEX CONCURRENTLY idx_orders_amount ON orders (amount);

索引也会膨胀，要 REINDEX

索引不是一成不变的。大量 UPDATE / DELETE 后，索引里会留下”空洞”，体积变大、查询变慢。这时候用 REINDEX 重建：

postgres=# REINDEX INDEX idx_orders_user_id;
postgres=# REINDEX TABLE orders;  -- 重建整张表的所有索引

找出用不上的索引

建了一堆索引，到底哪些从没被用过？查系统视图 pg_stat_user_indexes：

postgres=# SELECT indexrelname, idx_scan
postgres=# FROM pg_stat_user_indexes
postgres=# WHERE idx_scan = 0;  -- 扫描次数为 0，可能没用

索引选择的经验法则

给你的索引决策列几条我常用的经验：第一，常用于 WHERE、JOIN、ORDER BY 的列优先建；第二，选择性越高越值得建；第三，写多读少的表克制建；第四，小表别建。实在拿不准，就 EXPLAIN 看慢查询，让优化器告诉你它有没有走索引，比拍脑袋强。

索引和约束的关系

顺带厘清：主键和唯一约束会自动建索引，所以你不用再手动建一遍。外键不会自动在子表建索引，得自己加。普通索引只加速查询，不保证唯一。把这几种关系理清楚，就不会重复建索引或漏建索引。

常见误区

1. 索引越多查询越快。错，索引拖慢写入，还可能让优化器选错计划。
2. 给外键忘了建索引，导致 JOIN 慢。
3. 多列索引顺序乱放，结果后面的列永远用不上。
4. 小表上也疯狂建索引，纯属负担。