MySQL :: MySQL 8.3 Reference Manual :: 10.9.2 Switchable Optimizations

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Switchable Optimizations

10.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,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,hash_set_operations=on
1 row in set (0.00 sec)

要更改 optimizer_switch 的值，请分配一个值，包括一个或多个命令，以逗号分隔：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

每个 command 值应该具有以下表格所示的形式。

Command Syntax	Meaning
`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 生效，mrr 标志也必须设置为 on。目前，MRR 的成本估算太悲观。因此，还需要 mrr_cost_based 设置为 off，以便使用 BKA。

更多信息，请参阅第 10.2.1.12 节，“块嵌套循环和批量键访问连接”。
块嵌套循环标志
- block_nested_loop (默认 on)
  
  控制哈希连接的使用，如同 BNL 和 NO_BNL 优化器提示。
更多信息，请参阅第 10.2.1.12 节，“块嵌套循环和批量键访问连接”。
条件过滤标志
- condition_fanout_filter (默认 on)
  
  控制条件过滤的使用。
更多信息，请参阅第 10.2.1.13 节，“条件过滤”。
派生条件推送标志
- derived_condition_pushdown (默认 on)
  
  控制派生条件推送。
有关更多信息，请参阅第 10.2.2.5 节，“派生条件推送优化”
派生表合并标志
- derived_merge (默认 on)
  
  控制派生表和视图合并到外部查询块。
该 derived_merge 标志控制优化器是否尝试将派生表、视图引用和公共表表达式合并到外部查询块中，假设没有其他规则阻止合并；例如，视图的 ALGORITHM 指令优先于 derived_merge 设置。默认情况下，该标志为 on，以启用合并。

有关更多信息，请参阅第 10.2.2.4 节，“使用合并或物化优化派生表、视图引用和公共表表达式”。
引擎条件推送标志
- engine_condition_pushdown (默认 on)
  
  控制引擎条件推送。
有关更多信息，请参阅第 10.2.1.5 节，“引擎条件推送优化”。
哈希连接标志
- hash_join (默认 on)
  
  在 MySQL 8.3 中没有效果。请使用 block_nested_loop 标志。
有关更多信息，请参阅第 10.2.1.4 节，“哈希连接优化”。
索引条件推送标志
- index_condition_pushdown (默认 on)
  
  控制索引条件推送。
有关更多信息，请参阅第 10.2.1.6 节，“索引条件推送优化”。
索引扩展标志
- use_index_extensions (默认 on)
  
  控制索引扩展的使用。
有关更多信息，请参阅第 10.3.10 节，“索引扩展的使用”。
索引合并标志
- index_merge (默认 on)
  
  控制所有索引合并优化。
- index_merge_intersection (默认 on)
  
  控制索引合并交叉访问优化。
- index_merge_sort_union (默认 on)
  
  控制索引合并排序-Union 访问优化。
- index_merge_union (默认 on)
  
  控制索引合并 Union 访问优化。
有关更多信息，请参阅第 10.2.1.3 节，“索引合并优化”。
索引可见性标志
- use_invisible_indexes (默认 off)
  
  控制不可见索引的使用。
有关更多信息，请参阅第 10.3.12 节，“不可见索引”。
限制优化标志
- 优先排序索引 (默认 on)
  
  控制在查询中具有 ORDER BY 或 GROUP BY 且具有 LIMIT 子句时，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他优化。该优化默认情况下总是执行，以便尽可能快速执行查询。
  
  因为该算法不能处理所有可能的情况（部分是由于假设数据分布总是大致均匀），因此在某些情况下该优化可能不可取。可以通过将 优先排序索引 标志设置为 off 来禁用该优化。
有关更多信息和示例，请参阅第 10.2.1.19 节，“LIMIT 查询优化”。
多范围读取标志
- mrr (默认 on)
  
  控制多范围读取策略。
- mrr_cost_based (默认 on)
  
  控制基于成本的 MRR，如果 mrr=on。
有关更多信息，请参阅第 10.2.1.11 节，“多范围读取优化”。
半连接标志
- duplicateweedout (默认 on)
  
  控制半连接 Duplicate Weedout 策略。
- firstmatch (默认 on)
  
  控制半连接 FirstMatch 策略。
- loosescan (默认 on)
  
  控制半连接 LooseScan 策略（不要与 GROUP BY 的 Loose Index Scan 混淆）。
- semijoin (默认 on)
  
  控制所有半连接策略。
  
  这也适用于反连接优化。
该 semijoin、firstmatch、loosescan 和 duplicateweedout 标志启用半连接策略控制。该 semijoin 标志控制是否使用半连接。如果设置为 on，则 firstmatch 和 loosescan 标志启用更细粒度的半连接策略控制。

如果 duplicateweedout 半连接策略被禁用，则只有在所有其他适用策略也被禁用时才使用。

如果 semijoin 和 materialization 都是 on，则半连接也使用物化化策略。这些标志默认情况下都是 on。

有关更多信息，请参阅使用半连接变换优化 IN 和 EXISTS 子查询谓词。
集合操作标志
- 哈希集合操作 (默认 on)
  
  启用集合操作的哈希表优化，例如EXCEPT和INTERSECT）; 默认启用。否则，将使用临时表基于去重复的方法，如前版本的 MySQL。
  
  可以使用set_operations_buffer_size系统变量控制该优化所使用的内存量；增加该值通常会导致这些操作的执行速度更快。
跳过扫描标志
- skip_scan（默认 on）
  
  控制跳过扫描访问方法的使用。
有关更多信息，请参阅跳过扫描范围访问方法。
子查询物化标志
- materialization（默认 on）
  
  控制物化（包括半连接物化）。
- subquery_materialization_cost_based（默认 on）
  
  使用基于成本的物化选择。
该materialization标志控制是否使用子查询物化。如果semijoin和materialization都为on，则半连接也将使用物化。这些标志默认为on。

该subquery_materialization_cost_based标志启用基于成本的子查询物化选择。如果该标志为on(默认值)，优化器将在子查询物化和IN-to-EXISTS子查询转换之间进行基于成本的选择。如果该标志为off，优化器将选择子查询物化而不是IN-to-EXISTS子查询转换。

有关更多信息，请参阅第 10.2.2 节，“优化子查询、派生表、视图引用和公共表表达式”。

子查询转换标志

subquery_to_derived（默认 off）

优化器可以将选择子查询转换为左外连接派生表。（取决于派生表的可空性，这可以进一步简化为内连接。）这可以应用于满足以下条件的子查询：

子查询不使用任何非确定性函数，例如RAND()。
子查询不是使用MIN()或MAX()重写的任何或所有子查询。
父查询不设置用户变量，因为重写可能会影响执行顺序，从而导致意外结果，如果变量在同一查询中多次访问。
子查询不应相关，即不引用外部查询中的表列或包含在外部查询中评估的聚合。

该优化也可以应用于不包含GROUP BY的表子查询，该子查询是IN、NOT IN、EXISTS或NOT EXISTS的参数。

该标志的默认值为 off，因为在大多数情况下，启用该优化不会产生明显的性能改善（并且在许多情况下甚至会使查询运行得更慢），但您可以通过将 subquery_to_derived 标志设置为 on 来启用该优化。它主要用于测试。

示例，使用标量子查询：

d
mysql> CREATE TABLE t1(a INT);

mysql> CREATE TABLE t2(a INT);

mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);

mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);

mysql> SELECT * FROM t1
    ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);
+------+
| a    |
+------+
|    3 |
|    4 |
+------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

mysql> SET @@optimizer_switch='subquery_to_derived=on';


mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';
+----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=on%' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

从执行 SHOW WARNINGS 后立即执行第二个 EXPLAIN 语句，可以看到，启用优化后，查询 SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) 将被重写为类似于这里所示的形式：

SELECT t1.a FROM t1
    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d
            WHERE t1.a > d.c;

示例，使用带有 IN (子查询) 的查询：

mysql> DROP TABLE IF EXISTS t1, t2;

mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);

mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
mysql> INSERT INTO t2
    ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             t1.a IN (SELECT t2.a + 1 FROM t2);
+------+------+
| a    | b    |
+------+------+
|    2 |   20 |
|    3 |   30 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 9
          ref: std2.t1.a
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

检查和简化 SHOW WARNINGS 的结果，执行 EXPLAIN 语句后，显示当 subquery_to_derived 标志启用时，查询 SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) 将被重写为类似于这里所示的形式：

SELECT a, b FROM t1
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d
    ON t1.a = d.e
    WHERE   t1.b < 0
            OR
            d.e IS NOT NULL;

示例，使用带有 EXISTS (子查询) 的查询，并使用与前一个示例相同的表和数据：

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    2 |   20 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

如果我们执行 SHOW WARNINGS，然后执行 EXPLAIN 语句，查询 SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)，并简化结果的第二行，我们可以看到它已经被重写为类似于这里所示的形式：

SELECT a, b FROM t1
LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d
ON t1.a + 1 = d.e2
WHERE   t1.b < 0
        OR
        d.e1 IS NOT NULL;

当您将值分配给 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,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    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,
                    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

PREV HOME UP NEXT