MYSQL同样逻辑的四种SQL写法分析

这篇文章将为大家详细讲解有关MYSQL同样逻辑的四种SQL写法分析，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

提到复杂查询，MYSQL 头疼的旅程就开始了，当然优化的方法和其他的数据监控也不大同，MYSQL的语句优化属于发散性思维，只要你能用上的方法都可以，可不限制于数据库本身的语句优化。所以MYSQL的优化好像是一个讲不完的故事。

下面举一个列子看看同时达到同样结果的不同的语句的写法，产生的性能结果有什么不同

现在有两个表一个department 表 一个 员工与部门之间的关联表  dept_emp

现在由于部门裁撤，要统计哪些部门现在还有员工，将有员工的部门显示出来。

当然不提表的结构和行数的性能比较都是属于耍流氓

下面是两种写法

select em.dept_name

from (select distinct dept_no from dept_emp) as de

inner join departments as em on em.dept_no = de.dept_no;

select distinct em.dept_name 

from dept_emp as de 

inner join departments as em on em.dept_no = de.dept_no;

从上图的分析来看

select em.dept_name

from (select distinct dept_no from dept_emp) as de

inner join departments as em on em.dept_no = de.dept_no;

的写法要优于

select distinct em.dept_name 

from dept_emp as de 

inner join departments as em on em.dept_no = de.dept_no;

在有相关的索引的加持下，在查询中先将重复的数据进行去重后，在进行关联的方法要明显比，先关联在去重的方法要好。

那到此就完结了，有么有其他的写法，下面就是另一种写法

select em.dept_name

from departments as em 

inner join (

select de.dept_no_d from (select distinct dept_no as dept_no_d from dept_emp) as de  where de.dept_no_d in (select dept_no from departments)) as tm on em.dept_no = tm.dept_no_d  ;

同样能达到同样的结果，看上去复杂的写法，其实也并不慢

那我们是否还有其他的写法，或者让刚才的方式的查询变得更快

select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no);

最后我们将所有的四种写法，执行一遍，通过profile 对比一下四种方法的快慢和消耗

从上面的分析看，最次的是使用in来进行查询，而最好的是用exists 的方式来进行查询， 使用  JOIN 的方法属于中规中矩。

但在分析这四种查询的方法，以及产生的不同效果中，可以看到

select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no);

与

select distinct em.dept_name 

    -> from dept_emp as de 

    -> inner join departments as em on em.dept_no = de.dept_no;

两种方法在选择的索引以及执行计划都有类似的地方，为什么使用exists的子查询在这里要快于使用join的方式

可以看到虽然语句的执行计划相同，但不同的是慢的那个使用了Using temporary, 也就是二次处理了搜寻上来的结果，进行了一个去重的工作，而快的exists 则没有这个操作。

那问题就来了，不是说子查询慢吗，子查询是如何进行查询的，但实际上为什么在这个例子不慢。

MySQL子查询是从外部到内部评估查询。也就是说，它首先获取外层表达式的值，然后运行子查询并捕获它生成的行。对于子查询有用的优化是“通知”子查询，只有内部表达式的条件等于外部表达式的那些行才可以进行优化，将一个适当的等式下推到子查询的WHERE子句中来实现的。

写法如下

EXISTS (SELECT 1 FROM ... WHERE  外部条件=内部条件)

我们例子中的写法快的那个恰恰和这个写法相同，在转换之后，MySQL可以使用下推等式来限制它必须检查的行数来计算子查询，记得之前写过一篇关于 ICP 的文字，这里就不说 下推的问题了。

说到这里要实现ICP 还要有一个条件就是，不能有NULL 值，也就是空值， 所以这也是 DBA 费尽心机的 和 开发人员沟通，说你的这个字段尽量不要有NULL最好有 DEFAULT  默认值的一个原因，因为你不知道何时因为你的字段里面初期设计的有NULL 值，就造成费尽心机的优化半途而废。

如果有NULL 值结果就是

EXISTS (SELECT 1 FROM ... WHERE  外部条件=内部条件 or 内部条件 is NUll)

当然这也没有什么，MYSQL 遇到NULL 不走索引的，我也曾经写过一篇，辟谣了。

问题是 or 这个操作 您的另外进行一个表操作的问题，另外还有无法在ICP 下推了，主要的原因是NULL 在数据库里面并不是FALSE 而是未知的状态，ICP 下推必须要进行适当的计算，必须能够检查SELECT是否已经产生了任何行，这样内部条件 = 外部条件就不能下推到子查询中。

所以这也是为什么人家子查询不慢，你的慢的一个因素，不要认为查询写的一样，结果就一样，各种前期不注意的地方，就能坑你一下。

关于MYSQL同样逻辑的四种SQL写法分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。