MySQL8-中文参考-三十六-

布客飞龙
72 阅读1小时+

MySQL8 中文参考（三十六）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

10.2.1.3 索引合并优化

索引合并访问方法通过多个range扫描检索行，并将它们的结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成底层扫描的并集、交集或并集的交集。

可能使用索引合并的示例查询：

SELECT * FROM *tbl_name* WHERE *key1* = 10 OR *key2* = 20;

SELECT * FROM *tbl_name*
  WHERE (*key1* = 10 OR *key2* = 20) AND *non_key* = 30;

SELECT * FROM t1, t2
  WHERE (t1.*key1* IN (1,2) OR t1.*key2* LIKE '*value*%')
  AND t2.*key1* = t1.*some_col*;

SELECT * FROM t1, t2
  WHERE t1.*key1* = 1
  AND (t2.*key1* = t1.*some_col* OR t2.*key2* = t1.*some_col2*);

注意

索引合并优化算法具有以下已知限制：

  • 如果您的查询具有复杂的WHERE子句，带有深层次的AND/OR嵌套，并且 MySQL 没有选择最佳计划，请尝试使用以下标识变换来分发术语：

    (*x* AND *y*) OR *z* => (*x* OR *z*) AND (*y* OR *z*)
(*x* OR *y*) AND *z* => (*x* AND *z*) OR (*y* AND *z*)

  • 索引合并不适用于全文索引。

在EXPLAIN输出中，索引合并方法显示为type列中的index_merge。在这种情况下，key列包含使用的索引列表，key_len包含这些索引的最长键部分的列表。

索引合并访问方法有几种算法，这些算法显示在EXPLAIN输出的Extra字段中：

  • 使用 intersect(...)

  • 使用 union(...)

  • 使用 sort_union(...)

以下部分更详细地描述了这些算法。优化器根据各种可用选项的成本估算，在不同可能的索引合并算法和其他访问方法之间进行选择。

  • 索引合并交集访问算法

  • 索引合并并集访问算法

  • 索引合并排序-并集访问算法

  • 影响索引合并优化

索引合并交集访问算法

当WHERE子句转换为与AND组合的不同键上的几个范围条件时，并且每个条件是以下之一时，可以应用此访问算法：

  • 这种形式的*N部分表达式，其中索引恰好有N*部分（即，所有索引部分都被覆盖）：

    *key_part1* = *const1* AND *key_part2* = *const2* ... AND *key_partN* = *constN*

  • InnoDB表的主键上的任何范围条件。

示例：

SELECT * FROM *innodb_table*
  WHERE *primary_key* < 10 AND *key_col1* = 20;

SELECT * FROM *tbl_name*
  WHERE *key1_part1* = 1 AND *key1_part2* = 2 AND *key2* = 2;

索引合并交集算法对所有使用的索引执行同时扫描，并生成从合并索引扫描接收到的行序列的交集。

如果查询中使用的所有列都由使用的索引覆盖，则不会检索完整的表行（在这种情况下，EXPLAIN输出在Extra字段中包含Using index）。以下是这种查询的示例：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引未覆盖查询中使用的所有列，则仅在满足所有使用键的范围条件时才检索完整行。

如果合并条件之一是InnoDB表的主键条件，则不用于检索行，而是用于过滤使用其他条件检索的行。

索引合并联合访问算法

该算法的标准与索引合并交集算法的标准类似。当表的WHERE子句转换为与OR组合的不同键上的多个范围条件，并且每个条件是以下条件之一时，该算法适用：

  • 这种形式的*N部分表达式，其中索引恰好有N*部分（即所有索引部分都被覆盖）：

    *key_part1* = *const1* OR *key_part2* = *const2* ... OR *key_partN* = *constN*

  • 任何InnoDB表的主键上的范围条件。

  • 适用于索引合并交集算法的条件。

例子：

SELECT * FROM t1
  WHERE *key1* = 1 OR *key2* = 2 OR *key3* = 3;

SELECT * FROM *innodb_table*
  WHERE (*key1* = 1 AND *key2* = 2)
     OR (*key3* = 'foo' AND *key4* = 'bar') AND *key5* = 5;
索引合并排序联合访问算法

当WHERE子句转换为由OR组合的多个范围条件时，此访问算法适用，但索引合并联合算法不适用。

例子：

SELECT * FROM *tbl_name*
  WHERE *key_col1* < 10 OR *key_col2* < 20;

SELECT * FROM *tbl_name*
  WHERE (*key_col1* > 10 OR *key_col2* = 20) AND *nonkey_col* = 30;

排序联合算法和联合算法之间的区别在于排序联合算法必须首先获取所有行的行 ID 并对其进行排序，然后才能返回任何行。

影响索引合并优化

使用索引合并取决于index_merge、index_merge_intersection、index_merge_union和index_merge_sort_union标志的optimizer_switch系统变量的值。请参阅第 10.9.2 节，“可切换优化”。默认情况下，所有这些标志都是on。要仅启用某些算法，请将index_merge设置为off，并仅启用应允许的其他算法。

除了使用optimizer_switch系统变量来全局控制 MySQL 对索引合并算法的使用之外，MySQL 还支持优化器提示以影响每个语句的优化器。请参阅第 10.9.3 节，“优化器提示”。

原文：dev.mysql.com/doc/refman/8.0/en/hash-joins.html

10.2.1.4 哈希连接优化

默认情况下，MySQL（8.0.18 及更高版本）尽可能使用哈希连接。可以使用BNL和NO_BNL优化器提示之一，或者将block_nested_loop=on或block_nested_loop=off作为 optimizer_switch 服务器系统变量设置的一部分来控制是否使用哈希连接。

注意

MySQL 8.0.18 支持在optimizer_switch中设置hash_join标志，以及优化器提示HASH_JOIN和NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些都不再起作用。

从 MySQL 8.0.18 开始，MySQL 为每个连接都具有等值连接条件的查询使用哈希连接，并且没有可以应用于任何连接条件的索引，就像这个例子一样：

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

当存在一个或多个可以用于单表谓词的索引时，也可以使用哈希连接。

哈希连接通常比块嵌套循环算法更快，并且在这种情况下应该使用哈希连接，而不是在以前版本的 MySQL 中使用的块嵌套循环算法（参见块嵌套循环连接算法）。从 MySQL 8.0.20 开始，不再支持块嵌套循环，服务器在以前可能使用块嵌套循环的地方使用哈希连接。

在刚刚显示的示例和本节中的其余示例中，我们假设使用以下语句创建了三个表t1、t2和t3：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

你可以通过使用EXPLAIN来查看哈希连接的使用情况，就像这样：

mysql> EXPLAIN
 -> SELECT * FROM t1
 ->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   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: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)

(在 MySQL 8.0.20 之前，有必要包含FORMAT=TREE选项，以查看给定连接是否使用了哈希连接。)

EXPLAIN ANALYZE还显示了使用的哈希连接的信息。

哈希连接也用于涉及多个连接的查询，只要每对表的至少一个连接条件是等值连接，就像这里显示的查询一样：

SELECT * FROM t1
    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 ON (t2.c1 = t3.c1);

在像刚刚显示的这种情况下，使用内连接时，任何不是等值连接的额外条件在连接执行后作为过滤器应用。（对于外连接，如左连接、半连接和反连接，它们作为连接的一部分打印。）这可以在EXPLAIN的输出中看到：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT *
 ->     FROM t1
 ->     JOIN t2
 ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
 ->     JOIN t3
 ->         ON (t2.c1 = t3.c1)\G
*************************** 1\. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
 -> Table scan on t3  (cost=0.35 rows=1)
 -> Hash
 -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
 -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

正如刚刚展示的输出所示，多个哈希连接可以用于具有多个等值连接条件的连接。

在 MySQL 8.0.20 之前，如果任何一对连接的表没有至少一个等值连接条件，则无法使用哈希连接，并且会使用较慢的块嵌套循环算法。在 MySQL 8.0.20 及更高版本中，在这种情况下使用哈希连接，如下所示：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT * FROM t1
 ->     JOIN t2 ON (t1.c1 = t2.c1)
 ->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)
 -> Inner hash join (no condition)  (cost=1.05 rows=1)
 -> Table scan on t3  (cost=0.35 rows=1)
 -> Hash
 -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

(本节稍后提供更多示例。)

当未指定连接条件时，哈希连接也适用于笛卡尔积，如下所示：

mysql> EXPLAIN FORMAT=TREE
 -> SELECT *
 ->     FROM t1
 ->     JOIN t2
 ->     WHERE t1.c2 > 50\G
*************************** 1\. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)
 -> Table scan on t1  (cost=0.35 rows=1)

在 MySQL 8.0.20 及更高版本中，连接不再需要至少包含一个等值连接条件才能使用哈希连接。这意味着可以使用哈希连接优化的查询类���包括以下列表中的查询（附有示例）：

  • 内部非等值连接：

    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)
 -> Inner hash join (no condition)  (cost=4.70 rows=12)
 -> Table scan on t2  (cost=0.08 rows=6)
 -> Hash
 -> Table scan on t1  (cost=0.85 rows=6)

  • 半连接：

    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
 ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1\. row ***************************
EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t1  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t2  (cost=0.35 rows=1)

  • 反连接：

    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
 ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
*************************** 1\. row ***************************
EXPLAIN: -> Hash antijoin (t1.c1 = t2.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1

  • 左外连接：

    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1\. row ***************************
EXPLAIN: -> Left hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
 -> Table scan on t1  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t2  (cost=0.35 rows=1)

  • 右外连接（请注意，MySQL 将所有右外连接重写为左外连接）：

    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1\. row ***************************
EXPLAIN: -> Left hash join (t1.c1 = t2.c1)  (cost=0.70 rows=1)
 -> Table scan on t2  (cost=0.35 rows=1)
 -> Hash
 -> Table scan on t1  (cost=0.35 rows=1)

默认情况下，MySQL 8.0.18 及更高版本在可能的情况下始终使用哈希连接。可以使用BNL和NO_BNL优化器提示之一来控制是否使用哈希连接。

（MySQL 8.0.18 支持hash_join=on或hash_join=off作为optimizer_switch服务器系统变量设置的一部分，以及优化器提示HASH_JOIN或NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些不再起作用。）

可以使用join_buffer_size系统变量来控制哈希连接的内存使用情况；哈希连接不能使用超过此数量的内存。当哈希连接所需的内存超过可用量时，MySQL 会通过在磁盘上使用文件来处理此问题。如果发生这种情况，您应该意识到，如果哈希连接无法适应内存并且创建的文件多于为open_files_limit设置的数量，则连接可能不会成功。为避免此类问题，请进行以下更改之一：

  • 增加join_buffer_size，以使哈希连接不会溢出到磁盘。

  • 增加open_files_limit。

从 MySQL 8.0.18 开始，为哈希连接分配增量式连接缓冲区；因此，您可以将join_buffer_size设置得更高，而不会使小查询分配大量 RAM，但外连接会分配整个缓冲区。在 MySQL 8.0.20 及更高版本中，哈希连接也用于外连接（包括反连接和半连接），因此这不再是一个问题。

原文：dev.mysql.com/doc/refman/8.0/en/engine-condition-pushdown-optimization.html

10.2.1.5 引擎条件下推优化

这种优化提高了非索引列与常量之间直接比较的效率。在这种情况下，条件被“下推”到存储引擎进行评估。这种优化只能由NDB存储引擎使用。

对于 NDB Cluster，这种优化可以消除在集群的数据节点和发出查询的 MySQL 服务器之间发送不匹配行的需要，并且可以加快使用的查询速度，速度提高了 5 到 10 倍，超过了可以但未使用条件下推的情况。

假设一个 NDB Cluster 表定义如下：

CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDB;

引擎条件下推可以与如下所示的查询一起使用，其中包括非索引列与常量之间的比较：

SELECT a, b FROM t1 WHERE b = 10;

引擎条件下推的使用可以在EXPLAIN的输出中看到：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE b = 10\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition

然而，引擎条件下推 不能 与以下查询一起使用：

SELECT a,b FROM t1 WHERE a = 10;

引擎条件下推在这里不适用，因为列 a 上存在索引。（索引访问方法更有效，因此会选择索引访问方法而不是条件下推。）

当索引列使用 > 或 < 运算符与常量进行比较时，也可以使用引擎条件下推：

mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition

引擎条件下推的其他支持比较包括以下内容：

  • *column* [NOT] LIKE *pattern*

    pattern 必须是包含要匹配的模式的字符串文字；有关语法，请参见第 14.8.1 节，“字符串比较函数和运算符”。

  • *column* IS [NOT] NULL

  • *column* IN (*value_list*)

    value_list 中的每个项目必须是常量、文字值。

  • *column* BETWEEN *constant1* AND *constant2*

    constant1 和 constant2 必须是常量、文字值。

在前述列表中的所有情况中，条件可以转换为一个或多个列与常量之间的直接比较形式。

引擎条件下推默认启用。要在服务器启动时禁用它，请将optimizer_switch系统变量的engine_condition_pushdown标志设置为 off。例如，在 my.cnf 文件中，使用以下行：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

在运行时，像这样禁用条件下推：

SET optimizer_switch='engine_condition_pushdown=off';

限制。 引擎条件下推受以下限制：

  • 引擎条件下推仅受支持NDB存储引擎。

  • 在 NDB 8.0.18 之前，列只能与常量或计算为常量值的表达式进行比较。在 NDB 8.0.18 及更高版本中，只要它们具有完全相同的类型，包括相同的符号、长度、字符集、精度和比例，这些都适用，列就可以相互比较。

  • 用于比较的列不能是任何BLOB或TEXT类型。这个排除范围还包括JSON、BIT和ENUM列。

  • 与列进行比较的字符串值必须使用与列相同的排序规则。

  • 不支持直接连接；尽可能将涉及多个表的条件分开推送。使用扩展的EXPLAIN输出来确定实际被推送的条件。参见 Section 10.8.3, “Extended EXPLAIN Output Format”。

以前，引擎条件推送仅限于引用来自正在被推送条件的相同表的列值的术语。从 NDB 8.0.16 开始，还可以从查询计划中较早的表中引用推送条件的列值。这减少了 SQL 节点在连接处理期间必须处理的行数。过滤也可以在 LDM 线程中并行执行，而不是在单个mysqld进程中。这有可能显着提高查询的性能。

从 NDB 8.0.20 开始，如果在相同连接嵌套中使用的任何表上没有无法推送的条件，或者在其上依赖的连接嵌套中没有无法推送的条件，则可以推送使用扫描的外连接。对于半连接也是如此，前提是所采用的优化策略是firstMatch（参见 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”）。

在以下两种情况下，连接算法不能与引用先前表的列结合使用：

  1. 当任何被引用的先前表在连接缓冲区中时。在这种情况下，从扫描过滤表中检索的每一行都与缓冲区中的每一行匹配。这意味着在生成扫描过滤时无法从单个特定行中获取列值。

  2. 当列来自推送连接中的子操作时。这是因为在生成扫描过滤时，尚未检索到连接中祖先操作引用的行。

从 NDB 8.0.27 开始，可以将连接中祖先表的列下推，前提是它们符合先前列出的要求。以下是一个使用先前创建的表 t1 的查询示例：

mysql> EXPLAIN 
 ->   SELECT * FROM t1 AS x 
 ->   LEFT JOIN t1 AS y 
 ->   ON x.a=0 AND y.b>=3\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: y
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using pushed condition (`test`.`y`.`b` >= 3); Using join buffer (hash join) 2 rows in set, 2 warnings (0.00 sec)

原文：dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

10.2.1.6 索引条件推送优化

索引条件推送（ICP）是一种优化，用于 MySQL 使用索引从表中检索行的情况。没有 ICP，存储引擎遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，MySQL 服务器评估行的WHERE条件。启用 ICP 后，如果WHERE条件的部分可以仅通过索引列进行评估，MySQL 服务器将此部分WHERE条件向下推送到存储引擎。然后存储引擎通过使用索引条目评估推送的索引条件，仅当满足条件时才从表中读取行。ICP 可以减少存储引擎必须访问基表的次数，以及 MySQL 服务器必须访问存储引擎的次数。

索引条件推送优化的适用性取决于以下条件：

  • 当需要访问完整表行时，ICP 用于range、ref、eq_ref和ref_or_null访问方法。

  • ICP 可用于InnoDB和MyISAM表，包括分区的InnoDB和MyISAM表。

  • 对于InnoDB表，ICP 仅用于辅助索引。ICP 的目标是减少完整行读取的次数，从而减少 I/O 操作。对于InnoDB聚簇索引，完整记录已经读入InnoDB缓冲区。在这种情况下使用 ICP 不会减少 I/O。

  • ICP 不支持在虚拟生成列上创建的辅助索引。InnoDB支持虚拟生成列上的辅助索引。

  • 不能向下推送涉及子查询的条件。

  • 不能向涉及存储函数的条件推送。存储引擎无法调用存储函数。

  • 触发条件无法向下推送。（有关触发条件的信息，请参见第 10.2.2.3 节，“使用 EXISTS 策略优化子查询”。）

  • （MySQL 8.0.30 及更高版本：）条件无法向包含对系统变量引用的派生表推送。

要了解这种优化如何工作，首先考虑当未使用索引条件推送优化时索引扫描的进行方式：

  1. 首先通过读取索引元组获取下一行，然后使用索引元组定位并读取完整的表行。

  2. 测试适用于此表的WHERE条件部分。根据测试结果接受或拒绝行。

使用索引条件下推，扫描如下进行：

  1. 获取下一行的索引元组（但不是完整的表行）。

  2. 测试适用于此表且仅可以使用索引列进行检查的WHERE条件的部分。如果条件不满足，则继续到下一行的索引元组。

  3. 如果条件满足，使用索引元组定位并读取完整的表行。

  4. 测试适用于此表的WHERE条件的剩余部分。根据测试结果接受或拒绝行。

当使用索引条件下推时，EXPLAIN输出在Extra列中显示Using index condition。它不显示Using index，因为当必须读取完整的表行时，这不适用。

假设一个表包含有关人员及其地址的信息，并且该表具有定义为INDEX (zipcode, lastname, firstname)的索引。如果我们知道一个人的zipcode值，但不确定姓氏，我们可以这样搜索：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL 可以使用索引扫描具有zipcode='95054'的人员。第二部分（lastname LIKE '%etrunia%'）无法用于限制必须扫描的行数，因此没有索引条件下推，此查询必须检索所有具有zipcode='95054'的人员的完整表行。

使用索引条件下推，MySQL 在读取完整的表行之前检查lastname LIKE '%etrunia%'部分。这避免了读取与zipcode条件匹配但不匹配lastname条件的索引元组对应的完整行。

索引条件下推默认启用。可以通过设置optimizer_switch系统变量来控制，设置index_condition_pushdown标志：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

参见第 10.9.2 节，“可切换优化”。

原文：dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html

10.2.1.7 嵌套循环连接算法

MySQL 使用嵌套循环算法或其变体来执行表之间的连接。

  • 嵌套循环连接算法

  • 块嵌套循环连接算法

嵌套循环连接算法

简单的嵌套循环连接（NLJ）算法从第一个表中逐行读取行，将每行传递给处理连接中下一个表的嵌套循环。这个过程重复进行，直到所有要连接的表都处理完为止。

假设要使用以下连接类型执行三个表t1、t2和t3之间的连接：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单的 NLJ 算法，则连接的处理方式如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

因为 NLJ 算法将行逐个从外部循环传递到内部循环，所以通常会多次读取在内部循环中处理的表。

块嵌套循环连接算法

块嵌套循环（BNL）连接算法使用缓冲区来减少内部循环中读取表的次数。例如，如果将 10 行读入缓冲区并将缓冲区传递给下一个内部循环，那么内部循环中读取的每一行都可以与缓冲区中的所有 10 行进行比较。这将使内部表的读取次数减少一个数量级。

在 MySQL 8.0.18 之前，当无法使用索引时，此算法用于等值连接；在 MySQL 8.0.18 及更高版本中，在这种情况下使用哈希连接优化。从 MySQL 8.0.20 开始，MySQL 不再使用块嵌套循环，并且在以前使用块嵌套循环的所有情况下都使用哈希连接。参见第 10.2.1.4 节“哈希连接优化”。

MySQL 连接缓冲具有以下特点：

  • 当连接的类型为ALL或index（换句话说，无法使用任何可能的键，并且需要进行完全扫描，无论是数据行还是索引行），或者range时，可以使用连接缓冲。连接缓冲也适用于外连接，如第 10.2.1.12 节“块嵌套循环和批量键访问连接”中所述。

  • 为第一个非常量表不分配连接缓冲区，即使它的类型为ALL或index。

  • 仅将连接中感兴趣的列存储在其连接缓冲区中，而不是整行。

  • join_buffer_size 系统变量确定用于处理查询的每个连接缓冲区的大小。

  • 为每个可以缓冲的连接分配一个缓冲区，因此一个给定的查询可能会使用多个连接缓冲区。

  • 在执行连接之前分配连接缓冲区，并在查询完成后释放。

对于之前描述的 NLJ 算法的示例连接（不使用缓冲），使用连接缓冲区进行连接如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

如果 S 是连接缓冲区中每个存储的 t1, t2 组合的大小，C 是缓冲区中组合的数量，则表 t3 被扫描的次数为：

(*S* * *C*)/join_buffer_size + 1

当 join_buffer_size 的值增加时，t3 扫描的次数会减少，直到 join_buffer_size 足够大以容纳所有先前的行组合为止。在那一点上，通过增大它不会获得速度上的提升。

原文：dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html

10.2.1.8 嵌套连接优化

表达连接的语法允许嵌套连接。以下讨论涉及第 15.2.13.2 节，“JOIN 子句”中描述的连接语法。

与 SQL 标准相比，table_factor的语法有所扩展。后者仅接受table_reference，而不是在一对括号内列出它们。如果我们将*table_reference*项目列表中的每个逗号视为等同于内连接，则这是一种保守的扩展。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

等同于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在 MySQL 中，CROSS JOIN在语法上等同于INNER JOIN；它们可以互换使用。在标准 SQL 中，它们不等效。INNER JOIN与ON子句一起使用；否则使用CROSS JOIN。

一般来说，可以忽略仅包含内连接操作的连接表达式中的括号。考虑这个连接表达式：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

删除括号并将操作分组到左侧后，该连接表达式转换为以下表达式：

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

然而，这两个表达式并不等效。为了看到这一点，假设表t1，t2和t3具有以下状态：

  • 表t1包含行(1)，(2)

  • 表t2包含行(1,101)

  • 表t3包含行(101)

在这种情况下，第一个表达式返回包含行(1,1,101,101)，(2,NULL,NULL,NULL)的结果集，而第二个表达式返回行(1,1,101,101)，(2,NULL,NULL,101)：

mysql> SELECT *
       FROM t1
            LEFT JOIN
            (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
            LEFT JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

在以下示例中，外连接操作与内连接操作一起使用：

t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

该表达式无法转换为以下表达式：

t1 LEFT JOIN t2 ON t1.a=t2.a, t3

对于给定的表状态，这两个表达式返回不同的行集：

mysql> SELECT *
       FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

因此，如果在具有外连接运算符的连接表达式中省略括号，可能会改变原始表达式的结果集。

更确切地说，在左外连接操作的右操作数和右连接操作的左操作数中不能忽略括号。换句话说，我们不能忽略外连接操作的内表达式的括号。其他操作数（外表的操作数）的括号可以忽略。

以下表达式：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

对于任何表t1,t2,t3和任何条件P，在属性t2.b和t3.b上：

t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)

每当连接表达式（joined_table）中连接操作的执行顺序不是从左到右时，我们谈论嵌套连接。考虑以下查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

这些查询被认为包含这些嵌套连接：

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

在第一个查询中，嵌套连接是通过左连接操作形成的。在第二个查询中，它是通过内连接操作形成的。

在第一个查询中，括号可以省略：连接表达式的语法结构决定了连接操作的执行顺序相同。对于第二个查询，不能省略括号，尽管这里的连接表达式可以在没有括号的情况下明确解释。在我们的扩展语法中，第二个查询中(t2, t3)的括号是必需的，尽管理论上可以在没有它们的情况下解析查询：我们仍然会对查询有一个明确的语法结构，因为LEFT JOIN和ON扮演了表达式(t2,t3)的左右定界符的角色。

前面的例子演示了这些要点：

  • 对于仅涉及内连接（而不是外连接）的连接表达式，可以去掉括号并从左到右评估连接。实际上，表可以以任何顺序评估。

  • 一般来说，对于外连接或混合内连接的外连接，去掉括号可能会改变结果。

具有嵌套外连接的查询以与具有内连接相同的管道方式执行。更确切地说，利用了嵌套循环连接算法的一个变体。回想一下嵌套循环连接执行查询的算法（参见第 10.2.1.7 节，“嵌套循环连接算法”）。假设一个涉及 3 个表T1,T2,T3的连接查询具有以下形式：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3)

这里，P1(T1,T2)和P2(T3,T3)是一些连接条件（基于表达式），而P(T1,T2,T3)是关于表T1,T2,T3列的条件。

嵌套循环连接算法将以以下方式执行此查询：

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

表示通过连接行t1, t2和t3的列构造的行的符号t1||t2||t3。在以下一些示例中，表名出现的地方的NULL表示使用NULL作为该表的每列。例如，t1||t2||NULL表示通过连接行t1和t2的列，并为t3的每列使用NULL构造的行。这样的行被称为NULL-补充。

现在考虑一个具有嵌套外连接的查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3)

对于这个查询，修改嵌套循环模式以获得：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

一般来说，在外连接操作的第一个内表的嵌套循环中，引入了一个标志，在循环之前关闭，在循环之后检查。当在外表的当前行中找到来自表示内操作数的表的匹配时，该标志被打开。如果在循环周期结束时标志仍然关闭，则没有找到外表当前行的匹配。在这种情况下，行将通过为内表的列补充NULL值。结果行将传递到输出的最终检查或下一个嵌套循环中，但仅当该行满足所有嵌套外连接的连接条件时。

在示例中，以下表达式表示的外连接表被嵌入：

(T2 LEFT JOIN T3 ON P2(T2,T3))

对于带有内连接的查询，优化器可以选择不同顺序的嵌套循环，例如这样一个顺序：

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

对于带有外连接的查询，优化器只能选择外表的循环在内表的循环之前的顺序。因此，对于我们带有外连接的查询，只有一种嵌套顺序是可能的。对于以下查询，优化器评估了两种不同的嵌套。在这两种嵌套中，T1必须在外循环中处理，因为它用于外连接。T2和T3用于内连接，因此该连接必须在内循环中处理。然而，由于连接是内连接，T2和T3可以以任何顺序处理。

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

一个嵌套评估T2，然后是T3：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

另一个嵌套评估T3，然后是T2：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

在讨论内连接的嵌套循环算法时，我们省略了一些细节，这些细节对查询执行性能的影响可能是巨大的。我们没有提到所谓的“推送下推”条件。假设我们的WHERE条件P(T1,T2,T3)可以用一个合取公式表示：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

在这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带有内连接的查询：

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

您可以看到每个连接C1(T1)，C2(T2)，C3(T3)都被推出最内部循环到最外部循环，其中可以进行评估。如果C1(T1)是一个非常严格的条件，这种条件下推可能会大大减少传递给内部循环的表T1的行数。结果，查询的执行时间可能会极大地改善。

对于带有外连接的查询，WHERE条件只有在发现当前外表行在内表中有匹配时才会被检查。因此，将条件推出内部嵌套循环的优化不能直接应用于带有外连接的查询。在这里，我们必须引入由标志保护的条件推送下推谓词，当遇到匹配时这些标志被打开。

回想一下带有外连接的这个例子：

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

对于那个例子，使用受保护的推送下推条件的嵌套循环算法如下：

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

一般来说，推送下推谓词可以从连接条件中提取，例如P1(T1,T2)和P(T2,T3)。在这种情况下，推送下推谓词也受到一个标志的保护，该标志防止对由相应外连接操作生成的NULL-补充行进行谓词检查。

在相同的嵌套连接中，如果由WHERE条件引起，从一个内表到另一个内表的键访问是被禁止的。

原文：dev.mysql.com/doc/refman/8.0/en/outer-join-optimization.html

10.2.1.9 外连接优化

外连接包括LEFT JOIN和RIGHT JOIN。

MySQL 实现*A* LEFT JOIN *B* *join_specification*如下：

  • 表*B被设置为依赖于表A和A*依赖的所有表。

  • 表*A被设置为依赖于所有表（除了B*）在LEFT JOIN条件中使用的表。

  • LEFT JOIN条件用于决定如何从表*B*中检索行。（换句话说，WHERE子句中的任何条件都不会被使用。）

  • 执行所有标准连接优化，除了一个表始终在其依赖的所有表之后读取。如果存在循环依赖关系，则会出现错误。

  • 执行所有标准WHERE优化。

  • 如果*A中有一行与WHERE子句匹配，但B中没有一行与ON条件匹配，则会生成一个额外的B*行，其中所有列均设置为NULL。

  • 如果您使用LEFT JOIN查找在某个表中不存在的行，并且在WHERE部分中有以下测试：*col_name* IS NULL，其中*col_name*被声明为NOT NULL的列，那么 MySQL 在找到与LEFT JOIN条件匹配的一行后，停止搜索更多行（对于特定键组合）。

RIGHT JOIN的实现类似于LEFT JOIN，只是表的角色被颠倒。右连接被转换为等效的左连接，如 Section 10.2.1.10, “Outer Join Simplification”中所述。

对于LEFT JOIN，如果生成的NULL行的WHERE条件始终为假，则LEFT JOIN会转为内连接。例如，在以下查询中，如果t2.column1为NULL，则WHERE子句将为假：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，将查询转换为内连接是安全的：

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

在 MySQL 8.0.14 及更高版本中，常量文字表达式导致的平凡WHERE条件在准备期间被移除，而不是在优化的后期阶段，此时连接已经被简化。提前移除平凡条件允许优化器将外连接转换为内连接；这可以改善包含WHERE子句中包含平凡条件的外连接查询的计划，例如以下查询：

SELECT * FROM t1 LEFT JOIN t2 ON *condition_1* WHERE *condition_2* OR 0 = 1

优化器现在在准备期间看到 0 = 1 始终为假，使得OR 0 = 1多余，并将其删除，留下这样的内容：

SELECT * FROM t1 LEFT JOIN t2 ON *condition_1* where *condition_2*

现在优化器可以将查询重写为内连接，如下所示：

SELECT * FROM t1 JOIN t2 WHERE *condition_1* AND *condition_2*

现在优化器可以在表t1之前使用表t2，如果这样做可以得到更好的查询计划。要提供关于表连接顺序的提示，请使用优化器提示；参见第 10.9.3 节，“优化器提示”。或者，使用STRAIGHT_JOIN；参见第 15.2.13 节，“SELECT 语句”。然而，STRAIGHT_JOIN可能会阻止索引的使用，因为它禁用了半连接转换；参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”。

原文：dev.mysql.com/doc/refman/8.0/en/outer-join-simplification.html

10.2.1.10 外连接简化

查询的FROM子句中的表达式在许多情况下被简化。

在解析器阶段，具有右外连接操作的查询被转换为仅包含左连接操作的等效查询。在一般情况下，转换是这样执行的，即这个右连接：

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

变成这个等效的左连接：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

所有形式为T1 INNER JOIN T2 ON P(T1,T2)的内连接表达式都被替换为列表T1,T2，P(T1,T2)作为WHERE条件的一个连接被连接（或者连接的连接条件，如果有的话）。

当优化器评估外连接操作的计划时，它只考虑那些在每个这样的操作中，外部表在内部表之前被访问的计划。优化器的选择是有限的，因为只有这样的计划才能使用嵌套循环算法执行外连接。

考虑这种形式的查询，其中R(T2)大大缩小了与表T2匹配行的数量：

SELECT * T1 FROM T1
  LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

如果按照原样执行查询，优化器别无选择，只能在访问受限制较少的表T1之前访问受限制较多的表T2，这可能会产生一个非常低效的执行计划。

相反，如果WHERE条件被外连接操作拒绝，MySQL 会将查询转换为不包含外连接操作的查询。（也就是说，它将外连接转换为内连接。）如果条件对于为操作生成的任何NULL-补充行求值为FALSE或UNKNOWN，则称条件对于外连接操作被拒绝。

因此，对于这个外连接：

T1 LEFT JOIN T2 ON T1.A=T2.A

诸如这些条件之类的条件被拒绝，因为它们对于任何NULL-补充行（T2列设置为NULL）都不可能为真：

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

诸如这些条件之类的条件不被拒绝，因为它们可能对于一个NULL-补充行为真：

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

检查条件是否被外连接操作拒绝的一般规则很简单：

  • 它的形式是A IS NOT NULL，其中A是任何内部表的属性

  • 它是一个包含对内部表的引用的谓词，当其参数之一为NULL时求值为UNKNOWN

  • 它是一个包含被拒绝条件的合取作为一个连接

  • 它是一个被拒绝的条件的析取

一个条件可以被一个查询中的一个外连接操作拒绝，但对另一个查询中的另一个外连接操作不被拒绝。在这个查询中，WHERE条件对于第二个外连接操作被拒绝，但对于第一个外连接操作不被拒绝：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

如果WHERE条件在查询中被外连接操作拒绝，外连接操作将被替换为内连接操作。

例如，在前面的查询中，第二个外连接被空拒绝，可以被内连接替换：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

对于原始查询，优化器仅评估与单表访问顺序T1，T2，T3兼容的计划。对于重写后的查询，它还考虑了访问顺序T3，T1，T2。

一个外连接操作的转换可能会触发另一个的转换。因此，查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

首先被转换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

相当于查询：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

剩余的外连接操作也可以被内连接替换，因为条件T3.B=T2.B被拒绝为空。这导致一个没有任何外连接的查询：

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

有时优化器成功地替换了嵌套的外连接操作，但无法转换嵌套外连接。以下查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

被转换为：

SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

只能被重写为仍然包含嵌套外连接操作的形式：

SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0

任何尝试将查询中的嵌套外连接操作转换必须考虑嵌套外连接的连接条件以及WHERE条件。在这个查询中，WHERE条件对于嵌套外连接不被拒绝为空，但是嵌套外连接的连接条件T2.A=T1.A AND T3.C=T1.C被拒绝为空：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A AND T3.C=T1.C
  WHERE T3.D > 0 OR T1.D > 0

因此，查询可以转换为：

SELECT * FROM T1 LEFT JOIN
              (T2, T3)
              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
  WHERE T3.D > 0 OR T1.D > 0

原文：dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html

10.2.1.11 多范围读取优化

使用二级索引进行范围扫描读取行时，当表很大且未存储在存储引擎的缓存中时，可能会导致许多随机磁盘访问。通过磁盘扫描多范围读取（MRR）优化，MySQL 尝试通过首先仅扫描索引并收集相关行的键来减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后根据主键的顺序从基表中检索行。磁盘扫描 MRR 的动机是减少随机磁盘访问次数，而是实现对基表数据的更顺序扫描。

多范围读取优化提供以下好处：

  • MRR 使数据行能够按顺序访问，而不是按随机顺序，基于索引元组。服务器获取满足查询条件的一组索引元组，根据数据行 ID 顺序对其进行排序，并使用排序后的元组按顺序检索数据行。这使数据访问更高效，成本更低。

  • MRR 可以批量处理对需要通过索引元组访问数据行的操作的请求，例如范围索引扫描和使用索引进行等值连接的操作。MRR 遍历一系列索引范围以获取符合条件的索引元组。随着这些结果的累积，它们被用于访问相应的数据行。在开始读取数据行之前，不需要获取所有索引元组。

MRR 优化不支持在虚拟生成列上创建的二级索引。InnoDB 支持在虚拟生成列上的二级索引。

以下情景说明了何时可以优化 MRR：

情景 A：MRR 可以用于 InnoDB 和 MyISAM 表进行索引范围扫描和等值连接操作。

  1. 一部分索引元组被累积在缓冲区中。

  2. 缓冲区中的元组按其数据行 ID 进行排序。

  3. 数据行根据排序的索引元组序列进行访问。

情景 B：MRR 可以用于 NDB 表，用于多范围索引扫描或通过属性执行等值连接。

  1. 一部分范围，可能是单键范围，在提交查询的中央节点上累积在缓冲区中。

  2. 范围被发送到访问数据行的执行节点。

  3. 访问的行被打包成数据包发送回中央节点。

  4. 收到的带有数据行的数据包被放置在缓冲区中。

  5. 数据行从缓冲区中读取。

当使用 MRR 时，EXPLAIN 输出中的 Extra 列显示 Using MRR。

如果不需要访问完整的表行即可生成查询结果，则InnoDB和MyISAM不使用 MRR。如果结果可以完全基于索引元组中的信息生成（通过覆盖索引）；MRR 提供不了任何好处。

两个optimizer_switch系统变量标志提供了使用 MRR 优化的接口。mrr标志控制是否启用 MRR。如果mrr被启用（on），mrr_cost_based标志控制优化器是否尝试在使用和不使用 MRR 之间做出基于成本的选择（on），或者在可能的情况下始终使用 MRR（off）。默认情况下，mrr为on，mrr_cost_based为on。请参阅第 10.9.2 节，“可切换的优化”。

对于 MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为其缓冲区可以分配多少内存的指导。引擎最多使用read_rnd_buffer_size字节，并确定在单次传递中要处理的范围数量。

原文：dev.mysql.com/doc/refman/8.0/en/bnl-bka-optimization.html

10.2.1.12 块嵌套循环和批量键访问连接

在 MySQL 中，有一个批量键访问（BKA）连接算法，它同时使用对连接表的索引访问和连接缓冲。BKA 算法支持内连接、外连接和半连接操作，包括嵌套外连接。BKA 的好处包括由于更有效的表扫描而改进的连接性能。此外，之前仅用于内连接的块嵌套循环（BNL）连接算法被扩展，可以用于外连接和半连接操作，包括嵌套外连接。

以下各节讨论了原始 BNL 算法、扩展 BNL 算法和 BKA 算法的基础连接缓冲管理。有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

  • 用于块嵌套循环和批量键访问算法的连接缓冲管理

  • 用于外连接和半连接的块嵌套循环算法

  • 批量键访问连接

  • 块嵌套循环和批量键访问算法的优化提示

用于块嵌套循环和批量键访问算法的连接缓冲管理

MySQL 可以利用 join 缓冲来执行不仅内连接而且没有对内部表进行索引访问的外连接和半连接，这些连接出现在子查询展开之后。此外，当对内部表进行索引访问时，可以有效地使用 join 缓冲。

join 缓冲管理代码在存储感兴趣的行列的值时，稍微更有效地利用了 join 缓冲空间：如果一个行列的值是NULL，则不会为其分配额外的字节，对于VARCHAR类型的任何值，都会分配最少数量的字节。

代码支持两种类型的缓冲区，常规和增量。假设使用 join 缓冲B1连接表t1和t2，并且将此操作的结果使用 join 缓冲B2连接到表t3：

  • 一个常规的 join 缓冲包含每个连接操作数的列。如果B2是一个常规的 join 缓冲，那么放入B2的每一行r由来自B1的行r1的列和来自表t3的匹配行r2的感兴趣列组成。

  • 增量连接缓冲区仅包含由第二个连接操作数产生的表的行的列。也就是说，它是相对于第一个操作数缓冲区的增量。如果 B2 是一个增量连接缓冲区，它包含行 r2 的有趣列以及与 B1 中的行 r1 的链接。

增量连接缓冲区始终相对于较早连接操作的连接缓冲区是增量的，因此第一个连接操作的缓冲区始终是常规缓冲区。在刚才给出的示例中，用于连接表 t1 和 t2 的缓冲区 B1 必须是常规缓冲区。

用于连接操作的增量缓冲区的每一行仅包含要连接的表的行的有趣列。这些列与来自第一个连接操作数产生的表的匹配行的有趣列的引用一起增加。增量缓冲区中的多行可以引用相同的行 r，其列存储在先前的连接缓冲区中，只要所有这些行都匹配行 r。

增量缓冲区使得从先前连接操作中使用的缓冲区复制列的频率降低。这样可以节省缓冲区空间，因为在一般情况下，第一个连接操作数产生的行可以与第二个连接操作数产生的多行匹配。从第一个操作数复制一行的多个副本是不必要的。增量缓冲区还通过减少复制时间节省了处理时间。

在 MySQL 8.0 中，block_nested_loop 标志的 optimizer_switch 系统变量的工作方式如下：

  • 在 MySQL 8.0.20 之前，它控制优化器如何使用块嵌套循环连接算法。

  • 在 MySQL 8.0.18 及更高版本中，它还控制哈希连接的使用（参见 第 10.2.1.4 节，“哈希连接优化”）。

  • 从 MySQL 8.0.20 开始，该标志仅控制哈希连接，不再支持块嵌套循环算法。

batched_key_access 标志控制优化器如何使用批量键访问连接算法。

默认情况下，block_nested_loop 是 on，而 batched_key_access 是 off。参见 第 10.9.2 节，“可切换优化”。也可以应用优化提示；参见 块嵌套循环和批量键访问算法的优化提示。

有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

外连接和半连接的块嵌套循环算法

MySQL BNL 算法的原始实现已扩展以支持外连接和半连接操作（后来被哈希连接算法取代；请参见第 10.2.1.4 节，“哈希连接优化”）。

当这些操作与连接缓冲区一起执行时，放入缓冲区的每一行都附带一个匹配标志。

如果使用连接缓冲区执行外连接操作，则将第二个操作数生成的表的每一行与连接缓冲区中的每一行进行匹配检查。当找到匹配时，将形成一个新的扩展行（原始行加上第二个操作数的列），并发送给剩余的连接操作进行进一步扩展。此外，缓冲区中匹配的行的匹配标志将被启用。在检查完要连接的表的所有行之后，将扫描连接缓冲区。缓冲区中没有启用其匹配标志的每一行都将通过NULL补充（第二个操作数的每一列的NULL值）进行扩展，并发送给剩余的连接操作进行进一步扩展。

在 MySQL 8.0 中，block_nested_loop标志的optimizer_switch系统变量的工作方式如下：

  • 在 MySQL 8.0.20 之前，它控制优化器如何使用块嵌套循环连接算法。

  • 在 MySQL 8.0.18 及更高版本中，它还控制哈希连接的使用（请参见第 10.2.1.4 节，“哈希连接优化”）。

  • 从 MySQL 8.0.20 开始，该标志仅控制哈希连接，不再支持块嵌套循环算法。

更多信息，请参见第 10.9.2 节，“可切换的优化”。也可以应用优化器提示；请参见块嵌套循环和批量键访问算法的优化器提示。

在EXPLAIN输出中，当Extra值包含Using join buffer (Block Nested Loop)且type值为ALL、index或range时，表示对表使用 BNL。

有关半连接策略的信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”

批量键访问连接

MySQL 实现了一种称为批量键访问（BKA）连接算法的表连接方法。当第二个连接操作数产生的表具有索引访问时，可以应用 BKA。与 BNL 连接算法类似，BKA 连接算法使用连接缓冲区累积连接操作的第一个操作数产生的行的有趣列。然后，BKA 算法构建用于访问要连接的表的键，以便为缓冲区中的所有行提交这些键批量给数据库引擎进行索引查找。这些键通过多范围读取（MRR）接口提交给引擎（参见第 10.2.1.11 节，“多范围读取优化”）。提交键后，MRR 引擎函数以最佳方式在索引中执行查找，获取由这些键找到的连接表的行，并开始将匹配行提供给 BKA 连接算法。每个匹配行都与连接缓冲区中的行的引用相对应。

当使用 BKA 时，join_buffer_size 的值定义了每次请求到存储引擎的键批量有多大。缓冲区越大，对连接操作的右表进行的顺序访问就越多，这可以显著提高性能。

要使用 BKA，必须将optimizer_switch 系统变量的 batched_key_access 标志设置为 on。BKA 使用 MRR，因此 mrr 标志也必须为 on。目前，对于 MRR 的成本估算过于悲观。因此，还需要将 mrr_cost_based 设置为 off 才能使用 BKA。以下设置启用了 BKA：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MRR 函数执行有两种情况：

  • 第一种情况适用于传统基于磁盘的存储引擎，如InnoDB和MyISAM。对于这些引擎，通常将连接缓冲区中所有行的键一次性提交给 MRR 接口。引擎特定的 MRR 函数为提交的键执行索引查找，从中获取行 ID（或主键），然后通过 BKA 算法的请求逐个获取所有这些选定行 ID 的行。每行都返回一个关联引用，使得可以访问连接缓冲区中匹配的行。MRR 函数以最佳方式获取行：它们按行 ID（主键）顺序获取。这提高了性能，因为读取是按磁盘顺序而不是随机顺序进行的。

  • 第二种情况适用于远程存储引擎，如NDB。 MySQL 服务器（SQL 节点）将一部分连接缓冲区中的行的键包以及它们的关联关系发送到 MySQL 集群数据节点。作为回报，SQL 节点接收到一组（或多组）匹配行及其对应的关联关系。BKA 连接算法获取这些行并构建新的连接行。然后一组新的键被发送到数据节点，返回的包中的行被用来构建新的连接行。这个过程持续进行，直到连接缓冲区中的最后一个键被发送到数据节点，并且 SQL 节点已经接收并连接了所有与这些键匹配的行。这提高了性能，因为 SQL 节点发送给数据节点的带键包数量较少，意味着在 SQL 节点和数据节点之间执行连接操作的往返次数较少。

在第一种情况下，连接缓冲区的一部分被保留用于存储通过索引查找选择的行 ID（主键），并作为参数传递给 MRR 函数。

没有专门的缓冲区用于存储从连接缓冲区构建的键。相反，构建下一个缓冲区中行的键的函数被作为参数传递给 MRR 函数。

在EXPLAIN输出中，当Extra值包含Using join buffer (Batched Key Access)，并且type值为ref或eq_ref时，表示对表使用了 BKA。

用于块嵌套循环和批量键访问算法的优化器提示

除了使用optimizer_switch系统变量来控制全局会话中优化器使用 BNL 和 BKA 算法外，MySQL 还支持优化器提示来影响每个语句的优化器。请参阅第 10.9.3 节，“优化器提示”。

要使用 BNL 或 BKA 提示为外连接的任何内部表启用连接缓冲，必须为外连接的所有内部表启用连接缓冲。

原文：dev.mysql.com/doc/refman/8.0/en/condition-filtering.html

10.2.1.13 条件过滤

在连接处理中，前缀行是从一个连接中传递到下一个连接的行。 一般来说，优化器尝试尽早将前缀计数较低的表放在连接顺序中，以避免行组合数量迅速增加。 在优化器可以使用有关从一个表中选择并传递到下一个表的行的条件的信息的程度上，它可以更准确地计算行估计并选择最佳执行计划。

没有条件过滤，表的前缀行数是基于优化器根据选择的访问方法估计的WHERE子句选择的行数。 条件过滤使优化器能够使用WHERE子句中未被访问方法考虑的其他相关条件，并因此改进其前缀行数估计。 例如，即使可能存在可用于从当前表中选择行的基于索引的访问方法，也可能存在WHERE子句中对表的其他条件进行过滤（进一步限制）的情况，以过滤传递到下一个表的符合条件的行的估计。

仅当条件对过滤估计有贡献时才会计入：

  • 它指的是当前表。

  • 它取决于连接序列中较早表的常量值或值。

  • 它在访问方法中尚未考虑。

在EXPLAIN输出中，rows列指示所选访问方法的行估计，而filtered列反映条件过滤的效果。 filtered值以百分比表示。 最大值为 100，表示未发生任何行过滤。 从 100 减少的值表示过滤量增加。

前缀行数（从当前连接中传递到下一个连接的估计行数）是rows和filtered值的乘积。 也就是说，前缀行数是估计的行数，减去估计的过滤效果。 例如，如果rows为 1000，filtered为 20％，则条件过滤将 1000 的估计行数减少为 1000 × 20％= 1000 × .2 = 200。

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有以下特征：

  • employee表有 1024 行。

  • department表有 12 行。

  • 两个表在dept_no上都有索引。

  • employee表在first_name上有一个索引。

  • 8 行满足employee.first_name上的这个条件：

    employee.first_name = 'John'

  • 150 行满足employee.hire_date上的这个条件：

    employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

  • 1 行同时满足这两个条件：

    employee.first_name = 'John'
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

没有条件过滤，EXPLAIN会产生如下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

对于employee，在name索引上的访问方法选择匹配名称为'John'的 8 行。不进行过滤（filtered为 100%），因此所有行都是下一个表的前缀行：前缀行数为rows × filtered = 8 × 100% = 8。

使用条件过滤，优化器还考虑WHERE子句中未考虑的条件。在这种情况下，优化器使用启发式方法估计employee.hire_date上BETWEEN条件的过滤效果为 16.31%。因此，EXPLAIN生成类似以下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

现在前缀行数为rows × filtered = 8 × 16.31% = 1.3，更接近实际数据集。

通常，优化器不会计算最后一个连接表的条件过滤效果（前缀行数减少），因为没有下一个表可以传递行。一个例外情况是EXPLAIN：为了提供更多信息，过滤效果将计算所有连接表，包括最后一个。

要控制优化器是否考虑额外的过滤条件，请使用optimizer_switch系统变量的condition_fanout_filter标志（参见第 10.9.2 节，“可切换的优化”）。该标志默认启用，但可以禁用以抑制条件过滤（例如，如果发现特定查询在没有条件过滤的情况下性能更好）。

如果优化器高估了条件过滤的效果，性能可能会比不使用条件过滤更差。在这种情况下，可以采用以下技巧：

  • 如果某列没有索引，请为其创建索引，以便优化器了解列值的分布并改进其行估计。

  • 类似地，如果没有列直方图信息可用，请生成直方图（参见第 10.9.6 节，“优化器统计”）。

  • 更改连接顺序。实现这一点的方法包括连接顺序优化器提示（参见第 10.9.3 节，“优化器提示”）、SELECT后紧跟STRAIGHT_JOIN，以及STRAIGHT_JOIN连接操作符。

  • 禁用会话的条件过滤：

    SET optimizer_switch = 'condition_fanout_filter=off';

    或者，对于给定的查询，使用优化器提示：

    SELECT /*+ SET_VAR(optimizer_switch = 'condition_fanout_filter=off') */ ...

原文：dev.mysql.com/doc/refman/8.0/en/constant-folding-optimization.html

10.2.1.14 常量折叠优化

常量与列值之间的比较，其中常量值超出范围或与列类型不匹配，现在在查询优化期间处理一次，而不是在执行期间逐行处理。可以以这种方式处理的比较包括>, >=, <, <=, <>/!=, =, 和 <=>。

考虑以下语句创建的表：

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

查询SELECT * FROM t WHERE c < 256中的WHERE条件包含整数常量 256，这对于TINYINT UNSIGNED列来说超出范围。以前，这是通过将两个操作数都视为较大类型来处理的，但现在，由于c的任何允许值都小于常量，因此WHERE表达式可以折叠为WHERE 1，使查询重写为SELECT * FROM t WHERE 1。

这使得优化器可以完全删除WHERE表达式。如果列c可为空（即仅定义为TINYINT UNSIGNED），则查询将被重写如下：

SELECT * FROM t WHERE ti IS NOT NULL

对比较支持的 MySQL 列类型的常量进行折叠如下：

  • 整数列类型。 整数类型与以下类型的常量进行比较，如下所述：

    • 整数值。 如果常量超出列类型的范围，比较将折叠为1或IS NOT NULL，如已经显示的那样。

      如果常量是一个范围边界，比较将折叠为=。例如（使用已经定义的相同表）：

      mysql> EXPLAIN SELECT * FROM t WHERE c >= 255;
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where 1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
*************************** 1\. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255) 1 row in set (0.00 sec)

    • 浮点或定点值。 如果常量是十进制类型之一（如DECIMAL，REAL，DOUBLE或FLOAT）并且具有非零小数部分，则不能相等；相应地折叠。对于其他比较，根据符号四舍五入到整数值，然后执行范围检查并按照已经描述的方式处理整数-整数比较。

      无法表示为DECIMAL的REAL值将四舍五入为.01 或-.01，然后作为DECIMAL处理。

    • 字符串类型。 尝试将字符串值解释为整数类型，然后将比较处理为整数值之间的比较。如果失败，则尝试将值处理为REAL。

  • DECIMAL 或 REAL 列。 十进制类型与以下类型的常量进行比较，如下所述：

    • 整数值。 对列值的整数部分执行范围检查。如果没有折叠结果，将常量转换为与列值具有相同小数位数的DECIMAL，然后将其作为DECIMAL进行检查（见下文）。

    • DECIMAL 或 REAL 值。 检查溢出（即常量的整数部分是否比列的十进制类型允许的更多位数）。如果是，则折叠。

      如果常量的有效小数位数多于列的类型，截断常量。如果比较运算符是=或<>，则折叠。如果运算符是>=或<=，由于截断而调整运算符。例如，如果列的类型是DECIMAL(3,1)，SELECT * FROM t WHERE f >= 10.13变为SELECT * FROM t WHERE f > 10.1。

      如果常量的小数位数少于列的类型，将其转换为具有相同位数的常量。对于REAL值的下溢（即，小数位数太少无法表示），将常量转换为十进制 0。

    • 字符串值。 如果值可以解释为整数类型，则将其处理为整数类型。否则，尝试将其处理为REAL。

  • FLOAT 或 DOUBLE 列。 FLOAT(*m*,*n*)或DOUBLE(*m*,*n*)与常量的比较处理如下：

    如果值超出列的范围，折叠。

    如果值有超过n个小数位，截断，折叠时进行补偿。对于=和<>比较，按照之前描述的折叠为TRUE、FALSE或IS [NOT] NULL；对于其他运算符，调整运算符。

    如果值有超过m个整数位，折叠。

限制。 该优化不能用于以下情况：

  1. 使用BETWEEN或IN进行比较。

  2. 与BIT列或使用日期或时间类型的列。

  3. 在准备语句的准备阶段，尽管可以在实际执行准备语句时进行优化阶段应用。这是因为在语句准备期间，常量的值尚未知晓。

原文：dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

10.2.1.15 IS NULL 优化

MySQL 可以对 col_name IS NULL 执行与 col_name = constant_value 相同的优化。例如，MySQL 可以使用索引和范围来搜索带有 IS NULL 的 NULL。

示例:

SELECT * FROM *tbl_name* WHERE *key_col* IS NULL;

SELECT * FROM *tbl_name* WHERE *key_col* <=> NULL;

SELECT * FROM *tbl_name*
  WHERE *key_col*=*const1* OR *key_col*=*const2* OR *key_col* IS NULL;

如果 WHERE 子句包含对声明为 NOT NULL 的列的 col_name IS NULL 条件，则该表达式会被优化掉。在列可能产生 NULL 的情况下（例如，如果它来自 LEFT JOIN 的右侧表），此优化不会发生。

MySQL 也可以优化组合 *col_name* = *expr* OR *col_name* IS NULL，这种形式在解析子查询中很常见。EXPLAIN 在使用此优化时显示 ref_or_null。

此优化可以处理任何关键部分的一个 IS NULL。

一些查询示例，假设在表 t2 的列 a 和 b 上有索引：

SELECT * FROM t1 WHERE t1.a=*expr* OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 首先对参考键进行读取，然后单独搜索具有 NULL 键值的行。

该优化只能处理一个 IS NULL 级别。在下面的查询中，MySQL 仅在表达式 (t1.a=t2.a AND t2.a IS NULL) 上使用关键查找，并不能使用列 b 上的关键部分：

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

原文：dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

10.2.1.16 ORDER BY 优化

本节描述了 MySQL 何时可以使用索引来满足ORDER BY子句，当无法使用索引时使用的filesort操作，以及优化器提供的关于ORDER BY的执行计划信息。

带有和不带有LIMIT的ORDER BY可能以不同的顺序返回行，如第 10.2.1.19 节，“LIMIT 查询优化”中讨论的那样。

  • 使用索引满足 ORDER BY

  • 使用 filesort 满足 ORDER BY

  • 影响 ORDER BY 优化

  • ORDER BY 执行计划信息可用

使用索引满足 ORDER BY

在某些情况下，MySQL 可能使用索引来满足ORDER BY子句，并避免执行filesort操作所涉及的额外排序。

只要ORDER BY与索引不完全匹配，只要索引的所有未使用部分和所有额外的ORDER BY列在WHERE子句中都是常量，索引也可以被使用。如果索引不包含查询访问的所有列，那么只有在索引访问比其他访问方法更便宜时才会使用索引。

假设在(*key_part1*, *key_part2*)上有一个索引，以下查询可能使用索引来解决ORDER BY部分。优化器是否实际这样做取决于如果索引不包含的列也必须被读取，那么读取索引是否比表扫描更有效。

  • 在这个查询中，对(*key_part1*, *key_part2*)的索引使得优化器可以避免排序：

    SELECT * FROM t1
  ORDER BY *key_part1*, *key_part2*;

    然而，该查询使用了SELECT *，可能选择的列比*key_part1和key_part2*多。在这种情况下，扫描整个索引并查找表行以找到不在索引中的列可能比扫描表并对结果进行排序更昂贵。如果是这样，优化器可能不使用索引。如果SELECT *只选择索引列，那么索引会被使用，避免排序。

    如果t1是一个InnoDB表，表主键隐式地是索引的一部分，索引可以用于解决此查询的ORDER BY：

    SELECT *pk*, *key_part1*, *key_part2* FROM t1
  ORDER BY *key_part1*, *key_part2*;

  • 在这个查询中，*key_part1是常量，因此通过索引访问的所有行都按key_part2*顺序排列，而且在WHERE子句足够选择性的情况下，(*key_part1*, *key_part2*)上的索引避免排序比表扫描更便宜：

    SELECT * FROM t1
  WHERE *key_part1* = *constant*
  ORDER BY *key_part2*;

  • 在接下来的两个查询中，是否使用索引与之前显示的没有DESC的相同查询类似：

    SELECT * FROM t1
  ORDER BY *key_part1* DESC, *key_part2* DESC;

SELECT * FROM t1
  WHERE *key_part1* = *constant*
  ORDER BY *key_part2* DESC;

  • ORDER BY中的两列可以按相同方向排序（都是ASC或都是DESC），也可以按相反方向排序（一个ASC，一个DESC）。索引使用的条件是索引必须具有相同的同质性，但实际方向不必相同。

    如果查询混合使用ASC和DESC，优化器可以在索引上使用这些列，如果索引也使用相应的混合升序和降序列：

    SELECT * FROM t1
  ORDER BY *key_part1* DESC, *key_part2* ASC;

    如果key_part1是降序的，而key_part2是升序的，优化器可以在(key_part1, key_part2)上使用索引。如果key_part1是升序的，而key_part2是降序的，它也可以使用这些列的索引（进行反向扫描）。参见 Section 10.3.13, “Descending Indexes”。

  • 在接下来的两个查询中，*key_part1*与一个常量进行比较。如果WHERE子句足够选择性，使得索引范围扫描比表扫描更便宜，则会使用索引：

    SELECT * FROM t1
  WHERE *key_part1* > *constant*
  ORDER BY *key_part1* ASC;

SELECT * FROM t1
  WHERE *key_part1* < *constant*
  ORDER BY *key_part1* DESC;

  • 在下一个查询中，ORDER BY没有命名*key_part1，但所选的所有行都具有常量key_part1*值，因此仍然可以使用索引：

    SELECT * FROM t1
  WHERE *key_part1* = *constant1* AND *key_part2* > *constant2*
  ORDER BY *key_part2*;

在某些情况下，MySQL 无法 使用索引解析ORDER BY，尽管仍然可以使用索引找到与WHERE子句匹配的行。例如：

  • 查询在不同的索引上使用ORDER BY：

    SELECT * FROM t1 ORDER BY *key1*, *key2*;

  • 查询在索引的非连续部分上使用ORDER BY：

    SELECT * FROM t1 WHERE *key2*=*constant* ORDER BY *key1_part1*, *key1_part3*;

  • 用于获取行的索引与ORDER BY中使用的索引不同：

    SELECT * FROM t1 WHERE *key2*=*constant* ORDER BY *key1*;

  • 查询使用包含除索引列名之外的项的表达式进行ORDER BY：

    SELECT * FROM t1 ORDER BY ABS(*key*);
SELECT * FROM t1 ORDER BY -*key*;

  • 查询涉及多个表，并且ORDER BY中的列并非全部来自用于检索行的第一个非常量表（这是EXPLAIN输出中第一个没有const连接类型的表）。

  • 查询具有不同的ORDER BY和GROUP BY表达式。

  • 在ORDER BY子句中只有列名的前缀上有索引。在这种情况下，索引无法完全解析排序顺序。例如，如果只对CHAR(20)列的前 10 个字节建立索引，则索引无法区分第 10 个字节之后的值，需要使用filesort。

  • 索引不按顺序存储行。例如，在MEMORY表中的HASH索引是如此。

排序的索引可用性可能受到列别名的影响。假设列t1.a已建立索引。在此语句中，选择列表中的列名为a。它指的是t1.a，与ORDER BY中对a的引用一样，因此可以使用t1.a上的索引：

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中的列名也是a，但它是别名。它指的是ABS(a)，与ORDER BY中对a的引用一样，因此无法使用t1.a上的索引：

SELECT ABS(a) AS a FROM t1 ORDER BY a;

在以下语句中，ORDER BY 指的是选择列表中不是列名的名称。但是在 t1 中有一个名为 a 的列，因此 ORDER BY 指的是 t1.a，并且可以使用在 t1.a 上的索引。（当然，生成的排序顺序可能与 ABS(a) 的顺序完全不同。）

SELECT ABS(a) AS b FROM t1 ORDER BY a;

以前（MySQL 5.7 及更低版本），在某些条件下，GROUP BY 会隐式排序。在 MySQL 8.0 中，不再发生这种情况，因此不再需要在末尾指定 ORDER BY NULL 来抑制隐式排序（如以前所做）。但是，查询结果可能与以前的 MySQL 版本不同。为了产生给定的排序顺序，请提供一个 ORDER BY 子句。

使用 filesort 满足 ORDER BY

如果无法使用索引满足 ORDER BY 子句，MySQL 执行一个 filesort 操作，读取表行并对其进行排序。filesort 在查询执行中构成额外的排序阶段。

从 MySQL 8.0.12 开始，为了获取 filesort 操作的内存，优化器根据需要逐步分配内存缓冲区，直到达到 sort_buffer_size 系统变量指示的大小，而不是像在 MySQL 8.0.12 之前那样一次性分配固定数量的 sort_buffer_size 字节。这使用户可以将 sort_buffer_size 设置为较大的值，以加快较大的排序，而不必担心小排序的过度内存使用。（在 Windows 上，对于多个并发排序，这种好处可能不会发生，因为 Windows 具有弱多线程 malloc。）

filesort 操作根据需要使用临时磁盘文件，如果结果集太大而无法放入内存中。某些类型的查询特别适合完全在内存中进行 filesort 操作。例如，优化器可以使用 filesort 来有效地处理内存中的 ORDER BY 操作，而无需临时文件，对于以下形式的查询（和子查询）：

SELECT ... FROM *single_table* ... ORDER BY *non_index_column* [DESC] LIMIT [*M*,]*N*;

这些查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。例如：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
影响 ORDER BY 优化

对于慢的 ORDER BY 查询，如果不使用 filesort，请尝试降低 max_length_for_sort_data 系统变量的值，以触发 filesort。 （设置此变量值过高的症状是高磁盘活动和低 CPU 活动的组合。）这种技术仅适用于 MySQL 8.0.20 之前。从 8.0.20 开始，由于优化器的更改使其过时且无效，max_length_for_sort_data 已被弃用。

为了提高 ORDER BY 的速度，请检查是否可以让 MySQL 使用索引而不是额外的排序阶段。如果不可能，请尝试以下策略：

  • 增加sort_buffer_size变量值。理想情况下，该值应足够大，以使整个结果集适合排序缓冲区（以避免写入磁盘和合并操作）。

    请注意，存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值，并增加max_sort_length的值，则排序缓冲区元组的大小也会增加，可能需要您增加sort_buffer_size。

    要监视合并临时文件的合并次数，请检查Sort_merge_passes状态变量。

  • 增加read_rnd_buffer_size变量值，以便一次读取更多行。

  • 将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。变量值可以列出几个路径，以轮询方式使用；您可以使用此功能将负载分散到几个目录中。在 Unix 上用冒号字符（:）分隔路径，在 Windows 上用分号字符（;）分隔。这些路径应命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 执行计划信息可用

使用EXPLAIN（参见 Section 10.8.1，“使用 EXPLAIN 优化查询”输出的Extra列不包含Using filesort，则使用索引，不执行filesort。

  • 如果EXPLAIN输出的Extra列包含Using filesort，则未使用索引并执行filesort。

此外，如果执行了filesort，优化器跟踪输出将包括一个filesort_summary块。例如：

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

peak_memory_used 表示在排序过程中任意时间点使用的最大内存。这个值可能与 sort_buffer_size 系统变量的值一样大，但不一定。在 MySQL 8.0.12 之前，输出显示的是 sort_buffer_size，表示 sort_buffer_size 的值。（在 MySQL 8.0.12 之前，优化器总是为排序缓冲区分配 sort_buffer_size 字节。从 8.0.12 开始，优化器逐渐分配排序缓冲区内存，从一个小量开始，根据需要逐渐增加，直到 sort_buffer_size 字节。）

sort_mode 值提供有关排序缓冲区中元组内容的信息：

  • <sort_key, rowid>：表示排序缓冲区元组是包含排序键值和原始表行的行 ID 的对。元组按排序键值排序，行 ID 用于从表中读取行。

  • <sort_key, additional_fields>：表示排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。

  • <sort_key, packed_additional_fields>：与前一种变体类似，但附加列紧密打包在一起，而不是使用固定长度编码。

EXPLAIN 无法区分优化器是否在内存中执行 filesort。内存中的 filesort 使用可以在优化器跟踪输出中看到。查找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL Internals: Tracing the Optimizer。

原文：dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html

10.2.1.17 GROUP BY 优化

最一般的满足GROUP BY子句的方法是扫描整个表，并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组并应用聚合函数（如果有的话）。在某些情况下，MySQL 能够比这做得更好，通过使用索引访问来避免创建临时表。

使用索引进行GROUP BY的最重要的前提条件是所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储其键（例如BTREE索引是这样的，但HASH索引不是）。临时表的使用是否可以被索引访问替代还取决于查询中使用了索引的哪些部分，为这些部分指定的条件以及选择的聚合函数。

有两种通过索引访问执行GROUP BY查询的方法，如下节所述。第一种方法将分组操作与所有范围谓词（如果有的话）一起应用。第二种方法首先执行范围扫描，然后对结果元组进行分组。

  • 松散索引扫描

  • 紧凑索引扫描

在某些条件下，即使没有GROUP BY，也可以使用松散索引扫描。请参阅跳过扫描范围访问方法。

松散索引扫描

处理GROUP BY最有效的方法是直接使用索引检索分组列。通过这种访问方法，MySQL 使用某些索引类型的属性，即键是有序的（例如BTREE）。这种属性使得可以在索引中查找组，而无需考虑满足所有WHERE条件的所有键。这种访问方法只考虑索引中的一部分键，因此被称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取与组数相同的键，这可能比所有键的数量要小得多。如果WHERE子句包含范围谓词（请参阅第 10.8.1 节“使用 EXPLAIN 优化查询”中对range连接类型的讨论），松散索引扫描查找满足范围条件的每个组的第一个键，然后再次读取最小可能数量的键。这在以下条件下是可能的：

  • 查询只涉及单个表。

  • GROUP BY仅命名了构成索引最左边前缀的列，没有其他列。（如果查询中有DISTINCT子句而不是GROUP BY，则所有不同的属性都指向构成索引最左边前缀的列。）例如，如果表t1在(c1,c2,c3)上有索引，如果查询中有GROUP BY c1, c2，则可以应用松散索引扫描。如果查询中有GROUP BY c2, c3（列不是最左边前缀）或GROUP BY c1, c2, c4（c4不在索引中），则不适用。

  • 选择列表中使用的唯一聚合函数（如果有）是MIN()和MAX()，并且它们都引用相同的列。该列必须在索引中，并且必须紧随GROUP BY中的列之后。

  • 查询中除了GROUP BY引用的索引部分外，其他索引部分必须是常量（即，它们必须与常量相等），除了MIN()或MAX()函数的参数。

  • 对于索引中的列，必须对完整列值建立索引，而不仅仅是前缀。例如，对于c1 VARCHAR(20), INDEX (c1(10))，索引仅使用c1值的前缀，不能用于松散索引扫描。

如果查询适用于松散索引扫描，则EXPLAIN输出中的Extra列显示Using index for group-by。

假设在表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)。可以对以下查询使用松散索引扫描访问方法：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > *const* GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = *const* GROUP BY c1, c2;

以下查询不能使用这种快速选择方法执行，原因如下：

  • 存在除了MIN()或MAX()之外的其他聚合函数：

    SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

  • GROUP BY子句中的列不构成索引的最左边前缀：

    SELECT c1, c2 FROM t1 GROUP BY c2, c3;

  • 查询引用了GROUP BY部分之后的键的一部分，且没有与常量相等的情况：

    SELECT c1, c3 FROM t1 GROUP BY c1, c2;

    如果查询包括WHERE c3 = *const*，则可以使用松散索引扫描。

松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用，除了已经支持的MIN()和MAX()引用：

  • 支持AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)接受单个参数。COUNT(DISTINCT)可以有多个列参数。

  • 查询中不能包含GROUP BY或DISTINCT子句。

  • 仍然适用前面描述的松散索引扫描限制。

假设在表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
紧凑索引扫描

紧凑索引扫描可以是完整索引扫描或范围索引扫描，具体取决于查询条件。

当不满足松散索引扫描的条件时，仍然可能避免为GROUP BY查询创建临时表。如果WHERE子句中存在范围条件，则此方法仅读取满足这些条件的键。否则，它执行索引扫描。因为此方法在WHERE子句定义的每个范围中读取所有键，或者如果没有范围条件则扫描整个索引，所以称为紧凑索引扫描。使用紧凑索引扫描，只有在找到满足范围条件的所有键之后才执行分组操作。

要使这种方法起作用，查询中涉及到GROUP BY键的所有列都需要有一个常量相等条件，该条件位于GROUP BY键的前部或中间。相等条件中的常量填补了搜索键中的任何“间隙”，从而可以形成索引的完整前缀。然后可以使用这些索引前缀进行索引查找。如果GROUP BY结果需要排序，并且可以形成索引的前缀搜索键，MySQL 还会避免额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

假设在表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。前面描述的松散索引扫描访问方法无法处理以下查询，但仍可使用紧凑索引扫描访问方法。

  • GROUP BY中存在一个间隙，但被条件c2 = 'a'覆盖：

    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

  • GROUP BY不是以键的第一部分开头，但有一个条件为该部分提供了一个常量：

    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

原文：dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html

10.2.1.18 DISTINCT 优化

DISTINCT 与 ORDER BY 结合在许多情况下需要一个临时表。

由于 DISTINCT 可能使用 GROUP BY，了解 MySQL 如何处理 ORDER BY 或 HAVING 子句中不属于所选列的列。请参见 Section 14.19.3, “MySQL Handling of GROUP BY”。

在大多数情况下，DISTINCT 子句可以被视为 GROUP BY 的特例。例如，以下两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > *const*;

SELECT c1, c2, c3 FROM t1
WHERE c1 > *const* GROUP BY c1, c2, c3;

由于这种等价性，适用于 GROUP BY 查询的优化也可以应用于带有 DISTINCT 子句的查询。因此，有关 DISTINCT 查询的优化可能性的更多详细信息，请参见 Section 10.2.1.17, “GROUP BY Optimization”。

当将 LIMIT *row_count* 与 DISTINCT 结合时，MySQL 会在找到 row_count 个唯一行后停止。

如果在查询中没有使用所有表中的列，MySQL 会在找到第一个匹配项后停止扫描任何未使用的表。在以下情况下，假设 t1 在 t2 之前被使用（您可以通过 EXPLAIN 进行检查），MySQL 在找到 t2 的第一行后（对于 t1 中的任何特定行）停止从 t2 中读取：

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

原文：dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

10.2.1.19 LIMIT 查询优化

如果只需要结果集中的指定数量的行，请在查询中使用LIMIT子句，而不是获取整个结果集并丢弃额外的数据。

MySQL 有时会优化具有LIMIT *row_count*子句而没有HAVING子句的查询：

  • 如果你使用LIMIT只选择了少数几行，MySQL 在某些情况下会使用索引，而通常情况下它更倾向于进行全表扫描。

  • 如果将LIMIT *row_count*与ORDER BY结合使用，MySQL 会在找到排序结果的前*row_count行后停止排序，而不是对整个结果进行排序。如果使用索引进行排序，这是非常快的。如果必须进行文件排序，那么在找到不带LIMIT子句的查询的所有匹配行之前，将选择大多数或全部行，并对它们进行排序，然后才找到前row_count*行。在找到初始行之后，MySQL 不会对结果集的任何剩余部分进行排序。

    这种行为的一种表现是，带有和不带有LIMIT的ORDER BY查询可能以不同的顺序返回行，如本节后面所述。

  • 如果将LIMIT *row_count*与DISTINCT结合使用，MySQL 会在找到*row_count*个唯一行后停止。

  • 在某些情况下，GROUP BY可以通过按顺序读取索引（或对索引进行排序），然后计算摘要直到索引值发生变化来解决。在这种情况下，LIMIT *row_count*不会计算任何不必要的GROUP BY值。

  • 一旦 MySQL 向客户端发送了所需数量的行，除非你使用SQL_CALC_FOUND_ROWS，否则它会中止查询。在这种情况下，可以使用SELECT FOUND_ROWS()检索行数。参见第 14.15 节，“信息函数”。

  • LIMIT 0会快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用使结果集元数据可用的 MySQL API 的应用程序中获取结果列的类型。使用mysql客户端程序，你可以使用--column-type-info选项来显示结果列类型。

  • 如果服务器使用临时表来解析查询，它会使用LIMIT *row_count*子句来计算所需的空间。

  • 如果未对ORDER BY使用索引但同时存在LIMIT子句，优化器可能能够避免使用合并文件，并使用内存中的filesort操作在内存中对行进行排序。

如果多行在ORDER BY列中具有相同的值，服务器可以自由地以任何顺序返回这些行，并且可能根据整体执行计划而有所不同。换句话说，这些行的排序顺序对于非排序列是不确定的。

影响执行计划的一个因素是LIMIT，因此带有和不带有LIMIT的ORDER BY查询可能以不同的顺序返回行。考虑这个查询，它按category列排序，但对于id和rating列是不确定的：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

包含LIMIT可能会影响每个category值内行的顺序。例如，这是一个有效的查询结果：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

在每种情况下，行都按照ORDER BY列排序，这是 SQL 标准要求的全部内容。

如果确保带有和不带有LIMIT时返回相同的行顺序很重要，请在ORDER BY子句中包含额外的列，以使顺序确定。例如，如果id值是唯一的，您可以通过像这样排序来使给定category值的行按id顺序显示：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

对于带有ORDER BY或GROUP BY以及LIMIT子句的查询，优化器会尝试默认选择有序索引，以加快查询执行速度。在 MySQL 8.0.21 之前，即使在使用其他优化可能更快的情况下，也无法覆盖此行为。从 MySQL 8.0.21 开始，可以通过将optimizer_switch系统变量的prefer_ordering_index标志设置为off来关闭此优化。

示例：首先我们创建并填充一个表t，如下所示：

# Create and populate a table t:

mysql> CREATE TABLE t (
 ->     id1 BIGINT NOT NULL,
 ->     id2 BIGINT NOT NULL,
 ->     c1 VARCHAR(50) NOT NULL,
 ->     c2 VARCHAR(50) NOT NULL,
 ->  PRIMARY KEY (id1),
 ->  INDEX i (id2, c1)
 -> );

# [Insert some rows into table t - not shown]

验证prefer_ordering_index标志是否已启用：

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

由于以下查询有一个LIMIT子句，我们期望它使用有序索引，如果可能的话。在这种情况下，正如我们从EXPLAIN输出中看到的那样，它使用了表的主键。

mysql> EXPLAIN SELECT c2 FROM t
 ->     WHERE id2 > 3
 ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

现在我们禁用prefer_ordering_index标志，并重新运行相同的查询；这次它使用索引i（其中包括WHERE子句中使用的id2列），以及一个文件排序：

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
 ->     WHERE id2 > 3
 ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

参见第 10.9.2 节，“可切换优化”。

avatar
文章
阅读
粉丝