文章目录一、为什么需要清理无用索引1. 资源浪费2. 性能损耗3. 索引膨胀Index Bloat二、第一步识别从未使用的索引1. 启用索引统计前提2. 查询未使用索引3. 排除约束类索引三、第二步评估“低效”索引非零但极少使用1. 计算索引使用效率2. 结合查询日志分析四、第三步检测索引膨胀Bloat1. 使用社区脚本估算膨胀率方法一使用 pgstattuple 扩展精确但锁表方法二使用轻量级估算脚本推荐五、第四步安全删除无用索引1. 删除前 checklist2. 执行删除3. 验证删除效果六、第五步重建膨胀索引而非删除1. 重建命令2. 自动化重建策略七、建立自动化监控与治理机制1. 创建监控视图2. 设置告警3. 定期巡检脚本Shell psql八、最佳实践总结开发规范建议在 PostgreSQL 的长期运行中索引数量往往会随着业务迭代不断增长。然而并非所有索引都被有效利用——有些是历史遗留、有些因查询模式变更而失效、有些甚至从未被使用过。这些“僵尸索引”不仅浪费宝贵的磁盘空间还会拖慢 DML 操作INSERT/UPDATE/DELETE因为每次数据变更都需同步更新所有相关索引。更严重的是PostgreSQL 的 MVCC 机制会导致索引膨胀Index Bloat——当表数据频繁更新或删除时索引中的 dead tuples 无法及时回收使得索引物理大小远超实际需求进一步加剧 I/O 压力与查询延迟。本文将系统性地阐述如何识别、评估、安全删除无用索引并建立自动化监控与治理机制帮助你持续优化数据库存储效率与性能。一、为什么需要清理无用索引1. 资源浪费磁盘空间每个索引都是独立的物理结构占用与表相当甚至更多的空间内存缓存索引页会进入 shared_buffers 和 OS cache挤占热数据空间WAL 日志索引变更产生额外 WAL增加主从复制负担。2. 性能损耗写放大每新增一个索引INSERT/UPDATE/DELETE需多一次索引维护优化器负担过多索引使查询计划选择更复杂可能选错执行路径VACUUM 压力需扫描更多索引清理 dead tuples。3. 索引膨胀Index Bloat当表发生UPDATE或DELETE时旧索引项不会立即删除而是标记为 dead若autovacuum不及时dead tuples 积累索引体积膨胀膨胀后的索引导致更多随机 I/O更低的缓存命中率更长的查询时间。 经验数据生产环境中10%~30% 的索引可能从未被使用。二、第一步识别从未使用的索引PostgreSQL 提供了索引使用统计信息可精准定位“零访问”索引。1. 启用索引统计前提确保postgresql.conf中已开启track_counts on # 默认开启 track_io_timing off # 可选无需重启但统计信息从开启后才开始累积。2. 查询未使用索引SELECTschemanameASschema,tablenameAStable,indexnameASindex,pg_size_pretty(pg_relation_size(quote_ident(schemaname)||.||quote_ident(indexname)))ASindex_size,idx_tup_read,-- 索引扫描返回的行数Index Scanidx_tup_fetch-- 通过索引获取的堆元组数Index Only Scan Bitmap Heap ScanFROMpg_stat_user_indexesWHEREidx_tup_read0ANDidx_tup_fetch0ORDERBYpg_relation_size(quote_ident(schemaname)||.||quote_ident(indexname))DESC;关键条件idx_tup_read 0 AND idx_tup_fetch 0注意该统计基于自上次统计重置以来的数据排除对象主键PRIMARY KEY和唯一约束UNIQUE索引即使未用于查询也用于约束校验外键引用的索引用于级联操作加速。3. 排除约束类索引-- 获取所有约束索引WITHconstraint_indexesAS(SELECTconindidASindex_oidFROMpg_constraintWHEREcontypeIN(p,u,f)-- primary, unique, foreign key)SELECTn.nspnameASschema,c.relnameAStable,i.relnameASindex,pg_size_pretty(pg_relation_size(i.oid))ASsizeFROMpg_index idxJOINpg_class iONi.oididx.indexrelidJOINpg_class cONc.oididx.indrelidJOINpg_namespace nONn.oidc.relnamespaceLEFTJOINconstraint_indexes ciONci.index_oidi.oidWHEREci.index_oidISNULL-- 非约束索引AND(SELECTidx_tup_readidx_tup_fetchFROMpg_stat_user_indexesWHEREindexrelidi.oid)0ORDERBYpg_relation_size(i.oid)DESC;✅ 此结果集中的索引可安全评估删除。三、第二步评估“低效”索引非零但极少使用有些索引虽有少量访问但收益远低于维护成本。1. 计算索引使用效率SELECTschemaname,tablename,indexname,pg_size_pretty(pg_relation_size(indexrelid))ASindex_size,idx_tup_read,idx_tup_fetch,idx_scan,-- 索引被扫描次数ROUND((idx_tup_readidx_tup_fetch)*100.0/NULLIF(idx_scan,0),2)ASavg_rows_per_scanFROMpg_stat_user_indexesWHEREidx_scan0ORDERBYpg_relation_size(indexrelid)DESC;低效特征idx_scan很高但avg_rows_per_scan极低如 10→ 可能走错索引idx_scan极低如 10 次/天但索引很大1GB→ 收益不足。2. 结合查询日志分析使用pg_stat_statements查看哪些查询实际使用了该索引-- 需先启用 pg_stat_statementsCREATEEXTENSIONIFNOTEXISTSpg_stat_statements;SELECTquery,calls,total_exec_time,rowsFROMpg_stat_statementsWHEREqueryLIKE%INDEX_NAME%-- 替换为实际索引名需解析执行计划ORDERBYtotal_exec_timeDESC; 更准确方式通过EXPLAIN (ANALYZE, BUFFERS)确认执行计划是否使用目标索引。四、第三步检测索引膨胀Bloat即使索引被使用也可能因膨胀而效率低下。1. 使用社区脚本估算膨胀率PostgreSQL 官方未提供内置膨胀视图但社区有成熟脚本。方法一使用pgstattuple扩展精确但锁表-- 安装扩展CREATEEXTENSION pgstattuple;-- 分析单个索引会加 AccessShareLock短暂阻塞SELECT*FROMpgstatindex(your_index_name);输出关键字段avg_leaf_density叶子页填充率理想 80%leaf_pages实际叶子页数estimated_pages理论所需页数膨胀率 ≈ (leaf_pages - estimated_pages) / leaf_pages。⚠️ 缺点对大索引分析耗时长不适用于高频监控。方法二使用轻量级估算脚本推荐以下 SQL 基于统计信息估算膨胀无锁、快速-- 来源https://github.com/ioguix/pgsql-bloat-estimationWITHbtree_index_attsAS(SELECTnspname,relname,reltuples,relpages,indrelid,relam,regexp_split_to_table(indkey::text, )::smallintASattnum,indexrelidASindex_oidFROMpg_indexJOINpg_classONpg_class.oidpg_index.indexrelidJOINpg_namespaceONpg_namespace.oidpg_class.relnamespaceJOINpg_amONpg_class.relampg_am.oidWHEREpg_am.amnamebtree),index_item_sizesAS(SELECTi.nspname,i.relname,i.reltuples,i.relpages,i.indrelid,i.relam,i.index_oid,SUM(COALESCE(s.stawidth,0))8ASitem_size-- 8 bytes for ctidFROMbtree_index_atts iLEFTJOINpg_stats sONs.schemanamei.nspnameANDs.tablenamei.relnameANDs.attnamepg_get_indexdef(i.indrelid,i.attnum,true)GROUPBYi.nspname,i.relname,i.reltuples,i.relpages,i.indrelid,i.relam,i.index_oid),index_alignedAS(SELECTnspname,relname,reltuples,relpages,indrelid,relam,index_oid,CEIL(reltuples*item_size/(current_setting(block_size)::float-24))ASaligned_est_pagesFROMindex_item_sizes)SELECTnspnameASschema,relnameASindex,indrelid::regclassAStable,relpagesASactual_pages,aligned_est_pagesASestimated_pages,ROUND(100.0*(relpages-aligned_est_pages)/relpages,1)ASbloat_pct,pg_size_pretty((relpages-aligned_est_pages)*current_setting(block_size)::int)ASwasted_spaceFROMindex_alignedWHERErelpages100-- 忽略小索引ANDaligned_est_pages0AND(relpages-aligned_est_pages)100-- 浪费 100 pagesORDERBY(relpages-aligned_est_pages)DESC;bloat_pct 30%且wasted_space 100MB的索引建议重建。五、第四步安全删除无用索引1. 删除前 checklist确认非主键/唯一/外键约束索引确认idx_tup_read 0 AND idx_tup_fetch 0确认近期如 7 天无相关查询结合应用日志在低峰期操作备份 DDL以防误删。2. 执行删除-- 标准删除会锁表阻塞 DMLDROPINDEXCONCURRENTLY your_schema.your_index_name;✅务必使用CONCURRENTLY普通DROP INDEX会加ACCESS EXCLUSIVE锁阻塞所有读写CONCURRENTLY仅加SHARE UPDATE EXCLUSIVE锁允许并发 DML。3. 验证删除效果-- 确认索引消失SELECT*FROMpg_indexesWHEREindexnameyour_index_name;-- 观察磁盘空间释放可能需 VACUUMdu-sh $PGDATA/base/... 注意DROP INDEX立即释放磁盘空间与DELETE不同。六、第五步重建膨胀索引而非删除对于有用但膨胀的索引应重建而非删除。1. 重建命令-- 在线重建PostgreSQL 12REINDEXINDEXCONCURRENTLY your_index_name;-- 旧版本需手动创建切换CREATEINDEXCONCURRENTLY new_indexONtable(col);DROPINDEXCONCURRENTLY old_index;ALTERINDEXnew_indexRENAMETOold_index;2. 自动化重建策略对bloat_pct 50%且wasted_space 1GB的索引自动重建使用 cron 或 pg_cron 定期执行。七、建立自动化监控与治理机制1. 创建监控视图-- 无用索引视图CREATEMATERIALIZEDVIEWunused_indexesASSELECT...-- 上述查询逻辑REFRESH MATERIALIZEDVIEWunused_indexes;-- 每日刷新2. 设置告警Prometheus exporter 采集pg_stat_user_indexes告警规则-alert:UnusedLargeIndexexpr:pg_stat_user_indexes_idx_tup_read 0 and pg_stat_user_indexes_size_bytes1e9for:7dlabels:severity:warning3. 定期巡检脚本Shell psql#!/bin/bash# weekly_index_cleanup.shpsql -Atq -c SELECT DROP INDEX CONCURRENTLY || schemaname || . || indexname || ; FROM pg_stat_user_indexes WHERE idx_tup_read 0 AND idx_tup_fetch 0 AND pg_relation_size(indexrelid) 100*1024*1024 -- 100MB AND indexrelid NOT IN (SELECT conindid FROM pg_constraint); |psql -e⚠️ 脚本需人工审核后再执行八、最佳实践总结场景操作从未使用的非约束索引DROP INDEX CONCURRENTLY低效大索引极少使用评估业务后删除有用但膨胀的索引REINDEX INDEX CONCURRENTLY主键/唯一/外键索引禁止删除即使未用于查询新上线索引监控 7–30 天使用情况无效则清理开发规范建议索引上线需评审提供 EXPLAIN 计划证明必要性命名规范如idx_tab_col1_col2便于识别定期清理每季度执行一次索引健康检查。结语索引是性能的双刃剑——用得好查询飞快用不好拖垮系统。通过本文所述方法你可系统性地识别僵尸索引释放磁盘与内存检测索引膨胀保持高效结构安全删除/重建避免服务中断建立自动化机制实现持续优化。记住最好的索引是刚好够用的索引。定期清理无用索引不仅是节省成本更是对数据库健康负责。