如何利用MySQL 8.0的Invisible Index特性在性能测试中隐藏索引？

本文共计853个文字，预计阅读时间需要4分钟。

使用以下SQL语句即可将表索引设置为不可见，操作属于元数据级别变更，瞬间完成：

ALTER TABLE orders ALTER INDEX idx_user_id INVISIBLE;

注意：执行前必须确认该索引不是主键，也不是隐式主键（即没有显式主键时，第一个 UNIQUE NOT NULL 索引）。否则会报错 ERROR 3522 (HY000): A primary key index cannot be invisible。

• 不能对 PRIMARY 索引执行该操作，无论是否显式定义
• 如果表没主键但有 UNIQUE (a) NOT NULL，这个索引也属于隐式主键，同样禁止隐藏
• 普通二级索引、前缀索引、函数索引都支持隐藏

怎么验证隐藏是否生效？

别只信命令返回的 Query OK，要查 INFORMATION_SCHEMA.STATISTICS 或用 SHOW INDEX 看真实状态：

SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders' AND INDEX_NAME = 'idx_user_id';

返回 NO 才算成功隐藏。同时检查执行计划：EXPLAIN SELECT * FROM orders WHERE user_id = 123; —— 如果之前走该索引，现在变成 type: ALL 或换用了别的索引，基本可确认生效。

• SHOW INDEX FROM orders 的输出中，Comment 列会显示 invisible
• 如果查询里写了 USE INDEX (idx_user_id) 提示，会直接报错 ERROR 1176 (42000): Key 'idx_user_id' doesn't exist in table 'orders'
• 即使隐藏了，INSERT/UPDATE/DELETE 仍会维护该索引数据（唯一约束照常生效）

测试时优化器为啥还是用了隐藏索引？

大概率是会话或全局开启了 use_invisible_indexes=on。MySQL 默认关闭它（optimizer_switch 中该标志为 off），但若有人手动打开过，隐藏索引就“失效”了。

查当前值：SELECT @@optimizer_switch LIKE '%use_invisible_indexes=on%';

临时关掉（仅当前会话）：SET SESSION optimizer_switch = 'use_invisible_indexes=off';

• 生产环境不建议全局开启 use_invisible_indexes，容易干扰预期行为
• 单条 SQL 想强制用隐藏索引测试？可用 /*+ SET_VAR(optimizer_switch = "use_invisible_indexes=on") */ 提示，但仅限 MySQL 8.0.22+
• 性能测试期间，务必确认慢查询日志、Performance Schema 中的语句执行时间变化，不能只看执行计划

恢复可见和删除索引的区别在哪？

恢复：ALTER TABLE orders ALTER INDEX idx_user_id VISIBLE; —— 同样瞬时，不重建，原索引立刻参与优化器决策。

删除：DROP INDEX idx_user_id ON orders; —— DDL 锁表（InnoDB 行锁模式下仍可能阻塞写），且重建成本高；若后续发现需要，得重新 CREATE INDEX，大表可能耗时几分钟到几小时。

• 隐藏索引适合“怀疑但不敢删”的场景，尤其是线上核心表
• 隐藏后观察周期建议至少覆盖一个完整业务高峰，避免只看低峰期数据
• 有个易忽略点：备份工具（如 mysqldump）导出时默认包含隐藏索引定义，恢复后仍是隐藏状态，需额外处理