PostgreSQL 唯一索引完全指南

概述

唯一索引是 PostgreSQL 中一种特殊的索引类型，用于强制数据的唯一性约束。它不仅能够提升查询性能，更重要的是确保数据的完整性和一致性。

INFO

唯一索引与普通索引的主要区别在于：唯一索引会阻止重复值的插入，而普通索引仅用于加速查询。

基本语法

sql

CREATE UNIQUE INDEX index_name ON table_name (column1 [, column2, ...])
[ NULLS [ NOT ] DISTINCT ];

语法解析

index_name：索引的名称
table_name：目标表名
column1, column2, ...：参与唯一约束的列
NULLS [ NOT ] DISTINCT：控制 NULL 值的处理方式

核心特性

1. 索引类型限制

目前，只有 B-tree 索引可以声明为唯一索引。其他索引类型（如 GIN、GiST、BRIN）不支持唯一性约束。

2. NULL 值处理机制

PostgreSQL 中唯一索引对 NULL 值的处理有两种模式：

模式	语法	NULL 值处理	适用场景
默认模式	`CREATE UNIQUE INDEX`	NULL 值不相等，允许多个 NULL	大多数业务场景
严格模式	`CREATE UNIQUE INDEX ... NULLS NOT DISTINCT`	NULL 值相等，只允许一个 NULL	严格唯一性要求

实际业务场景应用

场景 1：用户邮箱唯一性

问题陈述

在用户注册系统中，需要确保每个邮箱地址只能注册一次，但允许某些用户暂时不填写邮箱（NULL 值）。

解决方案

创建表和索引测试数据插入

sql

-- 创建用户表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建邮箱唯一索引（默认模式）
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

sql

-- 插入正常数据
INSERT INTO users (username, email) VALUES
('张三', '[email protected]'),
('李四', '[email protected]'),
('王五', NULL),
('赵六', NULL);  -- 允许多个NULL值

-- 尝试插入重复邮箱（会失败）
INSERT INTO users (username, email) VALUES
('重复用户', '[email protected]');
-- ERROR: duplicate key value violates unique constraint

分析过程

索引创建：idx_users_email_unique确保 email 列的唯一性
NULL 值处理：默认模式下，多个 NULL 值被允许，满足"用户可以不填邮箱"的需求
约束执行：当尝试插入重复邮箱时，PostgreSQL 会抛出错误并拒绝插入

输入和输出

输入数据：

用户名    邮箱
张三     [email protected]
李四     [email protected]
王五     NULL
赵六     NULL

查询结果：

sql

SELECT * FROM users;

user_id	username	email	created_at
1	张三	[email protected]	2025-06-03 10:00:00
2	李四	[email protected]	2025-06-03 10:01:00
3	王五	NULL	2025-06-03 10:02:00
4	赵六	NULL	2025-06-03 10:03:00

场景 2：商品 SKU 管理

问题陈述

电商系统中，每个商品的 SKU（库存单位）必须全局唯一，不允许任何重复或 NULL 值。

解决方案

严格唯一索引业务操作示例

sql

-- 创建商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    price DECIMAL(10,2),
    category_id INTEGER
);

-- 创建SKU严格唯一索引
CREATE UNIQUE INDEX idx_products_sku_strict
ON products (sku)
NULLS NOT DISTINCT;

sql

-- 插入商品数据
INSERT INTO products (product_name, sku, price, category_id) VALUES
('iPhone 15 Pro 256GB 黑色', 'IPHONE15PRO-256-BLK', 8999.00, 1),
('iPhone 15 Pro 256GB 白色', 'IPHONE15PRO-256-WHT', 8999.00, 1),
('MacBook Pro 14寸 M3', 'MBP14-M3-512-SLV', 16999.00, 2);

-- 查看索引使用情况
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE sku = 'IPHONE15PRO-256-BLK';

分析过程

严格模式：使用NULLS NOT DISTINCT确保即使是 NULL 值也必须唯一
业务保障：防止 SKU 重复导致的库存混乱和订单错误
查询优化：唯一索引同时提供了快速的 SKU 查找能力

场景 3：多列组合唯一性

问题陈述

在课程选课系统中，需要确保同一个学生不能重复选择同一门课程，但不同学生可以选择相同课程。

解决方案

sql

-- 创建选课表
CREATE TABLE course_enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'active'
);

-- 创建学生-课程组合唯一索引
CREATE UNIQUE INDEX idx_student_course_unique
ON course_enrollments (student_id, course_id);

业务流程图

完整示例

数据插入测试查询分析

sql

-- 成功的选课操作
INSERT INTO course_enrollments (student_id, course_id) VALUES
(1001, 2001),  -- 学生1001选择课程2001
(1001, 2002),  -- 学生1001选择课程2002
(1002, 2001);  -- 学生1002选择课程2001

-- 失败的选课操作（重复选课）
INSERT INTO course_enrollments (student_id, course_id) VALUES
(1001, 2001);  -- 错误：学生1001已经选择了课程2001

sql

-- 查看某学生的选课情况
SELECT
    student_id,
    course_id,
    enrollment_date,
    status
FROM course_enrollments
WHERE student_id = 1001
ORDER BY enrollment_date;

-- 查看某课程的选课统计
SELECT
    course_id,
    COUNT(*) as enrollment_count
FROM course_enrollments
WHERE status = 'active'
GROUP BY course_id;

自动创建的唯一索引

主键约束

当定义主键时，PostgreSQL 自动创建唯一索引：

sql

-- 创建带主键的表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,  -- 自动创建唯一索引
    customer_id INTEGER NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2)
);

-- 查看自动创建的索引
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

唯一约束

sql

-- 使用UNIQUE约束
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,  -- 自动创建唯一索引
    phone VARCHAR(20) UNIQUE    -- 自动创建唯一索引
);

当表已有唯一约束或主键时，无需手动创建相同列的唯一索引，这样做只会重复自动创建的索引，浪费存储空间。

性能考量和最佳实践

1. 索引维护成本

2. 查询性能优化

使用唯一索引的查询性能对比测试

sql

-- 高效的点查询
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM users WHERE email = '[email protected]';

-- 查询计划显示：
-- Index Scan using idx_users_email_unique on users
--   Index Cond: (email = '[email protected]')

sql

-- 创建测试数据
INSERT INTO users (username, email)
SELECT
    'user_' || i,
    'user_' || i || '@test.com'
FROM generate_series(1, 100000) i;

-- 测试查询性能
\timing on
SELECT * FROM users WHERE email = '[email protected]';
-- Time: 0.123 ms （使用唯一索引）

3. 索引监控

sql

-- 监控索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE indexname LIKE '%unique%'
ORDER BY idx_scan DESC;

常见错误和解决方案

1. 重复值错误

sql

-- 错误示例
INSERT INTO users (username, email) VALUES ('新用户', '[email protected]');
-- ERROR: duplicate key value violates unique constraint "idx_users_email_unique"
-- DETAIL: Key (email)=([email protected]) already exists.

解决方案：

使用ON CONFLICT使用UPSERT应用层检查

sql

-- 方法1：使用ON CONFLICT忽略重复
INSERT INTO users (username, email)
VALUES ('新用户', '[email protected]')
ON CONFLICT (email) DO NOTHING;

sql

-- 方法2：使用UPSERT更新现有记录
INSERT INTO users (username, email)
VALUES ('新用户名', '[email protected]')
ON CONFLICT (email)
DO UPDATE SET
    username = EXCLUDED.username,
    updated_at = CURRENT_TIMESTAMP;

sql

-- 方法3：应用层先检查再插入
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM users WHERE email = '[email protected]') THEN
        INSERT INTO users (username, email) VALUES ('测试用户', '[email protected]');
    ELSE
        RAISE NOTICE '邮箱已存在，跳过插入操作';
    END IF;
END $$;

2. NULL 值处理问题

sql

-- 问题：需要确保某列最多只有一个NULL值
CREATE TABLE settings (
    setting_id SERIAL PRIMARY KEY,
    setting_name VARCHAR(50),
    is_default BOOLEAN
);

-- 解决方案：使用部分唯一索引
CREATE UNIQUE INDEX idx_settings_single_default
ON settings (is_default)
WHERE is_default = true;

维护和管理

重建唯一索引

sql

-- 重建索引（在线操作）
REINDEX INDEX CONCURRENTLY idx_users_email_unique;

-- 或者删除后重建
DROP INDEX idx_users_email_unique;
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

监控索引大小

sql

SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_indexes
WHERE tablename = 'users'
AND indexname LIKE '%unique%';

总结

唯一索引是 PostgreSQL 中确保数据完整性的关键机制：

数据完整性：防止重复数据，维护业务规则
查询性能：提供快速的等值查询能力
灵活的 NULL 处理：根据业务需求选择合适的 NULL 处理策略
自动维护：主键和唯一约束会自动创建对应的唯一索引

TIP

在设计数据库时，优先考虑使用约束（UNIQUE、PRIMARY KEY）而非手动创建唯一索引，这样可以更清楚地表达业务意图，同时获得相同的性能和数据完整性保障。

PostgreSQL 唯一索引完全指南 ​

概述 ​

基本语法 ​

语法解析 ​

核心特性 ​

1. 索引类型限制 ​

2. NULL 值处理机制 ​

实际业务场景应用 ​

场景 1：用户邮箱唯一性 ​

问题陈述 ​

解决方案 ​

分析过程 ​

输入和输出 ​

场景 2：商品 SKU 管理 ​

问题陈述 ​

解决方案 ​

分析过程 ​

场景 3：多列组合唯一性 ​

问题陈述 ​

解决方案 ​

业务流程图 ​

完整示例 ​

自动创建的唯一索引 ​

主键约束 ​

唯一约束 ​

性能考量和最佳实践 ​

1. 索引维护成本 ​

2. 查询性能优化 ​

3. 索引监控 ​

常见错误和解决方案 ​

1. 重复值错误 ​

2. NULL 值处理问题 ​

维护和管理 ​

重建唯一索引 ​

监控索引大小 ​

总结 ​

PostgreSQL 唯一索引完全指南

概述

基本语法

语法解析

核心特性

1. 索引类型限制

2. NULL 值处理机制

实际业务场景应用

场景 1：用户邮箱唯一性

问题陈述

解决方案

分析过程

输入和输出

场景 2：商品 SKU 管理

问题陈述

解决方案

分析过程

场景 3：多列组合唯一性

问题陈述

解决方案

业务流程图

完整示例

自动创建的唯一索引

主键约束

唯一约束

性能考量和最佳实践

1. 索引维护成本

2. 查询性能优化

3. 索引监控

常见错误和解决方案

1. 重复值错误

2. NULL 值处理问题

维护和管理

重建唯一索引

监控索引大小

总结