join的实现原理与优化思路

join实现原理

 mysql只有一种join算法，就是大名鼎鼎的Nested Loop Join

Nested Loop Join实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则在通过之前连个表的join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

Join语句优化

1、近可能减少Join语句中Nested Loop的循环总次数；

优化的前题条件是通过JOIN条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），就不能简单的通过结果集的大小来判断需要JOIN语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积大小来得到如何驱动更优化。

2、优先优化Nested Loop的内层顺序循环；

 不仅仅是在数据库的Join中应该做的，实际上在我们优化程序语言的时候也有类似的优化原则。内层循环是循环执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源

3、保证Join语句中被驱动表上Join条件字段已经被索引；

  保证驱动表上的JOIN条件字段已经被索引的目的，只有让被驱动表的join条件字段被索引了，才能保证循环中每次查询都能消耗较少的资源。

4、无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer的设置。

挡在某些特殊的环境中，我们的join 必须是all ，Index，range或者是index_merge类型的时候，Join buffer就会派上用场了。在这种情况下，Join Buffer的大小将对整个join语句的消耗起到非常关键的作用。