MySQL 8.0 官方文档 第八章 优化（八）—— 嵌套循环连接算法

第八章 优化（八）—— 嵌套循环连接算法

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.7 嵌套循环连接算法

MySQL使用嵌套循环算法或其变体在表与表之间执行连接，一共有两种方式，即：

• 嵌套循环连接算法

• 块嵌套循环连接算法

嵌套循环连接算法

简单嵌套循环连接(nested-loop join --> NLJ)算法每次从外循环的第一个表中读取一行，然后，把读取的每一行传递给嵌套的内部循环，该循环处理连接中的下一个表（译者：即传递进来的那行数据与内部表的每行数据进行连接）。只要还有表需要连接，这个过程就会进行下去。

假设有三个表t1、t2和t3，它们之间进行的连接将使用以下连接类型：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单嵌套循环连接算法，则按如下方式进行连接：

for each row in t1 matching range {                            # 循环t1表匹配范围的每一行
	for each row in t2 matching reference key {                # 循环t2表匹配引用键的每一行
		for each row in t3 {                                   # 循环t3表的每一行
			if row satisfies join conditions, send to client   # 如果该行符合连接条件，发送到客户端
		}
	}
}

因为嵌套循环连接算法每次从外部循环传递一行到内部循环，与内部循环中的表中符合条件的行进行比较连接，所以它通常会多次读取内部循环中表中的数据进行处理。

块嵌套循环连接算法

块嵌套循环（Block Nested-Loop --> BNL）连接算法采用在外部循环中一次读取一定量的行保存到缓冲区，然后把缓冲的数据全部传递给内部循环，在内部进行处理，从而减少了必须读取内部循环中的表的次数。例如，如果将10行数据读入到缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行处理。这将使必须读取内部表的次数减少一个数量级。

在MySQL 8.0.18之前，该算法适用于没有索引的等值连接；在MySQL 8.0.18及更高版本中，在这种情况下，会使用哈希连接优化。从MySQL8.0.20开始，MySQL不再使用块嵌套循环，在以前使用块嵌套循环的所有情况下都使用哈希连接。请参阅第8.2.1.4节“哈希连接优化”。

MySQL连接缓冲具有以下特点：

• 当连接类型为全表（ALL）或索引（index）（换句话说，当不能使用可能的键（possible keys），并且分别对数据行或索引行进行完全扫描）或范围（range）时，可以使用连接缓冲。缓存的使用也适用于外部连接，如8.2.1.12节“块嵌套循环和批量键访问连接”所述。

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

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

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

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

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

相对于前面的嵌套循环连接算法描述的连接示例（无缓冲），现在使用连接缓冲算法，按如下方式进行连接：

for each row in t1 matching range {                               # 循环t1表匹配范围的每一行
	for each row in t2 matching reference key {                   # 循环t2表匹配引用键的每一行
		store used columns from t1, t2 in join buffer             # 在连接缓冲区中保存t1和t2表中用到的列
		if buffer is full {                                       # 如果缓冲区已满
			for each row in t3 {                                  # 循环t3表的每一行
				for each t1, t2 combination in join buffer {      # 循环缓冲区中每个t1和t2表中列的结合体
				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
		}
	}
}

如果S是连接缓冲区中存储的每个t1,t2表中列组合的大小，C为连接缓冲区中存储的组合数，则扫描表 t3的次数为：

(S * C) / join_buffer_size + 1

对t3表扫描的次数会随着 join_buffer_size（连接缓冲区大小）值的增加而减少，直到 join_buffer_size足够大，足以容纳所有之前的行组合。达到这一点后，使它再变大也不能提高速度了。

上一集 MySQL 8.0 官方文档 第八章 优化（七）—— 索引条件下推优化

下一集 MySQL 8.0 官方文档 第八章 优化（九）—— 嵌套连接优化