deleted_atにインデックスを雑に貼ったら本番DBが死んだ

2026-05-14 追記: nulls_equalの因果関係やオプティマイザの逆転メカニズムについて、raahiiさんのコメントで深掘りいただきました！

RDSが朝のピーク時間帯にI/Oスパイクで応答不能になりました。前日夜にリリースしたdeleted_atへの単独インデックスが原因です。stagingのEXPLAINでは複合インデックスが正しく選択されていたので、レビューでは検出できていません。

根っこにあるのはMySQL 8.0 innodb_stats_methodのデフォルト値nulls_equalと、IS NULLに対するコスト計算の噛み合わせです。8.0系で現在も未修正のバグに類する挙動で、NULL多数カラムへの単独インデックスがトリガーになります。

根っこにあるのは、平均グループサイズによる行数推定が偏在分布を表現できないという構造的な限界です。加えて、サンプリングベースの統計更新（デフォルト20ページ）の揺らぎがコスト比較を逆転させます。NULL多数カラムへの単独インデックス追加がトリガーになります。

テーブルとクエリ

問題が起きたのはチケット管理SaaSのticketsテーブルです。ソフトデリートでdeleted_atを持つよくある設計です。

CREATE TABLE tickets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workspace_id INT UNSIGNED NOT NULL,
  assignee_id INT UNSIGNED NOT NULL,
  priority TINYINT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(255) NOT NULL,
  deleted_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_tickets_assignee_id (assignee_id),
  INDEX idx_tickets_assignee_deleted_priority (assignee_id, deleted_at, priority)
) ENGINE=InnoDB;

レコード数は約80万件です。うち50万件がdeleted_at IS NULL（アクティブ）で、残り30万件が論理削除済みです。

問題になったクエリはワークスペースのメンバー一覧と、各メンバーが担当するチケットをJOINで取得するものです。

SELECT m.*, u.*, t.*
  FROM workspace_members m
    LEFT OUTER JOIN users u ON u.id = m.user_id
    LEFT OUTER JOIN tickets t ON t.deleted_at IS NULL
      AND t.assignee_id = u.id
WHERE m.workspace_id = 42
  AND m.user_id IN (
    SELECT u2.id FROM users u2
    WHERE u2.deactivated_at IS NULL
  )
ORDER BY u.display_name ASC;

deleted_at IS NULLがJOINのON句に置かれています。オプティマイザはこれを「定数との等値比較（const）」として解釈します。

ショットガンインデックス

遅いクエリの改善チケットが立ち、対応として複数のインデックスをまとめて追加しました。

ALTER TABLE tickets ADD INDEX idx_tickets_deleted_at (deleted_at);
ALTER TABLE tickets ADD INDEX idx_tickets_workspace_deleted (workspace_id, deleted_at);
-- ...他にもいくつか

idx_tickets_assignee_deleted_priority（複合インデックス）は既にありましたが、idx_tickets_deleted_atは完全に余計でした。しかし「少しでも改善してくれれば」という気持ちで追加しました。遅いクエリに対して効くかもしれないインデックスを広めに貼る——ショットガンインデックスです。この手の「念のため」が一番危ない。

stagingでは正常だった

+----------+------+------------------------------------------+---------+-----------------+------+------------------------+
| table    | type | key                                      | key_len | ref             | rows | Extra                  |
+----------+------+------------------------------------------+---------+-----------------+------+------------------------+
| tickets  | ref  | idx_tickets_assignee_deleted_priority     | 13      | users.id, const | 1    | Using index condition  |
+----------+------+------------------------------------------+---------+-----------------+------+------------------------+

staging環境のEXPLAINでは複合インデックスが選ばれていました。ref = users.id, constでassignee_idとdeleted_at IS NULLの両方をインデックスで解決。rows=1でUsing index condition。完璧に見えます。

本番で死んだ

翌朝、業務開始のアクセス集中と同時にRDSのI/Oが急騰。wait/io/tableが90%近くまで跳ね上がりコネクションプールが枯渇しました。

本番のEXPLAINを確認すると、オプティマイザがidx_tickets_deleted_at（単独インデックス）を選択しています。

+----------+------+--------------------------+---------+-------+------+-------------+
| table    | type | key                      | key_len | ref   | rows | Extra       |
+----------+------+--------------------------+---------+-------+------+-------------+
| tickets  | ref  | idx_tickets_deleted_at   | 6       | const | 1    | Using where |
+----------+------+--------------------------+---------+-------+------+-------------+

rows=1なのは同じです。しかしExtraがUsing whereに変わっています。インデックスでdeleted_at IS NULLの50万件を引いたあと、assignee_idのフィルタは実データへ読みに行っています。

メンバー一覧画面を1人が開くだけで数万行のランダムI/Oが走ります。朝9時、全員が一斉にダッシュボードを開きました。

なぜオプティマイザは間違えたのか

2つの仕様が組み合わさって起きました。

nulls_equalの統計歪み

MySQL 8.0のデフォルト設定innodb_stats_method = nulls_equalは、NULL値を統計上「すべて同一の値」として扱います。50万件あるdeleted_at IS NULLが統計上1つのグループとしてカウントされます。

テーブル全体80万件に対しn_diff ≈ 300,001だと、平均グループサイズは約2.7件です。50万件のNULLが統計上2.7件に化けます。

IS NULLのconst解釈

JOIN ON句のdeleted_at IS NULLはオプティマイザに「定数との等値比較」として評価されます。ref = constになり、rows推定には統計上の平均グループサイズ（約2.7→切り捨て→1）が使われます。実際には50万件。推定は1件です。

コスト比較で逆転する

rows=1同士なら、参照カラム数が少ないインデックスほどコストは低く見積もられます。deleted_at単独の方が「シンプルで安い」とオプティマイザは判断します。50万行フルスキャンするインデックスが「最安」に見えます。

対処

メンテナンス画面を敷いて、単独インデックスをDROPしました。

ALTER TABLE tickets DROP INDEX idx_tickets_deleted_at;

DROP後は複合インデックスidx_tickets_assignee_deleted_priorityだけが候補になり、オプティマイザは正しい方を選ぶようになりました。誤った選択肢をそもそも存在させないのが一番確実な対処です。

スナップショットで再現検証して分かったこと

障害対処後、本番スナップショットから検証用DBを作って同じ状況を再現しました。

検証DBでADD INDEXを再実行した直後のEXPLAINでは複合インデックスが選ばれていました。stagingと同じ結果です。ところがANALYZE TABLE ticketsを実行した途端、オプティマイザがdeleted_at単独インデックスに切り替わりました。

フェーズ	選択されたインデックス	評価
ADD INDEX直後（ANALYZEなし）	idx_tickets_assignee_deleted_priority（複合）	✅
ANALYZE TABLE 実行後	idx_tickets_deleted_at（単独）	❌
DROP INDEX 後	idx_tickets_assignee_deleted_priority（複合）	✅

本番ではリリース直後から単独インデックスが選択されていました。検証DBではANALYZEを実行するまで複合インデックスが選ばれていました。同じデータなのに統計の状態次第で結果が逆転します。

ANALYZEはサンプリングベースで統計を更新します。innodb_stats_persistent_sample_pagesのデフォルト値は20です。たった20ページ分のサンプルでカーディナリティ全体を推定します。サンプリング結果にはランダム性があり、推定値が微妙に変動するだけでコスト比較がひっくり返ります。

staging環境のEXPLAINが正常だったのも同じ構造です。統計の初期値がたまたま複合インデックスに有利だっただけで、再現性のある安全はどこにもありませんでした。

MySQL側の状況

この挙動はMySQL公式Bugとして複数報告されています。

Bug #	概要	ステータス
#30423	InnoDBのNULL統計処理がrows推定を狂わせる	Closed（部分対処のみ）
#114237	IS NULLでrows推定が条件なしより悪化する逆転現象	Verified（未修正）

Bug #114237ではWHERE col IS NULLを付けた方がrows推定が増えるという逆転現象が報告されており、MySQL検証チームが2024年3月にVerifiedとしています。8.0系で現在も未修正です。

MySQL 8.0 リファレンス §10.3.8 — InnoDB and MyISAM Index Statistics Collection にも以下の記述があります。

For = comparisons, it does not matter how many NULL values are in the table. For optimization purposes, the relevant value is the average size of the non-NULL value groups. However, MySQL does not currently enable that average size to be collected or used.

（= 比較においては、テーブル内のNULL値の数は問題にならない。最適化の目的上、重要なのは非NULLの値グループの平均サイズである。しかし、MySQLは現時点ではその平均サイズを収集または使用できていない。）

nulls_equalの影響についてはこう書かれています。

If the NULL value group size is much higher than the average non-NULL value group size, this method skews the average value group size upward. This makes index appear to the optimizer to be less useful than it really is for joins that look for non-NULL values. Consequently, the nulls_equal method may cause the optimizer not to use the index for ref accesses when it should.

（NULLの値グループサイズが非NULLの平均値グループサイズよりはるかに大きい場合、このメソッドは平均値グループサイズを上方に歪める。これにより、非NULL値を検索するJOINに対して、オプティマイザにはインデックスが実際よりも役に立たないように見える。結果として、nulls_equalメソッドは、本来使うべきref accessでインデックスを使用しない原因となることがある。）

deleted_atインデックスの設計原則

IS NULLが大多数を占めるカラムに単独インデックスを貼ってはいけません。

ケース	設計	理由
IS NULLが大多数（ソフトデリート）	複合インデックスの後続カラムに配置	単独だとオプティマイザが誤選択する
IS NOT NULLが大多数（削除済みが大半）	単独も可	少数派のNULLを検索する用途なら有効
バッチ削除用	単独可（範囲スキャン用途）	WHERE deleted_at < ? なら効果あり

複合インデックスではカーディナリティが高いカラム（assignee_id等）を先頭に置き、deleted_atは後続に配置します。先頭カラムで十分に絞り込めていれば、IS NULLの統計歪みはコスト計算に影響しにくくなります。

-- ❌ IS NULLが大多数のカラムを単独で
ADD INDEX idx_tickets_deleted_at (deleted_at);

-- ✅ 検索条件の高カーディナリティカラムを先頭に
ADD INDEX idx_tickets_assignee_deleted_priority (assignee_id, deleted_at, priority);

stagingのEXPLAINが通っても安心はできません。統計のサンプリング結果次第でオプティマイザの判断はいつでもひっくり返ります。deleted_atに単独インデックスを貼った時点で、この爆弾はもう埋まっていました。