インデックスと実行計画｜データエンジニアリング

🎓 レベル：標準　|　重要度：A（必須）

📎 前提：SQLの基礎（結合・集約・サブクエリ）　|　関連：列指向ストレージと分析クエリ・シャッフルとパーティショニング

要点（BLUF）

インデックスは「値→行の位置」の索引（多くはB木）。これがあると、全行を見ずに目的の行へ対数時間でたどり着ける。
そのクエリが索引を使うか、全行を舐めるかは**実行計画（EXPLAIN）**で確認できる。SCAN（全表走査）とSEARCH ... USING INDEX（索引探索）の違いを読めることが第一歩。
インデックスはタダではない。書き込みのたびに索引更新コストがかかり、選択性が低い列（例：男女）には効かない。「全部に貼る」は逆効果。

概念 ── 本の索引と同じ

本で特定の語を探すとき、全ページめくる（全表走査）か、巻末の索引から該当ページへ飛ぶ（索引探索）か。インデックスは後者です。テーブルとは別に「列の値を整列した木」を持ち、値から行の位置へ最短で案内します。

flowchart TB
    Q["WHERE cust_id = 1"] --> D{"索引あり？"}
    D -->|なし| FS["全表走査：全行チェック O（N）"]
    D -->|あり| IS["B木をたどる：O（log N）で該当行へ"]

仕組み ── 実行計画で見る差

同じクエリを、インデックスの有無で比べます。

-- index なし
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE cust_id = 1;
-- index を作る
CREATE INDEX idx_orders_cust ON orders(cust_id);
-- index あり
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE cust_id = 1;

実行結果（実機・SQLite）:

[index なし] SCAN orders
[index あり] SEARCH orders USING INDEX idx_orders_cust (cust_id=?)

SCAN orders＝全行を読む。SEARCH ... USING INDEX＝索引で該当行だけを探す。データが100万行なら、この差は劇的です（全走査100万 vs 木をlog桁たどる）。「なぜ遅いか」は推測せず、まずEXPLAINを見るのが鉄則です。

設計の勘所 ── どこに貼るか

効く場面	効かない/逆効果
`WHERE`/`JOIN`/`ORDER BY`で頻出する列	選択性が低い列（値の種類が少ない）
主キー・外部キー	更新が極端に多いテーブル
値の種類が多い列（高選択性）	全行の大半がヒットするクエリ

選択性（カーディナリティ）：値の種類が多いほど索引が効く。「性別」（2種）に貼っても半分が該当し、結局ほぼ全走査。
複合インデックス：(a, b)の順序が重要。WHERE a=? AND b=?やWHERE a=?には効くが、WHERE b=?単独には効きにくい（左端優先）。
カバリングインデックス：必要な列が索引内に全部あれば、テーブル本体を読まずに済む。

なぜそうするか ── 読みと書きのトレードオフ

なぜ全列に索引を貼らないのか。索引は読みを速くする代わりに書きを遅くするからです。INSERT/UPDATE/DELETEのたびに、対応する全インデックスの木を更新します。索引が10個あれば書き込みコストも増える。だから「読まれ方（クエリパターン）」を見て、効く列だけに最小限貼る。OLTP（書き込み多）とOLAP（読み多）で最適点が違うのも、このトレードオフの表れです。分析系では索引より列指向（→ 列指向ストレージと分析クエリ）が効くことも多い。

⚠️ よくある落とし穴

「遅いから索引を増やす」を闇雲に → 書き込み劣化＆オプティマイザが迷う。EXPLAINで効果を確認してから。
列に関数や型変換を掛けて検索 → WHERE date(ymd)=...のように索引列を加工すると索引が使えない（sargable でない）。
複合索引の列順を無視 → 左端から使われる原則を外すと効かない。
統計情報が古い → オプティマイザが誤った計画を選ぶ。ANALYZEで統計更新。

対応ラボ

data-engineering-study/labs/03_sql_optimization.py（PYTHONIOENCODING=utf-8 で実行・SCAN→SEARCH USING INDEXの変化を確認済み）。