MySQL索引分析：5个SQL揪出僵尸索引

先问自己几个问题：

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

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

今天要介绍的5个SQL，就是把这些“寄生虫”一个个揪出来的利器。

---

什么是僵尸索引？MySQL索引的隐性成本

在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;

这个SQL直接查 performance_schema，找出那些计数为0、等待时间为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;

与僵尸相对，真正的功臣是那些被频繁使用的索引。这个SQL按等待时间倒序排列，帮你找出最繁忙的TOP10。这些索引是查询优化的核心，千万不能动。

---

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;

全表扫描是性能杀手。这个SQL筛选出那些在查询中没有使用索引且等待时间超过30秒的表。结果里的表，就是你需要紧急补充索引的地方。

---

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;

光看使用情况还不够，还得看空间占用。这个SQL结合 mysql.innodb_index_stats 和 information_schema，帮你算出每个索引的大小（MB）。如果某个索引占用空间巨大但使用率很低，那就是典型的“高成本低产出”，建议优先清理。

---

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) → 顺序不同，但可能有重叠作用

这种冗余索引就像“兄弟”一样，功能重叠，留一个就好。手动检查 SHOW CREATE TABLE 的输出，结合你对查询的了解，就能识别出这些“多余”的索引。

---

删除索引的正确姿势

1. 先观察：确认该索引至少7天内未被使用。可以用上面的SQL1定期跑一下，确保数据稳定。

2. 备份：CREATE INDEX 语句记下来，万一误删可快速恢复。别嫌麻烦，这一步是保险。

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

    复制代码
   ALTER TABLE your_table DROP INDEX idx_name;

4. 监控影响：删除后观察写性能是否有提升。注意，DROP INDEX 本身会短暂锁表，务必在业务低峰期执行。

---

总结：MySQL索引瘦身计划

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

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

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

你的MySQL“瘦身计划”，从今天开始。定期排查，才是真正的“瘦身计划”。