MySQL诊断系列（3/6）：索引分析——5个SQL揪出僵尸索引

你有没有想过：

• 为什么你的表 ALTER TABLE 越来越慢？
• 磁盘空间莫名增长，却找不到原因？
• INSERT/UPDATE 性能下降，怀疑是索引拖累？

很可能，你的数据库里藏着一批“僵尸索引”——它们被创建后，从未被查询使用过，却一直在消耗磁盘空间、拖慢写操作。

今天，我就用5个SQL，带你把这些“寄生虫”一个个揪出来！

---

什么是“僵尸索引”？

在MySQL中，每创建一个索引，InnoDB就会生成对应的B+树结构，并维护它。这意味着：

• 写放大：每次 INSERT/UPDATE/DELETE，不仅要改数据页，还要改所有相关索引页。
• 空间占用：一个索引可能占用几MB甚至几GB空间。
• 维护成本：OPTIMIZE TABLE、ALTER TABLE 时间变长。

如果一个索引从来没人用它来查询，那它就是纯粹的“负资产”。

---

5个SQL，全面扫描索引健康状况

1️⃣ 查找“零使用”索引 —— 最典型的僵尸

 复制代码
SELECT
    object_nameAS table_name,
    index_name,
    count_starAS usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
AND index_name IS NOT NULL AND count_star = 0
AND sum_timer_wait = 0;

---

2️⃣ 找出“最忙”索引 —— 真正的功臣

 复制代码
SELECT
    object_nameAS table_name,
    index_name,
    (sum_timer_wait / 1000000000000)AS total_wait_sec
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
AND index_name IS NOT NULL ORDER BY sum_timer_waitDESC
LIMIT 10;

---

3️⃣ 识别“全表扫描”重灾区 —— 缺少索引的表

 复制代码
SELECT
    object_nameAS table_name,
    (sum_timer_wait / 1000000000000)AS full_scan_wait_sec
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
AND index_nameISNULL  -- 没有使用索引（即全表扫描）
AND sum_timer_wait > 30000000000000  -- 等待时间 > 30秒
ORDER BY sum_timer_wait DESC
LIMIT 5;

---

4️⃣ 查看索引大小 —— 空间占用大户

 复制代码
SELECT
    t.table_name,
    s.index_name,
    ROUND(s.stat_value * @@innodb_page_size / 1024 / 1024, 2)AS index_size_mb
FROM mysql.innodb_index_stats s
JOIN information_schema.tables t
ON s.table_name = t.table_name AND s.database_name = t.table_schema
WHERE s.database_name = 'your_database'
AND s.stat_name = 'size'
AND s.index_name IS NOT NULL ORDER BY s.stat_value DESC;

---

5️⃣ 检查重复/冗余索引 —— “兄弟索引”只需留一个

虽然没有直接SQL，但可以通过分析 SHOW CREATE TABLE 来识别：

 复制代码
SHOW CREATE TABLE your_table;

常见冗余模式：

• KEY idx_a (a), KEY idx_a_b (a,b) → idx_a 是冗余的
• KEY idx_b_a (b,a), KEY idx_a_b (a,b) → 顺序不同，但可能有重叠作用

---

删除索引的正确姿势

1. 先观察：确认该索引至少7天内未被使用。

2. 备份：CREATE INDEX 语句记下来，万一误删可快速恢复。

3. 低峰期操作：使用 DROP INDEX：

    复制代码
   ALTER TABLE your_table DROP INDEX idx_name;

4. 监控影响：删除后观察写性能是否有提升。

---

总结

索引不是越多越好，合理的索引才是好索引。

定期执行这5个SQL，可以：

• 发现并删除“僵尸索引”，释放空间
• 识别“全表扫描”表，补充缺失索引
• 降低写操作延迟，提升整体性能

点赞 + 关注，解锁更多数据库秘籍！

你的MySQL“瘦身计划”，从今天开始！

PS: 看完是不是觉得要记下好多的SQL，排查步骤又繁琐，不要担心，在 AI 的时代，让大模型来替我们排查分析数据库问题，推荐一款开源好用的MCP Server 工具：SmartDB_MCP ，它不仅能让AI与多种数据库“畅聊无阻”，还能像瑞士军刀一样，提供从SQL优化到数据库健康检测分析的一站式解决方案。 github地址 ： github.com/wenb1n-dev/… 博文地址：SmartDB：AI与数据库的“翻译官”，开启无缝交互新时代！