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

77 阅读 0 评论 51 点赞

我是靠谱客的博主含糊柠檬，最近开发中收集的这篇文章主要介绍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大到足以容纳所有以前的行组合。在这一点上，不能通过增大速度来获得速度。