如果你想判断一个 MySQL 索引是否已经没有价值,直接 DROP INDEX 会把回滚成本和风险一起带进来。MySQL 8.4 的 invisible index 提供了更温和的办法:保留索引结构,把它从优化器的候选中暂时隐藏,再比较执行计划和业务观测结果。确认需要它时把状态改回可见;确认不需要时再安排删除。
官方地址:https://dev.mysql.com/doc/refman/8.4/en/invisible-indexes.html
本文只讨论已有二级索引的验证流程。重点是“隐藏后看默认计划”和“单条语句临时启用后做对照”这两个动作,不把示例 SQL 的计划输出当作某个实际数据库已经执行过的证据。
一、invisible index 到底改变了什么
索引变为不可见后,优化器默认不会把它用于查询计划构造,但索引本身仍然存在。对普通二级索引来说,写入和更新仍要维护这棵索引,唯一索引的唯一性约束也不会因为不可见而消失。因此它适合回答“读查询是否还依赖这个索引”,不等于立刻减少写放大或磁盘占用。
MySQL 官方文档还特别说明,主键不能设置为不可见;某些没有显式主键、但由 UNIQUE NOT NULL 索引承担隐式主键作用的表,也不能把该索引直接隐藏。
二、先把已有索引切换为不可见
假设订单表上有一个长期没有明确收益的联合索引 idx_orders_user_status。先记录当前定义和主要使用它的查询,再执行可逆的状态变更:
-- 只切换索引可见性,不删除索引结构 ALTER TABLE orders ALTER INDEX idx_orders_user_status INVISIBLE; -- 查询字典状态,确认目标索引已经对优化器不可见 SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND INDEX_NAME = 'idx_orders_user_status';
也可以用 SHOW INDEX FROM orders 查看同一状态。结果中目标索引的 IS_VISIBLE 应为 NO。这里要按索引名精确操作,避免把同表中的另一个覆盖索引误当成测试对象。
如果只是准备新索引而不想让它立即参与优化器选择,可以在创建时声明:
-- 新索引先保持不可见,等观察窗口准备好后再切换 CREATE INDEX idx_orders_created_status ON orders (created_at, status) INVISIBLE;
三、先看默认计划:隐藏后查询是否换路
索引设为不可见后,普通查询和普通 EXPLAIN 都按“不存在这条可选索引”的视角构造计划。可以固定同一组参数、同一条 SQL,分别保存切换前后的计划,重点看 key、possible_keys、扫描行数和额外排序。
-- 先用默认优化器行为查看隐藏索引后的候选与实际选择 EXPLAIN SELECT id, status, created_at FROM orders WHERE user_id = 10086 AND status = 'paid' ORDER BY created_at DESC LIMIT 20; -- 用字典确认 EXPLAIN 中不应再把目标索引作为默认候选 SHOW INDEX FROM orders;
如果计划从目标联合索引切换为全表扫描、另一条低选择性索引,或者出现额外排序,说明这条索引至少对这类查询仍有潜在价值。反过来,计划完全不变也不能单独证明索引无用:可能是样本查询没有覆盖真实条件,也可能是统计信息和数据分布让优化器选择了另一条同样有效的路径。
四、用单条语句临时启用隐藏索引做对照
MySQL 8.4 的 optimizer_switch 有 use_invisible_indexes 选项。默认关闭时,优化器忽略不可见索引;打开后,不可见索引仍保持不可见,但会被纳入这条语句的计划构造。用 SET_VAR 提示可以把影响范围限制在单条 EXPLAIN:
-- 只为这条 EXPLAIN 临时纳入不可见索引,不改变全局开关
EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
id, status, created_at
FROM orders
WHERE user_id = 10086
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- 这个对照计划可以观察目标索引恢复后是否可能成为 key
EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE user_id = 10086
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
对照时要把两条语句的筛选条件、排序和限制条数保持一致。第一条提示版计划回答“如果允许使用这条索引,优化器可能怎样走”;第二条普通计划回答“当前不可见状态下,默认会怎样走”。这适合定位候选索引对计划的影响,不等同于一次完整的线上基准测试。
五、把计划差异和实际工作负载放在一起判断
建议给验证留出一个可回滚的观察窗口,至少同时观察三类信号:
| 观察项 | 怎么看 | 说明 |
|---|---|---|
| 执行计划 | EXPLAIN、必要时使用 EXPLAIN ANALYZE | 关注访问类型、key、扫描行数和排序变化 |
| 查询性能 | 慢查询日志、Performance Schema 汇总 | 看同一类真实查询是否出现延迟或负载上升 |
| 业务结果 | 错误率、超时、连接池等待和高峰期趋势 | 避免只用一条人工 SQL 代表所有流量 |
如果隐藏后某类核心查询明显变慢,恢复可见即可回滚读路径。MySQL 文档列出的线索还包括:引用该索引的显式索引提示报错、Performance Schema 中相关工作负载增加、执行计划改变,以及原本不进慢查询日志的查询开始进入慢查询日志。
六、验证结束后怎么恢复或删除
验证结果有三种常见结论:
- 仍然需要:恢复可见,让优化器重新正常考虑它。
- 暂时没有证据:继续保持不可见,扩大真实流量观察窗口,不要立即删除。
- 确认不再需要:在确认没有依赖、约束和回滚要求后,再安排删除。
-- 发现核心查询受影响时,快速恢复索引可见性 ALTER TABLE orders ALTER INDEX idx_orders_user_status VISIBLE; -- 只有完成依赖确认并接受重建成本后,才执行不可逆的删除 DROP INDEX idx_orders_user_status ON orders;
恢复可见和删除索引是两个不同动作。前者主要改变优化器是否选用,后者还会释放索引空间并带来重建成本;不要因为“不可见期间没有报错”就直接跳过观察和回滚准备。
七、几个容易误判的边界
- 不可见不等于不维护。 表发生写入时,索引仍会被更新;它不会立刻消除写入开销。
- 主键不能隐藏。 对没有显式主键的 InnoDB 表,还要检查某个唯一且非空的索引是否在承担隐式主键角色。
- 计划改善不等于线上必然变快。 真实数据分布、缓存状态、并发和锁等待都可能改变结果。
- 提示版 EXPLAIN 不会让索引全局可见。
SET_VAR只影响当前语句的计划构造,适合做对照。 - 不要混用测试对象。 每次只切换一个索引,保留 SQL、参数、时间窗口和计划记录,结论才容易归因。
总结
MySQL 8.4 验证冗余索引的稳妥顺序是:先把二级索引设为 INVISIBLE,用普通 EXPLAIN 观察默认路径,再用 SET_VAR(optimizer_switch = 'use_invisible_indexes=on') 做单条语句对照,最后结合真实工作负载决定恢复可见、继续观察还是删除。这样可以把“是否值得保留”的判断拆成可逆的小步骤,同时保留明确的回滚动作。
常见问题
不可见索引会不会停止更新?
不会。不可见只影响优化器是否把它用于查询计划构造,写入时仍会维护索引,唯一索引仍然承担唯一性约束。
为什么 ALTER INDEX 把主键设为 INVISIBLE 会失败?
主键不能不可见。如果表没有显式主键,还要检查唯一且非空的索引是否被 InnoDB 当作隐式主键;这种索引同样不能直接隐藏。
隐藏后发现查询变慢,最先做什么?
先把目标索引恢复为 VISIBLE,再保存恢复前后的执行计划和观测指标。确认读路径恢复后,再分析是否需要调整索引设计,而不是在故障窗口继续删除索引。

MySQL JSON_OBJECTAGG 遇到重复键时保留哪个值
