PostgreSQL 索引维护

索引维护的重要性

1. 什么是索引维护

索引维护是指对数据库索引进行的一系列操作，包括：

• 索引重建
• 索引碎片整理
• 索引统计信息更新
• 无用索引清理

2. 索引维护的必要性

• 提高查询性能：保持索引的高效性
• 减少存储空间：清理碎片，减少索引占用空间
• 更新统计信息：帮助查询优化器生成更好的执行计划
• 修复索引损坏：处理可能的索引损坏问题
• 提高写入性能：减少索引维护的开销

索引重建（REINDEX）

1. 基本 REINDEX 操作

-- 重建单个索引
REINDEX INDEX idx_员工表_部门ID;

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

-- 重建整个数据库的所有索引
REINDEX DATABASE 数据库名;

-- 重建整个架构的所有索引
REINDEX SCHEMA public;

2. 并发 REINDEX

-- 并发重建索引（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_员工表_部门ID;

-- 并发重建表的所有索引
REINDEX TABLE CONCURRENTLY 员工表;

3. REINDEX 的使用场景

• 索引碎片化严重
• 索引统计信息不准确
• 索引损坏
• 表结构发生重大变化
• 升级 PostgreSQL 版本后

索引碎片管理

1. 查看索引碎片

-- 查看索引大小和表大小，计算碎片化程度
SELECT 
    t.tablename AS 表名,
    i.indexname AS 索引名,
    pg_size_pretty(pg_relation_size(t.tablename::regclass)) AS 表大小,
    pg_size_pretty(pg_relation_size(i.indexname::regclass)) AS 索引大小,
    pg_size_pretty(pg_relation_size(t.tablename::regclass) + pg_relation_size(i.indexname::regclass)) AS 总大小
FROM pg_tables t
JOIN pg_indexes i ON t.tablename = i.tablename
WHERE t.schemaname = 'public'
ORDER BY 总大小 DESC;

2. 碎片产生的原因

• 频繁的 INSERT、UPDATE、DELETE 操作
• 大表的批量操作
• 索引列的值频繁变化
• 长时间运行的事务

3. 碎片清理方法

-- 方法1：使用 VACUUM FULL 清理碎片（会锁定表）
VACUUM FULL ANALYZE 员工表;

-- 方法2：使用 REINDEX 重建索引
REINDEX INDEX CONCURRENTLY idx_员工表_部门ID;

-- 方法3：重建表（极端情况）
CREATE TABLE 员工表_new AS SELECT * FROM 员工表;
DROP TABLE 员工表;
ALTER TABLE 员工表_new RENAME TO 员工表;
-- 重建索引
CREATE INDEX CONCURRENTLY idx_员工表_部门ID ON 员工表(部门ID);

VACUUM ANALYZE 操作

1. VACUUM 的作用

• 清理死亡元组（dead tuples）
• 释放未使用的空间
• 更新可见性映射
• 防止事务ID回卷

2. ANALYZE 的作用

• 更新表的统计信息
• 帮助查询优化器生成更好的执行计划
• 提高查询性能

3. VACUUM ANALYZE 命令

-- 常规 VACUUM（不阻塞写操作）
VACUUM 员工表;

-- 带分析的 VACUUM
VACUUM ANALYZE 员工表;

-- 完整 VACUUM（会锁定表）
VACUUM FULL ANALYZE 员工表;

-- 自动 VACUUM 配置
ALTER TABLE 员工表 SET (autovacuum_enabled = true);
ALTER TABLE 员工表 SET (autovacuum_analyze_scale_factor = 0.05);
ALTER TABLE 员工表 SET (autovacuum_analyze_threshold = 50);

索引统计信息管理

1. 查看统计信息

-- 查看表的统计信息
SELECT 
    schemaname,
    tablename,
    n_live_tup AS 活跃行数,
    n_dead_tup AS 死亡行数,
    last_vacuum AS 最后VACUUM时间,
    last_analyze AS 最后ANALYZE时间
FROM pg_stat_user_tables;

-- 查看索引的统计信息
SELECT 
    relname AS 表名,
    indexrelname AS 索引名,
    idx_scan AS 扫描次数,
    idx_tup_read AS 索引读取行数,
    idx_tup_fetch AS 表读取行数
FROM pg_stat_user_indexes;

2. 手动更新统计信息

-- 分析单个表
ANALYZE 员工表;

-- 分析表的特定列
ANALYZE 员工表(部门ID, 状态);

-- 分析整个数据库
ANALYZE DATABASE 数据库名;

索引维护策略

1. 定期维护计划

• 每日：运行 VACUUM ANALYZE
• 每周：检查索引使用情况，清理无用索引
• 每月：重建碎片化严重的索引
• 季度：全面检查和优化所有索引

2. 监控索引健康状态

-- 创建监控视图
CREATE OR REPLACE VIEW v_index_health AS
SELECT 
    t.tablename AS 表名,
    i.indexname AS 索引名,
    pg_size_pretty(pg_relation_size(i.indexname::regclass)) AS 索引大小,
    u.idx_scan AS 扫描次数,
    CASE 
        WHEN u.idx_scan < 100 THEN '很少使用' 
        WHEN u.idx_scan < 1000 THEN '偶尔使用' 
        ELSE '频繁使用' 
    END AS 使用频率,
    CASE 
        WHEN (pg_relation_size(i.indexname::regclass)::numeric / NULLIF(pg_relation_size(t.tablename::regclass), 0)) > 0.5 THEN '高' 
        WHEN (pg_relation_size(i.indexname::regclass)::numeric / NULLIF(pg_relation_size(t.tablename::regclass), 0)) > 0.2 THEN '中' 
        ELSE '低' 
    END AS 索引比例
FROM pg_tables t
JOIN pg_indexes i ON t.tablename = i.tablename
JOIN pg_stat_user_indexes u ON u.indexrelname = i.indexname
WHERE t.schemaname = 'public';

-- 查看索引健康状态
SELECT * FROM v_index_health;

3. 自动维护配置

-- 全局自动 VACUUM 配置
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_naptime = '1min';
ALTER SYSTEM SET autovacuum_max_workers = 3;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;

-- 重新加载配置
SELECT pg_reload_conf();

索引维护的最佳实践

1. 生产环境注意事项

• 使用 CONCURRENTLY 选项避免阻塞
• 在业务低峰期执行维护操作
• 监控维护操作的进度
• 备份重要数据后再执行维护操作

2. 监控维护操作

-- 查看正在执行的维护操作
SELECT 
    pid,
    query,
    state,
    wait_event,
    wait_event_type,
    current_query AS 执行中的查询
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' OR query LIKE '%REINDEX%' OR query LIKE '%ANALYZE%';

3. 维护日志配置

-- 配置维护操作日志
ALTER SYSTEM SET log_autovacuum_min_duration = '100ms';
ALTER SYSTEM SET log_statement = 'mod';
SELECT pg_reload_conf();

常见问题（FAQ）

Q1：什么时候需要重建索引？

A1：需要重建索引的场景：

1. 索引碎片化严重（索引大小超过表大小的50%）
2. 索引扫描次数很少或几乎不使用
3. 表经历了大量的 UPDATE/DELETE 操作
4. 索引统计信息不准确
5. 升级 PostgreSQL 版本后

Q2：REINDEX 和 VACUUM FULL 有什么区别？

A2：REINDEX 和 VACUUM FULL 的区别：

操作	作用	锁级别	影响
REINDEX	重建索引	共享锁（普通）/ 排它锁（并发）	仅影响索引
VACUUM FULL	清理碎片并重组表	排它锁	影响整个表
VACUUM	清理死亡元组	共享锁	影响较小

Q3：如何判断索引是否需要维护？

A3：判断索引是否需要维护的方法：

1. 查看索引的使用频率（pg_stat_user_indexes.idx_scan）
2. 比较索引大小和表大小
3. 查看索引的碎片化程度
4. 检查 last_analyze 时间
5. 观察查询性能是否下降

Q4：并发 REINDEX 有什么注意事项？

A4：并发 REINDEX 的注意事项：

1. 需要足够的磁盘空间（临时存储重建的索引）
2. 会创建一个临时索引，重建完成后替换原索引
3. 不阻塞读操作，但会影响写操作
4. 可能需要更长的时间
5. 不支持重建系统索引

Q5：如何优化自动 VACUUM？

A5：优化自动 VACUUM 的方法：

1. 根据表的大小和更新频率调整参数
2. 为大表设置较低的 scale_factor
3. 增加 autovacuum_max_workers 数量
4. 减少 autovacuum_naptime 间隔
5. 监控自动 VACUUM 的执行情况

Q6：如何清理无用索引？

A6：清理无用索引的步骤：

1. 分析索引的使用情况（pg_stat_user_indexes.idx_scan）
2. 识别长时间未使用的索引
3. 测试删除索引前后的查询性能
4. 使用 DROP INDEX CONCURRENTLY 删除无用索引
5. 定期审查索引使用情况

Q7：索引维护会影响数据库性能吗？

A7：索引维护对数据库性能的影响：

1. 普通 REINDEX 和 VACUUM FULL 会阻塞写操作
2. 并发 REINDEX 和普通 VACUUM 影响较小
3. 维护操作会消耗 CPU、内存和 I/O 资源
4. 建议在业务低峰期执行维护操作
5. 合理配置自动 VACUUM 可以减少手动维护的需求

Q8：如何监控索引维护的进度？

A8：监控索引维护进度的方法：

1. 使用 pg_stat_activity 查看正在执行的维护操作
2. 配置 log_autovacuum_min_duration 记录自动 VACUUM 操作
3. 使用 pg_stat_progress_reindex 查看 REINDEX 进度（PostgreSQL 12+）
4. 监控系统资源使用情况（CPU、I/O）
5. 观察数据库的响应时间变化