SQL Lab — MySQL を大容量で叩いて学ぶ

この章で答える問い

索引(インデックス)とは、何という実物か
DB が「全部読んだ」のか「索引で引いた」のかは、どこでわかるか
索引はいつ作られ、何を代償にするか

実物 1: 名前と列を持つ物

orders にある索引

期待: 2 行。PRIMARY と customer_id

⌘/Ctrl + Enter で実行

SHOW INDEX FROM orders

2 行返る。Key_name が索引の名前、Column_name がどの列の索引か。orders には「PRIMARY(id の索引)」と「customer_id の索引」がある。主キーは自動で索引になる。customer_id の索引は、第 4 章の外部キーを宣言したときに MySQL が自動で作ったものだ。上のパネルで ▪ が付いている列がそれで、索引とは「テーブルとは別に存在する、名前と対象列を持つ物」だとわかる。

実物 2: ディスク上のバイト数

テーブル本体と索引の大きさ

期待: orders の index_length に数字がある

⌘/Ctrl + Enter で実行

SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'lab_basics' AND table_name = 'orders'

data_length が行そのもの、index_length が索引の大きさ(バイト)。索引はディスク上に本体とは別のバイト列として存在する。この章の後半で索引を 1 つ足すと、この数字が増えるのを確かめる。

実物 3: 探し方の記録

この章からラボに「推定プラン」「実測プラン」のタブが出る。DB が「どうやって行を探したか」の記録で、第二部ではこれを読み続ける。今は 1 か所だけ見る。実測プランの一番下の行が、Table scan(全部読んだ)か、Index lookup(索引で引いた)か。

索引のない列で探す

期待: 実測プランの一番下: Table scan on orders。読んだ行数 50

⌘/Ctrl + Enter で実行

SELECT * FROM orders WHERE status = 'paid'

主キーで探す

期待: Single-row index lookup。読んだ行数 1

⌘/Ctrl + Enter で実行

SELECT * FROM orders WHERE id = 10

customer_id で探す

期待: Index lookup on orders using customer_id。読んだ行数 8

⌘/Ctrl + Enter で実行

SELECT * FROM orders WHERE customer_id = 3

同じ SELECT でも、探し方が違う。status には索引がないので 50 行全部を読んで(Table scan)、status が paid の行を残した。id と customer_id には索引があるので、該当する行だけを読んだ。計器の「読んだ行数」が、それをそのまま表している。

索引とは、辞書の巻末索引

Table scan は行を先頭から全部読む。Index lookup は索引で id を引き、その行だけ読む。

テーブルの行は id の順に置かれている。だから id で探すのは速い。status や ordered_at の順には並んでいないので、それらで探すには全行を順に見るしかない。索引は「ordered_at の値 → その行の id」の一覧を、ordered_at の順に並べて別に持っておく仕組みで、辞書の巻末索引と同じだ。索引を引けば、目的の行がどこにあるかがすぐわかる。

ここでは比喩で止める。索引の中身(ページ、キー、木)は第二部 1 章で開ける。50 行では、どう探しても一瞬で、時間の差は出ないからだ。第二部 1 章で、同じクエリを 3,200 万行で叩いて 4 秒と 0.01 秒の差を見る。

索引を作る

ordered_at に索引を作る

期待: エラーなく終わる

⌘/Ctrl + Enter で実行

CREATE INDEX idx_ordered ON orders (ordered_at)

ordered_at で探す(索引あり)

期待: Index lookup (idx_ordered)。読んだ行数 1

⌘/Ctrl + Enter で実行

SELECT * FROM orders WHERE ordered_at = '2026-08-28 21:55:00'

大きさをもう一度

期待: index_length が増えている

⌘/Ctrl + Enter で実行

SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'lab_basics' AND table_name = 'orders'

CREATE INDEX 名前 ON テーブル (列)。作ると、実測プランの探し方が変わり、ディスク上のバイト数が増える。索引は本当に「別の物」として作られている。

代償

索引は行を足すたびに直す必要がある。索引が 5 本あれば、1 行の INSERT が 6 か所への書き込みになる。しかもディスクとメモリを食う。だから全列には張らず、「よく WHERE に書く列」「JOIN でつなぐ列」「ORDER BY する列」に絞る。第二部 3 章では「張ったせいで遅くなる」ケース、8 章では書き込みが何倍遅くなるかを実測する。

後始末

期待: エラーなく終わる

⌘/Ctrl + Enter で実行

DROP INDEX idx_ordered ON orders

索引は、テーブルとは別にディスク上に実在する「列の値 → 行の場所」の一覧。実測プランを見れば、全部読んだか索引で引いたかがわかる。

確認

あるクエリが全部読んだのか索引で引いたのかは、どこを見ればわかるか。

実測プランの一番下の行を見る。Table scan なら行を先頭から全部読んでおり、Index lookup なら索引で引いて該当する行だけを読んでいる。計器の「読んだ行数」も同じことを表す。

索引を作ると増えるものは何か。それはどこで確かめられるか。

索引はテーブル本体とは別のバイト列としてディスク上に作られるので、information_schema で見た index_length が増える。索引はただの得ではなく、場所と、行を足すたびに直す手間を代償に払っている。

開発環境で速かったクエリが本番で遅い、を動作確認で見つけられないのはなぜか。

行が少なければどう探しても一瞬で、探し方の差が時間に現れないから。だから WHERE / JOIN / ORDER BY に使う列に索引があるかは、動かして確かめるのではなくコードレビューで見る。

次の章へ

ここまでの SQL は、この画面から手で送っていた。本番ではアプリのコードが送る。次の章では、アプリから DB を使うときに起きる 3 つの事故 (SQL インジェクション、ORM が何を送っているか知らない、N+1)を見て、第二部へ渡る。