MySQL中的JOIN BUFFER是数据库查询优化器用来提高JOIN操作性能的一种机制。在执行JOIN操作时，尤其是当涉及到多个表的JOIN时，JOIN BUFFER可以显著减少由于表之间的JOIN操作导致的磁盘I/O次数，从而提高查询速度。

工作原理

选择驱动表：优化器会选择一个表作为驱动表，通常是预计返回行数较少的那个表。
读取行到JOIN BUFFER：优化器会将驱动表的一部分行读取到JOIN BUFFER中。
与被JOIN表匹配：然后，数据库会使用JOIN BUFFER中的行与被JOIN表中的行进行匹配。
重复步骤直到完成：这个过程会重复进行，直到驱动表的所有行都已经被处理。

JOIN BUFFER的类型

Block Nested-Loop Join（BNL）

原理：

在标准的Nested-Loop Join（NLJ）算法中，对于驱动表（外部循环）中的每一行，数据库都会扫描被驱动表（内部循环）以找到匹配的行。如果被驱动表很大且没有索引可用，这将导致大量的磁盘I/O。
BNL算法通过使用JOIN BUFFER来减少这种磁盘I/O。它不是对驱动表的每一行都扫描被驱动表，而是将驱动表的一部分行读入JOIN BUFFER，然后一次性与被驱动表进行比较。

步骤：

选择一个较小的表作为驱动表。
将驱动表的一部分行（一批）读入JOIN BUFFER。
对JOIN BUFFER中的行进行排序和去重，以减少与被驱动表比较时的开销。
扫描被驱动表，将JOIN BUFFER中的行与被驱动表的行进行比较。
重复步骤2-4，直到驱动表的所有行都被处理。

优点：

减少了被驱动表的扫描次数，从而减少了磁盘I/O。

缺点：

JOIN BUFFER的大小限制了可以一次性处理的行数，如果JOIN BUFFER不够大，则可能需要多次迭代，影响性能。
对于非常大的表，JOIN BUFFER可能不足以容纳足够的行，导致性能下降。

Batched Key Access（BKA）

原理：

BKA算法结合了索引查找和JOIN BUFFER。当JOIN条件部分可以使用索引时，BKA算法可以减少对索引的查找次数。
BKA首先使用索引来找到驱动表中的一批行，然后将这些行的键值读入JOIN BUFFER。接着，使用JOIN BUFFER中的键值批量访问被驱动表。

步骤：

使用索引在驱动表中找到一批行。
将这些行的键值读入JOIN BUFFER。
使用JOIN BUFFER中的键值对被驱动表进行批量访问。
对被驱动表返回的行与JOIN BUFFER中的行进行匹配。
重复步骤1-4，直到驱动表的所有行都被处理。

优点：

减少了索引查找的次数，提高了索引的使用效率。
相比于BNL，BKA通常能提供更好的性能，特别是当驱动表和被驱动表都有索引可用时。

Hash Join（从MySQL 8.0.18开始支持）

原理：

Hash Join是一种在两个表之间执行JOIN操作的算法，它通过在内存中创建哈希表来实现快速匹配。
MySQL使用JOIN BUFFER来构建哈希表，该哈希表基于驱动表中的JOIN列。

步骤：

选择一个较小的表作为驱动表，并读取其所有行到JOIN BUFFER。
使用JOIN BUFFER中的行构建一个哈希表。
扫描被驱动表，对于每一行，使用哈希函数查找哈希表中的匹配行。
将被驱动表的行与哈希表中的匹配行进行JOIN操作。

优点：

对于大数据集，Hash Join通常比BNL和BKA更高效，因为它减少了比较次数。
当JOIN条件无法有效利用索引时，Hash Join可以提供显著的性能提升。

缺点：

哈希表需要足够的内存来存储，如果JOIN BUFFER不足以容纳整个驱动表，则可能无法使用Hash Join。
对于小数据集，哈希表的构建和查找开销可能不如其他JOIN算法高效。

配置选项

在MySQL中，有几个配置选项可以影响JOIN BUFFER的大小和行为：

join_buffer_size：控制单个JOIN BUFFER的默认大小。
max_join_size：限制连接操作所处理的最大行数。
tmp_table_size 和 max_heap_table_size：控制何时使用磁盘上的临时表而非内存中的临时表。