不要なインデックス狩り：MySQLの矛盾と出会う

こんにちは！アルダグラムでエンジニアをしている森下霞です。

インデックス削除の基本的な方法や「削除すると書き込みが速くなる」といった話は、Google検索等ですぐに見つかります。この投稿では、自分が実際に不要インデックス削除を進める中で、「MySQLが『使っていない』と言っているのに実際は使っていた」、いわゆる「クエリオプティマイザから見て未使用だが、ストレージエンジンから見れば外部キー制約等に必要だった」という矛盾に遭遇し、どのように確認し、判断したのかを中心にまとめます。

未使用インデックス確認

sys.schema_unused_indexes とは

未使用の可能性が高いインデックスをMySQLが判別しリストアップするビュー。
公式ドキュメント

sys.schema_unused_indexes の落とし穴

未使用インデックスを調査する際、MySQL のオプティマイザが未使用と判断したインデックスをリストアップした sys.schema_unused_indexes ビューは非常に便利です。

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';

しかし、未使用であっても外部キー制約に必要なインデックスが含まれている場合があり、削除を試みると以下のようなエラーが発生します。

-- ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraint

そのため、外部キーに使用されているインデックスを削除対象から除外する必要があります。ただし、そう簡単ではありません。

それに、インデックス削除により順序変化が起こり得ますので、注意が必要です。

外部キー依存の確認が困難な理由

この問題の背景には、MySQLのスキーマ情報の持ち方に理由があります。

• インデックス名で直接紐付けられない
  外部キーでどのカラムが使われているかは information_schema.KEY_COLUMN_USAGE で確認できます。しかし、このテーブルにはどの「インデックス」がその外部キー制約のために使われているかという情報（インデックス名）が直接格納されていません。
• 暗黙的なインデックス
  MySQLは外部キー制約を定義した際に、参照元テーブルのキーに対して適切なインデックスが存在しない場合、暗黙的にインデックスを作成することがあります。このインデックスも sys.schema_unused_indexes にリストアップされる可能性があります。

このため、sys.schema_unused_indexes のリストと KEY_COLUMN_USAGE を単純に比較するだけでは、本当に削除して良いインデックスなのかを100%正確に自動判別することが困難なのです。

対策：外部キー依存を除外した未使用インデックス候補の抽出

そこで、テーブル名とカラム名をキーにして情報を突き合わせることで、外部キーで利用されている可能性が高いインデックスを特定し、削除候補から除外します。

参照するテーブル・ビュー

テーブル/ビュー	内容
sys.schema_unused_indexes	MySQLオプティマイザにより未使用インデックスと判断されたものの一覧
INFORMATION_SCHEMA.STATISTICS	テーブル内のインデックス詳細情報
INFORMATION_SCHEMA.KEY_COLUMN_USAGE	外部キー・主キー・ユニーク制約に関するカラム情報

外部キー依存を除外した未使用インデックスの抽出クエリ例

以下のクエリは sys.schema_unused_indexes に表示されているインデックスのうち、外部キーで使用されている可能性のあるものを除外して削除候補を抽出します。

SELECT
    ui.object_name AS target_table,
    ui.index_name AS unused_index_name,
    s.column_name
FROM
    sys.schema_unused_indexes ui
    JOIN INFORMATION_SCHEMA.STATISTICS s
      ON ui.index_name = s.index_name
      AND ui.object_schema = s.table_schema
      AND ui.object_name = s.table_name
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
      ON ui.object_name = kcu.table_name
      AND ui.object_schema = kcu.table_schema
      AND kcu.column_name = s.column_name
      AND kcu.ordinal_position = 1 -- 複合外部キーの場合を考慮
WHERE
    s.table_schema = 'your_db'
    AND kcu.referenced_table_name IS NULL; -- 外部キーとして使われていないものを抽出

クエリのポイント

• LEFT JOIN と kcu.referenced_table_name IS NULL を組み合わせることで、KEY_COLUMN_USAGE に外部キーとして登録されていないカラムを持つインデックスのみを抽出しています。
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE にはインデックス名が直接含まれないため、テーブル名＋カラム名で突合して外部キー依存有無を確認する必要があります。
• ordinal_position はインデックス内でのカラムの順序を示し、ordinal_position = 1 を条件に入れることで、複合インデックスの2番目以降のカラムが外部キーとして使われている場合に、誤ってインデックス全体を「安全」と判断してしまうことを防ぎます。

このプロセスを踏むことで MySQLのオプティマイザが「未使用」と判断したインデックスの中から、本当に削除可能なものだけを安全に抽出し、運用リスクを最小化して不要インデックス削除を進めることが可能です。

インデックス削除時に発生する順序変化の注意点

未使用インデックスを削除すると、それまで暗黙的にインデックスによって担保されていた結果の返却順序が変わり、アプリケーションやテストで不具合が発生する場合があります。

インデックス削除前後で結果の順序が変わる原因は、MySQLがインデックスを利用してソートを内部的に最適化していたものが、削除後に別のプランに変わり返却順序が不定になるためです。

この問題はクエリに ORDER BY を明示していなかった場合に発生し、インデックス削除が直接的な原因ではなく「クエリにソート条件を指定していなかったこと」が根本原因となります。

削除前の安全なワークフロー

✅ sys.schema_unused_indexes の結果を参考にしつつ、そのまま信用しない

✅ 外部キー依存の可能性を確認

✅ 明示的に順序を定めていないクエリの有無の確認

✅ EXPLAIN と負荷テストでクエリプラン・パフォーマンス変化を確認

重複インデックス確認

不要インデックス整理では sys.schema_redundant_indexes を使うことで重複インデックス候補を効率的に抽出可能ですが、一見重複に見えても削除するとパフォーマンス低下につながる場合があるため注意が必要です。

sys.schema_redundant_indexes とは

• 重複している可能性のあるインデックスをMySQLが判別しリストアップするビュー。
• テーブル・インデックス名・重複先インデックス名・理由などが含まれ、冗長インデックス整理の第一歩として非常に便利。
• 公式ドキュメント

クエリ例：重複インデックス候補の抽出

SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';

この結果をもとに、削除候補となる重複インデックスを確認できます。

削除前に確認すべきポイント

✅ クエリの構造依存

• クエリのWHERE句、ORDER BY、LIMITの使い方により、カラムの組み合わせ順序がパフォーマンスに影響する場合があります。
• 例えば、 (col1, col2) のインデックスと (col1) のインデックスは部分的に重複していますが、col1 単体の検索で (col1) インデックスの方が効率的に使われる場合があります。

✅ パフォーマンスへの影響確認

• 重複インデックスを削除するとINSERT性能は向上するが、特定クエリのパフォーマンスが低下する場合があるため、EXPLAIN や実運用クエリで使用状況を必ず確認する。

削除判断の進め方

部分的重複は EXPLAIN と実クエリログで使用状況を確認し、削除後の動作検証を必ず行う。

sys.schema_redundant_indexes は重複インデックス削除の第一歩として強力ですが、「重複先を全部削除しても大丈夫」というわけではないため、実運用環境に合わせて安全に整理を進めることが重要です。

補足：不要なインデックスがどれだけ場所を取っている？

不要インデックス削除の目的はパフォーマンス改善だけでなくストレージ容量の削減も重要な理由のひとつです。

特に大規模テーブルで「使っていないインデックス」が残っている場合、それだけで数百MB〜数GB単位の容量を消費していることがあり、定期的な削除判断のためにサイズを可視化しておくことが重要です。

特定インデックスの使用ページ数からMBを算出するクエリ

MySQLではインデックス単位の正確なサイズを直接取得することはできませんが、mysql.innodb_index_stats と @@innodb_page_size を活用し、おおよそのMBサイズを推計可能です。

SELECT
  database_name,
  table_name,
  index_name,
  stat_value AS pages,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS approx_mb
FROM
  mysql.innodb_index_stats
WHERE
  stat_name = 'size' AND
  database_name = 'your_db' AND
  table_name = 'your_table' AND
  index_name = 'your_index';

@@innodb_page_size は通常16KBで、これを掛けることで概算のインデックス容量をMB単位で把握できます。

おわりに

sys.schema_unused_indexes や sys.schema_redundant_indexes は、不要インデックス整理の強力な入口になります。ただ、見てきたように MySQL が「未使用」「重複」と報告するインデックスでも、実際には外部キー制約で必要だったり、特定クエリのパフォーマンスを支えていることがあり、簡単に削除できるものばかりではありません。

インデックス削除はスペース削減・性能改善につながる反面、削除が目的化すると思わぬ不具合や性能劣化を招くリスクがあります。だからこそ、MySQLの挙動やクエリの実際の使われ方を理解しながら、EXPLAIN や実クエリログで状況を確かめ、テスト環境で影響を見てから進めるのが現実的で安全な方法です。

インデックス削除は「不要なものを掃除する」だけの作業ではなく、MySQLと対話しながら仕組みを理解していくプロセスでもあります。

この記事が、MySQL運用における安全で実務的なインデックス整理のヒントになれば嬉しいです。

もっとアルダグラムエンジニア組織を知りたい人、ぜひ下記の情報をチェックしてみてください！