[SQLのはじめ方] Part21: クエリの実行計画とEXPLAINの使い方

はじめに

SQLを書くことに慣れてくると、「あれ？なんだかこのSQL、実行が遅いな… 🤔」と感じることがあるかもしれません。特に扱うデータ量が多くなったり、クエリが複雑になったりすると、パフォーマンスの問題は避けて通れません。

そんな時に役立つのが「実行計画」の確認です。データベースがSQLをどのように実行しようとしているかを知ることで、ボトルネックとなっている箇所を発見し、改善につなげることができます。このセクションでは、クエリの実行計画とは何か、そしてそれを表示するためのEXPLAINコマンドの使い方を学びましょう！🚀

クエリ実行計画とは？

クエリ実行計画とは、データベース管理システム（DBMS）が、あなたが書いたSQL文を実行するために内部的に作成する「手順書」のようなものです。 DBMSの「クエリオプティマイザ」と呼ばれる機能が、SQLを解析し、テーブルの統計情報（データの量や分布など）を考慮して、最も効率的（低コスト）だと判断した処理手順を選択します。

実行計画には、以下のような情報が含まれています。

どのテーブルにアクセスするか
テーブルへのアクセス方法（インデックスを使うか、全件スキャンするかなど）
複数のテーブルを結合する場合、どの順番で、どの方法（Nested Loop Join, Hash Joinなど）で結合するか
ソートや集計などの処理をどのように行うか
各処理ステップで推定されるコスト（処理時間やリソース消費量の見積もり）や処理対象行数

この計画を理解することで、なぜクエリが遅いのか、どこを改善すれば速くなるのかを知る手がかりを得られます。💡

EXPLAINコマンドの使い方

多くのリレーショナルデータベース（RDBMS）では、EXPLAINコマンド（またはEXPLAIN PLAN FORなど）を使って、特定のSQL文に対する実行計画を確認できます。基本的な使い方はとても簡単で、実行計画を知りたいSQL文の前にEXPLAINキーワードを付けるだけです。

基本構文 (MySQL, PostgreSQLなど):


EXPLAIN <調べたいSQL文>;

例えば、usersテーブルからIDが1のユーザーを取得するSELECT文の実行計画を見たい場合は、次のように実行します。


EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAINを実行しても、実際のデータ操作（SELECT, INSERT, UPDATE, DELETEなど）は行われません。あくまで「計画を表示する」だけなので、本番環境のデータに対しても比較的安全に実行できます。（ただし、システムによってはEXPLAIN ANALYZEのように実際にクエリを実行して詳細な情報を取得するオプションもあり、この場合はデータ変更を伴うクエリに注意が必要です。）

⚠️ 注意: EXPLAIN ANALYZE (PostgreSQLなど) や EXPLAIN FORMAT=JSON (MySQL) のように、オプションを付けることで、実際にクエリを実行した上での詳細な統計情報（実際の実行時間や行数など）を取得できる場合があります。これにより、プランナーの見積もりと実際の実行結果を比較できますが、INSERT, UPDATE, DELETE文に対して実行するとデータが変更されてしまうため、BEGINとROLLBACKでトランザクションを管理するなど、注意が必要です。

EXPLAINの出力結果の見方（主要な項目）

EXPLAINの出力形式や内容は、使用しているデータベース製品（MySQL, PostgreSQL, SQL Server, Oracleなど）によって異なります。ここでは、MySQLとPostgreSQLで共通して見られる、または重要ないくつかの項目について解説します。

ポイント: MySQLは表形式、PostgreSQLはツリー形式（テキスト）で表示されることが多いです。PostgreSQLの方が実行順序が視覚的に分かりやすい場合があります。

MySQLの主な出力項目 (表形式)

項目名	説明	注目ポイント
`id`	SELECTクエリの識別子（実行順序を示す番号）	数字が大きいものから実行されることが多い（JOINなどがない場合）。
`select_type`	SELECTの種類（SIMPLE, SUBQUERY, JOINなど）	クエリの構造を理解するのに役立つ。
`table`	アクセスしているテーブル名	どのテーブルに対する操作かを示す。
`type`	テーブルへのアクセス方法（結合タイプ）	超重要！ `ALL` (フルテーブルスキャン) は遅い原因になりやすい。`index`, `range`, `ref`, `eq_ref`, `const`/`system` の順に効率が良いとされる。`ALL`や`index`の場合はインデックスが効いていない可能性がある。
`possible_keys`	利用可能なインデックスの候補	ここに適切なインデックスが表示されているか確認。
`key`	実際に使用されるインデックス	重要！ `NULL`の場合、インデックスが使われていない。`possible_keys`にあっても`key`が`NULL`なら要注意。
`key_len`	使用されるインデックスの長さ（バイト単位）	複合インデックスの一部だけが使われているかなどを判断する材料になる。
`ref`	`key`列で指定されたインデックスを検索するために使われるカラムや定数	結合条件や検索条件がどのようにインデックスに適用されているかを示す。
`rows`	MySQLが見積もった、処理対象となる行数	重要！この値が大きい場合、処理に時間がかかる可能性がある。インデックスが適切に使われていないと非常に大きくなることがある。
`filtered`	`rows`で示された行のうち、条件で絞り込まれて残る行の割合（%）	低い値は、多くの行を読み取った後に多くが捨てられることを意味し、非効率な場合がある。
`Extra`	追加情報（非常に重要）	超重要！ `Using filesort` (ファイルソート発生), `Using temporary` (一時テーブル使用) はパフォーマンス低下の原因になりやすい。`Using index` (カバリングインデックス) は効率が良い。`Using where` (WHERE句での絞り込みあり) など、多くの情報が含まれる。

PostgreSQLの主な出力項目 (テキスト/ツリー形式)

PostgreSQLのEXPLAINは、処理のノードがツリー構造で表示されます。内側のインデントが深いノードから先に実行されます。


-- 例: EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18.38 rows=318 width=520) (actual time=0.010..0.151 rows=318 loops=1)
   Filter: (age > 30)
   Rows Removed by Filter: 682
 Planning Time: 0.078 ms
 Execution Time: 0.197 ms
(6 rows)

ノードの種類 (例: Seq Scan, Index Scan, Bitmap Heap Scan, Hash Join, Nested Loop, Sort): 実行される具体的な操作を示します。Seq Scanはテーブル全体のスキャン（フルスキャン）を意味し、遅い原因となり得ます。Index ScanやBitmap Heap Scanはインデックスが利用されていることを示します。
cost: スタートアップコスト..トータルコスト の形式で表示されます。プランナーによるコストの見積もり値です。単位は任意ですが、ディスクページフェッチに基づいています。トータルコストが低いプランが選ばれます。
rows: そのノードが返す推定行数。
width: 返されるデータの平均幅（バイト単位）。
(actual time=...) (ANALYZEオプション使用時): 最初の行を返すまでの時間..全行を返し終えるまでの時間（ミリ秒）。実際の実行時間を示します。見積もり(cost)と大きく乖離していないか確認します。
(rows=...) (ANALYZEオプション使用時): 実際に返された行数。見積もりと大きく乖離している場合、統計情報が古いなどの可能性があります。
(loops=...) (ANALYZEオプション使用時): そのノードが実行された回数。
Filter, Index Cond など: どのような条件でデータが絞り込まれているかを示します。
Planning Time (ANALYZEオプション使用時): 実行計画の作成にかかった時間。
Execution Time (ANALYZEオプション使用時): クエリ全体の実行にかかった時間。

なぜ実行計画が重要なのか？

実行計画を理解することは、SQLのパフォーマンスチューニングにおいて非常に重要です。 EXPLAINの結果を見ることで、以下のような問題点を発見できます。

インデックスが使われていない (フルテーブルスキャン): type: ALL (MySQL) や Seq Scan (PostgreSQL) が表示されている場合、大量のデータを読み込んでいる可能性があり、インデックスの作成や見直しを検討すべきです。
意図しないインデックスが使われている: possible_keysにはあるのにkeyで違うものが使われている、あるいはNULLになっている場合、より適切なインデックスを作成したり、クエリの書き方を見直す必要があるかもしれません。
見積もり行数(rows)が非常に大きい: 実際に取得したいデータ量に対して見積もり行数が大きすぎる場合、WHERE句の条件がインデックスをうまく使えていない可能性があります。
非効率な処理 (Using filesort, Using temporaryなど): これらの処理はメモリやディスクI/Oに負荷をかけるため、インデックスの利用などで回避できないか検討します。
結合順序や結合方法が適切でない: 複数のテーブルを結合する場合、結合順序や方法によってパフォーマンスが大きく変わることがあります。

EXPLAINを使ってこれらの問題点を発見し、インデックスを追加・修正したり、SQLの書き方を変えたりすることで、クエリのパフォーマンスを劇的に改善できることがあります。💪

最初は出力結果を読むのが難しく感じるかもしれませんが、まずはtype, key, rows, Extra (MySQL) や、ノードの種類 (Seq ScanかIndex Scanかなど), cost, rows (PostgreSQL) といった重要な項目に注目して、「どこか非効率な処理をしていないか？」という視点で見ていくと良いでしょう。繰り返し使っていくうちに、徐々に読み解けるようになっていきます！😊