MySQL実行計画の詳細な説明

EXPLAIN ステートメントは、MySQL がステートメントを実行する方法に関する情報を提供します。 EXPLAIN は、SELECT、DELETE、INSERT、REPLACE、および UPDATE ステートメントで使用されます。

EXPLAIN は、SELECT ステートメントで使用されるテーブルごとに 1 行を返します。出力内のテーブルは、MySQL がステートメントの処理中に読み取る順序でリストされます。 MySQL は、ネストされたループ結合方式を使用してすべての結合を解決します。つまり、MySQL は最初のテーブルから行を読み取り、次に 2 番目のテーブル、3 番目のテーブル、というように一致する行を検索します。すべてのテーブルを処理した後、MySQL は選択された列を出力し、一致する行がさらにあるテーブルが見つかるまでテーブルのリストを遡ります。このテーブルから次の行を読み取り、次のテーブルの処理を続行します。

1. EXPLAIN出力列

以下にいくつかの重要なコラムを示します。

• タイプ: 接続タイプ
• possible_keys : オプションのインデックス
• キー: 実際の実行中に使用されるインデックス
• ref : ref 列は、テーブルから行を選択するために、前のキー列に示された名前付きインデックスと比較される列または定数を示します。
• 行: 行列は、クエリを実行するために MySQL が調べる必要があると考える行数を示します。
  

2. 接続タイプ

接続タイプは、最良から最悪の順に次のとおりです。

システム

表には行が 1 つだけあります。これは const join 型の特殊なケースです。

定数

テーブルには一致する行が最大 1 つあり、クエリの開始時に読み取られます。行は 1 つしかないため、この行の列の値は、オプティマイザーの残りの部分では定数として扱うことができます。 Const テーブルは一度だけ読み取られるため、非常に高速です。

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;

等価参照

前の表の行の組み合わせごとに、この表から行を読み取ります。システム型と const 型を除けば、これは最適な結合型です。インデックスのすべての部分が結合によって使用され、インデックスが PRIMARY KEY または UNIQUE NOT NULL インデックスである場合に使用します。

eq_ref は、= 演算子を使用して比較されるインデックス付き列で使用できます。比較値は、定数、またはこのテーブルの前に読み取られたテーブルの列を使用した式にすることができます。

たとえば、次の例では、MySQL は eq_ref join を使用して ref_table を処理できます。

参照テーブル、その他のテーブルから * を選択
 ここで、ref_table.key_column=other_table.column;

参照テーブル、その他のテーブルから * を選択
 ここで、ref_table.key_column_part1=other_table.column であり、ref_table.key_column_part2=1 です。

参照

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

ref は、= または <=> 演算子を使用して比較されるインデックス付き列で使用できます。

たとえば、次の例では、MySQL は ref 接続を使用して ref_table を処理できます。

SELECT * FROM ref_table WHERE key_column=expr;

参照テーブル、その他のテーブルから * を選択
 ここで、ref_table.key_column=other_table.column;

参照テーブル、その他のテーブルから * を選択
 ここで、ref_table.key_column_part1=other_table.column
 かつ、ref_table.key_column_part2=1;

全文

FULLTEXTインデックスを使用して結合を実行する

参照またはnull

この結合タイプは ref に似ていますが、MySQL は NULL 値を含む行も検索します。この結合タイプの最適化は、サブクエリを解決するために最もよく使用されます。

たとえば、次の例では、MYSQL は ref_or_null を使用して ref_table を処理できます。

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

インデックスマージ

この結合タイプは、インデックス マージ最適化が使用されることを示します。この場合、出力行のキー列には使用されるインデックスのリストが含まれ、key_len には使用されるインデックスのキー部分の最長リストが含まれます。

ユニークサブクエリ

このタイプは、eq_ref を次の形式の IN サブクエリに置き換えます。

値 IN (SELECT primary_key FROM single_table WHERE some_expr)

インデックスサブクエリ

unique_subquery と同様に、IN サブクエリを置き換えますが、次の形式のサブクエリ内の非一意のインデックスで機能します。

値 IN (SELECT key_column FROM single_table WHERE some_expr)

範囲

インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。出力行のキー列は、使用されたインデックスを示します。 key_len には、使用される最長のキー部分が含まれます。このタイプの場合、ref 列は NULL です。

=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、または 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);

索引

インデックス結合タイプはすべてと同じですが、違いはインデックス結合タイプがインデックス ツリーをスキャンすることです。通常、これは次の 2 つの状況でのみ発生します。

• インデックスがクエリのカバー インデックスであり、テーブルで必要なすべてのデータを満たすために使用できる場合は、インデックス ツリーのみがスキャンされます。この場合、[追加] 列に [インデックスの使用] と表示されます。通常、インデックスのサイズはテーブル データよりも小さいため、インデックスのみのスキャンは ALL スキャンよりも高速です。
• インデックスからデータを読み取るを使用して完全なテーブルスキャンを実行し、インデックス順にデータ行を検索します。 「インデックスを使用」は「追加」列に表示されません。

全て

前のテーブルの行の組み合わせごとに完全なテーブルスキャンが実行されます。テーブルが const としてマークされていない最初のテーブルである場合、これは通常悪い結果となり、その他のすべてのケースでは通常非常に悪い結果となります。多くの場合、定数値または以前のテーブルの列値に基づいてテーブルから行を取得できるようにするインデックスを追加することで、ALL を回避できます。

3. 追加列

Extra 列の出力に関しては、一般的なものをいくつか示します。

ファイルソートの使用

MySQL は、ソートされた順序で行を取得する方法を判断するために追加の操作を実行する必要があります。ソートは、結合タイプに従ってすべての行を反復処理し、WHERE 句に一致するすべての行のソート キーと行へのポインターを格納することによって行われます。次にキーがソートされ、ソートされた順序で行が取得されます。

インデックスの使用

実際の行を読み取るための追加のシークを実行せずに、インデックス ツリーの情報のみを使用してテーブルから列情報が取得されます。この戦略は、クエリが単一のインデックスに属する列のみを使用する場合に使用できます。

一時的な使用

クエリを解析するには、MySQL は結果を保持するための一時テーブルを作成する必要があります。通常、これは、列を異なる方法で表示する GROUP BY 句と ORDER BY 句がクエリに含まれている場合に発生します。

where の使用

WHERE 句は、どの行が次のテーブルに一致するか、またはクライアントに送信されるかを制限するために使用されます。テーブルからすべての行を取得または検査するつもりがない限り、追加の値が where で使用されず、テーブル結合タイプが all または index である場合、クエリでエラーが発生する可能性があります。

4. ORDER BYを最適化する

場合によっては、MySQL は ORDER BY 句を満たすためにインデックスを使用することがあり、これにより、ファイルソート操作の実行に伴う余分なソートが回避されます。

(key_part1、key_part2) にインデックスがあると仮定すると、次のクエリはインデックスを使用して ORDER BY 部分を解決できます。オプティマイザが実際にこれを実行するかどうかは、インデックスの外部も読み取る必要がある場合に、インデックスの読み取りがテーブル スキャンよりも効率的かどうかによって決まります。

SELECT * FROM t1 ORDER BY key_part1, key_part2;

上記のステートメントでは、クエリは SELECT * を使用しており、key_part1 および key_part2 よりも多くの列が選択される場合があります。この場合、インデックス全体をスキャンし、インデックスに含まれていない列のテーブル行を検索すると、テーブルをスキャンして結果を並べ替えるよりもコストがかかる可能性があります。その場合、オプティマイザーがインデックスを使用する可能性は低くなります。 SELECT * がインデックス付き列のみを選択する場合、インデックスが使用され、ソートは回避されます。

次のクエリでは、key_part1 は定数であるため、インデックスを介してアクセスされるすべての行は key_part2 の順序になります。また、WHERE 句の選択性が十分に高く、インデックス範囲スキャンがテーブル スキャンよりも安価であれば、(key_part1、key_part2) のインデックスによってソートを回避できます。

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

以上がMySQL実行プランの詳しい内容です。MySQL実行プランの詳細については、123WORDPRESS.COMの他の関連記事もご覧ください。