MYSQL パフォーマンス アナライザー EXPLAIN 使用例分析

この記事では、例を使用して MYSQL パフォーマンス アナライザー EXPLAIN の使用方法を説明します。ご参考までに、詳細は以下の通りです。

方向：

EXPLAIN SELECT * FROM ユーザー;

環境とデータの準備

-- MySQL のバージョンを確認します SELECT VERSION();
 
-- MySQL はどのようなストレージ エンジンを提供していますか? SHOW ENGINES;
 
-- デフォルトのストレージ エンジンを表示します。SHOW VARIABLES LIKE '%storage_engine%';

出力：

id: 出力は、SQL 全体の実行順序を識別するために使用される整数です。 id が同じ場合、異なる id を持つ行は上から下に実行されます。id 値が大きいほど実行優先度が高くなり、行が最初に実行されます。行が他の行の結合結果を参照する場合、値は NULL になることがあります。

select_type:[クエリタイプ]

SIMPLE: 単純な SELECT クエリ、UNION またはサブクエリなし、単一テーブル クエリまたは複数テーブル JOIN クエリを含む

PRIMARY: 最も外側の選択クエリ。サブクエリや UNION クエリでよく使用されます。最も外側のクエリは PRIMARY としてマークされます。

UNION: UNION 操作の 2 番目以降の SELECT は、外部クエリの結果セットに依存しません (外部クエリは PRIMARY に対応する SELECT を参照します)

DEPENDENT UNION: UNION 操作の 2 番目以降の SELECT は、外部クエリの結果セットに依存します。

UNION RESULT: UNION の結果 (UNION ALL の場合は結果なし)

SUBQUERY: サブクエリ内の最初のSELECTクエリ。外部クエリの結果セットに依存しません。

依存サブクエリ: サブクエリ内の最初の選択クエリは、外部クエリの結果に依存します。

DERIVED: 派生テーブル (一時テーブル)。FROM 句にサブクエリがある場合によく使用されます。

注: MySQL 5.7 には派生テーブルの新機能があり、条件を満たす派生テーブル内の子テーブルを親クエリテーブルに直接結合できるため、実行プランが簡素化され、実行効率が向上します。この機能は MySQL 5.7 でデフォルトで有効になっているため、デフォルトでは上記の SQL の実行プランは次のようになります。

MATERIALIZED: マテリアライズド サブクエリは、MySQL 5.6 で導入された新しい select_type で、主に FROM 句または IN 句のサブクエリを最適化するために使用されます。詳細については、「マテリアライズドによるサブクエリの最適化」を参照してください。

キャッシュ不可能なサブクエリ: 外部メイン テーブルの場合、サブクエリをキャッシュできないため、毎回計算する必要があります。

UNCACHEABLE UNION: UNCACHEABLE SUBQUERY に似ていますが、UNION 操作で使用されます。

SIMPLLE、PRIMARY、SUBQUERY、DERIVED 実際の業務ではこの 4 つによく遭遇します。この 4 つを理解しておいてください。他のものについては、遭遇したときに調べてください。

テーブル: 対応する行がどのテーブルにアクセスしているかを示します (エイリアスがある場合は表示されます)。また、<union2,3>、<subquery2>、<derived2> などの類似した値があります (2,3、2、2 は id 列の値を参照します)

パーティション: クエリが一致するパーティション。パーティション化されていないテーブルの場合、この値は NULL になります。ほとんどの場合、パーティションは使用されないので、この列に注意を払う必要はありません。

タイプ：

結合タイプまたはアクセスタイプは、MySQL がテーブル内の条件を満たす行をどのように見つけるかを指定します。これは、クエリが効率的かどうかを判断するための重要な基準です。完全な概要については、explain-join-types を参照してください。

システム: このテーブルには1行のみ(=システムテーブル)があり、これはconst型の特殊なケースです。

const: 一致する行が 1 つしかないと判断された場合、MySQL オプティマイザはクエリの前にそれを読み取り、1 回だけ読み取るため、非常に高速になります。主キーまたは一意のインデックス内の定数値の比較に使用されます

eq_ref: 前のテーブルの各行に対して、このテーブルから最大 1 つの条件に該当するレコードが返されます。これは、接続で使用されるインデックスが PRIMARY KEY または UNIQUE NOT NULL インデックスである場合に非常に効率的です。

ref: インデックス アクセス (インデックス ルックアップとも呼ばれます) は、単一の値に一致するすべての行を返します。このタイプは、通常、複数テーブルのJOINクエリ、非UNIQUEまたは非PRIMARY KEY、または左端のプレフィックスルールインデックスを使用するクエリで使用されます。つまり、JOINがキーワードに基づいて1行を選択できない場合は、refを使用します。

fulltext: これは、フルテキスト インデックスが使用される場合に使用されます。このタイプのインデックスは通常は使用されず、専用の検索サービス (solr、elasticsearch など) に置き換えられます。

ref_or_null: refに似ていますが、NULLを具体的に検索できる行を追加します。

これは、武器列にインデックスがあり、武器列に NULL があるという前提条件に従います。

index_merge: このアクセスタイプはインデックスマージ最適化メソッドを使用します

これも条件付きです。id 列と weapon 列の両方に単一列のインデックスがあります。 index_merge が発生し、このタイプの SQL が後で頻繁に使用される場合は、単一列インデックスをより効率的な複合インデックスに置き換えることを検討できます。

unique_subquery: 2 つのテーブル結合における駆動テーブルの eq_ref アクセス メソッドと同様に、unique_subquery は IN サブクエリを含む一部のクエリ ステートメントで使用されます。クエリ オプティマイザーが IN サブクエリを EXISTS サブクエリに変換することを決定し、サブクエリが等価値の一致に主キーまたは一意のインデックスを使用できる場合は、unique_subquery が使用されます。

index_subquery: index_subquery は unique_subquery と似ていますが、サブクエリ内のテーブルにアクセスするために通常のインデックスが使用される点が異なります。

range: インデックスを使用して、指定された範囲内の行を取得します。=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、または IN 演算子を使用し、キーワード列を定数と比較する場合は、range が使用されます。前提として、インデックスに基づいている必要があります。つまり、id にインデックスが必要です。

インデックス: インデックス カバレッジを使用できるが、すべてのインデックス レコードをスキャンする必要がある場合は、インデックスを使用します。これは、統計を行うときに非常に一般的です。

ALL: おなじみのフルテーブルスキャン

possible_keys: この SQL で使用できるインデックスを示しますが、クエリ中に必ずしも使用されるわけではありません。空の場合は、使用できるインデックスがないことを意味します。この場合、WHERE ステートメントをチェックして、特定の列を参照できるか確認するか、新しいインデックスを作成してパフォーマンスを向上させることができます。

key: この SQL で実際に使用されているインデックスを表示します。インデックスが選択されていない場合、この列は null になります。MySQL で possible_keys 列のインデックスを使用または無視するように強制するには、クエリで FORCE INDEX、USE INDEX、または I GNORE INDEX を使用します。

key_len: MySQL が使用することを決定したキーの長さ (バイト単位) を表示します。キーが NULL の場合、長さは NULL になります。長さが短いほど、精度を失わずに良くなります。

ref: 定数や列など、インデックス列が何と同等であるかを示します。列の名前 (または定数) が表示されます。多くの場合、これは Null です。

行数: この SQL を実行するときに MySQL パーサーがスキャンされると予測する行数を示します。この値は推定値であり、特定の値ではなく、通常は実際の値よりも小さくなります。

フィルター: 返される行数と読み取る必要のある行数 (行の値) の比率を表示します。もちろん、小さいほど良いです。

余分な：

他の列にはないが重要な追加情報を示します。可能な値は多数あります。一般的な値をいくつか見てみましょう。

インデックスの使用: SQL がテーブルに戻ってデータをクエリするのではなく、カバーリング インデックスを使用することを示します。これにより、パフォーマンスが非常に向上します。

where: を使用すると、ストレージ エンジンはレコードの検索後に事後フィルタリングを実行することを示します。クエリがインデックスを使用できない場合、where: を使用すると、MySQL が where 条件を使用して結果セットをフィルタリングする必要があることが通知されるだけです。

一時テーブルの使用: MySQL は結果セットを保存するために一時テーブルを使用する必要があることを意味します。これはクエリの並べ替えやグループ化でよく使用されます。

ファイルソートの使用: MySQL がインデックスを使用して直接ソートできない (ソートされたフィールドがインデックス フィールドではない) こと、およびソートにバッファ スペース (メモリまたはディスク) が使用されることを示します。通常、この値は SQL を最適化する必要があることを示し、CPU を大量に消費します。

不可能な場合: この追加情報は、クエリステートメントの WHERE 句が常に FALSE の場合に表示されます。

もちろん、一般的ではないものもあります。遭遇した際にはぜひチェックしてください!!!

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL トランザクション操作スキル」、「MySQL ストアド プロシージャ スキル」、「MySQL データベース ロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。