Home

深色模式

PostgreSQL 索引与执行计划 ​

概述 ​

索引最容易被用成两种极端：一种是完全不建，查询慢了再拍脑袋补；另一种是看见字段就想加，最后表上挂满索引，写入变慢，查询也未必真受益。更稳的做法其实很朴素：先看真实查询，再决定索引怎么建。

这一篇重点不在“把索引类型背全”，而在“看到一条查询，知道该怎么想”。单列索引、联合索引、唯一索引、部分索引、表达式索引和 JSONB 索引，基本已经覆盖了中小项目最常见的场景。

查询模式 ​

建索引前，先把最常见的查询列出来，例如：

  • 按 email 查单个用户
  • 按 status 和 created_at 查订单列表
  • 按 user_id 查某个用户最近的订单
  • 按 metadata->>'channel' 查来源渠道

再根据这些查询去建索引，而不是反过来先建一堆索引，再希望业务自己去适配。

单列索引 ​

如果后台经常按邮箱找用户：

sql
CREATE UNIQUE INDEX idx_users_email ON users (email);

如果某个列表经常按创建时间倒序取最近数据：

sql
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

注意，主键和唯一约束通常已经自带索引，不要重复再建一份长得几乎一样的索引。

外键索引 ​

这是 PostgreSQL 里一个很常见的误会。下面这条外键：

sql
user_id BIGINT NOT NULL REFERENCES users(id)

会让 users.id 这边拥有主键索引，但不会自动帮 orders.user_id 建索引。

如果查询经常这样写：

sql
SELECT *
FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 20;

通常还要自己补一条：

sql
CREATE INDEX idx_orders_user_id ON orders (user_id);

如果还经常按 created_at 排序，那就进一步考虑联合索引。

联合索引 ​

例如订单列表常见查询：

sql
SELECT id, order_no, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

更贴合这条查询的索引会是：

sql
CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at DESC);

如果另一个常见查询是“看某个用户最近的订单”：

sql
CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

联合索引不是把常用字段随手拼在一起就结束了。顺序要贴着过滤条件和排序条件来想。

唯一约束 ​

像 email、order_no 这类字段，如果业务上明确要求唯一，就应该让数据库知道。

例如：

sql
ALTER TABLE orders
ADD CONSTRAINT orders_order_no_key UNIQUE (order_no);

从使用效果上看，唯一约束背后会用到唯一索引。更重要的是，它不只是为了加速查询，而是在数据库层表达“这条规则不能破”。

部分索引 ​

如果后台绝大多数查询只关心已支付订单，可以只给这一部分数据建索引：

sql
CREATE INDEX idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

这种索引通常更小，也更贴合实际查询。

它适合的场景一般是：

  • 某个状态值查询特别高频
  • 条件本身比较稳定
  • 没必要给整张表所有数据都建同样的索引

表达式索引 ​

如果经常按邮箱小写值查用户：

sql
CREATE INDEX idx_users_lower_email
ON users ((lower(email)));

对应查询也要写成同样的表达式：

sql
SELECT *
FROM users
WHERE lower(email) = 'tom@example.com';

表达式索引很适合那些“查询方式固定，但字段原始值不直接可索引命中”的场景。

JSONB 索引 ​

如果查询是这种包含关系：

sql
SELECT *
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

通常适合 GIN 索引：

sql
CREATE INDEX idx_orders_metadata_gin
ON orders USING GIN (metadata);

但如果只是频繁按一个固定路径过滤，有时表达式索引反而更直接：

sql
CREATE INDEX idx_orders_channel
ON orders ((metadata->>'channel'));

索引方式要跟查询方式对齐，不然很容易出现“索引建了，执行计划还是不用”的尴尬场面。

执行计划 ​

查询一慢，第一反应别急着改机器，也别先狂建索引，先看执行计划：

sql
EXPLAIN ANALYZE
SELECT id, order_no, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

最值得关注的是：

  • 是 Seq Scan 还是 Index Scan
  • 有没有额外 Sort
  • 估算行数和实际行数差多少
  • 过滤条件和索引顺序是否匹配

EXPLAIN ANALYZE 最大的价值，不是给人一种“数据库很高级”的感觉，而是让“为什么慢”这件事从猜测变成证据。

如果还想顺手看看缓存和磁盘页读写情况，可以进一步用：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

这比只看“有没有走索引”更接近真实现场。

在线建索引 ​

如果表已经很大、线上又还在写入，直接 CREATE INDEX 可能会对业务造成明显影响。这时候通常会优先考虑：

sql
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC);

它不是所有场景都必须用，但在在线加索引时很常见。只是它不能放在普通事务块里执行，这一点要提前知道。

常见误区 ​

每个字段都先加索引 ​

索引是成本，不是装饰。没有稳定查询场景的列，不要为了显得勤快就先建上。

联合索引不看顺序 ​

字段都对，不代表索引就对。顺序错了，执行计划可能根本用不上。

查询改了，索引没跟着改 ​

业务列表页过滤条件一变，旧索引很可能已经不合身了。索引不是“一次建完终身不管”。

参考 ​