可切换的优化

使用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,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

要更改的值optimizer_switch，请分配一个值，该值由一个或多个命令的逗号分隔列表组成：

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（默认off）
  控制BKA连接算法的使用。
为batched_key_access使设置为有效on，该mrr标记还必须为on。当前，MRR的成本估算过于悲观。因此，也有必要对mrr_cost_based要off用于要使用的BKA。
有关更多信息，请参见“块嵌套循环”。
块嵌套循环标志
- block_nested_loop（默认on）
  控制BNL连接算法的使用。
有关更多信息，请参见“块嵌套循环”。
条件过滤标志
- condition_fanout_filter（默认on）
  控制条件过滤的使用。
有关更多信息，请参见“条件过滤”。
派生表合并标志
- derived_merge（默认on）
  控制派生表和视图合并到外部查询块中。
derived_merge假设没有其他规则阻止合并，则该标志控制优化器是否尝试将派生表，视图引用和公用表表达式合并到外部查询块中。例如，ALGORITHM视图的指令优先于derived_merge设置。默认情况下，该标志on用于启用合并。
有关更多信息，请参见“通过合并或实现来优化派生表，视图引用和公用表表达式”。
发动机状态下推标志
- engine_condition_pushdown（默认on）
  控制发动机状态下推。
有关更多信息，请参见“引擎状态下推优化”。
哈希联接标志
- hash_join（默认on）
  控制哈希联接（仅适用于MySQL 8.0.18；在MySQL 8.0.19或更高版本中无效）。
有关更多信息，请参见“哈希联接优化”。
索引条件下推标志
- index_condition_pushdown（默认on）
  控制索引条件下推。
有关更多信息，请参见“索引条件下推优化”。
索引扩展标志
- use_index_extensions（默认on）
  控制索引扩展的使用。
有关更多信息，请参见“索引扩展的使用”。
索引合并标志
- index_merge（默认on）
  控制所有索引合并优化。
- index_merge_intersection（默认on）
  控制索引合并路口访问优化。
- index_merge_sort_union（默认on）
  控制索引合并排序联盟访问优化。
- index_merge_union（默认on）
  控制索引合并联合访问优化。
有关更多信息，请参见“优化索引”。
索引可见性标志
- use_invisible_indexes（默认off）
  控制不可见索引的使用。
有关更多信息，请参见“不可见索引”。
多范围读取标志
- mrr（默认on）
  控制多范围读取策略。
- mrr_cost_based（默认on）
  如果，则控制基于成本的MRR的使用mrr=on。
有关更多信息，请参见“多范围读取优化”。
跳过扫描标志
- skip_scan（默认on）
  控制“跳过扫描”访问方法的使用。
有关更多信息，请参见跳过扫描范围访问方法。
半联接标志
- semijoin（默认on）
  控制所有半联接策略。
  在MySQL 8.0.17和更高版本中，这也适用于抗联接优化。
- duplicateweedout（默认on）
  控制半联接重复除草策略。
- firstmatch（默认on）
  控制半联接的FirstMatch策略。
- loosescan（默认on）
  控制半联接的LooseScan策略（不要与Loose Index Scan for混淆GROUP BY）。
在semijoin，firstmatch，loosescan，和duplicateweedout标志启用过的半连接策略控制。该semijoin标志控制是否使用半联接。如果将其设置为on，则firstmatch和loosescan标志可对允许的半联接策略进行更好的控制。
如果duplicateweedout禁用了半连接策略，则除非所有其他适用策略也都被禁用，否则将不使用它。
如果semijoin和materialization均为on，则半联接在适用的情况下也使用物化。这些标志是on默认的。
有关更多信息，请参见“使用 EXISTS 策略优化子查询”。
子查询实现标志
- materialization（默认on）
  控制实现（包括半联接实现）。
- subquery_materialization_cost_based（默认on）
  使用基于成本的物化选择。
该materialization标志控制是否使用子查询实现。如果semijoin和materialization均为on，则半联接在适用的情况下也使用物化。这些标志是on默认的。
该subquery_materialization_cost_based标志使您可以控制子查询实现和IN-to-EXISTSsubquery转换之间的选择。如果标记为on（默认），则优化器将在子查询实现和IN-to-EXISTSsubquery转换之间执行基于成本的选择（如果可以使用这两种方法）。如果标志是off，优化器选择子查询物化了IN-到-EXISTS子查询的转变。
有关更多信息，请参见“通过合并优化派生表、视图引用和公用表表达式”。

当您为分配值时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,
                    condition_fanout_filter=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,
                    condition_fanout_filter=on