미사용/중복 인덱스 찾기, 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;

하는 일

사용되지 않는 인덱스와 다른 인덱스에 포함되는 인덱스를 나열한다. 삭제 전에 인덱스를 invisible로 만들면 해당 인덱스 없이 운영 동작을 테스트하고 필요 시 즉시(재구축 없이) 되돌릴 수 있다.

사용 시점

쓰기 비용, buffer pool 부담, 디스크 사용량을 줄이기 위해 인덱스를 정리할 때.

주의

schema_unused_indexes는 마지막 재시작 이후 수집된 통계에만 의존하므로 월간 리포트처럼 드문 쿼리는 나타나지 않을 수 있다. replica가 서로 다른 워크로드를 처리한다면 서버별로 따로 확인한다. invisible 인덱스도 DML 시 유지되지만(쓰기 비용 유지) 옵티마이저는 무시한다. unique 인덱스는 invisible 상태에서도 유일성을 강제한다. PRIMARY KEY는 invisible로 만들 수 없다. FORCE INDEX / USE INDEX에 해당 인덱스를 명시한 쿼리는 invisible이 되면 실패한다.