MySQL optimizer_switch 控制 use_invisible_indexes 的测试方案

给一个二级索引做下线评估时，直接 DROP INDEX 往往太激进。MySQL 的不可见索引可以先保留索引维护和唯一性语义，再用 optimizer_switch 的 use_invisible_indexes 做一组可重复的计划对比。最稳妥的做法是只改当前会话：先确认索引确实为 INVISIBLE，再分别用 off 和 on 执行 EXPLAIN，最后恢复原设置。

一、先固定测试对象与索引可见性

先把实验缩小到一张表、一个不可见二级索引和一条稳定查询。下面的示例只用于搭建测试对象，生产表请在影子库或低风险副本中操作：

-- 建立包含状态过滤列的二级索引，并先将它设为不可见
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_customer_status (customer_id, status) INVISIBLE
);

-- 查询索引名和可见性，避免测错对象
SELECT INDEX_NAME, 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;

核对结果时重点看三件事：INDEX_NAME 是目标索引，SEQ_IN_INDEX 的列顺序符合查询条件，且该索引的 IS_VISIBLE 为 NO。主键不属于这次开关实验的重点；不可见属性主要用于非主键索引。

二、用会话级 optimizer_switch 做基线对比

不要直接改 GLOBAL。先保存当前会话值，再在同一连接中执行两次 EXPLAIN，这样比较的是同一份统计信息和同一条 SQL：

-- 保存当前会话配置，便于实验结束时恢复
SELECT @@SESSION.optimizer_switch;

-- 基线：不可见索引保持不可见，不参与计划构造
SET SESSION optimizer_switch = 'use_invisible_indexes=off';
EXPLAIN SELECT id, customer_id, status
FROM orders
WHERE customer_id = 1001 AND status = 'paid';

-- 对照：只在当前连接允许优化器考虑不可见索引
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT id, customer_id, status
FROM orders
WHERE customer_id = 1001 AND status = 'paid';

对比时把输出记录成表，而不是只看“快不快”：

这里有一个容易误判的边界：on 表示“允许考虑”，不是“强制使用”。如果统计信息、选择性或回表成本不合适，key 仍可能不是目标索引。若只想为一条语句临时测试，也可以使用官方支持的 SET_VAR 优化器提示，避免改变连接后续语句的状态。

三、把计划差异与真实收益分开判断

计划变化只是第一层证据。先确认两次 SQL 的条件、参数类型、事务隔离级别和数据分布没有变化，再观察 access type、key_len、rows、filtered 与 Extra。如果 key 变了，说明开关影响了候选空间；不代表线上一定降低延迟。

需要执行层证据时，在受控样本上补充 EXPLAIN ANALYZE 或应用侧耗时统计，并固定冷/热缓存条件。不要把单次测试结果直接推广到所有查询：不可见索引仍然会被写操作维护，唯一索引也继续承担唯一性约束，开关只影响优化器构造执行计划的过程。

如果 off 与 on 的计划完全一致，也可能是合理结果：目标索引虽然可被考虑，但成本模型认为全表扫描或另一条可见索引更合适。这时应回到数据分布和查询选择性，而不是继续反复切换开关。

四、恢复设置并明确上线边界

实验结束必须清理会话状态。最简单的策略是在专用连接中测试并直接断开；如果连接还要复用，就把保存的原始值恢复，至少确认当前标志回到预期：

-- 恢复该标志的默认值；若测试前修改过其他开关，应按保存值完整恢复
SET SESSION optimizer_switch = 'use_invisible_indexes=default';
SELECT @@SESSION.optimizer_switch;

-- 需要结束下线评估时，再单独决定是否让索引恢复可见
ALTER TABLE orders ALTER INDEX idx_customer_status VISIBLE;

测试通过后，发布决策仍应结合慢查询、业务高峰和写入成本。不可见索引适合做“保留结构、观察缺失影响”的过渡实验；它不是删除索引，也不自动证明索引应该长期保留。

相关问题

为什么开关设为 on，EXPLAIN 仍没有使用这个索引？

因为 on 只扩大候选范围，最终选择仍由成本模型和统计信息决定；先检查列顺序、选择性、估算行数和其他可见索引。

这个设置应该用 GLOBAL 还是 SESSION？

单条测试优先使用 SESSION，它只影响当前连接。只有经过变更评审并明确影响范围时，才考虑全局配置。

不可见索引会不会停止维护？

不会。索引仍会随行变更维护，唯一索引也继续约束重复值；不可见只改变优化器是否把它纳入执行计划构造。