batched_key_access（默认 off） batched_key_access (default off)

控制 BKA 连接算法的使用。Controls use of BKA join algorithm.

block_nested_loop（默认 on） block_nested_loop (default on)

控制哈希连接的使用，与 BNL 和 NO_BNL 优化器提示作用相同。Controls use of hash joins, as do the BNL and NO_BNL optimizer hints.

condition_fanout_filter（默认 on） condition_fanout_filter (default on)

控制条件过滤的使用。Controls use of condition filtering.

derived_condition_pushdown（默认 on） derived_condition_pushdown (default on)

控制派生条件下推。Controls derived condition pushdown.

derived_merge（默认 on） derived_merge (default on)

控制派生表和视图合并到外部查询块中。Controls merging of derived tables and views into outer query block.

engine_condition_pushdown（默认 on） engine_condition_pushdown (default on)

控制发动机状态下推。Controls engine condition pushdown.

hash_join（默认 on） hash_join (default on)

在 MySQL 8.4 中无效。请改用 block_nested_loop 标志。Has no effect in MySQL 8.4. Use the block_nested_loop flag, instead.

index_condition_pushdown（默认 on） index_condition_pushdown (default on)

控制索引条件下推。Controls index condition pushdown.

use_index_extensions（默认 on） use_index_extensions (default on)

控制索引扩展的使用。Controls use of index extensions.

index_merge（默认 on） index_merge (default on)

控制所有索引合并优化。Controls all Index Merge optimizations.

index_merge_intersection（默认 on） index_merge_intersection (default on)

控制索引合并交集访问优化。Controls the Index Merge Intersection Access optimization.

index_merge_sort_union（默认 on） index_merge_sort_union (default on)

控制索引合并排序-联合访问优化。Controls the Index Merge Sort-Union Access optimization.

index_merge_union（默认 on） index_merge_union (default on)

控制索引合并联合访问优化。Controls the Index Merge Union Access optimization.

use_invisible_indexes（默认 off） use_invisible_indexes (default off)

控制不可见索引的使用。Controls use of invisible indexes.

prefer_ordering_index（默认 on） prefer_ordering_index (default on)

控制在查询包含 ORDER BY 或 GROUP BY 并带有 LIMIT 子句的情况下，优化器是否尝试使用有序索引，而不是无序索引、文件排序或其他优化。每当优化器判断使用该优化可以加快查询执行时，将默认执行此优化。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 optimization is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.

由于用于做出此决定的算法无法处理所有可能的情况（部分原因是其假设数据的分布总是或多或少均匀的），因此在某些情况下，这种优化可能并不理想。可以通过将 prefer_ordering_index 标志设置为 off 来禁用此优化。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. This optimization can be disabled by setting the prefer_ordering_index flag to off.

mrr（默认 on） mrr (default on)

控制多范围读取策略。Controls the Multi-Range Read strategy.

mrr_cost_based（默认 on） mrr_cost_based (default on)

如果 mrr=on，则控制基于成本的 MRR 使用。Controls use of cost-based MRR if mrr=on.

duplicateweedout（默认 on） duplicateweedout (default on)

控制半连接重复消除策略。Controls the semijoin Duplicate Weedout strategy.

firstmatch（默认 on） firstmatch (default on)

控制半连接的首匹配策略。Controls the semijoin FirstMatch strategy.

loosescan (默认 on) loosescan (default on)

控制半连接 LooseScan 策略（不要与用于 GROUP BY 的松散索引扫描混淆）。Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for GROUP BY).

semijoin (默认 on) semijoin (default on)

控制所有半连接策略。Controls all semijoin strategies.

这也适用于反连接优化。This also applies to the antijoin optimization.

hash_set_operations（默认 on） hash_set_operations (default on)

启用针对涉及 EXCEPT 和 INTERSECT 的集合操作的哈希表优化；默认启用。否则，将使用基于临时表的去重方法，如 MySQL 之前的版本中所使用。Enables the hash table optimization for set operations involving EXCEPT and INTERSECT); enabled by default. Otherwise, temporary table based de-duplication is used, as in previous versions of MySQL.

此优化使用的哈希内存量可以通过 set_operations_buffer_size 系统变量进行控制；通常增加此值会使使用这些操作的语句执行时间更短。The amount of memory used for hashing by this optimization can be controlled using the set_operations_buffer_size system variable; increasing this generally results in faster execution times for statements using these operations.

skip_scan（默认 on） skip_scan (default on)

控制跳过扫描访问方法的使用。Controls use of Skip Scan access method.

materialization（默认 on） materialization (default on)

控制物化（包括半连接物化）。Controls materialization (including semijoin materialization).

subquery_materialization_cost_based（默认 on） subquery_materialization_cost_based (default on)

使用基于成本的物化选择。Use cost-based materialization choice.

subquery_to_derived（默认 off） subquery_to_derived (default off)

优化器在许多情况下能够将 SELECT、WHERE、JOIN 或 HAVING 子句中的标量子查询转换为衍生表的左外连接。（根据衍生表的可空性，这有时可以进一步简化为内连接。）这可以用于满足以下条件的子查询：The optimizer is able in many cases to transform a scalar subquery in a SELECT, WHERE, JOIN, or HAVING clause into a left outer join on a derived table. (Depending on the nullability of the derived table, this can sometimes be simplified further to an inner join.) This can be done for a subquery which meets the following conditions:

这个优化也可以应用于作为 IN、NOT IN、EXISTS 或 NOT EXISTS 参数的表子查询，这些子查询不包含 GROUP BY。This optimization can also be applied to a table subquery which is the argument to IN, NOT IN, EXISTS, or NOT EXISTS, that does not contain a GROUP BY.

此标志的默认值为 off，因为在大多数情况下，启用此优化不会产生显著的性能提升（在许多情况下甚至可能使查询运行得更慢），但你可以通过将 subquery_to_derived 标志设置为 on 来启用该优化。它主要用于测试。The default value for this flag is off, since, in most cases, enabling this optimization does not produce any noticeable improvement in performance (and in many cases can even make queries run more slowly), but you can enable the optimization by setting the subquery_to_derived flag to on. It is primarily intended for use in testing.

示例，使用标量子查询：Example, using a scalar subquery:

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) 会以类似于此处所示的形式被重写：As can be seen from executing SHOW WARNINGS immediately following the second EXPLAIN statement, with the optimization enabled, the query SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) is rewritten in a form similar to what is shown here:

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

示例，使用带有 IN (subquery) 的查询：Example, using a query with 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

在执行EXPLAIN 对此查询后，检查并简化SHOW WARNINGS的结果显示，当启用subquery_to_derived 标志时，SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) 会被重写为类似此处所示的形式：Checking and simplifying the result of SHOW WARNINGS after executing EXPLAIN on this query shows that, when the subquery_to_derived flag enabled, SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) is rewritten in a form similar to what is shown here:

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) 的查询，以及与前一个示例相同的表和数据：Example, using a query with EXISTS (subquery) and the same tables and data as in the previous example:

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 后，在查询 SHOW WARNINGS 上运行 EXPLAIN 后执行 SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)，并简化结果的第二行，我们可以看到它已被重写为类似于这样的形式：If we execute SHOW WARNINGS after running EXPLAIN on the query SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) when subquery_to_derived has been enabled, and simplify the second row of the result, we see that it has been rewritten in a form which resembles this:

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 子查询谓词。For more information, see Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”, as well as Section 10.2.1.19, “LIMIT Query Optimization”, and Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations.