MySQL 5.6中关联子查询与派生表的性能差异原因问询

我们使用MySQL 5.6，有一张约3万行的records表，需要查询满足特定条件且column3为最大值的记录。

关联子查询实现 #

SQL语句：

SELECT * FROM records r1 
WHERE column1='X' AND column2='Y' AND
column3 = (SELECT MAX(column3) FROM records r2 
           WHERE r2.column1=r1.column1 AND r2.column2=r1.column2 AND r2.column3 <= '<some_date>');

执行耗时：14.094秒

执行计划（EXPLAIN）：

派生表实现 #

SQL语句：

SELECT * FROM records r1 
WHERE column1='X' AND column2='Y' AND
column3 = (SELECT MAX(column3) FROM 
              (SELECT * FROM records 
               WHERE column1='X' AND column2='Y' AND column3 <= '<some_date>') r2);    

执行耗时：0.047秒

执行计划（EXPLAIN）：

两个查询返回结果一致，但性能差异极大，请问这是什么原因？是否与MySQL 5.6的特性有关？从执行计划中未发现明显线索，可能是理解不到位。

• 关联子查询的执行逻辑：
  第一个查询是关联子查询（DEPENDENT SUBQUERY），MySQL 5.6对这类子查询的处理方式是逐行执行：主查询每从r1中取出一条满足column1='X' AND column2='Y'的记录，就会把这条记录的column1和column2值代入子查询，去r2中全表扫描计算MAX(column3)。
  假设主查询筛选出N条记录（最坏情况是3万条），子查询就会执行N次，每次都扫描3万行，总数据扫描量达到9亿行级别，这是它耗时14秒的核心原因。

• 派生表的执行逻辑：
  第二个查询里的派生表是独立执行一次：MySQL会先执行最内层的筛选语句，生成一个临时的派生表（仅包含符合条件的记录），然后只需要在这个派生表上执行一次MAX(column3)得到固定值，最后主查询用这个固定值去匹配r1中的记录。
  整个过程仅需3次全表扫描，总扫描量仅9万行级别，因此耗时仅0.047秒。

• 执行计划的隐藏信息：
  执行计划中的rows列仅显示单次扫描的行数，未体现执行次数。关联子查询的DEPENDENT SUBQUERY会执行N次，而派生表的子查询只执行一次，这就是执行计划未直接显示但影响性能的关键差异。

• MySQL 5.6的特性限制：
  确实和MySQL 5.6的优化能力有关。后续版本（如MySQL 8.0）会对关联子查询做更多优化，比如自动将其重写成JOIN形式避免重复执行，但MySQL 5.6没有这个优化逻辑，只能按逐行执行的方式处理关联子查询，导致性能差距巨大。

内容的提问来源于stack exchange，提问作者Nik