MySQLでは実行計画を生成するために、コストモデルを採用しています。オプティマイザーは実行計画を生成するために、さまざまなオペレーションからコストを見積もります。その際にベースとなる推定値が、mysqlデータベースのserver_costとengine_costテーブルに格納されていています。MySQL 5.6とそれ以前はコストパラメータの値は定数としてハードコードされていましたが、MySQL 5.7とそれ以降からはコストパラメータの値を変更することが可能になりました。

今回はMySQL 8.0.17を使用して、コスト見積もりを調整する方法について紹介したいと思います。

server_costテーブル

server_costは、一般的なサーバー操作のオプティマイザーの見積もりが格納されているテーブルです。SELECTしてみると、以下のようになっています。

mysql >SELECT * FROM server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2019-04-26 19:21:46 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2019-04-26 19:21:46 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2019-04-26 19:21:46 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2019-04-26 19:21:46 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2019-04-26 19:21:46 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2019-04-26 19:21:46 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+

各列の説明

• 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のバージョンによって異なります。

disk_temptable_xxよりもmemory_temptable_xxの基準値が小さい理由は、ディスクベースよりもメモリベースのテーブルの処理コストが低いからです。

engine_costテーブル

特定のストレージエンジンの操作のオプティマイザーの見積もりが格納されているテーブルです。SELECTしてみると、以下のようになっています。

mysql >SELECT * FROM engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2019-04-26 19:21:46 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2019-04-26 19:21:46 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+

各列の説明

engine_name列はコスト見積もりが適用されるストレージエンジンの名前が設定されています。defaultという値はすべてのストレージエンジンに適用されます。device_type列は現在使用されておらず、許可される値は0のみです。

上記以外の列はserver_costテーブルと同じです。

コストパラメータについて

それぞれのコストパラメータついて説明します。

io_block_read_cost

ディスクからデータを読み取るコストです。

memory_block_read_cost

メモリ（InnoDBストレージエンジンではInnoDB Buffer Pool）からデータを読み取るコストです。

それぞれのデフォルト値は以下のようになっています。MySQLのバージョンによって異なります。

たとえば、IOの遅いHDDを使用しているため、新たにディスクからデータを読み取るよりも、可能な限りすでにメモリにロードされたデータを優先して実行計画を生成したい場合はio_block_read_costの値を大きくしたり、memory_block_read_costの値を小さくする調整をします。

コスト値の変更方法

前述で紹介したコスト値を変更することができます。たとえば、server_costテーブルのkey_compare_cost を0.1へ変更してみます。

まずは、server_costテーブルにupdateを実行します。

mysql> UPDATE mysql.server_cost SET cost_value=0.1 WHERE cost_name='key_compare_cost';

その後、FLUSH OPTIMIZER_COSTS構文を実行することで設定が反映されます。

mysql> FLUSH OPTIMIZER_COSTS;

注意として、すでに接続中のセッションに対しては設定が反映されません。全体に反映させるには既存のセッションを切断し、再度接続する必要があります。

engine_costテーブルについては、特定のストレージエンジンのみに変更したい場合は以下のようにINSERTします。以下の例ではInnoDBストレージエンジンのみio_block_read_costの値を3に設定しています。

INSERT INTO mysql.engine_cost 
(engine_name,device_type,cost_name,cost_value)  VALUES 
('InnoDB', 0, 'io_block_read_cost', 3.0);

まとめ

今回はMySQLのコスト見積もりを調整する方法について紹介しました。

基本的にはこれらの値は変更することが少ないと思います。注意として、セッション単位やクエリ単位で設定することは不可で、変更はサーバ全体で有効になります。サービスリリース前の負荷テストやシナリオテスト時に希望する実行計画が全体的に得られないときなど、これらの値を調整することが望ましいと思います。

MySQLのコストモデルについて詳しく知りたい方は 8.9.5 The Optimizer Cost Modelをご参照ください。