NLJ和BNLJ之JOIN底层算法

Nested Loop Join （NLJ）算法:

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

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,            //进行条件匹配，若满足，发给client
send to client
}
}
}

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

for(;;)       //外层循环
{
for(;;)   //内层循环
{
...
}
}

Block Nested Loop Join （BNLJ）算法:

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

for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer      //将t1，t2的记录放入join buffer
if buffer is full {                                //如果buffer不为空
for each row in t3 {                             //t3和t1，t2的combination 进行匹配
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer                                     //将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
}
}
}