MySQL5.7性能优化系列(二)——SQL语句优化(3)——使用物化策略优化子查询

优化器使用物化策略(Materialization)来实现更有效的子查询处理。通过生成子查询结果作为临时表,通常在内存中,实现加速查询执行。 MySQL首次需要子查询结果,将该结果实现为临时表。任何随后的结果都需要,MySQL再次指向临时表。优化器可以使用散列索引对表进行索引,以使查找更加快速和便宜。该索引是唯一的,它消除了重复,并使表格更小。

子查询实现可能时使用内存中临时表,如果表变得太大,则返回到磁盘存储。

如果不使用物化策略,则优化器有时将非相关子查询重写为相关子查询。例如,以下IN子查询是不相关的(where_condition仅涉及从t2而不是t1的列):

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

优化器可能会将其重写为EXISTS相关的子查询:

SELECT * FROM t1
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的列,以便子查询不相关。

此查询可能会实现:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

以下限制适用于使用子查询物化:

  • 内部和外部表达式的类型必须匹配。例如,如果两个表达式都是整数,或两者都是十进制,则优化器可能能够使用实现,但如果一个表达式是整数,而另一个是十进制,则不能使用实现。
  • 内部表达式不能是BLOB。

使用EXPLAIN查询可以提供优化器是否使用子查询物化的指示。与不使用实现的查询执行相比,select_type可能会从“依赖子查询”更改为“子查询”。这表明,对于将在每个外部行执行一次的子查询,实现使子查询只能执行一次。此外,对于扩展EXPLAIN输出,由以下SHOW WARNINGS显示的文本包括物化和物化子查询。

原文链接:https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值