MySQL ALTER INDEX INVISIBLE 与删除索引的运维区别

MySQL 里，ALTER INDEX ... INVISIBLE 和删除索引不是同一个级别的动作：前者让优化器暂时不选这个索引，但索引仍然存在并继续维护；后者用 DROP INDEX 把索引结构移除。线上不确定索引是否还有查询依赖时，先隐藏、观察、必要时改回 VISIBLE，比直接删除更容易回滚。

先区分优化器可见性与物理删除

把索引设为不可见，适合回答“没有它时查询计划会怎样”；删除索引，回答的是“以后不再保留这份结构”。不可见索引仍会随表行变更维护，因此不会立刻释放全部索引空间，也仍然可能参与唯一性约束。真正删除后，若发现某条查询回归，只能重新执行建索引，重建时间和资源要重新承担。

-- 先让优化器忽略二级索引，但保留恢复入口
ALTER TABLE orders ALTER INDEX idx_orders_user_created INVISIBLE;

-- 观察后发现查询计划恢复正常，再改回可见
ALTER TABLE orders ALTER INDEX idx_orders_user_created VISIBLE;

-- 只有完成观察和变更审批后，才执行物理删除
DROP INDEX idx_orders_user_created ON orders;

先盘点索引用途与不可隐藏对象

变更前不要只看索引名字。先确认它是否为主键、唯一索引、外键支撑索引，是否被常用查询当作覆盖索引使用。MySQL 官方文档提供了 SHOW INDEX 和 Information Schema 两种查看方式；其中 IS_VISIBLE 可以记录当前状态。

-- 先确认索引名称、唯一性和列顺序，避免操作错对象
SHOW INDEX FROM orders;

-- 把可见性与唯一性保存到变更记录中
SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

主键以及某些承担隐式主键作用的唯一索引不能设为不可见。若索引还承载唯一性约束，隐藏它不会让重复数据突然变得合法；这也是“隐藏用于测试、删除需要单独审批”的关键区别。

先执行 INVISIBLE 灰度并观察执行计划

隐藏前后分别保存同一条代表性查询的 EXPLAIN 结果，重点看 possible_keys、key、访问类型和估算行数。不要只因为某一次查询仍然返回结果，就判断索引可以删除；要覆盖读流量、排序、分页和写入路径，并观察慢查询、锁等待和延迟是否出现回归。

-- 记录隐藏前的计划，查询条件要与线上典型请求一致
EXPLAIN SELECT id, created_at, status
FROM orders
WHERE user_id = 1001 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 隐藏后用相同 SQL 再看 key 与 rows 的变化
ALTER TABLE orders ALTER INDEX idx_orders_user_created INVISIBLE;
EXPLAIN SELECT id, created_at, status
FROM orders
WHERE user_id = 1001 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

如果计划明显退化，或慢查询与锁等待同步上升，直接执行下面的恢复命令，先把业务风险收回来，再分析为什么该索引仍有价值。

-- 恢复优化器对该索引的正常选择资格
ALTER TABLE orders ALTER INDEX idx_orders_user_created VISIBLE;

在恢复窗口内决定 VISIBLE、保留或 DROP

对于 InnoDB 二级索引，删除操作通常可以在线进行，但“表可继续读写”不代表变更没有成本：操作仍受并发事务、锁级别、磁盘空间和重建时间影响。大表应先确认执行算法、锁等待、复制延迟和回滚预案，不能把在线 DDL 理解成零风险。

用变更清单收口删除操作

• 保存索引定义、列顺序、唯一性、可见性，以及隐藏前后的代表性 EXPLAIN。
• 确认没有主键、唯一约束、外键、报表查询或覆盖索引依赖；明确观察期和责任人。
• 先准备 ALTER TABLE ... ALTER INDEX ... VISIBLE 回滚命令，再安排删除窗口。
• 删除后持续看慢查询、错误率、锁等待、复制延迟和磁盘变化；需要恢复时重新建索引，不要重复发送未评估的 DDL。

常见问题

INVISIBLE 会不会释放索引占用的磁盘空间？

不会把它当成释放空间的命令。索引仍存在并继续维护；若目标是删除结构，需要评估并执行 DROP INDEX。

隐藏唯一索引后还能插入重复值吗？

不能。索引可见性影响优化器选择，唯一性约束仍然生效；先确认它不是主键或约束所需对象。

看到一条 EXPLAIN 变慢就应该立刻改回 VISIBLE 吗？

如果代表性流量和线上指标已经回归，应优先恢复可见性；若只是估算变化，则补充不同参数、排序和分页场景后再下结论。