MySQL invisible index 试运行与优化器开关

MySQL 的 invisible index 适合做索引下线前的低风险试运行：把二级索引设为不可见后，普通会话中的优化器默认忽略它，但索引本身仍然维护，唯一索引也仍然阻止重复值。需要验证候选索引时，不必重新建索引，只在实验会话开启 use_invisible_indexes。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先把索引变成可回退的实验开关

假设订单表上有一个候选索引 idx_orders_customer_created。第一步不是直接删除，而是先记录当前定义和可见性。这样即使实验结论不理想，也可以用一条语句恢复。

-- 先确认目标索引和当前可见性，避免改错表或改错索引
SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_orders_customer_created';

-- 让普通会话暂时看不到该二级索引
ALTER TABLE shop.orders
  ALTER INDEX idx_orders_customer_created INVISIBLE;

-- 需要回滚时恢复可见性，不需要重建索引
ALTER TABLE shop.orders
  ALTER INDEX idx_orders_customer_created VISIBLE;

切换后再次查询 IS_VISIBLE 应为 NO。主键不能设为 invisible；某些没有显式主键的 InnoDB 表中，承担隐式主键作用的非空唯一索引也可能不能隐藏。这个限制要先写进变更单。

用 SESSION 或 SET_VAR 控制试运行范围

隐藏索引后，默认会话不会把它放进执行计划。要验证“如果保留该索引，优化器是否会选它”，建议先在隔离连接里设置会话级开关，而不是直接改全局值。开关只改变选计划，不会让索引在元数据中重新显示。

-- 只为当前实验连接启用 invisible index，离开连接后不影响新会话
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- 在同一连接中确认开关状态
SELECT @@session.optimizer_switch;

-- 只让这一条 EXPLAIN 临时看到隐藏索引，适合对照实验
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
  order_id, customer_id, created_at
FROM shop.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

如果只是做一次对照，优先使用 SET_VAR，避免后续同一连接中的其他查询带上实验条件。若需要连续跑一组基准，再使用 SET SESSION，并在实验结束时显式恢复或关闭该标记。

用执行计划和约束确认结果

对照时至少保留两份 EXPLAIN：普通会话的一份，以及打开 use_invisible_indexes 后的一份。重点记录 possible_keys、key、预估 rows 和 Extra。隐藏索引在普通计划里不出现，并不自动证明它没有价值；还要观察真实工作负载的慢查询、延迟和写入成本。

如果隐藏后查询计划变差、慢查询增多或出现依赖该索引提示的错误，就先恢复 VISIBLE，再分析是否应保留索引。若只是实验会话能选到它，不代表生产流量一定受益；统计信息、数据分布和排序条件仍会影响最终计划。

常见问题

invisible index 是不是等同于 DROP INDEX？

不是。索引仍占用空间、参与写入维护，并继续承担唯一约束；它只是默认不参与优化器选计划。

为什么打开开关后执行计划还是不用它？

use_invisible_indexes=on 只是把索引放回候选集合，优化器仍会按成本选择其他路径。应同时检查列顺序、统计信息、过滤条件和排序需求。

可以直接全局开启吗？

技术上可以设置全局值，但这会影响新建连接。索引灰度通常先用独立会话或 SET_VAR，确认收益后再决定是否改变全局策略。