mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:Simple Nested-Loop Join,Index Nested-Loop Join,Block Nested-Loop Join(简单-索引-缓冲区)
(注:参考公众号:InsideMySQL)
原理:
1.Simple Nested-Loop Join:
如下图,r为驱动表,s为匹配表,可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列,然后再合并数据,对s表进行了rn次访问,对数据库开销大
![c0f935f7d1b5d8a6ee8644081caec5dc.png](https://i-blog.csdnimg.cn/blog_migrate/1fa482869f6d048125f0af8381501d54.png)
2.Index Nested-Loop Join(索引嵌套):
这个要求非驱动表(匹配表s)上有索引,可以通过索引来减少比较,加速查询。
在查询时,驱动表(r)会根据关联字段的索引进行查找,挡在索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表(s)的关联健是主键的话,性能会非常高,如果不是主键,要进行多次回表查询,先关联索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。
![387c4bceec8bc51e672ad9fa145a905d.png](https://i-blog.csdnimg.cn/blog_migrate/15a29265450e3b1ff54f22bfadb40b27.jpeg)
3.Block Nested-Loop Join:
如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表(s)的访问频率。默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
![25f9457663627e4ee79d3371b92e870d.png](https://i-blog.csdnimg.cn/blog_migrate/faedc1124abb512707cfa842ff610840.jpeg)
left join:会保全左表数据,如果右表没相关数据,会显示null
fight join:会保全右表数据,如果左表没相关数据,会显示null
inner join:部分主从表,结果会取两个表针对on条件相匹配的最小集