MySQL性能优化--JOIN原理和使用优化

基础

内连接 inner join on

左连接（左外连接） left join on / left outer join on
注意到左表的顺序变化了，因为左连接是左边满足条件的先放入结果集中，不满足条件的再放入结果集中。

右连接（右外连接） right join on / right outer join on

简单嵌套循环算法（Simple Nested Loop Join）

MySQL实现join的时候，采取了一种称为嵌套循环算法的机制。指定循环外层是驱动表，循坏内层是被驱动表，驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录，然后从头扫描被驱动表，逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。被驱动表查找完后，再从驱动表中取出第二个记录，然后从头扫描被驱动表，逐一查找与驱动表第二条记录匹配的记录，连接起来形成结果表中的一条记录。重复上述操作，直到驱动表的全部记录都处理完毕为止。这就是简单嵌套循环连接算法的基本思想。
如果驱动表有N行数据，被驱动表有M行数据，则这两张表的join操作需要N*M次循环。

分块嵌套循环算法（Block Nested Loop Join）

简单嵌套循环算法的效率非常低，因此MySQL做的优化是，如果被驱动表上没有可用的索引，则把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把表中的每一行取出来，和join_buffer中的数据作对比，符合条件的数据作为结果集的一部分返回，这一算法称为Block嵌套循环算法。虽然在时间复杂度上和简单嵌套循环算法一样，但是这N*M次判断是在内存上完成的。

如果join_buffer的大小不够放下驱动表的字段怎么办？

join_buffer的大小是由参数join_buffer_size设定，默认值为256k，如果join_buffer的大小不足够存放驱动表t1的所有字段，则采用分段存放：

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

这样的话，内存判断NM次，扫描的行数是N+kM次（其中，k为分段数，k=i*N，i取0~1），因此，N越小，扫描的行数值越小，因此需要小表驱动大表。

索引嵌套循环算法（Index Nested Loop Join）

在简单嵌套循环连接算法的基本思想上，如果被驱动表上on后面的字段上建立了索引，则在内层循环上操作的时候会先在索引数上查找符合条件的数据，如果select后面需要查询被驱动表的其它字段，则还需要回表取数据，搜索一棵B+Tree的近似时间复杂度是以2为底M的对数，因此驱动表有N行数据，被驱动表有M行数据，则这两张表的join操作需要N+N2（以2为底M的对数）次循环。N越小，这个值就越小，因此需要小表驱动大表。

小结

驱动表有N行数据，被驱动表有M行数据

1. 被驱动表有可用索引的情况下，小表驱动大表，大表上有索引，能加快执行效率。时间复杂度为：N+N2（以2为底M的对数）

2. 被驱动表无可用索引的情况下：
   2.1 如果join_buffer 足够大，能放下驱动表的所有行，则大表和小表谁驱动谁是不会影响执行效率。内存判断NM次，扫描的行数是N+M次
   2.2 如果join_buffer 不够大，则需要block，小表驱动大表能加快执行效率。内存判断NM次，扫描的行数是N+kM次（其中，k为分段数，k=iN，i取0~1）。

3. 在被驱动表建立索引能够提高连接效率。

4. 排序：假设t1表驱动t2表进行连接操作，连接条件是t1.id=t2.id，而且要求查询结果对id排序。现在有两种选择，方式一[…ORDER BY t1.id]，方式二[…ORDER BY t2.id]。如果我们使用方式一的话，可以先对t1进行排序然后执行表连接算法，如果我们使用方式二的话，只能在执行表连接算法后，对结果集进行排序（Using temporary），效率自然低下。由此最后可得出，优先选择驱动表的属性进行排序能够提高连接效率。

SQL执行顺序

select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>

实践

MySQL性能优化–explain详解
test1表98288条数据，test2表14326条数据

（1）小表驱动大表提高连接效率（大表有可用索引的前提下）

使用等值连接（连接条件中的运算符为“=”），mysql优化区会自动判断出“小表”作为驱动表。使用left join，左边的表被认为是驱动表，right join认为右边的表是驱动表。

使用explain关键字查看执行计划，从type字段可以看出，驱动表需要遍历全表，被驱动表使用唯一性索引扫描，所以被驱动表为大表，使用索引，效率肯定会高点。

（2）在被驱动表建立索引能够提高连接效率

上面已经演示过，被驱动表的索引是有效的，驱动表的索引没有用到。

（3）优先选择驱动表的属性进行排序能够提高连接效率

优先选择驱动表test2的属性进行排序能够提高连接效率，可以先对test2进行排序然后执行表连接算法，如果我们使用test1的属性，只能在执行表连接算法后，对结果集进行排序（Using temporary），效率自然低下。


使用explain关键字查看执行计划，分析Extra字段，Using temporary表示使用了临时表保存中间结果

什么是“小表”

其实，这里的小表是一个相对的概念，并不是完整指表的原有行数

如下面这个sql，t1和t2的b列都没有索引，t1表的行数大于t2表的行数，使用t1当驱动表，会把t1的所有行放入join_buffer中，但是如果用t2当驱动表，只需要放入t2表的id小于等于50的行即可，因此这里，t2应该当做小表。

select * from t1 left join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 left join t1 on (t1.b=t2.b) where t2.id<=50;

再如下面这个sql，t1和t2的b列都没有索引，t1表只有100行，t2表数据超过100行，使用t1当驱动表，t1的b列会放入join_buffer中，但是如果用t2当驱动表，t2的所有列字段都会放入join_buffer中，因此这里，t1应该当做小表。

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;