【MySQL精通之路】SQL优化(1)-查询优化(7)-嵌套循环联接

主博客：

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

【MySQL精通之路】SQL优化(1)-查询优化(6)-索引条件下推-CSDN博客

1.嵌套循环连接算法

一个简单的嵌套循环联接（NLJ）算法一次从循环中的第一个表中读取一行，将每一行传递给一个嵌套循环，该嵌套循环处理联接中的下一个表。只要有剩余的表要联接，这个过程就会重复多次。

请把上面这句话熟记于心~

假设三个表t1、t2和t3之间的联接将使用以下联接类型来执行：

sql">Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单的NLJ算法，则连接的处理方式如下：

sql">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}}
}

因为NLJ算法一次一行地从外循环传递到内循环，所以它通常会多次读取在内循环中处理的表。

2.块嵌套循环连接算法

块嵌套循环（BNL）联接算法使用外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。例如，如果将10行读取到缓冲器中，并且将缓冲器传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲器中的所有10行进行比较。这将使必须读取内部表的次数减少一个数量级。

在MySQL 8.0.18之前，当不能使用索引时，此算法适用于等联接；

在MySQL 8.0.18及更高版本中，这种情况下会使用哈希连接优化。

从MySQL 8.0.20开始，MySQL不再使用块嵌套循环，并且在所有以前使用块嵌套循环的情况都使用Hash联接替代。

请参阅“哈希连接优化”。

【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询-CSDN博客

2.1 MySQL联接缓冲具有以下特点：

当联接类型为ALL或索引（换言之，当不能使用可能的键，并且分别对数据行或索引行进行了完全扫描）或范围查询时，可以使用联接缓冲。

缓冲的使用也适用于外部联接，如“块嵌套循环和批量密钥访问联接”所述。

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

只有联接查询相关的列存储在联接缓冲区中，而不是整行。

join_buffer_size系统变量确定用于处理查询的每个联接缓冲区的大小。

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

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

对于前面为NLJ算法描述的示例联接（无缓冲），使用联接缓冲按如下方式进行联接：

sql">for each row in t1 matching range {for each row in t2 matching reference key {store used columns from t1, t2 in join bufferif 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}}
}

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