A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. 简单嵌套循环联接（NLJ）算法从循环中的第一个表中一次读取一行，将每一行传递给一个嵌套循环，该嵌套循环处理联接中的下一个表。This process is repeated as many times as there remain tables to be joined.只要仍有表要联接，此过程就会重复多次。

Assume that a join between three tables t1, t2, and t3 is to be executed using the following join types:假设三个表t1、t2和t3之间的连接将使用以下连接类型执行：

Table   Join Type
t1      range
t2      ref
t3      ALL

If a simple NLJ algorithm is used, the join is processed like this:如果使用简单的NLJ算法，则连接的处理方式如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

Because the NLJ algorithm passes rows one at a time from outer loops to inner loops, it typically reads tables processed in the inner loops many times.由于NLJ算法一次将一行从外部循环传递到内部循环，因此它通常会多次读取在内部循环中处理的表。

A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. 块嵌套循环（BNL）联接算法使用外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. 例如，如果将10行读入一个缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。This reduces by an order of magnitude the number of times the inner table must be read.这将使必须读取内部表的次数减少一个数量级。

Prior to MySQL 8.0.18, this algorithm was applied for equi-joins when no indexes could be used; in MySQL 8.0.18 and later, the hash join optimization is employed in such cases. 在MySQL 8.0.18之前，该算法适用于无法使用索引的equi连接；在MySQL 8.0.18及更高版本中，在这种情况下使用哈希连接优化。Starting with MySQL 8.0.20, the block nested loop is no longer used by MySQL, and a hash join is employed for in all cases where the block nested loop was used previously. 从MySQL 8.0.20开始，MySQL不再使用块嵌套循环，并且在以前使用块嵌套循环的所有情况下都使用哈希联接。See Section 8.2.1.4, “Hash Join Optimization”.请参阅第8.2.1.4节，“哈希连接优化”。

MySQL join buffering has these characteristics:MySQL连接缓冲具有以下特点：

For the example join described previously for the NLJ algorithm (without buffering), the join is done as follows using join buffering:对于前面为NLJ算法描述的示例连接（无缓冲），使用连接缓冲按如下方式完成连接：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

If S is the size of each stored t1, t2 combination in the join buffer and C is the number of combinations in the buffer, the number of times table t3 is scanned is:如果S是连接缓冲区中存储的每个t1、t2组合的大小，C是缓冲区中的组合数，则扫描表t3的次数为：

(S * C)/join_buffer_size + 1

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. t3扫描的数量随着join_buffer_size值的增加而减少，直到join_buffer_size足够大以容纳所有以前的行组合为止。At that point, no speed is gained by making it larger.在这一点上，没有速度是通过使它更大。