SQL Server-聚焦NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL性能分析（十八）

tiyan

前言
　　本节我们来综合比较NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL的性能，简短的内容，深入的理解，Always to review the basics。

NOT IN、NOT EXISTS、LEFT JOIN...IS NULL性能分析
　　我们首先创建测试表



USE TSQL2012
GO
CREATE SCHEMA [compare]
CREATE TABLE [compare].t_left (
id INT NOT NULL PRIMARY KEY,
value INT NOT NULL,
stuffing VARCHAR(200) NOT NULL
)
CREATE TABLE [compare].t_right (
id INT NOT NULL PRIMARY KEY,
value INT NOT NULL,
stuffing VARCHAR(200) NOT NULL
)
GO
　　接着我们在两个表中的列value上创建索引



USE TSQL2012
GO
CREATE INDEX idx_left_value ON [compare].t_left (value)
CREATE INDEX idx_right_value ON [compare].t_right (value)
　　我们在t_left和t_right表中插入如下测试数据



USE TSQL2012
GO
BEGIN TRANSACTION
DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= 100000
BEGIN
INSERT
INTO    [compare].t_left
VALUES  (
@cnt,
@cnt % 10000,
LEFT('Left ' + CAST(@cnt AS VARCHAR) + ' ' + REPLICATE('*', 200), 200)
)
SET @cnt = @cnt + 1
END;
WITH    rows AS
(
SELECT  1 AS row
UNION ALL
SELECT  row + 1
FROM    rows
WHERE   row < 10
)
INSERT
INTO    [compare].t_right
SELECT  (id - 1) * 10 + row + 1,
value + 1,
LEFT('Right ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 200), 200)
FROM    [compare].t_left
CROSS JOIN
rows
COMMIT
　　我们稍微解释下上述插入的测试数据：
　　（1）t_left表中插入10万条数据，其中包含1万条重复数据。
　　（2）t_right表中插入100万条数据，其中包含1万条重复数据。
　　（3）t_left表中插入10条t_right表中没有的数据。
　　接下来我们一个个来看看其查询执行计划。

NOT IN性能分析



USE TSQL2012
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT  l.id, l.value
FROM    [compare].t_left l
WHERE   l.value NOT IN
(
SELECT  value
FROM    [compare].t_right r
)

　　我们重点看看上述图做了标记的两个重要的地方，最后返回结果集时使用了Merge Anti Semi Join也就是说是上述Merge Join和Right Anti Semi Join的结合，可以说这是一种非常高效的方式，事先通过索引来排序然会获取两个表的结果集。数据库通过Merge Join来迭代两个表的结果集从小值到大值，当然也是通过指针指向二者结果集的当前值然后接着指向下一个值。而Anti Semi Join主要是干什么的呢？前面我们讲过它是半联接，此时数据库引擎只要匹配到t_right表中的值就跳过所有t_left和t_right表其他也同样匹配的同一个值，为什么会跳过呢？ 因为此时Stream Aggregate起到了决定性作用（【关于Stream Aggregate前面简单了解了下，感觉理解的还是不够透，写这篇文章时才算是灰常了解了，后续会专门写写Stream Aggregate和Hash Aggregate】）我们知道Stream Aggregate首先需要排序，然后进行分组接着就是聚合，因为我们建立了索引所以就有了排序，接着执行Stream Aggregate进行分组，通过查看Stream Aggregate如下具体信息知道。因为对t_right表中的值进行了分组，所以当进行合并右半联接时，只取组中第一个，其余的自然而然就进行跳过，所以这种方式非常高效，通过索引来进行排序，再通过Stream Aggregate进行分组，最后执行Merge Join(Right Anti Semi Join)。最后我们看到查询仅仅只耗费了0.315秒。

NOT EXISTS性能分析
　　我们运行如下查询



USE TSQL2012
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT  l.id, l.value
FROM    [compare].t_left l
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    [compare].t_right r
WHERE   r.value = l.value
)

　　关于其查询耗费时间就不再给出了，其实NOT EXISTS和NOT查询计划和查询时间都是一样的，并没有任何区别，我们之前在单独讨论NOT EXISTS和NOT IN时就已经明确说过，二者在查询列不为NULL的前提下，二者的查询开销是一样的，而将查询列设置为可NULL时，NOT EXISTS的性能远高于NOT IN，这里我们就不过多的讨论了，不明白的童鞋可以看看前面关于二者比较的文章。

LEFT JOIN....IS NULL性能分析



USE TSQL2012
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT  l.id, l.value
FROM    [compare].t_left l
LEFT JOIN
[compare].t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

　　到这里我们知道很显然结果集肯定是一样的，但是查询计划和上述NOT EXISTS、NOT IN有很大的差异，LEFT JOIN...IS NULL首先是使用LEFT JOIN返回所有数据，其中包括重复的，然后再进行过滤，为什么会先进行LEFT JOIN然后再进行Filter呢？因为SQL Server根本无法很智能的识别LEFT JOIN上紧跟着的IS NULL，所以需要两步操作来完成。此时我们需要过滤100万条数据，这是一个非常耗时的工作，所以此时利用非常高效的Hash Match并且是并行的，但是过滤这些值还是要花费很长时间。整个时间花费了0.989秒，其查询耗费时间是NOT EXISTS或者NOT IN的3倍。所以到这里，关于此三者我们可以定下如下这样一个结论。
　　NOT IN VS NOT EXISTS VS LEFT JOIN..IS NULL结论：当查询缺省值时利用NOT EXISTS和NOT IN是最佳方式，但是前提是二者查询列都不能为NULL，否则使用NOT EXISTS。而LEFT JOIN...IS NULL因其总是不会跳过已经匹配过的值而是利用先返回所有结果集然后过滤的方式，其低效性可想而知。

总结
　　本节我们比较了NOT EXISTS和NOT IN和LEFT JOIN..IS NULL的性能，最终得出了三者性能分析结论，下一节我们已经确定是最后一篇终极篇比较EXISTS VS IN VS JOIN的性能，简短的内容，深入的理解，我们下节再会。