1、1.1 執行計劃1.1.1 執行計劃操作符詳解斷言 （Assert）1.1.1.1 前言：很多地方對于語句的優化，一般比較靠譜的回復即是 把執行計劃發 出來看看。 當然那些只看語句就說如何如何改代碼， 我一直都是拒絕的， 因 為這種算是純蒙。 根據本人經驗， 大量的性能問題單純從語句來看很難發現 瓶頸，同一個語句，由于環境的不同，差距非常大，所以比較合適的還是分 析執行計劃。那么對于執行計劃，一般使用圖形化執行計劃就差不多了，但是用過的 人也有一些疑惑，里面的圖標（稱為操作符）并不非常直觀。所以從本文開 始，會整理一些不怎么常見但又比較重要的操作符并進行解釋， 對于那些表 掃描、索引掃描、聚集

2、索引掃描、索引查找、聚集索引查找這些非常常見的 操作符，暫時不打算介紹。只有了解一些重要且常見的操作符，才能對語句進行準確有效的性能分 析和優化。本系列文章預計包含下面操作符：1. 斷言： Assert （英文版本圖形化界面的名字，中文版本中 XML 格式的執 行計劃和 TEXT 格式的執行計劃的名字。下同）2. 串聯： Concatenation3. 計算標量： Compute Scalar4. 鍵查找： Key Lookup5. 假脫機： Spools6. 表假脫機： Lazy Spool7. 索引假脫機： Index Spool8. 行計數假脫機： Row CountSpool9. 流聚

3、合： Stream Aggregate10. 排序： Sort11. 合并聯接： Merge Join12. 合并間隔： Merge Interval13. 拆分、折疊： Split ，Collapse1.1.1.2 斷言：Assert 運算符是一個物理運算符。在執行計劃中，如果為中文版圖形化 執行計劃，被稱為 “斷言”，在英文版及非圖形化執行計劃中顯示為 Assert。Assert 運算符用于驗證條件。例如，驗證引用完整性或確保標量子 查詢返回 一行。對于每 個輸入行 ， Assert 運算符都要 計算執行 計劃 的 Argument 列中的表達式。 如果此表達式的值為 NULL ，則通過

4、Assert 運 算符傳遞該行，并且查詢執行將繼續。如果此表達式的值非空，則將產生相 應的錯誤。1.1.1.3 斷言與 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_Gend

5、er_M_F CHECK ( GenderIN ( 'M' , 'F' )GO選中下面代碼，不要執行，選擇 “顯示估計的執行計劃 ”，如圖：代碼如下：INSERT INTO TableAssert ( ID , Gender )VALUES ( 1, 'X' )GO從上圖可見有一個操作符叫 “斷言（ Assert）”，那么這個里面是什么東西呢？把鼠標移到這個操作符上面可以看到下圖：注意上面的解釋：用于驗證指定的條件是否存在，這個解釋很直觀，并且看 謂詞部分，說明了實際驗證的內容， 判斷 Gender字段的插入值是否屬于 F/M 兩種，如果不是則返

6、回 NULL 。斷言操作符會針對驗證返回值進行處理，如果驗證返回 NULL ，則返回錯誤 信息，也就是如果你直接執行 INSERT 語句就可以看到報錯：1.1.1.4 斷言與外鍵約束：下面來看個關于外鍵約束的例子：use tempdbgoALTER TABLETableAssertADD ID_GenderINTGOIF OBJECT_ID( 'TableFOREIGN')IS NOTNULLDROPTABLETableFOREIGNGOCREATETABLETableFOREIGN( IDIntegerPRIMARY KEY,GenderGOINSERTINTOTableFO

7、REIGN( ID ,Gender )VALUES ( 1,'F')INSERTINTOTableFOREIGN( ID ,Gender )VALUES ( 2,'M')INSERTINTOTableFOREIGN( ID ,Gender )VALUES ( 3,'N')CHAR( 1)GO_Genders )ALTER TABLE TableAssert ADD CONSTRAINT fk_Tab2 FOREIGN KEY (REFERENCES TableFOREIGN ( ID )GO同樣，我們使用估計執行計劃測試一下 INSERT 語句：

8、語句如下：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' )會看到兩個結果，第一個是語句，不用關，我們看第二個結果：1. | -Assert(WHERE:(CASE WHEN NOT Pass10

9、09 AND Expr1008 IS NULL THEN (0) ELSE NULL END)2. | -Nested Loops(Left Semi Join, PASSTHRU:(tempdb.dbo.TableAssert.ID_Genders IS NULL), OUTER REFERENCES:(tempdb.dbo.TableAssert.ID_Genders), DEFINE:(Expr1008 = PROBE VALUE)3. | -Assert(WHERE:(CASE WHENtempdb.dbo.TableAssert.Gender<>'F' A

10、ND tempdb.dbo.TableAssert.Gender<>'M' THEN (0) ELSE NULL END)4. | -TableInsert(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)5. | -Clustere

11、d IndexSeek(OBJECT:(tempdb.dbo.TableFOREIGN.PK_TableFOR_3214E C27173876EA),SEEK:(tempdb.dbo.TableFOREIGN.ID=tempdb.dbo.TableA ssert.ID_Genders) ORDERED FORWARD)這個結果內容較多可能不直觀，讀者可以執行測試看結果。可以看到里面有兩次 Assert，自下而上地閱讀，第一個 Assert(也就是 下面那個， 針對于圖形化界面而言是右邊那個， 因為圖形化執行計劃是從右到 左地閱讀)是前面用于 CHECK 約束的，如果返回 0 則繼續運行語句，否

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

13、值，如果沒有找到 INSERT 中傳入的值， 斷言會返回一個 異常。如果 ID_Genders的值為 NULL ，那么 SQL Server不能返回異常，而是返 回“0并”繼續運行語句。如果運行上面的 INSERT 語句， SQL Server會返回異 常，因為值為 'X，'違反了 check 約束：但是如果把 X 換成 F 再運行，還是會報錯，因為違反了外鍵約束：但是當把 4換成 NULL 或1或2或3之后，再運行插入語句，就不會產 生異常：1.1.1.5 斷言與子查詢：斷言操作符同樣可以用于檢查子查詢， 對于標量子查詢不能返回多個值， 但是有時候寫法和數據的變動會引發多值錯

14、誤。 此時斷言扮演著校驗標量子 查詢是否返回一個值的角色。下面來看看這兩個語句：INSERT INTO TableAssert ( ID , Gender ) VALUES ( SELECT ID FROMTableAssert ), 'F' )INSERT INTO TableAssert( ID , Gender ) VALUES ( SELECT ID FROMTableAssert ), 'F' )用上面的方法查看一下執行計劃：SET SHOWPLAN_TEXT ONGOTableAssert ), 'F' )INSERT INTO Ta

15、bleAssert ( ID , Gender ) VALUES ( SELECT ID FROM TableAssert ), 'F' )觀察語句大概可以知道發生什么情況，第一個 insert 會成功(除非你已 經修改過里面的數據)，因為 VALUES 中的 SELECT 部分只返回一個值， 但是第二個 INSERT 由于 VALUES 中的 SELECT 有兩個值(第一個 INSERT 加入的)，所以會報錯。結果如下：1. | -Assert(WHERE:(Expr1013)2. | -Compute Scalar(DEFINE:(Expr1013=CASEWHENtemp

16、db.dbo.TableAssert.Gender<>'F' ANDtempdb.dbo.TableAssert.Gender<>'M' THEN (0) ELSE NULL END)3. | -TableInsert(OBJECT:(tempdb.dbo.TableAssert),SET:(tempdb.dbo.TableAssert.ID =Expr1009,tempdb.dbo.TableAssert.Gender =Expr1010,tempdb.dbo.TableAssert.ID_Genders = NULL)4. | -To

17、p(TOP EXPRESSION:(1)5.| -ComputeScalar(DEFINE:(Expr1009=Expr1012, Expr1010='F')6. | -Nested Loops(LeftOuter Join)7. | -ConstantScan8. | -Assert(WHERE:(CASE WHENExpr1011>(1) THEN (0) ELSE NULL END)9.| -StreamAggregate(DEFINE:(Expr1011=Count(*),Expr1012=ANY(te mpdb.dbo.TableAssert.ID)10.| -

18、TableScan(OBJECT:(tempdb.dbo.TableAssert)注意最內層的 Assert：可以看到 SQL Server創建一個 StreamAggregat(e 流匯聚， 可從預估執行 計劃中看到其解釋， 后續會專門介紹) 去計算子查詢會返回多少數據， 然后 把這個值傳遞給斷言用于檢測。作為已經商業化二十幾年的產品，其核心(查詢優化器)已經經過了很 多年的積累和改進，高版本的 SQL Server(如 2008 R2 及以上版本，這個沒 有絕對標準)，會對語句和表結構的當前情況來判斷是否需要使用 “斷言， Assert 操”作符。比如：INSERT INTO TableA

19、ssert ( ID , Gender ) VALUES ( SELECT ID FROMTableAssert WHERE ID = 1 ), 'F' )INSERT INTO TableAssert ( ID , Gender ) VALUES ( SELECT TOP 1 ID FROMTableAssert),'F' )先不執行，開啟估計執行計劃再看圖形化界面，可以看到如下結果：因為優化器檢測到第二個語句里面包含了 TOP 1，僅返回一行數據，所 以沒有必要引入斷言來檢測。1.1.1.6 總結：到這里為止， 對這個操作符的介紹已經完畢， 下一篇會介紹串聯

20、操作符。 對于這個斷言操作符，我們需要知道它是用來 “驗證 ”某些條件，但是每個操 作符的引入都必將帶來一定的開銷，可是這些操作符的引入又是必須的，因 為需要它們完成一些任務。如果需要改進，不妨先看看它是用來檢驗什么， 比如上面提到的子查詢，可以通過使用 TOP 1、添加唯一約束等方式來減少 這種校驗。但是所有改進都應該做充分的測試和論證。1.1.2 執行計劃操作符詳解串聯 (Concatenation )1.1.2.1 前言：本文開始講述另外一個操作符串聯 (Concatenation)，讀者可以根據這個詞(中英文均可)先幻想一下是干嘛的。其實還是挺直觀，就是把東西 連起來，那么下面我們來看

21、看到底連什么？怎么連？什么時候連？1.1.2.2 簡介：串聯操作符既是物理操作符，也是邏輯操作符，在中文版 SQL Server 的圖形化執行計劃中稱為“串聯”，在其 他格式及英文版本中稱為 “ Concatenation。其”圖標為：，它掃描多個輸入并返回每個掃描的行。通常用于實現 T-SQL 中的 UNION ALL 。它可以有多個輸入，但只有一個輸 出，就如多個集合 UNION ALL 一樣，最終返回一個結果集，注意這里一直 使用“集合 /集”，關系數據庫是基于集合論的， 所以使用關系數據庫時要以集 合的思維去考慮問題。在執行計劃中的每個操作符， 都要實現三個方法 /函數：Init()

22、、GetNext() 和 Close()。前面說了，串聯操作符是其中一種可以接受多個輸入的操作符， 這些輸入會在 Init() 方法中處理。在 Init() 方法中，串聯初始化然后建立所需的數據結構。然后在運行 GetNext()方法讀取輸入集中的第一行及后續行， 直到把輸入集合里面的所有 數據讀取完畢為止。1.1.2.3 環境搭建：下面創建一個測試表并循環插入 10000 行數據USE tempdbGOIF OBJECT_ID ( 'TEST' , 'U' ) IS NOT NULLDROP TABLE TESTGOCREATE TABLE Test (ID

23、INT Identity ( 1, 1 ) PRIMARY KEY, Nome VARCHAR( 250 ) DEFAULT NewID ()GOSET NOCOUNT ONGOINSERT INTO Test DEFAULTVALUESGO 100001.1.2.4 串聯演示：，那么現在就來看前面提到，串聯主要用于實現 T-SQL 的 UNION ALL 看 UNION ALL 的情況：開啟實際執行計劃并運行下面語句：SELECT *FROMTESTUNION ALLSELECT *FROMTESTUNION ALLSELECT *FROMTESTSELECT * FROM TEST執行計劃

24、如下：如果使用 SET SHOWPLAN_TEXT ON 來查看的話可以看到如下結果：這個圖的含義是把 4 個“ Clustered Index Scan 的結”果塞到一個結果集，然后調用 Init() 和 GetNext() 方法去遍歷這些數據，然后輸出。另外需要 說明的是這個操作符是根據 T-SQL 中結果集的出現順序來處理的， 為了證明 這個想法，我們來改寫一下語句：SET SHOWPLAN_TEXT ONGOSELECT * FROM TESTWHERE ID <100UNION ALLSELECT *FROM TESTWHERE IDBETWEEN 101AND 1000UNI

25、ON ALLSELECT *FROM TESTWHERE IDBETWEEN 1001AND 5000SELECTFROM TESTWHERE ID >5001然后看看輸出：對比一下參數可得每個 Clustered Index Seek 的順序和語句的出現順序 是一致的。另外讀者可能留意到每行最后的 ORDERED FORWARD ，其含 義是掃描索引的順序是按照聚集索引的順序并向前掃描。1.1.2.5 總結：本文主要演示了串聯操作符的情況，并且主要以 T-SQL 中的 UNION ALL 來觸發。由于目前沒有任何資料顯示是否僅 UNION ALL 才會使用，所 以這里也不做絕對的判斷，

26、讀者只需要知道這個操作符的含義、常見情景即 可。另外讀者可以使用 UNION 來檢查執行計劃，實際上 UNION 是不用串 聯的，因為它本質上需要去重，所以使用不同的操作符來實現，比如 Merge Join ，在后續再介紹。1.1.3 執行計劃操作符詳解計算標量 (ComputeScalar)1.1.3.1 前言：第三個常見的操作符計算標量 (Compute Scalar) 。這個操作符的名字 比較直觀 進行一個標量計算并返回計算值。 官方說明：Compute Scalar 運算符通過對表達式求值來生成計算標量值。 該值可以返回給用戶、 在查詢 中的其他位置引用或二者皆可。 例如，在篩選謂詞或

27、聯接謂詞中就會出現二 者皆可的情況。該操作符的圖標為： ，它既是一個邏輯操作符，也是一個物理 操作符。這個操作符可能不容易引起用戶注意， 因為一般我們看執行計劃是 因為語句有問題， 而有問題的語句又通常是比較復雜或混亂的， 這些語句生 成的執行計劃往往也非常復雜。 相對于整個執行計劃來說， 這個操作符通常 是比較小開銷的。但是這個操作符之所以重要或常見，是因為它通常是由于游標處理 或其他一些大范圍查找引起的，這些操作可能在 CPU 存在壓力時變得雪上 加霜。1.1.3.2 演示：使用 TempDB 做測試是一個不錯的選擇，簡單重啟一下 SQL 服務 即可清空過去的操作，不過如果你發現重啟后還在

28、，那不妨檢查一下是否建 到 Model 數據庫或者設置為啟動時運行。下面代碼在 TempDB 中創建一個 表，插入 10000 行數據后，循環 100 次進行數據檢查：USE tempdbGOCREATE TABLE test ( ID Int Identity ( 1, 1) PRIMARY KEY,Name VarChar ( 250 ) DEFAULT NewID ()GOSET NOCOUNT ONGOINSERT INTO test DEFAULT VALUESGO 10000- 循環插入行數據GO- 下面代碼循環 100 次，判斷是否存在某個 IDDECLARE I IntSET

29、I = 0WHILE I < 100BEGINIF EXISTS( SELECT ID FROM test WHERE ID = I)BEGINPRINT ' 存在這個 ID'ENDSET I = I + 1END看一下圖形化執行計劃：截圖中紅框部分表明使用了計算標量操作符，使用前面的方法，檢查文本化執行計劃：SET SHOWPLAN_TEXT ONGODECLARE I IntSET I = 0WHILE I < 100BEGINIF EXISTS( SELECT ID FROM test WHERE ID = I)BEGINPRINT ' 存在這個 ID

30、'ENDSET I = I + 1END可以看到執行計劃使用計算標量操作符來檢查嵌套循環( Nested Loop )是否返回了值，也就是說用于實現 IF EXISTS 操作。 如果使用 Profiler 來抓取信息，記住一下 CPU 開銷：下面改寫一下語句來避免這個操作符：DECLARE I Int , Var IntSET I = 0WHILE I < 100BEGINSELECT Var = ID FROM test WHERE ID = IIF ROWCOUNT> 0BEGINPRINT ' 存在這個 ID'ENDSET I = I + 1ENDGO

31、再看看圖形化執行計劃：及 Profiler 信息：如果再檢查文本化執行計劃就可以看到只有一個操作符：1. | -Clustered IndexSeek(OBJECT:(tempdb.dbo.test.PK_test_3214EC27D8827737), SEEK:(tempdb.dbo.test.ID=I) ORDERED FORWARD)對比 Profiler 中的數據， 沒有使用計算標量的執行計劃消耗更少的 CPU 和運行時間去完成結果，這里主要是演示計算標量，所以不對寫法做更深入 的研究。但是從寫法上看， 使用了rowcount 函數替代 IF EXISTS ，有時 候會有一定的幫助，當

32、然，并不是絕對的。如果你覺得是數據量的原因，不妨再看看下面的腳本：DECLARE Tab TABLE ( ID SmallInt PRIMARY KEY)SELECT 'A' + 'B' FROM Tab然后看看圖形化執行計劃：和文本化執行計劃：2.1. | -Compute Scalar(DEFINE:(Expr1002='A - B')| -Clustered Index Scan(OBJECT:(Tab)這個語句只是簡單地進行字符串拼接，但是也使用了計算標量運算符， 原因可以查看執行計劃的解釋：1.1.3.3 總結：正如一直以來的解釋，每個

33、操作符的出現都有其原因和作用，并不 能簡單地下定論這個操作符是好還是壞， 但是某些操作符確實意味著性能問 題，所以如果精力允許，也應該對常見的操作符進行一定程度的研究。當發 現某個低效查詢中出現這個操作符時，不妨想想其原因，并嘗試是否能進行 優化，優化的原則則是根據其含義而定，既然這個操作符是根據現有值計算 新值，那么我們的核心方案應該是減少這種操作的數據量或者預先計算新 值。總的而言，具體情況具體分析。1.1.4 根據執行計劃優化性能理解哈希、合并、嵌套循環連接策略1.1.4.1 前言：對于性能優化，需要集中處理以下的問題：1、為你的環境創建性能基線。2、監控現在的性能并發現瓶頸。3、解決瓶

34、頸以便得到更好的性能。一個預估執行計劃是描述查詢將會如何執行的一個藍圖， 而一個實際執 行計劃就是一個查詢執行時實際發生的鏡像。通過對比兩個執行計劃，可以 發現查詢是否真的按照預估執行計劃來執行。在執行計劃中，有一些非常重要的操作符需要清楚：1、Join 策略：SQLServer 有 3 種策略 哈希、合并、嵌套循環。每 種策略都有其優缺點，本章將講述這部分。2、掃描和查找是 SQLServer 用于讀取數據的兩種方式，這兩種方式在 性能優化中是核心概念。將會在下一篇中講述。3、鍵查找有時候會成為主要的性能問題。因為存儲引起必須從非聚集 索引中跳到聚集索引，一邊找到非聚集索引中的非鍵值列的值。

35、這樣的行為 通常很耗時間。1.1.4.2 理解哈希、合并、嵌套循環連接策略SQLServer 提供了 3 中 JOIN 的策略，它們沒有絕對的好和壞之分。1、哈希（ Hash Join ）：SQLServer 選擇哈希關聯作為物理操作符，一 邊對于大容量數據，且未排序或者沒有索引時進行查詢。兩個進程關聯起來 進行哈希關聯，它們為【建立】和【探測】 ，在【建立】進程中，會從建立 輸入（即 join 的左表中，但是可能這個左表會在優化過程中交換位置，使得 不一定就是實際上的左表。 ）讀取所有行，然后在內存中創建一個符合關聯 條件的哈希表。在【探測】進程中，會從探測表（輸入的右表）中讀取所有的行，并

36、根據關聯條件，與之前創建的內存哈希表匹配2、合并( Merge Join )：如果關聯表中已經排序， SQLServer 會選擇 合并關聯。合并關聯要求關聯條件中最少有一個是已經被排序了的。如果數 據量不大的時候，這比哈希關聯更加有效，它并不是重負荷關聯的方式。3、嵌套循環( Nested Loop )：在最少兩個結果集中，使用嵌套循環會 比較有效，這兩個結果集中，作為外部表的集合要小，而內部循環結果集具 有有效的索引。這種方式不適用于大結果集。1.1.4.3 準備工作：下面將創建兩個表，然后看看各種關聯方式的執行計劃：USE AdventureWorksGOIF OBJECT_ID ( &#

37、39;SalesOrdHeaderDemo' ) IS NOT NULLBEGINDROP TABLE SalesOrdHeaderDemoENDGOIF OBJECT_ID ( 'SalesOrdDetailDemo' ) IS NOT NULLBEGINDROP TABLE SalesOrdDetailDemoENDGOSELECT*INTOSalesOrdHeaderDemoFROMSales. SalesOrderHeaderGOSELECT*INTOSalesOrdDetailDemoFROMSales. SalesOrderDetailGO1.1.4.4 步

38、驟：1、執行一下查詢，并開啟執行計劃( Ctrl+M )：SELECT sh .*FROM SalesOrdDetailDemo AS sdINNER JOIN SalesOrdHeaderDemo AS sh ON sh . salesorderIDsd . salesorderidGO2、然后從執行計劃截圖中可以看到使用了哈希連接：3、現在先創建唯一的聚集索引在兩個表中：CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderIDON salesordheaderdemo ( SalesOrderID )ONGOCR

39、EATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderIDSalesOrdDetailDemo ( SalesOrderID , SalesOrderDetailIDGO4、再次執行步驟 1 的語句：5、截圖是第二次執行的執行計劃，可以發現變成了合并連接，并且表掃描 變成了聚集索引掃描：6、 現在來看看嵌套循環關聯，在上面的查詢中添加 where 條件來限定查詢 的結果集：SELECT sh .*FROM SalesOrdDetailDemoAS sdAS sh ON sh . salesorderIDINNER JOIN SalesOrd

40、HeaderDemosd . salesorderid= 43659WHERE sh . salesorderidGO7、 從執行結果中看到現在關聯變成了嵌套循環：1.1.4.5 分析：前面已經提到，哈希關聯工作在大數據量且關聯字段沒有排序的關 聯中。所以在步驟 1 中，由于沒有索引或者預先排序， 數據的關聯會使用哈 希關聯。在步驟 3 中，創建了一個唯一的聚集索引，所以表已經通過聚集索 引排序了，此時優化器會選擇合并關聯。在步驟 6 中，由于使用了 where 條件限制數據集的大小，同時由于 已經排序，所以使用了嵌套循環關聯。每一種關聯方法都有其優缺點，視乎如何優化而已。有時候哈希關 聯有其

41、非常重要的作用， 但是如果可以， 強烈建議每個表都應該有一個唯一 的聚集索引，一邊使用合并關聯，如果不可以，千萬別嘗試使用 OPTION 提示符來把關聯改成合并或者嵌套循環， 這可能會降低性能。 而嵌套循環僅 在小結果集的時候運行的最好。1.1.5 根據執行計劃優化性能查找表/索引掃描1.1.5.1 前言：在絕大部分情況下， 特別是從一個大表中返回少量數據時， 表掃描或者 索引掃描并不是一種高效的方式。這些必須找出來并解決它們從而提高性 能，因為掃描將遍歷每一行，查找符合條件的數據，然后返回結果。這種處 理是相當耗時耗資源的。在性能優化過程中，一般集中于：1、CPU2、Network3、磁盤

42、IO 而掃描操作會增加這三種資源的開銷1.1.5.2 準備工作：面將創建兩個表來查看不同的物理關聯操作的不同影響。 創建腳本已經在本系列的第 篇中給出，這里不再顯示。1.1.5.3步驟：1、打開執行計劃并運行下面查詢：SELECT sh .*FROM SalesOrdDetailDemoAS sdAS sh ON sh . salesorderIDINNER JOIN SalesOrdHeaderDemosd . salesorderidWHERE sh . orderdate'2004-07-31 00:00:00.000'其中執行計劃建議了丟失GO從執行計劃的截圖可以看到兩

43、表均使用了表掃描，索引。此時應該考慮是否有必要創建：2、為了避免表掃描，創建一個聚集索引在表 SalesOrdHeaderDemo中： CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON salesordheaderdemo( SalesOrderID )GO3、再次運行步驟 1 中的查詢，看看執行計劃是否已經移除了表掃描：4、上圖中可以看到創建了聚集索引的表已經變成了聚集索引掃描， 但是未 創建的就還是表掃描， 觀察聚集索引掃描， 它只是把表掃描換成了聚集索引 掃描，所以沒有很大的性能提升。5、現在繼續

44、把第二個表的表掃描去掉， 通過創建在這個表上的唯一聚集索 引：CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderID ON SalesOrdDetailDemo ( SalesOrderID , SalesOrderDetailID )GO6、再次執行查詢。7、下面截圖中可以看到表掃描已經徹底移除：1.1.5.4 分析：在深入討論之前，首選需要澄清的是，掃描并不總是壞的，而查找 并不總是好的，但是在絕大部分情況下，特別是在大表中返回少量數據 時，查找會有更好的性能表現。同樣，并不總是有方法在每個查詢中移 除掃描操作。如果查詢的性能問

45、題是因為掃描，那么移除掃描操作會更 好，否則，看看是否有什么改變方式去提高性能。在第一步中，因為兩表均沒有索引，所以優化器只能選擇掃描來查找 數據。在第三步中，已經創建了一個聚集索引在 SalesOrdHeaderDemo表上， 表掃描變成了聚集索引掃描，聚集索引查找是我們希望得到的結果，但是因 為我們沒有什么謂詞在第一個表上， 所以只能掃描整個聚集索引來代替掃描 整個表。在第六步中，在第二個表也創建了聚集索引，且有一個謂詞在這個表 上，所以出現了聚集索引查找，而不是聚集索引掃描。1.1.6 根據執行計劃優化性能鍵值查找1.1.6.1 前言：本文為本系列最后一篇，介紹鍵值查找的相關知識。 鍵值

46、查找是具有聚集索引的表上的一個書簽查找，鍵值查找用于SQLServer 查詢一些非鍵值列的數據。 使用非聚集索引的查詢不會有鍵值查 找，但是所有鍵值查找會伴隨非聚集索引出現。 這里特別提醒的是鍵值查找 總是伴有嵌套循環關聯。1.1.6.2 準備工作：下面將創建一個表，通過執行計劃看看鍵值查找的不同效果。為了產生鍵值 查找，需要兩件事情：1、 聚集索引2、 非聚集索引當你在非聚集索引鍵值上有謂詞時， 查詢的字段又不全部包含在非聚集索引 上，需要通過聚集索引去查找，此時會產生鍵值查找。執行下面操作產生測 試表：USE AdventureWorksGOIF OBJECT_ID ( 'Sale

47、sOrdDetailDemo' ) IS NOT NULLBEGINDROP TABLE SalesOrdDetailDemoENDGOSELECT *INTO SalesOrdDetailDemoFROM Sales . SalesOrderDetailGO1.1.6.3 步驟：1、 在測試表 SalesOrdDetailDemo上創建一個聚集索引和一個非聚集索引： CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderID ON SalesOrdDetailDemo ( SalesOrderID , SalesOrderDe

48、tailID )GOCREATE NONCLUSTERED INDEXidx_non_clust_SalesOrdDetailDemo_ModifiedDate ONSalesOrdDetailDemo ( ModifiedDate )GO2、 執行下面的查詢，并開啟實際執行計劃：SELECT ModifiedDateFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO4、對上面的查詢語句進行少許的改動，多查詢幾列：SELECT ModifiedDate ,SalesOrderID ,Sa

49、lesOrderDetailIDFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO5、再檢查執行計劃：它的文本化執行計劃如下：StmtText|-IndexSeek(OBJECT:(AdventureWorks.dbo.SalesOrdDetailDemo.idx_non_clust_SalesOrdDetailDemo_ModifiedDate),SEEK:(AdventureWorks.dbo.SalesOrdDetailDemo.ModifiedDate=CONVERT_IMPLI

50、CIT(datetime,1,0) ORDERED FORWARD)6、在上面的查詢中添加的列均包含在聚集索引和非聚集索引中，現在增加 更多的列：SELECT ModifiedDate ,SalesOrderID ,SalesOrderDetailID ,ProductID ,UnitPriceFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO7、查看執行計劃，此時出現了兩個新的操作符 鍵值查找和嵌套循環， 如圖：StmtText|-Nested Loops(Inner Join, OU

51、TER REFERENCES:(AdventureWorks.dbo.SalesOrdDetailDemo.SalesOrderID, AdventureWorks.dbo.SalesOrdDetailDemo.SalesOrderDetailID, Expr1004) WITH UNORDERED PREFETCH)|-Index Seek(OBJECT:(AdventureWorks.dbo.SalesOrdDetailDemo.idx_non_clust_SalesOrd DetailDemo_ModifiedDate), SEEK:(AdventureWorks.dbo.SalesOr

52、dDetailDemo.ModifiedDate='2004-07-31 00:00:00.000') ORDERED FORWARD)|-Clustered Index Seek(OBJECT:(AdventureWorks.dbo.SalesOrdDetailDemo.idx_SalesDetail_SalesOr derID),SEEK:(AdventureWorks.dbo.SalesOrdDetailDemo.SalesOrderID=AdventureWorks .dbo.SalesOrdDetailDemo.SalesOrderID AND AdventureWo8、同時可以看到在鍵值查找上的百分比相當高， 此時先試一下使用 hint 來 改變優化器的行為：SELECT ModifiedDate ,SalesOrderID ,SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemo WITH( INDEX =idx_SalesDetail