PostgreSQL / 系统课时
创建单列与联合索引
创建单列与联合索引
本课解决“索引该建在哪些列、顺序如何安排”的问题。你将根据查询中的过滤条件和排序要求,设计单列索引与联合索引,并避免看似很多却无法命中的重复索引。
学习目标
- 根据高频查询创建合适的单列索引
- 按过滤与排序方式确定联合索引列顺序
- 判断哪些查询只能使用联合索引的部分前缀
单列索引适合单一访问路径
当查询经常只按一个字段过滤,例如按订单号、创建时间或用户编号查找,可以先考虑单列索引。
例如,CREATE INDEX idx_orders_merchant_id ON orders (merchant_id); 可以为商户编号建立单列索引。它能支持 WHERE merchant_id = <ID>,也可能支持按 merchant_id 排序。但如果真实查询总是同时包含状态和时间,单列索引往往不能完整覆盖访问路径。
联合索引按查询模式设计
假设后台经常查询某商户的已支付订单,并按创建时间倒序分页。联合索引应把等值条件放在前面,把范围或排序列放在后面。
-- 重建演示表,避免已有对象影响结果
DROP TABLE IF EXISTS demo_payments;
CREATE TABLE demo_payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
merchant_id bigint NOT NULL,
status varchar(20) NOT NULL,
amount numeric(10, 2) NOT NULL,
created_at timestamp NOT NULL
);
-- 构造多个商户和状态的数据分布
INSERT INTO demo_payments (merchant_id, status, amount, created_at)
SELECT
(g % 20) + 1,
CASE WHEN g % 3 = 0 THEN 'PAID' ELSE 'PENDING' END,
(g % 5000) / 10.0,
timestamp '2026-01-01 00:00:00' + g * interval '1 minute'
FROM generate_series(1, 20000) AS g;
-- 等值过滤列在前,排序列放在最后
CREATE INDEX idx_demo_payments_merchant_status_time
ON demo_payments (merchant_id, status, created_at DESC);
ANALYZE demo_payments;
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, amount, created_at
FROM demo_payments
WHERE merchant_id = 8
AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;预期结果
执行计划的核心节点包含:
Limit
Index Scan using idx_demo_payments_merchant_status_time on demo_payments
Index Cond: merchant_id = 8 AND status = 'PAID'该索引先定位商户,再定位状态,最后按索引中的时间顺序取前 20 行,因此通常不需要额外排序。
理解最左前缀
联合索引 (merchant_id, status, created_at) 可以直接支持只按 merchant_id 查询,也可以支持同时按 merchant_id 和 status 查询。
如果查询只有 status = 'PAID',缺少最左侧的 merchant_id,该索引通常无法形成高效的连续搜索范围。此时是否另建 status 索引,要看状态值的选择性和查询频率。
一旦前面的列使用范围条件,后续列通常难以继续缩小索引扫描范围。例如 merchant_id > 10 AND status = 'PAID' 中,status 可能只参与过滤,而不是完整索引定位。
避免重复和低收益索引
若已经存在 (merchant_id, status, created_at),再创建完全相同的索引没有意义。是否还需要单独的 (merchant_id),要看查询量、索引大小和写入成本,而不是机械保留。
索引会增加插入、更新和删除时的维护工作。设计时应从高频 SQL 出发,用执行计划验证,而不是为每个查询条件都单独建索引。