8.8.2 EXPLAIN 出力フォーマット

EXPLAIN ステートメントは SELECT ステートメントの実行プランに関する情報を提供します。

EXPLAIN は SELECT ステートメントで使用される各テーブルに関する情報の行を返します。これは、MySQL がステートメントの処理中にテーブルを読み取る順番で、出力にテーブルを一覧表示します。MySQL は Nested Loop 結合メソッドを使用して、すべての結合を解決します。これは、MySQL が最初のテーブルから行を読み取り、次に 2 つめのテーブル、3 つめのテーブルというように、一致する行を見つけることを意味します。すべてのテーブルが処理されると、MySQL は選択したカラムを出力し、さらに一致する行があるテーブルが見つかるまで、テーブルリストを逆戻りします。次の行がテーブルから読み取られ、プロセスは次のテーブルに進みます。

EXTENDED キーワードを使用すると、EXPLAIN は、EXPLAIN ステートメントに続けて SHOW WARNINGS ステートメントを発行することで表示できる追加の情報を生成します。EXPLAIN EXTENDED はフィルタ処理されたカラムも表示します。セクション8.8.3「EXPLAIN EXTENDED 出力フォーマット」を参照してください。

注記

EXTENDED キーワードと PARTITIONS キーワードを、同じ EXPLAIN ステートメントで一緒に使用することはできません。

• EXPLAIN 出力カラム

• EXPLAIN 結合型

• EXPLAIN 追加情報

• EXPLAIN 出力の解釈

EXPLAIN 出力カラム

このセクションでは、EXPLAIN によって生成される出力カラムについて説明します。あとのセクションで、type と Extra カラムに関する追加情報を提供します。

EXPLAIN からの各出力行は 1 つのテーブルに関する情報を提供します。各行には、表8.1「EXPLAIN 出力カラム」で要約し、次の表に詳しく説明している値が格納されます。

表 8.1 EXPLAIN 出力カラム

カラム	意味
id	SELECT 識別子。
select_type	SELECT 型
table	出力行のテーブル
partitions	一致するパーティション
type	結合型
possible_keys	選択可能なインデックス
key	実際に選択されたインデックス
key_len	選択されたキーの長さ
ref	インデックスと比較されるカラム
rows	調査される行の見積もり
filtered	テーブル条件によってフィルタ処理される行の割合
Extra	追加情報

• id

  SELECT 識別子。これはクエリー内の SELECT の連番です。行がほかの行の和集合結果を参照する場合に、値は NULL になることがあります。この場合、table カラムには、<unionM,N> などの値が表示され、行が M および N の id 値のある行の和集合を参照していることが示されます。

• select_type

  SELECT の種類で、次の表に示すもののいずれかになります。

  select_type 値	意味
  SIMPLE	単純な SELECT (UNION やサブクエリーを使用しません)
  PRIMARY	もっとも外側の SELECT
  UNION	UNION 内の 2 つめ以降の SELECT ステートメント
  DEPENDENT UNION	UNION 内の 2 つめ以降の SELECT ステートメントで、外側のクエリーに依存します
  UNION RESULT	UNION の結果。
  SUBQUERY	サブクエリー内の最初の SELECT
  DEPENDENT SUBQUERY	サブクエリー内の最初の SELECT で、外側のクエリーに依存します
  DERIVED	派生テーブル SELECT (FROM 句内のサブクエリー)
  MATERIALIZED	実体化されたサブクエリー
  UNCACHEABLE SUBQUERY	結果をキャッシュできず、外側のクエリーの行ごとに再評価される必要があるサブクエリー
  UNCACHEABLE UNION	キャッシュ不可能なサブクエリー (UNCACHEABLE SUBQUERY を参照してください) に属する UNION 内の 2 つめ以降の SELECT

  DEPENDENT は一般に、相関サブクエリーの使用を示します。セクション13.2.10.7「相関サブクエリー」を参照してください。

  DEPENDENT SUBQUERY の評価は UNCACHEABLE SUBQUERY の評価とは異なります。DEPENDENT SUBQUERY の場合、その外部コンテキストの変数の異なる値の各セットにつき、一回だけサブクエリーが再評価されます。UNCACHEABLE SUBQUERY の場合、外部コンテキストの行ごとにサブクエリーが再評価されます。

  サブクエリーのキャッシュ可能性は、クエリーキャッシュへのクエリー結果のキャッシュ (これについてはセクション8.9.3.1「クエリーキャッシュの動作」で説明しています) と異なります。サブクエリーのキャッシュは、クエリー実行中に行われ、クエリーキャッシュは、クエリーの実行が終了したあとにのみ、結果を格納するために使用されます。

• table

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

  • <unionM,N>: 行は M および N の id 値のある行の和集合を参照しています。

  • <derivedN>: 行は N の id 値のある行の派生テーブル結果を参照しています。派生テーブルは、たとえば FROM 句内のサブクエリーの結果などになります。

  • <subqueryN>: 行は N の id 値のある行の実体化されたサブクエリーの結果を参照しています。セクション8.2.1.18.2「サブクエリー実体化によるサブクエリーの最適化」を参照してください。

• partitions

  クエリーでレコードが照合されるパーティション。このカラムは、PARTITIONS キーワードが使用されている場合にのみ表示されます。パーティション化されていないテーブルの場合、この値は NULL です。セクション19.3.5「パーティションに関する情報を取得する」を参照してください。

• type

  結合型。さまざまな型の説明については、「EXPLAIN 結合型」を参照してください。

• possible_keys

  possible_keys カラムは、MySQL がこのテーブル内の行の検索に使用するために選択できるインデックスを示します。このカラムは EXPLAIN の出力に表示されたテーブルの順序にまったく依存しません。つまり、possible_keys のキーの一部は、生成されたテーブルの順序で実際に使用できないことがあります。

  このカラムが NULL の場合は、関連するインデックスがありません。この場合、WHERE 句を調査して、それがインデックス設定に適したカラムを参照しているかどうかをチェックすることで、クエリーのパフォーマンスを向上できることがあります。その場合は、適切なインデックスを作成し、再度 EXPLAIN でクエリーをチェックします。セクション13.1.7「ALTER TABLE 構文」を参照してください。

  テーブルにあるインデックスを確認するには、SHOW INDEX FROM tbl_name を使用します。

• key

  key カラムは、MySQL が実際に使用することを決定したキー (インデックス) を示します。MySQL が行をルックアップするために、いずれかの possible_keys インデックスを使用することを決定した場合、キー値としてそのインデックスが一覧表示されます。

  key は possible_keys 値に存在しないインデックスを指定している可能性があります。これは possible_keys インデックスのどれも行のルックアップに適していない場合に発生する可能性がありますが、クエリーによって選択されるすべてのカラムはほかのインデックスのカラムになります。つまり、指定されたインデックスは選択されたカラムをカバーするため、取得する行を決定するために使用されませんが、インデックススキャンはデータ行スキャンよりも効率的です。

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

  MySQL で possible_keys カラムに示されたインデックスを強制的に使用させるか、無視させるには、クエリーで FORCE INDEX、USE INDEX、または IGNORE INDEX を使用します。セクション13.2.9.3「インデックスヒントの構文」を参照してください。

  MyISAM テーブルと NDB テーブルの場合、ANALYZE TABLE を実行することで、オプティマイザがより適切なインデックスを選択するために役立ちます。NDB テーブルの場合、これにより、分散されたプッシュダウン結合のパフォーマンスも向上します。MyISAM テーブルの場合、myisamchk --analyze は ANALYZE TABLE と同じことを実行します。セクション7.6「MyISAM テーブルの保守とクラッシュリカバリ」を参照してください。

• key_len

  key_len カラムは、MySQL が使用することを決定したキーの長さを示します。key カラムに NULL と示されている場合、この長さは NULL になります。key_len の値によって、MySQL が実際に使用するマルチパートキーのパート数を判断できます。

• ref

  ref カラムは、テーブルから行を選択するために、key カラムに指定されたインデックスに対して比較されるカラムまたは定数を示します。

  値が func の場合、使用される値は、特定の関数の結果です。どの関数か確認するには、EXPLAIN EXTENDED のあとに SHOW WARNINGS を付けて使用します。関数は、実際には算術演算子などの演算子である場合があります。

• rows

  rows カラムは、MySQL がクエリーを実行するために調査する必要があると考える行数を示します。

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

• filtered

  filtered カラムは、テーブル条件によってフィルタ処理されるテーブル行の推定の割合を示します。つまり、rows は調査される推定の行数を示し、rows × filtered / 100 が前のテーブルと結合される行数を示します。EXPLAIN EXTENDED を使用すると、このカラムが表示されます。

• Extra

  このカラムには、MySQL がクエリーを解決する方法に関する追加情報が含まれます。さまざまな値の説明については、「EXPLAIN の追加情報」を参照してください。

EXPLAIN 結合型

EXPLAIN 出力の type カラムには、テーブルの結合方法が示されます。次のリストに、もっとも適切な型からもっとも不適切な型の順番で並べた結合型を示します。

• system

  テーブルには行が 1 つしかありません (= system テーブル)。これは、const 結合型の特殊なケースです。

• const

  テーブルには、一致するレコードが最大で 1 つあり、クエリーの開始時に読み取られます。行が 1 つしかないため、この行のカラムの値は、オプティマイザの残りによって定数とみなされることがあります。const テーブルは、1 回しか読み取られないため、非常に高速です。

  const は PRIMARY KEY または UNIQUE インデックスのすべてのパートを定数値と比較する場合に使用されます。次のクエリーでは、tbl_name は const テーブルとして使用できます。

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref

  前のテーブルの行の組み合わせごとに、このテーブルから 1 行ずつ読み取られます。system と const 型以外で、これは最適な結合型です。これは、結合でインデックスのすべてのパートが使用されており、インデックスが PRIMARY KEY または UNIQUE NOT NULL インデックスである場合に使用されます。

  eq_ref は、= 演算子を使用して比較されるインデックス設定されたカラムに使用できます。比較値は、定数またはこのテーブルより前に読み取られたテーブルのカラムを使用する式を指定できます。次の例では、MySQL は eq_ref 結合を使用して、ref_table を処理できます。

  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

• ref

  前のテーブルの行の組み合わせごとに、一致するインデックス値を持つすべての行がこのテーブルから読み取られます。ref は、結合でキーの左端のプリフィクスのみが使用される場合、またはキーが PRIMARY KEY や UNIQUE インデックスではない場合 (つまり、結合で、キー値に基づいて単一の行を選択できない場合) に使用されます。使用されているキーがほんの数行にしか一致しない場合、これは適切な結合型です。

  ref は、= または <=> 演算子を使用して比較されるインデックス設定されたカラムに使用できます。次の例では、MySQL は ref 結合を使用して、ref_table を処理できます。

  SELECT * FROM ref_table WHERE key_column=expr;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

• fulltext

  結合は FULLTEXT インデックスを使用して実行されます。

• ref_or_null

  この結合型は、ref と似ていますが、MySQL が NULL 値を含む行の追加検索を実行することが追加されます。この結合型の最適化は、ほとんどの場合に、サブクエリーの解決で使用されます。次の例では、MySQL は ref_or_null 結合を使用して、ref_table を処理できます。

  SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;

  セクション8.2.1.8「IS NULL の最適化」を参照してください。

• index_merge

  この結合型はインデックスマージ最適化が使用されたことを示します。この場合、出力行の key カラムには使用されたインデックスのリストが含まれ、key_len には使用されたインデックスの最長キーパートのリストが含まれます。詳細については、セクション8.2.1.4「インデックスマージの最適化」を参照してください。

• unique_subquery

  この型は、次の形式の IN サブクエリーの ref を置き換えます。

  value IN (SELECT primary_key FROM single_table WHERE some_expr)

  unique_subquery は、効率化のため、サブクエリーを完全に置き換える単なるインデックスルックアップ関数です。

• index_subquery

  この結合型は unique_subquery に似ています。IN サブクエリーを置き換えますが、次の形式のサブクエリー内の一意でないインデックスに対して機能します。

  value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

  行を選択するためのインデックスを使用して、特定の範囲にある行のみが取得されます。出力行の key カラムは、使用されるインデックスを示します。key_len には使用された最長のインデックスパートが格納されます。この型の ref カラムは NULL です。

  range は、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、または IN() 演算子のいずれかを使用して、キーカラムを定数と比較する場合に使用できます。

  SELECT * FROM tbl_name
    WHERE key_column = 10;
  
  SELECT * FROM tbl_name
    WHERE key_column BETWEEN 10 and 20;
  
  SELECT * FROM tbl_name
    WHERE key_column IN (10,20,30);
  
  SELECT * FROM tbl_name
    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index

  index 結合型は、インデックスツリーがスキャンされることを除いて、ALL と同じです。これは 2 つの方法で行われます。

  • インデックスがクエリーのカバリングインデックスで、使用すると、テーブルから必要なすべてのデータを満たすことができる場合、インデックスツリーのみがスキャンされます。この場合、Extra カラムには Using index と示されます。インデックスのサイズは通常テーブルデータより小さいため、インデックスのみのスキャンは通常、ALL より高速です。

  • フルテーブルスキャンは、インデックスからの読み取りを使用して、インデックス順でデータ行をルックアップして実行されます。Extra カラムに Uses index が表示されません。

  MySQL は、クエリーで単一のインデックスの一部であるカラムのみが使用されている場合に、この結合型を使用できます。

• ALL

  フルテーブルスキャンは、前のテーブルの行の組み合わせごとに実行されます。これは、通常テーブルが const とマークされていない最初のテーブルである場合には適しておらず、通常ほかのすべてのケースで著しく不適切です。通常、定数値または以前のテーブルからのカラム値に基づいて、テーブルからの行の取得を可能にするインデックスを追加することで、ALL を回避できます。

EXPLAIN の追加情報

EXPLAIN 出力の Extra カラムには、MySQL がクエリーを解決する方法に関する追加情報が含まれます。次のリストに、このカラムに表示される可能性のある値について説明します。クエリーを可能なかぎり高速にしたい場合は、Using filesort および Using temporary の Extra 値に注意します。

• Child of 'table' pushed join@1

  このテーブルは、NDB カーネルにプッシュダウンできる結合内の table の子として参照されます。MySQL Cluster で、プッシュダウンされた結合が有効な場合にのみ適用されます。詳細と例については、ndb_join_pushdown サーバーシステム変数の説明を参照してください。

• const row not found

  SELECT ... FROM tbl_name などのクエリーの場合、テーブルは空でした。

• Deleting all rows

  DELETE に対し、一部のストレージエンジン (MyISAM など) は簡単で高速にすべての行テーブルを削除するハンドラメソッドをサポートしています。この Extra 値は、エンジンでこの最適化が使用された場合に表示されます。

• Distinct

  MySQL は個別の値を検索するため、最初に一致する行が見つかったら、現在の行の組み合わせについてのそれ以上の行の検索を停止します。

• FirstMatch(tbl_name)

  tbl_name には、準結合 FirstMatch 結合ショートカット戦略が使用されます。

• Full scan on NULL key

  これは、オプティマイザがインデックスルックアップアクセスメソッドを使用できない場合の代替の戦略として、サブクエリーの最適化で行われます。

• Impossible HAVING

  HAVING 句は常に false で、どの行も選択できません。

• Impossible WHERE

  WHERE 句は常に false で、どの行も選択できません。

• Impossible WHERE noticed after reading const tables

  MySQL はすべての const (および system) テーブルを読み取り、WHERE 句が常に false であることを通知します。

• LooseScan(m..n)

  準結合 LooseScan 戦略が使用されます。m と n はキーパート番号です。

• Materialize、Scan

  MySQL 5.6.7 より前では、これは単一の実体化された一時テーブルの使用を示します。Scan が存在する場合、テーブルの読み取りに一時テーブルインデックスは使用されません。そうでない場合は、インデックスルックアップが使用されます。さらに、Start materialize エントリも参照してください。

  MySQL 5.6.7 現在、実体化は、MATERIALIZED の select_type 値のある行と、<subqueryN> の table 値のある行によって示されます。

• No matching min/max row

  SELECT MIN(...) FROM ... WHERE condition などのクエリーの条件を満たす行がありません。

• no matching row in const table

  結合のあるクエリーで、空のテーブルまたは一意のインデックス条件を満足する行がないテーブルがありました。

• No matching rows after partition pruning

  DELETE または UPDATE に対し、オプティマイザはパーティションのプルーニング後に削除または更新するものが何も見つかりませんでした。それは、SELECT ステートメントの Impossible WHERE に意味が似ています。

• No tables used

  クエリーに FROM 句がないか、FROM DUAL 句があります。

  INSERT または REPLACE ステートメントで、SELECT パートがない場合に、EXPLAIN にこの値が表示されます。たとえば、EXPLAIN INSERT INTO t VALUES(10) に対して、それは EXPLAIN INSERT INTO t SELECT 10 FROM DUAL と同等であるために表示されます。

• Not exists

  MySQL はクエリーに対する LEFT JOIN 最適化を実行でき、LEFT JOIN 条件に一致する 1 つの行が見つかったら、前の行の組み合わせについて、このテーブルでそれ以上の行を調査しません。これは、このように最適化できるクエリーの種類の例です。

  SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
    WHERE t2.id IS NULL;

  t2.id が NOT NULL で定義されているとします。この場合、MySQL は t1 をスキャンし、t1.id の値を使用して t2 内の行をルックアップします。MySQL が t2 内に一致する行を見つけた場合、t2.id は NULL にならないことがわかっているため、同じ id 値を持つ t2 内の残りの行をスキャンしません。つまり、t1 の各行について、MySQL は、t2 内の実際に一致する行数にかかわらず、t2 内の単一のルックアップのみを実行する必要があります。

• Range checked for each record (index map: N)

  MySQL は使用に適したインデックスを見つけられませんでしたが、前のテーブルからのカラム値がわかったあとに、いくつかのインデックスが使用できることがわかりました。以前のテーブルの行の組み合わせごとに、MySQL は range または index_merge アクセスメソッドを使用して、行を取得できるかどうかをチェックします。これは、非常に高速ではありませんが、インデックスがまったくない結合の実行より高速です。前のテーブルのすべてのカラム値がわかっており、定数とみなされることを除き、適用基準は、セクション8.2.1.3「range の最適化」とセクション8.2.1.4「インデックスマージの最適化」で説明されているとおりです。

  インデックスは、テーブルの SHOW INDEX に示される同じ順序で 1 から番号付けされます。インデックスマップ値 N は、候補となるインデックスを示すビットマスク値です。たとえば、0x19 (2 進数の 11001) の値は、インデックス 1、4、および 5 が考慮されることを示します。

• Scanned N databases

  これは、セクション8.2.4「INFORMATION_SCHEMA クエリーの最適化」に説明するように、サーバーが INFORMATION_SCHEMA テーブルのクエリーを処理する際に実行するディレクトリスキャンの数を示します。N の値は 0、1、または all です。

• Select tables optimized away

  クエリーにはすべてインデックスを使用して解決された集約関数 (MIN()、MAX())、または COUNT(*) のみが含まれていますが、GROUP BY 句は含まれていませんでした。オプティマイザは 1 行のみを返すべきであると判断しました。

• Skip_open_table、Open_frm_only、Open_trigger_only、Open_full_table

  これらの値は、セクション8.2.4「INFORMATION_SCHEMA クエリーの最適化」に説明するように、INFORMATION_SCHEMA テーブルに対するクエリーに適用するファイルオープン最適化を示します。

  • Skip_open_table: テーブルファイルを開く必要はありません。データベースディレクトリをスキャンすることによって、クエリー内ですでに情報を使用できるようになっています。

  • Open_frm_only: テーブルの .frm ファイルのみを開く必要があります。

  • Open_trigger_only: テーブルの .TRG ファイルのみを開く必要があります。

  • Open_full_table: 最適化されていない情報のルックアップ。.frm、.MYD、および .MYI ファイルを開く必要があります。

• Start materialize、End materialize、Scan

  MySQL 5.6.7 より前では、これは複数の実体化された一時テーブルの使用を示します。Scan が存在する場合、テーブルの読み取りに一時テーブルインデックスは使用されません。そうでない場合は、インデックスルックアップが使用されます。さらに、Materialize エントリも参照してください。

  MySQL 5.6.7 現在、実体化は、MATERIALIZED の select_type 値のある行と、<subqueryN> の table 値のある行によって示されます。

• Start temporary、End temporary

  これは、準結合重複除去戦略の一時テーブルの使用を示します。

• unique row not found

  SELECT ... FROM tbl_name などのクエリーの場合に、テーブルに UNIQUE インデックスまたは PRIMARY KEY の条件を満たす行がありません。

• Using filesort

  MySQL はソート順で行を取得する方法を見つけるために、追加のパスを実行する必要があります。ソートは、結合型に従ってすべての行を進み、ソートキーと WHERE 句に一致するすべての行について行へのポインタを格納して実行されます。次にキーがソートされ、ソート順で行が取得されます。セクション8.2.1.15「ORDER BY の最適化」を参照してください。

• Using index

  実際の行を読み取るための追加のシークを実行する必要がなく、インデックスツリーの情報のみを使用して、テーブルからカラム情報が取得されます。この戦略は、クエリーで単一のインデックスの一部であるカラムのみを使用している場合に使用できます。

  Extra カラムに Using where とも示されている場合、キー値のルックアップを実行するためにインデックスが使用されていることを意味します。Using where がない場合、オプティマイザはインデックスを読み取って、データ行の読み取りを回避できますが、それをルックアップに使用していません。たとえば、インデックスがクエリーのカバリングインデックスである場合、オプティマイザはそれをルックアップに使用せずにそれをスキャンできます。

  ユーザー定義のクラスタ化されたインデックスを持つ InnoDB テーブルの場合、そのインデックスは Extra カラムに Using index がない場合でも使用できます。これは、type が index で key が PRIMARY の場合です。

• Using index condition

  インデックスタプルにアクセスし、まずそれらをテストして、すべてのテーブル行を読み取るかどうかを判断することによって、テーブルが読み取られます。このように、必要でないかぎり、すべてのテーブル行の読み取りを遅延 (「プッシュダウン」) するためにインデックス情報が使用されます。セクション8.2.1.6「インデックスコンディションプッシュダウンの最適化」を参照してください。

• Using index for group-by

  Using index テーブルアクセスメソッドと同様に、Using index for group-by は MySQL が、実際のテーブルへの追加のディスクアクセスをせずに、GROUP BY または DISTINCT クエリーのすべてのカラムを取得するために使用できるインデックスを見つけたことを示します。さらに、各グループに対して、少数のインデックスエントリだけが読み取られるように、インデックスがもっとも効率的に使われます。詳細については、セクション8.2.1.16「GROUP BY の最適化」を参照してください。

• Using join buffer (Block Nested Loop)、Using join buffer (Batched Key Access)

  初期の結合からのテーブルは、部分ごとに結合バッファーに読み込まれ、それらの行がバッファーから使用されて、現在のテーブルとの結合が実行されます。(Block Nested Loop) は Block Nested Loop アルゴリズムの使用を示し、(Batched Key Access) は Batched Key Access アルゴリズムの使用を示します。つまり、EXPLAIN 出力の前の行のテーブルからのキーがバッファリングされ、Using join buffer が表示された行によって表されるテーブルから、一致する行が一括してフェッチされます。

• Using MRR

  テーブルは Multi-Range Read 最適化戦略を使用して読み取られます。セクション8.2.1.13「Multi-Range Read の最適化」を参照してください。

• Using sort_union(...)、Using union(...)、Using intersect(...)

  これらは index_merge 結合型でインデックススキャンがどのようにマージされるかを示しています。セクション8.2.1.4「インデックスマージの最適化」を参照してください。

• Using temporary

  クエリーを解決するために、MySQL は結果を保持する一時テーブルを作成する必要があります。これは一般に、クエリーに、カラムを異なって一覧表示する GROUP BY 句と ORDER BY 句が含まれる場合に発生します。

• Using where

  WHERE 句は、次のテーブルに対して照合されるか、またはクライアントに送信される行を制限するために使用されます。具体的にテーブルからすべての行をフェッチするか、調査する意図がないかぎり、Extra 値が Using where でなく、テーブル結合型が ALL または index である場合、クエリーに何らかの誤りがある可能性があります。

• Using where with pushed condition

  この項目は NDB テーブルのみに適用されます。つまり、MySQL Cluster がコンディションプッシュダウン最適化を使用して、インデックス設定されていないカラムと定数の直接比較の効率を向上します。そのような場合、条件がクラスタのデータノードに「プッシュダウン」され、すべてのデータノードで同時に評価されます。これにより、一致しない行をネットワーク経由で送る必要がなくなり、コンディションプッシュダウンを使用できるが使用しない場合より、そのようなクエリーを 5 - 10 倍高速化できます。詳細については、セクション8.2.1.5「エンジンコンディションプッシュダウンの最適化」を参照してください。

EXPLAIN 出力の解釈

EXPLAIN 出力の rows カラムの値の積を取得することで、結合がどの程度適しているかを示す適切な目安を得ることができます。これは、クエリーを実行するために MySQL が調査する必要がある行数を大ざっぱに示すはずです。max_join_size システム変数によってクエリーを制限する場合、この行の積は、どの複数テーブル SELECT ステートメントを実行し、どれを中止するかを判断するためにも使用されます。セクション8.11.2「サーバーパラメータのチューニング」を参照してください。

次の例は、EXPLAIN によって得られた情報に基づいて、複数テーブル結合を段階的に最適化する方法を示しています。

ここに示す SELECT ステートメントがあり、EXPLAIN を使用して調査するつもりであるとします。

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

この例では次のように想定しています。

• 比較対象のカラムは次のように宣言されています。

  テーブル	カラム	データ型
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• テーブルには次のインデックスがあります。

  テーブル	インデックス
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID (主キー)
  do	CUSTNMBR (主キー)

• tt.ActualPC 値は均一に分布されていません。

最初、最適化が実行される前は、EXPLAIN ステートメントで次の情報が生成されました。

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

各テーブルの type は ALL であるため、この出力は MySQL がすべてのテーブル、つまりすべての行の組み合わせのデカルト積を生成することを示しています。これは、各テーブルの行数の積を調査する必要があるため、著しく時間がかかります。このケースの場合は、この積が 74 × 2135 × 74 × 3872 = 45,268,558,720 行になります。テーブルがもっと大きければ、どのくらい時間がかかっていたか簡単に想像がつきます。

ここでの問題の 1 つは、カラムが同じ型とサイズで宣言されている場合に、MySQL はカラムに対してインデックスをより効率的に使用できることです。このコンテキストでは、VARCHAR と CHAR は同じサイズとして宣言されている場合、それらは同じとみなされます。tt.ActualPC は CHAR(10) として宣言されており、et.EMPLOYID は CHAR(15) であるため、長さの不一致があります。

このカラム長の不一致を修正するには、ALTER TABLE を使用して ActualPC を 10 文字から 15 文字に長くします。

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

これで tt.ActualPC と et.EMPLOYID はいずれも VARCHAR(15) になります。EXPLAIN ステートメントを再度実行すると、次の結果が生成されます。

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

これは完全ではありませんが、はるかに改善されています。rows 値の積は 74 の係数分だけ少なくなります。このバージョンは、数秒で実行します。

2 つめの変更を実行して、tt.AssignedPC = et_1.EMPLOYID と tt.ClientID = do.CUSTNMBR の比較でのカラム長の不一致を解消できます。

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

その変更後、EXPLAIN は次に示す出力を生成します。

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

この時点で、クエリーはほぼ可能なかぎり十分に最適化されています。残りの問題は、MySQL はデフォルトで tt.ActualPC カラムの値が均一に分布しているものと想定しますが、tt テーブルにはそれが当てはまらないことです。さいわい、MySQL にキー分布を分析するように伝えることは簡単です。

mysql> ANALYZE TABLE tt;

追加のインデックス情報によって、結合が完全になり、EXPLAIN が次の結果を生成します。

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN の出力の rows カラムは、MySQL 結合オプティマイザの学習による推測です。rows の積とクエリーが返す実際の行数を比較して、数値が実際と近いかどうかをチェックしてください。数値がかなり異なる場合は、SELECT ステートメントで STRAIGHT_JOIN を使用し、FROM 句で異なる順序でテーブルを一覧表示してみるとパフォーマンスを改善できる可能性があります。

場合によっては、サブクエリーで EXPLAIN SELECT を使用するときに、データを変更するステートメントを実行できることもあります。詳細については、セクション13.2.10.8「FROM 句内のサブクエリー」を参照してください。