インデックスの仕組み
インデックスの仕組み とは
SQLでのデータ検索を高速化するインデックスの仕組みを、SQLでの操作を通して実践的に学びます。本レッスンでは、インデックスの仕組み の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。
なぜ重要か
「インデックスを張ると速くなる」というのは聞いたことがあっても、なぜ速くなるのか、どう書くと使われるのか を説明できる人は意外と少数派です。仕組みを知らないと、効かない インデックス を乱発したり、本当に効くインデックスを作り損ねたりします。インデックスは内部的にどう動いているのかを理解すると、SQL の書き方そのものが変わります。
ここではインデックスのデータ構造と探索フローを、B+ツリー の図と一緒に整理します。
インデックスのデータ構造
大半のリレーショナル DB は B+ツリー という多分木構造でインデックスを実装しています。テーブルが 100 万行あっても、B+ ツリーなら 3-4 段で目的の行に辿り着けます。
ルートから葉までを 3 段で辿るとき、各ノードで二分探索が走ります。100 万行を全探索すると 100 万比較ですが、B+ ツリーなら log2(1,000,000) ≒ 20 比較 で済みます。これがインデックスの威力です。
クラスタード vs セカンダリ
MySQL/TiDB の InnoDB では、主キー が自動的に クラスタードインデックス になります。
- クラスタードインデックス 葉ノードに 行データそのもの が格納される。1 テーブルに 1 つだけ
- セカンダリインデックス 葉ノードに インデックスキー + 主キーの値 が格納される
セカンダリインデックス を経由した検索は、まずセカンダリで主キーを特定し、その主キーで再びクラスタードを引きに行く 2 段階探索 になります。これを カバリングインデックス で 1 段に減らすのが定石です。
SQL クエリ
-- email で検索しても、name しか SELECT しなければセカンダリだけで完結する場合がある
CREATE INDEX idx_users_email_name ON users (email, name);
SELECT name FROM users WHERE email = 'taro@example.com';代表例 インデックスありとなしの比較
100 万行のテーブルでインデックスの効果を見るには EXPLAIN を使います。
SQL クエリ
-- インデックスなし: type=ALL (フルスキャン)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- インデックスあり: type=ref (定数アクセス)
CREATE INDEX idx_orders_user_id ON orders (user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 42;type 列が ALL から ref に変わり、rows 列が数百万から数十に減ります。これが B+ ツリー探索の成果です。
インデックスは「魔法」ではなくただのデータ構造です。WHERE 句で そのインデックスの先頭から連続するカラム を等価/範囲指定したときだけ効きます。先頭カラムを飛ばすと使われません。これは「左端優先の原則」と呼ばれます。
インデックスが効かないパターン
次のような書き方をすると、せっかくのインデックスが無視されます。
SQL クエリ
-- NG 関数を被せると効かない
SELECT * FROM users WHERE LOWER(email) = 'taro@example.com';
-- NG 暗黙の型変換
SELECT * FROM users WHERE user_code = 12345; -- user_code が VARCHAR
-- NG 先頭ワイルドカード
SELECT * FROM users WHERE name LIKE '%田中';どれも B+ ツリーの「キーが前から順にソートされている」性質を活かせないため、フルスキャンになります。
注意 本番でクエリが遅いと感じたら、まず
EXPLAINでtypeとkey列を確認しましょう。type=ALLkey=NULLならインデックスが効いていません。WHERE 句の書き方を変えるだけで解決することが大半です。
「The fastest query is the one you never make」と DBA の世界では言われます。インデックスはクエリを速くする最強の道具ですが、根本的にはクエリ自体を減らす設計 (キャッシュ・非正規化・集計テーブル) との合わせ技で使うものです。インデックスだけで何でも解決しようとしないのが、熟練した物理設計者の特徴です。
まとめ
- インデックスは B+ ツリーで実装され、log オーダで探索できる
- クラスタードは行本体、セカンダリはキー + 主キー
- カバリングインデックスにすると 2 段探索が 1 段になる
- 左端優先の原則と、関数被せ・型変換・先頭ワイルドカードに注意
EXPLAINで必ず効きを確認する
演習
B+ ツリーのノード数から「何段のツリーで何行を探索できるか」を計算してみましょう。
次のレッスン
次は CREATE INDEXの使い方 です。CREATE INDEXで検索速度を向上させる方法を、実際に操作しながら学びます。
事前確認 — 進む前に次の 3 つができることを確認しましょう。
- インデックスの仕組み の要点を自分の言葉で説明できる
- このレッスンの最小コード (または操作手順) を見ずに書ける
- 練習問題やクイズで間違えた箇所を読み直して理解した
理解度チェック (30 秒)
Q. インデックスの仕組み とは何か、1 文で説明してください。
A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。
関連レッスン
テーブル構造
schema.sql
CREATE TABLE btree_levels (
level INT PRIMARY KEY,
fanout INT NOT NULL
);
INSERT INTO btree_levels VALUES
(1, 100),
(2, 100),
(3, 100),
(4, 100);期待される出力
| level | reachable_rows |
|---|---|
| 1 | 100 |
| 2 | 10000 |
| 3 | 1000000 |
| 4 | 100000000 |