MySQL 8.4 Release Notes
10.2.1.12 块嵌套循环和批量键访问连接
在 MySQL 中，Batched Key Access（BKA）Join 算法可使用索引访问连接表并使用连接缓冲区。BKA 算法支持内连接、外连接和半连接操作，包括嵌套外连接。BKA 的优点包括由于更高效的表扫描提高了连接性能。此外，Block Nested-Loop（BNL）Join 算法之前只用于内连接现在也可以用于外连接和半连接操作，包括嵌套外连接。
以下部分讨论了原 BNL 算法的扩展、扩展 BNL 算法和 BKA 算法背后的连接缓冲区管理。关于半连接策略，请参阅使用半连接转换优化 IN 和 EXISTS 子查询谓词
MySQL 可以使用连接缓冲区来执行不仅是内连接（无索引访问）也可以是外连接和半连接（在子查询展平后出现的），此外，连接缓冲区还可以在有索引访问的情况下被有效地使用。
连接缓冲区管理代码更高效地利用连接缓冲区空间来存储感兴趣行的值：如果行列的值为 NULL，不分配额外字节；对 VARCHAR 类型的值则分配最少的字节数。
代码支持两种缓冲区类型，常规和增量。假设使用连接缓冲区 B1 连接表 t1 和 t2，然后将结果与表 t3 使用连接缓冲区 B2 进行连接：
-
常规连接缓冲区包含每个连接操作数的列。如果
B2是常规连接缓冲区，那么将r行放入B2中是由B1中的行r1和表t3中匹配行r2的感兴趣列组成。 -
增量连接缓冲区只包含第二个连接操作符产生的表中的列。也就是说，它是相对于第一个操作符缓冲区的增量。如果
B2是一个增量连接缓冲区，那么它包含行r2的有趣列，以及对行r1从B1的链接。
增量连接缓冲区总是相对于早期连接操作的缓冲区，所以第一个连接操作的缓冲区总是普通缓冲区。如前所示，用于将表t1和t2进行连接的缓冲区B1必须是一个普通缓冲区。
每一行增量缓冲区用于连接操作都包含要连接表中的有趣列。这些列与第一个连接操作符产生的表中的有趣列相结合。几个行在增量缓冲区中可以引用同一个行r，其列存储在前面的连接缓冲区中，只要所有这些行都匹配行r。
增量缓冲区使得将前一个连接操作的缓冲区中的列复制的频率减少，从而节省了缓冲区空间。因为一般情况下，第一个操作符产生的行可以被第二个操作符产生的多行匹配。增量缓冲区也提供了减少复制时间的优势。
block_nested_loopoptimizer_switch系统变量的块嵌套循环标志控制哈希连接。
batched_key_access标志控制优化器使用批量键访问连接算法的方式。
默认情况下，block_nested_loop是on，batched_key_access是off。请参阅第10.9.2节，“可切换优化”。优化器提示也可以应用；请参阅块嵌套循环和批量键访问算法的优化器提示。
关于半连接策略，请参阅使用半连接变换优化IN和EXISTS子查询谓词
MySQL BNL 算法的原始实现被扩展以支持外连接和半连接操作（后来被哈希连接算法所取代；请参阅第10.2.1.4节，“哈希连接优化”）。
当这些操作使用连接缓冲区执行时，每个行都被提供一个匹配标志。
如果使用连接缓冲区执行外部连接操作，第二个操作表的每一行将与连接缓冲区中的每一行进行匹配检查。找到匹配后，新的扩展行形成（原始行加上第二个操作表的列）并被剩余连接操作发送。另外，缓冲区中匹配行的匹配标志也被启用。在所有要连接的表都被检查完毕后，连接缓冲区被扫描。缓冲区中的没有匹配标志的每一行都被NULL补充（第二个操作表的每列的NULL值）并被剩余连接操作发送。
block_nested_loop系统变量中的optimizer_switch控制哈希连接。
请查看第10.9.2节，“可切换优化”，了解更多信息。优化器提示也可以应用；查看Block Nested-Loop 和批量键访问算法的优化器提示。
在EXPLAIN输出中，使用BNL表是指当Extra值包含Using join buffer (Block Nested Loop)且type值为ALL、index或range时。
关于半连接策略的信息，请参见使用半连接变换优化IN和EXISTS子查询谓词
MySQL 实现了一种称为批量键访问（BKA）join 算法的表连接方法。BKA 可以在第二个连接操作符产生索引访问时应用。像 BNL 连接算法一样，BKA 连接算法使用一个 join 缓冲区来累积第一个连接操作符生产的行中的有趣列，然后对要连接的表建立所有缓冲区中的键，并将这些键以批量形式提交给数据库引擎进行索引查找。这些键通过 Multi-Range Read（MRR）接口（见第10.2.1.11节，“Multi-Range Read 优化”)提交给引擎。提交键后，MRR 引擎函数对索引进行最优查找，fetch 到这些键对应的连接表行，并将匹配行与 join 缓冲区中的行关联起来。
当使用 BKA 时，join_buffer_size 的值定义了每个请求到存储引擎的键批量大小。缓冲区越大，对右表连接操作的顺序访问可以显著提高性能。
要使用 BKA，必须将 batched_key_access optimizer_switch 系统变量设置为 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 算法逐一请求行。每一行都返回与关联引用，可以访问匹配行在连接缓冲区。MRR 函数以优化方式获取行：它们以行 ID（主键）顺序获取。这提高性能，因为读取是磁盘顺序，而不是随机顺序。 -
第二种情况用于远程存储引擎，如
NDB。 MySQL Server（SQL节点）将 join 缓冲区的一部分键和关联信息发送给 MySQL 集群数据节点，反馈 SQL 节点接收到匹配行及其关联信息的包（或多个包）。BKA 连接算法使用这些行构建新的连接行，然后将新的一组键发送到数据节点，使用返回的包中的行来构建新的连接行。这个过程直到 join 缓冲区中的最后一组键被发送到数据节点，SQL 节点已经收到了并连接了所有匹配键的行。这提高性能，因为 SQL 节点向数据节点发送的键包数量少意味着 SQL 节点和数据节点之间的往返次数减少，从而减少连接操作时间。
使用第一种情况，join 缓冲区的一部分保留用于存储通过索引查找选择的行 ID（主键），并将其作为参数传递给 MRR 函数。
没有专门的缓冲区来存储 join 缓冲区中的键，而是将构建下一行缓冲区的函数作为参数传递给 MRR 函数。
在EXPLAIN 输出中，使用 BKA 对表的标志是当 Extra 值包含 Using join buffer (Batched Key Access)，并且 type 值为ref 或eq_ref。
优化器提示 for Block Nested-Loop 和 Batched Key Access 算法
除了使用optimizer_switch系统变量来控制优化器对BNL和BKA算法的会话级别使用，MySQL 还支持在语句级别使用优化器提示以影响优化器。见第10.9.3节，“优化器提示”。
要使用BNL或BKA提示来启用任何外连接的内表的缓冲区join，必须对所有外连接的内表启用缓冲区join。