MySQL 不可见索引如何安全评估替代方案

我处理“这个索引是不是可以删掉”时，通常不会先执行 DROP INDEX。MySQL 的不可见索引更适合做一次低风险试验：把候选索引设为 INVISIBLE，让优化器暂时不选它，再观察代表性查询的执行计划和真实负载。如果代价明显上升就恢复可见；如果长期没有影响，才继续评估替代索引或删除。

先确认候选索引真的可以被隐藏

不可见索引适用于普通二级索引，也适用于显式创建的非主键索引。主键不能设为不可见；某些没有显式主键的 InnoDB 表，会把 UNIQUE NOT NULL 索引当作隐式主键，这种索引同样不能隐藏。因此第一步不是改 DDL，而是核对索引名、唯一性和可见状态。

-- 先查看索引名、唯一性和可见状态，避免把列名误当索引名。
SHOW INDEX FROM shop_order;

-- 也可以只查看目标表的索引状态。
SELECT INDEX_NAME, NON_UNIQUE, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'shop_order';

同时选出两三条真实代表性查询，保存隐藏前的 EXPLAIN 结果和大致响应指标。不要只挑一条“最容易命中索引”的 SQL；订单列表、按用户查详情、后台筛选等不同访问路径，可能依赖不同的索引前缀。

用不可见状态做可回滚的替代评估

确认候选后，可以切换索引可见性。这个动作比删除并重建索引轻量，恢复时也只需改回 VISIBLE。但“轻量”不代表没有影响：索引仍会随写入更新，磁盘和写入成本不会因为不可见而消失。

-- 只改变优化器可见性，不删除索引结构。
ALTER TABLE shop_order
  ALTER INDEX idx_order_user_status INVISIBLE;

-- 完成观察后，需要立即恢复服务依赖的索引时执行。
ALTER TABLE shop_order
  ALTER INDEX idx_order_user_status VISIBLE;

隐藏后重点看三类信号：EXPLAIN 是否从索引访问退化为全表扫描或更高代价的路径；原本不慢的查询是否进入慢查询日志；Performance Schema 中受影响语句的耗时、扫描行数或调用量是否上升。遇到索引提示直接报错，也说明代码把这个索引名写死了。

不要把一次 EXPLAIN 当成最终结论

不可见测试回答的是“优化器不考虑它会怎样”，不是“它永远没有价值”。数据分布、参数、排序条件和连接顺序都会改变计划。建议把候选查询按读写场景分组，在接近生产的数据量和参数范围下比较，并记录隐藏前后扫描行数、访问类型、排序/临时表提示和延迟变化。

还有一个容易误判的点：不可见不等于失去唯一性。唯一索引即使不可见，插入重复值仍会被拒绝；因此不要把“隐藏成功”理解成约束已经解除。若目标是改约束，应该单独设计迁移和数据检查。

用单语句对照索引价值，再决定方案

当索引已不可见，但你想知道某条查询如果重新考虑它会不会更好，可以只对该语句打开 use_invisible_indexes。这样能把“候选索引本身的价值”和“隐藏后优化器选择的替代路径”分开观察。

-- 只为这次 EXPLAIN 临时考虑不可见索引，不改变全局配置。
EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
       order_id, user_id, status
FROM shop_order
WHERE user_id = 10086 AND status = 'paid';

如果打开该提示后计划重新选择候选索引，说明它对这条查询仍有价值；如果两种计划都不理想，替代方案可能需要重新设计列顺序、覆盖列或查询条件，而不是简单恢复旧索引。最终决策可以按“保留、替换、删除”三档记录：保留表示仍承担稳定路径，替换表示已有更好的复合索引方案，删除则要先确认没有提示、约束和低频任务依赖。

相关问题

不可见索引会减少写入开销吗？

不会。索引仍会随着表数据变化而维护，因此不可见主要用于测试优化器选择，不是立即降低写放大的开关。

主键能设为 INVISIBLE 吗？

不能。显式主键以及承担隐式主键作用的唯一非空索引，都不能设为不可见。

什么时候可以直接 DROP INDEX？

至少要先完成不可见观察，确认代表性查询、索引提示、唯一约束和低频作业都没有依赖，并准备好恢复或重建方案。