MySQL EXPLAIN出力列の詳細な説明

1. はじめに

EXPLAIN ステートメントは、MySQL がステートメントを実行する方法に関する情報を提供します。

EXPLAIN は、SELECT、DELETE、INSERT、REPLACE、および UPDATE ステートメントで使用されます。

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001;
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
| 1 | SIMPLE | 従業員 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

簡単に言えば、EXPLAIN を使用すると、SQL ステートメントがインデックスを使用するかどうか、またどのインデックスを使用するかを分析できます。

EXPLAIN は、SELECT ステートメントで使用されるテーブルごとに 1 行を返します。出力には、ステートメントの処理中に MySQL が読み取る順序でテーブルがリストされます。

MySQL は、ネストされたループ結合アルゴリズムを使用してすべての結合を解決します。つまり、MySQL は最初のテーブルから行を読み取り、次に 2 番目のテーブル、3 番目のテーブルなどで一致する行を検索します。すべてのテーブルを処理した後、MySQL は、選択された列を出力した後、一致する行がさらに存在するテーブルが見つかるまでテーブルリストを遡ります。その表から次の行を読み取り、次の表に進みます。

2. EXPLAIN出力列

MySQL バージョン 5.7.33
Windows 10 64ビット

上の図から、テーブルヘッダー id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra を含む EXPLAIN の結果を確認できます。これらのフィールドの意味を学び、例を通して理解しましょう。

2.1 id

SELECT 識別子、クエリ内の SELECT のシーケンス番号。行が他の行の結合結果を参照する場合、この値は NULL になることがあります。この場合、テーブル列には <unionM,N> のような値が表示され、その行が ID 値が M と N の行の結合を参照していることを示します。

ID 値には 3 つの種類があります。

IDは同じで、実行順序は上から下です

mysql> EXPLAIN (
    -> SELECT * FROM 従業員 emp
    -> LEFT JOIN dept_emp de ON emp.emp_no = de.emp_no
    -> LEFT JOIN departments dept ON dept.dept_no = de.dept_no
    -> WHERE emp.emp_no = 10001);
+----+-------------+---------+-----------+----------+---------------+---------+--------+-----------------------+-------+-------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+----------+---------------+---------+--------+-----------------------+-------+-------+------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | de | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 12 | employees.de.dept_no | 1 | 100.00 | NULL |
+----+-------------+---------+-----------+----------+---------------+---------+--------+-----------------------+-------+-------+------+
セットに 3 行、警告 1 回 (0.03 秒)

idが異なります。サブクエリの場合はid番号が増加します。id値が大きいほど、実行の優先度が高くなります。

mysql> EXPLAIN SELECT * FROM 従業員 emp
    -> WHERE emp.emp_no NOT IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no NOT IN ( SELECT dept_no FROM departments WHERE dept_name = 'Development'));
+----+--------------+-------------+------------+---------+-------------------+----------+---------+----------+----------+---------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+---------+-------------------+----------+---------+----------+----------+---------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | where の使用 |
| 2 | サブクエリ | de | NULL | インデックス | プライマリ | dept_no | 12 | NULL | 308493 | 100.00 | where の使用; インデックスの使用 |
| 3 | サブクエリ | departments | NULL | const | PRIMARY、dept_name | dept_name | 122 | const | 1 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+---------+-------------------+----------+---------+----------+----------+---------------------------+
セットに 3 行、警告 1 件 (0.00 秒)

同じIDと異なるIDの両方が存在する

ID が同じ場合はグループとしてみなされます。同じグループ ID の実行順序は上から下になります。異なるグループ間では、ID 値が大きいほど実行優先度が高くなります。

mysql> EXPLAIN SELECT * FROM 従業員 emp
    -> WHERE emp.emp_no IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no IN ( SELECT dept_no FROM departments WHERE dept_name LIKE '%Develop%'));
+----+--------------+--------------+------------+---------+-----------------+----------+---------+-------------------------------+---------+----------+----------------------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+--------------+------------+---------+-----------------+----------+---------+-------------------------------+---------+----------+----------------------------------------------------+
| 1 | SIMPLE | <サブクエリ2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | emp | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299468 | 0.00 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
| 2 | マテリアライズド | 部門 | NULL | インデックス | プライマリ | 部門名 | 122 | NULL | 9 | 11.11 | where の使用; インデックスの使用 |
| 2 | MATERIALIZED | de | NULL | ref | PRIMARY、dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | 100.00 | インデックスを使用 |
+----+--------------+--------------+------------+---------+-----------------+----------+---------+-------------------------------+---------+----------+----------------------------------------------------+
セットに 4 行あり、警告 1 件 (0.01 秒)

2.2 選択タイプ

クエリタイプは主に、共通クエリ、結合クエリ、サブクエリ、その他の複雑なクエリを区別するために使用されます。

SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION が含まれます。

単純

単純な SELECT で、UNION やサブクエリはありません。

mysql> EXPLAIN select * from employees where emp_no=10001;
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
| 1 | SIMPLE | 従業員 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+-----------+---------+-------+---------------+---------+-------+-------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

主要な

クエリに複雑なサブパーツが含まれている場合、最も外側のクエリはPRIMARYとしてマークされます。

mysql> EXPLAIN SELECT * FROM 従業員 emp
    -> WHERE emp.emp_no IN ( SELECT max(emp_no) FROM dept_emp);
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | where の使用 |
| 2 | 従属サブクエリ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 最適化されたテーブルを選択 |
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
セットに 2 行、警告 1 件 (0.00 秒)

連合

UNIONの後に現れる2番目以降のSELECT文はUNIONとしてマークされます。

mysql> EXPLAIN (SELECT emp_no,dept_no FROM dept_emp LIMIT 10)
    -> ユニオン
    -> dept_manager から emp_no、dept_no を選択します。
+----+--------------+--------------+------------+-------+---------------+---------+--------+---------+----------+-----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+--------------+------------+-------+---------------+---------+--------+---------+----------+-----------------+
| 1 | PRIMARY | dept_emp | NULL | インデックス | NULL | dept_no | 12 | NULL | 308493 | 100.00 | インデックスを使用 |
| 2 | UNION | dept_manager | NULL | index | NULL | dept_no | 12 | NULL | 24 | 100.00 | インデックスを使用 |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | 一時を使用 |
+----+--------------+--------------+------------+-------+---------------+---------+--------+---------+----------+-----------------+
セットに 3 行、警告 1 件 (0.00 秒)

従属連合

UNIONと同様に、UNIONまたはUNION ALLステートメントに表示されますが、このクエリは外部クエリの影響を受けます。

| UNION RESULT union_result UNION の結果。
| SUBQUERY なしサブクエリの最初のSELECT
| DEPENDENT SUBQUERY 依存 (true) サブクエリ内の最初のSELECT、外部クエリに依存
| DERIVED なし派生テーブル
| MATERIALIZED materialized_from_subquery マテリアライズドサブクエリ
| UNCACHEABLE SUBQUERY キャッシュ可能 (false) 結果をキャッシュできず、外部クエリの各行ごとに再評価する必要があるサブクエリ
| UNCACHEABLE UNION キャッシュ可能 (false) キャッシュ不可能なサブクエリに属する UNION 内の 2 番目以降の選択 (UNCACHEABLE SUBQUERY を参照)