join连接的底层原理和三种算法实现一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第8天，点击查看活

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第8天，点击查看活动详情

上篇文章讲到join连接的优化，我们知道了MySQL会有些情况下会将子查询变成inner连接查询，以及inner join和left/right join都有可能会相互转换，都在说能够使用join连接尽量不要使用子查询，这是为什么？本文主要讲述join的三种底层实现，SNLP/INLP/BNLP三种算法的使用场景。

不知道大家有没有注意到前面几篇在讲explain执行计划以及SQL优化的时候发现执行计划的extra字段输出的额外信息如下图：

这里是MySQL5.7，使用的是BNLJ（block nested loop join）一种块嵌套算法

如果是MySQL8.0版本，会使用hash join一种hash算法,如下图：

其实上述join优化的使用并不是每个场景都会生效，只有在join连接的情况下，且驱动表的type是all,range,index的时候才会生效，刚开始我一直没有出现这两种算法的使用，我很奇怪，最后被我查到了，之前因为我的表student的字段classId直接关联的是表class的id，使用所以导致驱动表的ref一直是eq_ref所以不会使用join buffer优化,于是我将表class的monitor字段值用class的id值覆盖，然后用monitor字段去充当join的on字段，就可以生效了，如下SQL：

explain select * from student s inner join class c on s.classId = c.monitor;

下面来介绍这几种算法的实现，以及什么时候会使用哪种算法，在正式开始介绍之前我想先说一下这几种算法的概念，嵌套循环算法其实和Java里的嵌套for循环一样，其实我们的join连接不就是相当于遍历然后符合条件的就记录下来吗？我们可以看下伪代码实现，很抽象：

for (int i = 0; i < a_size; i++) {
    for (int j = 0; j < b_size; j++) {
        if (a[i].field = b[j].field){
            //a表的连接字段等于b表的连接字段，符合条件记录下来
        }
    }
}