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
によって生成される出力カラムについて説明します。あとのセクションで、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 |
追加情報 |
-
SELECT
識別子。これはクエリー内のSELECT
の連番です。行がほかの行の和集合結果を参照する場合に、値はNULL
になることがあります。この場合、table
カラムには、<union
などの値が表示され、行がM
,N
>M
およびN
のid
値のある行の和集合を参照していることが示されます。 -
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 つめ以降の SELECTDEPENDENT
は一般に、相関サブクエリーの使用を示します。セクション13.2.10.7「相関サブクエリー」を参照してください。DEPENDENT SUBQUERY
の評価はUNCACHEABLE SUBQUERY
の評価とは異なります。DEPENDENT SUBQUERY
の場合、その外部コンテキストの変数の異なる値の各セットにつき、一回だけサブクエリーが再評価されます。UNCACHEABLE SUBQUERY
の場合、外部コンテキストの行ごとにサブクエリーが再評価されます。サブクエリーのキャッシュ可能性は、クエリーキャッシュへのクエリー結果のキャッシュ (これについてはセクション8.9.3.1「クエリーキャッシュの動作」で説明しています) と異なります。サブクエリーのキャッシュは、クエリー実行中に行われ、クエリーキャッシュは、クエリーの実行が終了したあとにのみ、結果を格納するために使用されます。
-
出力の行で参照しているテーブルの名前。これも次のいずれかの値になることがあります。
<union
: 行はM
,N
>M
およびN
のid
値のある行の和集合を参照しています。<derived
: 行はN
>N
のid
値のある行の派生テーブル結果を参照しています。派生テーブルは、たとえばFROM
句内のサブクエリーの結果などになります。<subquery
: 行はN
>N
のid
値のある行の実体化されたサブクエリーの結果を参照しています。セクション8.2.1.18.2「サブクエリー実体化によるサブクエリーの最適化」を参照してください。
-
クエリーでレコードが照合されるパーティション。このカラムは、
PARTITIONS
キーワードが使用されている場合にのみ表示されます。パーティション化されていないテーブルの場合、この値はNULL
です。セクション19.3.5「パーティションに関する情報を取得する」を参照してください。 -
結合型。さまざまな型の説明については、「
EXPLAIN
結合型」を参照してください。 -
possible_keys
カラムは、MySQL がこのテーブル内の行の検索に使用するために選択できるインデックスを示します。このカラムはEXPLAIN
の出力に表示されたテーブルの順序にまったく依存しません。つまり、possible_keys
のキーの一部は、生成されたテーブルの順序で実際に使用できないことがあります。このカラムが
NULL
の場合は、関連するインデックスがありません。この場合、WHERE
句を調査して、それがインデックス設定に適したカラムを参照しているかどうかをチェックすることで、クエリーのパフォーマンスを向上できることがあります。その場合は、適切なインデックスを作成し、再度EXPLAIN
でクエリーをチェックします。セクション13.1.7「ALTER TABLE 構文」を参照してください。テーブルにあるインデックスを確認するには、
SHOW INDEX FROM
を使用します。tbl_name
-
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
カラムは、MySQL が使用することを決定したキーの長さを示します。key
カラムにNULL
と示されている場合、この長さはNULL
になります。key_len
の値によって、MySQL が実際に使用するマルチパートキーのパート数を判断できます。 -
ref
カラムは、テーブルから行を選択するために、key
カラムに指定されたインデックスに対して比較されるカラムまたは定数を示します。値が
func
の場合、使用される値は、特定の関数の結果です。どの関数か確認するには、EXPLAIN EXTENDED
のあとにSHOW WARNINGS
を付けて使用します。関数は、実際には算術演算子などの演算子である場合があります。 -
rows
カラムは、MySQL がクエリーを実行するために調査する必要があると考える行数を示します。InnoDB
テーブルの場合、これは推定値であり、常に正確ではないことがあります。 -
filtered
カラムは、テーブル条件によってフィルタ処理されるテーブル行の推定の割合を示します。つまり、rows
は調査される推定の行数を示し、rows
×filtered
/100
が前のテーブルと結合される行数を示します。EXPLAIN EXTENDED
を使用すると、このカラムが表示されます。 -
このカラムには、MySQL がクエリーを解決する方法に関する追加情報が含まれます。さまざまな値の説明については、「
EXPLAIN
の追加情報」を参照してください。
EXPLAIN 結合型
EXPLAIN
出力の type
カラムには、テーブルの結合方法が示されます。次のリストに、もっとも適切な型からもっとも不適切な型の順番で並べた結合型を示します。
-
テーブルには行が 1 つしかありません (= system テーブル)。これは、
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;
-
前のテーブルの行の組み合わせごとに、このテーブルから 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
は、結合でキーの左端のプリフィクスのみが使用される場合、またはキーが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
インデックスを使用して実行されます。 -
この結合型は、
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 の最適化」を参照してください。
-
この結合型はインデックスマージ最適化が使用されたことを示します。この場合、出力行の
key
カラムには使用されたインデックスのリストが含まれ、key_len
には使用されたインデックスの最長キーパートのリストが含まれます。詳細については、セクション8.2.1.4「インデックスマージの最適化」を参照してください。 -
この型は、次の形式の
IN
サブクエリーのref
を置き換えます。value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery
は、効率化のため、サブクエリーを完全に置き換える単なるインデックスルックアップ関数です。 -
この結合型は
unique_subquery
に似ています。IN
サブクエリーを置き換えますが、次の形式のサブクエリー内の一意でないインデックスに対して機能します。value IN (SELECT key_column FROM single_table WHERE some_expr)
-
行を選択するためのインデックスを使用して、特定の範囲にある行のみが取得されます。出力行の
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
結合型は、インデックスツリーがスキャンされることを除いて、ALL
と同じです。これは 2 つの方法で行われます。インデックスがクエリーのカバリングインデックスで、使用すると、テーブルから必要なすべてのデータを満たすことができる場合、インデックスツリーのみがスキャンされます。この場合、
Extra
カラムにはUsing index
と示されます。インデックスのサイズは通常テーブルデータより小さいため、インデックスのみのスキャンは通常、ALL
より高速です。フルテーブルスキャンは、インデックスからの読み取りを使用して、インデックス順でデータ行をルックアップして実行されます。
Extra
カラムにUses index
が表示されません。
MySQL は、クエリーで単一のインデックスの一部であるカラムのみが使用されている場合に、この結合型を使用できます。
-
フルテーブルスキャンは、前のテーブルの行の組み合わせごとに実行されます。これは、通常テーブルが
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
値のある行と、<subquery
のN
>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
値のある行と、<subquery
のN
>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 句内のサブクエリー」を参照してください。