join执行原理

示例表

create table 't2' (
    'id' int(11) not null,
    'a' int(11) default null,
    'b' int(11) default null,
    primary key ('id'),-- 主键索引
    key 'a' ('a')-- 普通索引
) engine=innodb;

-- 插入t2表1000行数据
drop procedure idata;
delimiter ;;
create procedure idata()
begin 
    declare i int;
    set i=1;
    while(i<=1000) do
        insert into t2 values(i,i,i);
        set i=i+1;
    end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)
-- 插入t1表100行数据

Index Nested-Loop Join

select * from t1 straight_join t2 on(t1.a=t2.a);

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响分析SQL语句的执行过程。使用straight_join让MySQL使用固定的连接方式执行查询，优化器只会按照指定的方式去join。上述语句，t1是驱动表，t2是被驱动表。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	simple	t1	null	all	a	null	null	null	100	100.00	Using where
1	simple	t2	null	ref	a	a	5	test.t1.a	1	100.00	null

被驱动表t2的字段a上有索引，join过程用上了这个索引。

1. 从表t1中读入一行数据R；
2. 从数据行R中，取出a字段到表t2里去查找；
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4. 重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1，然后根据表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程跟写程序时的嵌套查询类似，并且可以用上被驱动表的索引，称之为”Index Nested-Loop Join”，简称NLJ。

1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；
2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于插入的数据都是一一对应的，因此每次的扫描过程都只扫描一行，总共扫描100行。
3. 整个执行流程，总扫描行数是200。

不使用join

假设不使用join，只能用单表查询。

1. 执行select * from t1，查出表t1的所有数据，这里有100行。
2. 循环遍历这100行数据：
   • 从每一行R取出字段a的值$R.a；
   • 执行select * from t2 where a=$R.a；
   • 把返回的结果和R构成结果集的一行。

这个查询过程，扫描了200行，但是总共执行了101条语句，比直接join多了100次交互。除此之外，客户端还要自己拼接SQL语句和结果。

驱动表选择

示例join语句执行过程中，驱动表是走全表扫描，被驱动表是走树搜索。

假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log₂M，在被驱动表上查一行的时间复杂度是2*log₂M。

假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。整个执行过程，近似复杂度是N+N*2*log₂M。显然N对扫描函数的影响更大，因此应该让小表来做驱动表。

N扩大1000倍，扫描行数就扩大1000倍；M扩大1000倍，扫描行数扩大不到10倍。

---

• 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
• 如果使用join语句的话，需要让小表做驱动表。前提是可以使用被驱动表的索引。

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上没有索引，每次到t2去匹配的时候，就要做一次全表扫描。这个SQL请求需要扫描表t2多达100次，总共扫描100*1000=10万行。

Block Nested-Loop Join

被驱动表上没有可用的索引，算法流程：

1. 把表t1的数据读入线程内存join_buffer中，由于语句中写的是select *，因此是把整个表t1放入了内存；
2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	simple	t1	null	all	a	null	null	null	100	100.00	null
1	simple	t2	null	all	null	null	null	null	1000	10.00	Using where;Using join buffer(Block Nested Loop)

在这个过程中，对t1和t2都做了一次全表扫描，总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是:100*1000=10万次。

如果使用Simple Nested-Loop Join算法进行查询，扫描行数也是10万行。从时间复杂度上说，两个算法是一样的。但是Block Nested-Loop Join算法的10万次判断是内存操作，速度上会快很多，性能也更好。

假设小表的行数是N，大表的行数是M：

1. 两个表都做一次全表扫描，所以总的扫描行数是M+N；
2. 内存中的判断次数是M*N。

调换这两个算式中的M和N没差别，此时选择大表还是小表做驱动表，执行耗时是一样的。

join_buffer

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据，就分段放。(join_buffer_size设置为1200)

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

算法中的“Block”表示分块去join。由于表t1被分成了两次放入join_buffer中，导致表t2会被扫描两次。虽然分成两次放入join_buffer，但是判断等值条件的次数是不变的，依然是(88+12)*1000=10万次。

驱动表选择

假设驱动表的数据行数是N，需要分K段(N越大K就会越大，表示为λ*N，λ取值范围0-1)才能完成算法流程，被驱动表的数据行数是M。

1. 扫描函数是N+λNM；
2. 内存判断N*M次。

内存判断次数是不受选择哪个表作为驱动表影响的。考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。应该让小表做驱动表。

在N+λ*N*M这个式子中，λ是影响扫描行数的关键因素，这个值越小越好。

N越大，分段数K越大。N固定的时候，λ(join_buffer_size)影响K的大小。join_buffer_size越大，一次可以放入的行越多，分成的段数也越小，对被驱动表的全表扫描次数就越少。所以，如果join语句很慢，就把join_buffer_size改大。

总结

使用join语句

1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，是没问题的；
2. 如果使用Block Nested-Loop Join算法，扫描的行数就会过多。尤其是在大表上的join操作，可能要扫描被驱动表很多次，会占用大量的系统资源。这种join尽量不要用。

在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现”Block Nested Loop”字样。

选择驱动表

1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
2. 如果是Block Nested-Loop Join算法：
   • 在join_buffer_size足够大的时候，是一样的；
   • 在join_buffer_size不够大的时候(需要对驱动表分块，对被驱动表做多次全表扫描)，应该选择小表做驱动表。

总体结论，应该使用小表做驱动表。

“小表”解析

示例1：

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

第二个语句，join_buffer只需要放入t2的前50行，是相对较小的表。

示例2：

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;

两个语句中，表t1和t2都是只有100行参加join，但是每次查询放入join_buffer中的数据是不一样的：

• 表t1只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；
• 表t2需要查所有的字段，因此如果把表t2放到join_buffer中，就需要放入三个字段id、a和b。

只需要一列参与join的表t1是相对较小的表。

两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表。