MySQL入門：explainの出力について -vol.1-

よっしー

こんにちは。よっしーです(^^)

今日は、mysqlのexplainについてご紹介します。

背景
EXPLAIN 出力形式
EXPLAIN 出力列
おわりに

背景

mysqlのexplainを調査したの内容を備忘として残しました。

explainの一次情報には下記を利用しています。

fw_error_www

EXPLAIN 出力形式

EXPLAIN文は、MySQLがステートメントを実行する方法に関する情報を提供します。

EXPLAINはSELECT、DELETE、INSERT、REPLACE、およびUPDATEステートメントと共に使用できます。

EXPLAINは、SELECTステートメントで使用される各テーブルについて情報の行を返します。

出力では、MySQLがステートメントを処理する際にテーブルを読み取る順序でテーブルがリストされます。これはつまり、MySQLが最初のテーブルから行を読み取り、次に2番目のテーブルで一致する行を見つけ、3番目のテーブルで同様に行を見つけ、その後も同様に処理を続けることを意味します。すべてのテーブルが処理されると、MySQLは選択した列を出力し、テーブルリストを逆戻りして、より一致する行があるテーブルが見つかるまで進みます。次の行はこのテーブルから読み取り、次のテーブルでプロセスを続行します。

EXPLAIN 出力列

このセクションでは、EXPLAINによって生成される出力列について説明します。後のセクションでは、タイプおよびExtra列に関する追加情報が提供されます。

EXPLAINからの各出力行は、1つのテーブルに関する情報を提供します。

各行には、下記の表で要約されている値が含まれており、その後の表の詳細について説明されています。

表の最初の列にはカラム名を、2番目の列はFORMAT=JSONが使用されるときに出力に表示される対応するプロパティ名を提供します。

Column	JSON Name	意味
id	select_id	SELECTの識別子
select_type	None	SELECTのタイプ
table	table_name	出力行のテーブル
partitions	partitions	一致するパーティション
type	access_type	結合の種類
possible_keys	possible_keys	選択可能なインデックス
key	key	実際に選択されたインデックス
key_len	key_length	選択されたキーの長さ
ref	ref	インデックスと比較される列
rows	rows	調査対象の行数の推定
filtered	filtered	テーブル条件によってフィルタリングされる行の割合
Extra	None	追加情報

NULL の JSON プロパティは、JSON 形式の EXPLAIN 出力には表示されません。

id（JSON名：select_id）

SELECTの識別子です。これはクエリ内のSELECT文の連番です。行が他の行の結果のUNIONを参照している場合、値はNULLになることがあります。この場合、table列には <unionM,N> のような値が表示され、その行がid値がMとNの行のUNIONを参照していることを示します。

select_type（JSON名：none）

SELECTの種類です。次の表に示されているいずれかの種類です。JSON形式のEXPLAINでは、SELECTの種類がSIMPLEまたはPRIMARYである場合を除いて、query_blockのプロパティとして公開されます。該当する場合、JSON名も表に表示されます。

以下はテーブル形式での説明です。

select_type	JSON Name	意味
SIMPLE	None	シンプルなSELECT（UNIONやサブクエリを使用しない）
PRIMARY	None	最も外側のSELECT
UNION	None	UNION内の2番目以降のSELECT文
DEPENDENT UNION	dependent (true)	UNION内の2番目以降のSELECT文で、外部クエリに依存している
UNION RESULT	union_result	UNIONの結果
SUBQUERY	None	サブクエリ内の最初のSELECT
DEPENDENT SUBQUERY	dependent (true)	サブクエリ内の最初のSELECTで、外部クエリに依存している
DERIVED	None	導出テーブル
DEPENDENT DERIVED	dependent (true)	他のテーブルに依存する導出テーブル
MATERIALIZED	materialized_from_subquery	材料化されたサブクエリ
UNCACHEABLE SUBQUERY	cacheable (false)	結果をキャッシュできず、外部クエリの各行で再評価する必要があるサブクエリ
UNCACHEABLE UNION	cacheable (false)	キャッシュできないサブクエリに属するUNION内の2番目以降のSELECT（UNCACHEABLE SUBQUERYを参照）

DEPENDENT（依存）は通常、相関サブクエリの使用を示します。

DEPENDENT SUBQUERY（依存サブクエリ）の評価は、UNCACHEABLE SUBQUERY（キャッシュできないサブクエリ）の評価と異なります。DEPENDENT SUBQUERYの場合、サブクエリは外部コンテキストの変数の異なる値のセットごとに1回だけ再評価されます。UNCACHEABLE SUBQUERYの場合、サブクエリは外部コンテキストの各行ごとに再評価されます。

EXPLAINでFORMAT=JSONを指定した場合、出力には直接的にselect_typeに対応する単一のプロパティはありません。query_blockプロパティが特定のSELECTに対応します。表示される場合、表示される適切なSELECTサブクエリのタイプに対応するプロパティが利用可能です（たとえば、MATERIALIZEDの場合はmaterialized_from_subqueryがあります）。SIMPLEまたはPRIMARYに対するJSONの相当物はありません。

非SELECTステートメントの場合、select_typeの値は影響を受けるテーブルのステートメントタイプを表示します。たとえば、DELETEステートメントの場合、select_typeはDELETEです。

table（JSON名：table_name）

出力の行が参照するテーブルの名前です。また、次の値のいずれかになることもあります：

<unionM,N>: 行はid値がMとNの行のUNIONを指しています。

<derivedN>: 行はid値がNの行の導出テーブルの結果を指しています。導出テーブルは、例えばFROM句内のサブクエリから生成されることがあります。

<subqueryN>: 行はid値がNの行の材料化サブクエリの結果を指しています。

partitions（JSON名：partitions）

クエリによって一致するレコードが存在するパーティションです。非パーティションテーブルの場合、値はNULLです。

type（JSON名：access_type）

結合の種類です。

possible_keys（JSON名：possible_keys）

possible_keys列は、MySQLがこのテーブルの行を見つけるために選択できるインデックスを示します。注意すべきは、この列がEXPLAINの出力に表示されるテーブルの順序とはまったく独立していることです。つまり、possible_keysの中には、生成されたテーブルの順序で実際には使用できないキーが含まれているかもしれません。

この列がNULLである場合（またはJSON形式の出力では未定義の場合）、関連するインデックスが存在しないことを示します。この場合、クエリのパフォーマンスを向上させるために、WHERE句を調べて、インデックスに適したカラムが言及されているかどうかを確認できます。もし適している場合は、適切なインデックスを作成し、再度EXPLAINを使用してクエリを確認してください。

テーブルがどのインデックスを持っているかを確認するには、SHOW INDEX FROM tbl_nameを使用します。

key（JSON名：key）

key列は、MySQLが実際に使用するキー（インデックス）を示します。MySQLが行を検索するためにpossible_keysのインデックスのうちの1つを使用することを決定した場合、そのインデックスがkeyの値としてリストされます。

keyがpossible_keysの値に存在しないインデックスの名前を指すことがあります。これは、possible_keysのいずれのインデックスも行を検索するのに適していないが、クエリで選択されたすべての列が他のインデックスの列である場合に発生します。つまり、指定されたインデックスが選択された列をカバーしているため、行を取得する際に使用されないものの、インデックススキャンがデータ行のスキャンよりも効率的です。

InnoDBの場合、クエリが主キーを選択している場合でも、InnoDBはセカンダリインデックスごとに主キーの値を保存するため、セカンダリインデックスが選択された列をカバーする可能性があります。keyがNULLの場合、MySQLはクエリをより効率的に実行するために使用するインデックスが見つからなかったことを示します。

possible_keys列にリストされているインデックスをMySQLに使用させたり無視させたりするには、クエリ内でFORCE INDEX、USE INDEX、またはIGNORE INDEXを使用します。

MyISAMテーブルの場合、ANALYZE TABLEを実行すると、オプティマイザがより適切なインデックスを選択するのに役立ちます。MyISAMテーブルの場合、myisamchk –analyzeも同様の効果があります。

key_len（JSON名：key_length）

key_len列は、MySQLが使用するキーの長さを示します。key_lenの値により、MySQLが実際にいくつのキーの部分を使用しているかを判断できます。key列がNULLである場合、key_len列もNULLとなります。

キーの格納形式により、NULLになる可能性のある列のキーの長さは、NOT NULL列に比べて1つ大きくなります。

ref（JSON名：ref）

ref列は、key列で指定されたインデックスと比較される列または定数を示します。これにより、テーブルから行を選択するために使用されます。

値がfuncである場合、使用される値はある関数の結果です。どの関数かを確認するには、EXPLAINの後にSHOW WARNINGSを使用し、拡張されたEXPLAIN出力を表示します。関数は実際には算術演算子などの演算子かもしれません。

rows（JSON名：rows）

rows列は、MySQLがクエリを実行するために調査する必要がある行数を示します。

InnoDBテーブルの場合、この数値は推定値であり、常に正確ではないことがあります。

filtered（JSON名：filtered）

filtered列は、テーブルの条件によってフィルタリングされると推定されるテーブル行の割合を示します。最大値は100で、これは行のフィルタリングが発生しなかったことを意味します。100から減少する値は、ますます多くのフィルタリングを示します。rowsは調査された行の推定数を示し、rows × filteredは次のテーブルと結合される行数を示します。たとえば、rowsが1000でfilteredが50.00（50%）の場合、次のテーブルと結合される行数は1000 × 50% = 500です。”