在实际开发中,使用join语法的时候,一般都是为了多表联查
但是DBA往往不建议使用join,为什么不建议呢
还有join语句真的要用,应该怎么使用呢
为了量化分析,创建了两个表t1和t2
| 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; 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) |
上面有两个字段相同的表
往表2上插入了1000行数据,往表1上插入了100行数据
然后进行如下的语句分析
select * from t1 straight_join t2 on(t1.a=t2a)
由于直接使用join语法,可能出现MySQL选择t1或者t2作为驱动表,影响我们分析SQL语句的执行过程,
这样我们采用straight_join来让MySQL固定连接方式,这样优化器可以按照指定方式来join,这就是t1是驱动表,t2是被驱动表
这一语句中,被驱动的表t2驱动表t1的字段a上有索引,join过程也是用了这个索引
那么这个语句的执行如下,首先是从表1中取出一行数据R
然后从数据R中,取出字段a到t2表中去寻找
从t2表中取出所有满足条件的行,跟R组成一行,作为结果集的一部分
重复执行着步骤1-3,直到t1的表结束
换算为Java代码,其实就类似我们书写的嵌套循环
整个流程中
对于驱动表t1,走了一次全表扫描,扫描了100行
然后对于每一行R,都会根据a字段去表2查找,走了树搜索,于是也是在b上搜索了100行
整体来说,走了200行
如果不适用join语句
那么只能考虑使用selelct * from t1,查出表t1的所有数据,总共有100行
循环遍历这100行数据,取出每一行的a值,然后编写 select * from t2 where a=$R.a
将结果和R组成结果集的一行
这样虽然还是只扫描200行,但是多执行了一次select * from t1
而且,需要手动拼接sql语句
显然不如将这些工作交给sql去做
那么,既然join比较好,如何选择驱动表呢?
驱动表走的是全表扫描,被驱动的走的是树搜索
假设被驱动的表的行是M,那么被驱动的查询一行数据,需要先走索引a,然后根据索引a上的主键,在主键上搜索
搜索一个树的复杂度是以2为底的M的对数,即为log2M,所以在被驱动表上差一行的时间复杂度是2log2M
那么驱动表的行数是N,那么执行过程中就会先扫描所有行
然后将驱动表上每一行,都到被驱动表上匹配一次
整体来说,复杂度为N+N*2*log2M
那么,这样看来,使用小表来做驱动表会比较合适
但是,上面都是建立在了SQL语句可以使用索引的情况下,
如果改为了不能使用索引的情况
那么如下的语句
select * from t1 straight_join t2 on (t1.a=t2.b)
那么因为t2上没有索引,那么每次去t2上匹配的时候,需要进行一次全表扫描,就不再是log2M了
这一行的搜索,需要扫描t2表高达100多次,总共扫描100*1000=10万行
这种语法称为 Simple Nested-Loop join算法
MySQL再次基础上进行了改进 Block Nested-Loop join
引入了线程内存join_buffer,对于join语句,将t1的数据放入了内存join_buffer中,由于使用的select *,就整体放进去了
然后扫描表t2,把表t2的每一行取出来,跟join_buffer中的数据做对比,如果满足join语句,就作为结果集返回
那么explain的结果为
虽然整体来看还是扫描了100*1000行,但是由于在内存中进行的计算,所以说速度会很快的
如同上面的BNL
现在来说,选择哪个表做驱动表
假设小表的行数是N,大表的行数是M
在这个语法中,两个表都做一个全表扫描,总的扫描行数是M+N
内存中的判断次数是M*N
总体来说,两个表对调没有任何差别,选择大表小表是一样的
看完了这个问题,试想一下如果join_buffer放不下呢
join_buffer_size决定了join_buffer的大小,默认是256K,如果放不下的,就分段放
如果这个值足够小,那么流程就变为了
1.扫描表t1,顺序读出数据行放入join_buffer,放过程中如果满了
2.就先扫描t2,t2中每一行都取出来,然后跟join_buffer中的做对比,满足条件的,作为结果集返回
3.清空join_buffer
4.扫描表t1,顺序读取剩余的t2的数据放入join_buffer中
上图的步骤4->5,就说明了清空join_buffer后反复使用
这就是Block的由来,表示分块去join
可以看出来,由于表t1被分为了两次放入join_buffer,导致表t2被扫描了两次,但是总体次数不变
(88+12)*1000=10万次
那么,对于block Nested Loop,如何选择驱动表呢
这是可以看出表t1被分为了两次放入了join_buffer中,表t2会扫描两次
那么假设驱动表的数据行数是N,需要分为K段才能完成,被驱动表行数是M,这里面K不是常数,N越大K越大,把K表示为λ*N,显然λ的取值范围是(0,1)
这个过程中,扫描了 N+(λ*N)*M次,内存判断了N*M次
说以还是小表作为驱动表吧
但是不建议无索引的join
那么最后说明一下,能不能使用join语句
1.能不能使用join语句
如果能用上Index Nested-Loop Join算法,也就是能用上索引,可以
如果不能用上索引,使用Block Nested-Loop Join算法,尽量不要使用
2.如果非要使用
建议使用小表作为驱动表,而不是大表
因为如果出现join_buffer_size不够大的时候,小表会更加合适
这个小表需要这么理解
因为只需要放入t2的前50行,所以建议t2作为驱动表
虽然只需要查询t2中100行
但是t1要查询的只有b字段,如果将t1作为驱动表放进去,只需要放一个b字段
t2需要查所有,将t2表放进去,需要放进去所有字段,建议使用t1作为驱动表