未使用 / 冗長なインデックスを特定し、不可視化してから安全に削除する

コマンド

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

概要

未使用のインデックスと、別のインデックスに包含されているインデックスを一覧表示する。削除前にインデックスを不可視にすると、そのインデックスなしで本番環境の動作をテストでき、必要に応じて即座に (再構築なしで) 元に戻せる。

使用する場面

書き込みコスト、バッファプールの負荷、ディスク使用量を減らすためにインデックスを整理する場合。

注意

schema_unused_indexes は最後の再起動以降に収集された統計のみに基づくため、月次レポートなど実行頻度の低いクエリは現れない場合がある。レプリカごとにワークロードが異なる場合は、各サーバーを個別に確認する。不可視インデックスも DML では引き続き維持される (書き込みコストは残る) が、オプティマイザは無視する。一意インデックスは不可視でも一意性を強制する。PRIMARY KEY は不可視にできない。FORCE INDEX / USE INDEX でそのインデックスを指定しているクエリは、不可視にすると失敗する。