mysql调优-Join多种连接方式

简单嵌套循环连接

r为驱动表，s为匹配表，可以看到从r中分别取出每一个记录去匹配s表的列，然
后再合并数据，对s表进行r表的行数次访问，对数据库的开销比较大

索引嵌套循环连接

这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。
在查询时，驱动表（r)会根据关联字段的索引进行查找，当在索引上找到符合的值，再回表进行查询，也就是只有当
匹配到索引以后才会进行回表查询。
如果非驱动表（s）的关联键是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根
据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

缓存块嵌套循环连接

如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop
Join。
可以看到中间有个join buffer缓冲区，是将驱动表®的所有join相关的列都先缓存到join buffer
中，然后批量与匹配表(s)进行匹配，将第一种多次比较合并为一次，降低了非驱动表(s)的访问频
率。
默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，
包括select的列，而不是仅仅只缓存关联列。
在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

通过下面这条SQL查询默认的 join_buffer 大小。

show variables like '%join_buffer%';

连接方式的小总结

当使用内连接的时候，两种方式一样
当使用左外连接的时候，会把左表的数据全部查出
当使用右外连接的时候，会把右表中的数据全部查出
最好是小表Join大表，减少循环查找次数

STRAIGHT JOIN 只适用于内连接，因为left join、right join已经知道了哪个表作为驱动表，哪个表作为被驱动表，比如left join就是以左表为驱动表，right join反之，而STRAIGHT_JOIN就是在内连接中使用，而强制使用左表来当驱动表，所以这个特性可以用于一些调优，强制改变mysql的优化器选择的执行计划。

大表join大表，第一种就是分区来进行执行，第二种就是拆分成多条语句执行，然后将数据合并到一起，第三种利用where条件过滤掉次要条件。
在排序中，也是利用分而治之，先拆分成多个小表，也就是分区表，保证多个小表按照某种顺序排列，然后合并到一起。

参考：
https://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html