EXPLAINコマンドの出力情報を読み解く

半年間、MySQLを基礎からやり直すことにしたので、その間に勉強したことをQiitaに投稿していきます。

EXPLAIN フィールドとソースコードの要素の関係

EXPLAIN フィールド	説明	ソースコードの要素
id	クエリID。サブクエリを使用している場合のみ意味がある	select_lex->select_number
select_type	テーブルから取得された結果セットによって何が生じるかを示す。サブクエリまたはUNIONを伴わない結合ではこの値はsimpleに設定される	select_lex->type
table	テーブルがクエリ内から参照されるエイリアス	エイリアスを使用しない場合はこのテーブルの実際の名前になる。正規(非派生)テーブルの場合、join_tab[k-1].table- >alias。kは結合順序におけるテーブルの数。派生テーブルの場合、join_tab[k-1].table->derived_select_number
type	テーブルからレコード取得の際に使用される方法。	join_tab[k-1].type。kは結合順序におけるテーブルの数
possible_keys	テーブルからのレコード取得の際にWHERE句と連携して使用できるキーのリスト	join_tab[k-1].keys。kは結合順序におけるテーブルの数
key	レコード取得の際に使用されるキーの名前。index_merge最適化が使用される場合、キーのリストを示す	キーまたはそのプレフィックスの1つの値に基づいて1つ以上のレコードをルックアップするためにキーが用いられる場合、キーのゼロベースのインデックス番号はjoin_tab[k-1].ref.keyに含められる
key_len	クエリで使用されるキーの長さ。これはキーの全体の長さである必要はない。キープレフィックスのみを使用することも可能である	インデックスがスキャンされている場合、キー番号はjoin_tab[k-1].indexに含められる。範囲最適化が実行される場合、キー番号はjoin_tab[k-1].select->quick->indexに含められる。キー定義は、join_tab[k-1].table->key_infoで開始するKEY構造体の配列に格納される。キーの名前はKEY構造体のnameメンバに格納される。キー定義構造体を見つける方法についてはキーフィールドの説明を参照。使用されるキーの長さはKEY構造体のkey_lengthメンバになる
ref	テーブルのインデックスルックアップに含まれている値を持つ、他のテーブルからのフィールドのリスト。	join_tab[k-1].ref.key_copy
rows	個々の結合繰り返しで取得される、テー ブルのレコードの平均の概算値	join_tab[k-1].best_positions.records_read
Extra	最適化戦略に関する追加のコメント	数多くの結合ディスクリプタデータメンバから収集される

EXPLAINコマンドの出力のselect_typeフィールドについて

SIMPLE

UNIONまたはサブクエリを使用しないSELECT

PRIMARY

最も外側のSELECTまたは結合(UNION)の最初のSELECT

UNION

結合(UNION)の一部であり、クエリの先頭には来ないSELECT

DEPENDENT UNION

依存性のサブクエリであることを除けばUNIONと同じ。オプティマイザが外側のSELECTの各行に対して変化する情報を使用できると判断した場合、サブクエリは外側のSELECTに依存しているとみなされる。残念ながら、これはオプティマイザが外側のSELECTの各行に対してサブクエリを再実行することを意味している

UNION RESULT

結合(UNION)の結果

SUBQUERY

非依存性のサブクエリ。オプティマイザはそれを一度だけ実行すればよいものと判断する

DEPENDENT SUBQUERY

依存性のサブクエリ。オプティマイザは外側のクエリに対してそれを一度だけ実行すればよいものと判断する。ここで注意したいのは、たとえそのような処理をする必要がない場合でも、オプティマイザはサブクエリの独立性に気づかない場合がある

DERIVED

派生テーブルを作成するSELECT。テーブルが別のクエリの結果セットから生成される場合、そのテーブルは派生とみなされる。SQL標準では、その種のテーブルは「FROM句のサブクエリ」と呼ばれる

EXPLAINコマンドの出力のtypeフィールドについて

system

テーブルに1つのレコードしか存在しない場合の特別なケース

const

テーブルには最大で1つのマッチしている行が存在する。この行はクエリの開始時に一度だけ読み出される。テーブルに一意キーが存在し、WHERE句が値をそれに対して提供する場合にこれが起こる

eq_ref

constと同じ。ただし、値は固定の定数ではなく、別のテーブルから取り出される。1つのレコードのみが取得される。したがってキーは一意である必要がある。

ref

キールックアップに使用される値が1つだけという点はeq_refとconstと同じ。ただし1つ以上のレコードを取得することが可能である。キーが一意ではないか、またはキーのプレフィックスだけが利用可能な場合にこれが起こる

ALL

フルテーブルスキャン。キー制約が使用できない場合で、かつオプティマイザはインデックスによりカバーされないカラムを読み出す必要がある場合にこれが起こる

range

レコードは、1つ以上の範囲制約を使用してインデックスを通じて読み出される。このレコードアクセス方式は範囲に対応するキーでのみ可能である。Bツリーキーは範囲に対応するが、ハッシュキーは対応していない

index

インデックス全体がスキャンされる。これはインデックスの効率的な用途ではない。つまり、ユーザはインデックスをうまく利用できなかったことを意味している。とはいえ、ユーザが提供したクエリについてオプティマイザが対応できる最善の方法になる。インデックス値に対する制約が存在していなかった。制約があれば、読み出す値の数も減っていた。インデックス全体を使用しながら、スキャンでは、インデックスがカバーしているレコードの部分のみをアクセスする。インデックスがレコード全体の小さい部分のみをカバーしている場合、この種のインデックススキャンはフルテーブルスキャンより効率的になる可能性がある

fulltext

オプティマイザは、レコード取得のためにフルテキストマッチング方式を用いる。これはフルテキスト対応のキーでのみ可能である。現在ではMyISAMストレージエンジンでのみ実装される

ref_or_null

追加でNULL値の検索が実行されることを除けばrefと同じ

unique_subquery

サブクエリが一意キー値を選択した際に、サブクエリでINを最適化するために使用される

index_subquery

インデックスが一意ではないことを除けばunique_subqueryと同じ

index_merge

2つのキーが個別に使用され、結果はマージされる

EXPLAINコマンドの出力のExtraフィールドについて

Using where

一部のレコードを取り除くためにWHERE句が評価された。キーで読み出し予定のすべてのレコードによってWHERE句が自動的に満たされることをオプティマイザが検出できない場合にこれが必要になる

Using index

オプティマイザは、必要とされるすべてのカラムが1つのキーに含まれていることに気づいた。したがって、オプティマイザは、データ全体ではなくそのキーをスキャンすることに決めた

Using index for group-by

オプティマイザは、個々のdistinct値に対して1つのキーの最初のレコードまたは最後のレコードのみを読み出してGROUP BYまたはDISTINCTを最適化することができる。GROUP BYの場合、これはMIN()とMAX()を除く非集約関数が存在しない場合に限り可能となる。クエリには1つのテーブルが伴う。必要なすべてのカラムは、オプティマイザが選んだインデックスによってカバーされる。さらに、GROUP BYのカラムの順序は、個々のdistinctキー値のすべてのレコードを見ることなく答えが得られるように、WHERE句またはMIN()/MAX()のカラムと連携する

Using filesort

オプティマイザはソートされた順序(ORDER BY)でレコードを取得するように依頼されていたが、そのレコードアクセス方式はそれを保証していない。したがってポストソーティングが必要になる。用語filesortはMySQLソートアルゴリズムを示す。これはメモリ内の小さなチャンクにおいてラディックスソートまたはクイックソートを実行する。ソート対象のレコード全体のセットがソートバッファに収まらない場合、一時的な結果がファイルに格納される。マージステップはすべてのチャンクで実行される

Using temporary

オプティマイザは、中間結果を格納するために一時テーブルを作成する必要がある。例えば、非キーカラムでGROUP BYが処理される場合、オプティマイザはGROUP BY式から成る一意キーとともに一時テーブルを作成する。正規の結果セット(GROUP BYを省略)の個々のレコードを対象として、それを一時テーブルに挿入するための試みが行われる。挿入が一意性制約違反により失敗した場合、既存のレコードは適宜更新される。一時テーブルが処理されると、結果セットがソートされ、クライアントに返される

Distinct

オプティマイザは結合内のレコードを取り除くことができる。これはクエリでDISTINCTキーワードを使用することにより可能になる

Not exists

レコード結合を取り除くために、左結合の間に特別な最適化が使用される。2番目のテーブルでNOT NULL属性によって定義されるカラムで結合が処理され、かつWHERE句がカラムがNULLであることを要求する場合、これが可能になる唯一のケースは、最初のテーブルカラムのマッチしている値が2番目のテーブルに存在しない場合である

range checked for each record: (index map: N)

オプティマイザは特定のテーブルで終始使用するインデックスを見つけられなかった。ただし、結合が進行するにつれ(結合順序の)先行テーブルの特定のレコード組み合わせが、一部のキーについて範囲またはインデックスマージ最適化を許可している可能性がある。このため、オプティマイザは、先行テーブルの個々のレコード組み合わせを対象に、どのインデックスが使用に最も適しているかを決めるためのチェックを行う

Using union()

このコメントはindex_mergeアクセス方式のケースで現れる。レコード取得のために複数のキーが用いられ、結果のソートされたリストマージを通じて正しい結果が得られる。言い換えれば、個々のキーの制約により、行IDによって個々のインデックスからレコードをソートする必要がない。個々のキーによりソートされたリストが自然に生成される。行IDによる自然にソートされた順序が保証されるのは、キーのすべてのパーツが既知の場合、またはキーが(InnoDBやBDBテーブルにおいて)クラスタ化プライマリキーである場合である

Using sort_union()

このコメントはindex_mergeアクセス方式のケースで現れる。レコード取得のために複数のキーが使用されるが、オプティマイザは、個々のキーが自然にソートされたリストを生成することを確信していない。したがって、重複する行を取り除くために、追加の処理が必要になる

Using intersect()

このコメントはindex_mergeアクセス方式のケースで現れる。レコード取得のために2つ以上のキーが使用され、結果のソートリストの交わり(intersection)を通じて正しい結果が得られる。この最適化は、結果セットが結合(OR演算)ではなく、交わり(AND演算)で処理されることを除いては、Using union()と酷似している

Using where with pushed condition

NDBテーブルが導入されるまで、オプティマイザは次のような仮定を行っていた。つまり、キーまたはスキャンのいずれかを使用してテーブルからレコードを読み出す場合、最悪のケースシナリオではローカルディスクへのアクセスが必要になるというものだ。たとえそのようなケースではなくても、それ以外に施す手立てはなかった。既存のストレージエンジンのいずれもレコードをプレフィルタリングする機能を持っていなかった。NDBの導入により、プレフィルタリングの機能は必須になった。NDBテーブルアクセスでは通常ネットワークI/Oが生じる。したがって、リモートノードに対するフィルタリング制約を通知できるほどストレージエンジンが賢ければ、パフォーマンスの大幅な最適化が実現できるはずである。ストレージエンジンがその処理をサポートしていれば(現在、NDBのみがサポートしている)、オプティマイザはフィルタリング制約をストレージエンジンインスタンスの条件スタックにプッシュすることができる。そうすれば今度はストレージエンジンがこの追加情報を使用してレコード取得の最適化ができる

参考：詳解MySQL