optimizer_switch
システム変数を使用するとオプティマイザの動作を制御できます。その値はフラグのセットで、それぞれ対応するオプティマイザの動作を有効にするかまたは無効にするかを示す on
または off
の値を持ちます。この変数はグローバル値およびセッション値を持ち、実行時に変更できます。グローバル値のデフォルトはサーバーの起動時に設定できます。
オプティマイザの現在のフラグセットを表示するには、変数値を選択します。
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on
optimizer_switch
の値を変更するには、1 つ以上のコマンドのカンマ区切りのリストから構成される値を割り当てます。
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
各 command
値は、次の表に示すいずれかの形式になるようにしてください。
コマンドの構文 | 意味 |
---|---|
default |
すべての最適化をそのデフォルト値にリセットします |
|
指定した最適化をそのデフォルト値に設定します |
|
指定した最適化を無効にします |
|
指定した最適化を有効にします |
default
コマンドが存在する場合最初に実行されますが、値の中のコマンドの順序は問題ではありません。opt_name
フラグを default
に設定すると、そのデフォルト値が on
または off
のどちらであってもそれに設定されます。値に特定の opt_name
を複数回指定することは許可されず、エラーが発生します。値のエラーによって、割り当てがエラーを伴って失敗し、optimizer_switch
の値が変更されないままになります。
次の表に、最適化戦略別にグループ化した、許可される opt_name
フラグ名を一覧表示します。
最適化 | フラグ名 | 意味 |
---|---|---|
Batched Key Access | batched_key_access |
BKA 結合アルゴリズムの使用を制御します |
Block Nested Loop | block_nested_loop |
BNL 結合アルゴリズムの使用を制御します |
エンジンコンディションプッシュダウン | engine_condition_pushdown |
エンジンコンディションプッシュダウンを制御します |
インデックスコンディションプッシュダウン | index_condition_pushdown |
インデックスコンディションプッシュダウンを制御します |
インデックス拡張 | use_index_extensions |
インデックス拡張の使用を制御します |
インデックスマージ | index_merge |
すべてのインデックスマージ最適化を制御します |
index_merge_intersection |
インデックスマージ共通集合アクセス最適化を制御します | |
index_merge_sort_union |
インデックスマージソート和集合アクセス最適化を制御します | |
index_merge_union |
インデックスマージ和集合アクセス最適化を制御します | |
Multi-Range Read | mrr |
Multi-Range Read 戦略を制御します |
mrr_cost_based |
mrr=on の場合にコストベースの MRR の使用を制御します |
|
準結合 | semijoin |
すべての準結合戦略を制御します |
firstmatch |
準結合 FirstMatch 戦略を制御します | |
loosescan |
準結合 LooseScan 戦略を制御します (GROUP BY の LooseScan と混同しないでください) |
|
サブクエリー実体化 | materialization |
実体化を制御します (準結合実体化を含む) |
subquery_materialization_cost_based |
使用されたコストベースの実体化の選択 |
block_nested_loop
および batched_key_access
フラグは MySQL 5.6.3 で追加されました。batched_key_access
が on
に設定されている場合に何らかの効果を持つためには、mrr
フラグも on
である必要があります。現在、MRR のコスト見積もりはきわめて悲観的です。したがって、BKA を使用するには、mrr_cost_based
を off
にする必要もあります。
semijoin
、firstmatch
、loosescan
、および materialization
フラグは MySQL 5.6.5 で、準結合およびサブクエリー実体化戦略を制御できるようにするために追加されました。semijoin
フラグは準結合を使用するかどうかを制御します。これが on
に設定されている場合、firstmatch
および loosescan
フラグによって、使用可能な準結合戦略を詳細に制御できます。materialization
フラグはサブクエリー実体化を使用するかどうかを制御します。semijoin
と materialization
が両方とも on
の場合、該当すれば準結合でも実体化が使用されます。これらのフラグはデフォルトで on
です。
subquery_materialization_cost_based
は、MySQL 5.6.7 で、サブクエリー実体化と IN -> EXISTS
サブクエリー変換の選択を制御できるようにするために追加されました。フラグが on
(デフォルト) の場合、オプティマイザは、サブクエリー実体化と IN -> EXISTS
サブクエリー変換のどちらの方法も使用できる場合に、コストベースの選択を実行します。フラグが off
の場合、オプティマイザは、MySQL 5.6.7 より前の動作だった IN -> EXISTS
サブクエリー変換より、サブクエリー実体化を選択します。
個々の最適化戦略の詳細については、次のセクションを参照してください。
optimizer_switch
に値を割り当てると、指定されていないフラグはそれらの現在の値を維持します。これにより、ほかの動作に影響を与えることなく、単一のステートメントで特定のオプティマイザの動作を有効または無効にできます。ステートメントは、ほかの存在するオプティマイザフラグやそれらの値に依存しません。すべてのインデックスマージ最適化が有効になっているとします。
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on
サーバーが特定のクエリーに対して、インデックスマージ和集合アクセスメソッドとインデックスマージソート和集合アクセスメソッドを使用しており、それらがなければオプティマイザの実行が改善されるかどうかをチェックする場合は、変数値を次のように設定します。
mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
index_merge_sort_union=off,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on