Поиск неиспользуемых / избыточных индексов, перевод в invisible, безопасное удаление

Команда

-- 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, падают с ошибкой, когда он невидим.