MySQL 5.7で学ぶMySQLの最適化2 (コスト計算と統計情報)

[前回](https://qiita.com/MariMurotani/items/22fb22a8180aad1176fa)はMySQLのオプティマイザの挙動について詳しく学びましたが、今回はオプティマイザが動作するにあたって必要な情報について確認していきます。
MySQL 5.7から新たに、server_cost、engine_costというスキーマが導入されてオプティマイザがクエリの実行計画をコスト計算をもとに作成するような仕組みになっています。
今回は、MySQL8の内容にも触れながら学んでいきます。
MySQL8の内容についてはとくに下記の書籍が参考になりますので、ぜひ、手に取ってみてください。

- [MySQL 8 Query Performance Tuning](https://www.amazon.co.jp/-/en/Jesper-Wisborg-Krogh/dp/1484255836/)

# 1. コスト算出方法
MySQLのオプティマイザはコストモデルというのを利用して、実行計画を作成します。
実行計画とは、受け取ったSQLを最適化するプロセスのことです。綿密なコスト計算に基づいた複数のプランを算出し、その中から一番コストが低いものが最適化案として採用されます。
見積もり算出のために、server_cost、engine_costというテーブルが用意されています。5.7ではデフォルト値はオンコーディングで実装されていますが、8.1では、カスタマイズ可能なテーブルとして用意されています。

https://dev.mysql.com/doc/refman/8.0/ja/cost-model.html

## エンジンコスト(server_cost)
エンジンコストのパラメータは2種類のみで、ディスクから読みとる場合とメモリから読み取る場合になります。
現在、スペースリーでは、Amazon Auroraを利用していますが、そちらの環境での最適化などを提案している文献が見つからないので、深くは追求しません。

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

| engine_name | device_type | cost_name  | cost_value | 初期値 | 意味 |
|:-----------------|:------------------|:------------------|:------------------|:------------------|:------------------|
| default     |           0 | io_block_read_cost     |       NULL | 40.0  | ディスクから読み取る場合のコスト |
| default     |           0 | memory_block_read_cost |       NULL | 2.0 | メモリから読み取る場合のコスト |

## サーバーコスト(server_cost)
こちらのコストはクエリ実行計画の作成に大きなインパクトを与えます。
行数と掛け合わせて最終的なコストの見積もりをするためです。

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

[コストパラメーターの説明](https://dev.mysql.com/doc/refman/8.0/ja/cost-model.html)
[初期値](https://github.com/mysql/mysql-server/blob/7ed30a748964c009d4909cb8b4b22036ebdef239/mysql-test/t/opt_costmodel.test#L7) 

| cost_name | cost_value | 初期値 | 意味 |
|:-----------------|:------------------|:------------------|:------------------|
| disk_temptable_create_cost  | NULL | 20 | <b>(MyIsamやInnoDBの内部的な)のテンポラリテーブルの作成コスト</b><br>ディスクへの書き込みが発生する |
| disk_temptable_row_cost | NULL | 0.5 | 上記の行単位コスト |
| key_compare_cost |  0.1  | 0.05 | <b>レコードキーを比較するコスト</b><br> この値を増やすと、多数のキーを比較するクエリー計画のコストが高くなり、インデックスソート < ファイルソートとなりえる |
| memory_temptable_create_cost | NULL | 1.0 | <b>インメモリのテンポラリテーブルの作成コスト</b><br>disk_temptable_row_costよりもコストが低いため、オプティマイザが選択しがち |
| memory_temptable_row_cost | NULL | 0.1  | 上記の行単位コスト |
| row_evaluate_cost |  NULL | 0.1  |  <b>行を評価するための一般的なコスト</b><br>この値を増やすと、多数の行を調査するクエリー計画が、調査する行数が少ないクエリー計画よりもコストが高くなり、 少量のレンジスキャン< テーブルスキャンとなりえる |

- 複数のテーブルを結合してSELECTする複雑なクエリの場合には、各テーブルへのアクセスコストのみならず、結合順序と利用されるインデックスも考慮する必要があります
-  `OUTER STRAIGHT JOIN`の場合には、結合順序は固定ですが、`INNER JOIN`の場合にはオプティマイザがコストを元に選択します
-  テーブルの組み合わせは、`N!`(Nの階乗)となることに注意が必要です
    -  5つのテーブルを結合する場合の組み合わせは、5 * 4* 3* 2 * 1 = `120通り`
-  MySQLは61テーブルまでJOINすることが可能ですが、 検討するプランの組み合わせが多くなり過ぎた場合に、見積もりの時間がかかりすぎる事があるので、注意が必要です
    -  `optimizer_prune_lebel`や`optimizer_search_depth`で刈り込みの設定をすることができます

# 2. インデックスダイブと統計情報
MySQLにはインデックスダイブという機能があり、range (範囲)スキャンの場合に、オプティマイザーがその範囲に含まれる行数を正確に見積もるための仕組みです。
[eq_range_index_dive_limit](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_eq_range_index_dive_limit)という変数に閾値が格納されていて、その範囲内であれば、インデックスダイブを利用、範囲外であればインデックス統計を利用するという仕組みなっています。
オプティマイザがコストを算出するために行数を数えるときに必要になります。

-  rangeスキャンとは、INやOR句にIDを直接指定したり、不等号記号(<,>=....)やBETWEENなどの句で指定された場合の最適化のこと
-  インデックスダイブを利用するということは、 [handler::records_in_range](https://dev.mysql.com/doc/internals/en/records-in-range.html)を利用するかどうかということと同じ意味です

```sql
mysql> show variables like 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.00 sec)
```


# 3. 統計情報
統計情報は、`mysql.innodb_table_stats`、`mysql.innodb_index_stats`というテーブルに格納されています。

### テーブルごとの統計情報
![tablestats.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89997/311481b2-e6dd-02e4-62ab-459f7476fb0e.png)

### インデックスごとの統計情報
![indexstats.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89997/702b538d-d090-3de8-5fc9-ea24e30d061f.png)


デフォルトでは、`innodb_stats_auto_recalc` というパラメーターがONになっており、全体の10%が更新されたら更新されます。
頻繁に統計が再計算される可能性があり、これにより、クエリーの実行計画が変化する可能性があります。
ローカルでクエリの実行計画をデバックする際には、常に最新化されている事を意識したいです。
[Analyze Table](https://dev.mysql.com/doc/refman/5.6/ja/analyze-table.html)コマンドの実行で即時に更新することができます。
その場合には、頻繁に統計情報を再計算することでサーバーへの負荷が高くなるので注意が必要です。


[innodb_stats_persistent](https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_stats_persistent)を利用することで再起動しても統計情報は消えなくなります。

```sql
mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
```

[MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.8.10.1 永続的オプティマイザ統計のパラメータの構成]
(https://dev.mysql.com/doc/refman/8.0/ja/innodb-persistent-stats.html)

また、テーブルごとに永続化をする方法もあります。
その場合、書き込み時に負荷がかかるので必要なテーブルに限定することをおすすめします。

```sql
ALTER TABLE projects STATS_PERSISTENT=1, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;
```

#### STATS_PERSISTENTのパラメーター一覧
| キー | 初期値 | 意味 |
|:------------------|:------------------|:------------------|
| STATS_PERSISTENT | 1 | 永続化を有効 | 
| STATS_SAMPLE_PAGES | 20 | サンプリングされるページのデフォルト数を20に設定 | 
| STATS_AUTO_RECALC | 11 | テーブルに対する大幅な変更後に統計を自動的に更新するかどうかを制御 |

-  STATS_SAMPLE_PAGESで指定するページ数とは、[Bツリーインデックス](https://dev.mysql.com/doc/refman/5.6/ja/index-btree-hash.html) のリーフの数です
   -  [SQLアンチパターンとBtreeインデックスの関連性](https://devblog.thebase.in/entry/2018/12/09/110000)の説明が分かりやすいのでこちらをご覧ください
-  下記の事象が発生したときのみ、`STATS_SAMPLE_PAGES`のチューニングが必要になります
  1. `ANALYZE TABLE`が遅すぎる
  2. 統計の精度が十分でないため、オプティマイザが次善の計画を選択する
  `show indexes from TABLE_NAME;`で表示されるインデックスごとの実際の`カーディナリティ`を比較して確認をします。
   `stat_name`が`n_diff_pfx${NN}`の場合の`stat_value`がカーディナリティを示します。

![statspersist.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89997/b639197e-ea5d-8b46-85e5-7e0e507290ad.png)

-  また、InnoDBではしばしフラグメンテーションが発生する場合があるので、[Optimize Table](https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html)を実行することで、インデックスデータの物理ストレージを再編成して、性能を向上できます

# 4. hash_joinとヒストグラム
[前回](https://qiita.com/MariMurotani/items/22fb22a8180aad1176fa)の学習でも触れましたが、
MySQL8では、オプティマイザに新たに、`hash_join`が導入されました。
[hash_join](https://dev.mysql.com/doc/refman/8.0/ja/hash-joins.html)はインデックスがない場合に、hash_keyを作成してそれをもとに結合を行います。
その際にオプティマイザが使用する統計情報として新たに[ヒストグラム](https://dev.mysql.com/doc/refman/8.0/ja/optimizer-statistics.html)が導入されました。
ここでいう[ヒストグラム](https://ja.wikipedia.org/wiki/%E3%83%92%E3%82%B9%E3%83%88%E3%82%B0%E3%83%A9%E3%83%A0)とは言わずとしれたヒストグラムの事で、階級ごとに度数を保持する統計情報です。

- `hash_join`結合において、indexと似たような働きをします。
- joinの結合順序を最適化するのに利用されます。
-  上述の、`mysql.innodb_table_stats`と同様に`Analyze Table`の実行で最新化する事ができます。
-  `optimizer_switch`変数の`condition_fanout_filter`をOFFにすることで無効化することもできます。

-------
本記事は、社内でのデーターベース勉強会のために作成した教材資料になります。
[Spacelyのブ・ロ・グサイト](https://tech.spacely.co.jp/)でもご覧になれます。
ご興味のある方は、他にも記事が掲載されているので、ご覧になってください。
ご指摘などございましたら、コメントいただけると幸いでございます。