pgsql-05 约束与索引

2026-03-16 约 2575 字预计阅读 6 分钟

05 - 约束与索引

1. 📖 概述

约束和索引是保证数据完整性和查询性能的关键。本章详细介绍PostgreSQL的各种约束类型和索引策略。

2. 🔒 约束(Constraints)

2.1 主键约束(PRIMARY KEY)

-- 方式1: 创建表时定义
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 方式2: 命名主键
CREATE TABLE products (
    id SERIAL,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(200),
    CONSTRAINT pk_products PRIMARY KEY (id)
);

-- 方式3: 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price NUMERIC(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- 添加主键约束
ALTER TABLE categories ADD PRIMARY KEY (id);

-- 删除主键约束
ALTER TABLE categories DROP CONSTRAINT categories_pkey;

与MySQL对比:

PostgreSQL主键自动创建唯一索引
MySQL主键是聚簇索引(InnoDB)

2.2 唯一约束(UNIQUE)

-- 单列唯一
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 命名唯一约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    CONSTRAINT uk_products_sku UNIQUE (sku)
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

-- 部分唯一(PostgreSQL特色)
CREATE UNIQUE INDEX uk_active_users_email
ON users(email) WHERE is_active = TRUE;

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT uk_users_email;

2.3 外键约束(FOREIGN KEY)

-- 基本外键
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount NUMERIC(10, 2)
);

-- 命名外键
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 级联操作
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    content TEXT
);

-- ON DELETE选项:
-- CASCADE: 删除父记录时,删除子记录
-- SET NULL: 删除父记录时,子记录外键设为NULL
-- SET DEFAULT: 设为默认值
-- RESTRICT: 有子记录时禁止删除(默认)
-- NO ACTION: 同RESTRICT

-- ON UPDATE选项(同上)
CREATE TABLE order_logs (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

2.4 检查约束(CHECK)

-- 单列检查
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price NUMERIC(10, 2) CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0)
);

-- 命名检查约束
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    salary NUMERIC(10, 2),
    CONSTRAINT chk_employees_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_employees_salary CHECK (salary > 0)
);

-- 多列检查
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CHECK (end_date >= start_date)
);

-- 复杂检查
CREATE TABLE discounts (
    id SERIAL PRIMARY KEY,
    discount_type VARCHAR(20),
    discount_value NUMERIC(5, 2),
    CHECK (
        (discount_type = 'percentage' AND discount_value BETWEEN 0 AND 100) OR
        (discount_type = 'fixed' AND discount_value >= 0)
    )
);

-- 添加检查约束
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- 删除检查约束
ALTER TABLE products DROP CONSTRAINT chk_price_positive;

2.5 NOT NULL约束

-- 创建表时定义
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- 可为NULL
);

-- 添加NOT NULL
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- 删除NOT NULL
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

2.6 DEFAULT约束

-- 默认值
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    is_published BOOLEAN DEFAULT FALSE
);

-- 表达式默认值
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    log_date DATE DEFAULT CURRENT_DATE,
    random_id UUID DEFAULT gen_random_uuid()
);

-- 修改默认值
ALTER TABLE posts ALTER COLUMN status SET DEFAULT 'pending';

-- 删除默认值
ALTER TABLE posts ALTER COLUMN status DROP DEFAULT;

3. 📇 索引(Indexes)

3.1 B-tree索引(默认)

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 多列索引(复合索引)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 唯一索引
CREATE UNIQUE INDEX uk_users_username ON users(username);

-- 降序索引
CREATE INDEX idx_posts_created_desc ON posts(created_at DESC);

-- 部分索引(条件索引)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_products_total_price ON products((price * quantity));

3.2 GIN索引(倒排索引)

适用于: JSONB, 数组, 全文搜索

-- JSONB索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 数组索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 全文搜索索引
CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('english', title || ' ' || content));

3.3 GiST索引(通用搜索树)

适用于: 几何类型, 范围类型, 全文搜索

-- 范围类型索引
CREATE INDEX idx_bookings_period ON room_bookings USING GIST (booking_period);

-- 几何类型索引
CREATE INDEX idx_locations_position ON locations USING GIST (position);

-- 全文搜索(GiST也支持)
CREATE INDEX idx_documents_search ON documents USING GIST (to_tsvector('english', content));

3.4 Hash索引

仅支持等值查询

-- Hash索引(PostgreSQL 10+才可靠)
CREATE INDEX idx_users_id_hash ON users USING HASH (id);

-- 一般不推荐,B-tree通常更好

3.5 BRIN索引(块范围索引)

适用于: 大表,自然排序的列

-- BRIN索引(非常小,适合大表)
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 适合场景:
-- - 时序数据(日志、监控数据)
-- - 自然排序的大表
-- - 磁盘空间有限

4. 🎯 索引最佳实践

4.1 1. 何时创建索引

-- ✅ 应该创建索引的列:
-- - WHERE子句频繁使用的列
CREATE INDEX idx_orders_status ON orders(status);

-- - JOIN条件列
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- - ORDER BY列
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- - GROUP BY列
CREATE INDEX idx_sales_region ON sales(region);

-- - 外键列(PostgreSQL不自动创建)
CREATE INDEX idx_orders_user_id ON orders(user_id);

4.2 2. 复合索引列顺序

-- 查询: WHERE user_id = ? AND status = ? ORDER BY created_at
-- 正确顺序: 选择性高的列在前
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at DESC);

-- 规则:
-- 1. WHERE等值条件列在前
-- 2. WHERE范围条件列在中
-- 3. ORDER BY列在后

4.3 3. 覆盖索引

-- 查询只需要索引列,不需要访问表
CREATE INDEX idx_users_email_username ON users(email) INCLUDE (username);

-- PostgreSQL 11+支持INCLUDE
-- 查询会使用Index Only Scan
SELECT username FROM users WHERE email = 'alice@example.com';

4.4 4. 部分索引

-- 只索引有效数据
CREATE INDEX idx_active_users_email ON users(email)
WHERE is_active = TRUE AND deleted_at IS NULL;

-- 优点: 索引更小,维护成本更低

4.5 5. 避免过度索引

-- ❌ 不好: 过多索引
CREATE INDEX idx1 ON users(email);
CREATE INDEX idx2 ON users(username);
CREATE INDEX idx3 ON users(created_at);
CREATE INDEX idx4 ON users(email, username);  -- 冗余!
CREATE INDEX idx5 ON users(email, created_at);  -- 可能冗余

-- ✅ 好: 根据查询模式合理索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_created ON users(created_at);

5. 🔍 索引管理

5.1 查看索引

-- 查看表的所有索引
\d users

-- 查看索引详情
\d idx_users_email

-- SQL查询索引
SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- 查看索引大小
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

5.2 索引使用统计

-- 查看索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

-- 找出未使用的索引
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

5.3 重建索引

-- 重建单个索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 重建数据库所有索引
REINDEX DATABASE mydb;

-- 并发重建(不锁表)
CREATE INDEX CONCURRENTLY idx_users_email_new ON users(email);
DROP INDEX idx_users_email;
ALTER INDEX idx_users_email_new RENAME TO idx_users_email;

5.4 删除索引

-- 删除索引
DROP INDEX idx_users_email;

-- 如果不存在不报错
DROP INDEX IF EXISTS idx_users_email;

-- 并发删除(不锁表)
DROP INDEX CONCURRENTLY idx_users_email;

6. 🆚 PostgreSQL vs MySQL

特性	PostgreSQL	MySQL
主键	非聚簇索引	聚簇索引(InnoDB)
外键自动索引	不自动创建	自动创建
部分索引	支持	不支持
表达式索引	支持	5.7+支持虚拟列
GIN索引	支持	不支持
GiST索引	支持	不支持
BRIN索引	支持	不支持
并发创建索引	CONCURRENTLY	ALGORITHM=INPLACE
CHECK约束	完整支持	8.0+支持
INCLUDE列	11+支持	不支持

7. 📝 实战示例

7.1 电商系统约束和索引

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3)
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES categories(id),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT chk_stock_nonnegative CHECK (stock >= 0)
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_name ON products USING GIN (to_tsvector('english', name));
CREATE INDEX idx_active_products ON products(category_id, price) WHERE is_active = TRUE;

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_total_positive CHECK (total_amount > 0),
    CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'delivered';
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 订单明细表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    subtotal NUMERIC(12, 2) NOT NULL,
    CONSTRAINT chk_quantity_positive CHECK (quantity > 0),
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT chk_subtotal_match CHECK (subtotal = price * quantity)
);

CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

8. 📚 下一步

学习基础查询语句
了解查询优化与执行计划

关键要点:

主键和唯一约束自动创建索引
外键列需要手动创建索引
复合索引注意列顺序
部分索引可以减小索引大小
定期检查未使用的索引
CONCURRENTLY避免锁表

目录

pgsql-05 约束与索引

05 - 约束与索引

1. 📖 概述

2. 🔒 约束(Constraints)

2.1 主键约束(PRIMARY KEY)

2.2 唯一约束(UNIQUE)

2.3 外键约束(FOREIGN KEY)

2.4 检查约束(CHECK)

2.5 NOT NULL约束

2.6 DEFAULT约束

3. 📇 索引(Indexes)

3.1 B-tree索引(默认)

3.2 GIN索引(倒排索引)

3.3 GiST索引(通用搜索树)

3.4 Hash索引

3.5 BRIN索引(块范围索引)

4. 🎯 索引最佳实践

4.1 1. 何时创建索引

4.2 2. 复合索引列顺序

4.3 3. 覆盖索引

4.4 4. 部分索引

4.5 5. 避免过度索引

5. 🔍 索引管理

5.1 查看索引

5.2 索引使用统计

5.3 重建索引

5.4 删除索引

6. 🆚 PostgreSQL vs MySQL

7. 📝 实战示例

7.1 电商系统约束和索引

8. 📚 下一步