MySQL EXPLAN出力列の詳細説明

1.概要
EXPLAN文はMySQLがどうやって文を実行するかに関する情報を提供します。
EXPLANはSELECT、DELETE、INSERT、REPLACEとUDATE文と一緒に使います。

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

簡単に言うと、EXPLANではSQL文がインデックスに行っていないかどうかを分析できます。インデックスは何ですか？
EXPLANはSELECT文で使われている表ごとに情報を返します。MySQLが文を処理する時にそれらを読み取る順番に出力中の表を並べました。
MySQLは、ネストされた循環接続（Nested-loop Join Algorithms）を使用して、すべての接続を解析します。これは、MySQLが最初のテーブルから一行を読み取ってから、第二のテーブル、第三のテーブルで該当する行を見つけたことを意味します。これに類推します。すべてのテーブルを処理した後、MySQLは選択した列を表のリストで出力し、テーブルを見つけたら、より多くのマッチする行があるまで遡ります。この表から次の行を読み取って次の表に進みます。
2.EXPLAN出力列

MySQLバージョン5.7.33

Windows 10 64ビット

上の図から見たEXPLANの結果には、表頭id、select_が含まれています。type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra、これらのフィールドの意味を学習して、実例を通じて確認します。
2.1 id
SELECT識別子、SELECTのシーケンス番号を調べます。行が他の行の集合結果を参照すると、その値はNULLとすることができる。この場合、テーブル列は、行参照ID値がMおよびNの行の集合であることを示すために、同様の＜unionM、N＞の値を表示する。
idの値は3つの状況に分けられます。
idは同じで、実行順序は上から下までです。

mysql> EXPLAIN (
    -> SELECT * FROM employees 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 | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
|  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 rows in set, 1 warning (0.03 sec)

idが異なり、サブクエリーであれば、idの番号が増え、idの値が大きいほど実行される優先度が高くなります。

mysql> EXPLAIN SELECT * FROM employees 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 | table       | partitions | type  | possible_keys     | key       | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
|  1 | PRIMARY     | emp         | NULL       | ALL   | NULL              | NULL      | NULL    | NULL  | 299468 |   100.00 | Using where              |
|  2 | SUBQUERY    | de          | NULL       | index | PRIMARY           | dept_no   | 12      | NULL  | 308493 |   100.00 | Using where; Using index |
|  3 | SUBQUERY    | departments | NULL       | const | PRIMARY,dept_name | dept_name | 122     | const |      1 |   100.00 | Using index              |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

idは同じでも違っても存在します。
IDが同一である場合、同じグループのIDの実行順序は上から下までであり、異なるグループの間では、id値が大きいほど、実行される優先度が高い。

mysql> EXPLAIN SELECT * FROM employees 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  | table       | partitions | type  | possible_keys   | key       | key_len | ref                           | rows   | filtered | Extra                                              |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL            | NULL      | NULL    | NULL                          |   NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | emp         | NULL       | ALL   | PRIMARY         | NULL      | NULL    | NULL                          | 299468 |     0.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | departments | NULL       | index | PRIMARY         | dept_name | 122     | NULL                          |      9 |    11.11 | Using where; Using index                           |
|  2 | MATERIALIZED | de          | NULL       | ref   | PRIMARY,dept_no | dept_no   | 12      | employees.departments.dept_no |  38561 |   100.00 | Using index                                        |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.01 sec)

2.2 select_タイプ
クエリーの種類は、主に一般的なクエリー、共同クエリ、サブクエリなどの複雑なクエリを区別するために使用されます。
SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUEY、UNCACHEABLE UNIONを含みます。
SIM PLE
簡単なSELECTは、UNIONまたはサブクエリを使用しません。

mysql> EXPLAIN select * from employees where emp_no=10001;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

PRIMARY
クエリーに複雑な部分が含まれている場合、最外階のクエリーはPRIMARYとしてマークされます。

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT max(emp_no) FROM dept_emp);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                        |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
|  1 | PRIMARY            | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299468 |   100.00 | Using where                  |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL |   NULL |     NULL | Select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

ユニオン
二つ目またはもっと後ろのSELECT文がUNION後にあるとUNIONと表記されます。

mysql> EXPLAIN (SELECT emp_no,dept_no FROM dept_emp LIMIT 10)
    -> UNION
    -> SELECT emp_no,dept_no FROM dept_manager;
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| id | select_type  | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra           |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
|  1 | PRIMARY      | dept_emp     | NULL       | index | NULL          | dept_no | 12      | NULL | 308493 |   100.00 | Using index     |
|  2 | UNION        | dept_manager | NULL       | index | NULL          | dept_no | 12      | NULL |     24 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2>   | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

DEPENDENT UNION
UNIONと同様に、UNIONまたはUNION ALL文に表示されますが、このクエリは外部クエリの影響を受けます。
|ユニオンRESULT ユニオン_レスリング Result of a UNION.
|SUBQUERY ノン？ネ First SELECT in subquery
|DEPENDENT SUBQUERY dependent(true) First SELECT in subquery，dependent on outer query
|DERIVD ノン？ネ Derivedテーブル
|MATERIALIZED materialized_fromsubquery Materialized subquery
|UNCACHEABLE SUBQUERY cachebale(false) A subquery for which the relt cannot be cached and must be re-evaluated for each row of the outer query
|UNCACHEABLE UNION cachebale(false) The second or later select in a UNIUNON that belongs to an uncacheable subquery(see UNCACHEABLE SUBQUERY)
締め括りをつける
ここで、MySQL EXPLANの出力列に関する記事を紹介します。MySQL EXPLANの出力列の内容については、以前の文章を検索したり、下記の関連記事を見たりしてください。これからもよろしくお願いします。