SQLServerIN 与 EXISTS 性能的比较

我很好奇下面哪一个会更有效率？

对于使用 IN，我一直比较谨慎，因为我相信 SQLServer 会将结果集转换成一个大的 IF语句。对于较大的结果集，这可能导致性能较差。对于较小的结果集，我不确定哪一个是更好的。对于大的结果集，EXISTS不是更有效吗？

WHERE EXISTS (SELECT * FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

对。

WHERE bx.BoxID IN (SELECT BoxID FROM Base WHERE [Rank = 2])
156990 次浏览
小开

我不能保证是正确的: 我相信在这种情况下第二个会更快。

  1. 在第一种情况下，相关的子查询可能会导致为每一行运行子查询。
  2. 在第二个示例中，子查询应该只运行一次，因为不相关。
  3. 在第二个示例中，IN一找到匹配项就会短路。
小开

我会选择 EXISTS，参见下面的链接:

SQLServer: JOIN vs IN vs EXISTS-逻辑差异

在返回结果方面，有一个常见的误解，即 IN 与 EXISTS 或 JOIN 的行为相同。这根本不是真的。

IN: 如果指定的值与子查询或列表中的任何值匹配，则返回 true。

存在: 如果子查询包含任何行，返回 true。

加入: 加入加入列上的2个结果集。

博客来源: https://stackoverflow.com/users/31345/mladen-prajdic

小开
最佳答案

EXISTS将更快，因为一旦发动机找到了一个命中，它将退出寻找，因为条件已被证明是真实的。

使用 IN，它将在进一步处理之前收集子查询的所有结果。

小开

在这些情况下，执行计划通常是相同的，但是直到您看到优化器如何在索引等的所有其他方面考虑因素之前，您真的永远不会知道。

小开

我已经在 SQLServer2005和2008上做了一些测试，并且在 EXISTS 和 IN 上都返回了完全相同的实际执行计划，正如其他人所说的那样。优化器是最优的。:)

但是有些事情需要注意，EXISTS、 IN 和 JOIN 有时可能会返回不同的结果，如果您的查询措辞不恰当的话: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

小开

要优化 EXISTS，必须非常文字化; 有些内容必须存在，但实际上不需要从相关子查询返回任何数据。你只是在评估一个布尔条件。

所以:

WHERE EXISTS (SELECT TOP 1 1 FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

因为相关的子查询是 RBAR，所以第一次命中结果将使条件为真，并且不会进一步处理该条件。

小开

人们普遍接受的答案是短视的，而且这个问题有点松散:

1)没有明确提到覆盖指数是否存在于 左边，右边，或者两边。

2)两者都没有考虑到输入左边集的大小和 输入右侧设置输入右侧设置。
(这个问题只是提到了一个整体较大的 结果集)。

我相信优化器足够聪明，当(1)和(2)有显著的成本差异时，它可以在“ in”和“ being”之间进行转换，否则它可能只是作为一个提示(例如，存在鼓励使用右边的可查找索引)。

这两个表单都可以转换为内部联接表单，将联接顺序颠倒，并以循环、散列或合并的方式运行——基于左边、右边或两边的估计行数(左边和右边)和索引存在。

小开

因此，IN 不同于 EXISTS，也不会产生相同的执行计划。

通常 EXISTS 用于相关子查询，这意味着您将把 EXISTS 内部查询与外部查询连接起来。这将添加更多步骤来生成结果，因为您需要解决外部查询联接和内部查询联接，然后匹配它们的 where 子句来连接两者。

通常使用 IN 时不需要将内部查询与外部查询关联起来，而且只需要一个步骤(在最佳情况下)就可以解决这个问题。

想想这个:

  1. 如果使用 IN 并且内部查询结果是数百万行不同的值，那么它的执行速度可能会比 EXISTS 慢，因为 EXISTS 查询是高性能的(具有与外部查询连接的正确索引)。

  2. 如果使用 EXISTS，并且外部查询的连接比较复杂(执行时间较长，没有合适的索引) ，那么外部表中的行数会减慢查询的速度，有时候估计完成的时间可能是几天。如果给定的硬件可以接受行数，或者数据基数正确(例如，大型数据集中的 DISTINCT 值较少) ，IN 可以比 EXISTS 执行得更快。

  3. 当每个表上有相当数量的行时，上述所有内容都会被记录下来(公平地说，我指的是超过 CPU 处理和/或内存缓存阈值的内容)。

所以答案是，这取决于。您可以在 IN 或 EXISTS 中编写一个复杂的查询，但是根据经验，当您有大量具有大量不同值的行时，应该尝试使用带有有限的不同值集和 EXISTS 的 IN。

技巧是限制要扫描的行数。

问候,

MarianoC

小开

这里有很多误导性的答案，包括那个高度赞成的(尽管我不认为他们的行动意味着伤害)。简短的回答是: 这些都是一样的。

在(T -) SQL 语言中有许多关键字，但最终，在硬件上真正发生的唯一事情是执行查询计划中看到的操作。

我们在调用 [NOT] IN和 [NOT] EXISTS时所做的关系(数学理论)运算是半连接(使用 NOT时是反连接)。相应的 sql-server 操作具有 同一个名字并非巧合。没有任何操作提到 IN或 EXISTS任何地方-只(反)半连接。因此，逻辑上等效的 ABC3与 EXISTS的选择不可能影响性能，因为只有一种方法，即(反)半连接执行操作，可以得到它们的结果。

举个例子:

查询1(计划)

select * from dt where dt.customer in (select c.code from customer c where c.active=0)

查询2(计划)

select * from dt where exists (select 1 from customer c where c.code=dt.customer and c.active=0)
小开

我知道这是一个很老的问题，但我想我的回答会增加一些技巧。

我刚刚看到一个关于 Sql 存在 vs in vs join的博客，结果表明它的性能基本相同。

但是，这两者之间的负面影响如下:

  1. in语句有一个缺点，它只能比较一列上的两个表。

  2. join语句将在重复值上运行，而 in和 exists将忽略重复值。

但是当你观察执行时间时，并没有很大的不同。

有趣的是，当您在表上创建一个 index时，来自 join的执行效果更好。

而且我认为 join还有另一个好处，那就是它更容易编写和理解，特别是对于新手来说。


提交答案