MySQL中的Join Buffer定义
join_buffer_size 在MySQL官方文档中的解释为用于类型为普通的索引扫，范围扫和全表扫的连接缓冲区，两个表连接时会产生一个join buffer，多个表的连接可以产生多个join buffer，join buffer在表连接发生之前进行分配，在SQL语句执行完毕之后进行释放。
Join Buffer应该设置多大？
join_buffer_size 官方默认值为256K，这个值对大表间的连接查询多数情况下是不够用的，但是join_buffer_siz是一个连接级别的参数，并不是全局参数，受并发连接数影响，所以需要结合自身的实际情况来分配，如果内存充足可适量调大，如调整为2M/4M/8M/16M，如果内存资源紧张且并发连接数高需谨慎调整，调大可能会让你的数据库因内存不足可造成系统OOM实例宕机的风险。
Join Buffer 如何设置？
- 全局统一设置
set global join_buffer_size = 4*1024*1024; --设置join_buffer_size为4M大小复制
更新my.cnf文件防止重启后失效：join_buffer_size=4M
- 会话级别的临时设置
set session join_buffer_size = 4 * 1024 * 1024; select * from .....复制
- 指定在特定SQL语句中设置
select /*+ SET_VAR(join_buffer_size = 16M) */ * from ...; 说明：对于可以识别到的数据量比较大的连接查询建议使用该方式（ /*+ SET_VAR(......) */ ）进行设置，这样可以节省内存资源。复制
Join Buffer 何时起作用？
在MySQL中两个表之间的关联算法在5.7版本的时候只有 Nested-Loop Join嵌套循环 简称NLJ。其含义大致就是先从表A里拿出来一条记录 R1，完了再用 R1 遍历表 B 里的每一条记录，并且字段 c1 来做匹配是否相同以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
驱动表：驱动表会选取数据量比较小的一张表，根据下图的执行计划截图，驱动表是rock2表。
被驱动表：被驱动表为rock1。
mysql> desc select * from rock1 inner join rock2 on rock1.a=rock2.a; +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+-------------+ | 1 | SIMPLE | rock2 | NULL | ALL | NULL | NULL | NULL | NULL | 997779 | 100.00 | Using where | | 1 | SIMPLE | rock1 | NULL | ref | a | a | 403 | world.rock2.a | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)复制
还有一种关联算法叫Block Nested-Loop Join ，块嵌套循环，简称 BNLJ 就是join buffer这块内存起的作用。join_buffer_size 就是在块嵌套循环中为了减少过多的表扫描而设计的，那 BNLJ 比 NLJ 来说，中间多了一块 BUFFER 来缓存外表的对应记录从而减少了外表的循环次数，也就减少了内表的匹配次数。
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。下面这个sql，Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
mysql> desc select * from rock3 inner join rock4 on rock3.a=rock4.a; +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+---------------------------------------------------------+ | 1 | SIMPLE | rock4 | NULL | ALL | NULL | NULL | NULL | NULL | 997779 | 100.00 | Using where | | 1 | SIMPLE | rock3 | NULL | ref | a | a | 403 | world.rock4.a | 1 | 100.00 | Using where;Using join buffer(Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+---------------+--------+----------+---------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)复制
MySQL8.0版本出现了 hash join，也是用了join buffer来做缓存，但是和 BNLJ 不同的是，它在 JOIN BUFFER 中以外表为基础建立一张哈希表，内表通过哈希算法来跟哈希表进行匹配，hash join 也就是进一步减少内表的匹配次数。
mysql> explain format=tree select * from rock1 inner join rock2 as t using(c1)\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (rock1.c1 = rock2.c1) (cost=324786300.01 rows=430081015) -> Table scan on rock1 (cost=189.00 rows=8779470) -> Hash -> Table scan on t (cost=111.00 rows=3200) 1 row in set (0.00 sec)复制
Join Buffer 总结及注意事项
- 只有执行计划结果type为 ALL,index,range时才可能会用到join_buffer_size
- 当多个表join时，会给每个join分别分配join_buffer_size，所以一条SQL语句可能会有多个join_buffer_size。
- join_buffer_size只回缓存SQL语句查询涉及到的字段数据，不会缓存多余的数据。
- 对于join_buffer_size的优化，由于是每个会话单独分配，因此需要根据最大连接数，服务器可用内存来分配，防止内存用尽或OOM，一般4-8M是一个比较合理的值，对于复杂的连接sql，可以在SQL语句中提前通过hint方式 SET_VAR 命令 进行join_buffer_size大小的设置。
