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

2024-02-28 00:03分类：茶道阅读：

文章目录

【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量
- optimizer_switch参数
- 查看optimizer_switch参数
- 修改optimizer_switch参数
- 小结

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

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

通过设置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参数的值。

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

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

例：

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

例：

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

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

其中对于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	启用指定功能