【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量
日期：2024-03-11 13:10 | 人气：
文章目录
- 【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 | 启用指定功能 |
例：SET @@optimizer_switch=“index_merge=off”;
参考：
8.9.2 Switchable Optimizations
https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html
本文介绍了通过系统变量optimizer_switch控制优化器功能的方法。
