【ITニュース解説】「先頭カラムが同じインデックス、それ1つ無駄ちゃう？」— 冗長インデックスの見つけ方と消し方

データベースのインデックスは、テーブル内の特定のデータに素早くアクセスするための「目次」のような役割を果たす。このインデックスがあることで、データベースは大量のデータの中から必要な情報を効率よく見つけ出し、検索処理の速度を大きく向上させることができる。しかし、インデックスも無制限に作れば良いというものではなく、不必要に作成されたインデックスは、かえってデータベース全体のパフォーマンスを低下させる原因となる場合がある。特に「冗長なインデックス」は、その典型的な例として挙げられる。

あるデータベースのテーブルに、類似した二つのインデックスが存在する状況を考えてみよう。一つはimport_idという一つのカラムだけに作成されたインデックスで、例えばidx_logs_on_import_idと命名されている。もう一つは、import_idとfile_pathという二つのカラムを組み合わせた複合インデックスで、idx_logs_on_import_id_and_fileと命名されているとする。このような場合、通常はimport_id単独のインデックスは冗長となり、削除できることが多い。

なぜimport_id単独のインデックスが冗長になるのかというと、それは複合インデックスが持つ「左端プレフィックス」という特性による。データベースでよく使われるB-Treeという種類のインデックスは、複数のカラムで構成される複合インデックスであっても、そのインデックスの定義順に沿ってデータが並べられている。例えば、(import_id, file_path)という複合インデックスは、まずimport_idの値でデータを並べ、そのimport_idが同じデータの中では、さらにfile_pathの値でデータを並べるという構造を持つ。これは、電話帳がまず「姓」で並び、その中で「名」で並ぶのと同じ原理である。

この特性のため、(import_id, file_path)という複合インデックスは、import_id単独でデータを検索するクエリに対しても有効に機能する。例えば、WHERE import_id = ?のような条件でデータを検索する場合、データベースはこの複合インデックスの先頭部分であるimport_idを利用して、効率的に目的のデータを見つけることができる。電話帳で「姓」だけを頼りに人を探すことができるように、複合インデックスもその左端のカラムだけで検索を実行できるのだ。しかし、この複合インデックスはfile_path単独で検索する場合には有効ではない。なぜなら、データはimport_idの順に並んでおり、file_pathはimport_idの次の並び順でしかないため、file_pathだけを指定しても効率的な検索はできないからだ。結果として、import_id単独のインデックスと、import_idを左端に持つ複合インデックスは、import_idを使った検索において同じ役割を果たすことになる。この重複した役割により、単独のimport_idインデックスは冗長と判断されるのだ。

冗長なインデックスは、単に存在しているだけで実害がないように思えるかもしれないが、実際にはデータベースのパフォーマンスに悪影響を及ぼす。第一に、テーブルへのデータの書き込み操作（データの挿入、更新、削除）が行われるたびに、そのテーブルに存在するすべてのインデックスも更新する必要がある。冗長なインデックスがあるとその分、データベースは余分な更新処理を行うことになり、書き込み処理の速度が低下する。第二に、インデックスは実際のデータとは別にディスク上の記憶領域を占有する。また、データベースが高速にアクセスできるようにメモリ上にインデックスデータをキャッシュすることもあるため、冗長なインデックスはディスク容量やサーバーのメモリを無駄に消費することになる。第三に、データベースが最適なデータ検索方法（実行計画）を決定する際、冗長なインデックスも検討対象に含められるため、実行計画の選択肢が増え、結果として最適な計画を立てるまでの処理時間が増加する可能性もある。これらの理由から、冗長なインデックスは削除することが望ましい。

冗長なインデックスは、人の目視ですべてのテーブルを確認して見つけるのは現実的ではないため、専用のツールを使って機械的に検出するのが一般的である。

MySQLデータベースを使用している場合、sys.schema_redundant_indexesという標準ビューが非常に有用だ。これはMySQL 5.7以降のバージョンであれば追加のインストールなしで利用でき、左端プレフィックスの重複パターンを自動的に検出する。このビューは、どのインデックスが冗長であるか（redundant_index_name）、そしてどのインデックスがそれを包含しているか（dominant_index_name）を示すだけでなく、その冗長なインデックスを削除するためのSQL文（sql_drop_index）まで生成してくれるため、検出から削除までをスムーズに行うことができる。

他の検出ツールとしては、Percona Toolkitに含まれるpt-duplicate-key-checkerがある。これはコマンドラインから実行できるツールで、データベース全体をスキャンして重複や冗長なインデックス、それらを削除するためのSQL文を出力する。データベースの運用自動化や定期的なチェックに組み込みやすい特徴を持つ。また、sys.schema_unused_indexesというビューも存在し、「前回のサーバー起動以降、一度もクエリで使われていないインデックス」を検出できる。これは冗長インデックスとは少し異なるが、使われていないインデックスも同様に無駄なリソースを消費するため、削除の検討対象となり得る。ただし、このビューの利用には後述する外部キーに関する特別な注意が必要である。さらに、Railsなどのアプリケーションフレームワークには、active_record_doctorのような、アプリケーションコード側から冗長インデックスを検出するツールが提供されている場合もある。

冗長インデックスを削除する際には、外部キー（Foreign Key, FK）に関連するインデックスに特に注意が必要だ。ツールが「冗長候補」として検出しても、外部キー制約のために必須となるインデックスを削除してしまうと、データベースエラーが発生する。外部キーとは、あるテーブルのカラムが別のテーブルのカラムを参照し、データの一貫性を保つための制約である。例えば、ログテーブルのuser_idカラムがユーザーテーブルのidカラムを参照している場合、user_idは外部キーとなる。

データベースは、親テーブルのデータが削除されたり更新されたりした際に、そのデータを参照している子テーブルの行がないかを常にチェックする。このチェックは、実質的に「外部キーのカラムで特定の値を検索する」という操作であるため、この外部キーカラムにインデックスがなければ、毎回テーブル全体をスキャンする非効率な処理が発生し、パフォーマンスが著しく低下する。そのため、多くのデータベースシステムでは、外部キーを設定する際に、関連するカラムにインデックスがなければ自動的に作成するか、インデックスの存在を必須とする。

したがって、sys.schema_unused_indexesで「未使用」と表示されたとしても、それが外部キー制約を維持するために必要なインデックスである場合、削除するとERROR 1553: Cannot drop index ... needed in a foreign key constraintのようなエラーが発生する。これは、そのインデックスが外部キー制約の機能を保証するために不可欠であることを意味している。

外部キーに関連するインデックスが本当に冗長かどうかを判断する鍵は、その外部キーカラムを「左端」に含む別の複合インデックスが存在するかどうかだ。もし、外部キーカラムが複合インデックスの左端として含まれている場合、その複合インデックスが外部キー制約に必要なインデックス要件をすでに満たしているため、単独で存在する外部キーカラムのインデックスは削除しても問題ない。先に挙げたimport_idの例で、単独インデックスが削除可能だったのは、import_idが(import_id, file_path)という複合インデックスの左端に含まれており、外部キーのインデックス要件をこの複合インデックスが肩代わりできたためである。もし、外部キーカラムが左端に位置する他のインデックスが他に存在しない場合、その単独インデックスが外部キー制約の唯一の支えであるため、削除することはできない。

冗長なインデックスを安全に削除するための推奨される方法は、データベースのスキーマ変更を管理する「マイグレーション」という仕組みを利用することである。マイグレーションファイルにインデックスを削除するSQL文（例: ALTER TABLE logs DROP INDEX idx_logs_on_import_id;）を記述し、実行する。マイグレーションを用いることで、データベース変更の履歴が残り、万が一問題が発生した場合でも変更を元に戻す（ロールバックする）ことが可能となり、安全な運用に繋がる。

ただし、マイグレーションを本番環境に適用する前に、特に外部キーに関連するインデックスについては、「本当に削除しても問題がないか」を、開発環境など、本番に近いデータベースで実際に検証することが非常に重要である。机上での判断だけでなく、実際に削除操作を行い、ERROR 1553のような外部キーに関するエラーが発生しないことを確認することで、安全性を確実に担保できる。具体的には、削除用のマイグレーションを開発データベースで一度実行し、エラーが出ずに正常に完了することを確認後、その変更をロールバックして開発環境を元の状態に戻す。この手順を踏むことで、「複合インデックスが外部キーの索引要件を確かに満たしている」という確証を得ることができ、安心して本番環境での削除作業を進めることができる。

インデックスはデータベースの性能を最適化するための強力なツールだが、適切に管理しなければ思わぬパフォーマンス低下を招く。冗長なインデックスを特定し、その影響を理解した上で安全に削除することは、データベースの書き込み性能の向上、ディスクやメモリの効率的な利用、そして最適な実行計画の選択に繋がり、データベースシステムの健全な運用には不可欠な作業だ。