SQLでEXPLAINコマンドを使用する方法

日常業務では、実行に時間のかかる SQL ステートメントを記録するために、スロー クエリを実行することがあります。これらの SQL ステートメントを見つけても、作業が完了したわけではありません。SQL ステートメントがインデックスを使用しているかどうか、および完全なテーブル スキャンが実行されているかどうかを確認するために、explain コマンドを使用してこれらの SQL ステートメントの実行プランを表示することがよくあります。これは、explain コマンドで表示できます。したがって、MySQL のコストベースのオプティマイザについて深く理解でき、また、オプティマイザが考慮する可能性のあるアクセス戦略や、SQL ステートメントを実行するときにオプティマイザが採用すると予想される戦略に関する多くの詳細も取得できます。

EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述するのに役立ちます。

これを使用するには、select ステートメントの前に explain を追加するだけです。

のように：

a.id=b.id の場合、a,b の形式で姓、名を選択します。 

1. EXPLAIN tbl_name

EXPLAIN tbl_name は、DESCRIBE tbl_name または SHOW COLUMNS FROM tbl_name の同義語です。

2. EXPLAIN [拡張] SELECT select_options

SELECT ステートメントの前にキーワード EXPLAIN を配置すると、MySQL は SELECT の処理方法を説明し、テーブルの結合方法と結合順序に関する情報を提供します。

EXPLAIN を使用すると、インデックスを使用してレコードを検索する SELECT を高速化するために、テーブルにインデックスをいつ追加する必要があるかを知ることができます。

オプティマイザーがテーブルを最適な順序で結合したかどうかも確認できます。オプティマイザーが SELECT ステートメントをテーブルの名前の順序で結合するように強制するには、ステートメントは SELECT だけではなく STRAIGHT_JOIN で始まる必要があります。

EXPLAIN は、SELECT ステートメントで使用されるテーブルごとに 1 行を返します。テーブルは、クエリの処理中に MySQL によって読み取られる順序でリストされます。 MySQL は、シングルスイープマルチ結合アプローチを使用してすべての結合を解決します。つまり、MySQL は最初のテーブルから行を読み取り、次に 2 番目のテーブルで一致する行を見つけ、次に 3 番目のテーブルで一致する行を見つけ、というように繰り返します。すべてのテーブルが処理されると、選択した列が出力され、一致する行がさらにあるテーブルが見つかるまでテーブル リストに戻ります。テーブルから次の行を読み取り、次のテーブルの処理を続行します。

EXTENDED キーワードを使用すると、EXPLAIN は SHOW WARNINGS で表示できる追加情報を生成します。この情報は、SELECT ステートメントでテーブル名と列名を修飾し、最適化ルールを書き換えて実行した後にオプティマイザーが実行する内容を示し、最適化プロセスに関するその他のコメントが含まれる場合もあります。

EXPLAIN の各出力行はテーブルに関する情報を提供し、各行は次の列で構成されます。

id: SELECT 識別子。これは SELECT のクエリ シーケンス番号です。

select_type: SELECT タイプ。

1. SIMPLE: 単純な SELECT (UNION またはサブクエリなし)
2. PRIMARY: 最も外側のSELECT
3. UNION: UNION内の2番目以降のSELECT文
4. 依存UNION: UNION内の2番目以降のSELECT文は外側のクエリに依存します。
5. UNION RESULT: UNIONの結果
6. SUBQUERY: サブクエリの最初のSELECT
7. 依存サブクエリ: 外部クエリに依存するサブクエリの最初のSELECT
8. DERIVED: エクスポートされたテーブルのSELECT (FROM句のサブクエリ)
   

テーブル:テーブル名

タイプ:接続タイプ

1. システム: テーブルには 1 行のみが含まれます (= システム テーブル)。これは const join 型の特殊なケースです。
2. const: テーブルには一致する行が最大 1 つあり、クエリの開始時に読み取られます。行が 1 つしかないため、この行の列値は、オプティマイザーの残りの部分では定数とみなすことができます。 const は、PRIMARY KEY または UNIQUE インデックスのすべての部分を定数値と比較するときに使用されます。
3. eq_ref: 前のテーブルの行の組み合わせごとに、このテーブルから行を読み取ります。これはおそらく、const 型を除いて最適な結合型です。インデックスのすべての部分が結合によって使用され、インデックスが UNIQUE または PRIMARY KEY である場合に使用されます。 eq_ref は、= 演算子を使用して比較されるインデックス付き列で使用できます。比較値は、定数、またはこのテーブルの前に読み取られたテーブルの列を使用した式にすることができます。
4. ref: 前のテーブルの行の組み合わせごとに、一致するインデックス値を持つすべての行がこのテーブルから読み取られます。結合でキーの左端のプレフィックスのみを使用する場合、またはキーが UNIQUE または PRIMARY KEY ではない場合 (つまり、結合でキーに基づいて単一の行を選択できない場合) は、ref を使用します。この結合タイプは、使用されるキーが少数の行にのみ一致する場合に適しています。 ref は、= または <=> 演算子を使用してインデックス付き列で使用できます。
5. ref_or_null: この結合タイプは ref に似ていますが、MySQL が NULL 値を含む行を具体的に検索できる点が追加されています。この結合タイプの最適化は、サブクエリの解決によく使用されます。
6. index_merge: この結合タイプは、インデックス マージ最適化方法が使用されることを示します。この場合、キー列には使用されているインデックスのリストが含まれ、key_len には使用されているインデックスの最長のキー要素が含まれます。
7. unique_subquery: このタイプは、次の形式の IN サブクエリの ref を置き換えます: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery は、サブクエリを完全に置き換えることができ、より効率的なインデックス検索関数です。
8. index_subquery: この結合タイプは unique_subquery に似ています。 IN サブクエリを置き換えることができますが、次の形式のサブクエリ内の非一意のインデックスに対してのみ可能です: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。キー列には、使用されたインデックスが表示されます。 key_len には、使用されているインデックスの最長のキー要素が含まれます。この型ではref列はNULLです。 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、または IN 演算子を使用してキーワード列を定数と比較するときに、範囲を使用できます。
10. index: この結合タイプは、インデックス ツリーのみがスキャンされる点を除いて、ALL と同じです。通常、インデックス ファイルはデータ ファイルよりも小さいため、これは ALL よりも高速です。
11. all: 前のテーブルの行の組み合わせごとに、完全なテーブルスキャンが実行されます。テーブルが const としてマークされていない最初のテーブルである場合、これは通常悪い結果となり、それ以外の場合にも通常非常に悪い結果となります。多くの場合、ALL を使用する代わりにインデックスを追加して、前のテーブルの定数値または列値に基づいて行を取得できます。
    

possible_keys: possible_keys 列は、MySQL がテーブル内の行を見つけるために使用できるインデックスを示します。この列は、EXPLAIN 出力に表示されるテーブルの順序とは完全に独立していることに注意してください。これは、possible_keys 内の一部のキーが、テーブルが生成された順序で実際に使用できないことを意味します。

key: key 列には、MySQL が実際に使用することを決定したキー (インデックス) が表示されます。インデックスが選択されなかった場合、キーは NULL になります。 MySQL で possible_keys 列のインデックスを強制的に使用または無視するには、クエリで FORCE INDEX、USE INDEX、または IGNORE INDEX を使用します。

key_len: key_len 列には、MySQL が使用することを決定したキーの長さが表示されます。キーが NULL の場合、長さは NULL になります。 key_len 値を使用すると、MySQL が実際に使用する複数部分キーワードの部分の数を決定できることに注意してください。

ref: ref 列には、テーブルから行を選択するためにキーとともに使用される列または定数が表示されます。

行:行の列には、クエリを実行するために MySQL が調べる必要があると考える行数が表示されます。

追加:この列には、MySQL がクエリを解決した方法に関する詳細情報が含まれています。

1. Distinct: MySQL は最初に一致する行を見つけた後、現在の行の組み合わせに対するそれ以上の行の検索を停止します。
2. 存在しない: MySQL はクエリに対して LEFT JOIN 最適化を実行できます。LEFT JOIN 基準に一致する 1 つの行が見つかった後、テーブル内の前の行の組み合わせの行はそれ以上チェックされません。
3. 各レコードの範囲がチェックされました (インデックス マップ: #): MySQL は使用する適切なインデックスを見つけられませんでしたが、前のテーブルの列値がわかっている場合は部分インデックスを使用できることがわかりました。前述のテーブルの行の組み合わせごとに、MySQL は range または index_merge アクセス メソッドを使用して行を取得できるかどうかを確認します。
4. filesort の使用: MySQL では、ソートされた順序で行を取得する方法を見つけるために追加のパスが必要です。ソートは、結合タイプに従ってすべての行を調べ、WHERE 句に一致するすべての行のソート キーと行ポインターを保存することによって実行されます。次にキーがソートされ、ソートされた順序で行が取得されます。
5. インデックスの使用: 実際の行をさらに検索して読み取ることなく、インデックス ツリーの情報のみを使用してテーブルから列情報を取得します。この戦略は、クエリが単一のインデックスの一部である列のみを使用する場合に使用できます。
6. 一時テーブルの使用: クエリを解決するには、MySQL は結果を保持するための一時テーブルを作成する必要があります。典型的なケースとしては、クエリに、異なるケースで列をリストする GROUP BY 句と ORDER BY 句が含まれている場合です。
7. where の使用: WHERE 句は、次のテーブルに一致する行やクライアントに送信される行を制限するために使用されます。テーブルのすべての行を明示的に要求または検査しない限り、Extra 値が Using where ではなく、テーブル結合タイプが ALL または index である場合、クエリにエラーが発生する可能性があります。
8. sort_union(...) の使用、union(...) の使用、intersect(...) の使用: これらの関数は、index_merge 結合タイプのインデックス スキャンをマージする方法を示しています。
9. グループ化にインデックスを使用する: テーブルにアクセスするためのインデックスの使用方法と同様に、グループ化にインデックスを使用するということは、MySQL が、実際のテーブルにアクセスするためにハード ディスクを追加検索することなく、GROUP BY または DISTINCT クエリのすべての列をクエリするために使用できるインデックスを見つけたことを意味します。また、インデックスは最も効率的な方法で使用されるため、グループごとに少数のインデックス エントリのみが読み取られます。
   

EXPLAIN 出力の行列のすべての値を乗算すると、結合がどのように機能するかについてのヒントが得られます。これにより、クエリを実行するために MySQL が調べる必要があった行数が大まかにわかります。この製品は、max_join_size 変数を使用してクエリを制限するときに、どのマルチテーブル SELECT ステートメントを実行するかを決定するためにも使用されます。

要約する

SQL の EXPLAIN コマンドの使用に関するこの記事はこれで終わりです。SQL EXPLAIN コマンドの使用に関する詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。