ITPub博客

首页 > 数据库 > MySQL > MySQL之SQL分析三部曲实际案例(四)--left join on的思考

MySQL之SQL分析三部曲实际案例(四)--left join on的思考

MySQL 作者:IT推动者 时间:2015-08-26 14:59:50 0 删除 编辑
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

问题出现于生产环境,为了方便表述,在测试环境构造了类似的场景

所有操作都是基于MySQL-5.7.7-rc下进行的(PS:5.7.8也出来了,看完改动信息,感觉release依然遥遥无期....._(:з」∠)_)

背景:有用户在抱怨生产系统上,某一个Web的页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了5s(慢查询日志给出的信息)

出问题的SQL语句

点击(此处)折叠或打开

  1. SELECT count(*) FROM tom
  2. INNER JOIN toa ON tom.order_id = toa.order_id
  3. LEFT OUTER JOIN tov ON tom.order_id = tov.order_id
  4. LEFT OUTER JOIN fo ON tom.sale_type = 2 AND fo.order_id = tom.order_id
  5. WHERE tom.pay_time >= '2015-07-20 11:15:47.068' AND tom.pay_time <= '2015-08-20 11:15:47.068' AND tom.shop_id IN ( 1 , 2 , 3 , 4 , 5 , 6 )
这段语句的执行效率一看就知道不高(...(*/ω\*)...废话,关联了一大堆表结果最终结果集的选择条件里面只有tom表的)

单纯的为了优化的话,把那些无用的表连接去掉就好,优化效果非常明显,这种类似的查询的时间都会降低到0.5-0.8秒左右

不过这里有一点是以前见得比较少,那就是在left join的连接条件后面,有一个对驱动表tom的选择条件在里面(红色加粗部分),且结果集里面,sale_type各种值都存在~

于是打算仔细研究一下选择条件挂在on和where的区别,以及在left join和inner join情况下的异同。

先上一些前期的分析:
inner join和left join :一个是内连接,一个是左外连接,一图说明问题(打字好累,还不一定能区分的很清楚_(:з」∠)_,PS:看看就好,有些语法不一定MySQL能用....)



on和where的选择条件的区别:

on后面的是连接条件,代表两个表建立关系所遵循的规则

where后面的可以看作是筛选条件,是对最终结果集进行过滤所遵循的规则

-------------------------------------------------------------------------------------------------思考---------------------------------------------------------------------------------------------------------------

到这里为止,根据之前的理解:on的条件里面标注了驱动表的sale_type的条件,这意味着满足了sale_type=2的条件的列才会去进行连接
所以中间集应该只有sale_type=2的结果,因此结果集中的sale_type都应该是2
PS:这里的理解出现了偏差

那么回到残酷的现实,为什么
结果集里面,sale_type各种值都存在~?

-------------------------------------------------------------------------------------------------求证---------------------------------------------------------------------------------------------------------------
测试环境构造:MySQL-5.7.7.-rc

建表语句

点击(此处)折叠或打开

  1. create table tb1 (id int(2) primary key, col1 int(2), col2 varchar(10));
  2. create table tb2 (id int(2) primary key, col1 int(2), col2 varchar(10));

  3. insert into tb1 values(1,11,'tb1_1');
  4. insert into tb1 values(2,12,null);
  5. insert into tb1 values(3,null,'tb1_3');
  6. insert into tb1 values(4,14,'tb1_4');
  7. insert into tb1 values(5,15,null);
  8. insert into tb1 values(7,null,null);

  9. insert into tb2 values(1,11,'tb2_1');
  10. insert into tb2 values(2,null,'tb2_1');
  11. insert into tb2 values(3,13,null);
  12. insert into tb2 values(4,14,'tb2_4');
  13. insert into tb2 values(6,16,'tb2_6');
  14. insert into tb2 values(7,null,null);
结果如图


构造如下示例语句

点击(此处)折叠或打开

  1. select tb1.id, tb1.col1 from tb1 left join tb2 on tb1.id=tb2.id and tb1.col1=11;
按照最初的想法,最终结果集应该只有tb1.col1=11的情况,那么看看结果


反而是inner join才是想象中的结果


那么问题应该就是出在left join和inner join的区别上了,
仔细回想一下两者的区别:left join会以驱动表做为标准来进行连接,如果从表不满足条件的匹配项,则用null填充,
换句话说,结果集中,驱动表全部行都会在结果集中体现,但是从表不满足条件的匹配项,则全部以null填充;

以这种理解来看,示例语句中,使用了left join的方式,则无论后面的on中对驱动表做出任何选择操作,如果where中不对最终结果集进行选择,那么tb1的所有列都会原封不动的在结果集中出现~

要验证这个结论,看看完整的结果集就知道了~上图~


如结论所描述,tb1中的所有列都出现在了结果集,且tb2中,不满足tb1.col1=11的其他列,都用null进行了填充

求证完结,撒花~(*/ω\*)~

总结:半吊子的理解害人,基本功一定要扎实~



-------------------------------------------------------------------------------------------------扩展---------------------------------------------------------------------------------------------------------------
知识点要不断的扩展,成为面,才是王道~

explain看一下示例的执行计划

PS:题外话,手动添加的unique index貌似只会在等值选择的条件下才会被用起来,如图中的eq_ref,而range类好像是用不上unique index,具体以后再研究(有生之年系列+1....._(:з」∠)_....坑越来越多了)

看一下trace的内容,优化器并没有改写这个语句,只是单纯的格式化了一下


那么如果这个tb1.col1=11换成tb2.col1=11会是什么效果?
根据之前的结论,得到的结果应该是:满足tb2.col1=11的tb2的列,会在结果集中用null填充


那么如果这个tb1.col1=11从on的连接条件换到where里面?
根据之前的结论,得到的结果应该是:连接完成以后的中间集包含tb1中的所有列,以及对应的tb2中的列,数据保持原始的样子,在最终结果集中会根据tb1.col1=11的条件,过滤掉不符合的行最终只剩下tb1.col1=11的数据

可能看描述没感觉,不过从结果集来看,有木有觉得很像是这个语句的结果呢~

点击(此处)折叠或打开

  1. select tb1.*,tb2.* from tb1 inner join tb2 on tb1.id=tb2.id and tb1.col1=11;
当然,下面这个语句也是同样的结果集

点击(此处)折叠或打开

  1. select tb1.*,tb2.* from tb1 inner join tb2 on tb1.id=tb2.id where tb1.col1=11;
当然,最终结果集一样,并不代表之前这几个语句的语义是一样的~

可以试着按照这种顺序去分析这几个语句:根据on条件得出中间结果集-->根据where条件进行过滤/选择-->得到最终结果集

-------------------------------------------------------------------------------------------------吐槽---------------------------------------------------------------------------------------------------------------

PS:坑坑坑,我的眼前一片坑......._(:з」∠)_......

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30435363/viewspace-1783169/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-08-26

  • 博文量
    5
  • 访问量
    2382