MySQLJOIN原理

先看⼀下实验的两张表：表comments，总⾏数28856

表comments_for，总⾏数57，comments_id是有索引的，ID列为主键。

以上两张表是我们测试的基础，然后看⼀下索引，comments_for这个表comments_id是有索引的，ID为主键。

最近被公司某⼀开发问道JOIN了MySQL JOIN的问题，细数之下发下我对MySQL JOIN的理解并不是很深刻，所以也查看了很多⽂档，最后在InsideMySQL公众号看到了两篇关于JOIN的分析，感觉写的太好了，拿出来分享⼀下我对于JOIN的实际测试吧。下⾯先介绍⼀下MySQL关于JOIN的算法，总共分为三种（来源为InsideMySQL）：

MySQL是只⽀持⼀种JOIN算法Nested-Loop Join（嵌套循环链接），不像其他商业数据库可以⽀持哈希链接和合并连接，不过MySQL的Nested-Loop Join（嵌套循环链接）也是有很多变种，能够帮助MySQL更⾼效的执⾏JOIN操作：（1）Simple Nested-Loop Join（图⽚为InsideMySQL取来）

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

（2）Index Nested-Loop Join，实现⽅式如下图：

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

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

在索引嵌套链接的⽅式下，如果⾮驱动表的关联键是主键的话，这样来说性能就会⾮常的⾼，如果不是主键的话，关联起来如果返回的⾏数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据⼆级索引的主键ID进⾏回表的操作。这样来说的话性能相对就会很差。（3）Block Nested-Loop Join，实现如下：

在有索引的情况下，MySQL会尝试去使⽤Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的SimpleNested-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。上⾯介绍完了，下⾯看⼀下具体的列⼦（1）全表JOIN

EXPLAIN SELECT * FROM comments gc

JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

看⼀下输出信息：

可以看到在全表扫描的时候comments_for 作为了驱动表，此事因为关联字段是有索引的，所以对索引idx_commentsid进⾏了⼀个全索引扫描去匹配⾮驱动表comments ，每次能够匹配到⼀⾏。此时使⽤的就是Index Nested-Loop Join，通过索引进⾏了全表的匹配，我们可以看到因为comments_for 表的量级远⼩于comments ，所以说MySQL优先选择了⼩表comments_for 作为了驱动表。（2）全表JOIN+筛选条件

SELECT * FROM comments gc

JOIN comments_for gcf ON gc.comments_id=gcf.comments_idWHERE gc.comments_id =2056

此时使⽤的是Index Nested-Loop Join，先对驱动表comments 的主键进⾏筛选，符合⼀条，对⾮驱动表comments_for 的索引idx_commentsid进⾏seek匹配，最终匹配结果预计为影响⼀条，这样就是仅仅对⾮驱动表的idx_commentsid索引进⾏了⼀次访问操作，效率相对来说还是⾮常⾼的。（3）看⼀下关联字段是没有索引的情况：

EXPLAIN SELECT * FROM comments gc

JOIN comments_for gcf ON gc.order_id=gcf.product_id

我们看⼀下执⾏计划：

从执⾏计划我们就可以看出，这个表JOIN就是使⽤了Block Nested-Loop Join来进⾏表关联，先把comments_for （只有57⾏）这个⼩表作为驱动表，然后将comments_for 的需要的数据缓存到JOIN buffer当中，批量对comments 表进⾏扫描，也就是只进⾏⼀次匹配，前提是join buffer⾜够⼤能够存下comments_for的缓存数据。⽽且我们看到执⾏计划当中已经很明确的提⽰：Using where; Using join buffer (Block Nested Loop)⼀般情况出现这种情况就证明我们的SQL需要优化了。

要注意的是这种情况下，MySQL也会选择Simple Nested-Loop Join这种暴⼒的⽅法，我还没搞懂他这个优化器是怎么选择的，但是⼀般是使⽤Block Nested-Loop Join，因为CBO是基于开销的，Block Nested-Loop Join的性能相对于Simple Nested-Loop Join是要好很多的。（4）看⼀下left join

EXPLAIN SELECT * FROM comments gc

LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

看⼀下执⾏计划：

这种情况，由于我们的关联字段是有索引的，所以说Index Nested-Loop Join，只不过当没有筛选条件的时候会选择第⼀张表作为驱动表去进⾏JOIN，去关联⾮驱动表的索引进⾏Index Nested-Loop Join。

如果加上筛选条件gc.comments_id =2056的话，这样就会筛选出⼀条对⾮驱动表进⾏Index Nested-Loop Join，这样效率是很⾼的。如果是下⾯这种：

EXPLAIN SELECT * FROM comments_for gcf

LEFT JOIN comments gc ON gc.comments_id=gcf.comments_idWHERE gcf.comments_id =2056

通过gcf表进⾏筛选的话，就会默认选择gcf表作为驱动表，因为很明显他进⾏过了筛选，匹配的条件会很少，具体可以看下执⾏计划：

此，join基本上已经很

明了了，未完待续中，欢迎⼤家指出错误，我会认真改正。。。。