MySQL性能优化-条件下推到物化表

【背景】

在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。 与此同时，优化器还具有延迟物化子查询的能力，先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。 与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENT SUBQUERY”。    

在使用Materialization（物化）能提高SQL性能的同时，也有必要留意相关SQL是否存在进一步优化空间的可能性。

比如下面描述的场景：

mysql>explain extended Select * from (select * from score where score >= 60) derived1 where class_id  = 10;+----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+| id | select_type | table      | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra                    |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+| 1  | PRIMARY     |            | ref   |               |             | 4       | const | 0    |      100 |                          || 2  | DERIVED     | score      | index | idx_score     | idx_score   | 4       |       | 1    |      100 | Using where; Using index |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+

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

SELECT * FROM score WHERE score >= 60 AND class_id = 10

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

【分析】

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

SELECT`remall`.`score`.`class_id` AS `class_id`,`remall`.`score`.`student_id` AS `student_id`,`remall`.`score`.`score` AS `score` FROM`remall`.`score` WHERE(    (`remall`.`score`.`class_id` = 10 ) AND ( `remall`.`score`.`score` >= 60 ));

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

SELECT*FROM (SELECT class_id,avg(score) FROM score GROUP BY class_id) derived1 WHERE class_id=10;

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

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

• 聚合子查询；

• 含有LIMIT的子查询；

• UNION 或UNION ALL子查询；

• 输出字段中的子查询； 

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

mysql>create algorithm=merge view v as select class_id, avg(score) from score group by class_id;执行成功,花费 2.46 ms.mysql>show warnings;+---------+------+-------------------------------------------------------------------------------+| Level   | Code | Message                                                                       |+---------+------+-------------------------------------------------------------------------------+| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |+---------+------+-------------------------------------------------------------------------------+

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

/**  Strategy for how to process a view or derived table (merge or materialization)*/enum enum_view_algorithm {  VIEW_ALGORITHM_UNDEFINED = 0,  VIEW_ALGORITHM_TEMPTABLE = 1,  VIEW_ALGORITHM_MERGE     = 2};

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

{...  // Check whether derived table is mergeable, and directives allow merging  if (!derived_unit->is_mergeable() ||      derived_table->algorithm == VIEW_ALGORITHM_TEMPTABLE ||      (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_DERIVED_MERGE) &&       derived_table->algorithm != VIEW_ALGORITHM_MERGE))    DBUG_RETURN(false);...}

【条件下推原则】    

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

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

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

1.从语义上看，下推到聚合子查询的条件可以放在 HAVING 子句里。下推后的 HAVING字句可以是： HAVING xxx and NEW_CONDITION operation VALUE;        

2.若条件是子查询的group 字段，且该条件上有索引，那么将该条件放在子查询的where字句中，性能会更好（HAVING条件中不含聚合函数时，将该条件下推到where字句中过滤整个group）。    对于其他类型的视图或from子查询，也可以通过语义检查的方式进行人工条件下推。

【结语】    

任何数据库的优化器都不是万能的。 了解优化器的特性后并规避其短处，才能写出最优SQL语句。