mysql的NLJ_一文看懂mysql两种join连接算法--NLJ和BNL

概述

相信许多开发/DBA在使用MySQL的过程中，对于MySQL处理多表关联的方式或者说性能一直不太满意。对于开发提交的含有join的查询，一般比较抗拒，从而建议将join拆分，避免join可能带来的性能问题，同时也增加了程序和DB的网络交互。

5.5 版本之前，MySQL本身只支持一种表间关联方式，就是嵌套循环(Nested Loop)。如果关联表的数据量很大，则join关联的执行时间会非常长。在5.5以后的版本中，MySQL通过引入BNL算法来优化嵌套执行,今天主要介绍两种join算法 Nested-Loop Join (NLJ) 和Block Nested-Loop Join(BNL) .

一、Nested Loop Join (NLJ)算法:

NLJ，顾名思义，是指嵌套循环算法，my.oschina.net 上面有一段代码对NLJ做出了说明：

即，将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。

(此处仅对两层循环分析，多层循环可以将最内层循环看作一层，将其余的看作一层进行分析)

这里可以将内层表看作被驱动表，外层表看作驱动表。每次join时，从驱动表中先拿出一条数据和被驱动表进行条件匹配，若匹配成功，则将数据连接后放入结果集。接着，外层的驱动表扫描获取第二条记录，并和被驱动表进行条件匹配，将成功的记录连接后放入结果集，剩余数据以此类推。最后，将结果集发给请求的客户端。

并且，这个模型和C语言的双层循环(或者多层循环)很相似。

for(;;) //外层循环

{

for(;;) //内层循环

{

...

}

}

二、Block Nested Loop Join (BNLJ)算法:

BNLJ，块嵌套循环。BNLJ是优化版的NLJ，BNLJ区别于NLJ的地方是它加入了缓冲区join buffer，它的作用是外层驱动表可以先将一部分数据事先存到join buffer中，然后再和内层的被驱动表进行条件匹配，匹配成功的记录将会连接后存入结果集，等待全部循环结束后，将结果集发给client即完成一次join。

以下是my.oschina.net 上一段对BNLJ的说明：

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为

(S * C)/join_buffer_size + 1

扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了。

BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率。

举个栗子：

如果内层循环有100条记录，外层循环也有100条记录，这样的话，每次外层循环先将10条记录放到buffer中，内层循环的100条记录每条与这个buffer中的10条记录进行匹配，只需要匹配内层循环总记录数次即可结束一次循环(在这里，即只需要匹配100次即可结束)，然后将匹配成功的记录连接后放入结果集中，接着，外层循环继续向buffer中放入10条记录，同理进行匹配，并将成功的记录连接后放入结果集。后续循环以此类推，直到循环结束，将结果集发给client为止。

可以发现，若用NLJ，则需要100 * 100次才可结束，BNLJ则需要100 / block_size * 100 = 10 * 100次就可结束，减少了9/10。

若是外层驱动表足够小，或者说恰恰是block_size大小的，那么每次的join将会只进行1 * 内层循环总记录数 = 内层循环总记录数 ，即每次只需要循环内层循环总记录数次就可结束并完成循环，即就是小表驱动大表。

MySQL使用Join Buffer有以下要点:

1. join_buffer_size变量决定buffer大小。

2. 只有在join类型为all, index, range的时候才可以使用join buffer。

3. 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。

4. 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。

5. 在join之前就会分配join buffer, 在query执行完毕即释放。

6. join buffer中只会保存参与join的列, 并非整个数据行。

总结

5.6版本及以后，优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ算法。需要注意的是：并不是所有的场景下Block Nested_Loop Join 都是最优选择！