系统optimizer_switch
变量可以控制优化器的行为。它的值是一组标志,每个标志的值为on
oroff
以指示相应的优化器行为是启用还是禁用。该变量具有全局值和会话值,可以在运行时更改。可以在服务器启动时设置全局默认值。
要查看当前的优化器标志集,请选择变量值:
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
要更改 的值
optimizer_switch
,请分配一个由逗号分隔的一个或多个命令列表组成的值:
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
每个command
值都应具有下表中显示的一种形式。
命令语法 | 意义 |
---|---|
default |
将每个优化重置为其默认值 |
|
将命名优化设置为其默认值 |
|
禁用命名优化 |
|
启用命名优化 |
值中命令的顺序无关紧要,但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。有关详细信息,请参阅 第 8.2.1.11 节,“阻止嵌套循环和成批密钥访问连接”。
阻止嵌套循环标志
block_nested_loop
(默认on
)控制 BNL 连接算法的使用。
有关详细信息,请参阅 第 8.2.1.11 节,“阻止嵌套循环和成批密钥访问连接”。
发动机状况下推标志
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
)控制索引合并排序联合访问优化。
index_merge_union
(默认on
)控制索引合并联合访问优化。
有关详细信息,请参阅 第 8.2.1.3 节,“索引合并优化”。
多范围读取标志
mrr
(默认on
)控制多范围读取策略。
mrr_cost_based
(默认on
)如果 ,则控制基于成本的 MRR 的使用
mrr=on
。
有关详细信息,请参阅 第 8.2.1.10 节,“多范围读取优化”。
半连接标志
semijoin
(默认on
)控制所有半连接策略。
firstmatch
(默认on
)控制半连接 FirstMatch 策略。
loosescan
(默认on
)控制半连接 LooseScan 策略(不要与 Loose Index Scan for 混淆
GROUP BY
)。
该
semijoin
标志控制是否使用半连接。如果它设置为on
,则firstmatch
和loosescan
标志可以更好地控制允许的半连接策略。如果
semijoin
和materialization
都是on
,则半连接也会在适用的情况下使用具体化。这些标志是on
默认的。有关详细信息,请参阅第 8.2.2.1 节,“使用半连接转换优化子查询”。
子查询实现标志
materialization
(默认on
)控制物化(包括半连接物化)。
subquery_materialization_cost_based
(默认on
)使用基于成本的物化选择。
该
materialization
标志控制是否使用子查询实现。如果semijoin
和materialization
都是on
,则半连接也会在适用的情况下使用具体化。这些标志是on
默认的。该
subquery_materialization_cost_based
标志可以控制子查询实现和子查询转换之间的IN
选择EXISTS
。如果标志是(默认值),优化器在子查询实现和子查询转换on
之间执行基于成本的选择( 如果可以使用任何一种方法)。如果标志是,优化器选择子查询实现而不是 子查询转换。IN
EXISTS
off
IN
EXISTS
有关详细信息,请参阅 第 8.2.2 节,“优化子查询和派生表”。
当您为 赋值时
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
如果服务器对某些查询使用 Index Merge Union 或 Index Merge Sort-Union 访问方法,并且您想检查优化器在没有它们的情况下是否性能更好,请像这样设置变量值:
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