MySQL关联子查询逐行比较执行逻辑原理相关疑问

你此前对子查询执行顺序的认知仅适用于非相关子查询，官方文档示例中的子查询属于相关子查询，二者执行逻辑存在本质区别：

1. 非相关子查询的执行逻辑 #

• 定义：子查询内部没有引用外层查询的任何字段，和外层查询完全独立
• 执行顺序：确实是优先一次性执行完子查询，拿到固定结果后再代入外层查询执行，整个过程子查询仅执行1次
• 示例：

-- 子查询不依赖外层任何变量，可单独执行
SELECT * FROM t1 WHERE id = (SELECT max(id) FROM t2);

2. 相关子查询的执行逻辑 #

• 定义：子查询的过滤条件中引用了外层查询的字段值，子查询的执行依赖外层的当前行数据，无法单独执行
• 你给出的示例中，子查询的WHERE t1.id = t.id里的t是外层查询t1的别名，t.id只有在外层遍历到具体某一行时才会有确定的取值，因此无法预先执行子查询得到固定结果
• 该SQL的具体执行步骤如下：
  • 第一步：遍历外层查询的t1表，逐行取出每一行作为当前行，记为行t，获取当前行的id具体取值（比如当前行id值为5）
  • 第二步：将当前行的id值代入子查询，执行SELECT COUNT(*) FROM t1 WHERE t1.id = 5，得到该id值在全表的总出现次数
  • 第三步：将子查询返回的计数值和2做比较，若相等则保留当前行t到结果集，否则丢弃
  • 第四步：重复上述三步，直到外层t1表所有行遍历完成

3. 补充说明 #

你提到的“该场景无法用JOIN实现”其实是误解，该逻辑完全可以用聚合+关联的方式实现，等效SQL如下，执行效率通常比逐行执行的相关子查询更高：

SELECT t.* FROM t1 AS t
INNER JOIN (
  SELECT id, COUNT(*) AS id_cnt FROM t1 GROUP BY id
) AS t1_agg ON t.id = t1_agg.id
WHERE t1_agg.id_cnt = 2;

MySQL的查询优化器在多数场景下会自动将你给出的相关子查询重写为上述JOIN形式执行，避免逐行调用子查询的性能损耗。

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