PostgreSQL 索引（Index） 是提升查询性能的关键数据库对象，通过在列上建立快速查找结构，大幅减少全表扫描（Sequential Scan），尤其在 WHERE、JOIN、ORDER BY、GROUP BY 等操作中。
一、索引核心作用
操作
无索引
有索引
WHERE id = 100
全表扫描
索引查找（O(log n)）
ORDER BY created_at
排序开销大
直接读取有序数据
JOIN 大表
嵌套循环慢
高效合并
二、PostgreSQL 支持的索引类型
类型
用途
语法
B-tree（默认）
=, >, <, >=, <=, BETWEEN, IN, IS NULL
CREATE INDEX ON table(col);
Hash
仅支持 =
CREATE INDEX ON table USING HASH(col);
GiST
几何、全文搜索、范围类型
CREATE INDEX ON table USING GIST(col);
SP-GiST
分区搜索（如电话号码、IP）
GIN
数组、jsonb、全文搜索
CREATE INDEX ON table USING GIN(col);
BRIN
超大表、按物理顺序存储
CREATE INDEX ON table USING BRIN(col);
三、创建索引基本语法
CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name
ON table_name [USING method]
(column_name [ASC|DESC] [NULLS FIRST|LAST], ...);
示例：
-- 1. 单列 B-tree 索引（默认）
CREATE INDEX idx_users_email ON users(email);
-- 2. 复合索引（多列）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 3. 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(lower(email));
-- 4. 表达式索引
CREATE INDEX idx_users_lower_name ON users(lower(name));
-- 5. 部分索引（WHERE 条件）
CREATE INDEX idx_orders_active ON orders(user_id) WHERE status = 'active';
-- 6. GIN 索引（用于 jsonb 或数组）
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- 7. 并发创建索引（不锁表，生产推荐）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
四、索引使用场景与最佳实践
场景
推荐索引
主键
自动创建 UNIQUE B-tree
外键
建议建索引（JOIN 加速）
频繁 WHERE 过滤列
B-tree
LIKE 'prefix%'
B-tree 支持
LIKE '%suffix'
不支持 → 用 pg_trgm + GIN
ORDER BY col
B-tree（可避免排序）
jsonb 字段查询
GIN
全文搜索
GIN + tsvector
超大表（亿级）+ 按时间顺序
BRIN
五、索引分析与优化
1. 查看执行计划（推荐）
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';
Index Scan using idx_users_email → 使用了索引
Seq Scan → 没用索引，需优化
2. 查看表所有索引
-- 方式1：\d+ 在 psql 中
\d+ users
-- 方式2：查询系统表
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'users';
3. 查看索引是否被使用
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';
六、索引维护
操作
命令
删除索引
DROP INDEX index_name;
重建索引
REINDEX INDEX index_name;
并发重建
REINDEX INDEX CONCURRENTLY index_name;
查看索引大小
“`sql
七、常见索引陷阱
问题
原因
解决
索引未使用
选择性低（如 gender）
避免低选择性列建索引
LIKE '%abc'
不支持 B-tree
用 pg_trgm 扩展 + GIN
函数包裹列
WHERE lower(email) = 'a'
建表达式索引
频繁更新列
索引维护开销大
权衡读写频率
索引膨胀
大量 UPDATE/DELETE
定期 REINDEX 或 VACUUM
八、实战示例：电商订单查询优化
-- 表结构
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT,
status VARCHAR(20),
created_at TIMESTAMP,
total_amount DECIMAL
);
-- 常见查询
SELECT * FROM orders
WHERE user_id = 123
AND status = 'completed'
AND created_at >= '2025-01-01'
ORDER BY created_at DESC;
推荐索引：
-- 复合索引（顺序重要！）
CREATE INDEX idx_orders_query
ON orders(user_id, status, created_at DESC);
-- 或部分索引（如果 status 很少变）
CREATE INDEX idx_orders_completed
ON orders(user_id, created_at DESC)
WHERE status = 'completed';
索引列顺序原则：WHERE 中等号 → WHERE 中范围 → ORDER BY
九、GIN 索引：数组与 JSONB
-- 数组列
ALTER TABLE products ADD COLUMN tags TEXT[];
CREATE INDEX idx_products_tags ON products USING GIN(tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics'];
-- JSONB 列
ALTER TABLE users ADD COLUMN profile JSONB;
CREATE INDEX idx_users_profile ON users USING GIN(profile);
SELECT * FROM users WHERE profile -> 'settings' ->> 'theme' = 'dark';
十、BRIN 索引：海量数据神器
适合 按插入顺序递增 的列（如 created_at），索引极小。
CREATE TABLE logs (
id BIGSERIAL,
created_at TIMESTAMP DEFAULT NOW(),
message TEXT
);
CREATE INDEX idx_logs_created_at_brin
ON logs USING BRIN(created_at);
-- 查询最近一天
SELECT * FROM logs WHERE created_at >= NOW() - INTERVAL '1 day';
BRIN 索引大小 ≈ 几 KB（即使表 100GB）
总结：索引选择表
场景
推荐索引
等值查询
B-tree
范围查询
B-tree
排序
B-tree（带方向）
全文搜索
GIN + tsvector
数组包含
GIN
JSONB 嵌套查询
GIN
超大表 + 时间序列
BRIN
几何数据
GiST
常用命令速查
-- 创建索引
CREATE INDEX idx_name ON table(col);
-- 并发创建（生产）
CREATE INDEX CONCURRENTLY idx_name ON table(col);
-- 删除索引
DROP INDEX idx_name;
-- 查看索引
\d+ table_name
-- 查看索引大小
SELECT pg_size_pretty(pg_total_relation_size('idx_name'));
-- 强制使用索引（调试）
SET enable_seqscan = OFF;