核心内容摘要
探寻四川女性的魅力密码：智慧、坚韧与风情万种
本文首发于「数据库干货铺」公众号转载请联系授权。
在日常的数据库运维中相信不少DBA都遇到过这样的困境某个索引到底有没有用删除会不会影响系统性能留着又怕影响写入速度。
MySQL
0带来的隐藏索引功能正是解决这一痛点的利器。
什么是隐藏索引简单来说隐藏索引Invisible Index就是一种软删除索引的方法。
当你将索引设置为隐藏后查询优化器会忽略它的存在但索引本身并没有被真正删除数据仍然保留在磁盘上。
这意味着你可以临时禁用索引观察系统运行情况如果发现性能问题只需将其重新设置为可见即可无需重建索引。
为什么需要隐藏索引在MySQL
7及更早版本中如果怀疑某个索引效果不佳通常只能直接删除。
但万一删除后发现问题重建索引的成本非常高——对于大表这可能需要数小时甚至更长时间。
而使用隐藏索引切换索引可见性的操作是瞬时的仅修改元数据不涉及数据重建。
这为DBA提供了一个安全试错的机会。
隐藏索引的实际操作创建隐藏索引你可以在建表时直接指定索引为隐藏CREATE TABLE books ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(
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 (
09 sec)恢复索引可见如果需要重新启用索引,则执行如下SQL即可ALTER TABLE books ALTER INDEX idx_name VISIBLE;注 隐藏/显示索引的操作几乎是瞬时的而添加索引即重建索引则需要数分钟。
这充分展示了隐藏索引在效率上的巨大优势。
隐藏索引的应用场景测试是否可以删除已有索引这是隐藏索引最典型的应用场景。
当你不确定删除索引对性能的影响时可以先将索引设置为隐藏然后观察系统性能如果性能无影响则删除索引如果有影响则将索引恢复为可见结合隐藏索引以下是一个安全的索引删除流程--
将目标索引设置为隐藏ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;--
观察一段时间如一周的业务运行情况-- 监控慢查询、系统负载等指标--
如果发现性能问题恢复索引ALTER TABLE table_name ALTER INDEX index_name VISIBLE;--
如果一切正常删除索引 ALTER table table_name DROP INDEX index_name;灰度发布新索引当你创建新索引时可以先将其创建为隐藏索引通过设置optimizer_switchuse_invisible_indexeson在特定会话中测试索引效果确认无误后再将索引设置为可见排查索引问题当怀疑某个索引导致查询性能下降或返回错误结果时可以暂时隐藏该索引进行问题定位。
使用隐藏索引的
注意事项在使用隐藏索引时需要注意以下事项主键不能隐藏无论是显式主键还是隐式主键没有显式主键时NOT NULL列上的唯一索引都不能设置为隐藏。
隐藏索引仍维护即使索引被隐藏MySQL仍然会维护索引数据因此对数据更新操作仍有性能影响。
长期不用的索引应直接删除。
强制索引仍有效如果查询中强制使用隐藏索引FORCE INDEX查询不会报错但优化器会忽略隐藏索引可能导致全表扫描。
事务支持隐藏索引的操作是即时且元数据级别的不会阻塞事务。
6.
总结MySQL
0的隐藏索引功能为数据库管理员提供了一个安全试错的机制大大降低了索引管理的风险。
通过隐藏索引我们可以在不影响业务的前提下测试索引效果避免因误删索引导致的性能问题。
下次当你考虑删除索引时不妨先将其隐藏观察一段时间后再做决定。
这一简单习惯可能会为你避免许多不必要的麻烦。
记住好的DBA不是不犯错而是懂得如何安全地试错。
你在维护索引方面如果更好的技巧或疑问欢迎留言讨论关注微信公众号「数据库干货铺」