8.9.2 可切换优化_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.9 控制查询优化器 / 8.9.2 可切换优化

8.9.2 可切换优化

系统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,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on,
                    prefer_ordering_index=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使用 offBKA。
有关详细信息，请参阅第 8.2.1.11 节，“阻止嵌套循环和成批密钥访问连接”。
阻止嵌套循环标志
- block_nested_loop （默认on）
  控制 BNL 连接算法的使用。
有关详细信息，请参阅第 8.2.1.11 节，“阻止嵌套循环和成批密钥访问连接”。
条件过滤标志
- condition_fanout_filter （默认on）
  控制条件过滤的使用。
有关详细信息，请参阅第 8.2.1.12 节，“条件过滤”。
派生表合并标志
- derived_merge（默认 on）
  控制将派生表和视图合并到外部查询块中。
该derived_merge标志控制优化器是否尝试将派生表和视图引用合并到外部查询块中，假设没有其他规则阻止合并；例如，ALGORITHM视图指令优先于 derived_merge设置。默认情况下，该标志是on启用合并。
有关详细信息，请参阅第 8.2.2.4 节，“使用合并或实现优化派生表和视图引用”。
发动机状况下推标志
- engine_condition_pushdown （默认on）
  控制发动机状态下推。
有关详细信息，请参阅第 8.2.1.4 节，“发动机状态下推优化”。
索引条件下推标志
- index_condition_pushdown （默认on）
  控制索引条件下推。
有关详细信息，请参阅第 8.2.1.5 节，“索引条件下推优化”。
索引扩展标志
- use_index_extensions （默认on）
  控制索引扩展的使用。
For more information, see Section 8.3.9, “Use of Index Extensions”.
Index Merge Flags
- index_merge (default on)
  Controls all Index Merge optimizations.
- index_merge_intersection (default on)
  Controls the Index Merge Intersection Access optimization.
- index_merge_sort_union (default on)
  Controls the Index Merge Sort-Union Access optimization.
- index_merge_union (default on)
  Controls the Index Merge Union Access optimization.
For more information, see Section 8.2.1.3, “Index Merge Optimization”.
Limit Optimization Flags
- prefer_ordering_index (default on)
  Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. This optimzation is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.
  Because the algorithm that makes this determination cannot handle every conceivable case (due in part to the assumption that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. Prior to MySQL 5.7.33, it ws not possible to disable this optimization, but in MySQL 5.7.33 and later, while it remains the default behavior, it can be disabled by setting the prefer_ordering_index flag to off.
For more information and examples, see Section 8.2.1.17, “LIMIT Query Optimization”.
Multi-Range Read Flags
- mrr (default on)
  Controls the Multi-Range Read strategy.
- mrr_cost_based (default on)
  Controls use of cost-based MRR if mrr=on.
For more information, see Section 8.2.1.10, “Multi-Range Read Optimization”.
Semijoin Flags
- duplicateweedout (default on)
  Controls the semijoin Duplicate Weedout strategy.
- firstmatch (default on)
  Controls the semijoin FirstMatch strategy.
- loosescan (default on)
  Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for GROUP BY).
- semijoin (default on)
  Controls all semijoin strategies.
The semijoin, firstmatch, loosescan, and duplicateweedout flags enable control over semijoin strategies. The semijoin flag controls whether semijoins are used. If it is set to on, the firstmatch and loosescan flags enable finer control over the permitted semijoin strategies.
If the duplicateweedout semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled.
If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.
For more information, see Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.
Subquery Materialization Flags
- materialization (default on)
  Controls materialization (including semijoin materialization).
- subquery_materialization_cost_based (default on)
  Use cost-based materialization choice.
The materialization flag controls whether subquery materialization is used. If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.
The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN-to-EXISTS subquery transformation. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN-to-EXISTS subquery transformation if either method could be used. If the flag is off, the optimizer chooses subquery materialization over IN-to-EXISTS subquery transformation.
For more information, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”.

当您为赋值时 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,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on,
                    prefer_ordering_index=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,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on,
                    prefer_ordering_index=on