手順

MySQLで使われていないインデックスの一覧を取得する

各インスタンスの sys.schema_unused_indexes を確認して未使用インデックスを調査する。

SELECT object_name, index_name
FROM sys.schema_unused_indexes
ORDER BY object_name, index_name;

dev.mysql.com

schema_unused_indexesビューの元はperformance_schemaですが、performance_schemaはDB起動時にリセットされます。そのためDB起動からしばらく経ち統計情報がある程度溜まってから実施するようにしましょう。

削除してはいけないインデックスを除外する

すべてのインスタンスに存在する未使用インデックスのみとする（それ以外は除外）

Writer/Reader構成の場合、それぞれで実行されるクエリが異なるためすべてのインスタンスに存在する未使用インデックスのみを対象にする。
- Writerのみ、Readerのみで使用されているクエリ、それに関連するインデックスがあるため
- Readerが複数存在する場合は念のため、すべて確認しておくほうが安全だが数が多い場合は半数見ていれば十分と思われる
- カスタムエンドポイントなどを設定している場合はエンドポイント毎の確認が必須。

外部キー制約で活用されているインデックスの除外

外部キー制約で活用されているインデックスは削除できないため、対象から除外する必要があります。もし削除してしまうとエラーとなってしまいます。

MySQL では、外部キーチェックを高速に実行でき、かつテーブルスキャンが必要なくなるように、外部キーおよび参照されるキーに関するインデックスが必要です。参照しているテーブルには、外部キーカラムが同じ順序で最初のカラムとしてリストされているインデックスが存在する必要があります。このようなインデックスが存在しない場合は、参照しているテーブル上に自動的に作成されます。外部キー制約の施行に使用できる別のインデックスを作成した場合、このインデックスは後で暗黙的に削除される可能性があります。index_name が指定されている場合は、前述のように使用されます。

dev.mysql.com

調査時のみ活用するインデックスの除外

普段は活用されないが障害調査時などに利用され、残しておきたいインデックスも稀にあります。プロダクトの運用担当者と相談して必要に応じて削除対象から除外します。

インデックスを不可視状態にする

MySQL8.0から invisible index という機能が追加されています。この機能を使うとオプティマイザが不可視にしたインデックスを使わなくなります。

ALTER TABLE tbl ALTER INDEX index_tbl_on_parent_id INVISIBLE;

なお不可視状態から可視状態に変更する場合は、INVISIBLE を VISIBLE に変更するだけです。

dev.mysql.com

なぜ、すぐに削除せず不可視状態にするステップを踏むかですが、schema_unused_indexesの情報はあくまでも目安であり、直近使われていなかっただけのインデックスの可能性もあります。

直近使われてなかっただけのインデックスを削除した場合、クエリのパファーマンスが劣化し、パフォーマンスを元に戻すためインデックス作成しようとするがインデックス作成によって負荷が掛かるため実施タイミングの調整が必要になりリカバリー対応が遅れるなどが想定されます。
invisible を visible に変更するだけであれば、インデックス追加のような負荷は掛からないためリカバリー対応を迅速に実施できるため、上記のような事態を回避することが出来ます。

インデックスを削除する

インデックスを不可視状態にしてしばらく経過観察し問題が起きていないことを確認出来たらインデックスを削除します。

DROP INDEX index_tbl_on_parent_id ON tbl;

その他

直近のプロジェクトでは Rails でマイグレーションに ridgepole を使っていました。index の invisible 設定は Rails および ridgepole ではサポートされていないようでした。そのため、invisible設定はMySQLから直接DDLを実行して実現する方針にしました。また index を invisible に変更する前に index の ignore オプションを trueに変更して、 ridgepole に対象の index を無視するようにしていました。