mysql join中，on和where的区别

--------------------------------------------------------------正文--------------------------------------------------------------

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

所有操作都是基于 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的选择条件的区别：
on后面的是连接条件， 代表两个表建立关系所遵循的规则

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