MySQL之Join语句执行流程是什么_Mysql一条sql语句的执行流程?

本文小编为大家详细介绍"MySQL之Join语句执行流程是什么",内容详细,步骤清晰,细节处理妥当,希望这篇"MySQL之Join语句执行流程是什么"文章能让粉丝们变的更强解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧.

Join语句执行流程

在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类：

不让使用 join,使用 join 有什么问题呢?

如果有两个大小不同的表做 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;

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)

可以看到,这两个表都有一个主键索引 id 和一个索引 a,字段 b 上无索引.

第一段：Index Nested-Loop Join

所以,为了便于分析执行过程中的性能问题,改用 straight_join 让 MySQL 使用固定的连接方式执行查询,这样优化器只会按照指定的方式去 join.

来看一下这个语句：

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

现在,来看一下这条语句的 explain 结果.

在形式上,这个过程就跟写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以称之为"Index Nested-Loop Join",简称 NLJ.它对应的流程图如下所示：

在这个流程里：

能不能使用 join?

假设不使用 join,那就只能用单表查询.

看看上面这条语句的需求,用单表查询怎么实现.

执行select * from t1,查出表 t1 的所有数据,这里有 100 行；

循环遍历这 100 行数据：

从每一行 R 取出字段 a 的值 $R.a；

把返回的结果和 R 构成结果集的一行.

显然,这么做还不如直接 join 好.

怎么选择驱动表?

在这个 join 语句执行过程中,==驱动表是走全表扫描,而被驱动表是走树搜索.==假设被驱动表的行数是 M.

每次在被驱动表查一行数据,要先搜索索引 a,再搜索主键索引.

假设驱动表的行数是 N,执行过程就要扫描驱动表 N 行,然后对于每一行,到被驱动表上匹配一次.

显然,N 对扫描行数的影响更大,所以呢应该让小表来做驱动表.

如果没觉得这个影响有那么"显然", 可以这么理解：N 扩大 1000 倍的话,扫描行数就会扩大 1000 倍；而 M 扩大 1000 倍,扫描行数扩大不到 10 倍.

小结一下,通过上面的分析得到了两个结论：

使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好；

如果使用 join 语句的话,需要让小表做驱动表.

但是,需要注意,这个结论的前提是"可以使用被驱动表的索引".

第二段：Simple Nested-Loop Join

再看看被驱动表用不上索引的情况.

现在,把 SQL 语句改成这样：

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

如果只看结果的话,这个算法是正确的,而且这个算法也有一个名字,叫做"Simple Nested-Loop Join".

第三段：Block Nested-Loop Join

当然,MySQL 也没有使用这个 Simple Nested-Loop Join 算法,而是使用了另一个叫作"Block Nested-Loop Join"的算法,简称 BNL.

这时候,被驱动表上没有可用的索引,算法的流程是这样的：

把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,所以呢是把整个表 t1 放入了内存；

这个过程的流程图如下：

对应地,这条 SQL 语句的 explain 结果如下所示：

前面我们说过,如果使用 Simple Nested-Loop Join 算法进行查询,扫描行数也是 10 万行.所以呢,从时间复杂度上来说,这两个算法是一样的.但是,Block Nested-Loop Join 算法的这 10 万次判断是内存操作,速度上会快很多,性能也更好.

在这种情况下,应该选择哪个表做驱动表.

假设小表的行数是 N,大表的行数是 M,那么在这个算法里：

两个表都做一次全表扫描,所以总的扫描行数是 M◆N；

内存中的判断次数是 M*N.

可以看到,调换这两个算式中的 M 和 N 没差别,所以呢这时候选择大表还是小表做驱动表,执行耗时是一样的.

这个例子里表 t1 才 100 行,要是表 t1 是一个大表,join_buffer 放不下怎么办呢?

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

执行过程就变成了：

清空 join_buffer；

执行流程图也就变成这样：

这个流程才体现出了这个算法名字中"Block"的由来,表示"分块去 join".

在这种情况下驱动表的选择问题.

假设,驱动表的数据行数是 N,需要分 K 段才能完成算法流程,被驱动表的数据行数是 M.

注意,这里的 K 不是常数,N 越大 K 就会越大,所以呢把 K 表示为lambda;*N,显然lambda;的取值范围是 (0,1).

所以,在这个算法的执行过程中：

扫描行数是 N◆lambda;NM；

内存判断 N*M 次.

显然,内存判断次数是不受选择哪个表作为驱动表影响的.

而考虑到扫描行数,在 M 和 N 大小确定的情况下,N 小一些,整个算式的结果会更小.所以结论是,应该让小表当驱动表.

在 N◆lambda;NM 这个式子里,lambda;才是影响扫描行数的关键因素,这个值越小越好.

刚刚我们说了 N 越大,分段数 K 越大.那么,N 固定的时候,什么参数会影响 K 的大小呢?（也就是lambda;的大小）答案是 join_buffer_size.

join_buffer_size 越大,一次可以放入的行越多,分成的段数也就越少,对被驱动表的全表扫描次数就越少.

如果你的 join 语句很慢,就把 join_buffer_size 改大.

第一个问题：能不能使用 join 语句?

如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的；

如果使用 Block Nested-Loop Join 算法,扫描行数就会过多.尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源.所以这种 join 尽量不要用.

所以在判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现"Block Nested Loop"字样.

第二个问题是：如果要使用 join,应该选择大表做驱动表还是选择小表做驱动表?

如果是 Index Nested-Loop Join 算法,应该选择小表做驱动表；

如果是 Block Nested-Loop Join 算法：

在 join_buffer_size 足够大的时候,是一样的；

在 join_buffer_size 不够大的时候（这种情况更常见）,应该选择小表做驱动表.

所以,这个问题的结论就是,总是应该使用小表做驱动表.

当然了,这里我需要说明下,什么叫作"小表".

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 字段都使用了没有索引的字段 b.

再来看另外一组例子：

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;

但是,这两条语句每次查询放入 join_buffer 中的数据是不一样的：

表 t1 只查字段 b,所以呢如果把 t1 放到 join_buffer 中,则 join_buffer 中只需要放入 b 的值；

应该选择表 t1 作为驱动表.也就是说在这个例子里,"只需要一列参与 join 的表 t1"是那个相对小的表.

所以,更准确地说,在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是"小表",应该作为驱动表.

读到这里,这篇"MySQL之Join语句执行流程是什么"文章已经介绍完毕,想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会,如果想了解更多相关内容的文章,欢迎关注***行业资讯频道.

以上就是土嘎嘎小编为大家整理的MySQL之Join语句执行流程是什么相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！