SQL執行計畫解析（2）- 基本查詢的圖形執行計畫（上）

某種程度上，學習閱讀圖形執行計畫和學習一門新語言很類似。不同之處是這門語言是基於表徵圖的，而且單詞（表徵圖）非常少。每個表徵圖代表了一個操作符，本章裡，“表徵圖”和“操作符”可以互換地使用。

前邊一章我們遇到了兩個操作符（select和table scan），實際上總共有79個，好在我們不需要全部學會才能開始閱讀執行計畫，大多數查詢只用到了其中的一小部分。如果遇到了我們沒有提到的表徵圖，參閱http://msdn2.microsoft.com/en-us/library/ms175913.aspx

圖形執行計畫裡有4中不同類型的操作符：

• 邏輯和物理操作符（logical and physical operators):藍色表徵圖，代表查詢執行或DML聲明
• 並行物理操作符（parallelism physical operators):也是藍色表徵圖，代表並行操作。某種意義上，它是邏輯和物理操作符的子集，之所以單獨考慮是因為它承擔是完全不同等級的執行計畫分析。
• 遊標操作符（Cursor operators）：黃色表徵圖，代表T-Sql遊標操作。
• 語言元素（Language elements）：綠色表徵圖，代表T-Sql語言元素，如Assign、Declare、If、Select、While等。

本章我們主要關注邏輯和物理操作符包括並行物理操作符。

我們需要瞭解操作符的行為。有些操作符，sort、hash match、hash join等，它們需要一定的記憶體才能執行,因此，如果查詢裡有這種操作符，那麼就可能需要等待可用的記憶體，對效能產生負面影響。絕大多數操作符都以阻塞方式或非阻塞方式運行。非阻塞操作符在接收到輸入資料的同時就建立輸出資料，阻塞式操作符必須等待所有的輸入到達後才能產生輸出資料。

 

單表查詢

1. 叢集索引掃描（Clustered Index Scan）

SELECT*FROM Person.Contact

下邊是實際的執行計畫

圖2-1

我們可以看到，這裡執行了一個叢集索引掃描操作來擷取資料，使用的叢集索引是PK_Contact_ContactID，得到了19972行資料。

Sql Server裡的索引儲存在一個B-Tree裡，而叢集索引不只是像常規索引那樣儲存了key structure，還儲存了了資料並排序。這也是一個表只能有一個叢集索引的主要原因。

叢集索引掃描和全表掃描（table scan）概念上基本相同，整個索引或大多數的索引都被逐行地遍曆來確定哪些資料是查詢需要的。

如本例所示，索引掃描通常發生在最佳化器認為需要返回的行數太多，與其使用索引裡邊的key還不如簡單地掃描所有資料來的快的情境。

 

2.叢集索引尋找（clustered index seek)

給上邊的查詢加個where子句

SELECT*FROM Person.Contact where ContactId =1

我們就得到下邊的執行計畫

圖2-2

索引尋找完全不同於索引掃描，索引掃描會遍曆所有的行來找需要的資料，索引尋找不論是叢集索引還是非叢集索引，發生在最佳化器能夠定位索引並且通過索引擷取所需資料的情境。因此，它需要告訴儲存引擎通過指定的索引的key來尋找value。索引尋找操作類似於從書的目錄裡先找到正確的頁數，以便快速找到單詞。叢集索引尋找還有另外的好處，它不僅比索引掃描更加成本低廉，而且不需要額外的步驟去擷取資料，因為資料就儲存在索引裡。另注意，Ordered屬性這裡為True。

3.非叢集索引尋找（Non-Clustered Index Seek）

SELECT ContactID
FROM Person.Contact
WHERE EmailAddress LIKE'sab%'

執行計畫如,使用了索引IX_Contact_EmailAddress.

圖2-3

注:非叢集索引尋找的表徵圖名字弄錯了,寫成了Index Seek,不過這沒什麼大的影響.

和叢集索引尋找一樣的是,非叢集索引尋找也使用來尋找那些行需要返回,不一樣的是,非叢集索引尋找使用的是非叢集索引,最佳化器可能在非叢集索引裡找到所需的全部資料,也可能還需要從叢集索引裡尋找資料,這個額外的IO操作會輕微降低效能,詳情如下節.

 

4.鍵尋找(Key lookup)

我們稍微修改下上邊的查詢,取其中的多個列.

SELECT ContactID,
    LastName,
    Phone
FROM Person.Contact
WHERE EmailAddress LIKE'sab%'

執行計畫如下

ps：我的資料庫版本比較舊，沒有Key Lookup而是一個lookup屬性為True的叢集索引，下邊這張圖還有2-6是從書裡截出來的，看起來模糊一點

圖2-4

我們終於見到了第一個有多個操作的計劃.從右到坐,從上到下,第一個是對IX_Contact_EmailAddress的索引尋找操作.這是一個值不唯一的、非聚集的索引，對我們這個查詢來說也不是覆蓋式（non-covering）。所謂非覆蓋式意思就是說，索引裡沒有包含查詢所需的所有列，必須再從叢集索引裡擷取資料。我們可以從Index Seek的output list裡看到，裡邊有EmailAddress和ContactId列。

 

圖2-5

然後Key lookup使用key的值從叢集索引PK_Contact_ContactID裡找出相應的行，它的output list是LastName和Phone列。如

 

圖2-6

一個Key lookup就是使用叢集索引對錶進行書籤尋找（bookmark lookup）。Key lookup的出現表示查詢能夠通過覆蓋式索引獲得效能提升。如果索引是覆蓋式的，那麼Key lookup就能避免掉。伴隨Key lookup出現的一定有一個嵌套迴圈串連（Nested loop join）操作，用於將兩個操作的結果組合起來。

 

圖2-7

嵌套迴圈串連是一個標準類型的串連，它的出現並不意味著效能問題。在我們的例子裡，由於有Key lookup，那麼就需要嵌套迴圈串連將Index Seek的行和Key lookup的行組合在一起。如果沒有Key lookup，那麼嵌套迴圈串連也就不會出現。

 

5.全表掃描（Table Scan）

顧名思義，全表掃描就是漸進式掃描表來擷取所需的資料。

SELECT*
FROM[dbo].[DatabaseLog]

計劃

 

圖2-8

全表掃描發生有幾個原因，通常是因為沒有可用的索引，最佳化器不得不檢索所有的行。另外個常見的原因是返回表的所有行，如本例所示，不論有沒有索引，掃描全部行通常都會比使用索引尋找每一行要快些。還有個原因是表裡的行很少，最佳化器認為掃描所有行比使用索引要快。

 

6.RID尋找（RID lookup）

如果我們給上邊的查詢在主鍵列上指定過濾條件，那麼就得到了一個使用RID尋找的執行計畫。

SELECT*
FROM[dbo].[DatabaseLog]
WHERE DatabaseLogID =1

執行計畫

 

圖2-9

為了返回結果，最佳化器首先首先在主鍵上執行索引尋找（Index Seek），評鑑行是否符合where條件，但是索引裡並不包含所需的全部資料。看上邊的Index Seek的tool tips2-10，我們就會發現output list裡邊的Bmk1000，這個告訴我們，索引尋找實際上是書籤尋找的一部分。然後最佳化器執行RID尋找，使用行標識符找到需要返回的行，RID尋找就是一種書籤尋找，發生在heap table（沒有叢集索引的表）上。換句話說，由於表沒有叢集索引，那麼就必須使用連結到堆索引的一個行標識符。這就增加了磁碟IO，因為這需要執行兩個不同的操作，讓後通過嵌套迴圈組合在一起。

 

圖2-10

RID尋找的tool hint如

 

圖2-11

我們又看到了Bmk1000，這次是在Seek Predicates部裡邊。這就意味著，查詢計劃使用了書籤尋找（我們這個例子裡就是RID尋找）。我們這個裡只需要尋找1行，效能上看不是什麼大問題，如果RID尋找返回很多行，那麼就需要仔細考慮如何降低磁碟IO了，重寫查詢或者添加叢集索引或者使用覆蓋式索引等。