フルスキャンとインデックス — 障害 1: マイページが 4 秒かかる

準備: 索引のない状態にする

order_items の customer_id に索引がない状態から始める。すでに idx_customer があれば落とす。なければ「Can't DROP」のエラーになるが、それは「もうない」という意味なので次へ進んでいい。

実物: 障害のクエリ

マイページのクエリそのものを叩く。実行時間と返る行数を予測してから。

数秒かかり、読んだ行数は 3,200 万、返した行数は 1(集計結果)。実測プランの一番下の行は Table scan on order_items。 「order_items を端から端まで読んだ」という意味だ。その上の Filter: (customer_id = 123456) は「読んだ行のうち、customer_id が 123456 のものだけ残した」。 顧客 123456 の明細は 64 行しかないので、読んだ行の 99.9998 % を捨てている。

なぜ全部読むしかなかったのか

DB は行を 1 行ずつバラバラに保存しているのではなく、ページという 16KB の箱に詰めて保存している。 order_items の 1 行は 60 バイトほどなので、1 ページに 250 行前後入る。3,200 万行なら約 13 万ページ。ディスクからの読み書きは、このページ単位で行われる。 この見積もりが合っているかは、テーブル本体のバイト数を 16KB で割れば確かめられる。

pages は約 13 万のはずだ。data_length が本体のバイト数で、これを 16KB(16,384 バイト)で割った数が、Table scan で開けるページの数になる。

ページの中で、行は主キー(id)の順に並んでいる。1 ページ目に id 1〜250、2 ページ目に id 251〜500、という具合だ。 だから WHERE id = 12345678 なら「id 12345678 は何ページ目か」を計算して、そのページだけ読めばいい(第 5 章)。

ところが customer_id は主キーではない。customer_id = 123456 の行は、id 104 と id 77120 と id 9930811 のように、13 万ページの中に散らばっている。 「customer_id が 123456 の行はどのページにあるか」を知る手段がない。だから全ページを開けて、1 行ずつ customer_id を確かめるしかない。 これが Table scan で、コストはテーブルの行数に比例する。1 年前の 200 行なら一瞬だった理由も、今 4 秒かかる理由も、これだ。

本体の並び順(id)と探したい列(customer_id)が食い違っている。これを埋めるのが次の索引だ。

索引を張る

「customer_id が 123456 の行はどこにあるか」を知る手段を作る。それが索引だ。中身の仕組みは後で開けるので、まず作って効果を見る。 作るのにかかる時間も記録しておく。

1 分前後かかったはずだ。3,200 万行で 1 分なら、本番の数十億行では数時間になる。その間、素朴にやるとテーブルへの書き込みが止まる。 「稼働中のテーブルにどうやって索引を足すか」は第 8 章の主題で、今の 1 分を覚えておくと腹落ちする。

同じクエリをもう一度

ミリ秒で返り、読んだ行数は 64。実測プランの一番下は Index lookup on order_items using idx_customer。 「idx_customer という索引を引いて、該当行だけ読んだ」。Filter の行も消えている。読んだ行がすべて正解なので、選別する必要がなかった。 同じ SQL、同じ結果。変わったのは、テーブルの横に「customer_id → 行の場所」の一覧ができたことだけだ。障害 1 はこれで閉じる。

索引の中身を開ける

第一部 8 章では「辞書の巻末索引」という比喩で止めた。ここでは中身を 3 つの言葉で開ける: キー、ページ、木。

キー: 「値 → 行の場所」の組

CREATE INDEX idx_customer ON order_items (customer_id) は、全行について「customer_id の値」と「その行の主キー(id)」の組を作り、customer_id の順に並べて別の場所に保存する。この組をキー(エントリ)と呼ぶ。こういう並びになる。

customer_id → id
123455      → 8812
123456      → 104
123456      → 77120
123456      → 9930811
123457      → 551
...

customer_id 順に並んでいるので、123456 を探すには「123456 のあたり」を開いて、隣り合った 64 エントリを読めばいい。 そこに書いてある id で本体の行を取りに行く。これが Index lookup の正体だ。「本体を取りに行く」二度手間については第 5 章でやる。

ページ: 16KB の箱に、キーが何個入るか

索引もテーブル本体と同じく、16KB のページに詰めて保存される。キー 1 つは customer_id(4 バイト)+ id(8 バイト)+ 管理情報で 20 バイト弱。 16KB ÷ 20 バイト ≒ 1 ページに約 800〜1,000 キー。3,200 万キーなら約 3〜4 万ページになる。

ここで問題が残る。「123456 のあたりを開く」と言ったが、3 万ページのうち何ページ目に 123456 があるかは、どうやって知るのか。 並んでいるとはいえ、端から開けていたらまた全部読むことになる。

木: 目次の目次

答えは「ページの目次を作る」だ。3 万ページそれぞれの先頭のキー(1 ページ目は 1、2 ページ目は 17、3 ページ目は 33、…)を並べた目次ページを作る。 目次のエントリも 1 ページに約 1,000 個入るので、3 万ページの目次は約 30 ページ。さらにその 30 ページの目次を作ると、1 ページに収まる。

この構造が B-tree で、いちばん上を根(root)、いちばん下を葉(leaf)と呼ぶ。123456 を探す手順はこうなる。

1. 根のページを読む。123456 を含む中間ページがどれかわかる。
2. その中間ページを読む。123456 を含む葉ページがどれかわかる。
3. その葉ページを読む。123456 のエントリが 64 個見つかる。

読んだページは 3 枚。3,200 万キーあっても、3 枚で目的の場所に着く。これが索引検索が速い理由のすべてだ。

なぜ「1,000 倍になっても 1 段しか増えない」のか

1 ページに約 1,000 エントリ入るので、段が 1 つ増えるごとに扱えるキーの数が 1,000 倍になる。

テーブルが 3,200 万行から 320 億行に(1,000 倍に)育っても、検索で読むページは 3 枚から 4 枚になるだけ。一方 Table scan は 13 万ページから 1.3 億ページになる。 「片方は足し算で増え、もう片方は掛け算で増える」のが、行数が育つほど差が開く理由だ。

範囲検索も速い理由

葉ページはキー順に並んでいて、隣のページへのリンクを持っている。だから WHERE customer_id BETWEEN 100000 AND 101000 のような範囲検索は、 木を降りて 100000 の位置に着いたあと、葉を右へ辿っていくだけで済む。>、<、BETWEEN、LIKE 'abc%'(前方一致)はこの仕組みで速い。

索引が使えない書き方

索引は「列の値」の順に並んでいる。列の値を加工した結果では並んでいない。だから WHERE の左辺に関数や計算を書くと、木を降りる手がかりがなくなり、Table scan に戻る。

-- 効かない: ordered_at の値ではなく YEAR(ordered_at) の値で比べている
WHERE YEAR(ordered_at) = 2026

-- 効く: ordered_at の値そのものの範囲
WHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01'

-- 効かない: customer_id + 0 という別の値で比べている
WHERE customer_id + 0 = 123456

-- 効かない: 前方が不定だと並び順を使えない
WHERE name LIKE '%洗剤'

索引の一覧を見る

2 行出る。読むべき列はこの 4 つ。

Cardinality は次章以降で重要になる。DB は「customer_id = X で何行ヒットするか」を、3,200 万 ÷ Cardinality(50 万)≒ 64 行、と見積もる。 この見積もりを使って「索引を使うべきか、Table scan のほうが速いか」を判断する。見積もりが外れると判断も外れる。それが第 3 章の障害だ。

テーブルは 16KB のページに主キー順で置かれている。索引は「列の値 → 主キー」を値の順に並べ、目次の目次(B-tree)を作ったもので、検索コストは木の段数 + ヒット行数。

確認

次の章へ

この章では実行計画の「一番下の行」だけ見た。次の章で、計画全体の読み方を覚える。cost、rows、actual time、loops の 4 つの数字が読めると、遅いクエリのどこで時間が消えたかが自分でわかる。