MySQLでは実行計画を生成するために、コストモデルを採用しています。オプティマイザーは実行計画を生成するために、さまざまなオペレーションからコストを見積もります。その際にベースとなる推定値が、mysqlデータベースのserver_costとengine_costテーブルに格納されていています。MySQL 5.6とそれ以前はコストパラメータの値は定数としてハードコードされていましたが、MySQL 5.7とそれ以降からはコストパラメータの値を変更することが可能になりました。
今回はMySQL 8.0.17を使用して、コスト見積もりを調整する方法について紹介したいと思います。
server_costテーブル
server_costは、一般的なサーバー操作のオプティマイザーの見積もりが格納されているテーブルです。SELECTしてみると、以下のようになっています。
各列の説明
cost_name
列はコストモデルで使用されるコストの見積もりの名前(コストパラメータ)です。
default_value
列はハードコードされた値でデフォルト値となります。
cost_value
列はデフォルト値から変更したいときにここに値を設定します。
last_update
列は行の更新時間です。
comment
列は任意のコメントを設定できます。もし、コストを変更した場合に理由などを残すことができます。
コストパラメータについて
それぞれのコストパラメータついて説明します。
- disk_temptable_create_cost,disk_temptable_row_cost
- ディスクベースのストレージエンジン(InnoDBまたはMyISAMのいずれか)に格納される、内部的に作成された一時テーブルのコスト見積もりです。これらの値を増やすと、内部一時テーブルの使用のコスト見積もりが増加し、オプティマイザーはそれらの使用頻度が少ないクエリプランを優先します。
- memory_temptable_create_cost,memory_temptable_row_cost
- MEMORYストレージエンジンに格納される、内部的に作成された一時テーブルのコスト見積もりです。これらの値を増やすと、内部一時テーブルの使用のコスト見積もりが増加し、オプティマイザーはそれらの使用頻度が少ないクエリプランを優先します。
- key_compare_cost
- レコードキーを比較するコストです。この値を大きくすると、多くのキーを比較するクエリプランがよりコストが高くなります。たとえば、
filesort
する実行計画は、インデックスを使用したソートを回避するクエリプランに比べて比較的コストが高く見積もられます。
- row_evaluate_cost
- レコードの条件を評価するコストです。この値を大きくすると、多くの行をフェッチするクエリプランが、より少ない行をフェッチするクエリプランに比べてコストが高くなります。たとえば、テーブルスキャンは読み取る行数が少ないレンジスキャンに比べて、比較的コストが高く見積もられます。
それぞれのデフォルト値は以下のようになっています。MySQLのバージョンによって異なります。
cost_name |
MySQL 8.0 |
MySQL 5.7 |
disk_temptable_create_cost |
20 |
40 |
disk_temptable_row_cost |
0.5 |
1 |
memory_temptable_create_cost |
1 |
2 |
memory_temptable_row_cost |
0.1 |
0.2 |
key_compare_cost |
0.05 |
0.1 |
row_evaluate_cost |
0.1 |
0.2 |
disk_temptable_xxよりもmemory_temptable_xxの基準値が小さい理由は、ディスクベースよりもメモリベースのテーブルの処理コストが低いからです。
engine_costテーブル
特定のストレージエンジンの操作のオプティマイザーの見積もりが格納されているテーブルです。SELECTしてみると、以下のようになっています。
各列の説明
engine_name
列はコスト見積もりが適用されるストレージエンジンの名前が設定されています。defaultという値はすべてのストレージエンジンに適用されます。device_type
列は現在使用されておらず、許可される値は0のみです。
上記以外の列はserver_costテーブルと同じです。
コストパラメータについて
それぞれのコストパラメータついて説明します。
- io_block_read_cost
- ディスクからデータを読み取るコストです。
- memory_block_read_cost
- メモリ(InnoDBストレージエンジンではInnoDB Buffer Pool)からデータを読み取るコストです。
それぞれのデフォルト値は以下のようになっています。MySQLのバージョンによって異なります。
cost_name |
MySQL 8.0 |
MySQL 5.7 |
io_block_read_cost |
1 |
1 |
memory_block_read_cost |
0.25 |
1 |
たとえば、IOの遅いHDDを使用しているため、新たにディスクからデータを読み取るよりも、可能な限りすでにメモリにロードされたデータを優先して実行計画を生成したい場合はio_block_read_costの値を大きくしたり、memory_block_read_costの値を小さくする調整をします。
コスト値の変更方法
前述で紹介したコスト値を変更することができます。たとえば、server_costテーブルのkey_compare_cost を0.1へ変更してみます。
まずは、server_costテーブルにupdateを実行します。
その後、FLUSH OPTIMIZER_COSTS
構文を実行することで設定が反映されます。
注意として、すでに接続中のセッションに対しては設定が反映されません。全体に反映させるには既存のセッションを切断し、再度接続する必要があります。
engine_costテーブルについては、特定のストレージエンジンのみに変更したい場合は以下のようにINSERTします。以下の例ではInnoDBストレージエンジンのみio_block_read_costの値を3に設定しています。
まとめ
今回はMySQLのコスト見積もりを調整する方法について紹介しました。
基本的にはこれらの値は変更することが少ないと思います。注意として、セッション単位やクエリ単位で設定することは不可で、変更はサーバ全体で有効になります。サービスリリース前の負荷テストやシナリオテスト時に希望する実行計画が全体的に得られないときなど、これらの値を調整することが望ましいと思います。
MySQLのコストモデルについて詳しく知りたい方は 8.9.5 The Optimizer Cost Modelをご参照ください。