MySQL can employ join buffers to execute not only inner joins without index access to the inner table, but also outer joins and semijoins that appear after subquery flattening. MySQL不仅可以使用连接缓冲区来执行内部连接，而无需对内部表进行索引访问，还可以执行子查询平坦化后出现的外部连接和半连接。Moreover, a join buffer can be effectively used when there is an index access to the inner table.此外，当存在对内部表的索引访问时，可以有效地使用联接缓冲区。

The join buffer management code slightly more efficiently utilizes join buffer space when storing the values of the interesting row columns: No additional bytes are allocated in buffers for a row column if its value is NULL, and the minimum number of bytes is allocated for any value of the VARCHAR type.join buffer management代码在存储感兴趣的行列的值时稍微更有效地利用了联接缓冲空间：如果行列的值为NULL，则不会在缓冲区中为其分配额外的字节，并且为VARCHAR类型的任何值分配最小的字节数。

The code supports two types of buffers, regular and incremental. 代码支持两种类型的缓冲区：常规缓冲区和增量缓冲区。Suppose that join buffer B1 is employed to join tables t1 and t2 and the result of this operation is joined with table t3 using join buffer B2:假设使用联接缓冲区B1联接表t1和t2，并且使用联接缓冲区B2将此操作的结果与表t3联接：

Incremental join buffers are always incremental relative to a join buffer from an earlier join operation, so the buffer from the first join operation is always a regular buffer. 增量连接缓冲区始终是相对于先前连接操作的连接缓冲区的增量，因此第一个连接操作的缓冲区始终是常规缓冲区。In the example just given, the buffer B1 used to join tables t1 and t2 must be a regular buffer.在刚刚给出的示例中，用于连接表t1和t2的缓冲区B1必须是常规缓冲区。

Each row of the incremental buffer used for a join operation contains only the interesting columns of a row from the table to be joined. 用于联接操作的增量缓冲区的每一行只包含要联接的表中某一行的感兴趣列。These columns are augmented with a reference to the interesting columns of the matched row from the table produced by the first join operand. 这些列通过对第一个联接操作数生成的表中匹配行的感兴趣列的引用进行扩充。Several rows in the incremental buffer can refer to the same row r whose columns are stored in the previous join buffers insofar as all these rows match row r.增量缓冲区中的几行可以引用同一行r，只要这些行与行r匹配，这些行的列就存储在以前的联接缓冲区中。

Incremental buffers enable less frequent copying of columns from buffers used for previous join operations. 增量缓冲区可以减少从先前联接操作所用缓冲区复制列的频率。This provides a savings in buffer space because in the general case a row produced by the first join operand can be matched by several rows produced by the second join operand. 这节省了缓冲区空间，因为在一般情况下，第一个联接操作数生成的行可以与第二个联接操作数生成的多行匹配。It is unnecessary to make several copies of a row from the first operand. Incremental buffers also provide a savings in processing time due to the reduction in copying time.不需要从第一个操作数复制一行的多个副本。由于复制时间的减少，增量缓冲区还可以节省处理时间。

In MySQL 8.0, the block_nested_loop flag of the optimizer_switch system variable works as follows:在MySQL 8.0中，optimizer_switch系统变量的block_nested_loop标志的工作原理如下：

The batched_key_access flag controls how the optimizer uses the Batched Key Access join algorithms.batched_key_access标志控制优化器如何使用批处理密钥访问连接算法。

By default, block_nested_loop is on and batched_key_access is off. 默认情况下，block_nested_loop处于on状态，batched_key_access处于off状态。See Section 8.9.2, “Switchable Optimizations”. 请参阅第8.9.2节，“可切换优化”。Optimizer hints may also be applied; see Optimizer Hints for Block Nested-Loop and Batched Key Access Algorithms.也可以应用优化器提示；请参阅有关块嵌套循环和批处理密钥访问算法的优化器提示。

For information about semijoin strategies, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”有关半连接策略的信息，请参阅第8.2.2.1节，“使用半连接转换优化IN和EXISTS子查询谓词”。

The original implementation of the MySQL BNL algorithm was extended to support outer join and semijoin operations (and was later superseded by the hash join algorithm; see Section 8.2.1.4, “Hash Join Optimization”).MySQL BNL算法的原始实现被扩展以支持外部连接和半连接操作（后来被哈希连接算法取代；请参阅第8.2.1.4节，“哈希连接优化”）。

When these operations are executed with a join buffer, each row put into the buffer is supplied with a match flag.当使用联接缓冲区执行这些操作时，放入缓冲区的每一行都会提供一个匹配标志。

If an outer join operation is executed using a join buffer, each row of the table produced by the second operand is checked for a match against each row in the join buffer. 如果使用联接缓冲区执行外部联接操作，则将检查第二个操作数生成的表的每一行是否与联接缓冲区中的每一行匹配。When a match is found, a new extended row is formed (the original row plus columns from the second operand) and sent for further extensions by the remaining join operations. 找到匹配项后，将形成一个新的扩展行（原始行加上第二个操作数中的列），并通过剩余的联接操作发送以进行进一步扩展。In addition, the match flag of the matched row in the buffer is enabled. 此外，将启用缓冲区中匹配行的匹配标志。After all rows of the table to be joined have been examined, the join buffer is scanned. 检查要联接的表的所有行之后，将扫描联接缓冲区。Each row from the buffer that does not have its match flag enabled is extended by NULL complements (NULL values for each column in the second operand) and sent for further extensions by the remaining join operations.缓冲区中未启用匹配标志的每一行都通过NULL补码（第二个操作数中每一列的NULL值）进行扩展，并通过其余的联接操作进行进一步扩展。

In MySQL 8.0, the block_nested_loop flag of the optimizer_switch system variable works as follows:在MySQL 8.0中，optimizer_switch系统变量的block_nested_loop标志的工作原理如下：

See Section 8.9.2, “Switchable Optimizations”, for more information. 有关更多信息，请参阅第8.9.2节，“可切换的优化”。Optimizer hints may also be applied; see Optimizer Hints for Block Nested-Loop and Batched Key Access Algorithms.也可以应用优化器提示；请参阅有关块嵌套循环和批处理密钥访问算法的优化器提示。

In EXPLAIN output, use of BNL for a table is signified when the Extra value contains Using join buffer (Block Nested Loop) and the type value is ALL, index, or range.在EXPLAIN输出中，当Extra值包含Using join buffer (Block Nested Loop)且type值为ALL、index或range时，表示对表使用BNL。

For information about semijoin strategies, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”有关半连接策略的信息，请参阅第8.2.2.1节，“使用半连接转换优化IN和EXISTS子查询谓词”。

MySQL implements a method of joining tables called the Batched Key Access (BKA) join algorithm. MySQL实现了一种连接表的方法，称为批处理密钥访问（BKA）连接算法。BKA can be applied when there is an index access to the table produced by the second join operand. 当对第二个联接操作数生成的表进行索引访问时，可以应用BKA。Like the BNL join algorithm, the BKA join algorithm employs a join buffer to accumulate the interesting columns of the rows produced by the first operand of the join operation. 与BNL join算法一样，BKA join算法使用联接缓冲区来累积联接操作的第一个操作数生成的行的感兴趣列。Then the BKA algorithm builds keys to access the table to be joined for all rows in the buffer and submits these keys in a batch to the database engine for index lookups. 然后，BKA算法构建键来访问缓冲区中所有行要联接的表，并将这些键批量提交给数据库引擎进行索引查找。The keys are submitted to the engine through the Multi-Range Read (MRR) interface (see Section 8.2.1.11, “Multi-Range Read Optimization”). 钥匙通过多量程读取（MRR）接口提交给发动机（请参阅第8.2.1.11节，“多量程读取优化”）。After submission of the keys, the MRR engine functions perform lookups in the index in an optimal way, fetching the rows of the joined table found by these keys, and starts feeding the BKA join algorithm with matching rows. 提交键后，MRR引擎函数以最佳方式在索引中执行查找，获取由这些键找到的联接表的行，并开始向BKA联接算法提供匹配行。Each matching row is coupled with a reference to a row in the join buffer.每个匹配行与连接缓冲区中的行的引用耦合。

When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. 使用BKA时，join_buffer_size的值定义对存储引擎的每个请求中密钥批的大小。The larger the buffer, the more sequential access is made to the right hand table of a join operation, which can significantly improve performance.缓冲区越大，对联接操作右侧表的顺序访问就越多，这可以显著提高性能。

For BKA to be used, the batched_key_access flag of the optimizer_switch system variable must be set to on. 要使用BKA，必须将optimizer_switch系统变量的batched_key_access标志设置为on。BKA uses MRR, so the mrr flag must also be on. BKA使用MRR，因此mrr标志也必须打开。Currently, the cost estimation for MRR is too pessimistic. 目前，MRR的估算也很悲观。Hence, it is also necessary for mrr_cost_based to be off for BKA to be used. 因此，为了使用BKA，还必须关闭mrr_cost_based的功能。The following setting enables BKA:以下设置启用BKA：

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

There are two scenarios by which MRR functions execute:MRR功能的执行有两种情况：

With the first scenario, a portion of the join buffer is reserved to store row IDs (primary keys) selected by index lookups and passed as a parameter to the MRR functions.在第一种情况下，连接缓冲区的一部分被保留用于存储由索引查找选择的行ID（主键），并作为参数传递给MRR函数。

There is no special buffer to store keys built for rows from the join buffer. 没有特殊的缓冲区来存储为join缓冲区中的行生成的键。Instead, a function that builds the key for the next row in the buffer is passed as a parameter to the MRR functions.相反，为缓冲区中的下一行生成键的函数作为参数传递给MRR函数。

In EXPLAIN output, use of BKA for a table is signified when the Extra value contains Using join buffer (Batched Key Access) and the type value is ref or eq_ref.在EXPLAIN输出中，当Extra值包含Using join buffer (Batched Key Access)且type值为ref或eq_ref时，表示对表使用BKA。

In addition to using the optimizer_switch system variable to control optimizer use of the BNL and BKA algorithms session-wide, MySQL supports optimizer hints to influence the optimizer on a per-statement basis. 除了使用optimizer_switch系统变量来控制整个会话范围内使用BNL和BKA算法的优化器外，MySQL还支持优化器提示，以每语句为基础影响优化器。See Section 8.9.3, “Optimizer Hints”.请参阅第8.9.3节，“优化器提示”。

To use a BNL or BKA hint to enable join buffering for any inner table of an outer join, join buffering must be enabled for all inner tables of the outer join.要使用BNL或BKA提示为外部联接的任何内部表启用联接缓冲，必须为外部联接的所有内部表启用联接缓冲。