MySql で SQL 実行プランをクエリするために explain を使用する方法

explain コマンドは、クエリオプティマイザーがクエリの実行を決定した方法を確認する主な方法です。

この機能には制限があり、必ずしも真実を伝えるわけではありませんが、その出力は入手可能な最良の情報であり、クエリがどのように実行されるかを知ることができるため、時間をかけて理解する価値があります。

1. MySQL実行計画とは何か

実行プランをより深く理解するには、MySQL の基本構造とクエリの基本原則について基本的な理解が必要です。

MySQL 自体の機能アーキテクチャは、アプリケーション層、ロジック層、物理層の 3 つの部分に分かれています。MySQL だけでなく、他のほとんどのデータベース製品もこのアーキテクチャに従って分割されています。

アプリケーション層は主に、クライアントとのやり取り、リンクの確立、リンクステータスの記憶、データの返却、リクエストへの応答を担当します。この層はクライアントを処理します。

ロジック層は、クエリを例にとると、主にクエリ処理、トランザクション管理、およびその他のデータベース機能処理を担当します。

クエリ SQL を受信すると、データベースはすぐにスレッドを割り当てて処理します。最初のステップでは、クエリプロセッサが SQL クエリを最適化します。最適化後、実行プランが生成され、プラン実行者に渡されて実行されます。

プラン実行者は、データの操作のために下位のトランザクションマネージャとストレージマネージャにアクセスする必要があります。それぞれの分担は異なりますが、最終的には物理層のファイルを呼び出してクエリ構造情報を取得し、最終結果をアプリケーション層に応答します。

物理層は、実際の物理ディスクに保存されているファイル、主にデータファイルとログファイルを指します。

上記の説明から、実行プランの生成は、SQL ステートメントを実行する上で不可欠なステップであることがわかります。実行プランを表示することで、SQL ステートメントのパフォーマンスを直感的に確認できます。実行プランにはさまざまなクエリタイプとレベルが用意されており、パフォーマンス分析の基礎として表示して使用できます。

2. 実行計画を分析する方法

MySQL には、SQL ステートメントの実行プランを直感的に表示するための explain キーワードが用意されています。

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

次に、explain を使用して次のようにクエリを作成します。

mysql> explain select * from payment;
+----+-------------+----------+-----------+--------+---------------+-----+--------+--------+--------+--------+--------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+-----+--------+--------+--------+--------+--------+
| 1 | SIMPLE | 支払い | NULL | すべて | NULL | NULL | NULL | NULL | 16086 | 100.00 | NULL |
+----+-------------+----------+-----------+--------+---------------+-----+--------+--------+--------+--------+--------+
セットに 1 行、警告 1 回 (0.01 秒)

クエリ構造には 12 個の列があります。各列の意味を理解することは、実行プランを理解する上で重要です。以下は表形式での説明です。

列名	例示する
id	SELECT 識別子。これは SELECT のクエリシーケンス番号です。
選択タイプ	SELECT タイプ。次のいずれかになります。 SIMPLE: 単純な SELECT (UNION またはサブクエリなし) PRIMARY: 最も外側のSELECT UNION: UNION内の2番目以降のSELECT文依存UNION: UNION内の2番目以降のSELECT文は外側のクエリに依存します。 UNION RESULT: UNIONの結果 SUBQUERY: サブクエリの最初のSELECT 依存サブクエリ: 外部クエリに依存するサブクエリの最初のSELECT DERIVED: エクスポートされたテーブルのSELECT (FROM句のサブクエリ)
テーブル	出力行が参照するテーブル
パーティション	クエリがパーティション化されたテーブルに対するものである場合、クエリがアクセスするパーティションが表示されます。
タイプ	結合のタイプ。以下に、さまざまな結合タイプを最良から最悪の順にランク付けして示します。 system: テーブルには 1 つの行 (= システムテーブル) のみが含まれます。これは const join 型の特殊なケースです。 const: テーブルには一致する行が最大 1 つあり、クエリの開始時に読み取られます。行が 1 つしかないため、この行の列値は、オプティマイザーの残りの部分では定数とみなすことができます。 const テーブルは一度だけ読み取られるため高速です。 eq_ref: 前のテーブルの行の組み合わせごとに、このテーブルから行を読み取ります。これはおそらく、const 型を除いて最適な結合型です。 ref: 前のテーブルの行の組み合わせごとに、一致するインデックス値を持つすべての行がこのテーブルから読み取られます。 ref_or_null: この結合タイプは ref に似ていますが、MySQL が NULL 値を含む行を具体的に検索できる点が追加されています。 index_merge: この結合タイプは、インデックスマージ最適化方法が使用されることを示します。 unique_subquery: このタイプは、次の形式の IN サブクエリの ref を置き換えます: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery は、サブクエリを完全に置き換えることができ、より効率的なインデックス検索関数です。 index_subquery: この結合タイプは unique_subquery に似ています。 IN サブクエリを置き換えることができますが、次の形式のサブクエリ内の非一意のインデックスに対してのみ可能です: value IN (SELECT key_column FROM single_table WHERE some_expr) range: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。 index: この結合タイプは、インデックスツリーのみがスキャンされる点を除いて、ALL と同じです。通常、インデックスファイルはデータファイルよりも小さいため、これは ALL よりも高速です。 ALL: 前のテーブルの行の組み合わせごとに完全なテーブルスキャンが実行され、クエリを最適化する必要があることを示します。一般的に言えば、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。
可能なキー	MySQLがこのテーブル内の行を見つけるために使用できるインデックスを示します
鍵	MySQL が実際に使用することを決定したキー (インデックス) を表示します。インデックスが選択されていない場合、キーは NULL になります。
キーの長さ	MySQL が使用することを決定したキーの長さを表示します。キーが NULL の場合、長さは NULL になります。長さが短いほど、精度を失わずに良くなります。
参照	テーブルから行を選択するためにキーと一緒に使用される列または定数を示します。
行	クエリを実行するために MySQL が調べる必要があると考える行数を表示します。複数の行にわたってデータを乗算すると、処理する行数を推定できます。
フィルター	条件によってフィルタリングされた行の割合の推定値を表示します。
余分な	この列には、MySQL がクエリを解決した方法に関する詳細情報が含まれています。 Distinct: MySQL は最初に一致する行を見つけた後、現在の行の組み合わせに対するそれ以上の行の検索を停止します。最適化されたテーブルを選択すると、MySQL はテーブルまたはインデックスをまったく走査せずにデータを返します。これは、それ以上最適化できないところまで最適化されていることを示します。存在しない: MySQL はクエリに対して LEFT JOIN 最適化を実行できます。LEFT JOIN 基準に一致する行が見つかると、テーブル内の前の行の組み合わせの行はそれ以上チェックされなくなります。各レコードの範囲がチェックされました (インデックスマップ: #): MySQL は適切なインデックスを見つけられませんでしたが、前のテーブルの列値がわかっている場合は、部分インデックスを使用できる可能性があることが分かりました。 filesort の使用: MySQL では、行をソート順に取得する方法を判断するために追加のパスが必要なため、クエリを最適化する必要があります。インデックスの使用: 実際の行をさらに検索して読み取ることなく、インデックスツリーの情報のみを使用してテーブルから列情報を取得します。一時テーブルの使用: クエリを解決するために、MySQL は結果を保持するための一時テーブルを作成する必要があります。つまり、クエリを最適化する必要があります。 where の使用: WHERE 句は、次のテーブルに一致する行やクライアントに送信される行を制限するために使用されます。 sort_union(...) の使用、union(...) の使用、intersect(...) の使用: これらの関数は、index_merge 結合タイプのインデックススキャンをマージする方法を示しています。グループ化にインデックスを使用する: テーブルにアクセスするためにインデックスを使用する方法と同様に、グループ化にインデックスを使用するということは、実際のテーブルにアクセスするためにハードディスクを検索する必要なく、GROUP BY または DISTINCT クエリのすべての列をクエリするために使用できるインデックスが MySQL によって検出されたことを意味します。

要約する

上記は、MySql で explain を使用して SQL の実行プランを照会する方法について紹介したものです。お役に立てば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。

以下もご興味があるかもしれません: