MySQL での実行計画の詳細分析

序文

効率的なSQL文の書き方は、Explain実行計画の分析と切り離せません。実行計画とは何か、効率的なSQLの書き方について、この記事では一つずつ紹介していきます。

実行計画

実行プランは、SQL ステートメントと関連テーブルの統計情報に基づいてデータベースによって作成されたクエリ ソリューションです。このソリューションは、クエリ オプティマイザーによって自動的に分析され、生成されます。

explain キーワードを使用すると、オプティマイザーが SQL クエリ ステートメントを実行するようにシミュレートできるため、MySQL が SQL ステートメントを処理する方法を理解し、選択ステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析し、選択の効率が低い理由を把握して、クエリを改善できます。

説明の結果は次のようになります。

以下は各列の詳細な紹介です。重要なものは、id、type、key、rows、extra です。

id

• id 列の数字は、選択のシーケンス番号であり、SQL 実行順序の識別子としても理解できます。選択の数だけ id が存在します。
  • 異なる ID 値: クエリのみの場合は、ID 番号が増加し、ID 値が大きいほど優先度が高くなり、早く実行されます。
  • id値は同じです:上から下に実行します。
  • id 列が null です。これは結果セットであり、クエリに使用する必要がないことを意味します。

選択タイプ

クエリ タイプは主に、共通クエリ、結合クエリ、サブクエリ、その他の複雑なクエリを区別するために使用されます。

simple: クエリに union 演算またはサブクエリが含まれていないことを示します。最も外側のクエリの select_type は simple であり、1 つだけです。

 説明 select * from t3 where id=3952602;

primary: 結合操作またはサブクエリを含む選択が必要です。最も外側のクエリの select_type は primary であり、1 つだけです。

select * from (select * from t3 where id=3952602) a を説明します。

派生: from リストに表示されるサブクエリは派生テーブルとも呼ばれます。MySQL はこれらのサブクエリを再帰的に実行し、結果を一時テーブルに配置します。

 select * from (select * from t3 where id=3952602) a を説明します。

サブクエリ: from 句に含まれるサブクエリを除き、他の場所に出現するサブクエリはすべてサブクエリになる場合があります。

select * from t3 where id = (select id from t3 whereid=3952602 ) ; を説明します。

union: 2 番目の select が union の後に現れる場合、union としてマークされます。union が from 句のサブクエリに含まれている場合、外側の select は derived としてマークされます。

select * from t3 where id=3952602 union all select * from t3; について説明します。

ユニオン結果: ユニオン テーブルから結果を取得する選択。クエリに参加する必要がないため、ID フィールドは null です。

 select * from t3 where id=3952602 union all select * from t3; について説明します。

従属ユニオン: union と同様に、union または union all ステートメント内に表示されますが、このクエリは外部クエリの影響を受けます。

従属サブクエリ: 従属ユニオンと同様に、サブクエリの最初の SELECT は外部テーブル クエリの影響を受けます。

テーブル

explain の行がどのテーブルにアクセスしているかを示します。

• クエリでエイリアスが使用されている場合は、そのエイリアスがここに表示されます。
• データ テーブルに対する操作が関係しない場合は、null として表示されます。
• 山括弧で囲まれている場合は、これが一時テーブルであることを意味し、その後ろの N は実行プランの ID であり、結果がこのクエリから取得されることを示します。
• 山括弧で囲まれた <union M,N> の場合は に似ており、これも一時テーブルであり、この結果が ID M,N のユニオン クエリの結果セットから取得されたことを示します。

タイプ

アクセス タイプは、MySQL がテーブル内の行を検索する方法を決定します。

最良から最悪まで: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。all を除き、他のタイプはインデックスを使用できます。index_merge を除き、他のタイプは 1 つのインデックスのみを使用できます。一般的に言えば、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。

1. system: テーブルには 1 行のデータしかありません (システム テーブルと同じです)。これは const 型の特殊なケースであり、通常は表示されないため、無視できます。
2. const: 一意のインデックスまたは主キーを使用します。つまり、インデックスを 1 回使用することで見つけることができます。const は、主キーまたは一意のインデックスを比較するために使用されます。 1 行のデータのみを一致させる必要があるため、非常に高速です。主キーを where リストに入れると、mysql はクエリを const に変換できるようになります。
3. eq_ref: 一意のインデックス スキャン。各インデックス キーに対して、テーブル内に一致するデータの行が 1 つだけ存在します。主キーまたは一意のインデックス スキャンでよく使用されます。
4. ref: 単一の値に一致するすべての行を返す非一意のインデックス スキャン。エッセンスもインデックスです。
5. fulltext: 全文インデックスの取得。全文インデックスは優先度が高いです。全文インデックスと通常のインデックスが同時に存在する場合、MySQL はコストに関係なく全文インデックスを優先します。
6. ref_or_null: ref メソッドに似ていますが、null 値の比較が追加されています。
7. index_merge: クエリが 2 つ以上のインデックスを使用することを示します。これはインデックスのマージの最適化方法であり、最終的に積集合または和集合を取得します。一般的な and または or 条件では異なるインデックスが使用されます。
8. unique_subquery: where 内のフォーム内サブクエリに使用され、サブクエリは重複のない一意の値を返します。
9. index_subquery: 補助インデックスを使用するフォーム内サブクエリまたは定数リストで使用されます。サブクエリは重複した値を返す場合があり、インデックスを使用してサブクエリの重複を排除できます。
10. range: インデックス範囲スキャン。>、<、between、in、like などの演算子を使用するクエリでよく使用されます。
11. インデックス: インデックスの完全なテーブルスキャン。インデックス ツリーを最初から最後までスキャンします。
12. all: テーブル全体を走査して一致する行を検索します (Index と ALL はどちらもテーブル全体を読み取りますが、index はインデックスから読み取り、ALL はハードディスクから読み取ります)
13. NULL: MySQL は最適化中にステートメントを分解し、テーブルやインデックスにアクセスすることなくステートメントを実行します。

可能なキー

クエリで使用される可能性のあるインデックスを表示します。

鍵

テーブルへのアクセスを最適化するためにクエリが実際に使用したインデックスを表示します。

select_type が index_merge の場合、ここに 2 つ以上のインデックスが表示される場合があります。その他の select_type の場合、ここには 1 つのインデックスのみが表示されます。

キーの長さ

• クエリの処理に使用されるインデックスの長さ。インデックスで使用されるバイト数を示します。この値を使用して、複数列インデックスのどの部分が実際に使用されているかを判断することができます。
• 注意: key_len によって表示される値は、インデックス フィールドの最大可能長であり、実際に使用される長さではありません。つまり、key_len はテーブル定義に基づいて計算され、テーブルから取得されるものではありません。また、key_len は where 条件で使用されるインデックスの長さのみを計算し、インデックスがソートやグループ化に使用されている場合でも、key_len では計算されません。

参照

キーで使用されるフィールドまたは定数を表示します。

• 定数等値クエリを使用している場合は、ここに const が表示されます。
• 結合クエリの場合、駆動テーブルの実行プランには、駆動テーブルの関連フィールドが表示されます。
• 条件で式または関数が使用されている場合、または条件列で内部の暗黙的な変換が発生した場合は、func として表示される場合があります。

行

MySQL がテーブル統計とインデックス選択に基づいて、必要なターゲット レコードを見つけるために読み取る必要がある行数を大まかに見積もることを示します。これは正確な値ではありません。

余分な

別の列に収まらない追加の重要な情報。

この列には多くの情報を表示できます。数十種類ありますが、よく使用されるものは次のとおりです。

フィルター

• この列は、explain Extended を使用する場合に表示されます。バージョン 5.7 以降ではこのフィールドがデフォルトで存在するため、explain Extended を使用する必要はありません。
• このフィールドは、ストレージ エンジンによって返されたデータがサーバー レベルでフィルター処理された後に、クエリを満たすレコード数の比率を示します。これはパーセンテージであり、特定のレコード数ではないことに注意してください。

MySQL 実行プランの制限

1. EXPLAIN では、トリガー、ストアド プロシージャ、またはクエリに対するユーザー定義関数の影響については通知されません。
2. EXPLAIN はさまざまなキャッシュを考慮しません。
3. EXPLAIN は、クエリの実行時に MySQL によって行われた最適化作業を表示しません。
4. 一部の統計は推定値であり、正確な値ではありません。
5. EXPALIN は SELECT 操作のみを説明できます。他の操作は SELECT として書き換えてから表示する必要があります。

クエリプランニングのケーススタディ

実行命令

1. (id = 4): [select id, name from t2]: select_type は union です。つまり、id=4 の選択は union 内の 2 番目の選択です。
2. (id = 3): [select id, name from t1 where address = '11']: これは from ステートメントに含まれるサブクエリであるため、DERIVED としてマークされます。address = '11' は複合インデックス idx_name_email_address を通じて取得できるため、type は index です。
3. (id = 2): [select id from t3]: 選択に含まれるサブクエリなので、SUBQUERY とマークされます。
4. (id = 1): [select d1.name, … d2 from … d1]: select_type は PRIMARY であり、クエリが最も外側のクエリであることを示します。また、テーブル列は "derived3" としてマークされており、クエリ結果が派生テーブル (id = 3 の選択結果) からのものであることを示します。
5. (id = NULL): [ … union … ]: は、union の一時テーブルから行を読み取る段階を表します。テーブル列の「union 1, 4」は、id=1 と id=4 の選択結果を使用した union 操作を示します。

要約する

これで、MySQL Explain 実行プランの詳細な調査に関するこの記事は終了です。MySQL Explain 実行プランの関連コンテンツの詳細については、123WORDPRESS.COM で以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。