MySQL / 系统课时
使用不可见索引验证索引依赖
使用不可见索引验证索引依赖
删除一个“看起来没用”的索引风险很高:某些低频查询可能正依赖它。不可见索引让优化器暂时忽略该索引,但索引本身仍保留并继续维护,因此可以先观察执行计划与耗时变化,再决定恢复还是删除。
学习目标
- 会把普通二级索引切换为
INVISIBLE,并确认其可见性状态 - 会用
EXPLAIN、EXPLAIN ANALYZE对比隐藏前后的访问路径 - 能根据代表性查询的退化情况判断索引是否存在真实依赖
不可见索引适合做什么
不可见索引的核心价值是可逆地模拟“没有这个索引”。默认情况下,优化器不会把不可见索引纳入执行计划,但索引结构没有被删除;将它重新设为 VISIBLE 通常比重新创建大索引轻得多。
这种实验只能回答“查询是否依赖它”,不能回答“删除后写入能快多少”。因为不可见索引仍会随着 INSERT、UPDATE、DELETE 更新,唯一索引的约束也仍然生效。
建立基线并暂时隐藏索引
下面用一个可重复实验观察 idx_status_created_at。先建立数据与统计信息,再查看基线计划;随后把索引设为不可见,并再次执行计划分析。
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;预期结果
SHOW INDEX 的 Visible 列为 NO。
基线 EXPLAIN 若原本依赖 idx_status_created_at,隐藏后计划会改用其他访问路径,
可能出现全表扫描、额外排序,或更高的实际扫描行数与耗时。
EXPLAIN ANALYZE 中的 actual time、rows、loops 会因机器与缓存状态不同而显示为 <VALUE>。
如果基线本来就没有选择该索引,隐藏后计划可能不变;这同样说明当前这条查询没有证明对该索引存在依赖。EXPLAIN ANALYZE 会真正执行查询,因此不要直接拿副作用语句或高风险查询做实验。比较时应保持 SQL、参数分布和会话设置一致,并多次运行以减少缓存冷热造成的偶然差异。
用单条查询重新允许不可见索引
索引隐藏后,不必立即改回 VISIBLE 才能做反向验证。可以只对一条语句临时允许优化器考虑不可见索引,确认性能改善是否确实来自该索引。
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;预期结果
如果 idx_status_created_at 对该查询有价值,计划通常会重新出现该索引路径,
并伴随更少的扫描、排序或更低的 actual time;具体数值显示为 <VALUE>。这个反向实验很重要:仅看到“隐藏后变慢”还不够,还要确认“让优化器重新考虑它后又恢复”,才能更有把握地把变化归因于该索引。
决定恢复还是删除
不要根据一条 SQL 就删除生产索引。至少应覆盖高频查询、关键接口、批处理任务和低频但高风险的报表,并观察慢查询、执行计划和 Performance Schema 中的工作量变化。
如果隐藏期间出现明显退化,直接恢复可见性:
ALTER TABLE orders_demo
ALTER INDEX idx_status_created_at VISIBLE;
SHOW INDEX FROM orders_demo
WHERE Key_name = 'idx_status_created_at';预期结果
SHOW INDEX 的 Visible 列恢复为 YES。如果一段有代表性的观察窗口内没有查询退化,才有证据进入“考虑删除”阶段。真正 DROP INDEX 前仍应确认没有应用 SQL 使用 FORCE INDEX 等显式索引提示,因为引用不可见索引的索引提示本身就可能报错。