MySQL実行計画の詳細な分析

序文

前回の面接では、実行計画について質問されたとき、多くの人がそれが何なのか知りませんでした。実行計画と実行時間は同じ概念だと思っている人もいました。今日は、実行計画とは何か、そしてその用途は何なのかを見てみましょう。

実行計画とは何ですか?

簡単に言えば、実行プランとは、データベースで SQL が実行されたときのパフォーマンスのことです。通常、SQL パフォーマンス分析や最適化などのシナリオで使用されます。

1. 実行計画から何がわかりますか?

SQL がインデックスを使用する方法
結合クエリの実行順序
スキャンしたデータのクエリ機能

2. 実施計画の内容

SQL実行プランの出力は複数行になる場合があり、各行はデータベースオブジェクトに対する操作を表します。

1. ID列

ID 列のデータが数値のセットである場合、それは SELECT ステートメントが実行される順序を示します。NULL の場合、このデータ行は他の 2 つの SQL ステートメントの UNION 操作によって生成された結果セットであることを意味します。
ID値が同じであれば、SQL実行順序は図のように上から下になることを意味します。
ID値が異なる場合は、ID値が大きいほど優先度が高くなり、早く実行されます。

デモ

上記の実行プランでは 3 行の結果が返されることがわかります。id 列の値は、SQL の SELECT 操作のシーケンス番号と見なすことができます。

上記の SQL には SELECT が 1 つしかないため、すべての ID は 1 です。したがって、実行プランを上から下に読み取る必要があります。

SQL ステートメントによれば、実行順序は a、b、c であると考えられますが、上の図から、Mysql は SQL に記述された順序でテーブル関連付け操作を完了しないことがわかります。

テーブルの実行順序は a、c、b です。これは、MySQL オプティマイザがテーブル内のインデックスの統計情報に基づいて、テーブル関連の実際の順序を調整するためです。

2. SELECT_TYPE列

価値	意味
単純	サブクエリや UNION 演算を含まないクエリ
主要な	クエリにサブクエリが含まれている場合、最も外側のクエリはPRIMARYとしてマークされます。
サブクエリ	SELECT リスト内のサブクエリ
依存サブクエリ	外部の結果に依存するサブクエリ
連合	Union演算の2番目以降のクエリの値はunionである
従属連合	UNIONをサブクエリとして使用する場合、2番目以降のクエリのselect_type値は
連合の結果	UNIONによって生成された結果セット
派生	FROM句に現れるサブクエリ

3. テーブル列

以下の結果が含まれます。

データ行が配置されているテーブルの名前を出力します。テーブルに別名がある場合は、別名が表示されます。
<union M,N>: ID MとNのユニオンをクエリして生成された結果セット
<派生 N>/<サブクエリ N>: ID N のクエリによって生成された結果

4. PARTITIONS列:

一致するレコードがどのパーティションから取得されるかを照会します。パーティション分割されたテーブルの場合は、クエリのパーティション ID を表示します。
パーティション化されていないテーブルの場合、これは NULL です。

5. タイプ列

パフォーマンスは最高から最低の順に次のようにリストされます。

価値	意味
システム	これは const 結合タイプの特殊なケースであり、クエリ対象のテーブルに行が 1 つしかない場合に使用されます。
定数	主キーや一意のインデックスに対するクエリなど、テーブル内に一致する行が 1 つしかない場合に使用します。これは最も効率的な結合方法です。
等価参照	ユニークインデックスまたは主キーインデックスクエリ。各インデックスキーに対応し、テーブル内に一致するレコードは 1 つだけです。
参照	一意でないインデックス検索。単一の値に一致するすべての行を返します。
参照またはnull	ref型のクエリに似ていますが、NULL値列のクエリが追加されています。
インデックスマージ	この結合タイプは、インデックスマージ最適化方法が使用されることを示します。
範囲	インデックス範囲スキャンは、between、>、<などのクエリ条件でよく使用されます。
索引	FULLインデックススキャンフルインデックススキャン。ALLとの違いは、インデックスツリーを走査することです。
全て	FULL TABLEスキャン最も効率の悪い結合方法であるフルテーブルスキャン

6. 追加列

MySQLがクエリを実行する方法に関する追加情報が含まれています

価値	意味
明確な	最初に一致する要素が見つかったら検索を停止するように、distinct 操作を最適化します。
存在しない	クエリを最適化するためにnot existsを使用する
ファイルソートの使用	通常は order by または group by クエリによる追加操作による並べ替え
インデックスの使用	クエリにカバーインデックスを使用する
一時的な使用	MySQL では、並べ替え、サブクエリ、グループ化クエリでよく使用されるクエリを処理するために一時テーブルを使用する必要があります。
where の使用	データをフィルタリングするには、MySQLサーバーレベルでWHERE条件を使用する必要があります。
最適化されたテーブルを選択する	テーブルにアクセスせずにインデックスを介して直接データを取得するのが、通常は最も効率的です。

7. POSSIBLE_KEYS列

MySQLがクエリを最適化するために使用できるインデックスを示します

クエリに関係する列のインデックスはリストされますが、使用されない可能性があります。

8. KEY列

クエリを最適化するためにクエリオプティマイザが実際に使用するインデックス

テーブルに利用可能なインデックスがない場合、NULLとして表示されます。

クエリでカバーリングインデックスが使用される場合、インデックスはキー列にのみ表示されます。

9. KEY_LEN列

MySQL インデックスで使用されるバイト数を表示します。結合インデックスに 3 つの列があり、3 つの列の合計長が 100 バイトの場合、Key_len は 100 バイト未満 (30 バイトなど) になることがあります。これは、クエリ中に結合インデックスのすべての列が使用されるわけではなく、最初の 1 列または 2 列のみが使用されることを意味します。