MySQL / 系统课时

使用不可见索引验证索引依赖

使用不可见索引验证索引依赖

删除一个“看起来没用”的索引风险很高:某些低频查询可能正依赖它。不可见索引让优化器暂时忽略该索引,但索引本身仍保留并继续维护,因此可以先观察执行计划与耗时变化,再决定恢复还是删除。

学习目标

  • 会把普通二级索引切换为 INVISIBLE,并确认其可见性状态
  • 会用 EXPLAIN、EXPLAIN ANALYZE 对比隐藏前后的访问路径
  • 能根据代表性查询的退化情况判断索引是否存在真实依赖

不可见索引适合做什么

不可见索引的核心价值是可逆地模拟“没有这个索引”。默认情况下,优化器不会把不可见索引纳入执行计划,但索引结构没有被删除;将它重新设为 VISIBLE 通常比重新创建大索引轻得多。

这种实验只能回答“查询是否依赖它”,不能回答“删除后写入能快多少”。因为不可见索引仍会随着 INSERT、UPDATE、DELETE 更新,唯一索引的约束也仍然生效。

建立基线并暂时隐藏索引

下面用一个可重复实验观察 idx_status_created_at。先建立数据与统计信息,再查看基线计划;随后把索引设为不可见,并再次执行计划分析。

sql
DROP TABLE IF EXISTS orders_demo;

CREATE TABLE orders_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  INDEX idx_status_created_at (status, created_at)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 20000;

INSERT INTO orders_demo (status, created_at, amount)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT
  CASE WHEN n % 100 = 0 THEN 'PAID' ELSE 'NEW' END,
  TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n MINUTE,
  n % 5000
FROM seq;

ANALYZE TABLE orders_demo;

EXPLAIN FORMAT=TREE
SELECT id, created_at, amount
FROM orders_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-03 00:00:00'
ORDER BY created_at
LIMIT 20;

ALTER TABLE orders_demo
  ALTER INDEX idx_status_created_at INVISIBLE;

SHOW INDEX FROM orders_demo
WHERE Key_name = 'idx_status_created_at';

EXPLAIN ANALYZE
SELECT id, created_at, amount
FROM orders_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-03 00:00:00'
ORDER BY created_at
LIMIT 20;

预期结果

text
SHOW INDEX 的 Visible 列为 NO。

基线 EXPLAIN 若原本依赖 idx_status_created_at,隐藏后计划会改用其他访问路径,
可能出现全表扫描、额外排序,或更高的实际扫描行数与耗时。
EXPLAIN ANALYZE 中的 actual time、rows、loops 会因机器与缓存状态不同而显示为 <VALUE>。

如果基线本来就没有选择该索引,隐藏后计划可能不变;这同样说明当前这条查询没有证明对该索引存在依赖。

EXPLAIN ANALYZE 会真正执行查询,因此不要直接拿副作用语句或高风险查询做实验。比较时应保持 SQL、参数分布和会话设置一致,并多次运行以减少缓存冷热造成的偶然差异。

用单条查询重新允许不可见索引

索引隐藏后,不必立即改回 VISIBLE 才能做反向验证。可以只对一条语句临时允许优化器考虑不可见索引,确认性能改善是否确实来自该索引。

sql
EXPLAIN ANALYZE
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
       id, created_at, amount
FROM orders_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-03 00:00:00'
ORDER BY created_at
LIMIT 20;

预期结果

text
如果 idx_status_created_at 对该查询有价值,计划通常会重新出现该索引路径,
并伴随更少的扫描、排序或更低的 actual time;具体数值显示为 <VALUE>。

这个反向实验很重要:仅看到“隐藏后变慢”还不够,还要确认“让优化器重新考虑它后又恢复”,才能更有把握地把变化归因于该索引。

决定恢复还是删除

不要根据一条 SQL 就删除生产索引。至少应覆盖高频查询、关键接口、批处理任务和低频但高风险的报表,并观察慢查询、执行计划和 Performance Schema 中的工作量变化。

如果隐藏期间出现明显退化,直接恢复可见性:

sql
ALTER TABLE orders_demo
  ALTER INDEX idx_status_created_at VISIBLE;

SHOW INDEX FROM orders_demo
WHERE Key_name = 'idx_status_created_at';

预期结果

text
SHOW INDEX 的 Visible 列恢复为 YES。

如果一段有代表性的观察窗口内没有查询退化,才有证据进入“考虑删除”阶段。真正 DROP INDEX 前仍应确认没有应用 SQL 使用 FORCE INDEX 等显式索引提示,因为引用不可见索引的索引提示本身就可能报错。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用不可见索引验证索引依赖

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通