8.2.1.11 阻止嵌套循环和批量密钥访问联接
在 MySQL 中，可以使用批处理键访问(BKA)联接算法，该算法同时使用对联接 table 的索引访问和联接缓冲区。 BKA 算法支持内部联接，外部联接和半联接操作，包括嵌套的外部联接。 BKA 的好处包括由于更高效的 table 扫描而提高了连接性能。此外，以前仅用于内部联接的块嵌套循环(BNL)联接算法得到了扩展，可以用于外部联接和半联接操作，包括嵌套的外部联接。
以下各节讨论基于原始 BNL 算法，扩展的 BNL 算法和 BKA 算法的扩展的连接缓冲区 Management。有关半联接策略的信息，请参阅第 8.2.2.1 节“使用半联接转换优化子查询，派生 table 和视图引用”
用于块嵌套循环和批处理密钥访问算法的连接缓冲区 Management
MySQL 可以使用联接缓冲区来执行内部联接，而无需内部索引访问内部 table，还可以执行在子查询展平后出现的外部联接和半联接。此外，当对内部 table 进行索引访问时，可以有效使用连接缓冲区。
连接缓冲区 Management 代码在存储感兴趣的行列的值时会稍微更有效地利用连接缓冲区空间：如果行列的值为NULL，则不会在缓冲区中为行列分配其他字节，并且为任何值分配最小字节数VARCHAR类型。
该代码支持两种类型的缓冲区：常规缓冲区和增量缓冲区。假设使用连接缓冲区B1来连接 tablet1和t2，并且使用连接缓冲区B2将该操作的结果与 tablet3进行连接：
-
常规连接缓冲区包含每个连接操作数中的列。如果
B2是常规连接缓冲区，则放入B2的每一行*r由来自B1的行r1的列和来自 tablet3的匹配行r2*的有趣的列组成。 -
增量连接缓冲区仅包含第二个连接操作数产生的 table 行中的列。也就是说，它从第一个操作数缓冲区递增到一行。如果
B2是增量连接缓冲区，则它包含行*r2*的有趣列以及从B1到行r1*的链接。
增量连接缓冲区始终相对于来自较早连接操作的连接缓冲区是增量的，因此来自第一个连接操作的缓冲区始终是常规缓冲区。在刚刚给出的示例中，用于联接 tablet1和t2的缓冲区B1必须是常规缓冲区。
用于联接操作的增量缓冲区的每一行仅包含要联接 table 中一行的有趣列。这些列通过引用第一个连接操作数产生的 table 中匹配行的有趣列来进行扩充。增量缓冲区中的几行可以引用同一行* r *，只要所有这些行都与 row * r *匹配，它们的列就存储在先前的连接缓冲区中。
增量缓冲区使从以前的联接操作所使用的缓冲区中复制列的频率降低。这样可以节省缓冲区空间，因为通常情况下，第一个联接操作数产生的行可以与第二个联接操作数产生的几行匹配。不必从第一个操作数复制一行。由于减少了复制时间，因此增量缓冲区还可以节省处理时间。
optimizer_switch系统变量的block_nested_loop和batched_key_access标志控制优化器如何使用块嵌套循环和批处理密钥访问联接算法。默认情况下，block_nested_loop是on，而batched_key_access是off。参见第 8.9.2 节“可切换的优化”。优化器提示也可能适用；参见块嵌套循环和批处理密钥访问算法的优化器提示。
有关半联接策略的信息，请参阅第 8.2.2.1 节“使用半联接转换优化子查询，派生 table 和视图引用”
用于外部联接和半联接的块嵌套循环算法
MySQL BNL 算法的原始实现已扩展为支持外部联接和半联接操作。
当使用连接缓冲区执行这些操作时，放入缓冲区的每一行都将提供一个匹配标志。
如果使用联接缓冲区执行外部联接操作，则检查第二个操作数生成的 table 的每一行是否与联接缓冲区中的每一行匹配。找到匹配项后，将形成一个新的扩展行(原始行加上第二个操作数中的列)，并通过其余的 join 操作发送以进行进一步扩展。另外，启用缓冲区中匹配行的匹配标志。在检查了要连接的 table 的所有行之后，将扫描连接缓冲区。缓冲区中未启用匹配标志的每一行都被扩展NULL补码(第二个操作数中每一列的NULL值)，并通过其余的联接操作发送以进行进一步扩展。
optimizer_switch系统变量的block_nested_loop标志控制优化器如何使用块嵌套循环算法。默认情况下，block_nested_loop是on。参见第 8.9.2 节“可切换的优化”。优化器提示也可能适用；参见块嵌套循环和批处理密钥访问算法的优化器提示。
在EXPLAIN输出中，当Extra值包含Using join buffer (Block Nested Loop)并且type值为ALL，index或range时，table 示对 table 使用 BNL。
在某些情况下，涉及一个或多个子查询与一个或多个左联接的组合，尤其是返回许多行的联接，可能会使用 BNL，即使在这种情况下并不理想。这是一个已知问题，已在 MySQL 8.0 中修复。如果升级 MySQL 对您而言不是立即可行的，那么您可能希望同时通过设置optimizer_switch='block_nested_loop=off'或使用NO_BNL优化器提示来禁用 BNL，以使优化器使用一个或多个索引提示(请参见第 8.9.4 节“索引提示”)或某些方式来选择更好的计划这些的组合，以提高此类查询的性能。
有关半联接策略的信息，请参阅第 8.2.2.1 节“使用半联接转换优化子查询，派生 table 和视图引用”
批量密钥访问联接
MySQL 实现了一种联接 table 的方法，称为批处理密钥访问(BKA)联接算法。当对第二个连接操作数产生的 table 进行索引访问时，可以应用 BKA。像 BNL 连接算法一样，BKA 连接算法采用连接缓冲区来累加连接操作的第一个操作数所产生的行的感兴趣的列。然后，BKA 算法将构建键以访问要为缓冲区中的所有行连接的 table，并将这些键批量提交给数据库引擎以进行索引查找。密钥通过多范围读取(MRR)界面(请参见第 8.2.1.10 节，“多范围读取优化”)提交给引擎。提交键之后，MRR 引擎函数以最佳方式在索引中执行查找，以获取由这些键找到的联接 table 的行，并开始向 BKA 联接算法提供匹配的行。每个匹配的行都与联接缓冲区中对行的引用耦合。
使用 BKA 时，值join_buffer_size定义了对存储引擎的每个请求中的密钥批次。缓冲区越大，连接操作右侧 table 的 Sequences 访问就越多，这可以显着提高性能。
要使用 BKA，必须将optimizer_switch系统变量的batched_key_access标志设置为on。 BKA 使用 MRR，因此mrr标志也必须为on。当前，对 MRR 的成本估算过于悲观。因此，对于_B _，mrr_cost_based也必须是off。以下设置启用 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(主键)Sequences 获取行。由于读取是按磁盘 Sequences 而不是随机 Sequences 进行的，因此可以提高性能。
-
第二种情况用于诸如NDB的远程存储引擎。 MySQL 服务器(SQL 节点)将来自连接缓冲区的一部分行的一组键及其关联，发送给 NDB Cluster 数据节点。作为回报，SQL 节点接收匹配行的包(或几个包)以及相应的关联。 BKA 联接算法采用这些行并构建新的联接行。然后，将一组新的密钥发送到数据节点，并将返回的包中的行用于构建新的联接行。该过程 continue 进行，直到将来自联接缓冲区的最后一个键发送到数据节点，并且 SQL 节点已接收并联接了与这些键匹配的所有行。这会提高性能，因为 SQL 节点发送到数据节点的键承载包更少，这意味着它与数据节点之间执行联接操作的往返次数更少。
在第一种情况下，保留了一部分连接缓冲区以存储由索引查找选择并作为参数传递给 MRR 函数的行 ID(主键)。
没有特殊的缓冲区来存储为连接缓冲区中的行构建的键。而是将为缓冲区中的下一行构建键的函数作为参数传递给 MRR 函数。
在EXPLAIN输出中，当Extra值包含Using join buffer (Batched Key Access)且type值为ref或eq_ref时，table 示对 table 使用 BKA。
块嵌套循环和批处理密钥访问算法的优化器提示
除了在整个会话范围内使用optimizer_switch系统变量来控制 BNL 和 BKA 算法在优化程序中的使用外，MySQL 还支持优化程序提示，以针对每个语句影响优化程序。参见第 8.9.3 节“优化程序提示”。
若要使用 BNL 或 BKA 提示为外部联接的任何内部 table 启用联接缓冲，必须为外部联接的所有内部 table 启用联接缓冲。
