今年の汚れ、今年のうちに！MySQLで使っていないインデックスを削除しよう

この記事はクラウドワークス Advent Calendar 2023 シリーズ2 2日目の記事です。

こんにちは。crowdworks.jp SRE チーム田中（@kangaechu）です。年末といえば大掃除ですね。皆さんのデータベースにも使っていないインデックスが溜まっていませんか？お掃除してきれいな新年を迎えましょう。

手順

1. MySQLで使っていないインデックスの一覧を取得

未使用のインデックスは sys.unused_indexes ビューで確認できます。

dev.mysql.com

しかし、このビューの元データである performance_schema テーブルは起動時から終了時までのデータしか保持していません。

Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.

https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html

そのため、長期間起動した状態のDBインスタンスを準備しましょう。

-- 未使用のインデックスを確認
SELECT
  object_name,
  index_name
FROM
  sys.schema_unused_indexes
WHERE
  object_schema = 'test'
ORDER BY
  object_name,
  index_name;

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

sys.unused_indexes ビューには、削除してはいけないインデックスも含まれています。今回対象となったのは、FOREIGN KEY 制約を持つインデックスです。

MySQLのドキュメントにはFOREIGN KEY 制約とインデックスの関係について、以下のような記述があります。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

FOREIGN KEYのチェックを高速化し、テーブルスキャンを必要としないように、FOREIGN KEYと参照キーのインデックスが必要
参照テーブルには、FOREIGN KEY列が同じ順序で最初の列としてリストされるインデックスが必要
このようなインデックスが存在しない場合は、参照テーブルに自動的に作成される

つまり、

-- parent table
CREATE TABLE parent(id int PRIMARY KEY, name varchar(20));

-- child table
CREATE TABLE child(id int PRIMARY KEY, name varchar(20), parent_id int,
 INDEX index_child_parent_id(parent_id),
 FOREIGN KEY fk_child_parent_id(parent_id) REFERENCES parent(id)
);

このようなテーブルがある場合、child テーブルの parent_id 列にはインデックスが必要です。そのため、 index_child_parent_id を削除しようとすると、インデックスが未使用であってもエラーになります。

mysql> DROP INDEX index_child_parent_id ON child ALGORITHM=INPLACE LOCK=NONE;
ERROR 1553 (HY000): Cannot drop index 'index_child_parent_id': needed in a foreign key constraint

そのため、これらのインデックスは削除対象から除外しましょう。

3. インデックスをinvisibleに変更

削除対象が決まったところで、インデックスを削除したいところです、がやっぱり怖いですよね。そんなあなたに朗報です。

MySQL 8.0からInvisible Indexという機能が追加されました。これはインデックスをInvisible（=不可視）にできるという機能です。レコードの追加・変更・削除時にインデックスは更新されますが、オプティマイザはそのインデックスを使いません。

インデックス削除
→ 実は使ってたのでパフォーマンス悪化
→ 慌ててインデックス作成
→ インデックス作成時にリソースを消費し、パフォーマンス悪化

みたいなことがないように、試しにオプティマイザがインデックスを使わない設定を試すことができます。

ALTER TABLE child ALTER INDEX index_child_parent_id INVISIBLE, ALGORITHM=INPLACE, LOCK=NONE;

4. インデックスを削除

インデックスをinvisibleにして問題ないことが確認できたら、実際に削除しましょう。

DROP INDEX index_child_parent_id ON child ALGORITHM=INPLACE LOCK=NONE;

このクエリでは ALGORITHM=INPLACE を指定していますが、これはクエリ実行時に使用するアルゴリズムを指定しています。指定しない場合、INSTANT → INPLACE →　COPYの順に適用されます。指定した場合、そのアルゴリズムが使えない場合には失敗します。

dev.mysql.com

今回は意図しない処理およびロックを防ぐため、明示的に指定しています。オペレーションごとに指定可能なアルゴリズムはドキュメントに記載があります。今回はOnline DDL Operations - Dropping an indexを参照し、In Place が Yes だったため、 INPLACE を指定しています。

dev.mysql.com

記述が重複するため詳細な説明は省きますが、LOCK もALGORITHM と同様に指定しています。