当前位置 博文首页 > JavaEdge全是干货的技术号:为何阿里不推荐MySQL使用join?
今天这篇文章,我就先跟你说说join语句到底是怎么执行的,然后再来回答这两个问题。
示例表:
select *
from t1 straight_join t2 on (t1.a = t2.a);
若直接使用join语句,MySQL优化器可能会选择表t1或t2作为驱动表,这会影响我们分析SQL语句的执行过程。为便于分析执行过程中的性能,改用straight_join让MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去join。所以,该语句里:
t1 是驱动表
t2是被驱动表
使用索引字段join的 explain结果
t2的字段a上有索引,join过程用了该索引,因此该语句执行流程:
这个过程是先遍历t1,然后根据从t1中取出的每行数据中的a值,去t2中查找满足条件的记录。形式上和我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,称之为“Index Nested-Loop Join”,NLJ。
该流程:
所以能不能使用join?
假设不使用join,那就只能用单表查询:
select * from t1
查出t1所有数据,这里有100行。
循环遍历这100行数据:
该查询过程,也扫描了200行,但共执行了101条语句,比join多了100次交互。而且客户端还要自己拼接SQL语句和结果。
这性能还不如直接join。
该示例中,驱动表t1走全表扫描,被驱动表t2走树搜索。
假设被驱动表行数M。每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一棵树的时间复杂度log2M
,所以在被驱动表上查一行的时间复杂度是 2*log2M
。
假设驱动表行数N,执行过程就要扫描驱动表N行,然后对每一行,到被驱动表上匹配一次。
因此整个执行过程,时间复杂度是 N + N*2*log2M
。N扩大1000倍,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。
可见,N严重影响扫描行数,应该让小表做驱动表。
这些结论的前提是“可以使用被驱动表的索引”。
若被驱动表用不上索引呢?
select * from t1 straight_join t2 on (t1.a=t2.b);
t2的b无索引,所以每次到t2去匹配时,就要做一次全表扫描。
但这样,该SQL就要扫描t2 100次,共扫描100*1000=10万行。若t1和t2都是10万行的表,就要扫描100亿行!
当然,MySQL也没有使用这个Simple Nested-Loop Join算法,而使用“Block Nested-Loop Join”算法,BNL。
被驱动表无可用索引时的算法流程:
BNL执行流程
TODO
不使用索引直接join的执行计划
t1、t2都做了次全表扫描,因此总扫描行数1100。由于join_buffer是以无序数组组织,因此对t2中的每一行,都要做100次判断,总共需要在内存中做的判断次数是:100*1000=10万次。
若使用SNL算法查询,扫描行数也是10万行。因此,时间复杂度一样的。但BNL算法的这10万次判断是内存操作,速度上会快很多,性能较好。
那么此时哪个表做驱动表呢?
假设小表的行数是N,大表的行数是M,则在该算法里:
M+N
M*N
所以调换M和N无差异,所以选择哪个做驱动表,执行耗时都一样。
把join_buffer_size改成1200,再执行:
select *
from t1 straight_join t2 on (t1.a = t2.b);
执行过程如下:
step4、5,表示清空join_buffer再复用。这也能看出该算法的确是分块join。
此时由于t1被分成两次放入join_buffer,导致t2会被扫描两次。虽然分成两次放入join_buffer,但判断等值条件的次数不变,依然是(88+12)*1000=10万次。
此时如何选择驱动表?
假设,驱动表数据行数N,需分K(K不是常数,N越大K就会越大,因此把K表示为λ*N,显然λ的取值范围是(0,1))段完成,被驱动表数据行数M。
所以,该算法执行过程:
显然,内存判断次数是不受选择影响。观察扫描行数,在M和N确定时,N越小,结果越小。
所以应该让小表当驱动表。
在N+λ*N*M
中,λ才是影响扫描行数的关键因素,越小越好。
N越大,分段数K越大。那么,N固定时,什么会影响K呢?( 即λ的大小)答案是join_buffer_size:
join_buffer_size越大,一次可放入行越多,分段数越少,被驱动表全表扫描次数越少
所以若你的join很慢,就把join_buffer_size加大。
综上:
若使用INL,当可以用被驱动表的索引,是没问题的。
若使用BNL,扫描行数就会过多。尤其是在大表上的join,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种join禁用。
所以判断要不要使用join,就是看explain结果里面,Extra字段里面有没有出现“Block Nested Loop”。
所以,该问题最终结论:永远使用小表做驱动表。
若加上 where t2.id<=50
:
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;
使用 b 是为了让被驱动表都用不上索引。
但若用第二个语句,join_buffer只需放入t2的前50行,显然更好。所以这里“t2的前50行”是那个相对小的表,即“小表”。
再看个例子:
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作为驱动表。该例中,“只需要一列参与join的t1”是相对的小表。
在决定哪个表做驱动表时,应该是两个表按各自条件过滤,过滤完后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,将其作为驱动表。
cs