mysql自我优化-优化select语句（三）

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。

1、外连接优化

外连接包括LEFT JOIN和 RIGHT JOIN。
MySQL 实现如下 A LEFT JOIN B， join的规范如下：

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

• 表A被设置为依赖于LEFT JOIN条件中使用的所有表(B除外)。

• 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，只是表角色颠倒了。右连接被转换为等效的左连接，如下面的“外连接简化”所述。

对于LEFT JOIN，如果生成的NULL行WHERE条件始终为 false ，则将LEFT JOIN更改为内部连接。例如， 如果在下面的查询t2.column1 is NULL, WHERE条款将要是false,

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;

现在优化器可以在表t2之前使用表，t1如果这样做会产生更好的查询计划。要提供有关表连接顺序的提示，请使用STRAIGHT_JOIN; 见“SELECT 语句”。但是， STRAIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换；请参阅 “使用半连接转换优化子查询、派生表和视图引用”。

2、外连接简化

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

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

(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 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

相反，如果WHERE条件被null拒绝，MySQL将查询转换为没有外部连接操作的查询。(也就是说，它将外部连接转换为内部连接。)如果对于为该操作生成的任何空补充行，该条件的计算结果为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 拒绝，因为它们可能适用于NULL-complemented 行：

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

检查外部连接操作的条件是否为null-rejected的一般规则很简单：

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

• 它是一个包含内部表引用的谓词，当其中一个参数为NULL时，该内部表的计算结果为UNKNOWN

• 它是一个连接，包含一个拒绝null的条件作为连接

• 它是null-rejected条件的分离

可以为查询中的一个外连接操作拒绝一个条件，而不为另一个外连接操作拒绝一个条件。在这个查询中，WHERE条件对第二个外连接操作是null-rejected的，但对第一个操作不是null-rejected的:

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

如果WHERE查询中的外连接操作的条件为null-rejected，则外连接操作将替换为内连接操作。

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

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 是null-rejected的。这会导致查询完全没有外部连接：

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嵌入外连接的 条件不是null-rejected，但是嵌入外连接的连接条件T2.A=T1.A AND T3.C=T1.C是null-rejected的：

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

3、多范围读取优化

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

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

• MRR 允许基于索引元组按顺序访问数据行，而不是按随机顺序访问。服务器获取一组满足查询条件的索引元组，按照数据行ID的顺序进行排序，并使用排序后的元组依次检索数据行。这使得数据访问更加高效且成本更低。

• MRR支持对需要通过索引元组访问数据行的操作的键访问请求进行批处理，例如范围索引扫描和对join属性使用索引的等价连接。MRR迭代一系列索引范围以获得合格的索引元组。随着这些结果的积累，将使用它们访问相应的数据行。在开始读取数据行之前，没有必要获取所有索引元组。

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

以下场景说明了 MRR 优化何时具有优势：

方案A：MRR可用于InnoDB和 MyISAM索引范围扫描和表相等连接的操作。

• 索引元组的一部分累积在缓存区中。

• 缓存区中的元组按其数据行 ID 排序。

• 根据排序的索引元组序列访问数据行。

方案 B：MRR 可用于 NDB表的多范围索引扫描或按属性执行相等连接。

• 一部分范围（可能是单键范围）在提交查询的中央节点上的缓存区中累积。

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

• 访问的行被打包成包并发送回中央节点。

• 接收到的带有数据行的包被放置在一个缓存区中。

• 从缓存区读取数据行。

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

如果不需要访问全表的行来产生查询结果，InnoDB和MyISAM不使用MRR。如果结果可以完全基于索引元组中的信息产生(通过覆盖索引)，就会出现这种情况;MRR没有提供任何好处。

两个optimizer_switch系统变量标志为使用MRR优化提供了接口。mrr标志用来控制是否使能mrr。如果启用了mrr(开)，mrr_cost_based标志控制优化器是否尝试在使用和不使用mrr(开)或尽可能使用mrr(关)之间做出基于成本的选择。缺省情况下，mrr为on, mrr_cost_based为on。请参见“可切换优化”。

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

4、阻止嵌套循环和批量密钥访问连接

在MySQL中，可以使用批处理密钥访问 (BKA) 连接算法，该算法使用对连接表的索引访问和连接缓存区。BKA算法支持内连接、外连接和半连接操作，包括嵌套的外连接。BKA的好处包括由于更高效的表扫描而提高了连接性能。此外，以前仅用于内部连接的块嵌套循环(BNL)连接算法得到了扩展，可以用于外部连接和半连接操作，包括嵌套的外部连接。

下面的章节将讨论连接缓存区管理，它是原始BNL算法、扩展BNL算法和BKA算法的扩展基础。有关半连接策略的信息，请参“用半连接转换优化子查询、派生表和视图引用”。

1. 块嵌套循环和批量密钥访问算法的连接缓存区管理

MySQL不仅可以使用连接缓存区来执行内部连接而不需要对内部表进行索引访问，还可以执行子查询扁平化后出现的外部连接和半连接。此外，当有对内部表的索引访问时，可以有效地使用连接缓存区。

连接缓存管理代码更有效地利用加入缓存区空间当有趣的行存储值列:没有额外字节的缓存区分配列一行,如果它的值是NULL,和最低分配的字节数为VARCHAR类型的任何值。

该代码支持两种类型的缓存区，常规缓存区和增量缓存区。假设使用连接缓存区B1连接表t1和表t2，并使用连接缓存区B2连接表t3:

MySQL 不仅可以使用连接缓存区来执行没有索引访问内表的内部连接，还可以执行在子查询展平后出现的外部连接和半连接。此外，当对内表进行索引访问时，可以有效地使用连接缓存区。

连接缓存区管理代码在存储感兴趣的行列的值时更有效地利用连接缓存区空间：如果行列的值为NULL ，则不会在缓存区中为行列分配额外的字节 NULL，并且为任何值分配最小字节数该 VARCHAR类型。

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

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

• 增量连接缓存区只包含由第二个连接操作数产生的表的行中的列。也就是说，它是从第一个操作数缓存区递增到一行。如果B2是一个增量连接缓存区，那么它包含了行r2中感兴趣的列以及从B1到行r1的链接。

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

用于连接操作的增量缓存区的每一行只包含要连接的表中某一行中感兴趣的列。通过引用第一个连接操作数产生的表中匹配行的感兴趣的列来扩充这些列。增量缓存区中的几行可以引用同一行r，其列存储在前面的连接缓存区中，只要所有这些行与行r匹配。

增量缓存区允许较不频繁地从用于前一个连接操作的缓存区复制列。这节省了缓存区空间，因为在一般情况下，第一个连接操作数产生的行可以与第二个连接操作数产生的行匹配。不需要从第一个操作数对一行进行多个复制。由于减少了复制时间，增量缓存区还节省了处理时间。

optimizer_switch系统变量的block_nested_loop和batched_key_access标志控制了优化器如何使用块嵌套循环和批密钥访问连接算法。默认情况下，block_nested_loop是on，batched_key_access是off。请参见“可切换优化”。也可以应用优化器提示，请参阅下面的"块嵌套循环和批密钥访问算法的优化提示"。

有关半连接策略的信息，“使用半连接转换 优化子查询、派生表和视图引用”

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

对MySQL BNL算法的原始实现进行了扩展，以支持外连接和半连接操作。

当使用连接缓存区执行这些操作时，放入缓存区的每一行都会提供一个匹配标志。

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

optimizer_switch系统变量的block_nested_loop标志控制了优化器如何使用块嵌套循环算法。默认情况下，block_nested_loop是on。请参见“可切换优化”。也可以应用优化器提示，请参阅下面的"块嵌套循环和批密钥访问算法的优化提示"。

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

一些涉及到一个或多个子查询与一个或多个左连接的组合的情况，特别是那些返回许多行的情况，可能会使用BNL，尽管它在这种情况下并不理想。该问题在MySQL 8.0中已经修复。如果不立即可行的升级MySQL,您可能希望禁用BNL同时通过设置optimizer_switch = ’ block_nested_loop =”或者使用NO_BNL优化器提示让优化器选择一个更好的计划,使用一个或多个索引提示(“索引提示”),或它们的一些组合,提高这类查询的性能。

有关半连接策略的信息，“使用半连接转换 优化子查询、派生表和视图引用”

3.批密钥访问连接

MySQL 实现了一种连接表的方法，称为批处理密钥访问 (BKA) 连接算法。当对第二个连接操作数生成的表进行索引访问时，可以应用 BKA。与 BNL 连接算法一样，BKA 连接算法使用连接缓存区来累积由连接操作的第一个操作数产生的行的感兴趣的列。然后 BKA 算法为缓存区中的所有行构建访问要连接的表的键，并将这些键批量提交给数据库引擎进行索引查找。密钥通过多范围读取 (MRR) 接口提交给引擎（请参阅 第 8.2.1.10 节，“多范围读取优化”）。提交键后，MRR 引擎函数以最佳方式在索引中执行查找，获取这些键找到的连接表的行，并开始向 BKA 连接算法提供匹配的行。每个匹配的行都与连接缓存区中一个行的引用耦合。

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

要使用BKA，必须将optimizer_switch系统变量的batched_key_access标志设置为on。BKA使用MRR，所以MRR标志也必须是打开的。目前，MRR的成本估计过于悲观。因此，也有必要关闭mrr_cost_based以便使用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 节点）发送到 NDB Cluster 数据节点。作为回报，SQL 节点接收匹配行的一个包（或多个包）以及对应的关联。BKA 连接算法采用这些行并构建新的连接行。然后一组新的键被发送到数据节点，并且来自返回包的行用于构建新的连接行。该过程一直持续到连接缓存区中的最后一个键被发送到数据节点，并且 SQL 节点已接收并连接与这些键匹配的所有行。

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

没有特殊的缓存区来存储为连接缓存区中的行构建的键。相反，为缓存区中的下一行构建键的函数作为参数传递给 MRR 函数。

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

4.块嵌套循环和批密钥访问算法的优化提示

除了使用optimizer_switch系统变量来控制会话范围内BNL和BKA算法的优化器使用之外，MySQL还支持优化器提示来影响每条语句的优化器。参见“优化提示”。

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