如何提高此SQL Server查询的性能？

Question

我在网络开发者面试时被问到这个问题。我的回答面试官说你以后在第二个表:(

我有两个表employee和bademployee：

• employee ( EMPID INT PK，名称VARCHAR（20）`）
• bademployee（badempid int pk, name varchar(20)）

现在，我只想选择优秀的员工。

我的回答是：

SELECT * 
FROM employee 
WHERE empid NOT IN (SELECT badempid from bademployee) 

他说，这个查询是不好的表现。

任何人都可以告诉我如何为相同的结果编写查询，不使用否定词（不在，！=）。

是否可以使用LEFT OUTER JOIN？

Answer 1

这可以使用OUTER JOIN与NULL检查或使用NOT EXISTS来重写。我喜欢NOT EXISTS：

SELECT * 
FROM Employee e 
WHERE NOT EXISTS (
    SELECT 1 
    FROM bademployee b 
    WHERE e.empid = b.badempid) 

这里是OUTER JOIN，但我相信你将有更好的服务表现与NOT EXISTS。

SELECT e.* 
FROM Employee e 
    LEFT JOIN bademployee b ON e.empid = b.badempid 
WHERE b.badempid IS NULL 

以下是关于性能差异一个有趣的文章：http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Answer 2

不管别人怎么说，你需要检查的执行计划和底座您的是什么赛斯结论。永远不要相信别人声称这个或那个，研究他的主张并通过关于这个主题的文档来验证，在这种情况下，执行计划清楚地告诉你发生了什么。来自SQL Authority博客的

One example显示LEFT JOIN解决方案的性能比NOT IN解决方案差得多。这是由查询计划程序执行的左侧反半连接引起的，该查询计划程序通常比LEFT JOIN + NULL检查执行得更好。行数很少时可能会有例外。作者之后还会跟我在第一段中所做的一样告诉你：总是检查执行计划。

Another blog post来自SQL Performance博客进一步介绍了这个实际性能测试结果。

TL; DR：在性能方面NOT EXISTS和NOT IN在同一级别，但NOT EXISTS最好是由于NULL值的问题。此外，不要只相信任何人声称，研究和验证您的执行计划。

Answer 3

我认为面试官在表现差异方面是错误的。因为连接的列在两个表中都是唯一的且不为空，所以NOT IN,NOT EXISTS和LEFT JOIN...WHERE IS NULL查询在语义上是相同的。 SQL是一种声明性语言，因此SQL Server优化程序可以提供最佳且相同的计划，而不管现在查询是否被表达。也就是说，它并不总是完美的，所以可能会有差异，特别是对于更复杂的查询。

下面是一个脚本，演示了这一点。在我的SQL Server 2014框中，我看到前2个查询（排序聚集索引扫描和合并连接）的相同执行计划以及最后一个过滤运算符的添加。我预计所有3个人的表现都一样，所以从性能的角度来看并不重要。我通常会使用NOT EXISTS，因为意图更清晰，并且它避免了NOT IN子查询返回NULL的情况，因此导致由于UNKNOWN谓词结果返回零行。

我不会推广这样的性能比较。如果连接的列允许NULL或不能保证是唯一的，则这些查询在语义上不是相同的，因此可能会产生不同的执行计划。

CREATE TABLE dbo.employee (
    empid int CONSTRAINT pk_employee PRIMARY KEY 
    , name varchar(20) 
    ); 

CREATE TABLE dbo.bademployee (
     badempid int CONSTRAINT pk_bademployee PRIMARY KEY 
    , name varchar(20) 
    ); 

WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)) 
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d) 
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c) 
INSERT INTO dbo.employee(empid, name) 
SELECT num, 'Employee name ' + CAST(num AS varchar(10)) 
FROM t16M 
WHERE num <= 10000; 

INSERT INTO dbo.bademployee(badempid, name) 
SELECT TOP 5 PERCENT empid, name 
FROM dbo.employee 
ORDER BY NEWID(); 
GO 
UPDATE STATISTICS dbo.employee WITH FULLSCAN; 
UPDATE STATISTICS dbo.bademployee WITH FULLSCAN; 
GO 

SELECT * 
FROM employee 
WHERE empid NOT IN (SELECT badempid from bademployee); 

SELECT * 
FROM Employee e 
WHERE NOT EXISTS (
    SELECT 1 
    FROM bademployee b 
    WHERE e.empid = b.badempid); 

SELECT e.* 
FROM Employee e 
    LEFT JOIN bademployee b ON e.empid = b.badempid 
WHERE b.badempid IS NULL; 
GO