Ungenutzte / redundante Indizes finden, unsichtbar machen, sicher löschen

Befehl

-- 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;

Was es tut

Listet ungenutzte Indizes und Indizes, die von einem anderen abgedeckt sind; einen Index vor dem Löschen unsichtbar zu machen, lässt dich das Produktionsverhalten ohne ihn testen und bei Bedarf sofort zurückschalten (kein Rebuild).

Wann einsetzen

Beim Aufräumen von Indizes, um Schreibkosten, Druck auf den Buffer Pool und Plattenbelegung zu senken.

Vorsicht

schema_unused_indexes stützt sich nur auf Statistiken seit dem letzten Neustart; seltene Abfragen wie Monatsberichte tauchen eventuell nicht auf. Bedienen Replicas unterschiedliche Workloads, prüfe jeden Server einzeln. Ein unsichtbarer Index wird bei DML weiter gepflegt (die Schreibkosten bleiben), aber vom Optimizer ignoriert; ein Unique Index erzwingt die Eindeutigkeit auch unsichtbar. Ein PRIMARY KEY kann nicht unsichtbar gemacht werden. Abfragen, die den Index in FORCE INDEX / USE INDEX nennen, schlagen fehl, sobald er unsichtbar ist.