PostgreSQL / 系统课时

创建单列与联合索引

创建单列与联合索引

本课解决“索引该建在哪些列、顺序如何安排”的问题。你将根据查询中的过滤条件和排序要求,设计单列索引与联合索引,并避免看似很多却无法命中的重复索引。

学习目标

  • 根据高频查询创建合适的单列索引
  • 按过滤与排序方式确定联合索引列顺序
  • 判断哪些查询只能使用联合索引的部分前缀

单列索引适合单一访问路径

当查询经常只按一个字段过滤,例如按订单号、创建时间或用户编号查找,可以先考虑单列索引。

例如,CREATE INDEX idx_orders_merchant_id ON orders (merchant_id); 可以为商户编号建立单列索引。它能支持 WHERE merchant_id = <ID>,也可能支持按 merchant_id 排序。但如果真实查询总是同时包含状态和时间,单列索引往往不能完整覆盖访问路径。

联合索引按查询模式设计

假设后台经常查询某商户的已支付订单,并按创建时间倒序分页。联合索引应把等值条件放在前面,把范围或排序列放在后面。

sql
-- 重建演示表,避免已有对象影响结果
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;

预期结果

text
执行计划的核心节点包含:
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 出发,用执行计划验证,而不是为每个查询条件都单独建索引。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建单列与联合索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通