查找未使用/冗余索引，设为不可见，再安全删除

命令

-- Candidate 1: indexes unused since the last restart
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
-- Candidate 2: redundant indexes that are a left prefix of another
SELECT table_name, redundant_index_name, redundant_index_columns, dominant_index_name, sql_drop_index
FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';

-- Make it invisible before dropping; revert instantly if anything regresses
ALTER TABLE shop.big ALTER INDEX ix_region INVISIBLE;
SELECT INDEX_NAME, IS_VISIBLE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'big' AND SEQ_IN_INDEX = 1;
-- Try the invisible index for a single query
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ COUNT(*) FROM shop.big WHERE region = 'TR';
ALTER TABLE shop.big ALTER INDEX ix_region VISIBLE;
-- After a few business cycles (incl. month-end) without issues
ALTER TABLE shop.big DROP INDEX ix_region, ALGORITHM=INPLACE, LOCK=NONE;

作用

列出未使用的索引以及被其他索引覆盖的索引；删除前先将索引设为不可见，可在没有该索引的情况下测试生产行为，必要时立即恢复（无需重建）。

适用场景

为降低写入成本、buffer pool 压力和磁盘占用而清理索引时。

注意

schema_unused_indexes 仅依赖自上次重启以来收集的统计；月度报表等低频查询可能不会出现。若各副本承担不同负载，请逐台服务器检查。不可见索引在 DML 时仍会被维护（写入成本仍在），但优化器会忽略它；唯一索引在不可见时仍强制唯一性。PRIMARY KEY 不能设为不可见。在 FORCE INDEX / USE INDEX 中指定该索引的查询在其不可见后会失败。