SQL Server-聚焦LEFT JOIN...IS NULL AND NOT EXISTS效能分析（十七）

標籤：and   難解   影響   its   原因   不能   div   儲存   例子   

前言

本節我們來分析LEFT JOIN和NOT EXISTS，簡短的內容，深入的理解，Always to review the basics。

LEFT JOIN...IS NULL和NOT EXISTS分析

之前我們已經分析過IN查詢在處理空值時是基於三值邏輯，只要子查詢中存在空值此時則沒有任何資料返回，而LEFT JOIN和NOT EXISTS無論子查詢中有無空值上處理都是一樣的，當然比較重要的是利用LEFT JOIN...IS NULL來檢查NULL。基於二者返回的結果集是一樣的，下面我們開始直接用前面節所建立表來進行測試。在BigTable和SmallerTable上首先未建立索引

USE TSQL2012GODBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONSET STATISTICS TIME ONSELECT BigTable.ID, SomeColumn  FROM BigTable LEFT OUTER JOIN SmallerTable ON BigTable.SomeColumn = SmallerTable.LookupColumn  WHERE LookupColumn IS NULLSELECT ID, SomeColumn FROM BigTableWHERE NOT EXISTS (SELECT LookupColumn FROM SmallerTable WHERE SmallerTable.LookupColumn = BigTable.SomeColumn)

 

二者執行CPU Time和elapsed Time如下

我們看到上述查詢計劃未建立索引之前二者在開銷上接近一致，而LEFT JOIN....IS NULL則首先進行雜湊匹配中的右方外部聯結，然後就是過濾，換句話說是LEFT JOIN....IS NULL會直接完全JOIN，然後再對重複資料進行過濾，而NOT EXISTS則是直接利用雜湊匹配中的右半聯結，關於半聯結我們在前面也已經說過，此時若有重複資料直接只取一個。所以LEFT JOIN....IS NULL和NOT EXISTS二者對於重複資料一個通過兩部操作完成先完全JOIN後進行過濾，而另外一個則是直接通過右半聯結過濾。所以對於此二者最大的不同在於：當使用LEFT JOIN.....IS NULL時，SQL還沒有那麼聰明，僅僅只檢查一次，因此它需要通過完全JOIN和過濾來完成，而NOT EXISTS則是在JOIN時就進行過濾。

在看二者執行CPU TIME和elapsed TIME時間，沒有太大的差異。接下來我們再來建立索引看看。

CREATE INDEX idx_BigTable_SomeColumnON BigTable (SomeColumn) CREATE INDEX idx_SmallerTable_LookupColumnON SmallerTable (LookupColumn)

看看二者的查詢執行計畫

 

此時我們通過看到上述查詢執行計畫，我們能夠清楚的看到LEFT JOIN....IS NULL還是完全JOIN然後在過濾，只是建立了索引之後效能改善了一點而已，但是不同於LEFT JOIN...IS NULL的NOT EXISTS的計劃執行情況不同於未建立索引，此時首先利用了流彙總然後雜湊匹配中的右半聯結變成了合并聯結中的右半聯結，我們一個個來看，這個Stream Aggregate（流彙總）是什麼鬼，對於此流彙總我是不瞭解的，不能裝懂，我們接下來具體講講流彙總，至於為什麼每當查詢計劃出現一個新的名詞都要去詳細瞭解下的原因，相信看過我SQL Server本系列的童鞋知道，每一節的內容都非常短，不會出現閱讀疲勞，而且是精講，我重頭系統學習SQL Server是為了對SQL Server中所有涉及到對效能調優有關的地方以及一些基礎知識都會去過一遍，以便後續再出現效能調優不至於束手無策。好了，回到話題，我們看看Stream Aggregate。

Stream Aggregate

msdn上有關概念如下：Stream Aggregate運算子按一列或多列對行分組，然後計算查詢返回的一個或多個彙總運算式。此運算子的輸出可供查詢中的後續運算子引用和/或返回到用戶端。Stream Aggregate 運算子要求輸入在組中按列進行排序。如果由於前面的 Sort 運算子或已排序的索引尋找或掃描導致資料尚未排序，最佳化器將在此運算子前面使用一個 Sort 運算子。在 SHOWPLAN_ALL 語句或 SQL Server Management Studio 的圖形執行計畫中，GROUP BY 謂詞中的列會列在 Argument 列中，而彙總運算式列在 Defined Values 列中。 

通過上述定義僅僅只是知道Stream Aggregate是用對行或者列進行彙總，至於什麼時候在查詢計劃中出現流彙總，什麼時候利用流彙總來提高查詢效能都是不得而知，我們接下來一起探討下。上述著重在於【分組】然後進行【彙總】計算，基於這點我們來看看使用Stream Aggregate的三種情境。

（1）摘要彙總

USE TSQL2012GOSELECT COUNT(custid) AS cutid, SUM(empid) AS empidFROM Sales.Orders

（2）先分組，再摘要彙總

USE TSQL2012GOSELECT custid, COUNT(custid) AS countCustIdFROM Sales.OrdersGROUP BY custid

（3）DISTINCT匯總

USE TSQL2012GOSELECT DISTINCT custidFROM 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。我們繼續回到LEFT JOIN....IS NULL和NOT EXISTS話題，當我們建立索引之後此時LEFT JOIN....IS ISNULL執行時間是NOT EXISITS的兩倍多。到此，關於LEFT JOIN...IS NULL和NOT EXISTS就此結束，我需要們同樣下個基本結論。

LEFT JOIN...IS NULL和NOT EXISTS效能分析結論：當我們需要找到子查詢中不匹配的行並且列為可空時，此時用NOT EXISTS，當需要找到子查詢中不匹配的行，此時列不為空白時可以用NOT EXISTS或者NOT IN。

由於LEFT JOIN..IS NULL對於不匹配的行不會立即進行返回而先需要完全JOIN後過濾，尤其是當有多個條件時，LEFT JOIN...IS NULL可能會更加影響查詢效能。

總結

本節我們學習了LEFT JOIN..IS NULL和NOT EXISTS的效能分析，下節我們進入這幾節內容的綜合篇，綜合比較NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL終極篇。簡短的內容，深入的理解，我們下節再會。 

SQL Server-聚焦LEFT JOIN...IS NULL AND NOT EXISTS效能分析（十七）