インデックスと実行計画の基礎

インデックスとは

インデックス? とはデータを素早く検索するための「索引」です。本の巻末索引と同様に、特定の値がどこにあるかを素早く見つけられるようにします。

インデックスがない場合、データベースはテーブルの全行を順番にスキャン（フルテーブルスキャン）します。100万行のテーブルでも全行を調べる必要があります。

インデックスがある場合は、B-treeを辿ることで対数時間（O(log n)）で目的のデータを見つけられます。

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

B-treeインデックス? はほとんどのRDBMSで使われているデフォルトのインデックス構造です。

図1: B-treeインデックスの構造（ルート→ブランチ→リーフ）

B-treeは「バランスドツリー」の略で、次の構造を持ちます。

ルートノード: ツリーの頂点。検索はここから始まる
ブランチノード: 中間ノード。キーの範囲で左右に分岐
リーフノード: 末端。実際のキー値と行の物理位置（ポインタ）を格納

-- 検索が O(log n) で済む理由
-- 1000万行のテーブルでも、B-treeの高さは約23程度
-- → 23回の比較で目的の行を見つけられる

インデックスの作成

-- 単一列インデックス
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 複合インデックス
CREATE INDEX idx_orders_date_status ON orders (created_at, status);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 削除
DROP INDEX idx_orders_customer ON orders;  -- MySQL
DROP INDEX idx_orders_customer;            -- PostgreSQL

主キーには自動的にユニークインデックスが作成されます。

EXPLAIN の読み方

EXPLAIN を付けてクエリを実行すると、データベースがどのように処理するかの実行計画が得られます。

図2: EXPLAINの出力の各カラムが意味すること

EXPLAIN SELECT * FROM orders
WHERE customer_id = 101
AND status = 'paid';

MySQL EXPLAINの主要カラム

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_customer  | idx_cus | 4       | const | 150  | Using where |

type（アクセスタイプ）: パフォーマンスへの影響が最も大きい。良い順に：

type	意味	速さ
const	主キーで1行を特定	最速
eq_ref	JOINで主キー/ユニークキーを使用	非常に速い
ref	インデックスを使った等値検索	速い
range	インデックスを使った範囲検索	まあまあ
index	インデックス全体のスキャン	遅い
ALL	フルテーブルスキャン	最遅

key: 実際に使われたインデックス名（NULLならインデックス未使用）

rows: スキャンが必要な行数の推定値（小さいほど良い）

Extra: 追加情報。Using filesort（ソートでインデックス未使用）や Using temporary（一時テーブル使用）が出たら要改善

-- PostgreSQL の場合
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101;
-- ANALYZE を付けると実際の実行時間も表示

インデックスが効くケース・効かないケース

効くケース

-- 等値検索
WHERE customer_id = 101

-- 範囲検索
WHERE created_at >= '2025-01-01'

-- LIKE の前方一致
WHERE name LIKE '田中%'

-- ORDER BY（インデックスの並び順と一致する場合）
ORDER BY customer_id

-- JOIN の結合キー
ON orders.customer_id = customers.id

効かないケース

-- 列に関数を適用
WHERE YEAR(created_at) = 2025  -- インデックス効かない
-- 代わりに:
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- LIKEの後方一致・中間一致
WHERE name LIKE '%田中'   -- インデックス効かない
WHERE name LIKE '%田中%'  -- インデックス効かない

-- 演算で列を変形
WHERE price * 1.1 > 1000  -- インデックス効かない
-- 代わりに:
WHERE price > 1000 / 1.1

-- NULLの検索（DBMSによる）
WHERE col IS NULL  -- DBMSによってはインデックス効かない

-- 低カーディナリティの列（例: status が3種類しかない）
WHERE status = 'paid'  -- 全体の50%が該当する場合はフルスキャンの方が速い

複合インデックスの列順

複合インデックスは列の順序が非常に重要です。

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

このインデックスが効くクエリと効かないクエリ：

-- OK: 先頭列(customer_id)を使っている
WHERE customer_id = 101
WHERE customer_id = 101 AND created_at >= '2025-01-01'

-- NG: 先頭列をスキップしている（created_at単独はインデックス効かない）
WHERE created_at >= '2025-01-01'  -- インデックス効かない

最左プレフィックスルール: 複合インデックスは先頭列から順番に使われます。先頭列をスキップした条件にはインデックスが効きません。

設計のヒント：

WHERE句でよく使う列を先頭に
等値検索の列を範囲検索の列より前に
カーディナリティ（種類数）が高い列を前に

カバリングインデックス

クエリが必要とするすべての列がインデックスに含まれている場合、テーブル本体へのアクセスが不要になります。これをカバリングインデックスと言います。

-- クエリ
SELECT customer_id, SUM(amount)
FROM orders
WHERE customer_id = 101
GROUP BY customer_id;

-- カバリングインデックスを作成
CREATE INDEX idx_covering ON orders (customer_id, amount);
-- customer_id と amount だけでクエリが完結できる
-- EXPLAINの Extra に "Using index" と表示される（テーブルアクセスなし）

まとめ

インデックス（B-tree）によって検索がO(log n)に高速化される
EXPLAIN で実行計画を確認し type・key・rows・Extra を見る
type=ALL（フルスキャン）や Using filesort が出たら改善を検討
関数適用・後方LIKE・演算変形ではインデックスが効かない
複合インデックスは最左プレフィックスルールに従い列順が重要
カバリングインデックスでテーブルアクセスを完全に省略できる

シリーズを終えて

全9話の「SQLをちゃんと理解する」シリーズ、お疲れ様でした。このシリーズでは SELECT文? の実行順序から始まり、 JOIN? ・サブクエリ? ・ CTE（共通テーブル式）? ・ GROUP BY? ・ウィンドウ関数? ・ NULL? ・トランザクション? ・インデックス? まで、「なんとなく使えている」から「仕組みを理解して使える」へのステップを一緒に歩んできました。

次のステップとして以下をおすすめします：

実際のプロジェクトで EXPLAIN を使ってクエリを最適化してみる
正規化? と ER図? を学んでテーブル設計を深める
ストアドプロシージャやビューなどの高度な機能を調べる
使っているDBMS固有の機能（PostgreSQLのJSONB、MySQLのJSON関数など）を探る

SQLは書くほど力がつきます。ぜひ実際のデータで手を動かして続けてください！