MySQL 隐形索引怎样安全观察删除影响

准备删除 MySQL 索引时，可以先把一个符合条件的二级索引设为 INVISIBLE，让默认配置下的优化器不再考虑它，再观察代表性查询和业务指标；出现退化时用 VISIBLE 恢复。这比直接删除更容易恢复，但不是无风险试验，也不能模拟删除后的全部存储与写入变化。

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

本文按 MySQL 8.4 的语义说明。面对“这个索引看起来没人用，能不能删”的问题，我更愿意先把判断拆成两项：查询还需不需要它，以及物理删除会改变什么。下面采用一个虚构的订单表作为说明，不把示例写成已经发生的线上排障经历。

隐形索引到底能替代哪部分删除试验？

我的判断是，它适合回答“查询不把这条索引当作可选访问路径时会怎样”。索引本体仍然存在，行变化时仍需维护；如果它是唯一索引，唯一性检查仍然有效。因此，隐藏后写入没有明显改善并不奇怪，不能据此断定删除不会减少索引维护成本。

隐形更像一次查询依赖试验。不要在结论里把“本次负载未发现退化”扩大成“永远不会用到”，也不要拿它预测索引空间最终能回收多少。

切换前我会留下哪些可恢复的信息？

假设候选是 demo_shop.orders 上的普通二级索引 idx_customer_created，索引列为 (customer_id, created_at)，表有显式主键。先保存表定义、索引名及列顺序，并记录切换前的查询计划、相近负载下的接口延迟和数据库工作量。表名、索引名和业务值都要按实际环境替换。

-- 保存原表定义，特别关注主键、唯一键和外键依赖。
SHOW CREATE TABLE demo_shop.orders;

-- 联合索引每个列位置一行，按顺序读取，不能只看第一行。
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE, IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'demo_shop'
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 记录本连接的优化器开关；业务连接配置也需单独确认。
SELECT @@SESSION.optimizer_switch;

观察期要覆盖真正依赖这个表的负载。对订单系统而言，日常查询、客服历史检索和结算报表可能处在不同时间窗口；只观察午间几分钟，不足以代表月度报表。我的取舍是先写清哪些任务已经覆盖、哪些还没有，再决定要不要延长观察。

不建议同时隐藏多条索引并发布业务改动。出现退化时，这样很难判断原因。候选涉及主键、唯一约束或外键依赖时，先单独分析约束关系，不把下面普通二级索引的例子直接套过去。

怎样隐藏索引并比较同一个查询？

先在接近真实数据分布的测试环境准备基线。以下 EXPLAIN 仅展示如何请求计划，不提供虚构的计划结果；实际使用哪个索引由数据、统计信息和成本估算共同决定。

-- 示例业务查询：查询某个客户近期的订单。
-- 在切换前保存计划；这是计划说明，不是查询耗时测量。
EXPLAIN
SELECT order_id, created_at
FROM demo_shop.orders
WHERE customer_id = 4207
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 30;

-- 只在计划好的变更窗口隐藏已确认的普通二级索引。
ALTER TABLE demo_shop.orders
  ALTER INDEX idx_customer_created INVISIBLE;

-- 切换后对相同参数再次请求计划，与切换前的记录比较。
EXPLAIN
SELECT order_id, created_at
FROM demo_shop.orders
WHERE customer_id = 4207
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 30;

重点看访问方式、候选索引、实际选择的索引、估算扫描行数和排序相关信息。计划不同是调查线索，不自动等于性能退化；计划看起来相似，也不能替代实际负载下的延迟比较。特别是不同客户的数据量差异很大时，不能只挑一个客户参数代表全部查询。

还有一个不太直观的点：业务 SQL 可能包含显式索引提示。官方文档指出，引用隐形索引的索引提示可能导致错误。观察时除了慢查询，还要看应用错误率；“请求直接失败了”不能被过滤成“数据库查询量变少了”。

观察什么信号，什么时候切回可见？

我会在变更记录中写下既有业务目标和撤回条件，而不是临时编一个通用百分比。对照相近请求量、参数分布和时段，检查尾部延迟、超时、错误，以及数据库扫描工作量。总平均值可能掩盖少数关键查询的退化。

Performance Schema 的语句摘要可按 SCHEMA_NAME 和 DIGEST 归组，观察同类语句的执行次数、耗时和扫描量。它是累计汇总，不是自动为本次变更开出的独立账本。需要保存窗口前后的快照并计算增量；实例重启、统计重置或采集缺失都会影响可比性。不要为了这次观察随意清空共享监控数据。

把观测材料分为三个对象更容易判断：业务延迟说明用户感受，语句摘要定位受影响的 SQL，执行计划帮助解释访问路径。它们共同支持撤回判断，单项没有报警不能替代其他证据。

-- 确认退化后恢复索引可见性；在正确实例和库上操作。
ALTER TABLE demo_shop.orders
  ALTER INDEX idx_customer_created VISIBLE;

-- 确认可见性元数据；不把命令发出等同于已经执行完成。
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'demo_shop'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_customer_created';

恢复后继续检查受影响请求与计划，不应只记录“恢复命令已提交”。虽然官方说明可见性切换是快速的原地操作，DDL 仍涉及元数据锁，可能等待其他事务。长事务与繁忙时段要纳入变更安排；不能承诺任何时刻都能瞬间恢复。这里的恢复是另一条 DDL，不是依靠事务 ROLLBACK 撤销。

没有退化，是否就可以马上 DROP INDEX？

还不够。先确认观察窗口覆盖了低频任务，业务连接没有通过 use_invisible_indexes=on 把这条索引重新纳入优化器考虑范围，且没有把请求错误或负载下降误看成性能改善。该开关默认关闭，但单独检查管理员连接不能证明所有连接都相同。

正式删除是另一个变更，要重新考虑约束依赖、DDL 条件和重建代价，保留原定义与恢复方案。尤其唯一索引，隐藏期间约束仍在，删除后约束的变化无法从此次查询观察中推出。本文不把删除命令混进试验脚本，防止将“观察”误操作成“删除”。

主键也能设成隐形吗？

不能。官方规则排除了显式主键和起到隐式主键作用的索引。没有显式主键的表，应特别检查第一个符合条件的非空唯一索引所承担的角色。

隐藏后能测出写入提升吗？

不能据此估计删除收益，因为索引仍被维护。写入与空间收益需要在合适的独立环境另行比较物理删除后的状态。

观察多久才算足够？

由业务周期决定。至少列明峰值、批处理与低频检索的覆盖情况；观察结束后仍有未执行的关键任务，就把结论限制在已覆盖范围。对我来说，隐形索引的价值是保留较低成本的恢复选择，并让删除决定有具体依据，而不是提供一张永久安全保证。

参考：MySQL 8.4 隐形索引、元数据锁、语句汇总表。