SQL文のパフォーマンスを分析するための標準的な要約

この記事では、explain を使用して SQL ステートメントを分析する方法を紹介します。

実際、インターネットには、explain in details の使い方を紹介する記事がたくさんあります。この記事では、例と原則を組み合わせて、理解を深めていただけるようにしています。この記事を注意深く読めば、きっと何か特別なものが得られるはずです。

explain は説明を意味し、MySQL では実行計画と呼ばれます。つまり、このコマンドを使用すると、オプティマイザが SQL を分析した後に MySQL が SQL の実行をどのように決定するかを確認できます。

オプティマイザといえば、MySQL には強力なオプティマイザが組み込まれていることを付け加えておきます。オプティマイザの主なタスクは、記述した SQL を最適化し、スキャンする行数を減らしたり、ソートを回避したりするなど、可能な限り低コストで実行することです。 SQL ステートメントを実行すると何が起こりますか? 前回の記事でオプティマイザーを紹介しました。

一般的に、Explain はいつ使用する必要があるのでしょうか。ほとんどの場合、MySQL のスロークエリログからクエリ効率の低い SQL ステートメントを分析するために explain を使用します。インデックスの追加など、MySQL を最適化するときに、追加したインデックスがヒットするかどうかを分析するために explain を使用する場合もあります。ビジネスを開発するときに、ニーズを満たすために、より効率的な SQL ステートメントを選択するために explain を使用する必要がある場合もあります。

では、explain はどのように使用するのでしょうか? とても簡単です。以下に示すように、sql の前に explain を追加するだけです。

mysql> t から * を選択します。

+----+-------------+-------+-------+---------------+-------+-------+-------+-------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+---------------+-------+-------+-------+-------+

| 1 | シンプル | t | すべて | NULL | NULL | NULL | NULL | 100332 | NULL |

+----+-------------+-------+-------+---------------+-------+-------+-------+-------+

セット内の1行（0.04秒）

ご覧のとおり、explain は約 10 個のフィールドを返します。異なるバージョンによって返されるフィールドはわずかに異なります。各フィールドは特定の意味を表します。この記事では、各フィールドを詳しく説明するつもりはありません。多すぎるため、覚えるのは簡単ではないと思います。まずはいくつかの重要なフィールドを理解したほうがよいでしょう。

その中でも、type、key、rows、Extra の各フィールドがより重要だと思います。これらのフィールドの意味をよりよく理解できるように、具体的な例を使ってみましょう。

まず最初に、これらのフィールドの文字通りの意味を簡単に紹介する必要があります。

タイプは、MySQL がデータにアクセスする方法を示します。一般的なものには、フルテーブルスキャン (all)、トラバーサルインデックス (index)、間隔クエリ (range)、定数または等しいクエリ (ref、eq_ref)、主キー等しいクエリ (const)、テーブルにレコードが 1 つしかない場合 (system) などがあります。以下は、効率の最高から最低までのランキングです。

システム > const > eq_ref > ref > 範囲 > インデックス > すべて

key は、クエリプロセスで実際に使用されるインデックス名を示します。

行は、クエリ処理中にスキャンする必要がある行数を示します。このデータは正確ではない可能性があり、MySQL のサンプリング統計です。

Extra は追加情報を示し、通常はインデックスが使用されているかどうか、ソートが必要かどうか、一時テーブルが使用されているかどうかなどを示します。

さて、ケース分析を始めましょう。

前回の記事で作成したストレージエンジンを使用してテストテーブルを作成しましょう。テーブルに 100,000 個のテストデータ項目を挿入します。テーブル構造は次のとおりです。

テーブル `t` を作成します (

 `id` int(11) NULLではない、

 `a` int(11) デフォルト NULL,

 `b` int(11) デフォルト NULL,

 主キー (`id`)

)ENGINE=InnoDB;

次に、次のクエリステートメントを確認します。このテーブルには現在主キーインデックスが 1 つしかなく、通常のインデックスは作成されていないことに注意してください。

mysql> テーブル t を変更し、インデックス a_index(a) を追加します。

クエリは正常、影響を受けた行は 0 行 (0.19 秒)

レコード: 0 重複: 0 警告: 0

 

mysql> テーブル t を変更し、インデックス b_index(b) を追加します。

クエリは正常、影響を受けた行は 0 行 (0.20 秒)

レコード: 0 重複: 0 警告: 0

 

mysql> t からインデックスを表示します。

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

| t | 0 | プライマリ | 1 | id | A | 100332 | NULL | NULL | | BTREE | | |

| t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | はい | BTREE | | |

| t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | はい | BTREE | | |

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

セット内の 3 行 (0.00 秒)

タイプ値は ALL です。これは、テーブル全体がスキャンされることを意味します。行フィールドには 100,332 件のレコードが表示されていることに注意してください。実際には、合計で 100,000 件のレコードしかないため、このフィールドは MySQL による推定値にすぎず、正確ではない可能性があります。この完全なテーブルスキャンは非常に非効率なので、最適化する必要があります。

次に、フィールド a と b にそれぞれ通常のインデックスを追加し、インデックスを追加した後の SQL ステートメントを確認します。

mysql> テーブル t を変更し、インデックス a_index(a) を追加します。

クエリは正常、影響を受けた行は 0 行 (0.19 秒)

レコード: 0 重複: 0 警告: 0

 

mysql> テーブル t を変更し、インデックス b_index(b) を追加します。

クエリは正常、影響を受けた行は 0 行 (0.20 秒)

レコード: 0 重複: 0 警告: 0

 

mysql> t からインデックスを表示します。

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

| t | 0 | プライマリ | 1 | id | A | 100332 | NULL | NULL | | BTREE | | |

| t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | はい | BTREE | | |

| t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | はい | BTREE | | |

+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

セット内の 3 行 (0.00 秒)

mysql> explain select * from t where a > 1000;

+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+

| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | where の使用 |

+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+

セット内の 1 行 (0.00 秒)

上記の SQL は少しわかりにくいように見えますか? Type は実際にはフィールド a にインデックスが追加されたばかりであることを示しており、possible_keys も a_index が使用可能であることを示していますが、key は null を示しており、MySQL が実際には a インデックスを使用しないことを示しています。なぜでしょうか?

これは、select *を使用する場合、フィールドbを検索するために主キーインデックスに戻る必要があるためです。このプロセスはテーブルリターンと呼ばれます。このステートメントは、条件を満たす90,000個のデータをフィルタリングします。つまり、これらの90,000個のデータをテーブルに返す必要があり、完全なテーブルスキャンには100,000個のデータしかありません。したがって、MySQLオプティマイザの観点からは、テーブル全体を直接スキャンする方が、少なくともテーブルリターンプロセスを回避するのに適しています。

もちろん、テーブルを返す操作があればインデックスにヒットしないというわけではありません。インデックスを使うための鍵は、MySQL がどのクエリコストが低いと判断するかにあります。上記の SQL の where 条件を少し変更してみましょう。

mysql> explain select * from t where a > 99000;

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------+

| 1 | SIMPLE | t | 範囲 | a_index | a_index | 5 | NULL | 999 | インデックス条件を使用 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------+

セット内の 1 行 (0.00 秒)

今回は型値が range で、キーが a_index なので、インデックス a がヒットします。この SQL 条件を満たすレコードは 1,000 件しかないため、これは適切な選択です。MySQL は、1,000 件のレコードがテーブルに返されても、テーブル全体をスキャンするよりもコストが低いと考えています。つまり、MySQL は実はとても賢いのです。

また、Extra フィールドの値が Using index condition になっていることがわかります。これは、インデックスが使用されているが、テーブルを返す必要があることを意味します。次のステートメントを見てみましょう。

mysql> explain select a from t where a > 99000;

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------+

| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | where の使用; index の使用 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------+

セット内の 1 行 (0.00 秒)

このExtraの値は、Using where; Using indexです。これは、クエリがインデックスを使用し、クエリ対象のフィールドをテーブルに戻さずにインデックスで取得できることを意味します。明らかに、この効率は上記よりも高いので、安易にselect *を記述しないでください。ビジネスに必要なフィールドのみをクエリし、テーブルに戻ることをできるだけ避けます。

整理する必要がある別のものを見てみましょう。

mysql> explain select a from t where a > 99000 order by b;

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------------+

| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | インデックス条件の使用; filesort の使用 |

+----+-------------+-------+-------+---------------+----------+-------+------+---------------------------------------+

セット内の 1 行 (0.00 秒)

この Extra は Using filesort を返します。つまり、ソートが必要です。これを最適化する必要があります。つまり、MySQL はデータを見つけた後、メモリ内でソートする必要があります。インデックス自体が順序付けられていることを知っておく必要がありますので、一般的に言えば、次のように記述するなど、インデックスの順序性をできるだけ利用するようにする必要があります。

mysql> explain select a from t where a > 99990 order by a;

+----+-------------+-------+-------+------------------+---------+-------+------+--------------------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+------------------+---------+-------+------+--------------------------+

| 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | where の使用; index の使用 |

+----+-------------+-------+-------+------------------+---------+-------+------+--------------------------+

セット内の 1 行 (0.00 秒)

別の複合インデックスを作成して確認してみましょう。

mysql> テーブル t を変更し、インデックス ab_index(a,b) を追加します。

クエリは正常、影響を受けた行は 0 行 (0.19 秒)

レコード: 0 重複: 0 警告: 0

mysql> explain select * from t where a > 1000;

+----+-------------+-------+-------+------------------+----------+-------+------+--------------------------+

| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |

+----+-------------+-------+-------+------------------+----------+-------+------+--------------------------+

| 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | where を使用; index を使用 |

+----+-------------+-------+-------+------------------+----------+-------+------+--------------------------+

セット内の 1 行 (0.00 秒)

この SQL は上で説明しました。複合インデックスが作成されていない場合は、完全なテーブルスキャンが実行されます。ここで、カバーインデックスが使用され、テーブルを返すプロセスも回避されます。つまり、クエリ対象のフィールドは (ab_index) インデックスで見つかります。

この記事では、いくつかの例を通して、explain を使用して SQL ステートメントの実行プランを分析する方法を紹介します。また、一般的なインデックスの最適化についてもいくつか説明します。実際には、他にも可能性があります。SQL ステートメントを自分で記述し、explain を使用して分析し、最適化できるものを確認することもできます。

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