INDEXとは何か

INDEX = 「目的の行へ素早くたどり着くための索引」

  • テーブルのある列（列の組合せ）の値と、対応する行の物理位置（ROWID/TID）を別データ構造にまとめたもの。
  • なくても結果は変わらないが、検索速度が変わる。
  • 更新時はテーブルとINDEXの両方を維持する必要があり、「読み取りの代わりに書き込みコストを払う」仕組み。
本の索引と同じ。「B-Tree」だけ知りたい時に1ページ目から全部読まなくて済む。

B-Treeインデックスの構造

B-Treeの特性

  • どの値も同じ高さ ⇒ 検索コスト一定
  • ノード間は値の範囲で分岐
  • 等価検索: O(log N)
  • 範囲検索: リーフを走査するだけ（リーフは双方向リンクで連結）
  • ソート済み ⇒ ORDER BYに利用可能
ほとんどのRDBMSの標準INDEXはB-Tree（実際にはB+Treeの変種）。

INDEXの種類

  • B-Tree（標準）: 汎用。等価・範囲検索どちらにも有効。
  • 一意（UNIQUE）: 重複を禁止。主キー/一意制約の実装に使われる。
  • 複合（Composite）: 複数列をひとまとめに索引化。列順序が極めて重要。
  • 関数索引（Functional）: UPPER(name) や TRUNC(date) など式に対するインデックス。
  • 部分/フィルタ索引: WHERE条件を満たす行だけ索引化（対応製品のみ）。
  • ビットマップ索引: カーディナリティが低い列で強い。DWH向け。OLTPには不向き。
  • ハッシュ索引: 完全一致検索が極めて高速。範囲検索は不可。
  • 全文索引（Full Text）: 文書からの単語検索用。LIKE '%word%' を高速化。
  • 空間索引（R-Tree等）: 位置情報・図形に対する検索用。

効くSQL / 効かないSQL

使われる

WHERE id = 100
WHERE name = 'Sato'
WHERE created_at >= '2026-01-01'
WHERE status = 'A' AND user_id = 7
ORDER BY created_at  -- 索引順に取れる
JOIN ON a.id = b.a_id  -- 結合キー

使われにくい

WHERE UPPER(name) = 'SATO'   -- 関数で値を加工
WHERE name LIKE '%sato%'     -- 左ワイルドカード
WHERE created_at + 1 < ...   -- 列に演算
WHERE TO_CHAR(d) = '2026'    -- 型変換が発生
WHERE flag IN (0,1)          -- 全件該当
-- 暗黙の型変換 (文字列⇔数値) も注意

複合インデックス設計の考え方

  1. WHEREで等価（=）で絞り込まれる列を先頭に置く。
  2. 範囲条件（>=、BETWEEN）の列は後ろに。
  3. ORDER BYと並びを揃えるとソートを省ける。
  4. 「先頭から連続して使える列」だけが有効。
  5. SELECT列まで全部入っているとIndex Only Scanが可能（カバーリングインデックス）。
-- 例: 注文検索
CREATE INDEX ix_ord
  ON orders(user_id, status, created_at);

-- 使われる
WHERE user_id = 7
  AND status  = 'OPEN'
  AND created_at >= :d;

-- 先頭欠落 ⇒ 使われない
WHERE status = 'OPEN'
  AND created_at >= :d;

INDEXのコスト（デメリット）

  • 更新が遅くなる: INSERT/UPDATE/DELETEのたびにインデックスも更新。大量バッチでは事前にDROP → 再作成が速いことも。
  • ディスクを消費する: テーブル本体と別にセグメントを持つ。複合や関数を増やすと表本体より大きくなることも。
  • メンテが必要: 断片化（B-Treeの偏り）が進むと効果が落ちる。REBUILD/REORGを計画的に。
  • 最適化の選択肢が増える: プランナが選択を誤ると逆に遅くなる場合がある。統計情報を最新に保つことが重要。
RULE OF THUMB: 「読み取りを高速化する代わりに、書き込みコストを払う」装置である。

INDEX運用 Tips

  1. 重複したINDEXに注意: (a) と (a,b) があっても (a) は (a,b) で代用可。古いインデックスは削除して書き込みコストを減らす。
  2. 統計情報を最新に保つ: オプティマイザは統計から実行計画を決める。DBMS_STATS / ANALYZE / pg_statistic を定期実行。
  3. 実行計画で確認: EXPLAIN / EXPLAIN PLAN で Index Range Scan / Index Unique Scanが採用されているかを確認する。
  4. 外部キー列にインデックス: 親テーブルの更新・削除時のロック競合や、子テーブルの参照整合性チェックを高速化するために設定するのが基本。
  5. NULLの扱いに注意: 多くのRDBMSでNULLはB-Treeに格納されない / 限定的。IS NULL での検索は索引が効きにくいことがある。