EXPLAINについて

普段はフロントばかりですがAPIを作成することがあったので、MySQLのEXPLAIN（実行プラン）について、忘れないようにまとめます。

EXPLAINは、クエリがどのように実行されるかを確認することができます。
結果を分析することで、パフォーマンスを向上させるためのインデックスの作成やクエリの最適化ができます。最適なクエリ実行計画を選択し、効率的なデータベースクエリを作成するのに役立ちます。

EXPLAIN実行方法

クエリの先頭にEXPLAINをつけて実行します。

EXPLAIN SELECT * FROM users WHERE id = 1

結果

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

EXPLAINの各項目解説

id

SELECT識別子で、実行順序を表します。

select_type

クエリ内の各SELECT文に関連する情報を示すフィールドです。select_type は、クエリ内でどのようにデータを選択または処理するかを説明している。

select_typeの種類
項目	内容
SIMPLE	クエリ内で単純なSELECT文が実行される場合に表示されます。通常、独立したテーブルからデータを選択する場合に使用されます。
PRIMARY	クエリ内で複数のSELECT文がネストされている場合、最も外側のSELECT文に対してこのタイプが表示されます。例えば、サブクエリが含まれる場合など。
SUBQUERY	主クエリ（プライマリクエリ）内でサブクエリが使用される場合に表示されます。サブクエリは別のクエリ内で実行されるクエリであり、その結果が主クエリに影響を与える場合に使用されます。
DERIVED	クエリ内で派生テーブルが使用される場合に表示されます。派生テーブルは、一時的なテーブルとして生成され、その結果がクエリ内で使用されます。サブクエリの結果を一時的なテーブルに格納する際にこのタイプが表示されることがあります。
UNION	クエリ内でUNION演算が使用される場合に表示されます。UNIONは複数のSELECT結果を結合するために使用され、複数のテーブルからデータを取得する場合に使用されます。
UNION RESULT	UNION演算の結果が格納されるテンポラリテーブルに関連するSELECT文に表示されます。
DEPENDENT SUBQUERY	サブクエリが外部クエリに依存している場合に表示されます。サブクエリ内の条件が外部クエリの結果に基づいている場合などに該当します。
UNCACHEABLE SUBQUERY	サブクエリがクエリキャッシュに保存できない場合に表示されます。サブクエリ内で非決定的な関数やランダムな値を使用する場合など、結果がキャッシュできないと判断される場合に該当します

table

アクセスするテーブル名

partitions

クエリの実行時にテーブルが分割されたパーティション（分割テーブル）の数を示す情報です。パーティションは、テーブル内のデータを論理的または物理的に分割して管理するための手法です。データベースシステムによっては、テーブルを複数のパーティションに分割することでデータの管理やクエリの最適化を向上させることがあります。

type

MySQLがどのようにテーブルへアクセスするかを教えてくれる項目。

typeの種類
項目	内容
const	PRIMARY KEY やUNIQUE KEY による等価比較が行われたときのタイプ。これらのキーには重複したレコードが無いため、キーとリテラル（具体的な値）の条件でクエリ実行されると結果は0行か1行のみになる。この場合、オプティマイザは検索結果を定数(const)のようにみなす。目指すべきtype
eq_ref	JOINするときにPRIMARY KEYやUNIQUE KEYが使われることを表す。constに似ているが、JOINの内部表へのアクセスで用いられる点が異なる。
ref	PRIMARY KEY、UNIQUE KEYでないインデックス（ユニークでないインデックス）を使って等価検索(WHERE key = value)を行うことを表す。JOINでも単一テーブルのSELECTでもインデックスがユニークでなければ、refになる。
range	インデックスを用いた範囲検索（不等号やBETWEEN）。検索範囲が大きくても小さくてもrangeとなるので注意が必要。
index	フルインデックススキャン。該当のインデックスをスキャンする重い処理。ORDER BY + LIMIT で行数を絞り込んでいる場合は、先頭の数エントリを読み込むだけで済むので問題にはならない。
ALL	インデックスを用いない、テーブルスキャン。このタイプが出たら要注意。改善の余地あり。

possible_keys

possible_keysはオプティマイザが利用可能なインデックスの候補として挙げたキー一覧。

key

keyは、実際にオプティマイザに選択されたキー。どのインデックスが使われるかはkeyを見ればわかる。

key_len

key_lenは、選択されたキーの長さ。

ref

検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される

rows

そのテーブルからフェッチされる行数の見積もり。
このフィールドはあくまでもテーブル全体の行数やインデックスの分散具合から導き出された大まかな見積もりなので、実際にフェッチされる正確な行数ではない。

ただしDERIVEDテーブルは実際に実行してみないと行数の見積もりができないので、オプティマイザはEXPLAINの際にもサブクエリを実行する。そのため、DERIVEDテーブルだけは常に正確な行数を見積もることができる。

また、フェッチされた全ての行がそのまま結果として返されるわけではない。Using whereがExtraフィールドに表示されている場合は、フェッチした行に対してさらにWHERE句の検索条件が適用されて行の絞り込みが行われるので、クライアントへ返される結果行は少なくなる可能性がある。

JOINを処理する場合、WHERE句により行の絞り込みがなければ最終的な結果行数の見積もりはJOINする全てのテーブルのrowsフィールドの積として考えることが出来る。PRIMARY KEYやUNIQUEインデックスを利用してJOINする場合、つまりレコードアクセスタイプがeq_refの場合rowsフィールドは1になる。eq_refは理想的なJOINの形式であると言える。しかしレコードアクセスタイプがeq_ref以外の場合、例えばrefやrangeになってしまっている場合には前のテーブルに対して多数のテーブルがJOINされてしまうことになるので注意が必要。

filtered

クエリの実行時にテーブルから選別された行の割合を示す情報です。この値は0から100までのパーセンテージで表現され、クエリがどれだけ効果的にデータをフィルタリングしているかを示します。
この値が高い場合、クエリの条件がデータセットを効果的にフィルタリングしており、必要なデータを取得するのに効率的であることを示します。

Extra

クエリの実行計画に関する追加情報を提供します。Extraには様々な値が表示され、クエリの実行や最適化に関連する情報が含まれます。

Extraの種類
項目	内容
Using where	クエリがWHERE句を使用して行を選択していることを示します。つまり、結果セットに含まれる行はWHERE条件に合致しています。
Using index	クエリがインデックスを使用してデータを取得していることを示します。インデックスはデータの取得に効率的であるため、この値が表示されることは一般的に望ましいです。
Using temporary	クエリの実行に一時テーブルが使用されていることを示します。一時テーブルはクエリ実行の一時的な結果を格納するために使用されることがあります。一時テーブルの使用は、クエリのパフォーマンスに影響を与えることがあるため、注意が必要です。
Using filesort	クエリの結果セットのソートにファイルソートが使用されていることを示します。ファイルソートは、大量のデータをソートする際にパフォーマンスに影響を及ぼす可能性があるため、最適化の余地があるかもしれません。
Using join buffer	JOIN操作時にJOINバッファが使用されていることを示します。JOINバッファは、JOIN操作の一時的な結果を格納するために使用されます。適切にサイズ調整されていない場合、パフォーマンスに悪影響を及ぼすことがあります。
Distinct	結果セットから重複する行を削除するためにDISTINCTキーワードが使用されていることを示します。
Full scan on NULL key	インデックスのNULLキーに対するフルスキャンが行われていることを示します。NULLキーのインデックスに対して検索を行うと、インデックスの一部が無視されるため、パフォーマンスが低下する可能性があります。
Full scan on NULL key	最適化によりクエリ内で不要なテーブルが省略されていることを示します。これは、クエリの最適化により不要なJOIN操作が省略される場合などに表示されます。

終わりに

まだまだ理解してないところがありますので何かご指摘ありましたらよろしくお願いします。
SQLは奥深い。。。