MySQL-5.7-8.2.1.6 Nested-Loop Join Algorithms(嵌套循环连接算法)

MySQL executes joins between tables using a nested-loop algorithm or variations on it.

MySQL使用嵌套循环算法或其变体来执行表之间的连接。

Nested-Loop Join Algorithm

嵌套循环连接算法

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之间的连接使用以下连接类型执行:

If a simple NLJ algorithm is used, the join is processed like this:

如果使用一个简单的NLJ算法，连接是这样处理的:

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算法每次将一行从外部循环传递到内部循环，所以它通常会多次读取在内部循环中处理的表。

Block Nested-Loop Join Algorithm

块嵌套循环连接算法

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.

Block Nested-Loop (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.

 这将从一个数量级上减少必须读取内部表的次数。

MySQL join buffering has these characteristics:

MySQL连接缓冲具有以下特点

• Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or range. Use of buffering is also applicable to outer joins, as described in Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

• 当联接类型为ALL或index(换句话说，当不可能使用键，并且分别对数据行或索引行进行了完全扫描时)或range时，可以使用联接缓冲。

• A join buffer is never allocated for the first nonconstant table, even if it would be of type ALL or index.

• 联接缓冲区永远不会分配给第一个非常量表，即使它的类型是ALL或index。

• Only columns of interest to a join are stored in its join buffer, not whole rows.

• 只有连接感兴趣的列存储在连接缓冲区中，而不是整行。

• The join_buffer_size system variable determines the size of each join buffer used to process a query.

• join_buffer_size系统变量决定了用于处理查询的每个连接缓冲区的大小。

• One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.

• 为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区处理给定的查询。

• A join buffer is allocated prior to executing the join and freed after the query is done.

• 联接缓冲区是在执行联接之前分配的，并在查询完成后释放。

For the example join described previously for the NLJ algorithm (without buffering), the join is done as follows using join buffering:

对于之前描述的NLJ算法的示例连接(没有缓冲)，连接是通过使用连接缓冲完成的:

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的次数为:

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. At that point, no speed is gained by making it larger.

t3扫描的数量随着join_buffer_size值的增加而减少，直到join_buffer_size大到足以容纳所有以前的行组合。在这一点上，不能通过增大速度来获得速度。
​