MySQL JOIN算法原理

MySQL的JOIN相关操作，是通过“嵌套循环连接算法，NLJ”或者该算法的优化变体“块嵌套循环连接算法，BNLJ”来实现的。

嵌套循环连接算法

两个表join时，可以简单理解为两层for循环，外层循环一般称作驱动表，循坏内层称作被驱动表。每次取出驱动表中一条数据，然后通过内循环遍历被驱动表，并在循环体中判断驱动表记录与被驱动表记录是否匹配。直到驱动表的全部记录都处理完毕为止。这就是嵌套循环连接算法的基本思想，伪代码如下。

for each row in t1 {
  for each row in t2  {
      if row satisfies join conditions, send to client
  }
}

块嵌套循环连接算法

大致思想就是建立一个缓存区，一次从驱动表中取多条记录，然后扫描被驱动表，被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配，如果匹配则连接并加入结果集。缓冲区越大，驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。伪代码如下。

for each row in t1 {
    store used columns from t1 in join buffer
    if buffer is full {
      for each row in t2 {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
}