MySQL パフォーマンス最適化のための魔法のツール、Explain の基本的な使用分析

MySQL パフォーマンス最適化のための魔法のツール、Explain の基本的な使用分析

導入

MySQL には、SELECT ステートメントを分析し、開発者が最適化できるように SELECT 実行に関する詳細な情報を出力する EXPLAIN コマンドが用意されています。

EXPLAIN コマンドの使い方は非常に簡単です。SELECT ステートメントの前に Explain を追加するだけです。例:

EXPLAIN SELECT * from user_info WHERE id < 300;

準備する

EXPLAIN の使用方法を示すには、まず 2 つのテスト テーブルを作成し、対応するデータを追加する必要があります。

テーブル `user_info` を作成します (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL デフォルト ''
 `age` INT(11) デフォルト NULL,
 主キー (`id`)、
 キー `name_index` (`name`)
）
 エンジン = InnoDB
 デフォルト文字セット = utf8

user_info (名前、年齢) に VALUES ('xys'、20) を挿入します。
user_info (名前、年齢) に VALUES ('a'、21) を挿入します。
user_info (名前、年齢) に VALUES ('b'、23) を挿入します。
user_info (名前、年齢) に VALUES ('c'、50) を挿入します。
user_info (名前、年齢) に VALUES ('d'、15) を挿入します。
user_info (名前、年齢) に VALUES ('e'、20) を挿入します。
user_info (名前、年齢) に VALUES ('f'、21) を挿入します。
user_info (名前、年齢) に VALUES ('g'、23) を挿入します。
user_info (名前、年齢) に VALUES ('h'、50) を挿入します。
user_info (名前、年齢) に VALUES ('i'、15) を挿入します。
テーブル「order_info」を作成します（
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `user_id` BIGINT(20) デフォルト NULL,
 `product_name` VARCHAR(50) NOT NULL デフォルト ''
 `productor` VARCHAR(30) デフォルト NULL,
 主キー (`id`)、
 キー `user_product_detail_index` (`user_id`、`product_name`、`productor`)
）
 エンジン = InnoDB
 デフォルト文字セット = utf8

order_info (user_id、product_name、productor) に VALUES (1、'p1'、'WHH') を挿入します。
order_info (user_id、product_name、productor) に VALUES (1、'p2'、'WL') を挿入します。
order_info (user_id、product_name、productor) に VALUES (1、'p1'、'DX') を挿入します。
order_info (user_id、product_name、productor) に VALUES (2、'p1'、'WHH') を挿入します。
order_info (user_id、product_name、productor) に VALUES (2、'p5'、'WL') を挿入します。
order_info (user_id、product_name、productor) に VALUES (3、'p3'、'MA') を挿入します。
order_info (user_id、product_name、productor) に VALUES (4、'p1'、'WHH') を挿入します。
order_info (user_id、product_name、productor) に VALUES (6、'p1'、'WHH') を挿入します。
order_info (user_id、product_name、productor) に VALUES (9、'p8'、'TE') を挿入します。

EXPLAIN出力フォーマット

EXPLAIN コマンドの出力は、おおよそ次のようになります。

mysql> 説明 select * from user_info where id = 2\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   型: 定数
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: 定数
   行数: 1
  フィルター: 100.00
  追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

各列の意味は次のとおりです。

  • id: SELECT クエリの識別子。各 SELECT には一意の識別子が自動的に割り当てられます。
  • select_type: SELECT クエリのタイプ。
  • テーブル: クエリ対象のテーブル
  • パーティション: 一致するパーティション
  • タイプ: 結合タイプ
  • possible_keys: このクエリで使用できるインデックス
  • key: このクエリで使用される正確なインデックス。
  • ref: キーで使用されるフィールドまたは定数
  • 行数: このクエリによってスキャンされた行数を示します。これは推定値です。
  • フィルタリング: このクエリ条件によってフィルタリングされたデータの割合を示します
  • 追加: 追加情報

次に、より重要なフィールドをいくつか見てみましょう。

選択タイプ

  • select_type はクエリの種類を示し、一般的な値は次のとおりです。
  • SIMPLE、つまりこのクエリにはUNIONクエリやサブクエリは含まれません
  • PRIMARY、このクエリが最も外側のクエリであることを示します
  • UNION、このクエリがUNIONの2番目以降のクエリであることを示します。
  • DEPENDENT UNION、UNION内の2番目以降のクエリは外側のクエリに依存します
  • UNION RESULT、UNIONの結果
  • SUBQUERY、サブクエリの最初のSELECT
  • 依存サブクエリ: サブクエリの最初の SELECT は、外部クエリに依存します。つまり、サブクエリは外部クエリの結果に依存します。

最も一般的なクエリ タイプは SIMPLE です。たとえば、クエリにサブクエリや UNION クエリがない場合、通常は SIMPLE タイプになります。次に例を示します。

mysql> 説明 select * from user_info where id = 2\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   型: 定数
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: 定数
   行数: 1
  フィルター: 100.00
  追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

UNION クエリを使用する場合、EXPLAIN 出力は次のようになります。

mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3))
 -> ユニオン
 -> (SELECT * FROM user_info WHERE id IN (3, 4, 5));
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+
| 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | where の使用 |
| 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | where の使用 |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | 一時を使用 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+
セットに 3 行、警告 1 件 (0.00 秒)

テーブル

クエリに関係するテーブルまたは派生テーブルを示します

タイプ

タイプ フィールドは、クエリが効率的かどうかを判断するための重要な基準を提供するため重要です。タイプ フィールドを通じて、クエリがフル テーブル スキャンであるかインデックス スキャンであるかを判断できます。

タイプ 一般的なタイプ

type の一般的な値は次のとおりです。

  • system: テーブルにはデータが 1 つだけあります。この型は特別な const 型です。
  • const: 主キーまたは一意のインデックスに対する等値クエリの場合、返されるデータは最大で 1 行のみです。const クエリは 1 回しか読み取られないため、非常に高速です。
    たとえば、次のクエリでは主キー インデックスが使用されるため、type は const になります。
mysql> 説明 select * from user_info where id = 2\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   型: 定数
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: 定数
   行数: 1
  フィルター: 100.00
  追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)
  • eq_ref: このタイプは通常、複数テーブルの結合クエリで使用され、前のテーブルの各結果に対して、次のテーブルの結果の 1 行のみが一致することを示します。クエリの比較演算は通常 = であり、クエリ効率が高くなります。例:
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: order_info
 パーティション: NULL
   タイプ: インデックス
可能なキー: ユーザー製品詳細インデックス
   キー: user_product_detail_index
  キーの長さ: 314
   参照: NULL
   行数: 9
  フィルター: 100.00
  追加: where の使用; index の使用
************************** 2. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   タイプ: eq_ref
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: test.order_info.user_id
   行数: 1
  フィルター: 100.00
  追加: NULL
セットに 2 行、警告 1 件 (0.00 秒)
  • ref: このタイプは通常、複数テーブルの結合クエリ、一意でないキーまたは主キーでないインデックス、または左端のプレフィックス ルール インデックスを使用するクエリで使用されます。

たとえば、次の例では、ref 型クエリが使用されています。

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   型: 定数
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: 定数
   行数: 1
  フィルター: 100.00
  追加: NULL
************************** 2. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: order_info
 パーティション: NULL
   タイプ: ref
可能なキー: ユーザー製品詳細インデックス
   キー: user_product_detail_index
  キーの長さ: 9
   参照: 定数
   行数: 1
  フィルター: 100.00
  追加: インデックスの使用
セットに 2 行、警告 1 件 (0.01 秒)
  • 範囲: インデックス範囲クエリを使用して、インデックス フィールド範囲を通じてテーブル内の部分的なデータ レコードを取得することを示します。このタイプは通常、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 操作で使用されます。

type が range の場合、EXPLAIN 出力の ref フィールドは NULL になり、key_len フィールドはクエリで使用される最長のインデックスになります。

たとえば、次の例は範囲クエリです。

mysql> EXPLAIN SELECT *
 -> user_info から
 -> ID が 2 から 8 の間 \G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   タイプ: 範囲
可能なキー: プライマリ
   キー: PRIMARY
  キーの長さ: 8
   参照: NULL
   行数: 7
  フィルター: 100.00
  追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)
  • index: 完全なインデックス スキャンを示します。これは ALL タイプに似ていますが、ALL タイプはテーブル全体をスキャンするのに対し、index タイプはデータをスキャンせずにすべてのインデックスのみをスキャンする点が異なります。

インデックス タイプは通常、クエリ対象のデータが、データをスキャンせずにインデックス ツリーで直接取得できる場合に表示されます。この場合、[追加] フィールドに [インデックスを使用] と表示されます。

例えば：

mysql> EXPLAIN SELECT name FROM user_info \G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: user_info
 パーティション: NULL
   タイプ: インデックス
可能なキー: NULL
   キー: name_index
  キーの長さ: 152
   参照: NULL
   行数: 10
  フィルター: 100.00
  追加: インデックスの使用
セットに 1 行、警告 1 件 (0.00 秒)

上記の例では、クエリしている名前フィールドがインデックスであるため、テーブル内のデータをクエリしなくても、クエリ要件を満たすデータをインデックスから直接取得できます。したがって、この場合、type の値は index で、Extra の値は Using index です。

  • ALL: 完全なテーブルスキャンを示します。このタイプのクエリは、パフォーマンスが最も悪いクエリの 1 つです。一般的に、ALL タイプのクエリは使用すべきではありません。このようなクエリは、データ量が多い場合にデータベースのパフォーマンスに大きな悪影響を及ぼすためです。クエリが ALL タイプのクエリである場合、一般的に、対応するフィールドにインデックスを追加してこれを回避できます。

以下は、フル テーブル スキャンの例です。フル テーブル スキャンでは、possible_keys フィールドと key フィールドの両方が NULL であり、インデックスが使用されていないこと、行が非常に大きいこと、そのため全体的なクエリ効率が非常に低いことがわかります。

mysql> EXPLAIN SELECT age FROM user_info WHERE age = 20 \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: user_info
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 10
   フィルター: 10.00
    追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)

タイプ別の性能比較

一般的に言えば、さまざまなタイプのパフォーマンス関係は次のとおりです。

ALL < インデックス < 範囲 ~ index_merge < ref < eq_ref < const < システム

ALL タイプは、同じクエリ条件でテーブル全体をスキャンするため、最も低速です。

インデックス タイプのクエリはテーブル全体をスキャンしませんが、すべてのインデックスをスキャンするため、ALL タイプよりもわずかに高速になります。

以下のタイプはすべてインデックスを使用してデータをクエリするため、データの一部または大部分をフィルター処理でき、クエリの効率が比較的高くなります。

可能なキー

possible_keys は、MySQL がクエリ時に使用できるインデックスを示します。possible_keys にインデックスがいくつか表示されていても、このインデックスが実際に MySQL によって使用されるわけではないことに注意してください。クエリ時に MySQL が使用する特定のインデックスは、キー フィールドによって決まります。

鍵

このフィールドは、MySQL が現在のクエリで実際に使用するインデックスです。

キーの長さ

クエリ オプティマイザーによって使用されるバイト数を示します。このフィールドは、複合インデックスが完全に使用されているか、左端のフィールドのみが使用されているかを評価できます。
key_len の計算規則は次のとおりです。

弦

  • char(n): 長さはnバイト
  • varchar(n): utf8 エンコーディングの場合は 3n+2 バイト、utf8mb4 エンコーディングの場合は 4n+2 バイト。

値のタイプ:

  • TINYINT: 1バイト
  • SMALLINT: 2バイト
  • MEDIUMINT: 3 バイト
  • INT: 4バイト
  • BIGINT: 8 バイト

時間タイプ

  • 日付: 3 バイト
  • タイムスタンプ: 4 バイト
  • DATETIME: 8 バイト

フィールド属性: NULL 属性は 1 バイトを占めます。フィールドが NULL でない場合、この属性はありません。

2つの簡単な例を見てみましょう。

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: order_info
  パーティション: NULL
     タイプ: 範囲
可能なキー: ユーザー製品詳細インデックス
     キー: user_product_detail_index
   キーの長さ: 9
     参照: NULL
     行数: 5
   フィルタリング: 11.11
    追加: where の使用; index の使用
セットに 1 行、警告 1 件 (0.00 秒)

上記の例では、テーブル order_info から指定されたコンテンツをクエリします。テーブル作成ステートメントから、テーブル order_info に結合インデックスがあることがわかります。

キー `user_product_detail_index` (`user_id`、`product_name`、`productor`)

ただし、クエリ WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' では、user_id 範囲クエリが最初に実行されます。左端のプレフィックス マッチングの原則によると、範囲クエリに遭遇すると、インデックス マッチングは停止します。したがって、実際に使用するインデックス フィールドは us​​er_id のみです。したがって、EXPLAIN では、表示される key_len は 9 です。user_id フィールドは BIGINT であるため、8 バイトを占め、NULL 属性は 1 バイトを占めるため、合計は 9 バイトになります。user_id フィールドを BIGINT(20) NOT NULL DEFAULT '0' に変更すると、key_length は 8 になります。

左端のプレフィックス一致の原則により、クエリでは結合インデックスの user_id フィールドのみが使用されるため、効率は高くありません。

次の例を見てみましょう。

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G;
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: order_info
  パーティション: NULL
     タイプ: ref
可能なキー: ユーザー製品詳細インデックス
     キー: user_product_detail_index
   キーの長さ: 161
     参照: const、const
     行数: 2
   フィルター: 100.00
    追加: インデックスの使用
セットに 1 行、警告 1 件 (0.00 秒)

このクエリでは、範囲クエリを使用しておらず、key_len の値は 161 です。なぜでしょうか? クエリ条件 WHERE user_id = 1 AND product_name = 'p1' では、結合インデックスの最初の 2 つのフィールドのみが使用されるため、keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161 となります。

行

行も重要なフィールドです。統計に基づいて、MySQL クエリ オプティマイザーは、結果セットを見つけるために SQL がスキャンする必要がある行数を推定します。
この値は SQL の効率を直接示します。原則として、行数が少ないほど効率が高くなります。

余分な

EXplain の多くの追加情報は Extra フィールドに表示されます。一般的な情報は次のとおりです。

  • ファイルソートの使用

Extra に Using filesort が含まれている場合、MySQL では追加のソート操作が必要であり、インデックス順序によるソート効果を実現できないことを意味します。このようなクエリは CPU リソースを大量に消費するため、一般的に、Using filesort を最適化して削除することをお勧めします。

たとえば、次の例をご覧ください。

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: order_info
  パーティション: NULL
     タイプ: インデックス
可能なキー: NULL
     キー: user_product_detail_index
   キーの長さ: 253
     参照: NULL
     行数: 9
   フィルター: 100.00
    追加: インデックスの使用; ファイルソートの使用
セットに 1 行、警告 1 件 (0.00 秒)

私たちのインデックスは

キー `user_product_detail_index` (`user_id`、`product_name`、`productor`)

ただし、上記のクエリは product_name でソートされているため、インデックスを最適化に使用することはできず、filesort を使用することになります。

ソート基準を ORDER BY user_id, product_name に変更すると、Using filesort は表示されなくなります。例:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: order_info
  パーティション: NULL
     タイプ: インデックス
可能なキー: NULL
     キー: user_product_detail_index
   キーの長さ: 253
     参照: NULL
     行数: 9
   フィルター: 100.00
    追加: インデックスの使用
セットに 1 行、警告 1 件 (0.00 秒)
  • インデックスの使用

「カバーリングインデックススキャン」とは、クエリがテーブルデータファイルをスキャンせずにインデックスツリー内の必要なデータを見つけることができることを意味し、多くの場合、良好なパフォーマンスを示します。

  • 一時的な使用

クエリは一時テーブルを使用します。これは通常、並べ替え、グループ化、および複数テーブルの結合の状況で発生します。クエリの効率は高くありません。最適化することをお勧めします。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • MySQL のパフォーマンス分析と使用方法の説明
  • mysql explain（分析インデックス）の使い方の詳しい説明
  • MySql で SQL 実行プランをクエリするために explain を使用する方法
  • MySQL は低速クエリを可能にします (EXPLAIN SQL ステートメントの使用の概要)
  • mysql explain の使用法 (クエリ ステートメントを最適化するために explain を使用する)
  • MySQL 最適化ツールである explain の使い方の紹介
  • MySQL 実験: explain を使用してインデックスの傾向を分析する
  • Mysql Explainコマンドの使用と分析
  • MySQLの詳細な説明Explain
  • MySQL で explain ステートメントを使用する基本的なチュートリアル

<<:  WeChatアプレットが9マスグリッド効果を実現

>>:  Linux環境でタイムゾーンを設定できない問題を解決

推薦する

MySQL 8.0.16 圧縮パッケージのインストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 8.0.16圧縮パッケージのインストールと設定方法を参考までに紹介します。...

MySQLのストレージエンジンについてお話しましょう

基礎リレーショナル データベースでは、各データ テーブルはファイルに相当し、異なるストレージ エンジ...

DockerはGitを使用してJenkinsのリリースとテストプロジェクトの詳細なプロセスを実装します

目次1. Dockerをインストールする2. カスタムネットワークアドレスを作成する3. Docke...

Vueのアイデアを使ってストレージをカプセル化する方法

目次背景関数目的アイデアの源成し遂げるセット得るプロパティの削除拡張機能を禁止するもっている要約する...

WeChatアプレットはシンプルなチャットルームを実装します

この記事では、WeChatアプレットの具体的なコードを共有し、簡単なチャットルームを実装します。具体...

25 個の CSS フレームワーク、ツール、ソフトウェア、テンプレートを共有

スプライトカウダウンロード CSS リントダウンロード プレフィックスダウンロード 1140px C...

mysql mycat ミドルウェアの簡単な紹介

1. mycatとはエンタープライズアプリケーション開発のための完全にオープンソースの大規模データベ...

HTML における相対と絶対の使用法と違いの詳細な説明

HTML における相対と絶対の違い: 正直に言うと、HTML は世界で最もシンプルな言語です。タグ言...

Vue でのルーティングパラメータの受け渡しとクロスコンポーネントパラメータの受け渡しの詳細な説明

ルートジャンプ this.$router.push('/course'); this...

Vue フロントエンド開発における keepAlive の使用方法の詳細な説明

目次序文keep-avlive フック関数keep-avliveはどのコンポーネントをキャッシュする...

HTML の ReadOnly と Enabled の違い

ReadOnly 属性を持つ TextBox は、クライアント上で次のマークアップとして表示されます...

JavaScript の setTimeout と setTimeinterval の使用例の説明

どちらの方法も、一定時間後に JavaScript コードを実行するために使用できますが、それぞれに...

JS で配列の重複排除を実装する 7 つの方法

目次1. Set()+Array.from() を使用する2. 2層ループ+アレイ接合方式の使用3....

mysql explain（分析インデックス）の使い方の詳しい説明

EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合す...

Linux ファイル管理コマンド例の分析 [表示、閲覧、統計など]

この記事では、Linux ファイル管理コマンドについて例を挙げて説明します。ご参考までに、詳細は以下...