オプティマイザ
オプティマイザとは
最適化「式の等価な変換」によって得られる複数の実行計画から、最適なものを選択する
ex) 内部結合を入れ替える, where 句の条件を入れ替える, サブクエリを別のアルゴリズムに置き換える...
コストベース もっともコストが低くなると見積もれるものを選択する
実行の流れ
エグゼキュータの仕事量は、クエリ内容とオプティマイザの能力によって決まる。
https://gyazo.com/fe2f217205361aab8e8fa307b596de2c
MySQL 5.7 での更新
コストモデル
MySQL 5.6 まではコストの計算にはハードコーディングされた程数値が用いられていたが、5.7 からは係数が指定できるようになった。この変更の背景には、ハードウェアの多様性がある。潤沢なメモリ、CPU、IOPS 等のリソースがある環境や、Raspberry Pi のような性能の低い環境まで存在し、その際にコストの係数が同一だとコストを見積もることが困難になる。
mysql.server_cost
ストレージエンジンによらない一般的なコストを設定する。
code:mysql
mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | NULL | 2019-01-20 22:44:29 | NULL | 20 |
| disk_temptable_row_cost | NULL | 2019-01-20 22:44:29 | NULL | 0.5 |
| key_compare_cost | NULL | 2019-01-20 22:44:29 | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 2019-01-20 22:44:29 | NULL | 1 |
| memory_temptable_row_cost | NULL | 2019-01-20 22:44:29 | NULL | 0.1 |
| row_evaluate_cost | NULL | 2019-01-20 22:44:29 | NULL | 0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)
mysql.engine_cost
ストレージエンジン固有のコストを設定する。
コスト見積もり
MySQL 5.7 からは、テーブルの結合順序に見積もりに、WHERE による絞り込みが考慮されるようになった。
例えば、駆動表 t1 と内部表 t2 の JOIN を考える。この時、JOIN のアルゴリズムは以下のように動く。
1. t1 に対して、アクセスタイプに基づき $ x 件アクセスする
2. 上記でアクセスした行各々につき、where 条件にマッチするか評価し、$ f % に件数を絞り込む
3. 上記で絞り込んだ結果各々に対し、t2 に対し、アクセスタイプに基づき平均 $ y 件アクセスする
この SELECT でアクセスする行数を数えてみる。まず、最初に $ x 件アクセスする。その後のフィルタリングは行に新たにアクセスするわけではなく、すでにアクセスした行に対してフィルタリング条件を適用しているにすぎない。フィルタリングした結果の行数は $ x \times \frac{f}{100} 件であり、そこからさらに内部表である t2 との JOIN を行う。t2 はアクセスタイプに基づき、JOIN 対象となる行は $ y 件に絞り込める。よって、約 $ x \times \frac{f}{100} \times y 件のアクセスを行う。
https://gyazo.com/20ec0dc676eb2f38b8c00eb3028e845b
最終的に、SELECT によってアクセスされる行数は $ x + x \times \frac{f}{100} \times y 件となる。t2 の WHERE 条件は、この場合はパフォーマンスにほとんど影響しない。すでに必要な行は全てフェッチした後なので。
MySQL 5.6 までは、この時の $ f % が考慮されていなかったが、MySQL 5.7 からは考慮されるようになった。