MySQLのEXPLAIN解説

# EXPLAINとは
MySQLの`EXPLAIN`を使用することで、MySQLがクエリを実行する方法についての実行計画を知ることができます。
この実行計画によってクエリやインデックス最適化を行うことができます。

# データ準備
本題に入る前に、今回の解説で利用するテーブルとデータを作成します。
下記のような`company`テーブル、`employee`テーブル、`message`テーブルを準備します。

```sql:companyテーブルの作成
mysql> CREATE TABLE `company` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `company_name` VARCHAR(50) COMMENT '会社名',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会社';
Query OK, 0 rows affected (0.01 sec)
```

```sql:employeeテーブルの作成
mysql> CREATE TABLE `employee` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `name` VARCHAR(20) COMMENT '名前',
    ->   `company_id` INT UNSIGNED NOT NULL COMMENT '会社ID',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   `updated_at` datetime NOT NULL COMMENT '更新日時',
    ->   PRIMARY KEY (`id`)
    ->   KEY `idx_employee_created_at` (`created_at`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='従業員';
Query OK, 0 rows affected (0.01 sec)
```

```sql:messageテーブルの作成
mysql> CREATE TABLE `message` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `employee_id` INT UNSIGNED NOT NULL COMMENT '従業員id',
    ->   `message` TEXT COMMENT 'メッセージ',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   `updated_at` datetime NOT NULL COMMENT '更新日時',
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_message_employee_id` (`employee_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='メッセージ';
Query OK, 0 rows affected (0.00 sec)
```

```sql:companyのデータ準備
mysql> INSERT INTO company VALU (1, '株式会社hoge', NOW()););
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO company VALUES (10, 'fuga株式会社', NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM company;
+----+------------------+---------------------+
| id | company_name     | created_at          |
+----+------------------+---------------------+
|  1 | 株式会社hoge     | 2021-05-06 16:04:29 |
| 10 | fuga株式会社     | 2021-05-06 16:04:56 |
+----+------------------+---------------------+
2 rows in set (0.01 sec)

```
```sql:employeeのデータ準備
mysql> INSERT INTO employee VALUES (1, '山田', 1, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, '佐藤', 10, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *  FROM employee;
+----+--------+------------+---------------------+---------------------+
| id | name   | company_id | created_at          | updated_at          |
+----+--------+------------+---------------------+---------------------+
|  1 | 山田   |          1 | 2021-05-06 16:07:30 | 2021-05-06 16:07:30 |
|  2 | 佐藤   |         10 | 2021-05-06 16:07:38 | 2021-05-06 16:07:38 |
+----+--------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
```

```sql:messageのデータ準備
mysql> INSERT INTO message VALUES (1, 1, 'こんにちは', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (2, 2, '元気ですか', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (3, 1, 'hello', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (4, 1, 'good night', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM message;
+----+-------------+-----------------+---------------------+---------------------+
| id | employee_id | message         | created_at          | updated_at          |
+----+-------------+-----------------+---------------------+---------------------+
|  1 |           1 | こんにちは      | 2021-05-06 16:08:30 | 2021-05-06 16:08:30 |
|  2 |           2 | 元気ですか      | 2021-05-06 16:08:36 | 2021-05-06 16:08:36 |
|  3 |           1 | hello           | 2021-05-06 16:38:59 | 2021-05-06 16:38:59 |
|  4 |           1 | good night      | 2021-05-06 16:39:10 | 2021-05-06 16:39:10 |
+----+-------------+-----------------+---------------------+---------------------+
4 rows in set (0.00 sec)
```


# EXPLAINの実行方法
EXPLAINの実行方法はとても簡単で、下記のように実行計画を見たいクエリの先頭に`EXPLAIN`をつけるだけです。

```sql:EXPLAINの実行 
--- 下記のクエリの実行計画を確認する
--- SELECT id FROM user WHERE id = 1;
mysql > EXPLAIN SELECT id FROM user WHERE id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   100   |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+

```

# EXPLAIN結果の見方
EXPLAINの目的・実行方法が分かったところで、EXPLAINの出力結果の意味を解説していきます。

## id
idはSELECT識別子で、実行順序を表します。

## table
クエリを発行しているテーブル。

## select_type
select_typeは、SELECTの種類を表します。
種類は以下の通りです。

#### SIMPLE
UNIONやサブクエリを使わない単純なSELECT。

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

#### PRIMARY
外部クエリのSELECT。
#### SUBQUERY
サブクエリ内の最初のSELECT。

```sql:PRIMARYとSUBQUERYの例
mysql> EXPLAIN SELECT * FROM employee WHERE id = (SELECT employee_id FROM message where id = 1);
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | SUBQUERY    | message  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
```
#### UNION
UNION内の2つ目以降のSELECT。

#### UNION RESULT
UNIONの結果。

```sql:UNIONとUNION_RESULTの例
mysql> EXPLAIN SELECT id as employee_id FROM employee WHERE created_at < '2021-05-06 15:20:00' UNION SELECT employee_id FROM message;
+----+--------------+------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type  | table      | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------+------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY      | employee   | NULL       | range | idx_employee_created_at | idx_employee_created_at | 5       | NULL |    1 |   100.00 | Using where; Using index |
|  2 | UNION        | message    | NULL       | index | NULL                    | idx_message_employee_id | 4       | NULL |    2 |   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内で相関関係のあるサブクエリが使用された場合、こちらに当てはまります。


#### DEPENDENT SUBQUERY
相関関係のあるサブクエリ。

#### DERIVED
FROM内で使用しているサブクエリ。

#### DEPENDENT DERIVED
別のテーブルに依存する導出テーブル。

#### MATERIALIZED
実体化されたサブクエリ

#### UNCACHEABLE SUBQUERY
キャッシュできず、外部クエリの行ごとに実行されるサブクエリ。

#### UNCACHEABLE UNION
`UNCACHEABLE SUBQUERY`のうち、UNION内の2つ目以降のSELECT。

## partitions
一致するパーティション。

## type
テーブルの結合方法。
種類は以下の通りです。

#### system
テーブルに行が1行しかありません。

#### const
**一致するレコードが最大で1つであり、非常に高速**です。
**プライマリキーとユニークキーを定数値と比較**する時に使用されます。

#### eq_ref
前のテーブルの行の組み合わせごとに、このテーブルから1行ずつ読み取られる。
**結合で**インデックスが使用されており、インデックスが**プライマリキーまたはユニークキー（NOT NULL）である場合に使用**されます。(つまりconstとの違いは、結合でインデックスが使用されていること。)
単一の行の絞り込みが可能であるため、system・constの次に高速で最適なtype。

#### ref
**プライマリキーまたはユニークキー以外のインデックスを用いて等価検索（=または<=>）を行った場合に使用**されます。
単一の行の絞り込みはできませんが、使用されているキーが数行の場合は適切な結合型です。

このカラムに`func`が格納されている場合、特定の関数や演算子の結果です。詳細を知りたい場合、EXPLAIN実行後に`SHOW WARNINGS;`を実行することで確認できます。


#### fulltext
FULLTEXTインデックスを使用しています。

#### index_merge
インデックスマージ最適化が行われています。

#### unique_subquery
**INサブクエリで**プライマリキーまたはユニークキーをSELECTする場合に使用されます。


#### index_subquery
**INサブクエリで**プライマリキーまたはユニークキー以外のカラムをSELECTする場合に使用されます。
#### range
インデックスを用いて特定の範囲の行を取得する場合に使用されます。
このtypeの`key`カラムは、使用されるインデックスを示し、`key_len`カラムには使用された最長のインデックスパートが格納されます。
このtypeの`ref`カラムはNULLになります。
`=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE または IN()`の演算子を用いてキーカラムを定数と比較する場合に使用されます。
#### index
indexは下記の2つの方法に分かれます。

- インデックスがクエリのカバリングインデックスで、インデックスツリーのみが参照される場合に使用されます。このとき、`Extra`カラムには`Using index`が格納されます。
- インデックスがカバリングインデックスでなく、フルテーブルスキャンが行われます。インデックスからの読み取りを使用して、インデックス順でデータ行をルックアップして実行されます。このとき、`Extra`カラムには`Uses index`が格納されます。

#### ALL
**インデックスが全く使用されていないフルテーブルスキャン**の場合に使用されます。
パフォーマンスがかなり悪いのでチューニングの必要があります。

## Possible keys
選択可能なインデックスが格納されます。

## keys
実際に使用されたインデックスが格納されます。

## rows
fetchされる行数の見積もり。
あくまでも見積もりなので、**実際にfetchされる行数とは異なります**。

## filtered
フィルタ処理される行数の割合。
最大値は100で、このときフィルタ処理されなかったことを表し、数字が小さくなるほどフィルタリングされた行が増加したことを示します。

## key_len
使用するキーの長さを示します。短いほうが速いです。

## ref
実際に使用されたインデックス（`keys`カラムに格納されたインデックス）と比較される値またはカラム。
値が指定されている場合は、constが格納されます。

## Extra
Extraカラムには、実行計画の追加情報が格納されます。
特によく見る種類だけ書いておきます。

#### Using where
WHERE句で検索条件が指定されており、インデックスだけではその検索条件を適用できない場合に使用されます。

#### Using filesort
クイックソートが使用されています。

#### Using index
上でも記載の通り、`type`カラムがindexでインデックスがカバリングインデックスの場合に使用されます。

#### Uses index
上でも記載の通り、`type`カラムがindexでインデックスがカバリングインデックスでない場合に使用されます。
    
# 参考サイト
[EXPLAIN出力フォーマット](https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html#explain-join-types)
[MySQLのEXPLAINを徹底解説!!](http://nippondanji.blogspot.com/2009/03/mysqlexplain.html)