MySQL5.7性能优化系列(二)——SQL语句优化(3)——使用物化策略优化子查询
优化器使用物化策略(Materialization)来实现更有效的子查询处理。通过生成子查询结果作为临时表,通常在内存中,实现加速查询执行。 MySQL首次需要子查询结果,将该结果实现为临时表。任何随后的结果都需要,MySQL再次指向临时表。优化器可以使用散列索引对表进行索引,以使查找更加快速和便宜。该索引是唯一的,它消除了重复,并使表格更小。
子查询实现可能时使用内存中临时表,如果表变得太大,则返回到磁盘存储。
如果不使用物化策略,则优化器有时将非相关子查询重写为相关子查询。例如,以下IN子查询是不相关的(where_condition仅涉及从t2而不是t1的列):
1.SELECT * FROM t1
2.WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
优化器可能会将其重写为EXISTS相关的子查询:
1.SELECT * FROM t1
2.WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
使用临时表的子查询实现避免了这种重写,并且使得可以仅执行一次而不是每行外部查询一次执行子查询。
对于要在MySQL中使用的物化的子查询,必须启用optimizer_switch系统变量materialization标志。启用实现标志后,实现应用于出现在任何地方的子查询谓词(在选择列表中,WHERE,ON,GROUP BY,HAVING或ORDER BY),适用于任何这些用例的谓词:
- 谓词具有此形式,当外部表达式oe_i或内部表达式ie_i为空时。 N为1以上。
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
- 谓词具有这种形式,当存在单个外部表达式oe和inner expression ie时。表达式可以为空。
oe [NOT] IN (SELECT ie ...)
- 谓词为IN或NOT IN,UNKNOWN(NULL)的结果与FALSE的含义相同。
以下示例说明了对UNKNOWN和FALSE谓词评估的等价性的要求如何影响是否可以使用子查询实现。假设where_condition仅包含从t2而不是t1的列,以便子查询不相关。
此查询可能会实现:
1.SELECT * FROM t1
2.WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
以下限制适用于使用子查询物化:
- 内部和外部表达式的类型必须匹配。例如,如果两个表达式都是整数,或两者都是十进制,则优化器可能能够使用实现,但如果一个表达式是整数,而另一个是十进制,则不能使用实现。
- 内部表达式不能是BLOB。
使用EXPLAIN查询可以提供优化器是否使用子查询物化的指示。与不使用实现的查询执行相比,select_type可能会从“依赖子查询”更改为“子查询”。这表明,对于将在每个外部行执行一次的子查询,实现使子查询只能执行一次。此外,对于扩展EXPLAIN输出,由以下SHOW WARNINGS显示的文本包括物化和物化子查询。