sql - "NOT IN"在SQL查询中的性能

Question

我对 SQL 查询分析还很陌生。最近，我偶然发现其中一个查询存在性能问题，我想知道我的思维过程是否正确，以及为什么查询优化器在这种情况下会以这种方式工作。我是 SQL Server 2012。我有一个看起来像这样的 SQL 查询

SELECT * FROM T1 
WHERE Id NOT IN 
(SELECT DISTINCT T1_Id from T2);

在我的测试服务器上运行大约需要 30 秒。在试图理解什么花了这么长时间的同时，我使用临时表重写了它，如下所示:

SELECT DISTINCT T1_Id 
INTO #temp from T2;
SELECT * FROM T1 
WHERE Id NOT IN  
(SELECT T1_Id from #temp);

它的运行速度比第一个快一百倍。

有关表格的一些信息:T2 有大约 100 万行，其中大约有 1000 个不同的 T1_id 值。 T1 有大约 1000 多行。最初，我只在 T2 上除 T1_Id 以外的列上有聚集索引，因此根本没有对 T1_id 建立索引。

查看执行计划，我发现对于第一个查询，索引扫描的数量与不同的 T1_id 值的数量一样多，因此在这种情况下，SQL Server 基本上执行大约 1000 次索引扫描。这让我意识到，在 T1_id 上添加非聚集索引可能是一个好主意(不可否认，该索引从一开始就应该存在)，并且添加索引确实使原始查询运行得更快，因为现在它确实是非聚集的索引查找。

我正在寻找的是了解原始查询的查询优化器行为 - 它看起来合理吗？有没有什么方法可以让它以类似于我在此处发布的临时表变体的方式工作，而不是进行多次扫描？我只是误会了什么吗？预先感谢您提供类似讨论的任何链接，因为我还没有真正找到任何有用的东西。

Answer 1

Not in 很直观，但速度很慢。这种构造通常会运行得更快。

where id in 
(select id from t1
except select t1_id from t2)