SQL Server 執行計畫操作符詳解（1）——斷言(Assert

標籤：

前言：

 

很多很多地方對於語句的最佳化，一般比較靠譜的回複即使——把執行計畫發出來看看。當然那些只看語句就說如何如何改代碼，我一直都是拒絕的，因為這種算是純蒙。根據本人經驗，大量的效能問題單純從語句來看很難發現瓶頸，同一個語句，由於環境的不同，差距非常大，所以比較合適的還是分析執行計畫。

那麼對於執行計畫，一般使用圖形化執行計畫就差不多了，但是用過的人也有一些疑惑，裡面的表徵圖（稱為操作符）並不非常直觀。所以從本文開始，會整理一些不怎麼常見但由比較重要的操作符並進行解釋，對於那些表掃描、索引掃描、叢集索引掃描、索引尋找、叢集索引尋找這些非常常見的操作符，暫時不打算介紹。

只有瞭解一些重要且常見的操作符，才能對語句進行準確有效效能分析和最佳化。

本系列文章預計包含下面操作符：    

1. 斷言：Assert （英文版本圖形化介面的名字，中文版本中XML格式的執行計畫和TEXT格式的執行計畫的名字。下同）    
2. 串聯：Concatenation   
3. 計算標量：Compute Scalar  
4. 鍵尋找：Key Lookup      
5. 假離線：Spools      
6. 表假離線：Lazy Spool   
7. 索引假離線：Index Spool      
8. 行計數假離線：Row CountSpool
9. 流彙總：Stream Aggregate  
10. 排序：Sort     
11. 合并聯結：Merge Join 
12. 合并間隔：Merge Interval   
13. 拆分、摺疊：Split，Collapse

 接下來從斷言開始介紹。原文出處：http://blog.csdn.net/dba_huangzj/article/details/50261747

斷言：
Assert運算子是一個物理運算子。在執行計畫中，如果為中文版圖形化執行計畫，被稱為“斷言”，在英文版及非圖形化執行計畫中顯示為Assert。

其表徵圖為：

Assert 運算子用於驗證條件。例如，驗證參考完整性或確保標量子查詢返回一行。對於每個輸入行，Assert 運算子都要計算執行計畫的 Argument 列中的運算式。如果此運算式的值為 NULL，則通過 Assert 運算子傳遞該行，並且查詢執行將繼續。如果此運算式的值非空，則將產生相應的錯誤。

斷言與Check約束：

 

先來看看這段代碼，在伺服器執行時，先建立測試環境，使用TempDB是不錯的選擇：

USE tempdbGOIF OBJECT_ID(‘TableAssert‘) IS NOT NULLDROP TABLE TableAssertGOCREATE TABLE TableAssert (ID INTEGER,Gender CHAR(1))GOALTER TABLE TableAssert ADD CONSTRAINT ck_Gender_M_F CHECK (Gender IN (‘M‘,‘F‘))GO

選中下面代碼，不要執行，選擇“顯示估計的執行計畫”，

代碼如下：

INSERT INTO TableAssert(ID ,Gender    )VALUES (1,‘X‘)GO

從可見有一個操作符叫“斷言（Assert）”，那麼這個裡面是什麼東西呢？把滑鼠移到這個操作符上面可以看到：

注意上面的解釋：用於驗證指定的條件是否存在，這個解釋很直觀，並且看謂詞部分，說明了實際驗證的內容，判斷Gender欄位的插入值是否屬於F/M兩種，如果不是則返回NULL。

斷言操作符會針對驗證傳回值進行處理，如果驗證返回NULL，則返回錯誤資訊，也就是如果你直接執行INSERT語句就可以看到報錯：

 

原文出處：http://blog.csdn.net/dba_huangzj/article/details/50261747

斷言與外鍵約束：

下面來看個關於外鍵約束的例子：

use tempdbgoALTER TABLE TableAssert ADD ID_Genders INT GO  IF OBJECT_ID(‘TableFOREIGN‘) IS NOT NULL   DROP TABLE TableFOREIGN GO CREATE TABLE TableFOREIGN(ID Integer PRIMARY KEY, Gender CHAR(1))  GO  INSERT INTO TableFOREIGN(ID, Gender) VALUES(1, ‘F‘) INSERT INTO TableFOREIGN(ID, Gender) VALUES(2, ‘M‘) INSERT INTO TableFOREIGN(ID, Gender) VALUES(3, ‘N‘) GO  ALTER TABLE TableAssert ADD CONSTRAINT fk_Tab2 FOREIGN KEY (ID_Genders) REFERENCES TableFOREIGN(ID) GO  

同樣，我們使用估計執行計畫測試一下INSERT語句：

語句如下：

INSERT INTO TableAssert(ID, ID_Genders, Gender) VALUES(1, 4, ‘X‘)

這次我們使用另外一個工具：SET SHOWPLAN_TEXT ON 按這種方式執行：

SET SHOWPLAN_TEXT ONGOINSERT INTO TableAssert(ID, ID_Genders, Gender) VALUES(1, 4, ‘X‘)

會看到兩個結果，第一個是語句，不用關，我們看第二個結果：

|--Assert(WHERE:(CASE WHEN NOT [Pass1009] AND [Expr1008] IS NULL THEN (0) ELSE NULL END))       |--Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[TableAssert].[ID_Genders] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[TableAssert].[ID_Genders]), DEFINE:([Expr1008] = [PROBE VALUE]))            |--Assert(WHERE:(CASE WHEN [tempdb].[dbo].[TableAssert].[Gender]<>‘F‘ AND [tempdb].[dbo].[TableAssert].[Gender]<>‘M‘ THEN (0) ELSE NULL END))            |    |--Table Insert(OBJECT:([tempdb].[dbo].[TableAssert]), SET:([tempdb].[dbo].[TableAssert].[ID] = [@1],[tempdb].[dbo].[TableAssert].[ID_Genders] = [@2],[tempdb].[dbo].[TableAssert].[Gender] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@3],0)))            |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TableFOREIGN].[PK__TableFOR__3214EC27173876EA]), SEEK:([tempdb].[dbo].[TableFOREIGN].[ID]=[tempdb].[dbo].[TableAssert].[ID_Genders]) ORDERED FORWARD)

這個結果內容較多可能不直觀，讀者可以執行測試看結果。

可以看到裡面有兩次Assert，自下而上地閱讀，第一個Assert（也就是下面那個，針對於圖形化介面而言是右邊那個，因為圖形化執行計畫是從右至左地閱讀）是前面用於CHECK約束的，如果返回0則繼續運行語句，否則返回錯誤。

對於第二個Assert用於檢測兩表關聯的結果，其中“[Expr1008] IS NULL”（注意[Expr1008]不是固定的，根據每台機器可能返回不同值，在本人機器上的SQL 2008/2012分別執行都得到不同的[Expr]值），我們需要知道[Expr1008]是什麼，內容中有DEFINE:([Expr1008] = [PROBE VALUE])，這就是表關聯的結果。如果INSERT語句中ID_Gender的值已經存在與TableFOREIGN，那麼這個Probe（探測器）會返回關聯值。否則返回NULL。所以這個“斷言”是檢查TableForeign中的值，如果沒有找到INSERT中傳入的值，斷言會返回一個異常。

如果ID_Genders的值為NULL，那麼SQL Server不能返回異常，而是返回“0”並繼續運行語句。如果運行上面的INSERT語句，SQL Server會返回異常，因為值為’X’，違反了check約束：

但是如果把X換成F再運行，還是會報錯，因為違反了外鍵約束：

但是當把4換成NULL或1或2或3之後，再運行插入語句，就不會產生異常：

原文出處：http://blog.csdn.net/dba_huangzj/article/details/50261747

斷言與子查詢：

斷言操作符同樣可以用於檢查子查詢，對於標量子查詢不能返回多個值，但是有時候寫法和資料的變動會引發多值錯誤。此時斷言扮演著校正標量子查詢是否返回一個值的角色。

下面來看看這兩個語句：

INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert), ‘F‘)    INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert), ‘F‘)

用上面的方法查看一下執行計畫：

SET SHOWPLAN_TEXT ONGOINSERT INTO TableAssert(ID,Gender) VALUES((SELECT ID FROM TableAssert), ‘F‘) INSERT INTO TableAssert(ID,Gender) VALUES((SELECT ID FROM TableAssert), ‘F‘)

觀察語句大概可以知道發生什麼情況，第一個insert會成功（除非你已經修改過裡面的資料），因為VALUES中的SELECT部分只返回一個值，但是第二個INSERT由於VALUES中的SELECT有兩個值（第一個INSERT加入的），所以會報錯。

結果如下： 

|--Assert(WHERE:([Expr1013]))      |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN[tempdb].[dbo].[TableAssert].[Gender]<>‘F‘ AND[tempdb].[dbo].[TableAssert].[Gender]<>‘M‘ THEN (0) ELSE NULL END))           |--Table Insert(OBJECT:([tempdb].[dbo].[TableAssert]),SET:([tempdb].[dbo].[TableAssert].[ID] =[Expr1009],[tempdb].[dbo].[TableAssert].[Gender] =[Expr1010],[tempdb].[dbo].[TableAssert].[ID_Genders] = NULL))                 |--Top(TOP EXPRESSION:((1)))                      |--ComputeScalar(DEFINE:([Expr1009]=[Expr1012], [Expr1010]=‘F‘))                           |--Nested Loops(LeftOuter Join)                                |--ConstantScan                               |--Assert(WHERE:(CASE WHEN [Expr1011]>(1) THEN (0) ELSE NULL END))                                     |--StreamAggregate(DEFINE:([Expr1011]=Count(*),[Expr1012]=ANY([tempdb].[dbo].[TableAssert].[ID])))                                         |--Table Scan(OBJECT:([tempdb].[dbo].[TableAssert]))

注意最內層的Assert：

可以看到SQL Server建立一個StreamAggregate（流匯聚，可從預估執行計畫中看到其解釋，後續會專門介紹）去計運算元查詢會返回多少資料，然後把這個值傳遞給斷言用於檢測。

作為已經商業化二十幾年的產品，其核心（查詢最佳化工具）已經經過了很多年的積累和改進，高版本的SQL Server（如2008 R2及以上版本，這個沒有絕對標準），會對語句和表結構的當前情況來判斷是否需要使用“斷言，Assert”操作符。比如：

INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert WHERE ID = 1), ‘F‘) INSERT INTO TableAssert(ID, Gender) VALUES((SELECT TOP 1 ID FROM TableAssert), ‘F‘)

原文出處：http://blog.csdn.net/dba_huangzj/article/details/50261747

先不執行，開啟估計執行計畫再看圖形化介面，可以看到如下結果：

因為最佳化器檢測到第二個語句裡麵包含了TOP 1，僅返回一行資料，所以沒有必要引入斷言來檢測。

總結：
到這裡為止，對這個操作符的介紹已經完畢，下一篇會介紹串聯操作符。對於這個斷言操作符，我們需要知道它是用來“驗證”某些條件，但是每個操作符的引入都必將帶來一定的開銷，可是這些操作符的引入又是必須的，因為需要它們完成一些任務。如果需要改進，不妨先看看它是用來檢驗什麼，比如上面提到的子查詢，可以通過使用TOP 1、添加唯一約束等方式來減少這種校正。但是所有改進都應該做充分的測試和論證。

原文出處：http://blog.csdn.net/dba_huangzj/article/details/50261747

SQL Server 執行計畫操作符詳解（1）——斷言(Assert