MySQLクエリ最適化分析チュートリアルをステップバイステップで教えます

序文

MySQL は、強力なクエリ機能、高いデータ一貫性、高いデータセキュリティ、およびセカンダリインデックスのサポートを備えたリレーショナルデータベースです。ただし、そのパフォーマンスは非リレーショナルデータベースに比べてわずかに劣り、特にデータが 100 万を超えると、クエリが遅くなりやすくなります。このとき、クエリが遅い理由を分析する必要があります。一般的に、これはプログラマーの SQL の書き方が下手、キーインデックスが不足、またはインデックスが無効であることが原因で発生します。

このとき、MySQL が提供する EXPLAIN コマンドが特に重要です。このコマンドは、SELECT 文を分析し、SELECT 実行に関する詳細な情報を出力して、開発者がターゲットを絞って最適化できるようにします。

クエリステートメントの前に Explain を追加するだけです。

EXPLAIN SELECT * FROM customer WHERE id < 100;

準備する

まず、2 つのテストテーブルとデータを作成する必要があります。

テーブル「顧客」を作成します（ 
 `id` BIGINT(20) 符号なし NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL デフォルト ''
 `age` INT(11) 符号なし DEFAULT NULL,
 主キー (`id`)、
 キー `name_index` (`name`)
) エンジン = InnoDB デフォルト文字セット = utf8mb4

顧客 (名前、年齢) に VALUES ('a'、1) を挿入します。
顧客（名前、年齢）にVALUES（'b'、2）を挿入します。
顧客（名前、年齢）にVALUES（'c'、3）を挿入します。
顧客（名前、年齢）にVALUES（'d'、4）を挿入します。
顧客（名前、年齢）にVALUES（'e'、5）を挿入します。
顧客（名前、年齢）にVALUES（'f'、6）を挿入します。
顧客（名前、年齢）にVALUES（'g'、7）を挿入します。
顧客 (名前、年齢) に VALUES ('h'、8) を挿入します。
顧客 (名前、年齢) に VALUES ('i'、9) を挿入します。

テーブル「orders」を作成します（
 `id` BIGINT(20) 符号なし NOT NULL AUTO_INCREMENT,
 `user_id` BIGINT(20) unsigned NOT NULL DEFAULT 0, `product_name` VARCHAR(50) NOT NULL DEFAULT '',
 `productor` VARCHAR(30) NOT NULL デフォルト ''
 主キー (`id`)、
 キー `user_product_detail_index` (`user_id`、`product_name`、`productor`)
) エンジン = InnoDB デフォルト文字セット = utf8mb4

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

EXPLAIN出力フォーマット

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

mysql> id = 1\G の顧客から * を選択します。
************************** 1. 行 ****************************
 id: 1
 選択タイプ: シンプル
 テーブル: 顧客
 パーティション: NULL
 型: 定数
可能なキー: プライマリ
 キー: PRIMARY
 キーの長さ: 8
 参照: 定数
 行数: 1
 フィルター: 100.00
 追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

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

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

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

選択タイプ

SIMPLE - サブクエリやUNIONを含まない単純な選択クエリ
PRIMARY - クエリに複雑なサブクエリが含まれている場合、最も外側のクエリがプライマリとしてマークされます。
UNION - このクエリがUNIONの2番目以降のクエリであることを示します。
依存ユニオン - 外部クエリに依存するユニオン内の2番目以降のクエリ
UNION RESULT - UNION テーブルから結果を選択
DERIVED - from リストに含まれるサブクエリは派生としてマークされます。MySQL はこれらのサブクエリを再帰的に実行し、結果を一時テーブルに配置します。
SUBQUERY - SELECTまたはWHEREリストにサブクエリが含まれています
依存サブクエリ - サブクエリの最初の SELECT は外部クエリに依存します。つまり、サブクエリは外部クエリの結果に依存します。

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

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

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

mysql> EXPLAIN (SELECT * FROM customer WHERE id IN (1, 2, 3)) 
 -> ユニオン
 -> (SELECT * FROM customer WHERE id IN (3, 4, 5));
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+
| 1 | PRIMARY | 顧客 | NULL | 範囲 | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | where の使用 |
| 2 | UNION | customer | 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> explain select * from customer where id = 2\G
************************** 1. 行 ****************************
 id: 1
 選択タイプ: シンプル
 テーブル: 顧客
 パーティション: NULL
 型: 定数
可能なキー: プライマリ
 キー: PRIMARY
 キーの長さ: 8
 参照: 定数
 行数: 1
 フィルター: 100.00
 追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

eq_ref: このタイプは通常、複数テーブルの結合クエリで使用され、前のテーブルの各結果に対して、次のテーブルの結果の 1 行のみが一致することを示します。クエリの比較演算は通常 = であり、クエリ効率が高くなります。例:

mysql> EXPLAIN SELECT * FROM customer, order_info WHERE customer.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
 選択タイプ: シンプル
 テーブル: 顧客
 パーティション: NULL
  タイプ: eq_ref
可能なキー: プライマリ
  キー: PRIMARY
 キーの長さ: 8
  参照: test.order_info.user_id
  行数: 1
 フィルター: 100.00
 追加: NULL
セットに 2 行、警告 1 件 (0.00 秒)

ref: このタイプは通常、複数テーブルの結合クエリ、一意でないキーまたは主キーでないインデックス、または左端のプレフィックスルールインデックスを使用するクエリで使用されます。

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

mysql> EXPLAIN SELECT * FROM customer, order_info WHERE customer.id = order_info.user_id AND order_info.user_id = 5\G
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル: 顧客
 パーティション: 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 秒)

range: インデックス範囲クエリを使用して、インデックスフィールド範囲を通じてテーブル内の部分的なデータレコードを取得することを示します。このタイプは通常、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 操作で使用されます。タイプが範囲の場合、EXPLAIN によって出力される ref フィールドは NULL になり、key_len フィールドはこのクエリで使用される最長のインデックスになります。

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

mysql> EXPLAIN SELECT * FROM customer WHERE id BETWEEN 2 AND 8 \G
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル: 顧客
 パーティション: NULL
  タイプ: 範囲
可能なキー: プライマリ
  キー: PRIMARY
 キーの長さ: 8
  参照: NULL
  行数: 7
 フィルター: 100.00
 追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)

index: 完全なインデックススキャンを示します。これは ALL タイプに似ていますが、ALL タイプはテーブル全体をスキャンするのに対し、index タイプはデータをスキャンせずにすべてのインデックスのみをスキャンする点が異なります。

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

例えば：

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

タイプ別の性能比較

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

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

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

インデックスタイプのクエリはテーブル全体をスキャンしませんが、すべてのインデックスをスキャンするため、ALLタイプよりもわずかに高速です。次のタイプはすべてインデックスを使用してデータをクエリするため、データの一部または大部分をフィルタリングできるため、クエリ効率が比較的高くなります。

プログラマーの場合、クエリが少なくとも範囲レベル、できれば参照レベルに到達することを保証できれば、優秀で責任あるプログラマーであると見なされます。

ALL: (フルテーブルスキャン) フルテーブルスキャンは間違いなく最悪です。データ量が数百万または数千万の場合、フルテーブルスキャンは非常に遅くなります。
インデックス: (フルインデックススキャン) フルインデックスファイルスキャンは、すべてよりはるかに優れています。結局のところ、インデックスツリーからデータを検索する方が、テーブル全体からデータを検索するよりも高速です。
range: インデックスを使用して行を照合し、指定された範囲内の行のみを取得します。範囲が狭まり、当然ながらフルテーブルスキャンやフルインデックスファイルスキャンよりも高速になります。 SQL ステートメントには通常、between、in、>、< などのクエリが含まれます。
ref: 非一意インデックススキャンは、基本的に、単一の値に一致するすべての行を返すインデックスアクセスです。たとえば、会社の R&D チームのすべての同僚を照会すると、一致する結果は複数になりますが、一意の値ではありません。
eq_ref: 一意のインデックススキャン。各インデックスキーに対して、テーブル内に一致するレコードが存在します。たとえば、会社の CEO を照会すると、一致する結果は 1 つのレコードのみになる場合があります。
const: 一度インデックスを付けるだけで値を見つけられることを示します。const は主キーまたは一意のインデックスを比較するために使用されます。一致するデータは 1 行のみであるため、主キーが where リストに配置されていれば、MySQL はクエリをすぐに定数に変換できます。
system: テーブルにはレコードが 1 つだけあります (システムテーブルと同じです)。これは const 型の特別な列です。通常は表示されません。理解してください。

可能なキー

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

鍵

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

キーの長さ

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

key_len の計算規則は次のとおりです。

弦
- char(n): 長さはnバイト
- varchar(n): utf8エンコードの場合は3
  n + 2 バイト。utf8mb4 エンコードの場合は 4
  n + 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 範囲クエリが最初に実行されます。左端のプレフィックスマッチングの原則によると、範囲クエリに遭遇すると、インデックスマッチングは停止します。したがって、実際に使用するインデックスフィールドは user_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 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 秒)

私たちのインデックスは

キー `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 秒)