テーブルのインデックスを眺めていて、こんな2本が並んでいるのを見つけたことはないだろうか。
KEY `idx_logs_on_import_id` (`import_id`)
UNIQUE KEY `idx_logs_on_import_id_and_file` (`import_id`, `file_path`)
結論から言うと、上の単独インデックスはたいてい消せる。この記事では「なぜ消せるのか」「本当に消していいのか（消せない例外）」「どう機械的に見つけるか」「安全な消し方」までを一通りまとめる。
なぜ「単独 (import_id)」が冗長なのか
ポイントは 複合インデックスの “左端プレフィックス（leftmost prefix）” は、単独インデックスとしても使える という B-Tree インデックスの性質だ。
(import_id, file_path) というインデックスは、まず import_id で並び、その中で file_path で並ぶ。電話帳が「姓 → 名」の順に並んでいるのを想像するといい。
- 「姓だけで探す」→ できる（左から順に並んでいるから）
- 「名だけで探す」→ できない
同じように (import_id, file_path) は、
-
WHERE import_id = ?→ 効く（左端がimport_id） -
WHERE file_path = ?→ 効かない（2番目のカラム単独では使えない）
つまり WHERE import_id = ? の検索は、複合インデックスがそのまま賄える。単独の (import_id) は複合と役割が丸かぶりというわけだ。
冗長インデックスは「あって困る」
「使わないだけで害はないのでは？」と思うかもしれないが、インデックスはタダではない。
- 書き込みが遅くなる：INSERT / UPDATE / DELETE のたびに、全インデックスを更新する必要がある。冗長な1本ぶん、無駄な更新コストが毎回乗る。
- ディスク・メモリを食う：インデックスは実データとは別に実体を持つ。バッファプールも圧迫する。
- オプティマイザの選択肢を無駄に増やす：実行計画の検討対象が増える。
読み取りのメリットはゼロ（複合で足りる）なのに、書き込みコストだけ払っている状態。これが「冗長」を消すべき理由だ。
どうやって機械的に見つけるか
目視で全テーブルを追うのは非現実的。ツールに任せよう。
1. MySQL 標準ビュー sys.schema_redundant_indexes（おすすめ・導入不要）
MySQL 5.7 以降なら追加インストール不要で使える。左端プレフィックスの重複を判定し、削除用の SQL まで生成してくれる。
SELECT
table_name,
redundant_index_name, -- 冗長な方（消す候補）
redundant_index_columns,
dominant_index_name, -- それを包含している方（残す）
dominant_index_columns,
sql_drop_index -- そのまま実行できる DROP 文
FROM sys.schema_redundant_indexes
WHERE table_name = 'logs';
出力イメージ：
redundant_index_name : idx_logs_on_import_id
dominant_index_name : idx_logs_on_import_id_and_file
sql_drop_index : ALTER TABLE `app`.`logs` DROP INDEX `idx_logs_on_import_id`
2. pt-duplicate-key-checker（Percona Toolkit / CLI）
DB 全体をまとめてスキャンして、重複・冗長インデックスと DROP 文を吐く定番ツール。CI や定期ジョブに組み込みやすい。
pt-duplicate-key-checker --host 127.0.0.1 --user readonly --ask-pass
3. sys.schema_unused_indexes（未使用インデックスの検出）
「冗長」とは別軸で、「前回のサーバ起動以降、一度もクエリで使われていないインデックス」を洗い出せる。ただし後述の FK 注意あり。
4. ORM 付属のツール（例：Rails の active_record_doctor）
アプリ側から detect_extraneous_indexes（冗長）や detect_unindexed_foreign_keys（FK に索引が無い）を検出できる。マイグレーションと同じ言語で回せるので CI 相性が良い。
【重要】消してはいけない冗長候補：外部キー（FK）の索引
ツールが「未使用」「候補」と出しても、消すとエラーになるインデックスがある。それが 外部キーの索引だ。
MySQL は「親テーブルの行が削除・更新されたとき、それを参照している子行がいないか」を毎回チェックする。これは実質 WHERE fk_column = ? の検索で、索引が無いと毎回フルスキャンになって激遅。だから MySQL は「FK を張るなら子カラムに索引を必ず持て」と要求し、無ければ自動で作る。
-- これは「未使用」に見えても、FK のために必須。消すと ERROR 1553。
KEY `fk_logs_on_user_id` (`user_id`),
CONSTRAINT `fk_logs_on_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
見分け方：その FK カラムを「左端」に持つ “別の” インデックスがあるか？
- あるなら（例：複合インデックスの左端が FK カラム）→ FK の要件はそちらが満たすので、単独インデックスは消せる。
- 無いなら → その索引が FK 唯一の頼み → 消せない（消すと
ERROR 1553: Cannot drop index ... needed in a foreign key constraint）。
冒頭の例で単独 (import_id) を消せるのは、複合 (import_id, file_path) の左端が import_id で、FK の索引要件も肩代わりできるからだ。
安全な消し方
マイグレーション
インデックスを1本 DROP するだけ。可逆（ロールバックで復活）にしておく。
class RemoveRedundantIndexFromLogs < ActiveRecord::Migration[8.0]
def change
remove_index :logs, column: :import_id, name: 'idx_logs_on_import_id'
end
end
素の SQL なら：
ALTER TABLE `logs` DROP INDEX `idx_logs_on_import_id`;
適用前に「本当に消せるか」を実地で裏取り
とくに FK が絡むカラムは、机上で「複合が包含するはず」と思っても、実際に DROP して ERROR 1553 が出ないかを一度確認しておくと安心だ。開発 DB で up → down して、元に戻せることも確認しておく。
# 実際に DROP が通るか（FK エラーが出ないか）を確認
bin/rails db:migrate:up VERSION=<version>
# 確認できたら元に戻す（開発 DB を汚さない）
bin/rails db:migrate:down VERSION=<version>
up が ERROR 1553 を出さずに通れば、「複合インデックスが FK の索引要件を満たしている」ことの動かぬ証拠になる。
Top comments (0)