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](http://www.oreilly.co.jp/books/9784873113432/)