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

news/2024/10/31 9:30:36/

文章目录

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

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

【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 Variableoptimizer_switch
ScopeGlobal, Session
DynamicYes
SET_VAR Hint AppliesYes
TypeSet
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参数的值。

  1. 通过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)
  1. 通过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 SyntaxMeaning
defaultReset every optimization to its default value恢复所有指定功能为默认值
opt_name=defaultSet the named optimization to its default value设置指定功能为默认值
opt_name=offDisable the named optimization关闭指定功能
opt_name=onEnable 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>

参考:
8.9.2 Switchable Optimizations
https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

小结

本文介绍了通过系统变量optimizer_switch控制优化器功能的方法。


http://www.ppmy.cn/news/549757.html

相关文章

【软件设计师暴击考点】操作系统知识高频考点暴击系列【一】

&#x1f468;‍&#x1f4bb;个人主页&#xff1a;元宇宙-秩沅 &#x1f468;‍&#x1f4bb; hallo 欢迎 点赞&#x1f44d; 收藏⭐ 留言&#x1f4dd; 加关注✅! &#x1f468;‍&#x1f4bb; 本文由 秩沅 原创 &#x1f468;‍&#x1f4bb; 收录于专栏&#xff1a;软件…

步进电机驱动

步进电机驱动 以下是几款步进电机驱动芯片。 https://industrial.panasonic.cn/ea/products/semiconductors/motordrivers/stepping/stepping-motor-driver-ics?reset1 https://toshiba-semicon-storage.com/list/index.php?codeparam_604&regionapc&langzh_cn&am…

东芝Toshiba e-STUDIO810 一体机驱动

东芝Toshiba e-STUDIO810 一体机驱动是官方提供的一款一体机&#xff08;打印/扫描&#xff09;驱动&#xff0c;本站收集提供高速下载&#xff0c;用于解决一体机与电脑连接不了&#xff0c;无法正常使用的问题&#xff0c;本动适用于&#xff1a;Windows XP / Windows 7 / Wi…

东芝Toshiba TS-8200F 打印机驱动

东芝Toshiba TS-8200F 打印机驱动是官方提供的一款打印机驱动&#xff0c;本站收集提供高速下载&#xff0c;用于解决打印机与电脑连接不了&#xff0c;无法正常使用的问题&#xff0c;本动适用于&#xff1a;Windows XP / Windows 7 / Windows 8 / Windows 10 32/64位操作系统…

sundancest201驱动_Toshiba Sundance ST201 based PCI Fast Ethernet Adapter 驱动程序下载——更新 Toshiba 软件...

Toshiba Sundance ST201 based PCI Fast Ethernet Adapter 驱动程序下载 如何手动下载和更新: 此内置 Toshiba Sundance ST201 based PCI Fast Ethernet Adapter驱动程序应包含在Windows操作系统中&#xff0c;或者可以通过Windows更新获得。 内置驱动程序支持Toshiba Sundance…

在内核目录中编译驱动与Kconfig

查看驱动目录下的makefile 可以看到很多配置项 linux\drivers\char\Makefile: obj-$(CONFIG_DTLK) dtlk.o obj-$(CONFIG_APPLICOM) applicom.o obj-$(CONFIG_SONYPI) sonypi.o obj-$(CONFIG_RTC) rtc.o obj-$(CONFIG_HPET) hpet.o obj-$(CONFIG_EFI_RTC) efirtc…

Linux SD卡驱动

二、MMC/SD介绍及SDI主机控制器 首先我们来理清几个概念&#xff1a; MMC&#xff1a;(Multi Media Card)由西门子公司和首推CF的SanDisk于1997年推出的多媒体记忆卡标准。SD&#xff1a;(Secure Digital Memory Card)由日本松下、东芝及美国SanDisk公司于1999年8月共同开发研…

SPI驱动文件解析

三&#xff1a;SPI设备驱动程序 在板文件中添加SPI设备 <span style"color:#444444"><strong>static </strong> <strong>struct</strong> spi_board_info s3c_spi_devs [] __initdata[ <span style"color:#880000">0…