文档首页
MySQL 9.0 参考手册
相关文档
MySQL 9.0 发行说明
下载本手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
手册页 (TGZ) - 258.2Kb
手册页 (Zip) - 365.3Kb
信息 (Gzip) - 4.0Mb
信息 (Zip) - 4.0Mb
MySQL 9.0 参考手册  /  ...  /  可切换优化

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]...';

每个 命令 值应采用下表所示的形式之一。

命令语法 含义
默认 将每个优化重置为其默认值
opt_name=默认 将指定的优化设置为其默认值
opt_name=off 禁用指定的优化
opt_name=on 启用指定的优化

值中命令的顺序无关紧要，但如果存在 默认 命令，则会首先执行该命令。将 opt_name 标志设置为 默认 会将其设置为其默认值 on 或 off 中的任何一个。不允许在值中多次指定任何给定的 opt_name，否则会导致错误。值中的任何错误都会导致赋值失败并出现错误，从而使 optimizer_switch 的值保持不变。

以下列表描述了允许的 opt_name 标志名称，按优化策略分组

  • 批量键访问标志

    为了使 batched_key_access 在设置为 on 时生效，mrr 标志也必须为 on。目前，MRR 的成本估算过于悲观。因此，要使用 BKA，还需要将 mrr_cost_based 设置为 off。

    有关详细信息，请参阅第 10.2.1.12 节“块嵌套循环和批量键访问连接”。

  • 块嵌套循环标志

    有关详细信息，请参阅第 10.2.1.12 节“块嵌套循环和批量键访问连接”。

  • 条件过滤标志

    有关详细信息，请参阅第 10.2.1.13 节“条件过滤”。

  • 派生条件下推标志

    有关详细信息，请参阅第 10.2.2.5 节“派生条件下推优化”

  • 派生表合并标志

    • derived_merge (默认 on)

      控制将派生表和视图合并到外部查询块中。

    derived_merge 标志控制优化器是否尝试将派生表、视图引用和公用表表达式合并到外部查询块中，假设没有其他规则阻止合并；例如，视图的 ALGORITHM 指令优先于 derived_merge 设置。默认情况下，该标志为 on 以启用合并。

    有关更多信息，请参见第 10.2.2.4 节 “使用合并或物化优化派生表、视图引用和公用表表达式”。

  • 引擎条件下推标志

    有关更多信息，请参见第 10.2.1.5 节 “引擎条件下推优化”。

  • 哈希连接标志

    有关更多信息，请参见第 10.2.1.4 节 “哈希连接优化”。

  • 索引条件下推标志

    有关更多信息，请参见第 10.2.1.6 节 “索引条件下推优化”。

  • 索引扩展标志

    有关更多信息，请参见第 10.3.10 节 “使用索引扩展”。

  • 索引合并标志

    有关更多信息，请参见第 10.2.1.3 节 “索引合并优化”。

  • 索引可见性标志

    有关更多信息，请参见第 10.3.12 节 “不可见索引”。

  • 限制优化标志

    • prefer_ordering_index（默认值 on）

      控制在查询具有带 LIMIT 子句的 ORDER BY 或 GROUP BY 的情况下，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他一些优化。默认情况下，只要优化器确定使用它可以更快地执行查询，就会执行此优化。

      由于做出此决定的算法无法处理所有可能的情况（部分原因是假设数据的分布始终或多或少是均匀的），因此在某些情况下，此优化可能不可取。可以通过将 prefer_ordering_index 标志设置为 off 来禁用此优化。

    有关更多信息和示例，请参见第 10.2.1.19 节 “LIMIT 查询优化”。

  • 多范围读取标志

    • mrr（默认值 on）

      控制多范围读取策略。

    • mrr_cost_based（默认值 on）

      如果 mrr=on，则控制使用基于成本的 MRR。

    有关更多信息，请参见第 10.2.1.11 节 “多范围读取优化”。

  • 半连接标志

    • duplicateweedout（默认值 on）

      控制半连接重复数据删除策略。

    • firstmatch（默认值 on）

      控制半连接 FirstMatch 策略。

    • loosescan（默认值 on）

      控制半连接 LooseScan 策略（不要与 GROUP BY 的松散索引扫描混淆）。

    • semijoin（默认值 on）

      控制所有半连接策略。

      这也适用于反连接优化。

    semijoin、firstmatch、loosescan 和 duplicateweedout 标志可以控制半连接策略。semijoin 标志控制是否使用半连接。如果将其设置为 on，则 firstmatch 和 loosescan 标志可以更精细地控制允许的半连接策略。

    如果禁用 duplicateweedout 半连接策略，则除非所有其他适用策略也被禁用，否则不会使用它。

    如果 semijoin 和 materialization 均为 on，则半连接也会在适用时使用物化。默认情况下，这些标志为 on。

    有关更多信息，请参见使用半连接转换优化 IN 和 EXISTS 子查询谓词。

  • 集合操作标志

    • hash_set_operations（默认值 on）

      为涉及 EXCEPT 和 INTERSECT 的集合操作启用哈希表优化；默认情况下启用。否则，将使用基于临时表的去重，如以前的 MySQL 版本中那样。

      可以使用 set_operations_buffer_size 系统变量控制此优化用于哈希的内存量；增加此值通常会导致使用这些操作的语句的执行时间更快。

  • 跳过扫描标志

    • skip_scan（默认值 on）

      控制跳过扫描访问方法的使用。

    有关更多信息，请参见跳过扫描范围访问方法。

  • 子查询物化标志

    materialization 标志控制是否使用子查询物化。如果 semijoin 和 materialization 均为 on，则半连接也会在适用时使用物化。默认情况下，这些标志为 on。

    subquery_materialization_cost_based 标志可以控制在子查询物化和 IN 到 EXISTS 子查询转换之间进行选择。如果该标志为 on（默认值），则如果可以使用这两种方法，优化器将在子查询物化和 IN 到 EXISTS 子查询转换之间执行基于成本的选择。如果该标志为 off，则优化器会选择子查询物化而不是 IN 到 EXISTS 子查询转换。

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

  • 子查询转换标志

    • subquery_to_derived（默认值 off）

      在许多情况下，优化器能够将 SELECT、WHERE、JOIN 或 HAVING 子句中的标量子查询转换为派生表上的左外连接。（根据派生表的可空性，有时可以将其进一步简化为内连接。）这可以针对满足以下条件的子查询完成

      • 子查询不使用任何非确定性函数，例如 RAND()。

      • 子查询不是可以使用 MIN() 或 MAX() 重写的 ANY 或 ALL 子查询。

      • 父查询不设置用户变量，因为重写它可能会影响执行顺序，如果在同一查询中多次访问该变量，则可能导致意外结果。

      • 子查询不应相关联，也就是说，它不应引用外部查询中表中的列，也不应包含在外部查询中计算的聚合。

      此优化也可以应用于作为 IN、NOT IN、EXISTS 或 NOT EXISTS 的参数且不包含 GROUP 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

      正如在第二个 EXPLAIN 语句之后立即执行 SHOW WARNINGS 所示，启用优化后，查询 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

      在对此查询执行 EXPLAIN 后检查并简化 SHOW WARNINGS 的结果表明，当启用 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

      如果我们在启用 subquery_to_derived 时对查询 SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) 运行 EXPLAIN 后执行 SHOW WARNINGS，并简化结果的第二行，我们会看到它已被重写为类似于以下形式

      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;

      有关更多信息，请参阅 “第 10.2.2.4 节 “使用合并或物化优化派生表、视图引用和公用表表达式”，以及 “第 10.2.1.19 节 “LIMIT 查询优化” 和 使用半连接转换优化 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

如果服务器正在对某些查询使用索引合并联合或索引合并排序联合访问方法，并且您想检查优化器在没有它们的情况下是否可以执行得更好，请像这样设置变量值

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

上一页   首页   上一级   下一页
相关文档
MySQL 9.0 发行说明
下载本手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
手册页 (TGZ) - 258.2Kb
手册页 (Zip) - 365.3Kb
信息 (Gzip) - 4.0Mb
信息 (Zip) - 4.0Mb