MySQLのEXPLAIN（実行プラン）まとめ

MySQLのEXPLAIN（実行プラン）について、まとめます。

EXPLAINは、クエリがどのように実行されるかを確認できます。

例えば、昨日まで1sだったクエリが、今日は10sかかる。という経験はないでしょうか。
EXPLAINを確認すると、意図しないIndexが使われいて、遅くなっていた。ということがあります。

# はじめに

MySQL 5.6.3以降では、EXPLAIN に使用できる説明可能なステートメントは、SELECT、DELETE、INSERT、REPLACE、UPDATE です。MySQL 5.6.3より前では、SELECT が唯一の説明可能なステートメントです。

今回は、MySQLのバージョンは5.7で試します。

# EXPLAIN確認方法
クエリの先頭に `EXPLAIN` をつけて実行します。

```sql
EXPLAIN SELECT * FROM users WHERE id = 1
```

**結果**

Keyが `PRIMARY` のため、`users` テーブルのプライマリキーを使ったことがわかります。

```
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
```

**各項目の説明**

|  項目  |  内容  |
| ---- | ---- |
|  id  |  実行順序  |
|  select_type  |  SIMPLE: 単純な SELECT (UNION やサブクエリーを使用しません)<br>PRIMARY: もっとも外側の SELECT<br>UNION: UNION 内の 2 つめ以降の SELECT ステートメント<br>DEPENDENT: UNION 内の 2 つめ以降の SELECT ステートメントで、外側のクエリーに依存します<br>UNION RESULT: UNION の結果。<br>SUBQUERY: サブクエリー内の最初の SELECT<br>DEPENDENT SUBQUERY: サブクエリー内の最初の SELECT で、外側のクエリーに依存します<br>DERIVED: 派生テーブル SELECT (FROM 句内のサブクエリー)<br>MATERIALIZED: 実体化されたサブクエリー<br>UNCACHEABLE SUBQUERY: 結果をキャッシュできず、外側のクエリーの行ごとに再評価される必要があるサブクエリー<br>UNCACHEABLE UNION: キャッシュ不可能なサブクエリーに属する UNION 内の 2 つめ以降の SELECT  |
|  table  |  対象テーブル  |
|  partitions  |  テーブルパーティション  |
|  type  |  system: テーブルに1行しかない<br>const: プライマリキー、ユニークキーのルックアップによるアクセス<br>eq_ref: joinにおいてのconstと同義<br>ref: constでないインデックスを使って等価検索<br>range: indexを用いた範囲検索<br>index: フルインデックススキャン<br>ALL: フルテーブルスキャン<br>fulltext: FULLTEXTインデックスによる検索<br>ref_or_null: refに追加でNULL値でも検索する<br>index_merge: インデックスマージ最適化を使用<br> unique_subquery: 効率化のため、サブクエリーを完全に置き換える単なるインデックスルックアップ関数<br>index_subquery: 働きは、unique_subqueryと同様。サブクエリー内の一意でないインデックスに対して機能する  |
|  possible_keys  |  optimizerがテーブルのアクセスに利用可能だと判断したインデックス  |
|  key  |  実際にoptimizerによって使用されたキー  |
|  key_len  |  選択されたキーの長さ |
|  ref  |  定数の場合: const<br>JOINを使用している場合: 結合する相手側のテーブルで検索条件として利用されているカラムが表示される |
|  rows  |  対象テーブルから取得される行の見積もり  |
|  filtered  |  テーブル条件によってフィルタ処理される行の推定の割合  |
|  Extra  |  optimizerがどのような戦略を立てたかを知ることが出来る  |

typeがALL（テーブルフルスキャン）または、index（フルインデックススキャン）は、注意が必要です。
データ量が少ないテーブルであれば問題ありませんが、データ量が多いテーブルでフルスキャンをしてしまうと、パフォーマンスに影響します。（例えば、1件対象データを探す場合でも、フルスキャンした場合は、テーブル内の全データから探そうとします）

参考
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types


# バインド変数を使ったクエリのEXPLAIN
バインド変数（プレースホルダ）次のように確認します。

```sql
SET @num=1;
EXPLAIN SELECT * FROM users WHERE id = @num;
```

バインド変数を使った場合と、直接リテラルを指定するのとで、EXPLAIN結果に差がでる場合があります。
実際と同じクエリでEXPLAINを実行してください。

参考：https://teratail.com/questions/299162

# 実行中のクエリを調べてEXPLAIN
実行中のクエリは、show processlistで確認できます。

```sql
SHOW PROCESSLIST;

+----+--------+------------------+--------+---------+------+----------+------------------+
| Id | User   | Host             | db     | Command | Time | State    | Info             |
+----+--------+------------------+--------+---------+------+----------+------------------+
|  1 | root   | 172.0.0.1:62456  | NULL   | Query   |    0 | starting | show processlist |
+----+--------+------------------+--------+---------+------+----------+------------------+
3 rows in set (0.01 sec)
```

Infoにクエリが表示されます。ただし、全文表示されない場合があります。
その場合は、show full processlistを実行してください。

```sql
SHOW FULL PROCESSLIST\G
```

クエリが表示されたらEXPLAINEで確認できます。

5.7からはEXPLAIN FOR CONNECTIONが使えるようになりました。PROCESSLISTのIdを指定するだけで、EXPLAINEが取得できます。また、自分自身のプロセスに対してEXPLAIN FOR CONNECTIONを発行してしまうと無限LOOPのような挙動になります（無限にコネクションが増えていく）。指定するIdを間違えないように注意してください。

```sql
EXPLAIN FOR CONNECTION <Id>
```


# 統計情報
EXPLAINはmysqlのオプティマイザにより生成されます。
オプティマイザは、クエリを解析して、最適な実行プランを立ててくれます。問い合わせの最適化を行う機能です。
では、どのようにオプティマイザは考えるのでしょうか。

一つはDBに保存されている統計情報を元に考えています。
テーブルやインデックスに、どのようなデータが入っているか。の統計です。

テーブルの統計情報

```sql
select * from mysql.innodb_table_stats;
```

```sql
+---------------+----------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name           | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+----------------------+---------------------+--------+----------------------+--------------------------+
| test          | users                | 2021-02-07 10:41:28 |      0 |                    1 |                        0 |
+---------------+----------------------+---------------------+--------+----------------------+--------------------------+
```

 - last_updateは、最後に行を更新した日付（統計を取り直した日付）
 - n_rowsは、テーブル内の行数
 - clustered_index_sizeは、プライマリキーのサイズ（ページ数）
 - sum_of_other_index_sizesは、プライマリキー以外のインデックスのサイズ（ページ数）

**補足**
テーブルの件数とn_rowsが合わないことがあります。n_rowsは統計情報を更新したときのテーブル件数です。
例えば、1月1日にn_rowsが10だとして、1月2日に3件登録があり、合計13件になったとします。この場合、統計情報が更新されなければ、n_rowsは10のままです。更新されると13になります。


インデックスの統計情報

```sql
select * from mysql.innodb_index_stats;
```

```sql
+---------------+----------------------+-------------------------------------------+---------------------+--------------+------------+-------------+------------------------------------+
| database_name | table_name           | index_name                                | last_update         | stat_name    | stat_value | sample_size | stat_description                   |
+---------------+----------------------+-------------------------------------------+---------------------+--------------+------------+-------------+------------------------------------+
| test          | users                | PRIMARY                                   | 2021-02-07 10:41:28 | n_diff_pfx01 |          0 |           1 | id                                 |
| test          | users                | PRIMARY                                   | 2021-02-07 10:41:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index  |
| test          | users                | PRIMARY                                   | 2021-02-07 10:41:28 | size         |          1 |        NULL | Number of pages in the index       |
+---------------+----------------------+-------------------------------------------+---------------------+--------------+------------+-------------+------------------------------------+
```

 - last_updateは、最後に行を更新した日付（統計を取り直した日付）
 - stat_nameは、stat_value カラムに値がレポートされている統計の名前
 - stat_valueは、stat_name カラムで名前が指定されている統計の値
 - sample_sizeは、stat_value カラムに示されている推定値のサンプリングされるページの数
 - stat_descriptionは、stat_name カラムで名前が指定されている統計の説明

## stat_name、stat_value
stat_name、stat_valueが何かわかりにくいので、少し詳しくみていきます。

### size
stat_name=sizeである場合、stat_value カラムには、インデックス内のページの総数が表示されます。DBはページという単位でデータが格納されます。つまり、ページが多いほどインデックスの容量が大きくなります。今回は1件しか入っていないテーブルなので、「1」と表示されています。

### n_leaf_pages
stat_name=n_leaf_pages である場合、stat_value カラムには、インデックス内のリーフページの数が表示されます。
インデックスは下図のように上からデータをたどる構造になっています。末端のことをリーフといいます。（下図だとA,C,E,H）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/0c54ed1c-b8f8-51bf-66cd-2cde8db59036.png)

出典: https://ja.wikipedia.org/wiki/%E6%9C%A8%E6%A7%8B%E9%80%A0_(%E3%83%87%E3%83%BC%E3%82%BF%E6%A7%8B%E9%80%A0)

### n_diff_pfxNN

stat_name=n_diff_pfx01である場合、stat_descriptionに書かれているカラムのデータの種類（カーディナリティ）がstat_valueカラムに入ります。
例えば

 - id:1だけテーブルに登録されていた場合、stat_valueは、1
 - id:1〜5の5件がテーブルに登録されていた場合、stat_valueは、5
 - id:1が5件テーブルに登録されていた場合、stat_valueは、1　（※idを重複ありのインデックスと考えた場合）

さらに、n_diff_pfx01がn_diff_pfx02、n_diff_pfx03と連番で登録されることがあります。
これは複数カラムを一つのインデックスにした場合です。

例えば、first_nameとlast_nameを一つのインデックスにしたカラムがあったとします。

```
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| 鈴木        | 太郎      |
| 鈴木        | 次郎      |
+------------+-----------+

```

n_diff_pfx01とn_diff_pfx02の統計情報が作成されます。
n_diff_pfx01のstat_descriptionには、first_name。
n_diff_pfx02のstat_descriptionには、first_name、last_nameと登録されます。

n_diff_pfx01のstat_valueは鈴木で「1」です。
n_diff_pfx012stat_valueは（鈴木、太郎）、（鈴木、次郎）で「2」です。


## sample_size
sample_sizeは、統計情報作成にあたり、どの程度データを調べるかです。
100ページあるデータの、1ページだけを見て統計を作成するのか、50ページ見て統計を作成するのかでは、精度が違います。
たくさんのページを見た方が精度はあがりますが、DBの時間とリソースを使います。



参考
https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html


## 統計情報の更新タイミング

テーブル内のデータが大幅に変更されたあとは、InnoDB によって自動的に永続的統計が再計算されます。現在(5.6以降)のしきい値は、テーブル内の行の 10% です。

**統計情報が更新される理由**
例えば、稼働当初、「鈴木」というデータが10件登録されていました。
統計情報では、n_diff_pfx01のstat_valueは1と登録されます。一ヶ月後、たくさんの人が登録し名字の種類が佐藤、斎藤など、100種類に増えました。統計情報が更新されないと、DBは「鈴木」の1l種類しか存在しないと考え、1種類を探すのに最適な実行をします。しかし、実際には100種類あるので、DBが考えたプランは最適ではないのです。

これにより、インデックスを使ってほしいのに、使われない。スキャンして欲しいのにフルスキャンしてしまう。ということが発生します。更新されるテーブルに対しては定期的に統計情報更新が必要です。

参考
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_stats_auto_recalc


## 手動での統計情報更新
統計情報を手動で更新できます。例えば、1億件あるテーブルは、1千万件データが更新されないと、統計情報が自動で再取得されません。実際には300万更新されたタイミングで取得したい場合などがあります。

```
ANALYZE TABLE <テーブル名>;
```

# オプティマイザトレース
ここまでで、実行プランの確認方法、統計情報やオプティマイザの役割について説明しました。
さらに踏み込んで、どのように実行プランが作成されるのかを確認してみます。

オプティマイザが考えた実行プラン生成の詳細を取得してみます。

手順は4つ
 - オプティマイザトレースを有効化する
 - クエリを実行する
 - トレースを表示する
 - トレースを無効化にする

まず、トレースを有効にします。

```sql
SET optimizer_trace="enabled=on";  
``` 

クエリを実行します。

```sql
select * from users where id = 1;  
``` 

トレースを表示します。

```sql
SELECT * FROM information_schema.optimizer_trace\G 
```
※結果が長いので後述

トレースの無効化

```sql
SET optimizer_trace="enabled=off";  
```

## トレース結果
EXPLAINをより詳細にした結果が得られました。

```sql
*************************** 1. row ***************************
                            QUERY: select * from test.users where id = 1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `test`.`users`.`id` AS `id`,`test`.`users`.`name` AS `name` from `test`.`users` where (`test`.`users`.`id` = 1)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`test`.`users`.`id` = 1)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, `test`.`users`.`id`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, `test`.`users`.`id`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, `test`.`users`.`id`)"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`test`.`users`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`test`.`users`",
                "field": "id",
                "equals": "1",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`test`.`users`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
          {
            "condition_on_constant_tables": "1",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "1",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
              ]
            }
          },
          {
            "refine_plan": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
```

以上になります。