8.9.2 可切换的优化
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
要更改optimizer_switch的值，请分配一个值，该值由一个或多个命令的逗号分隔列 table 组成：
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
每个* command *值应具有下 table 所示的形式之一。
| Command Syntax | Meaning |
|---|---|
default | 将每个优化重置为其默认值 |
opt_name=default | 将命名的优化设置为其默认值 |
opt_name=off | 禁用命名优化 |
opt_name=on | 启用命名的优化 |
值中命令的 Sequences 无关紧要，尽管default命令(如果存在)首先执行。将* opt_name 标志设置为default会将其设置为默认值on或off中的任何一个。不允许在值中多次指定任何给定的 opt_name *并导致错误。值中的任何错误都会导致分配失败并显示错误，而使optimizer_switch的值保持不变。
以下列 table 描述了允许的opt_name1 *标志名称，按优化策略分组：
-
批处理密钥访问标志
-
batched_key_access(默认off)
控制 BKA 连接算法的使用。
为了使batched_key_access设置为on时生效，mrr标志还必须为on。当前，对 MRR 的成本估算过于悲观。因此，mrr_cost_based也必须是off才能使用 BKA。
有关更多信息，请参见第 8.2.1.11 节，“阻止嵌套循环和批处理键访问联接”。
-
块嵌套循环标志
-
block_nested_loop(默认on)
控制 BNL 连接算法的使用。
有关更多信息，请参见第 8.2.1.11 节，“阻止嵌套循环和批处理键访问联接”。
-
条件过滤标志
-
condition_fanout_filter(默认on)
控制条件过滤的使用。
有关更多信息，请参见第 8.2.1.12 节，“条件过滤”。
-
派生 table 合并标志
-
derived_merge(默认on)
控制派生 table 和视图合并到外部查询块中。
derived_merge标志控制优化器是否尝试将派生 table 和视图引用合并到外部查询块中，假设没有其他规则阻止合并；例如，视图的ALGORITHM指令优先于derived_merge设置。默认情况下，该标志为on以启用合并。
有关更多信息，请参见第 8.2.2.4 节“通过合并或实现来优化派生 table 和视图引用”。
-
发动机状态下推标志
-
engine_condition_pushdown(默认on)
控制发动机状态下推。
有关更多信息，请参见第 8.2.1.4 节“引擎状态下推式优化”。
-
索引条件下推标志
-
index_condition_pushdown(默认on)
控制索引条件下推。
有关更多信息，请参见第 8.2.1.5 节“索引条件下推优化”。
-
索引扩展标志
-
use_index_extensions(默认on)
控制索引扩展的使用。
有关更多信息，请参见第 8.3.9 节“使用索引扩展”。
-
索引合并标志
-
index_merge(默认on)
控制所有索引合并优化。
index_merge_intersection(默认on)
控制索引合并路口访问优化。
index_merge_sort_union(默认on)
控制索引合并排序 union 访问优化。
index_merge_union(默认on)
控制索引合并联合访问优化。
有关更多信息，请参见第 8.2.1.3 节“索引合并优化”。
-
多范围读取标志
-
mrr(默认on)
控制多范围读取策略。
mrr_cost_based(默认on)
如果是mrr=on，则控制基于成本的 MRR 的使用。
有关更多信息，请参见第 8.2.1.10 节，“多范围读取优化”。
-
Semijoin Flags
-
semijoin(默认on)
控制所有半联接策略。
duplicateweedout(默认on)
控制半联接重复除草策略。
firstmatch(默认on)
控制半联接的 FirstMatch 策略。
loosescan(默认on)
控制半联接的 LooseScan 策略(不要与GROUP BY的 Loose Index Scan 混淆)。
semijoin，firstmatch，loosescan和duplicateweedout标志启用对半联接策略的控制。 semijoin标志控制是否使用半联接。如果将其设置为on，则firstmatch和loosescan标志可以更好地控制允许的半联接策略。
如果禁用了duplicateweedout半联接策略，则除非所有其他适用的策略也都被禁用，否则将不使用它。
如果semijoin和materialization均为on，则半联接在适用的情况下也使用实现。这些标志默认为on。
有关更多信息，请参见第 8.2.2.1 节“使用半联接转换优化子查询，派生 table 和视图引用”。
-
子查询实现标志
-
materialization(默认on)
控制实现(包括半联接实现)。
subquery_materialization_cost_based(默认on)
使用基于成本的物化选择。
materialization标志控制是否使用子查询实现。如果semijoin和materialization均为on，则半联接在适用的情况下也使用实现。这些标志默认为on。
subquery_materialization_cost_based标志可控制子查询实现和IN到EXISTS子查询转换之间的选择。如果标志是on(默认值)，则优化器将在子查询实现和_5 至 6 子查询转换之间执行基于成本的选择(如果可以使用这两种方法)。如果标志是off，那么优化器将选择子查询实现，而不是IN到EXISTS子查询转换。
有关更多信息，请参见第 8.2.2 节“优化子查询，派生 table 和视图引用”。
当您将值分配给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
