EXPLAINを使ってクエリ解析

## MySQLのクエリ解析をするまでの手順備忘録
EXPLAINを使ったクエリ解析の手順を浅くサクッと解説します。

### EXPLAINとは
[公式](https://dev.mysql.com/doc/refman/8.0/ja/using-explain.html)では以下のように説明されています。
にないｒま
> クエリー実行計画 (つまり、MySQL がクエリーをどのように実行するかの説明) を取得するために使用されます。

要はEXPLAINを使うことでクエリがどれくらい効率的かを確認することができるということです。

### EXPLAINによる解析
##### 前準備
以下のような人を一覧管理するuserテーブルがあるとします。
カラムは`id`と`name`の2つだけで超シンプル。
また、テスト用データとしてこのテーブルに3万件ほどデータを入れている状態となります。

```mysql
mysql> show columns from user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
```

このテーブルのインデックスを見てみるとプライマリーキーであるidが追加されていることが分かります。

```mysql
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |       10051 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
```

この状況でインデックスがはられていないカラム`name`に対してSELECT文を実行してみます。
クエリ自体は取得したレコードを降順にして上位100件を出力する単純なものとなります。
```
mysql> SELECT * FROM user ORDER BY name DESC LIMIT 100;
+------+----------+
| id   | name     |
+------+----------+
| 9999 | Test9999 |
| 9998 | Test9998 |
| 9997 | Test9997 |
以下省略
```

まぁ値は取得できしたね。

##### クエリ解析
ただ上記クエリが効率的にどうなのかはこれだけでは分かりません。
そこで出てくるのが`EXPLAIN`になります。
先ほどのSELECT文にEXPLAINを付けて実行してみましょう。

```
mysql> EXPLAIN SELECT * FROM user ORDER BY name DESC LIMIT 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 29694 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
```

また色々と出力されました。
各カラムがどういったことを表しているのかはこちらの[公式](https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html)を参照してください。

ここでは例として`rows`に着目します。ここにはテーブルから取得される行数の見積もりが表示されます。

:::note warn
今回のクエリは100件のデータを取得できれば良いのに対し、**実際には3万件近くのデータが取得されている**ことが分かります。
これでは非効率と言わざるを得ません。
:::

では、次に`user`テーブルの`name`カラムに対してインデックスを作りましょう。
```
mysql> ALTER TABLE user ADD INDEX name_index (name);
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY    |            1 | id          | A         |       10051 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user  |          1 | name_index |            1 | name        | A         |       10000 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
```

この状態でもう一度`EXPLAIN`をかけていきます。
```
mysql> EXPLAIN SELECT * FROM user ORDER BY name DESC LIMIT 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | name_index | 43      | NULL |  100 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)
```

ここでまた、`rows`に着目してください。

:::note info
取得見積もり件数が100件のみとなっており、最低限のアクセスに抑えることができています。
:::

##### インデックス作成前と後の違い
`EXPLAIN`によって出力された結果をもう少し見ていきましょう。

| インデックス | type | key | rows |
|:-:|:-:|:-:|:-:|
| 無 | ALL | NULL | 29694 |
| 有 | index | name_index | 100.00 |

インデックス作成前であればtypeは「ALL」、keyは「NULL」となっていました。
これはインデックスが使われずにフルテーブルスキャンが行われていることになります。
テーブルからの行の取得を可能にするインデックスを追加することで、ALL を回避できます。

インデックス作成後であればtypeは「index」、keyは「name_index」となっています。
これらより正しくインデックスが使用され効率的にクエリが実行されていることが分かります。

### まとめ
以前書いた記事（[スロークエリの見つけ方](https://qiita.com/eiji-noguchi/items/7188da49be9372a2ee10)）と今回の`EXPLAIN`を組み合わせることによりクエリの最適化ができるようになります。
深掘ればもっと細かくクエリに関する情報を確認できるみたいですが、気になる方は公式サイト等をぜひ一読してみてください。