引言
MySQL在执行Join语句时会根据当前语句和表结构选择不同类型的Join算法,下面将依次介绍这些算法,为了便于说明,建立t1,t2两张表,t1表插入了1000行数据,每一行的a=1001-id的值。也就是说,表t1中字段a是逆序的。同时,在表t2中插入了100万行数据,语句如下
create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, 1001-i, i);
set i=i+1;
end while;
set i=1;
while(i<=1000000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
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是被驱动表。
现在,我们来看一下这条语句的explain结果。
可以看到,在这条语句里,被驱动表t2的字段a上有索引,join过程用上了这个索引,因此这个语句的执行流程是这样的:
-
从表t1中读入一行数据 R;
-
从数据行R中,取出a字段到表t2里去查找;
-
取出表t2中满足条件的行,跟R组成一行,作为结果集的一部分;
-
重复执行步骤1到3,直到表t1的末尾循环结束。
这个过程是先遍历表t1,然后根据从表t1中取出的每行数据中的a值,去表t2中查找满足条件的记录。在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称NLJ。
它对应的流程图如下所示:
在这个流程里:
-
对驱动表t1做了全表扫描,这个过程需要扫描100行;
-
而对于每一行R,根据a字段去表t2查找,走的是树搜索过程。由于我们构造的数据都是一一对应的,因此每次的搜索过程都只扫描一行,也是总共扫描100行;
-
所以,整个执行流程,总扫描行数是200。
在这个join语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。
假设被驱动表的行数是M。每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数,记为log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。
假设驱动表的行数是N,执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。
因此整个执行过程,近似复杂度是 N + N*2*log2M。
显然,N对扫描行数的影响更大,因此在该算法中使用小表来做驱动表可以获得更好的查询性能。
Simple Nested-Loop Join
现在,我们把SQL语句改成这样:
select * from t1 straight_join t2 on (t1.a=t2.b);
由于表t2的字段b上没有索引,因此再用上图的执行流程时,每次到t2去匹配的时候,就要做一次全表扫描。
你可以先设想一下这个问题,继续使用图2的算法,是不是可以得到正确的结果呢?如果只看结果的话,这个算法是正确的,而且这个算法也有一个名字,叫做“Simple Nested-Loop Join”。
但是,这样算来,这个SQL请求就要扫描表t2多达100次,总共扫描100*1000=10万行。
这还只是两个小表,如果t1和t2都是10万行的表(当然了,这也还是属于小表的范围),