MySQLでは実行計画を生成するために、
今回はMySQL 8.
server_costテーブル
server_
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のバージョンによって異なります。
cost_ |
MySQL 8. |
MySQL 5. |
---|---|---|
disk_ |
20 | 40 |
disk_ |
0. |
1 |
memory_ |
1 | 2 |
memory_ |
0. |
0. |
key_ |
0. |
0. |
row_ |
0. |
0. |
disk_
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_
列はコスト見積もりが適用されるストレージエンジンの名前が設定されています。defaultという値はすべてのストレージエンジンに適用されます。device_
列は現在使用されておらず、
上記以外の列はserver_
コストパラメータについて
それぞれのコストパラメータついて説明します。
- io_
block_ read_ cost - ディスクからデータを読み取るコストです。
- memory_
block_ read_ cost - メモリ
(InnoDBストレージエンジンではInnoDB Buffer Pool) からデータを読み取るコストです。
それぞれのデフォルト値は以下のようになっています。MySQLのバージョンによって異なります。
cost_ |
MySQL 8. |
MySQL 5. |
---|---|---|
io_ |
1 | 1 |
memory_ |
0. |
1 |
たとえば、
コスト値の変更方法
前述で紹介したコスト値を変更することができます。たとえば、
まずは、
mysql> UPDATE mysql.server_cost SET cost_value=0.1 WHERE cost_name='key_compare_cost';
その後、FLUSH OPTIMIZER_
構文を実行することで設定が反映されます。
mysql> FLUSH OPTIMIZER_COSTS;
注意として、
engine_
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.