• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL查询优化:子查询物化

MySQL SQL 彭东稳 6年前 (2018-07-02) 16390次浏览 已收录 0个评论

一、背景

MySQL引入了Materialization(物化)这一关键特性用于子查询(比如在IN/NOT IN子查询以及 FROM 子查询)优化。 具体实现方式是:在SQL执行过程中,第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ,后续对子查询结果集的访问将直接通过临时表获得。与此同时,优化器还具有延迟物化子查询的能力,先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。与之相对的执行方式是对外表的每一行都对子查询进行调用,其执行计划中的查询类型为“DEPENDENT SUBQUERY”。

在使用Materialization(物化)能提高SQL性能的同时,也有必要留意相关SQL是否存在进一步优化空间的可能性。比如下面描述的场景:

从执行计划可看出,MySQL首先物化了子查询(select_type=DERIVED,或者以format=json格式查看执行计划),然后再通过class_id字段对结果集进行过滤。这个SQL从语义上,也可以写成如下形式,若索引合理执行效率会更高。

从这个例子可以看出子查询物化时的一个潜在问题:当子查询本身比较耗费资源或结果集较大时,往往存在较高的优化空间,特别是在外层条件可作用于子查询的情况下。通过条件下推,在执行过程中尽早减少数据访问量,能显著提高性能。本文重点描述将条件下推到物化子查询的场景。

二、分析

事实上前面提到的查询在MySQL 5.7版本可以自动重写。打开优化器选项 derived_merge=on 后,查看重写后的语句如下:

另一方面,并不是所有子查询都可以做到自动条件下推。比如下面这个语句:

出现这种现象的原因是MySQL优化器目前只能对Mergable的视图或子查询进行重写。理解这一概念可以先从视图的两种算法入手:merge 和 temptable。

一般较为复杂的视图或子查询会使用temptable算法类型,包括:

  1. 聚合子查询;
  2. 含有LIMIT的子查询;
  3. UNION 或UNION ALL子查询;
  4. 输出字段中的子查询;

我们也可以显示的通过创建视图来判断子查询是否使用了merge算法。 比如:

我们创建视图时指定使用merge,但是数据库判定该算法不适合因此使用默认的undefined(实际执行过程中使用temptable算法)。

使用merge算法的视图或子查询能够将查询条件下推到视图或子查询内部;而temptable算法子查询或视图不能将条件下推,只能在结果集上做进一步过滤。优化器对这一判断标准为:

三、条件下推原则

不是所有数据库引擎都完美实现条件下推到子查询的功能。对MySQL中使用聚合查询的视图或者from子查询,建议的条件下推原则是:

查询中只依赖于视图或者from子查询输出字段的where条件能够安全的下推。

同时需要注意条件下推到视图或derived table子查询后所存放的恰当位置:

  1. 从语义上看,下推到聚合子查询的条件可以放在 HAVING 子句里。下推后的HAVING字句可以是: HAVING xxx and NEW_CONDITION operation VALUE;
  2. 若条件是子查询的 group 字段,且该条件上有索引,那么将该条件放在子查询的where字句中,性能会更好(HAVING条件中不含聚合函数时,将该条件下推到where字句中过滤整个group)。

对于其他类型的视图或from子查询,也可以通过语义检查的方式进行人工条件下推。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!