如何编写检测清理数据库无效索引

wen 实用脚本 2026-06-25 32

如何高效检测与清理数据库无效索引

目录导读

无效索引的“隐形杀手”本质
检测无效索引的四大核心场景
清理前的风险评估与备份策略
实战清理流程与SQL脚本示例
自动化监控与长期维护方案
常见问题解答（FAQ）

无效索引的“隐形杀手”本质

在数据库运维中，索引是提升查询性能的关键工具，随着业务迭代、数据迁移或表结构变更，系统中可能残留大量“僵尸索引”——即未被使用、重复、冗余或碎片化严重的索引，这些无效索引不仅占用存储空间，还会拖慢INSERT/UPDATE/DELETE操作,甚至引发死锁风险。

如何编写检测清理数据库无效索引

典型无效索引特征：

未被任何查询计划使用的索引（使用率低于0.1%）
高度重复的联合索引（如同时存在(a,b)和(a)索引）
碎片率超过30%的索引（频繁数据修改导致）
被删除表时残留的孤儿索引

根据墨天轮社区案例，某电商平台清理30个无效索引后，批量写入性能提升42%，磁盘IO下降17%。

检测无效索引的四大核心场景

无人使用的索引（Oracle/MySQL/SQL Server）

-- MySQL 查询从未被使用的索引
SELECT 
  object_schema,
  object_name,
  index_name,
  count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0;

重复/冗余索引检测（PostgreSQL示例）

-- 查找表内重复索引组合
SELECT 
  pg_size_pretty(sum(pg_relation_size(indexrelid))) as total_size,
  indrelid::regclass as table_name,
  array_agg(indexrelid::regclass) as duplicate_indexes
FROM pg_index 
GROUP BY indrelid, indkey
HAVING count(*) > 1;

高碎片率索引（SQL Server动态管理视图）

-- 碎片率>30%的索引列表
SELECT 
  OBJECT_NAME(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id 
  AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

跨数据库平台的通用检测逻辑

监控索引统计信息刷新频率（sys.dm_db_index_usage_stats）
分析慢查询日志中索引提示缺失情况
使用pg_stat_user_indexes（PostgreSQL）检查扫描次数

清理前的风险评估与备份策略

风险告知：错误删除索引可能导致OLTP系统瘫痪,尤其是主键索引和唯一索引。

三明治备份方案

数据库全量备份：采用RMAN/mysqldump/pg_dump创建可回滚点

索引DDL脚本导出：

-- MySQL: 导出所有索引创建语句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, 
              ' ADD INDEX ', INDEX_NAME, '(', GROUP_CONCAT(COLUMN_NAME), ');')
FROM INFORMATION_SCHEMA.STATISTICS
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

审核清单：标记可能影响核心查询的索引（如ERP系统的订单查询）

灰度删除流程

先在测试环境禁用索引（ALTER INDEX ... DISABLE）
观察48小时业务无异常告警
执行DROP操作后，保留DROP语句至回滚脚本库

实战清理流程与SQL脚本示例

四阶段清理法

阶段1：收集元数据

-- 创建清理基线表
CREATE TABLE index_cleanup_base AS
SELECT 
  table_schema,
  table_name,
  index_name,
  index_type,
  cardinality,
  (SELECT count(*) FROM information_schema.statistics s2 
   WHERE s2.table_schema = t.table_schema 
     AND s2.table_name = t.table_name 
     AND s2.index_name = t.index_name
  ) as column_count
FROM information_schema.statistics t;

阶段2：业务影响分析

使用数据库审计日志（MySQL General Log/PostgreSQL CSVLOG）
分析pg_stat_statements中索引扫描占比
对比查询计划变化（EXPLAIN ANALYZE）

阶段3：分批执行删除

-- 单次删除5个索引，避免长事务
DROP INDEX IF EXISTS idx_old_orders_date ON orders_table;
DROP INDEX IF EXISTS idx_backup_sku ON product_stock;
-- 每次删除后执行ANALYZE TABLE

阶段4：验证与回滚

-- 验证索引是否成功移除
SELECT * FROM information_schema.statistics 
WHERE table_name = 'orders_table';
-- 若性能异常立即执行预先保存的CREATE INDEX脚本

自动化监控与长期维护方案

智能索引管理工具方案

Percona Toolkit：pt-index-usage 分析慢查询并推荐删除
pg_repack：重建索引同时清理碎片
CloudWatch/SLIS：设置碎片率>40%自动告警

月度巡检脚本框架

#!/bin/bash
# Linux 定时任务：0 2 * * 1 /opt/dba/script/index_health.sh
mysql -e "CALL sp_index_cleanup();" -- 存储过程定期执行
psql -c "SELECT indexratio_check();" >> /var/log/index_maintenance.log

性能基准对比

指标	清理前	清理后	提升
磁盘空间占用	120GB	98GB	-18.3%
批量插入TPS	4500	5900	+31.1%
锁等待频率	22次/分钟	9次/分钟	-59%

常见问题解答（FAQ）

Q1：如何区分必要索引和冗余索引？ A：使用sys.schema_unused_indexes（MySQL 8.0）或pg_stat_user_indexes配合业务查询频率分析，若某索引在过去30天内未出现在EXPLAIN的索引扫描中，且不涉及唯一约束,则可标记为待清理。

Q2：清理索引时突然业务崩溃怎么办？ A：立即执行预置的回滚脚本，若未提前备份，使用二进制日志或WAL日志点恢复，建议在低峰期（如凌晨2-5点）操作,并开启事务日志。

Q3：索引碎片率和未使用率哪个更重要？ A：碎片率>30%时优先REBUILD而非DROP；未使用率>90%且无需业务支持的索引直接删除，两者冲突时，先分析查询模式（如频繁全表扫描的表，碎片率容忍度更高）。

Q4：云数据库（如RDS/Aurora）是否适用？ A：适用，但需注意云平台对performance_schema的权限限制，可通过AWS CloudWatch指标“IndexBlk Read”间接判断，或使用托管工具pg_stat_monitor。

Q5：每天写入量大的表如何安全清理？ A：使用ONLINE DDL（MySQL 5.6+）或pg_repack工具，避免长时间锁表，先禁用索引（ALTER INDEX ... INACTIVE）,观察一周后确认无影响再DROP。

最终建议：建立索引生命周期管理流程，结合AWR报告、慢查询日志和业务变更记录，每季度执行一次全面清理，实际案例表明，生产环境平均可回收10%-25%的无效索引空间，同时带来显著的性能提升——正如墨天轮社区一位DBA所言：“删除一个无用的索引，比创建一个新索引更有价值。”