JOIN与EXISTS的表现

Question

一般来说，使用JOIN选择行与EXISTS
where子句之间在性能上有区别吗？通过搜索各种问答网站，可以发现联接效率更高，但是我记得很久以前就知道，EXISTS在Teradata中更好。

例如，考虑以下两个查询，它们返回相同的结果：

select   svc.ltv_scr, count(*) as freq
from     MY_BASE_TABLE svc
join     MY_TARGET_TABLE x
on       x.srv_accs_id=svc.srv_accs_id
group by 1
order by 1

-和-

select   svc.ltv_scr, count(*) as freq
from     MY_BASE_TABLE svc
where exists(
    select 1
    from   MY_TARGET_TABLE x
    where  x.srv_accs_id=svc.srv_accs_id)
group by 1
order by 1

两个表上的主索引（唯一）是“ srv_accs_id”。MY_BASE_TABLE很大（2亿行），而MY_TARGET_TABLE很小（200,000行）。

EXPLAIN计划中有一个显着差异：第一个表示 “通过RowHash匹配扫描 将两个表连接 在一起” ，第二个表示
“通过全行扫描”连接在一起 。两者都说这是 “所有AMP的加入步骤” ，总的估计时间是相同的（0.32秒）。

这两个查询执行相同的操作（我正在使用Teradata 13.10）。

一项类似的查找不匹配项的实验，将LEFT OUTER JOIN与相应的IS NULL where子句与NOT
EXISTS子查询进行比较，确实显示出性能差异：

select   svc.ltv_scr, count(*) as freq
from     MY_BASE_TABLE svc
left outer join MY_TARGET_TABLE x
on       x.srv_accs_id=svc.srv_accs_id
where    x.srv_accs_id is null
group by 1
order by 1

-和-

select   svc.ltv_scr, count(*) as freq
from     MY_BASE_TABLE svc
where not exists(
    select 1
    from   MY_TARGET_TABLE x
    where  x.srv_accs_id=svc.srv_accs_id)
group by 1
order by 1

第二个查询计划更快（如EXPLAIN所述，为2.21秒对2.14秒）。

我的例子可能太琐碎，看不到区别。我只是在寻找编码指南。

Answer 1

NOT ISISTS比使用LEFT OUTER JOIN使用IS NULL条件排除参与表中缺少的记录的效率更高，因为优化程序将选择使用带有NOT
EXISTS谓词的EXCLUSION MERGE JOIN。

虽然您的第二项测试未对数据集产生令人印象深刻的结果，但随着数据量的增加，使用不存在连接而不是通过左联接获得的性能提升非常明显。请记住，表将需要像参加LEFT
JOIN一样，由参加NOT EXISTS联接的列进行散列分布。因此，数据偏斜会影响EXCLUSION MERGE JOIN的性能。

编辑：

通常，我会将EXISTS替换为IN，而不是将其用于重新编写联接解决方案。当参与逻辑比较的列可以为NULL时，尤其如此。这并不是说您不能使用EXISTS代替INNER
JOIN。代替EXCLUSION JOIN，您将最终获得INCLUSION JOIN。本质上，INNER
JOIN是一个包含联接。我敢肯定有些细微之处我会忽略，但是如果您希望花些时间阅读它们，可以在手册中找到它们。