My SQL EXPLAINクエリプランの詳細

EXPLAINは、My SQLがSQL 文に対して生成した実行計画を表示するために使用されます。実行プランでは、アクセス順序、インデックスの選択、スキャンの推定行数、ソート、一時テーブルなどの情報を分析できます。

DESCとEXPLAINの違い

テーブル構造を見る

DESCはDESCRIBEの略で、テーブルまたはビューのフィールド情報を表示します。

DESC employee;

DESCRIBE employee;

通常、2つの文には、フィールド名、データ型、NULLを許可するかどうか、インデックスタイプ、デフォルト値、および追加情報が表示されます。

SQL 実行プランの表示

クエリ文の前にEXPLAINを追加すると、オプティマイザが選択した実行プランを表示できます。

EXPLAIN
SELECT *
FROM employee
WHERE first_name = 'tom';

My SQLは他の出力形式もサポートしています。例えば、ツリー形式は実行順序を観察しやすいです。

EXPLAIN FORMAT=TREE
SELECT *
FROM employee
WHERE first_name = 'tom';

EXPLAIN ANALYZEはSQLを実際に実行し、推定値と実際の実行データを返します。更新文や削除文は、誤ってデータを変更しないように注意してください。

EXPLAIN ANALYZE
SELECT *
FROM employee
WHERE first_name = 'tom';

EXPLAIN共通のリスト

従来の表形式には、通常、次の列が含まれます。

リスト	機能
id	クエリブロック番号は、クエリブロック间の実行の判断にできる
select_type	クエリ·ブロックのタイプ単纯クエリ、メイン·クエリ、サブクエリ、抽出テーブルなど
table	現在アクセスされているテーブル、別名、または内部一時結果
partitions	アクセスが予想されるパーティション。パーティションテーブルが使用されていない場合は通常NULL
typeは	テーブルアクセス方式は、インデックスの使用状況を判断する重要な指標です。
possible_keys	オプティマイザが使用する可能性があると考えるインデックス
key	実際に選択されたインデックス
key_len	使用される予定のインデックス·キーの長さ（バイト単位）
ref	インデックス·カラムと比較される定数またはカラム
rows	オプティマイザチェックが必要なロー数の推定{{おぷてぃまいざちぇ っくが必要なロー数の推定}}
filtered	現在のテーブル条件でフィルタ処理された後に予想される保持率
Extra	ソート、テンポラリ·テーブル、上書き索引などの補足情報

idクエリブロック番号

idは異なるクエリブロックを識別するために使用されますが、単純に絶対的な実行順序として扱うことはできません。

• id同じ複数のローは、通常、同じクエリ·ブロックに属し、テーブルの表示順序は、そのジョイン·プラン内のアクセス順序を反映します。
• idが異なることは、通常、サブクエリ、派生テーブル、またはUNIONなどの複数のクエリ·ブロックが存在することを示す。
• 結果行によっては、idがNULLになることもあります。たとえば、UNION RESULTです。

複雑なSQLの実際の実行は、FORMAT=TREEまたはEXPLAIN ANALYZEと組み合わせて判断する必要があります。

select_type問合せタイプ

通常値は以下の通り。

値	意味
“	UNIONまたはサブクエリを含まない単純なクエリ
PriMARY	最も外側の検索
UN	UNION内の2 番目以降のクエリブロック
DEPENDENT UNION	クエリの
`UNION RESULT	UNION結果セット
SUBQUERY	外部クエリに依存しないサブクエリ
DEPENDENT SUBQUERY	外部クエリの結果に依存する関連サブクエリ
DERIVED	FROM句内の抽出テーブル{{FROMくないのしゅうせいてーぶる}}
MATERIALIZED	オブジェクト化後に再利用されたサブクエリ結果

table：オブジェクトへのアクセス

tableでは、一般にテーブル名またはテーブル別名が表示されます。オプティマイザによって生成される内部結果には、次の形式が表示されます。

• <derivedN>番号Nの派生テーブルクエリブロックから。
• <unionM，N>：番号M，NなどのクエリブロックからのUNION結果.
• `：オブジェクト化されたサブクエリの結果。

typeアクセス方法

typeは、My SQLがテーブルからデータを読み取る方法を説明します。以下に一般的な型を正確な順から広い順に示しますが、実際のパフォーマンスは返される行数、データ分布、キャッシュなどの要因にも影響されます。

タイプ	摘要
system	表は1 行のみと見なされ，constの特例である
const	プライマリ·キーまたはユニーク·インデックスの同値一致により、最大 1つのローを返す
eq_ref	プライマリ·キーまたはNULLでないユニーク·インデックスによるジョイン時のマッチング。上位テーブルの組み合わせごとに最大 1ローがヒット
ref	通常のインデックスまたはユニークなインデックスの不完全なプレフィックスによる等価検索。複数のローを返すことがある。
fulltext	全文索引の使用
ref_or_null	refに類似し、NULLを追加検索する
index_merge	複数のインデックスのスキャン結果の結合
unique_subquery	一部のINサブクエリはユニークなインデックスで検索されます
index_sub	一部のINサブクエリはユニークでないインデックスで検索されます
range	インデックスに対する範囲スキャンの実行
index	インデックス全体のスキャン
ALL	テーブル全体をスキャン

ALLとindexに焦点を当てて分析することが多い。しかし、小さなテーブル全体のスキャンは必ずしも問題ではなく、typeだけでSQLの最適化が必要かどうかを判断することはできません。

インデックス関連カラム{{いんでっくすかんすうからむ}}

possible_keys

オプティマイザが使用する可能性があると考えるインデックスを表示します。値NULLは、現在のクエリに明確に使用可能なインデックスがないことを示しますが、SQLにパフォーマンス上の問題があるとは限りません。

key

オプティマイザが最終的に選択したインデックスが表示されます。possible_keysに複数のインデックスがリストされている場合でも、keyには通常、実際に採用されたインデックスのみが表示されます。index_mergeを使用すると、複数のインデックスが表示される場合があります。

key_len

実行計画で使用されると予想されるインデックス·キーの長さが表示されますユニオンインデックスの場合、この値はどのインデックス列が使用されているかを判断するのに役立ちます。

key_lenは推定された最大長であり、実際に読み取られたデータの長さを表しません。文字セット、空可カラム、可変長フィールド、インデックス付きカラム型はすべてこの値に影響します。

ref

インデックス検索時に比較に使用される値を表示します。例えば：

• const：定数と比較する.
• 库名.表名.列名：別の表の列との比較。
• func：式、関数の結果、または型変換が行われた値との比較。

rowsとfiltered

rows

rowsは、オプティマイザがチェックする必要があると推定した行数であり、実際のスキャン行数ではありません。統計情報が不正確な場合、推定値にばらつきが生じる可能性がある。

filtered

filteredは、現在のテーブル条件によってフィルタリングされた後に保持されると予想されるデータの割合であり、パーセンテージで表されます。

次のステップに渡される行数は、次の方法で概算できます。

rows × filtered ÷ 100

実際の実行行数と時間を観察するには、EXPLAIN ANALYZEを使用してください。

Extra：補足情報

Extraの一般情報は以下の通りです。

情報	説明
Using index	必要なカラムは、上書きインデックスを使用して取得できます。通常、テーブルに戻る必要はありません
Using where	データを読み込んだ後に条件に基づいてフィルタリングする必要があります
Using index condition	インデックス条件下プッシュを使用して、ストレージ·エンジン層でレコードの一部をフィルタリングする
Using filesort	ソートは適切なインデックスを直接利用できず、追加のソートが必要です。必ずしもディスクファイルに書き込まれません。
Using temporary	内部テンポラリ·テーブルを使用した中間結果の保存（部分的なグループ化、重複除去、ソート操作でよく見られる）
Using join buffer	接続プロシージャは接続バッファを使用します。通常は接続条件とインデックスのチェックが必要です。
First	ハーフジョイン最適化戦略の1つで、最初の一致が見つかったら検索を停止します。
LooseScan	半接続最適化戦略の1つで、インデックスホップスキャンによる重複マッチングの削減
“Impossible WHERE”	オプティマイザはクエリ条件が不可能であると判断

Extraは複数の情報を同時に表示できます。例えば：

Using index condition; Using where

基本分析の考え方

実行計画を解析するときは、次の順序でチェックできます。

1. テーブルのアクセス順序が合理的であることを確認します。

2. typeで、大きなテーブルのALLまたは不要なindexスキャンが発生していないかどうかを確認します。

3. possible_keysとkeyを比較して、インデックスが選択されていることを確認します。

4. key_lenと合わせて連合インデックスの使用範囲を判断する.

5. rows、filtered、およびExtraのソート、テンポラリ·テーブル、および接続バッファ情報に注目します。

6. 重要なSQLにEXPLAIN ANALYZEを使用して、実際の行数と時間で見積もりを検証します。