从sys.schema_unused_indexes到INVISIBLE:安全删除冗余索引的完整流程

举报
这个DBA有点耶 发表于 2026/11/10 15:22:10 2026/11/10
【摘要】 索引膨胀是写入性能的隐性杀手,但直接删除索引的风险同样很高——季度报表可能依赖它,优化器的执行计划可能因此改变。MySQL 8.0的不可见索引提供了一种“灰度删除”的中间态:索引仍然维护,但优化器默认忽略它。本文拆解不可见索引的生效机制、三个容易被误用的边界(主键与隐式主键、FORCE INDEX、唯一约束),并给出基于sys.schema_unused_indexes的灰度删索引实操流程。

大家好,我是小耶,写功课只是为了我踩过的坑,你们别再踩了!

一条冗余索引,写入TPS掉了20%。想删,但不敢。

这个索引可能被季度报表依赖,可能被某个低频但关键的业务查询使用,也可能被优化器在某些数据分布下选中。直接删,风险不可控;不删,写入性能持续受损。

MySQL 8.0的不可见索引(Invisible Indexes) 提供了一种中间态:索引仍然存在、仍然维护、唯一约束仍然生效,但优化器在生成执行计划时默认忽略它。你可以观察一段时间,确认没有性能回退后,再真正删除。

这个机制听起来简单,但有三个边界必须搞清楚,否则“不可见”可能变成“不可控”。

一、不可见索引的生效机制

语法:

-- 创建时直接设为不可见
CREATE INDEX idx_name ON t (col) INVISIBLE;

-- 将已有索引改为不可见
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;

-- 恢复可见
ALTER TABLE t ALTER INDEX idx_name VISIBLE;

核心机制:不可见索引不是“禁用”索引。它仍然被InnoDB正常维护,INSERT/UPDATE/DELETE仍然会更新这个索引的B+树。唯一的区别是,优化器在评估执行计划时,默认不将其纳入候选。

这与MyISAM的“disabled index”有本质区别:disabled index会停止索引维护,不可见索引不会。

二、边界一:主键和“隐式主键”不可设为不可见

这是第一个容易被忽略的限制。

显式主键不能设为不可见,这是明确的:

ALTER TABLE t ALTER INDEX PRIMARY INVISIBLE;
-- ERROR 3522 (HY000): A primary key index cannot be invisible

更隐蔽的是隐式主键。如果一个表没有显式定义PRIMARY KEY,但存在一个NOT NULL列上的UNIQUE索引,InnoDB会将这个唯一索引提升为聚簇索引(clustering index),承担主键的角色。这种情况下,这个唯一索引同样不能被设为不可见。

判断方法:如果ALTER INDEX ... INVISIBLE报ERROR 3522,说明这个索引当前承担着主键的职责。需要先添加一个显式主键,才能将原唯一索引设为不可见。

三、边界二:FORCE INDEX 也救不了不可见索引

很多人的直觉是:优化器默认忽略,但我用FORCE INDEX强制指定,总该能用了吧?

不行。

MySQL官方文档明确说明:不可见索引不会被任何查询使用,即使查询使用了FORCE INDEX提示。Percona的测试也验证了这一点:对一个不可见索引使用FORCE INDEX,MySQL会执行全表扫描,而不是走索引。

这意味着:不可见索引的“不可见”是对优化器的硬屏蔽,不是“优先级降低”。如果你需要临时验证某个不可见索引是否仍然有效,唯一的方法是先将其改为VISIBLE。

一个例外:通过设置optimizer_switch='use_invisible_indexes=ON',可以让优化器在会话或全局级别看到不可见索引。但这是全局/会话级别的开关,不是通过Hint控制的。

四、边界三:不可见 ≠ 约束失效

这是最容易产生误解的边界。

唯一索引被设为不可见后,唯一性约束仍然生效。

也就是说,即使优化器不把这个索引用于查询计划,InnoDB在写入时仍然会检查这个索引的唯一性。尝试插入重复值会报Duplicate entry错误。

同理,外键约束相关的索引如果被设为不可见,外键约束的检查逻辑不受影响。不可见只影响“优化器是否用它来加速查询”,不影响“数据库是否用它来保证数据完整性”。

这个边界的实际意义:你可以放心地把一个唯一索引设为不可见来做灰度测试,不用担心唯一约束被绕过。但如果你的目的是“暂时禁用某个约束”,不可见索引做不到,必须DROP INDEX。

五、灰度删索引的实操流程

不可见索引的核心用途是安全地删除冗余索引。推荐流程:

第一步:识别候选索引

SELECT * FROM sys.schema_unused_indexes;

这个视图列出自上次服务器启动以来未被任何查询使用过的索引。注意:服务器刚重启时这个视图的数据不可靠,需要观察足够长的时间窗口(至少覆盖一个完整的业务周期,比如一个月)。

第二步:将候选索引设为不可见

ALTER TABLE t ALTER INDEX idx_candidate INVISIBLE;

注意:这个操作需要获取MDL锁。如果有长事务持有该表的MDL共享锁,ALTER INDEX会被阻塞,进而阻塞后续的DML操作。建议在业务低峰期执行,并先检查information_schema.innodb_trx中的长事务。

第三步:观察期

设为不可见后,监控以下指标:

  • 关键查询的响应时间是否回退

  • sys.schema_unused_indexes是否仍然显示该索引未被使用

  • 慢查询日志中是否有新出现的、依赖该索引的查询

第四步:确认后删除

观察期(建议至少两周,覆盖一个完整的报表周期)无异常后:

DROP INDEX idx_candidate ON t;

如果观察期内发现问题:立即ALTER INDEX ... VISIBLE恢复。这是一个元数据变更,瞬间完成,不需要重建索引。

六、另一个用途:索引的“灰度上线”

不可见索引还可以反向使用:先创建为不可见,观察优化器在“没有这个索引”时的表现,然后改为可见,对比性能变化

-- 先创建为不可见(对优化器不可见,但索引结构已建立)
ALTER TABLE t ADD INDEX idx_new (col) INVISIBLE;

-- 观察一段时间后,改为可见,对比执行计划和性能
ALTER TABLE t ALTER INDEX idx_new VISIBLE;

这种方式适合在不确定新索引是否会被优化器选中的场景下使用,避免“建了索引但优化器不用”的尴尬。

七、小结

不可见索引是MySQL 8.0中一个实用但边界清晰的工具。它提供的是灰度删除的能力:索引仍然维护、约束仍然生效,但优化器默认忽略。三个边界必须记住:主键和隐式主键不能设为不可见;FORCE INDEX救不了不可见索引;唯一约束和不可见状态是两件事。

使用流程很简单:sys.schema_unused_indexes找候选 → INVISIBLE灰度 → 观察一个完整业务周期 → DROP。中途发现问题,VISIBLE瞬间恢复。这个中间态的价值不在于“更高级的索引管理”,而在于把删索引这件事从“不可逆决策”变成“可回退的实验”。

小耶在手,SQL 不愁

还有什么想了解的,欢迎留言!小耶一定知无不言言无不尽……我们下次见~

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。