问题出现于 生产环境,为了方便表述,在测试环境构造了类似的场景
所有操作都是基于 MySQL-5.7.7-rc 下进行的(PS:5.7.8也出来了,看完改动信息,感觉release依然遥遥无期....._(:з」∠)_)
背景:有用户在抱怨生产系统上,某一个Web的页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了5s(慢查询日志给出的信息)
出问题的SQL语句
SELECT count(*) FROM tom
INNER JOIN toa ON tom.order_id = toa.order_id
LEFT OUTER JOIN tov ON tom.order_id = tov.order_id
LEFT OUTER JOIN fo ON tom.sale_type = 2 AND fo.order_id = tom.order_id
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后面的可以看作是筛选条件,是对最终结果集进行过滤所遵循的规则
测试环境构造:MySQL-5.7.7.-rc
结果如图
explain看一下示例的执行计划
PS:题外话,手动添加的unique index貌似只会在等值选择的条件下才会被用起来
看一下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的数据
可能看描述没感觉,不过从结果集来看,有木有觉得很像是这个语句的结果呢~
当然,最终结果集一样,并不代表之前这几个语句的语义是一样的~
可以试着按照这种顺序去分析这几个语句:根据on条件得出中间结果集-->根据where条件进行过滤/选择-->得到最终结果集。
--------------------------------------------------------------结论--------------------------------------------------------------
on后面的是连接条件, 代表两个表建立关系所遵循的规则
where后面的可以看作是筛选条件,是对最终结果集进行过滤所遵循的规则