SQLインデックスとEXPLAIN QUERY PLAN：100万行で実測

シリーズ：SQL 講座 4/6（中級）。 SELECT・JOIN・GROUP BY は書けるが、「ある日突然遅くなったクエリ」に出会った方向けです。読み終えると EXPLAIN QUERY PLAN を読み、どの列にどの順番でインデックスを張るかを決め、インデックスが無視される 4 つの書き方を見抜けるようになります。

社内の業務システムで「注文履歴の画面だけ重い」「月次バッチが年々遅くなる」と言われたとき、あるいはレビューで「その列、インデックス張ってある？」と聞かれて確かめ方が分からなかったとき、この記事が役に立ちます。インデックスは SQL の性能改善でいちばん効く手段ですが、いちばん間違えやすい手段でもあります。正しいインデックスはクエリを約 1,900 倍速くし、間違ったインデックスは別のクエリを 10 倍遅くしました。数字はすべて、固定の式で作った 100 万行のテーブルで自分たちが計ったもので、同じデータを誰でも再現できます。

実行環境： SQLite 3.51.0（sqlite3 CLI と、Python 3.9.6 に同梱の SQLite。こちらも 3.51.0）、Apple Silicon Mac（M1 Pro）、macOS。時間は中央値で、テーブルの中身は毎回同じになります。

100 万行の注文テーブルを 1 秒で作る

SQLite の random() にはシード（乱数の種）を指定できないため、線形合同法（前の値 × 1103515245 + 12345 を 2³¹ で割った余り、という決まった式）を再帰 CTE で回してデータを作ります。make_orders.sql として保存してください。

-- 1,000,000 shop orders, deterministic (no random(): an LCG on the row number)
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL,
  amount      INTEGER NOT NULL,   -- yen
  email       TEXT    NOT NULL,
  created_at  TEXT    NOT NULL    -- 'YYYY-MM-DD HH:MM:SS'
);
WITH RECURSIVE n(i, r) AS (
  SELECT 1, 42
  UNION ALL
  SELECT i + 1, (r * 1103515245 + 12345) % 2147483648 FROM n WHERE i < 1000000
)
INSERT INTO orders (id, customer_id, status, amount, email, created_at)
SELECT i,
       1 + r % 50000,
       CASE (r / 7) % 20 WHEN 0 THEN 'cancelled' WHEN 1 THEN 'refunded'
                         WHEN 2 THEN 'pending' ELSE 'paid' END,
       100 + (r / 13) % 20000,
       'User' || (1 + r % 50000) || '@example.com',
       datetime('2025-01-01', '+' || ((r / 17) % 31536000) || ' seconds')
FROM n;

$ time sqlite3 shop.db < make_orders.sql
sqlite3 shop.db < make_orders.sql  0.73s user 0.08s system 99% cpu 0.824 total
$ sqlite3 shop.db "SELECT count(*), count(DISTINCT customer_id), min(created_at), max(created_at) FROM orders;"
1000000|50000|2025-01-01 00:00:00|2025-12-31 23:59:44

顧客 5 万人・2025 年 1 年分・100 万件の注文で、ファイルは 62.6MB です（再帰 CTE そのものは第 6 回で扱います）。

EXPLAIN QUERY PLAN の読み方：SCAN と SEARCH

インデックスとは、1 つ以上の列を並べ替えて別に持っておく索引で、各項目が元の行（SQLite では rowid）を指しています。並んでいるので、全行を読まずに二分探索できます。EXPLAIN QUERY PLAN（以下 EQP）は、クエリを実行せずに「どう実行するつもりか」を DB に尋ねる命令です。

sqlite> .timer on
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SCAN orders
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.052 user 0.036612 sys 0.013766

SCAN orders は「テーブルを全部読む」という意味です。インデックスを作ってもう一度聞きます。

sqlite> CREATE INDEX idx_orders_customer ON orders(customer_id);
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.000 user 0.000018 sys 0.000006

EQP で見るべき言葉は次のとおりです。

4 行目はよく誤解されます。「COVERING INDEX」と出ていても、頭が SCAN なら全項目を読んでいます。

CLI の .timer は 1ms 単位で粗すぎるので、以降の時間は同じ SQLite を使う Python の小さな計測関数で取りました。数回空回ししてページキャッシュを温めてから、中央値を返します。

import sqlite3, statistics, time
con = sqlite3.connect("shop.db")
def med(sql, params_list, reps=5):
    for p in params_list[:3]:
        con.execute(sql, p).fetchall()                    # warm-up
    ts = []
    for _ in range(reps):
        for p in params_list:
            t = time.perf_counter(); con.execute(sql, p).fetchall()
            ts.append((time.perf_counter() - t) * 1000)
    return statistics.median(ts)                          # ms

random.Random(42) で選んだ顧客 ID 20 件 × 5 回の中央値です。

作成 0.5 秒とディスク 11MB で、約 1,900 倍です。

複合インデックスは列の順番がすべて

(customer_id, created_at) の複合インデックスは、まず顧客順、同じ顧客の中では日付順に並んだ名簿です。「顧客 5 の 12 月以降」を直接引けます。(created_at, customer_id) は日付が先なので、同じ質問では 12 月の先頭まで飛んだあと、12 月の全項目について顧客を確かめるしかありません。

(customer_id, created_at):    0.010 ms   SEARCH orders USING INDEX i (customer_id=? AND created_at>?)
(created_at, customer_id):    3.219 ms   SEARCH orders USING INDEX i (created_at>?)

どちらも SEARCH なので「インデックスは使われている」で止まってはいけません。括弧の中を読むと、2 つ目は created_at>? しか使っていません。原則は 「= の列が先、範囲・並べ替えの列が後」 です。

複合インデックスは、その左端からの部分（leftmost prefix）でも使えるので、単独の customer_id インデックスは不要になります。idx_orders_customer を消したあとの EQP：

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE customer_id = 5;
`--SEARCH orders USING COVERING INDEX idx_orders_cust_created (customer_id=?)
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE created_at >= '2025-12-01';
`--SCAN orders USING COVERING INDEX idx_orders_cust_created

2 つ目は先頭列を飛ばしているのでスキャンです。同じインデックスでソートも消えます。

-- index on (customer_id) only
SELECT id, amount, created_at FROM orders WHERE customer_id = 5 ORDER BY created_at DESC LIMIT 5;
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY
-- index on (customer_id, created_at)
`--SEARCH orders USING INDEX idx_orders_cust_created (customer_id=?)

迷ったら CLI の .expert（インデックス提案機能）に聞けます。提案はまさにこの形でした。

sqlite> .expert
sqlite> SELECT sum(amount) FROM orders WHERE customer_id = 12345 AND created_at >= '2025-06-01' ORDER BY created_at;
CREATE INDEX orders_idx_30ddf7e6 ON orders(customer_id, created_at);

SEARCH orders USING INDEX orders_idx_30ddf7e6 (customer_id=? AND created_at>?)

よくある間違い：インデックスはあるのに使えない書き方

以下はすべて customer_id・email・created_at にインデックスがある状態で実行しました。

1. 列を関数で包む。 date(created_at) = '2025-03-01' と書いた。何が起きるか： インデックスは created_at の順に並んでいて date(created_at) の順ではないので、スキャンになり 86.4ms。直し方： 生の列に対する範囲条件に書き換えると 0.069ms。

-- SCAN orders USING COVERING INDEX idx_orders_created     86.407 ms
SELECT count(*) FROM orders WHERE date(created_at) = '2025-03-01';
-- SEARCH ... (created_at>? AND created_at<?)                0.069 ms
SELECT count(*) FROM orders WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';

関数がどうしても必要なら（メールアドレスを大文字小文字無視で探すなど）、式そのものにインデックスを張ります。

sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SCAN orders
Run Time: real 0.160 user 0.138400 sys 0.013989
sqlite> CREATE INDEX idx_orders_email_lower ON orders(lower(email));
sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SEARCH orders USING INDEX idx_orders_email_lower (<expr>=?)
Run Time: real 0.000 user 0.000098 sys 0.000058

2. 普通のインデックスに LIKE。 LIKE 'User123@%' は前方一致に見えますがスキャンでした。SQLite の LIKE は既定で ASCII の大文字小文字を区別せず、普通のインデックスは区別する順（BINARY 照合順序）で並んでいるため、使えないのです。大文字小文字を区別する GLOB か、COLLATE NOCASE で作ったインデックスなら使えます（クエリ・プラン・.timer の実時間を 1 行にまとめた抜粋）。

WHERE email LIKE 'User123@%'             `--SCAN orders                                    0.081 s
WHERE email GLOB 'User123@*'             `--SEARCH ... idx_orders_email (email>? AND email<?)
-- after CREATE INDEX idx_orders_email_nocase ON orders(email COLLATE NOCASE);
WHERE email LIKE 'user123@%'             `--SEARCH ... idx_orders_email_nocase (email>? AND email<?)
WHERE email LIKE '%123@example.com'      `--SCAN orders                                    0.103 s

最後の行は越えられない壁です。先頭が % だと、文字列の先頭で並んだ B-tree インデックスは役に立ちません。全文検索（SQLite なら FTS5）か、文字列を逆順にした列が必要です。日本語の部分一致検索がこれにあたる場面は多いはずです。

3. 列をまたぐ OR。 customer_id = 7 OR email = 'User8@example.com' は両方にインデックスがあるので MULTI-INDEX OR。customer_id = 7 OR amount = 500 は SCAN orders でした。片側にインデックスがないと、条件全体が全件スキャンになります。

4. 複合インデックスの先頭列を使わない。 前の節のとおりです。

逆に SQLite で壊れない例：WHERE customer_id = '7' は文字列で比べても SEARCH でした。INTEGER 列の型アフィニティが文字列を数値に変換するからです。MySQL では文字列型の列を数値と比べるとインデックスが効かなくなる有名な罠がありますが、ここでは実行していません。

インデックスでかえって遅くなるとき

いちばん意外だった計測です。created_at にインデックスがある状態で、期間を広げながら amount を合計しました。NOT INDEXED / INDEXED BY で各方式を強制し、7 回の中央値です。

普通のインデックスでは、一致した項目ごとに amount を取りにテーブルをばらばらの順で引きに行きます。テーブルの約 8%（1 か月）で、すでに全件を順に読むより 2 倍遅くなりました。しかも SQLite は ANALYZE 後もこの遅いプランを自分で選びました。

sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-07-01';
`--SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
497165|5022432858
Run Time: real 1.186 user 0.536726 sys 0.474209
sqlite> ANALYZE;
sqlite> SELECT * FROM sqlite_stat1;
orders|idx_orders_created|1000000 1
-- EQP again: same SEARCH ... USING INDEX idx_orders_created
Run Time: real 1.071 user 0.497285 sys 0.439523

sqlite_stat1 は「1 キーあたり何行か」しか持たず、日付の分布を知らないので、6 か月がテーブルの半分だと判断できません（今回の SQLite はサンプルを保存する SQLITE_ENABLE_STAT4 なしでビルドされていました）。対策は 2 つ。インデックスをカバリングにする（22.7ms）か、単項プラスでこの条件にインデックスを使わせないことです。

sqlite> SELECT count(*), sum(amount) FROM orders WHERE +created_at >= '2025-01-01' AND +created_at < '2025-07-01';
`--SCAN orders
Run Time: real 0.095 user 0.077709 sys 0.013404

インデックスが書き込みに払わせるコスト

INSERT のたびに、すべてのインデックスにも挿入が起きます。100 万行のテーブルに 10 万行を 1 トランザクションで追加しました（各 3 回）。

インデックス 3 つで挿入は約 8 倍遅くなり、ファイルは 2 倍以上になりました。高いのは文字列のインデックス（email、created_at）です。「念のため全部の列に」は、書き込みの多いテーブルでは逆効果です。

どのインデックスを作るか：判断表

練習問題

1. ある画面が SELECT id, amount, created_at FROM orders WHERE status = 'paid' AND customer_id = ? ORDER BY created_at DESC LIMIT 3 を実行します。USE TEMP B-TREE が出ないプランにするインデックスを 1 つ答えてください。
2. WHERE substr(email, 1, 7) = 'User123' は email にインデックスがあってもスキャンになります。新しいインデックスを作らずに、インデックスが使われる形に書き換えてください。

解答

1. CREATE INDEX idx_orders_cust_status_created ON orders(customer_id, status, created_at); 等号の 2 列を先に、並べ替えの列を最後に置きます。実行したプランは SEARCH orders USING INDEX idx_orders_cust_status_created (customer_id=? AND status=?) で一時 B-tree はなく、顧客 5 の新しい支払済み注文 3 件がそのまま並んで返りました。
2. WHERE email >= 'User123' AND email < 'User124'。User123 で始まる文字列はすべてこの 2 つの値の間に並びます。どちらも 2,276 行を返し、プランは SCAN … USING COVERING INDEX idx_orders_email から SEARCH … (email>? AND email<?) に変わりました。

正直な限界とバージョンの注意

• 実行したのは SQLite 3.51.0 だけです。EQP の木構造の表示（`--）は 3.24 からで、それより古い版は平らな表で出力します。.expert は CLI の機能で、最小構成のビルドにはないことがあります。
• 式インデックスは SQLite 3.9 以降です。NOT INDEXED・INDEXED BY・単項プラスは SQLite 固有で、MySQL には USE INDEX / IGNORE INDEX があり、PostgreSQL には標準のインデックスヒントがありません（ここでは実行していません）。
• PostgreSQL や MySQL はヒストグラムを持つので、6 か月分のケースでは普通のインデックスを避ける可能性が高いですが、試していません。約 8% という分岐点も行の幅・キャッシュ・ストレージで変わるので、自分のテーブルで測ってください。
• データは合成です。線形合同法は良い乱数ではなく、同じ顧客の金額が繰り返されます。インデックスの挙動には影響しませんが、統計には使わないでください。
• 時間は他の処理も動いている 1 台のマシンでの中央値です。比率は信頼してよく、ミリ秒の絶対値は目安です。

インデックスと同じくらい大事なパラメータ化クエリについては PDO + SQLite と SQL インジェクション を、学習全体の中での SQL の位置づけは プログラミング学習ロードマップ をどうぞ。

前回： 第 3 回：JOIN・GROUP BY・サブクエリ · 次回： 第 5 回：トランザクション・分離レベル・ロック