外观
PostgreSQL 索引创建策略
索引类型选择
1. B树索引
B树索引是PostgreSQL中最常用的索引类型，适合大多数查询场景：
sql
-- 创建基本B树索引
CREATE INDEX idx_员工表_部门ID ON 员工表(部门ID);
-- 创建唯一B树索引
CREATE UNIQUE INDEX idx_员工表_邮箱 ON 员工表(邮箱);
-- 创建复合B树索引
CREATE INDEX idx_员工表_部门状态 ON 员工表(部门ID, 状态);2. 哈希索引
哈希索引适合等值查询，但不支持范围查询：
sql
-- 创建哈希索引
CREATE INDEX idx_员工表_身份证号 ON 员工表 USING HASH(身份证号);3. GIN 索引
GIN（Generalized Inverted Index）索引适合数组、JSON、全文搜索等复杂类型：
sql
-- 创建GIN索引用于数组类型
CREATE INDEX idx_产品表_标签 ON 产品表 USING GIN(标签);
-- 创建GIN索引用于JSONB类型
CREATE INDEX idx_订单表_扩展信息 ON 订单表 USING GIN(扩展信息);
-- 创建GIN索引用于全文搜索
CREATE INDEX idx_文章表_内容 ON 文章表 USING GIN(to_tsvector('chinese', 内容));4. GiST 索引
GiST（Generalized Search Tree）索引适合空间数据、范围类型等：
sql
-- 创建GiST索引用于范围类型
CREATE INDEX idx_预订表_时间范围 ON 预订表 USING GiST(时间范围);
-- 创建GiST索引用于空间数据
CREATE INDEX idx_地理位置表_坐标 ON 地理位置表 USING GiST(坐标);索引创建原则
1. 选择合适的列
- 选择查询频率高的列
- 选择选择性高的列（不同值比例高）
- 选择用于JOIN、WHERE、ORDER BY、GROUP BY的列
- 避免在低选择性列上创建索引（如性别、状态等）
2. 复合索引设计
- 将选择性高的列放在前面
- 考虑查询的排序需求
- 避免创建过多的复合索引
- 复合索引的前缀匹配原则
sql
-- 复合索引示例：选择性高的列在前
CREATE INDEX idx_订单表_客户日期 ON 订单表(客户ID, 订单日期);
-- 可以支持以下查询
SELECT * FROM 订单表 WHERE 客户ID = 123;
SELECT * FROM 订单表 WHERE 客户ID = 123 AND 订单日期 >= '2023-01-01';
SELECT * FROM 订单表 WHERE 客户ID = 123 ORDER BY 订单日期;3. 避免过度索引
- 索引会增加写操作开销
- 索引会占用存储空间
- 索引会增加查询优化器的选择难度
- 定期清理无用索引
高级索引技术
1. 部分索引
只对表中部分数据创建索引，适合查询特定条件的数据：
sql
-- 为活跃客户创建部分索引
CREATE INDEX idx_客户表_活跃 ON 客户表(最后活跃日期)
WHERE 状态 = '活跃';
-- 为高价值订单创建部分索引
CREATE INDEX idx_订单表_高价值 ON 订单表(客户ID, 订单金额)
WHERE 订单金额 > 10000;2. 函数索引
对列的函数结果创建索引，适合需要函数计算的查询：
sql
-- 创建函数索引用于大写查询
CREATE INDEX idx_员工表_姓名大写 ON 员工表(UPPER(姓名));
-- 查询时可以使用该索引
SELECT * FROM 员工表 WHERE UPPER(姓名) = '张三';
-- 创建函数索引用于日期查询
CREATE INDEX idx_订单表_月份 ON 订单表(EXTRACT(MONTH FROM 订单日期));3. 覆盖索引
包含查询所需的所有列，避免回表查询：
sql
-- 创建覆盖索引，包含常用查询列
CREATE INDEX idx_订单表_覆盖 ON 订单表(客户ID) INCLUDE (订单日期, 订单金额, 状态);
-- 查询时直接使用覆盖索引，无需回表
SELECT 订单日期, 订单金额, 状态 FROM 订单表 WHERE 客户ID = 123;索引创建最佳实践
1. 生产环境创建索引
sql
-- 生产环境创建索引，避免阻塞写操作
CREATE INDEX CONCURRENTLY idx_表名_列名 ON 表名(列名);
-- 查看索引创建进度
SELECT
pid,
query,
state,
wait_event,
wait_event_type
FROM pg_stat_activity
WHERE query LIKE '%CREATE INDEX%';2. 索引命名规范
- 使用
idx_表名_列名格式 - 复合索引使用
idx_表名_列名1_列名2格式 - 部分索引使用
idx_表名_列名_条件格式 - 函数索引使用
idx_表名_函数名_列名格式
3. 索引维护
sql
-- 查看索引大小
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexname)) AS 索引大小
FROM pg_indexes
WHERE schemaname = 'public';
-- 查看索引使用情况
SELECT
relname AS 表名,
indexrelname AS 索引名,
idx_scan AS 扫描次数,
idx_tup_read AS 索引读取行数,
idx_tup_fetch AS 表读取行数
FROM pg_stat_user_indexes u
JOIN pg_stat_user_tables t ON u.relid = t.relid;
-- 重建索引
REINDEX INDEX CONCURRENTLY idx_表名_列名;
-- 删除无用索引
DROP INDEX IF EXISTS idx_无用索引;索引创建的性能影响
1. 对写操作的影响
- INSERT：需要维护索引
- UPDATE：如果更新了索引列，需要维护索引
- DELETE：需要维护索引
2. 对读操作的影响
- 提高查询速度
- 占用缓存空间
- 增加查询优化时间
3. 平衡读写性能
- 对于写密集型表，减少索引数量
- 对于读密集型表，适当增加索引
- 定期分析表统计信息
sql
-- 分析表统计信息
ANALYZE 表名;
-- 自动分析配置
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;常见问题（FAQ）
Q1：如何判断是否需要创建索引？
A1：判断是否需要创建索引的方法：
- 分析查询执行计划，查看是否有全表扫描
- 检查查询的WHERE、JOIN、ORDER BY、GROUP BY子句
- 考虑列的选择性和查询频率
- 测试创建索引前后的查询性能
Q2：复合索引和多个单列索引有什么区别？
A2：复合索引和多个单列索引的区别：
| 特性 | 复合索引 | 多个单列索引 |
|---|---|---|
| 存储 | 存储在一个索引结构中 | 存储在多个索引结构中 |
| 前缀匹配 | 支持前缀匹配 | 不支持 |
| 查询优化 | 优化器更容易选择 | 优化器选择难度大 |
| 维护成本 | 较低 | 较高 |
| 覆盖查询 | 支持 | 不支持 |
Q3：什么时候应该使用部分索引？
A3：适合使用部分索引的场景：
- 表中只有部分数据被频繁查询
- 查询条件包含固定值
- 索引列选择性在特定条件下很高
- 希望减少索引大小和维护成本
Q4：如何选择索引类型？
A4：选择索引类型的依据：
| 查询类型 | 推荐索引类型 |
|---|---|
| 等值查询 | B树、哈希 |
| 范围查询 | B树、GiST |
| 数组查询 | GIN |
| JSON查询 | GIN |
| 全文搜索 | GIN、GiST |
| 空间查询 | GiST、SP-GiST |
| 范围类型 | GiST |
Q5：如何监控索引性能？
A5：监控索引性能的方法：
- 使用
pg_stat_user_indexes查看索引使用情况 - 使用
EXPLAIN ANALYZE分析查询计划 - 监控索引大小和增长趋势
- 检查长时间运行的索引维护操作
- 使用
pg_stat_statements查看索引相关查询的性能
Q6：如何处理大型表的索引创建？
A6：大型表索引创建的最佳实践：
- 使用
CONCURRENTLY选项避免阻塞 - 在业务低峰期执行
- 考虑使用分区表减少索引大小
- 分批创建索引
- 监控索引创建进度
Q7：索引可以提高所有查询的性能吗？
A7：不是所有查询都能通过索引提高性能：
- 全表扫描可能比索引扫描更快（当查询返回大部分数据时）
- 低选择性列上的索引可能不会被使用
- 频繁更新的列上的索引可能影响写性能
- 复杂查询可能无法有效使用索引
Q8：如何清理无用索引？
A8：清理无用索引的步骤：
- 分析索引使用情况，找出长时间未使用的索引
- 测试删除索引前后的查询性能
- 使用
DROP INDEX CONCURRENTLY删除无用索引 - 定期审查索引使用情况
- 建立索引创建和删除的审批流程