MySql Join连接三种算法

前言：

MySQL是只支持一种JOIN算法Nested-Loop Join（嵌套循环链接），不过MySQL的Nested-Loop Join（嵌套循环链接）也是有很多变种，能够帮助MySQL更高效的执行JOIN操作。

1. Simple Nested-Loop Join

  这个算法相对来说很简单，从驱动表A 中拿取关联条件A1 匹配被驱动表B所有列，得到结果记录。继续A2,A3… 直到驱动表A 中所有关联条件匹配完。然后合并所有记录，这种算法要对被驱动表B 进行 An 次访问，而且每次访问被驱动表B 都是全表扫描，性能非常差。

2. Index Nested-Loop Join

  从驱动表A中拿取关联条件A1 匹配被驱动表B，被驱动表B 是基于索引查询非常快(不用全表扫描)，得到结果记录。继续A2,A3… 直到驱动表A中所有关联条件匹配完。这种算法要对被驱动表B 进行 An 次访问，而被驱动表B 每次基于索引查询是非常快的，性能完全取决于驱动表A 的关联条件条数。

3. Block Nested-Loop Join

  在有索引的情况下，MySQL 会尝试去使用 Index Nested-Loop Join 算法，在有些情况下，可能 Join 的列就是没有索引，那么这时 MySQL 的选择绝对不会是最先介绍的 Simple Nested-Loop Join 算法，而是会优先使用Block Nested-Loop Join 的算法。

  Block Nested-Loop Join 对比 Simple Nested-Loop Join 多了一个中间处理的过程，也就是 join buffer，使用 join buffer 将驱动表的查询 JOIN 相关列都给缓冲到了 JOIN BUFFER 当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。
  这里需要注意一个问题，比如 Simple Nested-Loop Join 连接是按顺序去访问的，得到的结果集也是符合 表A的顺序；而 Block Nested-Loop Join 是多个关联字段一起去方法，可能先命中A2记录，得到的顺序是 B记录3、B记录1，顺序可能会错乱，这个需要注意。

  在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。