logo标签列表

SQL Server中IN和EXISTS的性能比较

sql-serversql-server-2005existsquery-performancesql-in
139

我想知道以下哪种方法更有效?

我一直对使用IN有点谨慎，因为我认为SQL Server会将结果集转换为一个大的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])
9
最好的方法是尝试并进行一些测量。 - Klaus Byskov Pedersen
11
这里一定有无数个重复的...... - marc_s
@marc_s：是的，但我认为这确实取决于情况。我猜想规范答案应该是klausbyskov（或者任何可以声称先前艺术品的人）。 - D'Arcy Rittich
7
如果您希望最高性能，可以在“where exists”中使用“select 1 from Base...”，因为您并不真正关心结果，只需确保存在一行即可。 - brad
@brad 不完全是这样。编译器会忽略 select 和 from 之间的任何内容（除非您在其中放置另一个子查询或类似的东西...奇怪）。在我看来，语法应该是 semi join table2 on ....。 - George Menoutis
10个回答
167

EXISTS语句会更快，因为一旦引擎找到一条匹配记录，它就会停止查找，因为条件已经被证明是真的。

而对于IN语句，在进一步处理之前，它将首先收集子查询中的所有结果。

85
以前这个说法是正确的，但在当前版本（至少2008年以后）中，优化器更加聪明了…… 它将 IN() 与 EXISTS() 处理方式视为相同。 - Aaron Bertrand
14
@Aaron - 是的，通常优化器会在内部生成更好的计划。然而，在更复杂的情况下，依赖内部快捷方式可能会有害。 - Scott Coates
奇怪。对于我的一个查询，尽管查询计划完全相同，但解释显示IN的行数为972，而EXIST的行数为959。其他所有内容都相同。不过这是在Mysql 5.1.42上，可能只是因为版本太旧了。 - techdude
3
这就是错误的，简单明了。这种情况在2010年时存在，现在仍存在。 - Magnus
4
IN和EXISTS在查询计划和IO方面完全相同。没有理由认为它们的性能有所不同。检查一下时间统计数据，自己证明即可。 - Nelssen
我曾经看到在SQL 2019中，IN被视为完整的子查询，而不得不将其转换为EXISTS。我认为过时的统计数据可能会导致这种情况发生，但我只是猜测。大多数情况下它们是相同的。 - Brain2000
45
被接受的答案短视，问题有点模糊，因为：

1）两者都没有明确说明左侧、右侧或两侧是否存在覆盖索引。

2）两者都没有考虑输入左侧集和输入右侧集的大小。
（问题只提到了一个总体较大的结果集。）

我认为当 (1) 和 (2) 带来显着成本差异时，优化器足够聪明可以在 "in" 与 "exists" 之间进行转换，否则它可能只被用作提示（例如 exists 以鼓励使用可寻址的右侧索引）。
这两种形式都可以在内部转换为连接形式，反转连接顺序，并根据预估的行计数（左侧和右侧）和左、右侧的索引存在情况运行为循环、哈希或合并。
1
优化器对于 IN 和 EXISTS 始终会给出相同的计划。请尝试想出任何一种情况，使它们不会得到相同的计划（尽管这不适用于 NOT IN 和 NOT EXISTS）。 - Martin Smith
@MartinSmith 我认为你知道自己在说什么，但是你有没有任何证据证明计划总是相同的？如果有的话，这将消除这里长达十年的争议。 - MarredCheese
1
@MarredCheese - 要求认为这是不同的人提供一个单一的例子。 - Martin Smith
39

我已经在SQL Server 2005和2008上进行了一些测试，结果发现EXISTS和IN的实际执行计划完全相同，正如其他人所说，优化器非常优秀。 :)

需要注意的是，如果你的查询没有使用正确的语法，EXISTS、IN和JOIN有时可能会返回不同的结果：http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

9
6
这里有许多误导性的答案，包括得到了高票的回答（尽管我不认为他们的运维有恶意）。简短的答案是：它们是相同的。
(T-)SQL语言中有许多关键字，但最终在硬件上发生的唯一事情就是执行查询计划中所看到的操作。
当我们调用[NOT] IN和[NOT] EXISTS时进行的关系（数学理论）操作是半连接（使用NOT时为反连接）。相应的SQL Server操作具有相同的名称，这并非巧合。没有任何提及IN或EXISTS的操作 - 只有（反）半连接。因此，由于只有（反）半连接执行操作才能获得它们的结果，逻辑等效的IN vs EXISTS选择不可能影响性能。
例如：
查询1 ( plan )
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和结果吗？ - UnhandledExcepSean
已经进行了多次测试。我可以创建另一个测试用例，而且我会这样做，但是测试用例并不意味着优化器在具有不同统计信息的表上执行完全相同的计划。这可能会让某些人认为答案是部分的，但是不存在多个半连接运算符是事实。也许我会找到一个列表并提供链接。 - George Menoutis
4

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

每当我读到优化器有多好时，我总是想在我的使用情况下它会失败 ¯_(ツ)_/¯ - akostadinov
3
所以，IN和EXISTS不同，也不会产生相同的执行计划。
通常情况下，EXISTS用于相关子查询，这意味着您将使用外部查询连接EXISTS内部查询。这将增加更多步骤来生成结果，因为您需要解决外部查询连接和内部查询连接，然后匹配它们的where子句以连接两者。
通常情况下，IN在不将内部查询与外部查询相关联的情况下使用，这可以在一个步骤中解决（在最佳情况下）。
考虑以下情况：
1. 如果您使用IN并且内部查询结果是数百万行不同的值，则可能比EXISTS慢，因为EXISTS查询具有性能（具有正确的索引以与外部查询连接）。
2. 如果您使用EXISTS并且与外部查询连接的连接很复杂（需要更长时间来执行，没有合适的索引），则它将使查询减慢外部表中的行数，有时完成估计时间可能需要几天。如果行数对于您的硬件来说是可接受的，或者数据的基数是正确的（例如，在大型数据集中较少的DISTINCT值），IN可能比EXISTS更快。
3. 当每个表上有公平数量的行时（公平数量指超过CPU处理和/或RAM缓存阈值的数量）将注意到上述所有内容。
所以答案是取决于情况。您可以在IN或EXISTS中编写复杂的查询，但作为经验法则，应尝试使用具有有限不同值集的IN和具有许多具有不同值的行的EXISTS。
诀窍是限制要扫描的行数。
问候，
MarianoC
1
为了优化EXISTS，要非常字面理解；某些东西只需要存在，但实际上不需要从相关的子查询中返回任何数据。您只是在评估布尔条件。
因此： WHERE EXISTS (SELECT TOP 1 1 FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2) 由于相关的子查询是RBAR，第一个结果使条件为真，并且不再进行处理。
我在使用LEFT JOIN + NULL编码时总是非常谨慎，因为如果你在处理NULL时不小心，很容易得到漏掉或扭曲的结果。我很少遇到过存在情况或CTE（用于查找重复项或合成插入缺失数据）不能同时满足要求并且优于LEFT JOIN + NULL的情况。 - Josh Lewis
4
当与EXISTS一起使用时，TOP 1应该是完全无关的（或者甚至是多余的）。EXISTS总是在找到任何匹配行后立即返回。 - Karl Kieninger
到目前为止，我没有看到任何性能上的好处。请展示一些执行计划的截图。 - DaFi4
1
我知道这是一个非常古老的问题，但我认为我的答案会增加一些提示。
我刚刚看到了一个关于mssqltips sql exists vs in vs join的博客，结果表明它们在性能上基本相同。
但是，它们之间的缺点如下：

  1. in语句的缺点是它只能比较两个表中的一个列。

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

但是，当您查看执行时间时，没有太大的区别。
有趣的是，当您在表上创建索引时，join的执行效果更好。
我认为join还有另一个优点，那就是对于新手来说更容易编写和理解。
-1

凭我的经验，但不保证正确：我认为在这种情况下第二个会更快。

  1. 在第一个示例中，相关子查询可能会导致每行运行子查询。
  2. 在第二个示例中，由于不相关，子查询应该只运行一次。
  3. 在第二个示例中，IN将在找到匹配项后立即停止。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接