8.8.5.2 切り替え可能な最適化の制御

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	すべての最適化をそのデフォルト値にリセットします
opt_name=default	指定した最適化をそのデフォルト値に設定します
opt_name=off	指定した最適化を無効にします
opt_name=on	指定した最適化を有効にします

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 サブクエリー変換より、サブクエリー実体化を選択します。

個々の最適化戦略の詳細については、次のセクションを参照してください。

• セクション8.2.1.14「Block Nested Loop 結合と Batched Key Access 結合」

• セクション8.2.1.5「エンジンコンディションプッシュダウンの最適化」

• セクション8.2.1.7「インデックス拡張の使用」

• セクション8.2.1.6「インデックスコンディションプッシュダウンの最適化」

• セクション8.2.1.4「インデックスマージの最適化」

• セクション8.2.1.13「Multi-Range Read の最適化」

• セクション8.2.1.18「サブクエリーの最適化」

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