Join原理(2)--连接原理（四十)

前面说了join的用法，外连接有左连接，右连接，内连接，当用外连接的时候，on代表驱动表数据一定会查询来，被驱动表则查出来是null，内连接则on和where使用是一样的，where则是全部过滤掉，不管驱动还是被驱动表不符合的都不返回。

Join原理

明白了左连接还右连接内连接之后，下面介绍他的原理

嵌套循环连接（Nested-Loop join）

上篇文章我们说的其实就是嵌套循环查询方法，比如驱动表查出来3条数据，则被驱动表会吧三条数据全部一条条带入，比如t2.m1 = t1.m1，则会查询三次被驱动表，若链接了三个表，然后则第三个表又在前面表查询出来的基础上，插叙多次，这样一层层嵌套循环，就是嵌套循环查询。

使用索引加快连接速度

我们前面说过嵌套查询分为两个步骤，在回顾一下

步骤1：先查询驱动表的所有数据，结果若果有两条

步骤2：t2.m1 = 2 and t2.n1<’d’，t2.m2 = 3 and t2.n1<’d’这两条全部带入被驱动表查询。

如果在步骤2的时候全部都是全表查询，那将是对数据库的灾难，连接的表越多，查询的越多，这就是为什么经常用连接会导致sql性能差的原因。比如内连接，若不加限制条件，结果呈指数增长，这个结果类似。所以我们可以给被驱动表加上索引，别忘了驱动表获取到数据只会，被驱动表就是单表查询了。

Select * from t2 where t2.m1 = 2 and t2.n1<’d’

Select * from t2 where t2.m1 = 3 and t2.n1<’d’;

这时候如果我们给m1加上索引的话，那么他会走二级索引树，先查询二级索引的b+树叶子节点，之后再回表过滤n1<’d’，这时候访问数据库就是ref方法。(这里有一点需要注意，假设m1是是主键或者的二级索引，那么他访问的方法将会是const方法，而设计师吧这种对外连接里面对被驱动表查询采用主键或者二级索引的方法，称为eq_ref)

若我们给n1加索引的话，那么他会走range方法访问数据库，之后在回表查询m1是否成立。

当然也可以给他们都设置索引，那么mysql优化器就会挑选一个性能更好的索引执行。当然建了索引也不一定使用索引，如果回表的代价太大，就不会回表查询。

另外我们如果where条件后面没有跟着驱动表的条件，只跟着被驱动表的条件，而查询的条件里又是索引的某个部分，比如联合索引的其中一个索引，这样我们也可以走index方法来访问数据库。

基于块的嵌套循环连接(Block Nested-Loop join)

生活中我们的数据可能海量的，不可能跟我们演示的一样是放三条，如果数据太多，导致内存放不下怎么办呢，则会吧内存前面的数据删掉，从磁盘吧新的数据放入内存。所以扫描表前面的记录，数据可能还在磁盘上，扫描到后面之后，前面的数据就从内存中释放。

所以如果被驱动表需要访问多次，每次访问都重新从磁盘上I/O读取数据库，再刷新到内存，这样无疑是缓慢的，所以这时候join buffer就出来了，直接把被驱动表的数据放入当前缓存，然后和驱动表查询出来的数据匹配，这样直接在内存里处理，无疑比一次一次的访问内存快很多。

好的情况是join buffer足够大，足够吧被驱动表的数据放入其中，mysql吧这个连接方式称为基于块的嵌套循环连接。

这种可以通过启动参数join_buffer_size配置，默认262144字节(256kb)，小设置128字节，当然好是给被驱动表加上索引查询。

另外注意，驱动表的列并不是所有的都放进join buffer，只有过滤和查询的列，所以这里再次强调，sql优化中，不要查询select *。

文章来源：知乎平台 原文地址：https://zhuanlan.zhihu.com/p/411141898