背  景

MySQL引入了Materialization(物化)这一关键特性用于子查询(比如在IN/NOTIN子查询以及 FROM子查询)优化，其关键点在于对子查询只需要执行一次。具体实现方式为：

  • 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表。
  • 后续对子查询结果集的访问将直接通过临时表获得。

与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENTSUBQUERY”。虽然物化子查询有利于提高SQL的执行效率，但如果使用不当，会引起意想不到的后果，比如数据库宕机，今天所讲的案例就是这样一个场景。

案例分析

一MySQL-5.7.17测试环境，业务发出一条查询sql语句后，直接引起数据库的宕机，查看err日志，发现sql语句如下：

mysql 子查询多列1展示 mysql子查询 多次使用_mysql子查询

问题sql为包括两个子查询的多表关联select语句。

分析执行计划

mysql 子查询多列1展示 mysql子查询 多次使用_子查询_02

执行计划相对简单，值得注意的是select_type为“SUBQUERY”，它表明该sql可能使用了物化子查询功能，为了得到确切的信息，查看warning信息：

mysql 子查询多列1展示 mysql子查询 多次使用_sql语句包含_03

当包含“materialize”和”materialized-subquery“时，已表明该sql语句使用了物化子查询。为了看清物化子查询是如何使用的，格式化上述信息如下：

mysql 子查询多列1展示 mysql子查询 多次使用_子查询_04

该sql语句被MySQL进行了重写，并且两个子查询都使用了物化子查询进行了优化，分配两个临时表用于存放子查询的结果，并且为每个临时表创建hashindex，用于关联其它表时，提高效率。

查看optimizer_switch参数

mysql 子查询多列1展示 mysql子查询 多次使用_mysql子查询_05

“materialization=on”表明启用物化子查询功能，”derived_merge=on”表明改写sql，将子查询合并至外部语句。难道是物化子查询这个功能引起的吗？

关闭物化子查询功能

mysql 子查询多列1展示 mysql子查询 多次使用_mysql子查询_06

重新发起上述sql语句

mysql 子查询多列1展示 mysql子查询 多次使用_mysql子查询_07

执行计划表明，该sql语句没再使用物化子查询功能

mysql 子查询多列1展示 mysql子查询 多次使用_子查询_08

该sql语句执行成功，而且数据库也并没有宕机，看起来真的是物化子查询这个功能的原因。

查看文档

在查看5.7.23的变更文档中发现如下这个bug

mysql 子查询多列1展示 mysql子查询 多次使用_sql语句包含_09

上述bug说明，物化子查询可能会导致mysql服务宕机，5.7.23及以上版本修复了该问题，运行这样的sql语句将会报错，而不会再导致mysql宕机。

测试

为了验证这个bug，将上述sql语句运行于5.7.23版本中

mysql 子查询多列1展示 mysql子查询 多次使用_mysql子查询_10

5.7.23版本中确实产生了错误，并且要求关闭物化子查询功能。

总结

启用物化子查询功能，可以提升包含子查询sql的执行效率，但也会触发一些潜在问题，如上述的导致MySQL宕机bug，虽通过升级MySQL至5.7.23或最新版本，可以避免该问题；或闭关物化子查询功能，使sql回退至原始的”DEPENDENT SUBQUERY“执行方式，但相应的sql执行效率也会下降很多，特别是外层结果特别巨大时。所以在生产环境，避免子查询的使用，才是解决该问题的王道。