Encontrar índices no usados / redundantes, hacerlos invisibles y eliminarlos con seguridad

Comando

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

Qué hace

Lista los índices no usados y los índices cubiertos por otro; hacer invisible un índice antes de eliminarlo permite probar el comportamiento de producción sin él y revertir al instante (sin reconstrucción) si hace falta.

Cuándo usarlo

Al depurar índices para reducir el costo de escritura, la presión sobre el buffer pool y el uso de disco.

Precaución

schema_unused_indexes se basa solo en estadísticas recopiladas desde el último reinicio; consultas poco frecuentes como los informes mensuales pueden no aparecer. Si las réplicas atienden cargas distintas, revise cada servidor por separado. Un índice invisible se sigue manteniendo en el DML (el costo de escritura permanece) pero el optimizador lo ignora; un índice único invisible sigue imponiendo la unicidad. Una PRIMARY KEY no puede hacerse invisible. Las consultas que nombran el índice en FORCE INDEX / USE INDEX fallan una vez que es invisible.