MySQL invisible index 使用 SET_VAR 临时启用的单条查询配置方法

我在评估一条慢查询的候选索引时，最怕的是为了看一次执行计划，先把开关改成 SESSION 或 GLOBAL，结果同一连接后面的语句也被影响。MySQL invisible index 更适合做这种低影响验证：索引仍保留在表上，但默认不参加优化器计划构建；需要单独观察时，把 use_invisible_indexes 只放进当前语句的 SET_VAR 提示里。

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

从会话开关到 SET_VAR：把影响面缩到一个查询

先把索引定义成不可见，适合保留回滚空间。下面的例子只表达配置关系，不代表已经在本文环境执行。

-- 保留索引结构，但让默认优化计划先忽略它
CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at) INVISIBLE;

-- 也可以把已有索引切换回不可见状态
ALTER TABLE orders
  ALTER INDEX idx_orders_user_created INVISIBLE;

接着不要急着改全局变量。官方文档给出的查询级方式，是在目标语句的优化器提示中设置 use_invisible_indexes=on。它让这一次计划构建可以考虑不可见索引，其他未带提示的语句仍保持默认行为。

单条查询的写法：先看计划，再决定是否继续

把提示放在 EXPLAIN 包裹的目标查询上，最适合做第一轮对比。示例中的注释是 MySQL 优化器提示，不是普通 SQL 注释。

-- 只为这一条计划分析临时纳入不可见索引
EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
       order_id, created_at
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at
LIMIT 50;

可以把这份结果与不带提示的同一条 EXPLAIN 放在一起看。重点不是看到 possible_keys 就宣布优化成功，而是确认 key 是否变化、type 是否合理、估算的 rows 是否下降，以及 Extra 是否暴露了额外排序或回表成本。

为什么不直接改 SESSION：影响范围和结论边界

SESSION 级设置适合明确控制一段连接内的多条测试语句，但在连接池、脚本复用或排障临时操作中，最容易把开关遗留给下一条 SQL。SET_VAR 的价值是让配置和目标查询放在一起，代码审查时也能直接看出意图。

不过，计划改善不等于线上耗时一定改善。统计信息、数据分布、锁等待、回表列宽和缓存状态都可能改变最终结果。我的落地顺序通常是：先用带提示的 EXPLAIN 判断候选索引是否值得进入下一轮，再用受控流量或影子查询观察真实代价，最后才决定把索引改为 VISIBLE 或删除。

兼容边界与上线前清单

• 确认实例版本和文档支持 SET_VAR 提示，以及 use_invisible_indexes 开关。
• 保留不带提示的基线计划，比较时使用同一组筛选条件、排序和 LIMIT。
• 不要把 possible_keys 当作实际选择；以 key 和完整计划解释为准。
• 记录候选索引名称、验证 SQL、回滚语句和决定人，避免连接池残留配置。

这样做的核心不是让 invisible index 变成“隐藏的万能加速器”，而是把一次计划假设限制在可读、可回退的语句范围内。

相关问题

invisible index 会不会占用存储空间？

会。它仍是索引结构，只是默认不参与优化器计划构建；因此它适合短期评估，不适合无限期堆积不用的索引。

为什么带了 SET_VAR，key 还是没有变？

这只说明优化器看到了候选项，不代表一定选择它。数据分布、成本估算、排序需求或索引前缀不匹配，都可能让原计划仍然更便宜。

验证后应该改成 VISIBLE 还是删除？

如果候选索引在真实负载下稳定有价值，可以安排受控变更改为 VISIBLE；如果长期没有使用价值，再按备份、回滚和业务低峰计划删除。