从原理上来讲Join在逻辑运算上比Left Join会少但有些业务情况下为什么反而会慢?
如下Sql语句:
select sonACD.ID
from
MN_ACD_DETAIL as sonACD
left join
MN_ACD_DETAIL as inbound
on sonACD.ANI=inbound.ANI
left join
call_detail_record as cdr
on sonACD.UCID=cdr.ucid
where
sonACD.ANSWER_TIME!=0
group by
sonACD.ID
select sonACD.ID
from
MN_ACD_DETAIL as sonACD
inner join
MN_ACD_DETAIL as inbound
on sonACD.ANI=inbound.ANI
inner join
call_detail_record as cdr
on sonACD.UCID=cdr.ucid
where
sonACD.ANSWER_TIME!=0
group by
sonACD.ID
在数据库跑时发现前者明显比后者快
查询执行计划发现,left join时只打描MN_ACD_DETAIL一次,而inner join时MN_ACD_DETAIL扫描两次,call_detail_record 一次!而原因也就在这。
通过查询开销可以发现前者需要合并联接两次而后者在合并联接上打开销为0%,后者所有的开销都用在聚集索引扫描上,前者先查出了sonACD 与inbound 联接的结果再与call_detail_record 联接返回,
而left join必定有数据库对其优化,以sonACD 为主同时索引inbound 与cdr 查到符合条件立即返回。
这就是Left Join 有时比Join快的原因