MySQL での実行計画の explain コマンド例の詳細な説明

序文

explain コマンドは、クエリ オプティマイザーがクエリの実行を決定した方法を確認する主な方法です。

この機能には制限があり、必ずしも真実を伝えるわけではありませんが、その出力は入手可能な最良の情報であり、クエリがどのように実行されるかを知ることができるため、時間をかけて理解する価値があります。

EXPLAINの呼び出し

SELECT ステートメントの前に EXPLAIN を追加すると、MySQL はクエリにフラグを設定し、クエリ プランの実行時に、実行プランを実行する代わりに、実行プランの各ステップに関する情報を返すようになります。

実行プランの各部分とそれらが実行される順序を示す 1 行以上の情報を返します。

簡単な説明効果は次のとおりです。

クエリ内の各テーブルの出力には 1 行のみが含まれます。クエリが 2 つのテーブルの結合である場合は、出力に 2 行が含まれます。

エイリアス形式は 1 つのテーブルとしてカウントされるため、テーブルをそれ自体に結合すると、出力には 2 つの行が含まれます。

ここでの「テーブル」の意味は非常に広く、サブクエリや結合結果なども含まれます。

同時に 2 つの説明バリエーションがあります。

EXPLAIN EXTENDED は、実行プランを SELECT ステートメントに「逆コンパイル」するようにサーバーに指示します。

この生成されたステートメントは、直後に show warnings を実行することで確認できます。このステートメントは、この時点でデータ構造になっている元の SQL ステートメントではなく、実行プランから直接取得されます。

ほとんどのシナリオでは、元のステートメントとは異なります。クエリ オプティマイザーがステートメントをどのように変換するかを確認できます。

EXPLAIN EXTENDED は MySQL 5.0 以降で使用可能で、フィルタリングされた列は 5.1 で追加されました。

EXPLAIN PARTITIONS は、クエリがパーティション化されたテーブルに対して実行された場合に、クエリがアクセスするパーティションを表示します。

MySQL 5.1 以降に存在します。

EXPLAIN の制限:

explain では、トリガー、ストアド プロシージャ、または UDF がクエリにどのように影響するかについてはまったく説明されません。

ストアドプロシージャはサポートされていませんが、クエリを手動で抽出して個別に説明することは可能です。

MySQL が実行計画で行った特定の最適化については説明されません。

クエリの実行プランに関するすべての情報は表示されない

同じ名前のものを区別しません。たとえば、メモリ内ソートと一時ファイルの両方に「filesort」を使用し、ディスク上とメモリ内の一時テーブルの両方に「Using temporary」と表示されます。

誤解を招く可能性があります。たとえば、制限が少ないクエリに対して完全なインデックス スキャンが表示されることがあります (mysql 5.1 の explain では、チェックされた行数に関するより正確な情報が表示されますが、それ以前のバージョンでは制限が考慮されていません)。

非SELECTクエリの書き換え

mysql explain は選択クエリのみを説明でき、ストアド プロシージャ呼び出しや挿入、更新、削除、その他のステートメントは説明できません。ただし、特定の非選択クエリを書き換えて、explain を活用することができます。これを実現するには、ステートメントを、同じ列すべてにアクセスする同等の SELECT に変換するだけです。追加の列は、SELECT リスト、JOIN 句、または WHERE 句に含まれている必要があります。

次の更新文を、explainを使用できるように書き直したいとします。

sakila.actor を更新
sakila.film_actor を (actor_id) を使用して内部結合します。
actor.last_update を film_actor.last_update に設定します。

次の explain ステートメントは、サーバーが任意のテーブルから last_update 列を取得する必要がないため、上記の更新と同等ではありません。

この区別は非常に重要です。たとえば、出力では、MySQL がカバー インデックスを使用することを示しています。ただし、last_updated 列を取得および更新する場合、カバー インデックスは使用できません。次の書き直しは、元のステートメントに近いものです。

このようにクエリを書き直すことはそれほど科学的ではありませんが、クエリが何を実行しているかを理解するのに十分なものです。
(MySQL 5.6 では非 SELECT クエリの説明が可能になります)

クエリ プランを表示する場合、書き込みクエリに「同等の」読み取りクエリは存在しないことを理解することが重要です。 SELECT クエリでは、データのコピーを 1 つ見つけて返すだけです。データを変更するクエリでは、すべてのインデックスにあるデータのすべてのコピーを検索して変更する必要があり、一見同等の SELECT クエリよりもはるかにコストがかかることがよくあります。

EXPLAINの列

説明結果の各列の意味については、次のセクションで説明します。

出力の行は、mysql がクエリの各部分を実際に実行した順序で表示されます。これは、元の SQL に表示される順序と必ずしも同じではありません。

[id列]

この列には、選択が属する行を識別する番号が常に含まれています。ステートメントにサブクエリや結合がない場合、選択は 1 つだけなので、各行のこの列には 1 が表示されます。それ以外の場合、内部の選択ステートメントは通常、元のステートメント内の位置に応じて順番に番号が付けられます。

MySQL は、選択クエリを単純なタイプと複雑なタイプに分類します。複雑なタイプは、単純なサブクエリ、いわゆる派生テーブル (from 句のサブクエリ)、およびユニオン クエリの 3 つのカテゴリに分類できます。

以下は簡単なサブクエリです。

from 句のサブクエリと結合により、id 列の複雑さがさらに増します。

以下は from 句の基本的なサブクエリです。

ご存知のとおり、このクエリは匿名の一時テーブルを使用して実行されます。MySQL は、外部クエリ内でこの一時テーブルをエイリアス (der) を介して内部的に参照します。より複雑なクエリでは、ref 列が表示されます。

最後に、結合クエリを次に示します。

3 行目の追加行に注意してください。結合の結果は常に匿名の一時テーブルに配置されます。その後、MySQL は一時テーブルから結果を読み取ります。一時テーブルは元の SQL には表示されないため、その id 列は null になります。

前の例 (サブクエリの from 句を示した) と比較すると、このクエリの結果の一時テーブルは、結果の最初の行ではなく最後の行として表示されます。

ここまでは非常に簡単ですが、この 3 種類のステートメントを混在させると、出力がかなり複雑になる可能性があります。これについては後ほど説明します。

[select_type列]

この列には、対応する行が単純な選択か複雑な選択かが表示されます (後者の場合は、3 つの複雑さのタイプのどれかが表示されます)。 simple 値は、クエリにサブクエリまたはユニオンが含まれていないことを意味します。クエリに責任のあるサブパーツがある場合、最も外側のパーツがプライマリとしてマークされ、他のパーツは次のようにマークされます。

サブクエリ

選択リストにあるサブクエリ（つまり、from句に含まれていない）に含まれる選択は、SUBQUERYとしてマークされます。

派生

DERIVED 値は、FROM 句に含まれるサブクエリの選択が MySQL によって再帰的に実行され、結果が一時テーブルに配置されることを示すために使用されます。一時テーブルはサブクエリから派生するため、サーバーは内部的にこれを「派生テーブル」と呼びます。

連合

UNION 内の 2 番目以降の選択は unoin としてマークされ、最初の選択は外部クエリの一部として実行されたかのようにマークされます。このため、前の例のユニオンの最初の選択はプライマリとして表示されます。 UNION が from 句のサブクエリに含まれている場合、その最初の SELECT は派生としてマークされます。

連合の結果

ユニオンの匿名一時テーブルから結果を取得するために使用される選択は、UNION RESULT としてマークされます。

これらの値に加えて、SUBQUERY と UNION を DEPENDENT および UNCACHEABLE としてマークすることもできます。

DEPENDENT は、選択が外部クエリで見つかったデータに依存することを意味します。

UNCACHEABLE は、選択の一部の特性により、結果が Item_cache にキャッシュされないことを意味します。

(Item_cache は文書化されておらず、クエリ キャッシュと同じものではありませんが、RAND() 関数などの同じタイプの構成要素によって無効にすることができます。)

【表の列】

この列は、対応する行がどのテーブルにアクセスしているかを示します。通常の場合、これは非常に単純です。つまり、テーブル、またはそのテーブルの列 (SQL で別名が定義されている場合) です。

この列では、MySQL の結合オプティマイザがクエリに対して上から下に向かって選択する結合順序を確認できます。たとえば、次のクエリでは、MySQL が選択する結合順序がステートメントで指定された順序と異なることがわかります。

MySQL の実行プランは常に左側の深さ優先ツリーです。このプランを逆さまにすると、リーフ ノードを順番に読み取ることができ、これは explain の行に直接対応します。前のクエリ プランは次の図のようになります。

派生テーブルとユニオン

from 句にサブクエリまたは結合がある場合、テーブル列ははるかに複雑になります。これらのシナリオでは、MySQL によって作成された匿名の一時テーブルはクエリの実行中にのみ存在するため、参照する「テーブル」は実際には存在しません。

from 句にサブクエリがある場合、テーブル列は <derivedN> の形式になります。ここで、N はサブクエリの ID です。これは常に「前方」を参照します。つまり、N は explain 出力の次の行を指します。

結合がある場合、結合結果のテーブル列には結合に参加する ID のリストが含まれます。これは常に「後方参照」になります。これは、結合の結果が結合に参加しているすべての行の後に表示されるためです。リストに 20 を超える ID がある場合は、テーブル列が長くなりすぎないように切り捨てられ、その時点ですべての値を表示できなくなります。幸いなことに、最初の行の ID を確認できるため、どの行が含まれているかを推測することは可能です。また、この行と結合結果の間に表示されるものはすべて何らかの方法で含まれます。

複雑な選択タイプの例

ここでは、特定の種類の複雑な選択の簡潔な例として、意味のないクエリを使用します。

limit 句は、結果を説明せずにプログラムを実行する予定の場合の便宜のためだけのものです。

以下に、explain の結果の一部を示します。

問題を特定できるように、各クエリ部分が意図的に異なるテーブルにアクセスするようにしましたが、上記のことから始めても解決するのはまだ困難です。

1 行目は der_1 を参照します。このクエリは <derived3> としてマークされていますが、これは元の SQL の 2 行目です。出力のどの行が <derived3> の選択ステートメントを参照しているかを知るには、読み進めてください。

2 行目の ID は 3 です。これは、クエリの 3 番目の選択の一部だからです。これは、from 句のサブクエリ内にネストされているため、派生として分類されます。これは、元の SQL の 4 行目です。

3 行目の ID は 2 で、これは元の SQL の 3 行目です。この行は ID が高い行の後にあるため、後で実行されることが示唆されており、これは妥当です。これは、依存サブクエリとして分類されます。つまり、その結果は外部クエリ (つまり、相関サブクエリ) に依存します。この例の外部クエリは行 2 から始まり、der_1 からデータを選択します。

4 行目はユニオンとして分類されます。つまり、ユニオン内の 2 番目以降の選択であり、そのテーブルは <derived6> です。つまり、from 句のサブクエリからデータを取得し、それをユニオンの一時テーブルに追加します。前と同様に、このサブクエリのクエリ プランを示す説明行を見つけるには、下にスクロールします。

5 行目は元の SQL の 8 行目の der_2 サブクエリであり、explain ではこれを <derived6> と呼び出します。

6 行目は、<derived6> の選択リスト内の通常のサブクエリであり、その ID は 7 です。これは非常に重要です...

...これは、行 7 の ID である 5 より大きいためです。なぜそれが重要なのでしょうか? <derived6> サブクエリの境界を示しているためです。 explain が、選択タイプが derived の行を出力する場合、それは「ネストされた範囲」の開始を示します。後続の行の ID が小さい場合 (この場合、5 は 6 より小さい)、ネストされたスコープが閉じられていることを意味します。これにより、7 行目が <derived6> からデータを取得する選択リストの一部であることがわかります。たとえば、4 行目の選択リストの最初の部分 (元の SQL では 7 行目) です。この例は、ネストされたスコープの意味とルールを知らなくてもかなり簡単に理解できますが、もちろん、それほど簡単ではない場合もあります。出力内のこの行について注意すべきもう 1 つの点は、ユーザー変数のため、UNCACHEABLE SUBQUERY としてリストされていることです。

最後の行 (union result) は、union の一時テーブルから行を読み取る段階を表します。必要に応じてこの行から逆方向に作業すると、それぞれ <derived3> と <derived6> を参照する ID 1 と 4 の行の結果が返されます。

ご覧のとおり、これらの選択タイプの複雑な組み合わせにより、explain 出力が理解しにくくなる可能性があります。ルールを理解すれば簡単になりますが、それでも時間がかかります。

explain の出力を読むには、リスト内を移動する必要があることがよくあります。たとえば、出力の最初の行をもう一度見てみると、ただ見ただけではそれが union の一部であるかどうかはわかりません。最後の行を見るまで理解できません。

[列を入力]

MySQL ユーザー マニュアルでは、この列には「関連付けの種類」が表示されると記載されていますが、アクセスの種類、つまり MySQL がテーブル内の行を検索する方法を示す方が正確であると考えられます。最も重要なアクセス方法を、最悪から最善の順に次に示します。

全て：

これはフル テーブル スキャンと呼ばれ、MySQL は必要な行を見つけるためにテーブル全体を最初から最後までスキャンする必要があることを意味します。 （クエリ内で limit が使用されている場合や、追加列に「distinct/not exists を使用しています」と表示される場合など、例外もあります。）

索引：

これはフル テーブル スキャンと同じですが、MySQL は行順ではなくインデックス順にテーブルをスキャンします。主な利点はソートを回避できることです。最大の欠点は、インデックス順にテーブル全体を読み取るオーバーヘッドです。これは通常、ランダムな順序で行にアクセスすると非常にコストがかかることを意味します。

追加列に「インデックスを使用」と表示されている場合、MySQL はインデックス カバーリングを使用していることを意味します。インデックス順にすべての行をスキャンするのではなく、インデックス データのみをスキャンします。これは、インデックス順にテーブル全体をスキャンするよりもはるかに低コストです。

範囲：

範囲スキャンは、インデックス内の特定のポイントから開始し、この範囲の値に一致する行を返す制限付きインデックス スキャンです。インデックス全体をトラバースする必要がないため、フル インデックス スキャンよりも優れています。明らかなスキャンは、where 句に between または > が含まれるクエリです。

MySQL が in() や or リストなどの値の範囲を見つけるためにインデックスを使用する場合、これも範囲スキャンとして表示されます。ただし、これら 2 つは実際にはまったく異なるアクセス タイプであり、パフォーマンスに重要な違いがあります。

このタイプのスキャンのコストは、インデックス スキャンのコストと同程度です。

参照:

これは、単一の値に一致するすべての行を返すインデックス アクセス (インデックス シークと呼ばれることもあります) です。ただし、複数の条件に該当する行が見つかる可能性があるため、シークとスキャンを組み合わせたものになります。このタイプのインデックス アクセスは、一意でないインデックスまたは一意のインデックスの一意でないプレフィックスを使用する場合にのみ発生します。インデックスが参照値と比較されるため、ref と呼ばれます。この参照値は、定数、または複数テーブル クエリ内の前のテーブルの結果値のいずれかです。

ref_or_null は ref のバリエーションであり、mysql が最初の検索の結果で null エントリを見つけるために 2 回目の検索を実行する必要があることを意味します。

等価参照:

このインデックス検索を使用すると、MySQL は基準を満たすレコードを最大 1 つ返すことを認識します。このアクセス方法は、MySQL が主キーまたは一意のインデックス検索を使用し、それらを参照値と比較するときに見られます。 MySQL は、一致する行の範囲を推定したり、一致する行が見つかった後に検索を続行したりする必要がないことを認識しているため、このタイプのアクセスを非常に適切に最適化します。

定数、システム:

MySQL は、クエリの一部を最適化して定数に変換できる場合に、これらのアクセス タイプを使用します。たとえば、行の主キーを where 句に入れて選択すると、MySQL はクエリを定数に変換し、結合からテーブルを効果的に削除できます。

ヌル:

このアクセス方法は、MySQL が最適化フェーズ中にクエリ ステートメントを分解でき、実行フェーズ中にテーブルやインデックスにアクセスする必要がないことを意味します。たとえば、インデックス付き列から最小値を選択するには、実行時にテーブルにアクセスせずに、インデックスを検索するだけで済みます。

[possible_keys列]

この列には、クエリによってアクセスされる列と使用される比較演算子に基づいて、クエリが使用できるインデックスが表示されます。このリストは最適化プロセスの初期段階で作成されるため、リストされているインデックスの一部は、後続の最適化プロセスでは役に立たない可能性があります。

[キーコラム]

この列には、テーブルへのアクセスを最適化するために MySQL が使用するインデックスが表示されます。インデックスが possible_keys 列に表示されない場合は、MySQL が別の理由でそれを選択したことになります。たとえば、where 句がなくてもカバーリング インデックスを選択した可能性があります。

つまり、possible_keys はどのインデックスが検索を効率的に実行するのに役立つかを明らかにし、key はオプティマイザーがクエリ コストを最小限に抑えるためにどのインデックスを使用できるかを示します。次に例を示します。

[key_len列]

この列には、MySQL がインデックスで使用しているバイト数が表示されます。MySQL がインデックスの一部の列のみを使用している場合は、この値を使用してどの列を使用しているかを確認できます。MySQL 5.5 以前のバージョンでは、インデックスの左端のプレフィックスのみを使用できることに注意してください。たとえば、film_actor の主キーは 2 つの smallint 列で、各 smallint 列は 2 バイトであるため、インデックスの各項目は 4 バイトです。次にクエリの例を示します。

結果の key_len 列に基づいて、クエリはインデックス検索を実行するために最初の列 (actor_id 列) のみを使用していると推測できます。列の使用を計算するときは、文字列の文字セット ページを考慮する必要があります。

実行プランを表示します。

このクエリの平均長は 13 バイトで、これは列 a と列 b の合計長です。列 a は 3 文字で、それぞれは UTF8 で最大 3 バイト、列 b は 4 バイトの整数です。

MySQL では、インデックスが実際にどの程度使用されているかが常に表示されるわけではありません。たとえば、プレフィックス パターン マッチで LIKE クエリを実行すると、列の全幅が使用されていることが示されます。

key_len 列には、インデックス フィールドで可能な最大長が表示されます。テーブル内のデータで使用される実際のバイト数ではありません。前の例では、列 a に長さが 1 文字しか含まれていない場合でも、MySQL は常に 13 バイトを表示します。つまり、key_len はテーブル内のデータではなく、テーブル定義を参照して計算されます。

[参照コラム]

この列には、前のテーブルでキー列レコードのインデックスの値を検索するために使用された列または定数が表示されます。次の例は、結合条件とエイリアスの組み合わせを示しています。ref 列は、クエリ テキストで film テーブルが f としてエイリアス化されていることを反映していることに注意してください。

[行 列]

この列は、必要な行を見つけるために MySQL が読み取る必要があると推定する行数です。この数値は、インライン ループ結合プラン内のループの数です。つまり、これは MySQL がテーブルから最終的に読み取ると想定している行数ではなく、クエリの各ポイントで基準を満たす行を見つけるために MySQL が読み取る必要がある行の平均数です。 (この基準には、SQL で指定された条件と、結合順序の前のテーブルの現在の列が含まれます。)

テーブルの統計とインデックスの選択によっては、この推定値は非常に不正確になる可能性があります。MySQL 5.0 以前のバージョンでは、制限句は反映されません。たとえば、次のクエリでは実際には 1057 行はチェックされません。

すべての行の列の値を掛け合わせると、クエリ全体で調べる行数を大まかに見積もることができます。たとえば、次のクエリでは約 2600 行を調べます。

この数は、MySQL が検査すると想定している行数であり、結果セット内の行数ではないことに留意することが重要です。また、連想バッファやキャッシュなどの多くの最適化は、表示される行数に影響を与えないことに注意してください。MySQL は、推定したすべての行を実際に読み取るとは限らず、オペレーティング システムやハードウェア キャッシュについても何も知りません。

[追加コラム]

この列には、他の列に収まらない追加情報が含まれています。ここで表示される値のほとんどは、MySQL ユーザー マニュアルに記載されています。

最も一般的で最も重要な値は次のとおりです。

「インデックスの使用」

この値は、MySQL がテーブルへのアクセスを回避するためにカバー インデックスを使用することを示します。カバーリング インデックスとインデックス アクセス タイプを混同しないでください。

「whereを使う」

これは、MySQL サーバーがストレージ エンジンが行を取得した後に行をフィルター処理することを意味します。多くの where 条件はインデックス内の列に関係しており、ストレージ エンジンがインデックスを読み取るときに (そして読み取るかどうか) チェックできるため、where 句を含むすべてのクエリで "Using where" が表示されるわけではありません。 「WHERE の使用」が存在する場合、クエリが別のインデックスから恩恵を受ける可能性があることを示唆することがあります。

「一時的な使用」

これは、MySQL がクエリ結果をソートするときに一時テーブルを使用することを意味します。

「ファイルソートの使用」

これは、MySQL がテーブルからインデックス順に行を読み取るのではなく、外部インデックスを使用して結果を並べ替えることを意味します。 MySQL には 2 つのファイル ソート アルゴリズムがあり、どちらもメモリ内またはディスク上で実行できます。Explain では、MySQL がどのファイル ソートを使用するかはわかりません。また、ソートがメモリ内で実行されるかディスク上で実行されるかもわかりません。

「各レコードの範囲チェック(インデックスマップ: N)」

これは、使用できる適切なインデックスが存在しないことを意味します。新しいインデックスは結合の各行で再評価されます。N は、possible_keys 列に表示されるインデックスのビットマップであり、冗長です。

ツリー形式で出力

MySQL ユーザーは、実行プランをより正確に表示するために、explain の出力をツリーにフォーマットすることを好むことが多いです。実際、explain で実行プランを表示する方法は確かに少し不格好で、ツリー構造は表形式の出力には適していません。余分な列に大量の値がある場合、欠点はより明白になります。union を使用する場合も同様です。union は、MySQL が実行できる他の種類の結合とは異なり、explain には適していません。

explain のルールと特性を十分に理解していれば、ツリー構造の実行プランを使用することも可能です。しかし、これは少し面倒なので、自動化ツールに任せるのが最善です。Percona Toolkit には、そのようなツールである pt-visual-explain が含まれています。

MySQL 5.6 の改善点

MySQL 5.6 には、説明に関する重要な改善点、つまり更新、挿入などのクエリを説明する機能が含まれます。DML ステートメントは同等の「選択」クエリに変換して説明できますが、結果はステートメントの実行方法を完全に反映するものではないため、これは依然として非常に役立ちます。 Percona Toolkit で pt-upgrade のようなものを開発する際にこの手法を使用しようとしましたが、クエリを SELECT に変換するときに、オプティマイザーが期待どおりのコード パスを実行できないことが何度もわかりました。したがって、クエリを選択に変換せずに説明すると、実行プロセス中に何が起こるかを理解するのに非常に役立ちます。

MySQL 5.6 には、クエリの最適化と実行エンジンに対する一連の変更も含まれます。これにより、匿名の一時テーブルを、この一時テーブルを使用するクエリの一部を最適化して実行するときに常に作成してデータを入力するのではなく、できるだけ遅くマテリアライズできるようになります。これにより、MySQL は、サブクエリを実際に最初に実行することなく、サブクエリを含むクエリ ステートメントを直接解釈できるようになります。

最後に、MySQL 5.6 では、サーバーに最適化追跡機能を追加することでオプティマイザーが改善され、ユーザーはオプティマイザーによる決定だけでなく、入力 (インデックスのカーディナリティなど) や決定の理由も確認できるようになります。これは、サーバーが選択した実行プランを理解するだけでなく、そのプランを選択した理由を理解するのにも非常に役立ちます。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。