MySQL EXPLAIN ステートメントの使用例

MySQL の最適化に関しては、最適化の方法について話す前に、まず現在の SQL ステートメントが実際のデータベースでどのように実行されるかを知る必要があります。 MySQL には、ステートメントの実行をシミュレートするための非常に便利なキーワード EXPLAIN が用意されています。 EXPLAIN を使用すると、SQL ステートメントの実行効果を表示できます。これにより、より適切なインデックスを選択してクエリ ステートメントを最適化し、より最適化されたステートメントを記述できるようになります。そこで今日は、このキーワードの基本的な使用法と応用についてお話します。

1. 使用方法

EXPLAIN の使い方は非常に簡単です。

mysql> EXPLAIN SELECT * FROM user;

簡単に言うと、元の SQL ステートメントの前に EXPLAIN キーワードを追加するか、チェックする SQL ステートメントの後に EXPLAIN キーワードを追加します。

2. 出力結果

EXPLAIN ステートメントの出力は、必要なデータであり、分析の焦点となります。
まず、上記のステートメントによって示される対応する結果の形式を見てみましょう。

+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| 1 | シンプル | ユーザー | NULL | すべて | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+

EXPLAIN ステートメントでは合計 10 列のデータが返されます。次に、パフォーマンスの最適化においてより重要ないくつかのデータ列の意味を見てみましょう。

1.id

これは選択クエリのシーケンス番号です。

2.選択タイプ

SQL ステートメントが非選択ステートメント (つまり、削除、更新など) の場合、このフィールドの値は対応する操作タイプ (削除、更新など) になります。

mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');

この時点での出力 select_type は、対応する INSERT です。

+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| 1 | INSERT | ユーザー | NULL | すべて | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+

SQL ステートメントが選択ステートメントの場合、次のようないくつかの詳細な選択タイプに対応します。

SIMPLE: 単純な SELECT (UNION やサブクエリなどは使用しません)
PRIMARY: 最も外側のSELECT
UNION: UNION 内の 2 番目以降の SELECT ステートメント。DEPENDENT UNION: UNION 内の 2 番目以降の SELECT ステートメントは、外部クエリに依存します。UNION RESULT: UNION の結果。
SUBQUERY: サブクエリの最初のSELECT
依存サブクエリ: 外部クエリに依存するサブクエリ内の最初の SELECT DERIVED: 派生テーブルの SELECT (FROM 句内のサブクエリ)

以下は、可能な限り最も単純な SIMPLE クエリの例です。

mysql> EXPLAIN SELECT * FROM user;

+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
| 1 | シンプル | ユーザー | NULL | すべて | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+

3.表

この操作でアクセスされたデータがどのテーブルに関するものかを表示します。

4.パーティション

テーブルで使用されるパーティションを表示します。10 年間の会社の注文量をカウントする場合は、データを各年ごとに 1 つずつ、10 個のパーティションに分割できます。これにより、クエリの効率が大幅に向上します。

5.タイプ

これは最も重要な列です。接続が使用するクラスと、インデックスが使用されるかどうかを表示します。クエリのパフォーマンスを分析するための鍵となります。
結果は最良から最悪まで次の通りです。

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

これらの状況の意味は次のとおりです。

• system、const: クエリ変数を定数に変換できます。たとえば、id=1; id は主キーまたは一意のキーです。
• eq_ref: インデックスにアクセスし、単一行のデータを返します。(通常は結合時に表示され、クエリで使用されるインデックスは主キーまたは一意キーです)
• ref: インデックスにアクセスし、特定の値のデータを返します。(複数行を返すことができます) 通常、= を使用する場合に発生します。
• 範囲: この結合タイプは、インデックスを使用して行の範囲を返します。たとえば、何かを検索するために > または < を使用し、フィールドにインデックスがある場合に発生します (注: 必ずしもインデックスよりも優れているわけではありません)。
• インデックス: インデックスの順序でテーブル全体をスキャンします。利点はソートする必要がないことですが、欠点はテーブル全体をスキャンする必要があることです。
• ALL: テーブル全体のスキャンは可能な限り避けるべきです_

一般的に、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。そうしないと、パフォーマンスの問題が発生する可能性があります。

6.可能なキー

クエリ ステートメントで使用される可能性のあるインデックス列を表示します。値は 1 つ、複数、または null のいずれかになります。

7.キー

キー列には、クエリ ステートメントで実際に使用されるインデックス列が表示されます。 null の場合、インデックスは使用されません。
possible_key と key の実際の効果を表示します。
以下は、age 列にインデックスが設定されたデータ テーブルです。次のクエリを実行します。

mysql> 説明 select * from user where age = 1;

以下の結果が得られます。

+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+------+------+------+------+
| 1 | SIMPLE | ユーザー | NULL | ref | 年齢 | 年齢 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+--------+-----------+--------+---------------+-------+-------+-------+------+------+------+------+

8.キーの長さ

現在のクエリ ステートメントで使用されるインデックスの長さを表示します。長さが短いほど、精度を失わずに良くなります。

9.参照

参照されている前のテーブルの列。

10行

MySQL はテーブルとクエリに基づいて、最終結果を返すために調べる必要がある行数を推定します。この列の値が大きいほど、クエリの効率は悪くなります。

11.フィルタリング

行列の値と一緒に使用されるパーセンテージ値は、クエリ実行プラン (QEP) 内の前のテーブルの結果セットを推定し、結合操作の反復回数を決定できます。小さなテーブルが大きなテーブルを駆動し、結合の数を減らします。

12.追加

MySQL がクエリを解析する方法については、いくつかの種類の追加情報があります。

Extraに含まれる値は次のとおりです。

• インデックスの使用: インデックスのみを使用するため、テーブルへのアクセスを回避でき、パフォーマンスが高くなります。
• where の使用: where を使用してデータをフィルタリングします。すべての where 句を where を使用して表示する必要はありません。たとえば、= の方法でインデックスにアクセスします。
• 一時テーブルの使用: 現在のクエリを処理するために一時テーブルを使用します。
• filesort の使用: 追加のソートを使用します。この時点で、MySQL は結合タイプに従ってすべての適格なレコードを参照し、ソート キーと行ポインターを保存してから、キーをソートして行を順番に取得します。 (order by v1 が使用され、インデックスが使用されない場合は、追加のソートが実行されます。)
• 各レコードの範囲がチェックされました (インデックス マップ:N): 適切なインデックスは使用できません。
• group-by にインデックスを使用する: __ は、実際のテーブルをクエリしなくても、グループ化に必要なすべてのデータがインデックス内で見つかることを示します。 t_order から user_id を選択し、user_id でグループ化する方法について説明します。

上記はMySQL EXPLAIN文の使用例の詳しい内容です。MySQL EXPLAIN文の詳細については、123WORDPRESS.COMの他の関連記事もご覧ください。