SQLServer-聚焦ISNULLANDNOTEXISTS性能分析
（⼗七）
前⾔
本节我们来分析LEFT JOIN和NOT EXISTS，简短的内容，深⼊的理解，Always to review the basics。
IS NULL和NOT EXISTS分析
之前我们已经分析过IN查询在处理空值时是基于三值逻辑，只要⼦查询中存在空值此时则没有任何数据返回，⽽LEFT JOIN和NOT EXISTS ⽆论⼦查询中有⽆空值上处理都是⼀样的，当然⽐较重要的是利⽤IS NULL来检查NULL。基于⼆者返回的结果集是⼀样的，下⾯我们开始直接⽤前⾯节所创建表来进⾏测试。在BigTable和SmallerTable上⾸先未创建索引
USE TSQL2012
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT BigTable.ID, SomeColumn
FROM BigTable LEFT OUTER JOIN SmallerTable ON BigTable.SomeColumn = SmallerTable.LookupColumn
WHERE LookupColumn IS NULL
SELECT ID, SomeColumn FROM BigTable
WHERE NOT EXISTS (SELECT LookupColumn FROM SmallerTable WHERE SmallerTable.LookupColumn = BigTable.SomeColumn)
⼆者执⾏CPU Time和elapsed Time如下
我们看到上述查询计划未创建索引之前⼆者在开销上接近⼀致，⽽IS NULL则⾸先进⾏哈希匹配中的右外部联接，然后就是过滤，换句话说是IS NULL会直接完全JOIN，然后再对重复数据进⾏过滤，⽽NOT EXISTS则是直接利⽤哈希匹配中的右半联
接，关于半联接我们在前⾯也已经说过，此时若有重复数据直接只取⼀个。所以IS NULL和NOT EXISTS⼆者对于重复数据⼀个通过两部操作完成先完全JOIN后进⾏过滤，⽽另外⼀个则是直接通过右半联接过滤。所以对于此⼆者最⼤的不同在于：当使⽤IS NULL时，SQL还没有那么聪明，仅仅只检查⼀次，因此它需要通过完全JOIN和过滤来完成，⽽NOT EXISTS则是在JOIN时就进⾏过滤。
在看⼆者执⾏CPU TIME和elapsed TIME时间，没有太⼤的差异。接下来我们再来创建索引看看。
CREATE INDEX idx_BigTable_SomeColumn
ON BigTable (SomeColumn)
CREATE INDEX idx_SmallerTable_LookupColumn
ON SmallerTable (LookupColumn)
看看⼆者的查询执⾏计划
此时我们通过看到上述查询执⾏计划，我们能够清楚的看到IS NULL还是完全JOIN然后在过滤，只是创建了索引之后性能改善了⼀点⽽已，但是不同于IS NULL的NOT EXISTS的计划执⾏情况不同于未创建索引，此时⾸先利⽤了流聚合然后哈希匹配中的右半联接变成了合并联接中的右半联接，我们⼀个个来看，这个Stream Aggregate（流聚合）是什么⿁，对于此流聚合我是不了解的，不能装懂，我们接下来具体讲讲流聚合，⾄于为什么每当查询计划出现⼀个新的名词都要去详细了解下的原因，相信看过我SQL Server本系列的童鞋知道，每⼀节的内容都⾮常短，不会出现阅读疲劳，⽽且是精讲，我重头系统学习SQL Server是为了对SQL Server中所有涉及到对性能调优有关的地⽅以及⼀些基础知识都会去过⼀遍，以便后续再出现性能调优不⾄于束⼿⽆策。好了，回到话题，我们看看Stream Aggregate。
Stream Aggregate
msdn上有关概念如下：Stream Aggregate运算符按⼀列或多列对⾏分组，然后计算查询返回的⼀个或多个聚合表达式。此运算符的输出可供查询中的后续运算符引⽤和/或返回到客户端。Stream Aggregate 运算符要求输⼊在组中按列进⾏排序。如果由于前⾯的 Sort 运算符或已排序的索引查或扫描导致数据尚未排序，优化器将在此运算符前⾯使⽤⼀个。在 SHOWPLAN_ALL 语句或 SQL Server Management Studio 的图形执⾏计划中，GROUP BY 谓词中的列会列在 Argument 列中，⽽聚合表达式列在 Defined Values 列中。
通过上述定义仅仅只是知道Stream Aggregate是⽤对⾏或者列进⾏聚合，⾄于什么时候在查询计划中出现流聚合，什么时候利⽤流聚合来提⾼查询性能都是不得⽽知，我们接下来⼀起探讨下。上述着重在于【分组】然后进⾏【聚合】计算，基于这点我们来看看使⽤Stream Aggregate的三种场景。
（1）聚合汇总
USE TSQL2012
GO
SELECT COUNT(custid) AS cutid, SUM(empid) AS empid
FROM Sales.Orders
（2）先分组，再聚合汇总
USE TSQL2012
GO
SELECT custid, COUNT(custid) AS countCustId
FROM Sales.Orders
GROUP BY custid
（3）DISTINCT汇总
USE TSQL2012
GO
SELECT DISTINCT custid
FROM Sales.Orders
上述查询使⽤通过DISTINCT，实际上是对cutid进⾏了分组。以上是⽤到了Stream Aggregate的场景，当然聚合还有另外⼀种就是哈希匹配聚合，后续会再进⾏补充。我们再来理解Stream Aggregate定义，我们将定义概括为对输⼊进⾏排序后，接下来进⾏分组然后再进⾏聚合计算。在上述（2）和（3）中都是进⾏了分组，但是没有排序，实际上内部已经默认实现了排序，我们看下在（3）中表中custid数据，如下
当进⾏DISTINCT之后
但是在（3）中没有进⾏聚合，为什么会进⾏流聚合呢？实际上在流聚合中存在状态变量，状态变量具
体个数根据聚合个数⽽定，此状态变量⽤来设置结果集，当进⾏分组后对应的数据进⾏保存，此时对应的状态变量为0，当匹配到对应数据时此时状态变量加1，所以上述（3）中可以说隐式进⾏了聚合计算，只是每条数据对应的状态变量为0⽽已，到了这⾥就不难解释，只进⾏了排序，分组⽽没有进⾏聚合计算的原因。关于Stream Aggregate都知道的⼀个例⼦则是我们在利⽤SqlDataReader记性读取数据时，可以说是读取流记录，如果我们需要汇总结果集时，此时每当Read时，其内部的状态变量都会加1最终返回汇总和到客户端。在这⾥我们只是简单讲讲Stream Aggregate，后续会⼀并讲讲Hash Aggregate。我们继续回到IS NULL和NOT EXISTS话题，当我们创建索引之后此时IS ISNULL执⾏时间是NOT EXISITS的两倍多。到此，关于IS NULL和NOT EXISTS就此结束，我们同样下个基本结论。
IS NULL和NOT EXISTS性能分析结论：当我们需要到⼦查询中不匹配的⾏并且列为可空时，此时⽤NOT EXISTS，当需要到⼦查询中不匹配的⾏，此时列不为空时可以⽤NOT EXISTS或者NOT IN。
由于LEFT JOIN..IS NULL对于不匹配的⾏不会⽴即进⾏返回⽽先需要完全JOIN后过滤，尤其是当有多个条件时，IS NULL可能会更加影响查询性能。
总结
本节我们学习了LEFT JOIN..IS NULL和NOT EXISTS的性能分析，下节我们进⼊这⼏节内容的综合篇，综合⽐较NOT IN VS NOT EXISTS VS IS NULL终极篇。简短的内容，深⼊的理解，我们下节再会。
>多表left join

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。