MySQL: deleted_at に単独インデックスを雑に貼ると本番が死にうる
ソフトデリートで deleted_at IS NULL が大多数のとき、deleted_at 単独インデックスを「念のため」追加すると、オプティマイザが複合インデックスよりそれを選び、巨大な NULL 集合をスキャンして I/O が爆発することがある。staging の EXPLAIN が良くても、統計の揺らぎで本番だけ逆転する。
どんなときに使う？
- 遅いクエリ対策でインデックスをショットガン追加しそうになったとき（特に
deleted_at） - staging EXPLAIN は複合インデックス、本番だけ別インデックス、という不一致を疑うとき
何が起きたか（要約）
- テーブル約 80 万行、うちアクティブ（
deleted_at IS NULL）約 50 万 - 既に
(assignee_id, deleted_at, priority)の複合インデックスがあるのに、INDEX (deleted_at)を追加 - staging EXPLAIN は複合が選ばれるが、本番ピークで単独
deleted_atが選ばれ、Using whereで実データ大量読み - 検証では
ANALYZE TABLE後に計画が逆転。サンプリング統計の誤差でコスト比較がひっくり返る
設計原則
| ケース | 方針 |
|---|---|
| IS NULL が大多数（典型的なソフトデリート） | 単独インデックス禁止。高カーディナリティ条件を先頭にした複合の後段に置く |
| 削除済みが大多数で「残 NULL」を探す | 単独もありうる |
バッチで deleted_at < ? の範囲スキャン | 用途限定で単独可 |
sql
-- NG
ADD INDEX idx_tickets_deleted_at (deleted_at);
-- OK（例）
ADD INDEX idx_tickets_assignee_deleted_priority (assignee_id, deleted_at, priority);対処の本筋は、誤った候補インデックスそのものを DROP して選択肢から消すこと。障害中の DROP INDEX は MDL 待ちで悪化しうるので、別記事の Online DDL / MDL 注意も参照。
注意点
- staging EXPLAIN 通過 ≠ 安全。統計はいつでも揺れる
- MySQL 側にも関連バグ報告あり（rows 推定の歪み）。アプリ側で「危ないインデックスを生やさない」のが現実的