Trouver les index inutilisés / redondants, les rendre invisibles, les supprimer en sécurité

Commande

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

Ce que fait la commande

Liste les index inutilisés et les index couverts par un autre ; rendre un index invisible avant de le supprimer permet de tester le comportement de la production sans lui et de revenir en arrière instantanément (sans reconstruction) si nécessaire.

Quand l'utiliser

Lors du nettoyage des index pour réduire le coût d'écriture, la pression sur le buffer pool et l'utilisation disque.

Attention

schema_unused_indexes s'appuie uniquement sur les statistiques collectées depuis le dernier redémarrage ; les requêtes peu fréquentes comme les rapports mensuels peuvent ne pas apparaître. Si les réplicas servent des charges différentes, vérifiez chaque serveur séparément. Un index invisible reste maintenu lors des DML (le coût d'écriture demeure) mais l'optimiseur l'ignore ; un index unique continue d'imposer l'unicité lorsqu'il est invisible. Une PRIMARY KEY ne peut pas être rendue invisible. Les requêtes qui nomment l'index dans FORCE INDEX / USE INDEX échouent dès qu'il est invisible.