MySQL中Cross Join与Left Join查询耗时相近的原因咨询

你之所以会觉得Left Join应该更快，是误解了MySQL执行查询的实际流程——MySQL并不会完全按照你写的SQL字面顺序执行，而是会通过优化器对查询做等价重写，最终生成相同的执行计划，这就是两个查询耗时相近的核心原因。

一、两个查询的逻辑本质是等价的 #

先看你的两个查询：

查询1（Left Join写法）

select s.*, a.score as score_01, b.score as score_02
from student s
left join (select * from sc where cid = '01') a using (sid)
left join (select * from sc where cid = '02') b using (sid)
where a.score > b.score;

这里的Left Join看似是外连接，但WHERE a.score > b.score这个条件会直接过滤掉a或b为NULL的行（因为NULL无法参与大小比较），相当于把Left Join转换成了Inner Join——只有同时存在cid='01'和cid='02'成绩的学生才会被保留。

查询2（Cross Join写法）

select s.*, a.score as score_01, b.score as score_02
from student s
,(select * from sc where cid = '01') a
,(select * from sc where cid = '02') b
where a.score > b.score and a.sid = b.sid and s.sid = a.sid;

这里用逗号分隔表的写法在MySQL里是Cross Join，但WHERE子句里的a.sid = b.sid和s.sid = a.sid是连接条件，优化器会把这种写法转换成Inner Join，和查询1的逻辑完全一致。

二、MySQL优化器的等价重写机制 #

MySQL的基于成本的优化器（CBO）会分析查询的语义，自动把不同写法的等价查询转换成最优的执行计划：

• 对于查询2，优化器不会真的先生成s、a、b三个表的笛卡尔积（这会产生极大的中间表），而是会先利用cid='01'和cid='02'过滤sc表得到小结果集，再通过sid做连接，最后过滤a.score > b.score的条件。
• 对于查询1，优化器识别到WHERE条件抵消了Left Join的外连接特性，会把它重写成Inner Join，执行流程和查询2完全一致。

最终两个查询会生成几乎完全相同的执行计划，所以耗时自然没有明显差异。

验证方法 #

你可以用EXPLAIN命令查看两个查询的执行计划，比如：

EXPLAIN
select s.*, a.score as score_01, b.score as score_02
from student s
left join (select * from sc where cid = '01') a using (sid)
left join (select * from sc where cid = '02') b using (sid)
where a.score > b.score;

EXPLAIN
select s.*, a.score as score_01, b.score as score_02
from student s
,(select * from sc where cid = '01') a
,(select * from sc where cid = '02') b
where a.score > b.score and a.sid = b.sid and s.sid = a.sid;

对比输出的type、key、rows等字段，会发现两者的执行计划基本一致。

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