インデックスのコスト（更新オーバーヘッド）

インデックスはタダではない

インデックスは検索を速くする代わりに、以下のコストを裏で払っています。

• ストレージ: テーブル本体とは別に、インデックスのB-tree構造をディスクに保持する。大規模テーブルでは数百MB〜数GBに達することも
• 書き込みオーバーヘッド: INSERT / UPDATE / DELETE のたびに、テーブル本体だけでなくインデックス側にも書き込みが発生する
• 統計情報の維持: オプティマイザが判断を下すための統計情報も、インデックスの数だけ管理対象が増える
• プランニング時間: オプティマイザの検討候補が増えるほど、実行計画を立てる時間も伸びる

書き込みコストの正体は「n+1 ページ書き込み」

インデックスを貼れば貼るほど INSERT が遅くなる、と言われる理由はシンプルです。

1 件の INSERT で、テーブル本体のページ 1 枚に加えて、貼っているインデックスの葉ノードページを 1 枚ずつ更新する必要があります。インデックスが n 個あれば n + 1 ページの書き込みです。

INSERT INTO users (email, name, status, ...)
VALUES (...);

-- 発生する書き込み（概念）
-- 1. users テーブル本体のページ ×1
-- 2. idx_email の葉ノード         ×1
-- 3. idx_status の葉ノード        ×1
-- 4. idx_created_at の葉ノード    ×1
--   （インデックスが n 個あるだけ増える）

インデックスの葉ノード分裂や、B-tree の再バランスが発生すればさらに書き込みは増えます。 UPDATE の場合は「更新されたカラムに関連するインデックスだけ」が対象ですが、主キーや複合キーの更新は複数インデックスに波及します。

物理コストは「ページI/O 回数」で決まる

インデックスが「速い」「遅い」と言うときの実体は、ページI/Oの回数と種類です。目安は以下。

• シーケンシャルページ読み取り: 1枚あたり数十マイクロ秒〜。連続領域は先読みが効くので実質もっと速い
• ランダムページ読み取り: 1枚あたり数ミリ秒〜。シーケンシャルの数十〜数百倍遅い
• ページ書き込み: WAL/redo log や fsync も絡み、読み取りより更に重い

だから「更新頻度が高いテーブルにインデックスを大量に貼る」ことは、実質的に 書き込み時のランダムページ書き込み回数を掛け算で増やす 選択と同じです。

どこまで貼っていいか（目安）

魔法の数字はないですが、実務でよく使う指針:

• 主キー / ユニーク制約: 迷わず貼る（強制的に必要）
• 頻出のWHERE条件: SELECT ... WHERE X = ? を高頻度で叩くなら X にインデックス
• 頻出のJOIN条件: 外部キー側にインデックス。特に大きなテーブル同士の JOIN
• ソート/GROUP BY: 頻出のソート順にインデックス

大まかな上限感として、1テーブルに 5〜7 個を超えたら要確認、10個以上ならほぼ間違いなく貼りすぎです。

「貼りすぎ」を防ぐ実務チェックリスト

1. 本当に使われているか：EXPLAIN でインデックスが選ばれているか確認。使われていないインデックスは削除候補
2. 重複していないか：(A) と (A, B) がある場合、前者は後者で代替できることが多いので削除できる
3. 書き込み比率を見る：INSERT/UPDATE の頻度が高いテーブルでは、インデックス数を積極的に絞る
4. 使用回数の統計ビューを見る：多くのRDBMSに「そのインデックスが何回使われたか」を記録する統計ビューがある。ゼロ回のものは棚卸し対象
5. ログ系・時系列テーブルは特に絞る：ほぼ書き込みしかないテーブルにインデックスを多く貼るのは典型的なアンチパターン

まとめ

インデックス設計は「速くする」だけの作業ではなく トレードオフを設計する 作業です。

検索頻度、書き込み頻度、データサイズ、ビジネス上の許容レイテンシ──これらを見て、本当に必要なものだけ を貼るのが正解に近づく方法です。 「とりあえず全部のカラムにインデックス」は、書き込みコストとストレージを爆発させる最短ルートなので避けましょう。