MySQL 使用嵌套循环算法（Nested-Loop Algorithm）或其变体来执行表之间的连接。
嵌套循环连接算法种类
-
简单嵌套循环连接算法 (Nested-Loop Join, NLJ)
-
块嵌套循环连接算法 (Block Nested-Loop Join, BNL)
简单嵌套循环连接算法 (NLJ)
简单嵌套循环连接算法逐个从外层表中读取一行记录，并将每一行传递到嵌套循环中以处理下一张表。这个过程会对剩余要连接的所有表重复。
假设需要对三张表 t1、t2 和 t3 执行以下连接类型的查询：
| 表名 | 连接类型 |
|---|---|
| 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 // 将结果发送给客户端
}
}
}
}
由于 NLJ 算法会将每一行从外层循环传递到内层循环，因此通常会多次读取内层表。
块嵌套循环连接算法 (BNL)
块嵌套循环连接算法通过缓冲外层循环读取的多行数据来减少对内层表的读取次数。例如，如果将 10 行数据读入缓冲区，并将其传递给下一个内层循环，则内层循环读取的每一行可以与缓冲区中的 10 行进行比较。这将减少内层表读取次数约一个数量级。
在 MySQL 8.0.18 之前，BNL 算法在无法使用索引的等值连接（equi-joins）中应用；在 MySQL 8.0.18 及之后版本中，使用哈希连接优化（hash join optimization）。从 MySQL 8.0.20 开始，不再使用块嵌套循环，取而代之的是哈希连接。详见 10.2.1.4 哈希连接优化。
MySQL 连接缓冲特性
-
连接缓冲适用于 ALL 或 index 类型的连接（即无法使用任何键的连接，全表扫描或索引扫描），以及 range 类型的连接。
-
对于第一个非常量表，即使其类型为 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 { // 遍历 t3 的每一行
for each t1, t2 combination in join buffer { // 遍历缓冲区中每种 t1、t2 组合
if row satisfies join conditions { // 如果满足连接条件
send to client // 发送结果给客户端
}
}
}
}
块嵌套循环的优化
如果连接缓冲区中存储的每种 t1 和 t2 的组合大小为 S，缓冲区中的组合数为 C，则表 t3 的扫描次数计算公式为：
当 join_buffer_size 足够大以容纳所有先前的行组合时，表 t3 的扫描次数达到最小，不再通过增大 join_buffer_size 提升速度。
