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

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,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
1 row in set (0.00 sec)

要更改的值 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.12 节，“阻止嵌套循环和成批密钥访问连接”。
阻止嵌套循环标志
- block_nested_loop （默认on）
  控制 BNL 连接算法的使用。BNL在 MySQL 8.0.18 及更高版本中，这也控制哈希连接的使用，就像 NO_BNL优化器提示一样。在 MySQL 8.0.20 及更高版本中，从 MySQL 服务器中删除了块嵌套循环支持，并且此标志仅控制散列连接的使用，引用的优化器提示也是如此。
有关详细信息，请参阅第 8.2.1.12 节，“阻止嵌套循环和成批密钥访问连接”。
条件过滤标志
- condition_fanout_filter （默认on）
  控制条件过滤的使用。
有关详细信息，请参阅第 8.2.1.13 节，“条件过滤”。
派生条件下推标志
- derived_condition_pushdown （默认on）
  控制派生条件下推。
有关详细信息，请参阅第 8.2.2.5 节，“派生条件下推优化”
派生表合并标志
- derived_merge（默认 on）
  控制将派生表和视图合并到外部查询块中。
该derived_merge标志控制优化器是否尝试将派生表、视图引用和公用表表达式合并到外部查询块中，假设没有其他规则阻止合并；例如，ALGORITHM 视图指令优先于 derived_merge设置。默认情况下，该标志是on启用合并。
有关详细信息，请参阅第 8.2.2.4 节，“使用合并或实现优化派生表、视图引用和公用表表达式”。
发动机状况下推标志
- engine_condition_pushdown （默认on）
  控制发动机状态下推。
有关详细信息，请参阅第 8.2.1.5 节，“发动机状态下推优化”。
哈希连接标志
- hash_join（默认 on）
  仅在 MySQL 8.0.18 控制 hash join，对后续版本无效。在 MySQL 8.0.19 及更高版本中，要控制散列连接的使用，请改用 block_nested_loop 标志。
有关详细信息，请参阅第 8.2.1.4 节，“哈希连接优化”。
索引条件下推标志
- index_condition_pushdown （默认on）
  控制索引条件下推。
有关详细信息，请参阅第 8.2.1.6 节，“索引条件下推优化”。
索引扩展标志
- use_index_extensions （默认on）
  控制索引扩展的使用。
有关详细信息，请参阅第 8.3.10 节，“索引扩展的使用”。
索引合并标志
- index_merge（默认 on）
  控制所有索引合并优化。
- index_merge_intersection （默认on）
  控制索引合并交叉访问优化。
- index_merge_sort_union （默认on）
  控制索引合并排序联合访问优化。
- index_merge_union （默认on）
  控制索引合并联合访问优化。
有关详细信息，请参阅第 8.2.1.3 节，“索引合并优化”。
索引可见性标志
- use_invisible_indexes （默认off）
  控制不可见索引的使用。
有关详细信息，请参阅第 8.3.12 节，“不可见索引”。
限制优化标志
- prefer_ordering_index （默认on）
  控制在具有 ORDER BYor子句GROUP BY的查询的情况下LIMIT，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他一些优化。只要优化器确定使用它可以更快地执行查询，就会默认执行此优化。
  由于做出此决定的算法无法处理所有可能的情况（部分原因是假设数据分布总是或多或少均匀），因此在某些情况下可能不需要此优化。在 MySQL 8.0.21 之前，无法禁用此优化，但在 MySQL 8.0.21 及更高版本中，虽然它仍然是默认行为，但可以通过将 prefer_ordering_index 标志设置为来禁用它off。
有关更多信息和示例，请参阅第 8.2.1.19 节，“LIMIT 查询优化”。
多范围读取标志
- mrr（默认 on）
  控制多范围读取策略。
- mrr_cost_based （默认on）
  如果，则控制基于成本的 MRR 的使用 mrr=on。
有关详细信息，请参阅第 8.2.1.11 节，“多范围读取优化”。
半连接标志
- duplicateweedout （默认on）
  控制 semijoin Duplicate Weedout 策略。
- firstmatch（默认 on）
  控制半连接 FirstMatch 策略。
- loosescan（默认 on）
  控制半连接 LooseScan 策略（不要与 Loose Index Scan for 混淆GROUP BY）。
- semijoin（默认 on）
  控制所有半连接策略。
  在 MySQL 8.0.17 及更高版本中，这也适用于反连接优化。
、semijoin、 firstmatch和标志可以控制半连接策略loosescan。 duplicateweedout该 semijoin标志控制是否使用半连接。如果它设置为 on，则 firstmatch和 loosescan标志可以更好地控制允许的半连接策略。
如果duplicateweedout 半连接策略被禁用，除非所有其他适用的策略也被禁用，否则它不会被使用。
如果semijoin和 materialization都是 on，则半连接也会在适用的情况下使用具体化。这些标志是on默认的。
有关详细信息，请参阅第 8.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”。
跳过扫描标志
- skip_scan（默认 on）
  控制跳过扫描访问方法的使用。
有关详细信息，请参阅跳过扫描范围访问方法。
子查询实现标志
- materialization （默认on）
  控制物化（包括半连接物化）。
- subquery_materialization_cost_based （默认on）
  使用基于成本的物化选择。
该materialization标志控制是否使用子查询实现。如果 semijoin和 materialization都是 on，则半连接也会在适用的情况下使用具体化。这些标志是on默认的。
该 subquery_materialization_cost_based 标志可以控制子查询实现和子查询转换之间的 IN选择EXISTS。如果标志是（默认值），优化器在子查询实现和子查询转换on之间执行基于成本的选择（如果可以使用任何一种方法）。如果标志是，优化器选择子查询实现而不是子查询转换。 INEXISTSoffINEXISTS
有关详细信息，请参阅第 8.2.2 节，“优化子查询、派生表、视图引用和公用表表达式”。

子查询转换标志

subquery_to_derived （默认off）

从 MySQL 8.0.21 开始，优化器在许多情况下能够将、、或子句中的标量子查询转换 SELECT为WHERE派生 JOIN表HAVING 上的左外连接。（根据派生表的可空性，这有时可以进一步简化为内部联接。）这可以用于满足以下条件的子查询：

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

在 MySQL 8.0.22 之前，子查询不能包含 GROUP BY子句。

此优化还可以应用于表子查询，该子查询是、、或的参数IN，不包含 . NOT INEXISTSNOT EXISTSGROUP BY

此标志的默认值为 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 (subquery)

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 (subquery)

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)subquery_to_derived

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;

有关详细信息，请参阅第 8.2.2.4 节，“使用合并或实现优化派生表、视图引用和公用表表达式”，以及第 8.2.1.19 节，“LIMIT 查询优化”和第 8.2.2.1 节，“优化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,
                    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