実行計画の読み方 — EXPLAIN QUERY PLAN

本記事で使うデータ — perf_sales（5 万行の売上）

クエリが速いか遅いかを判断するとき、最初に見るのが実行計画（クエリプラン）です。

データベースは SQL を受け取ると「どのテーブルをどの順に、どうやって読むか」という手順を内部で決めます。

その手順を文字で見せてくれるのがEXPLAIN QUERY PLANです。

クエリの前にこの語を付けて実行すると、結果の代わりに実行手順が表示されます。

演習に入る前に、本記事で使うperf_salesテーブルの列定義とデータのサンプルを確認しておきます。データ生成に少し時間がかかるので、最初の実行は数秒待つことがあります。

① PRAGMA table_info(perf_sales);で列名・型・主キーを確認してください。

② SELECT * FROM perf_sales LIMIT 5;で先頭 5 行のデータをプレビューしてください。

③ SELECT COUNT(*) FROM perf_sales;で行数が 5 万件あることを確認してください。

SCAN と SEARCH — フルスキャンとインデックススキャン

実行計画でまず覚えるのがSCANとSEARCHの 2 語です。

SCAN perf_salesはテーブルの先頭から末尾まで全行を順に読む（フルスキャン）という意味です。

索引が無い列で絞り込むと、データベースは「どの行が該当するか分からない」ため、5 万行すべてを 1 行ずつ確認します。

一方SEARCH perf_sales USING INDEX 索引名 (列=?)は、索引をたどって該当行だけにピンポイントで到達する（インデックススキャン）という意味です。

索引（CREATE INDEX）を作ると、その列で絞る条件は全行を見ずに済むようになり、実行計画がSCANからSEARCH ... USING INDEXに変わります。

-- region で絞ると、索引が無いので全行を読む
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- region に索引を作ってから同じ計画を見ると
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

「特定担当者の売上件数を数えるクエリが遅い」という想定で、索引を作る前と後の実行計画を比べます。1 回の実行で索引作成と計画確認を完結させてください。（正しく実行できれば解説が表示されます）

① まずEXPLAIN QUERY PLANを付けて、emp_idが 7 の件数を数えるクエリの計画を確認してください。索引が無いのでSCANになります。

② 次にDROP INDEX IF EXISTSで同名索引を消してからCREATE INDEXでemp_idに索引を作り、もう一度同じクエリのEXPLAIN QUERY PLANを実行してください。SEARCH ... USING INDEXに変わることを確認してください。

USE TEMP B-TREE — 並べ替え用の一時領域

USE TEMP B-TREE FOR ORDER BY（一時 B ツリー）は、ORDER BYの対象列に索引が無いため、結果を並べ替えるための一時的な作業領域を作っている、という意味です。

5 万行を一時領域にいったん入れて並べ直すため、行数が多いほど重くなります。

並べ替えに使える索引があると、この行は計画から消え、索引の並び順をそのまま使えるようになります。

-- amount 昇順 上位 10 件 → 索引が無いので並べ替え用の一時領域が必要
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- amount に索引を作ると並べ替えに索引の順序をそのまま使える
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE は計画から消える

「金額の小さい順に上位 10 件を出すクエリ」の実行計画を、索引なしの状態で確認します。下の 3 演習を順に流して、索引前後で計画がどう変わるかを比べます。

① 1 つ目のコンソールでEXPLAIN QUERY PLANを付け、perf_salesをamountの昇順に並べて先頭 10 件（sale_id, amount）を取るクエリの計画を実行してください。

出力にSCAN perf_salesと並べ替え用のUSE TEMP B-TREE FOR ORDER BYの 2 行が出ていることを確認してください。並べ替え列amountに索引が無いため、5 万行を一時領域に入れて並べ直しているという読み方です。

② 2 つ目のコンソールでDROP INDEX IF EXISTS ix_amount;で前回分を消してから、perf_salesのamount列にix_amountという名前のインデックスを作成してください。

索引作成は 1 度きりのコストなので、純粋な計画比較には含めません。次の 3 つ目で同じクエリの計画を取り直します。

③ 3 つ目のコンソールで、① と同じEXPLAIN QUERY PLANを再実行してください。

計画からUSE TEMP B-TREE FOR ORDER BYの行が消え、SEARCH perf_sales USING INDEX ix_amountのように索引を使う計画に変わっていることを確認します。索引の並び順をそのまま使えるので、一時領域での並べ替えが不要になっています。

USING COVERING INDEX — テーブル本体を読まない索引

USING COVERING INDEX（インデックスオンリースキャン、英語ではカバリングインデックスとも）は、クエリが必要とする列がすべて索引の中に含まれていて、テーブル本体を読まずに索引だけで結果を作れたという意味です。

索引はテーブルから必要な列だけを抜き出した小さなコピーなので、本体を読まない分だけ速くなります。

SEARCH ... USING INDEXよりさらに進んだ状態で、計画にUSING COVERING INDEXが出たら最も効率の良い読み方ができています。

-- emp_id だけの索引 → 索引で行を見つけて、続いて本体から amount を読む
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (本体も読む)

-- (emp_id, amount) の複合索引 → 必要列が全部索引内なので本体を読まない
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

「emp_idで絞ってamountを出すだけ」のクエリを、テーブル本体を読まずに索引だけで完結する計画に変えます。1 回の実行で索引作成と計画確認を完結させてください。

① DROP INDEX IF EXISTSで同名索引を消してから、emp_idとamountの 2 列を順に並べた複合索引をCREATE INDEXで作成してください。

② EXPLAIN QUERY PLANを付けて、emp_idが 7 の行のemp_id, amountだけを取るクエリの計画を確認してください。必要な列がすべて索引の中にあるため、本体を読まずに索引だけで完結する計画になることを読み取ってください。

サブクエリの計画 — CORRELATED と MATERIALIZE

サブクエリを含むクエリの計画には専用の語が出ます。

CORRELATED SCALAR SUBQUERY（相関スカラサブクエリ）は、外側の行ごとに値が変わるサブクエリを各行で実行し直しているという意味です。

外側が 5 万行ならサブクエリも 5 万回走る可能性があり、計画にこの語が出たら書き換えの検討対象になります。

一方MATERIALIZE（マテリアライズ、実体化）は、サブクエリや CTE の結果を一度だけ計算して一時表に保存し、それを使い回すという意味です。

同じ中間結果を何度も参照するときに、計算を 1 回で済ませる最適化です。

計画にこの 2 語のどちらが出るかで、サブクエリが「毎回計算されている」のか「1 回で済んでいる」のかが読み取れます。

-- 相関スカラサブクエリ: 外側の行ごとに「その region の最大額」を引き直す
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> CORRELATED SCALAR SUBQUERY という行が出る

-- 派生表（FROM 句のサブクエリ）は一度だけ計算される
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> MATERIALIZE という行が出ることがある

「各行について、その担当者の平均金額より大きい売上だけを上位 10 件取り出す」というクエリの計画を確認し、サブクエリが行ごとに再実行される構造を読み取ります。

① EXPLAIN QUERY PLANを付けて、perf_salesの各行に対し「同じemp_idの平均amount」を相関サブクエリで求め、amountがそれを上回る行を 10 件取るクエリの計画を確認してください。

② 出力に相関スカラサブクエリを示す行と、外側 / 内側それぞれのSCAN perf_salesが出ていることを読み取ってください。外側の行ごとに内側のサブクエリが評価されるため、件数の多いテーブルでは重い構造であることを確認してください。

他の RDBMS との対応 — MySQL / PostgreSQL / Oracle

本記事で見た計画用語は SQLite のEXPLAIN QUERY PLANのものですが、同じ概念は MySQL / PostgreSQL / Oracle でも別の名前で表示されます。

まず各 DB で実行計画を出すコマンドを確認したうえで、計画に出てくる用語の早見表を見比べると、別 DB に移ったときにそのまま読み解けます。

各 DB での実行計画の表示方法

• SQLite: クエリの前にEXPLAIN QUERY PLANを付ける（例: EXPLAIN QUERY PLAN SELECT ...;）。実行はせず計画だけを表示する。EXPLAIN単体だと低水準のバイトコードが出るので、人が読むのはEXPLAIN QUERY PLANの方
• MySQL: クエリの前にEXPLAINを付ける（例: EXPLAIN SELECT ...;）。MySQL 8.0+ はEXPLAIN ANALYZE SELECT ...;で実測時間と実際の行数も得られる。JSON 形式が欲しいときはEXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL: クエリの前にEXPLAINを付ける（例: EXPLAIN SELECT ...;）。これは推定プランのみ。実測も見るならEXPLAIN ANALYZE SELECT ...;、I/O バッファまで詳しく見るならEXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle: EXPLAIN PLAN FOR <クエリ>;で計画を内部表に保存し、続いてSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);で表示する 2 段構え。簡単に見たいときはSET AUTOTRACE ONセッションオプションも使える

計画に出てくる用語の対応表

用語は違っても、データベースが内部でしていることはほぼ同じです。「全行を読んでいるのか、索引で絞っているのか、並べ替えに作業領域が要るのか、サブクエリを何度も走らせていないか」を、各 DB のEXPLAIN出力から読み取れるようになるのが本記事の到達点です。