别急着删索引！MySQL的隐藏索引让你安全试错

本文首发于「数据库干货铺」公众号，转载请联系授权。

在日常的数据库运维中，相信不少DBA都遇到过这样的困境：某个索引到底有没有用？删除会不会影响系统性能？留着又怕影响写入速度。MySQL8.0带来的隐藏索引功能，正是解决这一痛点的利器。

1. 什么是隐藏索引？

简单来说，隐藏索引（Invisible Index）就是一种"软删除"索引的方法。当你将索引设置为隐藏后，查询优化器会忽略它的存在，但索引本身并没有被真正删除，数据仍然保留在磁盘上。

这意味着你可以临时禁用索引，观察系统运行情况，如果发现性能问题，只需将其重新设置为可见即可，无需重建索引。

2. 为什么需要隐藏索引？

在MySQL 5.7及更早版本中，如果怀疑某个索引效果不佳，通常只能直接删除。但万一删除后发现问题，重建索引的成本非常高——对于大表，这可能需要数小时甚至更长时间。而使用隐藏索引，切换索引可见性的操作是瞬时的，仅修改元数据，不涉及数据重建。这为DBA提供了一个安全试错的机会。

3. 隐藏索引的实际操作

• 创建隐藏索引

你可以在建表时直接指定索引为隐藏：

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    INDEX idx_name (name) INVISIBLE
);

也可以将现有索引改为隐藏：

ALTER TABLE books ALTER INDEX idx_name INVISIBLE;

• 查看索引状态

通过以下命令可以查看索引的可见性：

mysql> SELECT INDEX_NAME, IS_VISIBLE
    -> FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'books';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| idx_name   | NO         |
| PRIMARY    | YES        |
+------------+------------+
2 rows in set (0.09 sec)

• 恢复索引可见

如果需要重新启用索引,则执行如下SQL即可：

ALTER TABLE books ALTER INDEX idx_name VISIBLE;

注： 隐藏/显示索引的操作几乎是瞬时的，而添加索引（即重建索引）则需要数分钟。这充分展示了隐藏索引在效率上的巨大优势。

4. 隐藏索引的应用场景

• 测试是否可以删除已有索引

这是隐藏索引最典型的应用场景。当你不确定删除索引对性能的影响时，可以先将索引设置为隐藏，然后观察系统性能；如果性能无影响，则删除索引；如果有影响，则将索引恢复为可见

结合隐藏索引，以下是一个安全的索引删除流程：

-- 1. 将目标索引设置为隐藏
ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;
-- 2. 观察一段时间（如一周）的业务运行情况
-- 监控慢查询、系统负载等指标
-- 3. 如果发现性能问题，恢复索引
ALTER TABLE table_name ALTER INDEX index_name VISIBLE;
-- 4. 如果一切正常，删除索引
 ALTER table  table_name DROP INDEX index_name;

• 灰度发布新索引

当你创建新索引时，可以先将其创建为隐藏索引，通过设置optimizer_switch='use_invisible_indexes=on'在特定会话中测试索引效果；确认无误后，再将索引设置为可见

• 排查索引问题

当怀疑某个索引导致查询性能下降或返回错误结果时，可以暂时隐藏该索引进行问题定位。

5. 使用隐藏索引的注意事项

在使用隐藏索引时需要注意以下事项：

• 主键不能隐藏：无论是显式主键还是隐式主键（没有显式主键时，NOT NULL列上的唯一索引），都不能设置为隐藏。
• 隐藏索引仍维护：即使索引被隐藏，MySQL仍然会维护索引数据，因此对数据更新操作仍有性能影响。长期不用的索引应直接删除。
• 强制索引仍有效：如果查询中强制使用隐藏索引（FORCE INDEX），查询不会报错，但优化器会忽略隐藏索引，可能导致全表扫描。
• 事务支持：隐藏索引的操作是即时且元数据级别的，不会阻塞事务。

6. 总结

MySQL8.0的隐藏索引功能为数据库管理员提供了一个安全试错的机制，大大降低了索引管理的风险。通过隐藏索引，我们可以在不影响业务的前提下测试索引效果，避免因误删索引导致的性能问题。

下次当你考虑删除索引时，不妨先将其隐藏，观察一段时间后再做决定。这一简单习惯，可能会为你避免许多不必要的麻烦。

记住：好的DBA不是不犯错，而是懂得如何安全地试错。

你在维护索引方面如果更好的技巧或疑问，欢迎留言讨论！

关注微信公众号「数据库干货铺」，获取更多数据库运维干货。