MySQL 不可见索引怎样临时恢复可见性

想让 MySQL 的不可见索引重新被优化器考虑，不一定要修改整张表。只试一条查询，可使用 SET_VAR 提示；连续比较多条查询，可在专用会话打开 use_invisible_indexes。只有要让业务默认重新考虑该索引，才使用 ALTER TABLE ... ALTER INDEX ... VISIBLE。前两种并没有恢复索引元数据的可见性，第三种才是真正修改索引状态，而且不会自动到期。

官方地址：https://www.mysql.com/

下面以 MySQL 8.4 和 InnoDB 为讨论范围。设业务库 app_demo 中已有 orders 表、二级索引 idx_customer_created(customer_id, created_at)，以及 id 列。库名、字段和数值均为示例，执行前应替换为自己的对象；本文未运行数据库示例，也不预设计划或耗时结果。

先明确临时试用还是恢复业务默认

考虑这样一个场景：团队为了评估一个二级索引是否还需要，把它设成不可见，随后某类订单查询出现疑问。此时有两个不同目标：让诊断连接临时看见更多候选，或者恢复应用连接的默认候选范围。把前者误当后者，会出现“我这里计划变了，业务为什么没变”的困惑。

删除再重建为什么不适合这次切换

如果索引结构仍在，删除再重建就扩大了本次任务。可见性切换的意义正在于保留现有索引，而不是重新创建一份。不可见也不代表停止维护：写入仍要维护索引，唯一性约束也不会因为隐藏而解除。因此，这种方式适合评估查询依赖，不适合模拟“完全没有索引时的写入成本”。

先记录原索引定义和可见状态，避免把原本就可见的索引错误地当作试验对象。复合索引在 STATISTICS 中按列返回多行，这不是多个同名索引。

-- 记录目标索引原状态，复合索引按列序号排列
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app_demo'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_customer_created'
ORDER BY SEQ_IN_INDEX;

这里看 IS_VISIBLE：NO 表示元数据不可见。没有记录则先确认库、表、索引名和权限，不要直接补建一个新索引。

三种开关改变的是不同作用域

表级 VISIBLE 使索引恢复到普通候选范围；会话 use_invisible_indexes=on 则是在该会话允许考虑不可见索引，不只针对 idx_customer_created 一个名字。单语句 SET_VAR 把这种临时配置限制在当前语句，不污染随后语句的会话设置。这三种语义不是“三级强制命中”。

默认 use_invisible_indexes 为 off。是否最终使用某个索引，仍取决于查询条件、其他可用路径和成本估算。这里的“恢复可见性”必须写清作用域，不能只说“开关已经打开”。

用同一条查询对照候选索引

先用同一组条件做普通 EXPLAIN 对照。下面两条都只是估算计划查看，不是 EXPLAIN ANALYZE，也不能代替业务负载下的耗时比较。

-- 基线：保持当前会话的既有优化器设置
EXPLAIN SELECT id, created_at
FROM app_demo.orders
WHERE customer_id = 7301
ORDER BY created_at DESC
LIMIT 20;

-- 对照：只在这一语句中允许考虑不可见索引
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
  id, created_at
FROM app_demo.orders
WHERE customer_id = 7301
ORDER BY created_at DESC
LIMIT 20;

-- 在带提示的语句后立即查看提示是否产生警告
SHOW WARNINGS;

基线必须记录当前会话设置；如果它原本已经为 on，两条查询就不能代表“关闭与打开”的对照。关注 possible_keys 与 key 的区别：前者是可能相关的索引，后者才是本次计划选择；不要仅因为索引出现在候选中，就报告优化成功。SET_VAR 语法错误或不支持的设置可能产生警告而被忽略，因此需要读取同一连接紧接着返回的警告。

需要连续比较多条查询时，可以使用专用连接，保存完整开关值，结束后恢复。不要在被连接池任意切换的连接上分别执行这些语句。

-- 在同一专用连接保存完整原配置，而非假定原值一定是 off
SET @saved_optimizer_switch = @@SESSION.optimizer_switch;
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- 在这里执行自己的计划对照；示例不提供虚构的查询结果
EXPLAIN SELECT id, created_at
FROM app_demo.orders
WHERE customer_id = 7301
ORDER BY created_at DESC
LIMIT 20;

-- 完成或异常退出前恢复；客户端需在清理分支保证执行
SET SESSION optimizer_switch = @saved_optimizer_switch;

最后一行不会因为前面的客户端异常而自动执行。应用代码应使用清理分支恢复；恢复失败时不要把污染连接放回池里。保存完整值也应限定在专用连接，避免覆盖同连接上其他代码有意修改的开关。

上线切换要留意版本和连接状态

若目标是恢复应用的常规访问，把目标索引改为 VISIBLE，而不是只修改运维终端的会话变量。下面是变更写法，不是建议在未知生产库直接执行。

-- 经变更审批后恢复目标二级索引的表级可见状态
ALTER TABLE app_demo.orders
  ALTER INDEX idx_customer_created VISIBLE,
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- 只有原状态为不可见且需要结束试验时，才显式改回
-- ALTER TABLE app_demo.orders
--   ALTER INDEX idx_customer_created INVISIBLE,
--   ALGORITHM=INPLACE,
--   LOCK=NONE;

对 InnoDB 而言，该类变更无需重建索引数据；但在线 DDL 仍有元数据锁要求，不能把 LOCK=NONE 理解成没有任何等待。不支持所请求算法或并发级别时，应处理实际错误，不要机械删除限制让它退化成未经评估的操作。

VISIBLE 没有“十分钟后自动恢复”的含义，也不要指望事务 ROLLBACK 撤销这种 DDL。应保存原状态、执行记录与独立回退命令。主键及承担隐式主键作用的索引不属于可随意隐藏的对象；其他版本或 MySQL 兼容产品应查看各自文档，不直接套用这里的语法。

按目标选择恢复方式

只想知道隐藏索引有没有可能改善某条查询，优先单语句提示；需要多个查询的同条件比较，使用专用会话并恢复设置；已经决定让应用常规考虑该索引，再做表级 VISIBLE 变更。不要为了诊断默认修改全局 optimizer_switch。

打开以后仍不走索引怎么办？

先区分“没有进入候选”与“进入候选但成本不合适”。记录原配置、提示警告、查询参数及计划字段，再分析具体条件，而不是反复切换可见性。

改成不可见能节省索引空间吗？

不能把隐藏当作删除。若目标是回收空间，已是另一个变更任务，应单独评估依赖和回退成本。

临时恢复结束后怎么确认状态？

对会话试验读取会话配置，对表级试验读取 IS_VISIBLE；在业务连接范围内再观察所需计划与负载。本文只给检查方法，不声称已经在读者环境验证成功。

参考：MySQL 不可见索引、优化器开关、优化器提示、InnoDB 在线 DDL。