DBMSと実行計画

# はじめに
最近ISUCONに参加するべく、いろんな高速化技術について勉強しています。
今回は実行計画ついて学んだので、簡単ですが紹介したいと思います。

インデックスについては[SQLチューニングとインデックス](https://qiita.com/hot_study_man/items/38227fddb21a9648a400)にまとめました

# 結論
* 負荷の高いSQLが発生したときに最初に調べるべき対象は`実行計画`(データのアクセス方法)
* 実行計画をみて、どういったデータのアクセス方法なのかを確認してSQLを改善する
  - 狙い通りインデックスが使われているか
  - どれくらいの行を読み込んでいるか　など

# DBMSのクエリ処理の流れ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273424/71551d56-054e-812f-4748-59966e4c217a.png)
引用:[クエリ評価エンジンと実行計画―“シェフおまかせ”はいつも美味しいのか（1）](https://gihyo.jp/dev/serial/01/db-academy/000401)

* パーサ
  - `構文解析`
  - SQLが構文的に正しいかの整合性チェック 
* オプティマイザ
  - 「最適」なデータアクセスの方法（`実行計画`）が決定する
  - インデックス有無、データの分散や偏りの度合い、DBMSの内部パラメータなどの条件を考慮して、選択可能な多くの実行計画を作成を利用するかどうかもオプティマイザが決定する
  - 実行計画を作成する際に、④のカタログマネージャーを参照する
  - ②でいくつかの実行計画を作成して、③で一番低コストな実行計画を選択する
* カタログマネージャ
  - DBMSの内部情報を集めたもの、テーブル、インデックスの統計情報が格納
* プラン評価
  - オプティマイザが1つの実行計画に絞り込んだら、それを受け取って`実行`
  - 実行計画は人が読める形になっている
 

# 注意事項
* テーブルに対してデータの挿入・更新・削除が行われたのにカタログ情報が更新されていないと、オプティマイザは古い情報をもとに実行計画を作ろうとして、最適なプランが選ばれないことがある
* て０ブルのデータが大きく更新されたらカタログの統計情報もセットで更新するべき
* 統計情報が最新でも、SQLが複雑すぎて、構成がよくなくて、オプティマイザが最適なアクセスパスを生成できないこともあります。SQLの遅延が発生したとき、最初に調べるべき対象は実行計画

# 実行計画の見方
* 実行計画とは
  - explain を使うことで該当SQLがインデックスを使うのかや、どうやってテーブルスキャンしているかなどがわかる


# 題材
[private-isu](https://github.com/catatsuy/private-isu)にでてきた遅いSQLの実行計画をみてみました。

# 遅いsqlをexplainしてみた
8系を使用

```zsh
$ mysql --version
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on aarch64 ((Ubuntu))
```
commentsテーブルが10万行近く
```sql
mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'isuconp';
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| comments   |      99633 |
| posts      |       9396 |
| users      |       1000 |
+------------+------------+
```

テーブル定義

```sql
mysql> desc comments;
+------------+-----------+------+-----+-------------------+-------------------+
| Field      | Type      | Null | Key | Default           | Extra             |
+------------+-----------+------+-----+-------------------+-------------------+
| id         | int       | NO   | PRI | NULL              | auto_increment    |
| post_id    | int       | NO   |     | NULL              |                   |
| user_id    | int       | NO   |     | NULL              |                   |
| comment    | text      | NO   |     | NULL              |                   |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-----------+------+-----+-------------------+-------------------+
```
インデックスは自動生成される主キーのみ

```sql
mysql> show index from comments\G;
*************************** 1. row ***************************
        Table: comments
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 99633
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)
```

## explain実行
スロークエリログから性能が悪いSQLを見つけ出します。今回は下記SQLが対象になります。explainを使用して見つけ出さした性能の悪いSQLの実行計画をみていく。

```sql
mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ALL     
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99633
     filtered: 10.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
```

[Mysql8.0 explain output](https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html)
[MySQLのEXPLAINを徹底解説!!](http://nippondanji.blogspot.com/2009/03/mysqlexplain.html)

* type: ALL
  - フルスキャン
* rows: 99633
  - フルスキャンなのでテーブルの全行を読み取る
* key: NULL
  - インデックス利用なし
* Extra: Using where
  -  WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示
* Extra: Using filesort
  - ソート処理が実行されている。ソート処理重い処理なので、基本的には`Using filesort`は出力させないほうがよさそう

**10万行近いテーブルをフルスキャンしているので、SQLの改善が必要だということがわかる**

## インデックス追加後のexplain
* SLQ自体はシンプルなので問題なし。commentsテーブルには主キー以外のインデックスがなかったので、インデックスによる効果は大きそう。

* where条件の`post_id`にインデックス追加
  - post_idのカーディナリティは問題ない
  - [カーディナリティについてまとめてみた](https://qiita.com/soyanchu/items/034be19a2e3cb87b2efb)

```sql
mysql> alter table comments add index index_comments_on_post_id(post_id);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

インデックス追加後のexplain
```sql
mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ref
possible_keys: index_comments_on_post_id
          key: index_comments_on_post_id
      key_len: 4
          ref: const
         rows: 9
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
```

* type: ref
  - インデックスを使った検索
* possible_keys
  - 使えるインデックスの候補
* key
  - 使ったインデックス
* rows
  - 9行の読み取り
* Extra: Using filesort
  - ソート処理が実行されている

狙った通りにインデックスを利用できていて、読み取りも9行のみにとどまり改善できているように見える。

ただ、`Using filesort`が出力されているので、まだ改善の余地がありそう。
ソート処理は重い処理なので、ソートを使わないようにすると高速化できそう。

## マルチカラムインデックス作成してみる
`Extra: Backward index scan`を避けるためdescを指定

```sql
mysql> alter table comments add index index_comments_on_multi_post_id_created_at(post_id, created_at desc);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
```sql
mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ref
possible_keys: index_comments_on_multi_post_id_created_at
          key: index_comments_on_multi_post_id_created_at
      key_len: 4
          ref: const
         rows: 9
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)
```

* フルスキャンなし、ファイルソートなし。狙った通りにマルチカラムインデックスを利用できている
* `Extra: NULL`　問題になりそうな指摘もなし

# まとめ
* まず、スロークエリから遅いSQLを見つけ出します。次に実行計画を見てデータアクセス方法を確認します。最後にSQLをチューニング(今回はインデックス利用)します。
* 実行計画をみて遅いSQLを高速にすることができました