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

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 を使用して分析し、最適化できるものを確認することもできます。

以下もご興味があるかもしれません:
  • MySQL Limitクエリのパフォーマンスを向上させる方法
  • MySQL スロークエリを通じて MySQL のパフォーマンスを最適化する方法
  • show processlist コマンドによる MySQL パフォーマンス検査の説明
  • MySQL Limitパフォーマンス最適化とページングデータパフォーマンス最適化の詳細な説明
  • MySQL の制限パフォーマンス分析と最適化

<<:  NginxはURLのパスに応じてアップストリームに動的に転送します

>>:  WeChatアプレットが計算機機能を実装

推薦する

Docker で MySQL 接続と設定ファイルの最大数を変更する

1. MySQLイメージを見つける ドッカーps 2. ミラーmysqlイメージを入力する dock...

デザイナーはコーディングを学ぶ必要がありますか?

多くの場合、 Web デザインが完成した後でデザイナーの無知が露呈し、批判されることがあります。彼ら...

Docker 起動時の ES メモリ オーバーフローの解決方法

jvm.options ファイルを elasticsearch 構成に追加し、スタック サイズを変更...

MySQL 8.0.13 で日付を 0000-00-00 00:00:00 に設定すると発生する問題を解決する

データベース操作を学び始めたばかりです。今日、データを保存していたところ、エラーが発生していることに...

Antd+vueは円形属性フォームの動的検証のアイデアを実現します

必要な項目をループして検証するために、クエリ フォームのいくつかのプロパティを実装したいと考えていま...

Vueのシンプルな状態管理ストアモードを理解する方法

目次概要1. store.jsを定義する2. store.js を使用するコンポーネント3. 成果を...

vuexサードパーティパッケージを使用してデータの永続性を実装する方法

目的: vuex で管理されている状態データを同時にローカルに保存できるようにします。独自のストレー...

Windows 7 で Python 3.4 を使って MySQL データベースを使用する

Python 3.4でMySQLデータベースを使用する詳細なプロセスは次のとおりです。 Window...

Dockerのyumソースの設定とCentOS7へのインストールの詳細な説明

ここではCentOS7が使用されており、カーネルバージョンは [root@localhost ~]#...

MySQL8 ベースの docker-compose デプロイメント プロジェクトの実装

1. まず、次のパスに従って対応するフォルダを作成します。 ローカルのdockerでmysqlを実行...

Vue3+TypeScriptはaxiosをカプセル化し、リクエスト呼び出しを実装します

まさか、2021年になってもTypeScriptについて聞いたことがない人がいるなんて?プロジェクト...

MySQLクエリ結果をCSVにエクスポートする方法

MySQL クエリ結果をcsvにエクスポートするには、通常、php を使用して mysql に接続し...

ログインインターセプションを実装するためのVueルーティング

目次1. 概要2. ログインインターセプションを実装するためのルーティングナビゲーションガード1. ...

JavaScript ベースのシンプルなカルーセルの実装

この記事では、シンプルなカルーセルを実装するためのJavaScriptの具体的なコードを参考までに紹...

Docker デプロイメント RabbitMQ コンテナ実装プロセス分析

1. イメージをプルするまず、次のコマンドを実行して、イメージをローカル コンピューターにダウンロー...