「インデックスを増やせば検索は速くなるが、書き込みは遅くなる」とよく言われますが、実際にどれくらい遅くなるのか、INSERT/UPDATE/DELETE それぞれで何が起きているのかを把握しないと、トレードオフ判断ができません。
本記事ではインデックスの書き込みコストを DML 別に整理します（Use The Index, Luke! 第8章 より）。
インデックスは冗長データという原則
インデックスは「テーブルのデータを別の順序でも引けるようにする」ための 冗長なデータ です。テーブル本体に何かを書き込むたびに、インデックス側でも一貫性を保つ処理が走ります。
具体的には、リーフノードへのエントリ追加／削除と、それに伴うノード分割や再バランス、ブランチノードの更新が発生します（B-tree内部構造 を参照）。
インデックスが多いほど書き込み時のオーバーヘッドが積み上がるため、「とりあえずインデックスを増やす」はアンチパターンです（インデックスショットガン3アンチパターン）。
INSERT
INSERT は WHERE 句を持たない唯一の DML で、インデックスから直接の利益を受けません。一方コストは最も大きくなります。
何が起きるか
- テーブル本体への行追加（空きブロックに書く軽い処理）
- インデックスごとに以下を実行:
- 適切なリーフノードを B-tree 走査で探す
- エントリを挿入
- 必要ならノード分割
- 上位ブランチノードの更新
つまり、インデックスを 5 個張ったテーブルに 1 行 INSERT すると、内部では 1 + 5 = 6 箇所への書き込みが発生します。
コストのスケール感
Use The Index, Luke! 第8章のグラフでは、インデックスゼロの状態の挿入が約 0.0003 秒、インデックス 1 個で約 100 倍、その後インデックスを増やすたびにさらに伸びる、という測定結果が示されています。
1 つインデックスを追加するだけで挿入コストは劇的に変わる
ことを意識する必要があります。
バルクロード時の対処
データウェアハウスやバッチ処理での大量 INSERT では、一時的に全インデックスを落としてからロード後に再構築する のが正しい運用です。
-- ロード前: インデックスを削除（または無効化）
DROP INDEX idx_xxx ON sales;
DROP INDEX idx_yyy ON sales;
-- 大量 INSERT
LOAD DATA INFILE 'sales.csv' INTO TABLE sales ...;
-- ロード後: インデックスを再作成
CREATE INDEX idx_xxx ON sales(...);
CREATE INDEX idx_yyy ON sales(...);行ごとにインデックスを更新するより、最後にまとめてソート＋ビルドする方が桁違いに速いためです。
DELETE
DELETE は WHERE 句を持つので「行を見つける部分」では検索のロジックが効きます。WHERE 列にインデックスがあれば、対象行の特定は INDEX RANGE SCAN で高速に終わります。
何が起きるか
- WHERE 句で対象行を特定
- テーブル本体から行を物理削除
- 対象行に紐づく全インデックスのエントリを削除
- ノード結合や再バランスが必要なら実行
実際の削除処理は INSERT に近く、ツリーバランス維持のためインデックス数に比例して遅くなります。
TRUNCATE TABLE を使うべきケース
WHERE 句なしで全行削除するなら TRUNCATE TABLE を使うのが定石です。DELETE と違って各行を個別に処理せず、テーブル領域ごと初期化するため桁違いに高速です。
-- 遅い: 1行ずつ削除＋インデックス更新
DELETE FROM sales;
-- 速い: テーブル領域を初期化
TRUNCATE TABLE sales;ただし副作用に注意:
- 暗黙コミット が発生（ロールバック不可）
- トリガが動かない ため、削除トリガに依存する処理がある場合は使えない
- 外部キーで参照されている場合は MySQL では実行できない
PostgreSQL の MVCC は別物
PostgreSQL の MVCC では DELETE はテーブルブロックに「削除済みフラグ」を立てるだけで、インデックス数に直接影響されません。物理削除とインデックスメンテは VACUUM の仕事です。
ただし、VACUUM が走るまではインデックスのリーフにも古いエントリが残るため、長期的なオーバーヘッドはやはりインデックス数に比例します。
UPDATE
UPDATE は 対象列を含むインデックスだけが更新されます。
何が起きるか
UPDATE は「古いエントリを削除して新しい場所に入れる」動作です。対象列の値が変わると、そのインデックス上での位置も変わるため、リーフノードから古いエントリを削除し、新しい位置にエントリを挿入し直します。
つまり、name 列だけを UPDATE しても、name を含むインデックス（idx_name、idx_name_age 等）だけが更新され、age 単独のインデックスは触られません。
ORM の「全列 UPDATE」問題
問題は ORM が 「変更のなかった列も含めて全列 UPDATE」 を発行するケースです。たとえば 5 列のテーブルで name だけを変えたつもりでも、ORM が
UPDATE users SET id = ?, name = ?, age = ?, email = ?, created_at = ? WHERE id = ?;のような SQL を発行すると、5 列すべてに紐づくインデックスが更新対象 になります。本来 1 つのインデックスだけ触れば済む処理が、5 つのインデックスを舐めることになります。
各 ORM の挙動:
| ORM | デフォルト挙動 |
|---|---|
| Hibernate | 全列 UPDATE。@DynamicUpdate で変更カラムのみに切り替え可能 |
| Active Record（Rails） | 変更カラムのみ UPDATE（partial_writes 設定。デフォルト ON） |
| Doctrine（PHP） | デフォルトは全列 UPDATE。changeset 検出はあり |
| TypeORM | @UpdateDateColumn などで部分更新サポート |
ORM 利用時は SQL ロギングを開発時に有効化して 生成 SQL を確認する のが推奨運用です。
まとめ
- インデックスは冗長データであり、書き込みのたびに一貫性を保つコストを払う
- INSERT は WHERE がないため利益ゼロ。インデックス 1 個で挿入コストが約 100 倍に跳ね上がるケースも
- バルクロード時は 一時的にインデックスを落とす のが定石
- DELETE は対象行特定にインデックスが効くが、削除処理自体はインデックス数に比例
- 全削除は
TRUNCATE TABLE（暗黙コミット・トリガ不発に注意） - UPDATE は対象列を含むインデックスだけが更新される
- ORM の「全列 UPDATE」は無関係なインデックスまで触る。SQL ロギングで確認