PostgreSQL / 系统课时
创建唯一与部分索引
创建唯一与部分索引
本课解决两个常见需求:阻止重复业务数据,以及只为“真正需要查询的那部分行”建立索引。你将使用唯一索引维护规则,并用部分索引降低索引体积和维护成本。
学习目标
- 使用唯一索引约束单列或多列组合
- 使用
WHERE条件创建部分索引 - 判断查询条件是否能匹配部分索引谓词
唯一索引不仅用于加速
唯一索引会在写入时检查键值是否重复,因此它同时承担查询优化和数据完整性约束。
下面要求同一租户内的邮箱不能重复,但不同租户可以使用相同邮箱。
sql
-- 唯一索引示例:同一租户内邮箱不可重复
DROP TABLE IF EXISTS demo_accounts;
CREATE TABLE demo_accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
email varchar(200) NOT NULL
);
CREATE UNIQUE INDEX uq_demo_accounts_tenant_email
ON demo_accounts (tenant_id, email);
INSERT INTO demo_accounts (tenant_id, email)
VALUES (1, 'alice@example.com');
INSERT INTO demo_accounts (tenant_id, email)
VALUES (2, 'alice@example.com');
-- 使用冲突处理验证重复组合不会再次写入
INSERT INTO demo_accounts (tenant_id, email)
VALUES (1, 'alice@example.com')
ON CONFLICT DO NOTHING
RETURNING id;预期结果
text
前两次插入成功。
第三次插入返回 0 行,因为 tenant_id = 1 与 email 的组合已经存在。若业务希望重复写入直接报错,可以移除 ON CONFLICT DO NOTHING。错误信息会指出唯一约束或唯一索引冲突。
NULL 值需要单独理解
普通唯一索引默认允许出现多个 NULL,因为 SQL 语义中 NULL 不等于另一个 NULL。如果业务要求空值也只能出现一次,PostgreSQL 15 及以上可以在唯一索引中使用 NULLS NOT DISTINCT。该选项只适用于允许空值的列,并应根据真实业务规则决定是否启用。
部分索引只覆盖目标行
如果订单表的大多数记录已经完成,但后台经常查询待处理订单,可以只索引 PENDING 行。
sql
-- 部分索引示例:只索引待处理任务
DROP TABLE IF EXISTS demo_tasks;
CREATE TABLE demo_tasks (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
merchant_id bigint NOT NULL,
status varchar(20) NOT NULL,
created_at timestamp NOT NULL
);
-- 仅约 5% 的记录处于 PENDING 状态
INSERT INTO demo_tasks (merchant_id, status, created_at)
SELECT
(g % 50) + 1,
CASE WHEN g % 20 = 0 THEN 'PENDING' ELSE 'DONE' END,
timestamp '2026-01-01 00:00:00' + g * interval '1 minute'
FROM generate_series(1, 20000) AS g;
-- 索引中只保存满足谓词的行
CREATE INDEX idx_demo_tasks_pending
ON demo_tasks (merchant_id, created_at)
WHERE status = 'PENDING';
ANALYZE demo_tasks;
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, created_at
FROM demo_tasks
WHERE status = 'PENDING'
AND merchant_id = 11
ORDER BY created_at;预期结果
text
执行计划的核心节点包含:
Index Scan using idx_demo_tasks_pending on demo_tasks
Index Cond: merchant_id = 11查询条件明确包含 status = 'PENDING',优化器才能证明结果全部位于部分索引中。若查询写成 status IN ('PENDING', 'DONE'),该索引不能覆盖完整结果。
选择唯一索引还是唯一约束
UNIQUE 约束底层通常也会创建唯一 B-tree 索引。需要表达明确的数据规则时,优先使用表约束;需要表达式、部分条件或特殊索引参数时,则直接创建唯一索引更灵活。
按自己的节奏,确认真正理解。