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

最新推荐文章于 2024-05-06 16:55:49 发布

转载最新推荐文章于 2024-05-06 16:55:49 发布 · 305 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/285392

文章标签：

#数据库

本文探讨了MySQL优化器如何将外连接转换为内连接的过程。通过具体案例分析，展示了当从表选择条件确保结果集中无NULL值时，外连接可被优化为内连接，从而提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

联动贴：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中加入条件，

点击(此处)折叠或打开

select * from tb1 left join tb2 on tb1.id=tb2.id where tb2.id>1;
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 的情况

点击(此处)折叠或打开

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上面

试验：

点击(此处)折叠或打开

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

trace信息

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

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