MySQL 隐藏索引怎样无风险评估查询计划

MySQL 想判断一个索引能不能删除时，直接执行 DROP INDEX 并不是最稳妥的第一步。更适合评估的是“不可见索引”：保留索引结构和维护动作，只让优化器默认忽略它，再对比查询计划和实际负载。这样可以把“删掉后会怎样”的问题拆成可逆的测试动作，但它并不等于完全没有风险，DDL、索引提示、唯一约束和业务流量仍然需要单独考虑。

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

推荐顺序是：确认索引边界 → 记录基线 → 切换为 INVISIBLE → 用 EXPLAIN 对比默认计划和临时启用计划 → 观察负载 → 恢复 VISIBLE 或安排正式删除。不要把“不可见”直接当成“已经删除”。

先明确不可见索引能测试什么

MySQL 的不可见索引不会被优化器用于构造查询计划，普通索引默认是可见的。把二级索引改成不可见后，索引文件仍然存在，表数据变化也仍会维护它；如果它是唯一索引，重复值约束仍然生效。因此这个功能适合测试“优化器不再依赖它”的影响，不适合用来估算“立刻释放索引空间”或“取消唯一约束”的效果。

候选索引最好满足三个条件：它不是主键；它不是 InnoDB 因唯一非空列形成的隐式主键；它的业务依赖可以被列出并在测试窗口内观察。主键索引不能被设为不可见，隐式主键也不能为了测试而绕开。

第一阶段：记录索引和查询基线

先把索引名称、列顺序和当前可见性写入记录。索引提示引用的是索引名，不是列名，所以后面排查 USE INDEX、FORCE INDEX 或 IGNORE INDEX 时必须使用同一个名称。

-- 先确认候选索引的名称、列顺序和可见性，不要直接按列名猜索引
SELECT
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    NON_UNIQUE,
    IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 记录切换前的计划，重点关注 possible_keys、key、rows 和 Extra
EXPLAIN
SELECT order_id, user_id, created_at, total_amount
FROM app.orders
WHERE user_id = 10086
  AND created_at >= '2026-10-01'
ORDER BY created_at DESC
LIMIT 50;

这里的基线不是为了证明查询一定快，而是为了保留“当前使用了哪个索引、估算扫描多少行、是否需要排序”的参照。若查询是线上高频 SQL，还应记录它的调用入口和典型参数，避免只用一个极端值做判断。

第二阶段：把普通二级索引切换为 INVISIBLE

确认候选后使用 ALTER TABLE ... ALTER INDEX ... INVISIBLE。这个动作改变的是优化器是否考虑该索引，而不是删除索引本体。切换前要保存索引名和原始状态，恢复时用同一个名字改回 VISIBLE。

-- 只切换已经确认过的普通二级索引，保留索引结构用于可逆评估
ALTER TABLE app.orders
    ALTER INDEX idx_orders_user_created INVISIBLE;

-- 切换后再次查看状态，确认记录中的索引名没有写错
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_orders_user_created';

-- 需要撤销试验时恢复可见，不要用 DROP INDEX 代替回滚
ALTER TABLE app.orders
    ALTER INDEX idx_orders_user_created VISIBLE;

如果切换语句报错，不要把错误当成索引“没有价值”的证据。它可能是主键索引、隐式主键，或名称与目标表不匹配。先回到索引目录确认边界，再决定是否换一个候选。

第三阶段：对比默认计划与临时启用计划

索引不可见后，普通 EXPLAIN 用来观察“优化器不考虑该索引时”会选择什么。若想判断该索引本来是否能提供更合适的访问路径，可以只在一条 EXPLAIN 语句中打开 use_invisible_indexes，这样不必改变全局配置。

-- 默认计划：不可见索引不进入优化器的候选索引集合
EXPLAIN
SELECT order_id, user_id, created_at, total_amount
FROM app.orders
WHERE user_id = 10086
  AND created_at >= '2026-10-01'
ORDER BY created_at DESC
LIMIT 50;

-- 单条语句临时纳入不可见索引，只用于计划对照，不改全局 optimizer_switch
EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
       order_id, user_id, created_at, total_amount
FROM app.orders
WHERE user_id = 10086
  AND created_at >= '2026-10-01'
ORDER BY created_at DESC
LIMIT 50;

对比时至少看四个点：key 是否改变、possible_keys 是否重新出现候选索引、rows 估算是否明显变化，以及 Extra 是否出现额外排序或回表信息。EXPLAIN 是优化器估算，不是线上耗时证明；它只能帮助你判断下一步该观察什么。

第四阶段：把计划差异和真实负载放在一起判断

如果不可见后计划变化，不能只凭 key 变了就认定索引必须保留。应把变化映射到实际工作负载：受影响查询的性能数据是否上升、之前没有进入慢查询日志的语句是否出现、调用了该索引名的索引提示是否报错，以及常用业务参数下的结果是否仍符合预期。

反过来，如果计划没有变化，也不能立刻删除。可能是测试参数没有覆盖选择性较高的场景，也可能是查询使用了复合索引的另一部分或统计信息让计划暂时保持不变。建议使用代表性参数做对照，并把观察窗口覆盖到典型读写周期。

-- 用索引目录确认仍处于不可见状态，避免把错误的对象当成试验结论
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders';

-- 需要取消试验时先恢复可见，再重新查看关键查询计划
ALTER TABLE app.orders
    ALTER INDEX idx_orders_user_created VISIBLE;

-- 恢复后再次记录计划，确认回滚动作对应的是原来的索引名
EXPLAIN
SELECT order_id, user_id, created_at, total_amount
FROM app.orders
WHERE user_id = 10086
  AND created_at >= '2026-10-01'
ORDER BY created_at DESC
LIMIT 50;

什么时候可以进入真正的删除评估

只有当候选索引在代表性查询、写入路径和索引提示依赖上都完成观察，才适合进入删除评估。正式删除仍是独立变更：它会回收索引结构，但也会改变写入维护成本、磁盘使用和未来查询可用路径，不能因为不可见测试结果“看起来没问题”就直接在生产高峰执行。

常见误区和速查表

• 把 INVISIBLE 当成 DROP。不可见索引仍会维护，唯一索引仍然约束重复值。
• 只改全局 optimizer_switch。对照测试优先使用单条语句的 SET_VAR，减少影响范围。
• 只看 EXPLAIN 的 key。还要结合 rows、Extra、实际负载和索引提示。
• 忽略主键边界。主键和 InnoDB 隐式主键不能通过不可见索引方式测试。

最后再强调一次：不可见索引提供的是可逆的优化器评估窗口，不是“零风险删除”。只要把索引约束、提示依赖、代表性参数和回滚动作一起纳入流程，就能在真正删除之前先获得更可靠的证据。

相关问题

• MySQL 不可见索引会不会停止维护？不会，索引仍随表数据变化更新。
• 不可见索引能不能通过 FORCE INDEX 使用？引用不可见索引的提示可能产生错误，应先确认调用方是否依赖该索引名。
• 为什么普通 EXPLAIN 看不到不可见索引？因为默认的 use_invisible_indexes 是关闭的。
• 不可见索引可以替代删除前的所有测试吗？不能，写入成本、空间回收和业务回归仍需单独评估。