MySQLでレスポンスが遅い場合にWorkBenchのVisual EXPALNを確認しながらSQLを改善していく

## 概要
普段MySQLを使用しているが、レスポンスが遅くなって調査することがある。
今回は70,000件のレコードから10件のレコードと結合テーブルを取得したい場合のSQLを調査して改修した内容を記載する。
基本的にはWorkBenchのVisual EXPALNを確認しながら解決していく。

### WorkBenchのVisual EXPALN
添付画像の赤色の線を囲った虫眼鏡マークのボタンをクリックすることで確認できる。
通常のEXPLAINよりも図があって見やすいため使用している。
![SnapCrab_NoName_2022-8-30_11-56-39_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1229062/73554beb-7b02-71fc-015f-e852fc876183.png)

## 環境
■ MySQLのバージョン
5.7.37
■ Workbenchのバージョン
6.3

## テーブル定義
- parents（親テーブル）
    - id（主キー）
    - index（indexを貼っているカラム）
    - created_at
- children（子テーブル）
    - id
    - parent_id（外部キー）

```ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin```

`parents`テーブルと`children`テーブルの関係性は「1対多」である。
`parents`テーブルには70,000件のレコードが存在しており、その中から10件のレコードを取得する。
※テーブル名は今回の記事に合わせて元々ある名称を分かりやすく変更したものであり、今後出てくるVisual EXPALNの画像では一部テーブル名を書き換えている。

## 発行しているSQL

`parents`テーブルとそれに紐づく`children`テーブルを10件取得したい。

```sql
SELECT
    `Parents`.`id`,
    `Parents`.`index`,
    `Parents`.`created_at`
FROM
    `parents` AS `Parents`
    LEFT OUTER JOIN `children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`
ORDER BY
    created_at desc
LIMIT
    0, 10;
```

### EXPLAIN
上記のSQLでEXPLAINを確認すると、トータルのQuery costが`106,317.60`かかっている。
また、取得したい`parents`テーブルのレコード数は10件であるが、FROM句内で75,000件ほど取得している。
![SnapCrab_NoName_2022-8-25_15-52-56_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1229062/2846d09f-be20-3adc-0e30-591d79608c91.png)


Query costが多くかかっている原因としては以下が考えられる。
①`parents`テーブルの取得時にフルテーブルスキャンを使用している。
②`parents`テーブルを取得する際に全てのレコードを取得していてcostがかかっている。それに伴いテーブル結合した`children`テーブルの取得にも90442.8のcostがかかっている。
③ORDER句を使用時にfilesortになっている。

## フルテーブルスキャンの回避
### フルテーブルスキャンとは
指定された票の全てのデータにアクセスするためデータの取得に時間がかかる。
https://dev.mysql.com/doc/refman/5.6/ja/how-to-avoid-table-scan.html

フルテーブルスキャンを回避する方法は上記サイトに複数記載されているが、今回はindexが設定されたカラムに対してWHERE句を指定して解決する。
本来WHERE句は指定したい条件がある場合に用いるが、今回は「indexが設定されたカラムに対してWHERE句を指定する」ことでフルテーブルスキャンが回避されることを記載したいと思い、上記の解決方法を選択している。
WHERE句を用いる必要がない場合は添付しているMySQL公式に記載されているいくつかの方法で解決していくことが望ましい。
なお、今回の最重要のボトルネックは後述する「取得コードが多すぎる」ことにあるため、必ずしもフルテーブルスキャンを回避する必要はない。

### 解決方法
`parents`テーブルの`index`カラムにindexを付与しているのでWHERE句で指定してみる。

```SQL
SELECT
    `Parents`.`id`,
    `Parents`.`index`,
    `Parents`.`created_at`
FROM
    `parents` AS `Parents`
    LEFT OUTER JOIN `children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`
-- 追加
WHERE
    `Parents`.`index` = [任意の値]
ORDER BY
    created_at desc
LIMIT
    0, 10;
```

#### EXPLAIN
indexを貼っている`index`カラムをWHERE句で指定することで、Query costが減少していることが分かる。（WHERE句で指定したため取得レコード数が減少したことも影響している）

![SnapCrab_NoName_2022-8-25_16-11-58_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1229062/01bbeb13-c81c-4990-b59f-6a95b72088bf.png)


■ `parents`テーブルの取得
15,874.8 → 99,939.8
■ `children`テーブルの取得
90,442.8 → 452,20.8
■ Query cost
106,317.60 → 55,160.60

## 取得コードを絞る
indexを貼ることでコストを現象させることができたが、依然として取得するレコード数も多くQuery costがかかっている。
現状だと、`parents`テーブルを全て取得し、`children`テーブルを結合させた上でORDER句を使用しているので時間がかかっている。

サブクエリを発行し、`parents`テーブルを必要なレコード数（今回だと10件)取得してから次の処理を実施するようにする。

```SQL
SELECT
    `Parents`.`id`,
    `Parents`.`index`,
    `Parents`.`created_at`
FROM
    (
        SELECT
            `Parents`.*
        FROM
            `parents` AS `Parents`
        WHERE
            `Parents`.`index` = [任意の値]
        ORDER BY
            created_at desc
        LIMIT
            0, 10
    ) AS `Parents`
   LEFT OUTER JOIN `children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`
WHERE
	`Parents`.`index` = [任意の値]
ORDER BY
    created_at desc;
```

サブクエリ内で`parents`テーブルを10件取得するように変更した。

#### EXPLAIN

![SnapCrab_NoName_2022-8-25_16-31-9_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1229062/c2f71ed7-4fc8-fc03-d186-9b1d4f7facc3.png)

■ `parents`テーブルの取得
15,874.8 → 99,939.8 → 2.4
■ `children`テーブルの取得
90,442.8 → 452,20.8 → 2
■ Query cost
106,317.60 → 55,160.60 → 4.80


取得するテーブル数を限定することで大幅にQuery costの削減が可能になった。
メインクエリの以下の構文はサブクエリでも記載しているため不要の様に思われるが、今回のテーブル構成だとサブクエリで取得した表をメインクエリで取得する際に、インデックスが付与されているカラムを指定しないので再度フルテーブルスキャンを使用する。
そのため、メインクエリでもWHERE句を使用している。

```sql
WHERE
	`Parents`.`index` = [任意の値]
```

## ORDER句のカラムにindexを貼る
### filesortとは
ソートに[sort_buffer_size](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_sort_buffer_size)以上のメモリ量が必要な場合に実行される。
メモリに入る単位でデータを切り出してクイックソートを行う。
その際にテンポラリファイルを使用する。
[MySQL公式](https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html)にソート時にテンポラリファイルを使用することが記載されている。（以下を参照）
>When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

私の環境の`sort_buffer_size`は以下である。

```SQL
SELECT @@GLOBAL.sort_buffer_size;

-- 結果
262144
```

### 解決方法

ORDER句で指定したいカラムにindexを貼る。
今回の場合はorder句で指定するカラムを`index`カラムをに変更して解消を行った。

### EXPLAIN
![SnapCrab_NoName_2022-8-25_17-3-27_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1229062/9c0908ee-3eaf-74d4-bd7b-11efd1be135b.png)

Query costは変わっていないが、filesortがEXPLAINからなくなっていることが確認できる。

## Nested Loop Join
3つほど解決方法を記載したが、最重要のボトルネックは取得レコードが多すぎたことである。
今回は[Nested Loop](https://dev.mysql.com/doc/refman/5.6/ja/nested-loop-joins.html)という結合アルゴリズムが採用されている。
MySQL公式にて、以下のNested Loop Joinのアルゴリズムに関して記載がある。

>単純な Nested Loop Join (NLJ) アルゴリズムは、ループ内の最初のテーブルから行を一度に 1 つずつ読み取り、各行を、結合の次のテーブルを処理するネストしたループに渡します。このプロセスは、結合するテーブルが残っている回数だけ繰り返されます。

上記により、駆動表もしくは内部表のレコード数が多ければ多いほどテーブルの結合にコストがかかってしまう。
今回の場合だと、70,000件以上のレコードが駆動表にあったためテーブルの結合にコストがかかりレスポンスが遅くなっていた。

また、Nested Loop Joinのアルゴリズムは以下である。

```
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
}
```

上記を見ると、テーブル結合順番は大きくコストに関わることが分かる。
今回の結合テーブルは1つであったが、複数のテーブルを結合する際はレコード数の多いテーブルは後ろ（上記アルゴリズムの内側）に格納することも効率化につながる。