MySQL优化之外连接消除----空值拒绝

联动贴：http://blog.itpub.net/29510932/viewspace-1777673/

既然研究到了外连接和内连接，那就顺便整理一下MySQL优化器的外连接消除

想法和理解部分参考《数据库查询优化器的艺术》page 389-396
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

MySQL优化器会对SQL进行优化（废话....._(:з」∠)_...）

在这种优化中,就包括将外连接转换为内连接，所依据的条件为：内表（从表）符合空值拒绝

先上个人的总结和理解：在where的cond中，存在对内表的选择条件，在经过cond的选择后，在最终结果集里面，内表参与表连接的列col不存在null的情况（不管最后是否将col的结果输出）

-------------------------------------------------------------------------------------------------求证---------------------------------------------------------------------------------------------------------------

分析方式与联动帖的分析方式一致：
根据on条件得出中间结果集-->根据where条件进行过滤/选择-->得到最终结果集

测试环境构造：与联动帖一致，MySQL-5.7.7-rc

外连接结果如图，这个结果即为"根据on条件得出中间结果集"


可以看到，外连接中，从表的表连接列，tb2.id出现了null，因为tb2中并没有id=5的情况

那么试试在where中加入条件，

点击(此处)折叠或打开

1. select * from tb1 left join tb2 on tb1.id=tb2.id where tb2.id>1;
   
2. select * from tb1 inner join tb2 on tb1.id=tb2.id where tb2.id>1;

从结果上看，两者一致

按照之前对空值拒绝的理解， 第一个语句的where条件中有从表tb2的选择条件，且依据这个选择条件筛选得到的最终结果集中，不存在tb2.id为null的数据行，
所以第一个语句应该是被优化器转换成了内连接；
查看第一个语句的trace，部分结果如下


可以看到外连接已经转换成了内连接

当然，这个cond-->tb2.id可以换成其他的列，比如说tb2.col1>0



如比较复杂的cond，就需要特别注意 or 的情况

点击(此处)折叠或打开

1. select tb1.id, tb2.id from tb1 left join tb2 on tb1.id=tb2.id where tb1.col1>0 or tb2.col1>0

当tb1.col1>0的条件为true，tb2.col1>0为false时，无法满足空值拒绝了，所以最终这个语句只能 采用left join进行连接

PS：如书中描述空值拒绝的条件可以出现在where和on上面

试验：

点击(此处)折叠或打开

1. select tb1.id, tb2.id from tb1 left join tb2 on tb1.id=tb2.id and tb2.col1>0;

trace信息

并没有将外连接转换为内连接，应该是条件写的并不完整， 所以在前面的理解和总结中，并没有加上on，只是单独写了where

-------------------------------------------------------------------------------------------------完结---------------------------------------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/29510932/viewspace-1778693/