【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接

 

在MySQL中，可以使用批处理Key访问（BKA）联接算法，该算法使用对联接表的索引访问和联接缓冲区。

BKA算法支持内联接、外联接和半联接操作，包括嵌套的外部联接。

BKA的优点包括由于更高效的表扫描而提高了联接性能。

此外，以前仅用于内部联接的块嵌套循环（BNL）联接算法得到了扩展，可以用于外部联接和半联接操作，包括嵌套的外部联接。

以下部分讨论连接缓冲区管理，它是原始BNL算法、扩展的BNL算法和BKA算法扩展的基础。

有关半联接策略的信息，请参阅“使用半联接转换优化IN和EXISTS子查询语句”

1.连接缓冲区管理

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

连接缓冲区管理代码在存储感兴趣的行列的值时稍微更有效地利用连接缓冲区空间：

如果行列的数值为NULL，则不会在缓冲区中为其分配额外的字节，并且为VARCHAR类型的任何数值分配最小字节数。

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

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

增量联接缓冲区仅包含由第二个联接操作数生成的表的行中的列。也就是说，它是从第一个操作数缓冲区向一行递增的。如果B2是增量联接缓冲区，则它包含行r2的感兴趣的列以及从B1到行r1的链接。

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

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

        增量缓冲区可以减少从用于先前联接操作的缓冲区复制列的频率。这提供了缓冲区空间的节省，因为在一般情况下，第一联接操作数产生的行可以与第二联接操作数生成的若干行相匹配。没有必要从第一个操作数复制一行的多个副本。由于复制时间的减少，增量缓冲区还可以节省处理时间。

在MySQL 8.0中，optimizer_switch系统变量的block_nested_roop标志的工作原理如下：

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

在MySQL 8.0.18及更高版本中，它还控制散列联接的使用（请参阅第10.2.1.4节“散列联接优化”）。

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

batched_key_access标志控制优化器如何使用batched key access联接算法。

默认情况下，block_nested_roop处于打开状态，batched_key_access处于关闭状态。

请参阅“可切换优化”。

也可以应用优化器提示；请参阅 块嵌套循环和批量密钥访问算法的优化器提示。

有关半联接策略的信息，请参阅“使用半联接转换优化IN和EXISTS子查询语句”

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

MySQL BNL算法的原始实现被扩展为支持外部联接和半联接操作

（后来被哈希联接算法取代；请参阅“哈希联接优化”）。

【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询-CSDN博客

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

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

在MySQL 8.0中，optimizer_switch系统变量的block_nested_roop标志的工作原理如下：

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

在MySQL 8.0.18及更高版本中，它还控制散列联接的使用（请参阅“Hash联接优化”）。

【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询-CSDN博客

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

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

在EXPLAIN输出中，当Extra值包含Using join buffer（块嵌套循环）并且类型值为ALL、index或range时，表示表使用BNL。

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

3.批量Key访问联接

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

当使用BKA时，join_buffer_size的值定义了向存储引擎发出的每个请求中密钥批的大小。缓冲区越大，对联接操作的右侧表的顺序访问就越多，这可以显著提高性能。

对于要使用的BKA，优化器切换系统变量的batched_key_access标志必须设置为on。BKA使用MRR，因此MRR标志也必须为on。目前，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 Server（SQL节点）将联接缓冲区中一部分行的密钥包及其关联发送到MySQL Cluster数据节点。作为回报，SQL节点接收与相应关联耦合的匹配行的包（或多个包）。BKA联接算法获取这些行并构建新的联接行。然后，将一组新的键发送到数据节点，并使用返回包中的行来构建新的连接行。该过程一直持续到连接缓冲区中的最后一个键被发送到数据节点，并且SQL节点已经接收并连接了所有与这些键匹配的行。这提高了性能，因为SQL节点向数据节点发送的密钥承载包更少，意味着它与数据节点之间执行联接操作的往返次数更少。

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

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

在EXPLAIN输出中，当Extra值包含Using join buffer（Batched Key Access）并且类型值为ref或eq_ref时，表示表使用BKA。

4.优化器提示

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

参见“优化器提示”。

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