【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量

MySQL提供了多种控制MySQL优化器行为方法
本文将介绍通过系统变量optimizer_switch控制优化器的功能。

optimizer_switch参数

通过设置optimizer_switch系统变量的值可以控制优化器的行为。

- optimizer_switch系统变量由一组代表某个优化器行为的标志变量组成。
- 每个标志变量的值可以设置为on或off，表示相应的优化器行为（功能）是否启用或禁用。
- optimizer_switch系统变量可以全局级别（Global）或者会话级别（Session）设置，并且也可以在SQL语句级别通过SET_VAR提示设置。
- optimizer_switch系统变量在不同的版本上，具有不同的标志变量和默认值。

optimizer_switch系统变量详细：

Command-Line Format	–optimizer-switch=value
System Variable	optimizer_switch
Scope	Global, Session
Dynamic	Yes
SET_VAR Hint Applies	Yes
Type	Set
Valid Values (≥ 8.0.22)	batched_key_access={on\|off} block_nested_loop={on\|off} condition_fanout_filter={on\|off} derived_condition_pushdown={on\|off} derived_merge={on\|off} duplicateweedout={on\|off} engine_condition_pushdown={on\|off} firstmatch={on\|off} hash_join={on\|off} index_condition_pushdown={on\|off} index_merge={on\|off} index_merge_intersection={on\|off} index_merge_sort_union={on\|off} index_merge_union={on\|off} loosescan={on\|off} materialization={on\|off} mrr={on\|off} mrr_cost_based={on\|off} prefer_ordering_index={on\|off} semijoin={on\|off} skip_scan={on\|off} subquery_materialization_cost_based={on\|off} subquery_to_derived={on\|off} use_index_extensions={on\|off} use_invisible_indexes={on\|off}
Valid Values (≥ 8.0.21)	batched_key_access={on\|off} block_nested_loop={on\|off} condition_fanout_filter={on\|off} derived_merge={on\|off} duplicateweedout={on\|off} engine_condition_pushdown={on\|off} firstmatch={on\|off} hash_join={on\|off} index_condition_pushdown={on\|off} index_merge={on\|off} index_merge_intersection={on\|off} index_merge_sort_union={on\|off} index_merge_union={on\|off} loosescan={on\|off} materialization={on\|off} mrr={on\|off} mrr_cost_based={on\|off} prefer_ordering_index={on\|off} semijoin={on\|off} skip_scan={on\|off} subquery_materialization_cost_based={on\|off} subquery_to_derived={on\|off} use_index_extensions={on\|off} use_invisible_indexes={on\|off}
Valid Values (≥ 8.0.18)	batched_key_access={on\|off} block_nested_loop={on\|off} condition_fanout_filter={on\|off} derived_merge={on\|off} duplicateweedout={on\|off} engine_condition_pushdown={on\|off} firstmatch={on\|off} hash_join={on\|off} index_condition_pushdown={on\|off} index_merge={on\|off} index_merge_intersection={on\|off} index_merge_sort_union={on\|off} index_merge_union={on\|off} loosescan={on\|off} materialization={on\|off} mrr={on\|off} mrr_cost_based={on\|off} semijoin={on\|off} skip_scan={on\|off} subquery_materialization_cost_based={on\|off} use_index_extensions={on\|off} use_invisible_indexes={on\|off}
Valid Values (≥ 8.0.13)	batched_key_access={on\|off} block_nested_loop={on\|off} condition_fanout_filter={on\|off} derived_merge={on\|off} duplicateweedout={on\|off} engine_condition_pushdown={on\|off} firstmatch={on\|off} index_condition_pushdown={on\|off} index_merge={on\|off} index_merge_intersection={on\|off} index_merge_sort_union={on\|off} index_merge_union={on\|off} loosescan={on\|off} materialization={on\|off} mrr={on\|off} mrr_cost_based={on\|off} semijoin={on\|off} skip_scan={on\|off} subquery_materialization_cost_based={on\|off} use_index_extensions={on\|off} use_invisible_indexes={on\|off}
Valid Values (≤ 8.0.12)	batched_key_access={on\|off} block_nested_loop={on\|off} condition_fanout_filter={on\|off} derived_merge={on\|off} duplicateweedout={on\|off} engine_condition_pushdown={on\|off} firstmatch={on\|off} index_condition_pushdown={on\|off} index_merge={on\|off} index_merge_intersection={on\|off} index_merge_sort_union={on\|off} index_merge_union={on\|off} loosescan={on\|off} materialization={on\|off} mrr={on\|off} semijoin={on\|off} subquery_materialization_cost_based={on\|off} use_index_extensions={on\|off} use_invisible_indexes={on\|off}

参考：
optimizer_switch
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_switch

查看optimizer_switch参数

和其他系统变量一样，可以通过多种方法查看查看optimizer_switch参数的值。

通过SHOW VARIABLES 查看变量的设置情况。
例：

mysql> show variables like 'optimizer_switch' \G
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set, 1 warning (0.00 sec)

通过SELECT @@<变量名>来查看变量。

例：

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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

还可以通过在SELECT @@<变量名>后面加上like查看optimizer_switch 内部标志变量的值。

例：

--返回1表示找到相关内部标志变量，然后通过on和off的值判断是否启用了该功能。
mysql> SELECT @@optimizer_switch LIKE '%index_merge=on%';
+--------------------------------------------+
| @@optimizer_switch LIKE '%index_merge=on%' |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@optimizer_switch LIKE '%index_merge=off%';
+---------------------------------------------+
| @@optimizer_switch LIKE '%index_merge=off%' |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

参考：
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认

修改optimizer_switch参数

可以通过SET命令修改optimizer_switch参数，具体语法如下：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

其中对于command（也就是optimizer_switch 内部标志变量）具有以下表中所示的一种形式值。

Command Syntax	Meaning
default	Reset every optimization to its default value	恢复所有指定功能为默认值
opt_name=default	Set the named optimization to its default value	设置指定功能为默认值
opt_name=off	Disable the named optimization	关闭指定功能
opt_name=on	Enable the named optimization	启用指定功能

- 可以将opt_name标志设置为default 、on或off。
- 命令值的顺序无关紧要，但如果存在默认命令，则首先执行默认命令。
- 不可以对相同的opt_name多次指定值，并会导致错误。
- 任何值中的错误都会导致分配失败并出现错误，使optimizer_switch的值保持不变。

例：SET @@optimizer_switch=“index_merge=off”;

mysql> show variables like 'optimizer_switch' \G
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set, 1 warning (0.00 sec)mysql> SELECT @@optimizer_switch LIKE '%index_merge=on%';
+--------------------------------------------+
| @@optimizer_switch LIKE '%index_merge=on%' |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)mysql> SET @@optimizer_switch="index_merge=off";
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@optimizer_switch LIKE '%index_merge=on%';
+--------------------------------------------+
| @@optimizer_switch LIKE '%index_merge=on%' |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)mysql>  show variables like 'optimizer_switch' \G
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: index_merge=off,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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set, 1 warning (0.00 sec)mysql>