提到复杂查询,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是否已经产生了任何行,这样内部条件 = 外部条件就不能下推到子查询中。
所以这也是为什么人家子查询不慢,你的慢的一个因素,不要认为查询写的一样,结果就一样,各种前期不注意的地方,就能坑你一下。
当然也可以看看群里面的一个PDF ,或许能收获更多。加群的方式在下边