MySQL基础 - 连接的原理

这篇文章写的比较好懂：https://zhuanlan.zhihu.com/p/54275505#:~:text=%20%E7%AE%80%E5%8D%95%E6%9D%A5%E8%AF%B4%E5%B5%8C%E5%A5%97%E5%BE%AA%E7%8E%AF%E8%BF%9E%E6%8E%A5%E7%AE%97%E6%B3%95%E5%B0%B1%E6%98%AF%E4%B8%80%E4%B8%AA%E5%8F%8C%E5%B1%82for%20%E5%BE%AA%E7%8E%AF%20%EF%BC%8C%E9%80%9A%E8%BF%87%E5%BE%AA%E7%8E%AF%E5%A4%96%E5%B1%82%E8%A1%A8%E7%9A%84%E8%A1%8C%E6%95%B0%E6%8D%AE%EF%BC%8C%E9%80%90%E4%B8%AA%E4%B8%8E%E5%86%85%E5%B1%82%E8%A1%A8%E7%9A%84%E6%89%80%E6%9C%89%E8%A1%8C%E6%95%B0%E6%8D%AE%E8%BF%9B%E8%A1%8C%E6%AF%94%E8%BE%83%E6%9D%A5%E8%8E%B7%E5%8F%96%E7%BB%93%E6%9E%9C%EF%BC%8C%E5%BD%93%E6%89%A7%E8%A1%8Cselect%20%2A%20from%20user%20tb1,on%20tb1.id%3Dtb2.user_id%20Nested-Loop%20Join%20%E7%AE%80%E5%8D%95%E7%B2%97%E6%9A%B4%E5%AE%B9%E6%98%93%E7%90%86%E8%A7%A3%EF%BC%8C%E5%B0%B1%E6%98%AF%E9%80%9A%E8%BF%87%E5%8F%8C%E5%B1%82%E5%BE%AA%E7%8E%AF%E6%AF%94%E8%BE%83%E6%95%B0%E6%8D%AE%E6%9D%A5%E8%8E%B7%E5%BE%97%E7%BB%93%E6%9E%9C%EF%BC%8C%E4%BD%86%E6%98%AF%E8%BF%99%E7%A7%8D%E7%AE%97%E6%B3%95%E6%98%BE%E7%84%B6%E5%A4%AA%E8%BF%87%E4%BA%8E%E7%B2%97%E9%B2%81%EF%BC%8C%E5%A6%82%E6%9E%9C%E6%AF%8F%E4%B8%AA%E8%A1%A8%E6%9C%891%E4%B8%87%E6%9D%A1%E6%95%B0%E6%8D%AE%EF%BC%8C%E9%82%A3%E4%B9%88%E5%AF%B9%E6%95%B0%E6%8D%AE%E6%AF%94%E8%BE%83%E7%9A%84%E6%AC%A1%E6%95%B0%3D1%E4%B8%87%20%2A%201%E4%B8%87%20%3D1%E4%BA%BF%E6%AC%A1%EF%BC%8C%E5%BE%88%E6%98%BE%E7%84%B6%E8%BF%99%E7%A7%8D%E6%9F%A5%E8%AF%A2%E6%95%88%E7%8E%87%E4%BC%9A%E9%9D%9E%E5%B8%B8%E6%85%A2%E3%80%82

 

从本质上上来说，连接就是把各个表中的记录都取出来依次进行匹配，并把匹配后的组合发送给客户端，如果不加任何过滤条件，产生的结果就是笛卡儿集。
内连接和外连接的根本区别在于：

　　在驱动表中的记录不符合ON子句中的连接条件时，内连接不会把该记录加入到最后的结果集中，而外连接会（不存在的记录的列用NULL表示）。

　　

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

1、Simple Nested-Loop Join 嵌套循环连接

　　这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了。

　　驱动表扫一条行记录，然后匹配驱动表所有行记录，也就是说每扫秒一条驱动表记录，就要把被驱动表全扫描一遍，假如驱动表和被驱动表均有100条数据，就要扫描 100*100 次。

2、Index Nested-Loop Join 索引嵌套循环连接

　　驱动表扫描一行记录，然后如果非驱动表匹配的列有索引，就直接和索引比较。

　　索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。

　　这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

　　在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

3、Block Nested-Loop Join 基于块的嵌套循环连接，

　　先申请一块内存 叫JOIN BUFFER(变量 join_buffer_size控制它的大小 默认 256KB)，直接一次读取驱动表多条记录写入JOIN BUFFER，拿JOIN BUFFER 同时和多条被驱动表数据进行匹配，减少被驱动表的扫描次数，从而降低磁盘I/O代价。一般用不上 索引嵌套循环，就搞这手了。

　　在有索引的情况下，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当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。

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