SQL Server對於每一個送來的查詢必定經過下面四個步驟：
- •解析：有效的語法會被解析成樹形表示式
- •綁定：針對表示式會做驗證，比對資料庫的表與列以確保查詢有效
- •優化：查詢最佳化工具會評估各種可能的執行計畫，找出一個最優的
- 執行：系統執行，回傳結果
那查詢最佳化工具是如何評估執行計畫的呢？ 其實它是以成本為依據，會選出處理成本最低的！而影響執行計畫成本的因素有很多，主要有以下四點：
- –Predicates(述詞)
其他你也應該知道的：
- SARG( 查詢的條件)
- •Constant Expressions (摺疊運算式)
- •Non-foldable Expressions (不可摺疊運算式)
- •哪些狀況下，會無法估計基數呢？
- •Contradiction Detections (矛盾探測)
- •Foreign Keys
- •Check Constraints
- 以上這些因素你若都能了解，相信能幫助你設計出較有效率的查詢！
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Predicates(述詞)
述詞是評估為 TRUE、FALSE 或 UNKNOWN 的運算式，用於 WHERE 子句和 HAVING 子句的搜尋條件中、FROM 子句的聯結條件中，簡單來說就是篩選的條件，了解這個作什呢？因為查詢最佳化工具會用分析述詞來預測回傳的筆數，我們直接看例子吧。
-- 先建立測試用資料表
USE tempdb
GO
IF OBJECT_ID('TestTable') IS NOT NULL
CREATE TABLE TestTable (ID INT,
BEGIN
DROP TABLE TestTable
END
GOCREATE TABLE TestTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- 插入十萬筆資料
INSERT INTO TestTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'John',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Taipei
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Taichung
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Tainan
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Kaohsiung
ELSE 'PingTung' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
以下的例子我為了每次都重編譯執行計畫，查詢後面有接查詢提示OPTION(RECOMPILE)喔，這是為了示範用的。
--Query 1
OPTION(RECOMPILE)
GO
--Query 2
SELECT ID, FirstName FROM TestTable WHERE City = 'Taipei
OPTION(RECOMPILE)
GO
檢查執行計畫吧，如下圖，看執行計畫的順序一般都是從右看到左，從上看到下
兩個查詢我們首先看到的都是Table Scan(資料表掃描)運算子，資料表掃描運算子最常出現在資料表上沒有建立有用的索引，就像查詢2，雖有篩選的條件WHERE City = 'Taipei'，但沒有任何索引可用，查詢最佳化工具只能讀取資料表中的所有資料列，並擷取符合篩選條件的資料，另一個就是查詢要求回傳全部的資料列，如查詢1，從執行計畫看相對批次的成本兩個查詢是一樣的，各為50%，其實若你開啟磁碟IO統計資訊來看的話，邏輯讀取次數也是一樣的，表示成本一樣，接著我們將滑鼠游標移到Table Scan運算子上看工具提示吧，如下圖
我把查詢1的工具提示放在左邊，查詢2的放在右邊，方便兩者比較，我們要注意的是Estimated Number of Rows(估計的資料列數目)與Actual Number of Rows(實際的資料列數目)，右邊的估計與實際的都是100000，左邊的則都是100，而兩個查詢就差在有沒有篩選條件，仔細再看右邊的，下面Argument是不是出現到Predicates(述詞)為[tempdb].[dbo].[TestTable].[City]='Taipei'啊，不就是查詢2篩選的條件！右邊出現述詞的，儲存引擎會比對所有滿足述詞的資料列，此種情況是不會使用索引的喔，估計的資料列數目就是靠述詞中篩選的值來預估有多少筆回傳，而沒有述詞的，就只能有總筆數來當預估值囉。
接著我們建立叢集索引來看對執行計畫的影響
-- 建立叢集索引
-- 建立叢集索引
CREATE CLUSTERED INDEX [IX_TestTable_ID] ON [dbo].[TestTable]
(
[ID] ASC
)
GO
--一樣我們一次執行剛剛的兩個查詢
--Query 1
SELECT ID, FirstName FROM TestTable
OPTION(RECOMPILE)
GO
--Query 2
SELECT ID, FirstName FROM TestTable WHERE City = 'Taipei
OPTION(RECOMPILE)
GO
再檢查一次執行計畫吧，如下圖
再檢查一次執行計畫吧，如下圖
有沒有發現我們建立叢集索引後，對成本似乎沒什影響，還是各50％，但你會發現Table Scan轉換成Clustered Index Scan(叢集索引掃描)，其實若你開啟磁碟IO統計資訊來看的話，邏輯讀取次數與之前未建立叢集索引之前是一模一樣的，由此也可看出為什麼別人說Table Scan與Clustered Index Scan其實是一樣的。
一樣繼續看Clustered Index Scan運算子的工具提示吧，如下圖，跟前面的Table Scan沒什兩樣。
目前資料表上雖有建立叢集索引，但對查詢2來說是無用的索引，所以建立這索引並不能改善查詢2的效能，但是若我們在篩選條件欄位上建立非叢集索引呢？來看看會發生甚麼事。
-- 替篩選欄位建立非叢集索引
CREATE NONCLUSTERED INDEX [IX_TestTable_City] ON [dbo].[TestTable]
(
[City] ASC
)
GO
--一樣我們一次執行剛剛的兩個查詢
--Query 1
SELECT ID, FirstName FROM TestTable
OPTION(RECOMPILE)
GO
--Query 2
SELECT ID, FirstName FROM TestTable WHERE City = 'Taipei
OPTION(RECOMPILE)
GO
再檢查一次執行計畫吧，如下圖
再檢查一次執行計畫吧，如下圖
看查詢1依然沒有變化，其實若查詢若沒有任何篩選條件，或是說查詢的資料表沒有任何索引，查詢最佳化工具就不會去花時間去計算哪個執行計畫較好囉，就會產生trivial plan，沒有價值的計劃，可能是覺得花時間計算結果不會比較好；而查詢若是有價值的(有篩選條件且有可用的索引)，那查詢最佳化工具就會去以成本為基礎計算出較佳的執行計畫，像此時查詢2的篩選條件上有可用的索引，查詢最佳化工具就會選擇使用Index Seek(索引搜尋)去取得資料，因為在此例使用索引搜尋的成本較低，但是這個非叢集索引並沒有涵蓋我們所有需要的資料，像FirstName沒有涵蓋在索引裡，就必須有額外的操作回到叢集索引去取得FirstName，這個獨特的行為就是Key Lookup囉，而Key Lookup一律伴隨Nested Loops 運算子，這樣應該了解這個執行計畫了吧。
相信你也發現Key Lookup這額外的操作成本好高啊，占了查詢2成本的99%，所以BOL有提到
相信你也發現Key Lookup這額外的操作成本好高啊，占了查詢2成本的99%，所以BOL有提到
在查詢計畫中使用 Key Lookup 運算子，表示查詢可以進行效能微調。例如，您可以新增涵蓋索引來提高查詢效能。先不管怎麼去除Key Lookup，我們繼續看查詢2中運算子的工具提示吧，如下圖
剛有提到NonClustered Index沒有包含所有需要的資料，你看Output List就只有ID欄位，所以必須透過Key Lookup去Clustered Index取得額外的資料，看Key Lookup運算子的Output List就指出FirstName欄位啦。
這裡看到另外一種述詞，Seek Predicates(搜尋述詞)，當在實現索引搜尋前，會評估查詢條件來決定哪個索引鍵是合適的，儲存引擎會使用索引來處理滿足搜尋述詞的資料列。
也許你曾看到一種狀況，搜尋運算子裡同時出現Seek Predicates與Predicates，這通常出現在使用複合索引的查詢裡，是指查詢的篩選條件不完全對應付複合索引欄位，造成單靠Seek Predicates操作不足以應付，還需要比對Predicates才能完成，你可以參考Index Selectivity and Column Order與SQL Server Column Considerations and Column Placement的例子。
這裡看到另外一種述詞，Seek Predicates(搜尋述詞)，當在實現索引搜尋前，會評估查詢條件來決定哪個索引鍵是合適的，儲存引擎會使用索引來處理滿足搜尋述詞的資料列。
也許你曾看到一種狀況，搜尋運算子裡同時出現Seek Predicates與Predicates，這通常出現在使用複合索引的查詢裡，是指查詢的篩選條件不完全對應付複合索引欄位，造成單靠Seek Predicates操作不足以應付，還需要比對Predicates才能完成，你可以參考Index Selectivity and Column Order與SQL Server Column Considerations and Column Placement的例子。
順便提一下怎麼去除Key Lookup，一種是建立複合索引欄位，把查詢用到的欄位都加到索引裡，另一種是建立內含資料行的索引，把查詢的FirstName與ID欄位放到INCLUDE裡，如下(其實ID欄位可不用放，因為ID是叢集索引資料行，一定會包含在非叢集索引裡的)
-- 建立內含資料行的索引
CREATE NONCLUSTERED INDEX [IX_TestTable_Include] ON [dbo].[TestTable]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO
在執行一下剛剛的查詢，再檢查一下執行計畫，Key Lookup消失啦，如下圖
USE AdventureWorks2008R2
GO
--按以下Ctrl + M來看實際的執行計畫
SELECT * FROM [Sales].[Customer] WHERE [TerritoryID] = 4
OPTION(RECOMPILE)
OPTION(RECOMPILE)
GO
我們直接看執行計畫的篩選運算子的工具提示囉，如下圖
一樣看到Predicates(述詞)為[AdventureWorks2008R2].[Sales].[Customer].[TerritoryID]=(4)，儲存引擎會比對所有滿足述詞的資料列喔，Estimated Number of Rows與Actual Number of Rows皆為4696筆，先告訴你估計的與實際的資料列數目一樣是好事，差一些些也還好，若差距過大，這可能會造成成本估計的錯誤，進而選擇較差的執行計畫喔！舉個例子來講好了，從你家到公司你估計有300m，這麼近應該會選擇用走路的吧，既省錢又環保，但是你估計錯了，實際上是30km，結果你還是用走的，是不是會走到汗流浹背還想罵髒話啊，要是我估計30km，我就會選開車的吧，所以估計準不準確是不是很重要啊。
再告訴你，基本上估計與實際的差距過大有幾個可能性，一個是統計資訊過期了需要更新，再不然就是查詢優化器在某些狀況下會無法正確的分析述詞，以至於無法做準確的預估囉，這個我之後會在別篇會在介紹。
另外，也許你自己試著改變查詢條件(述詞)，看執行計畫裡估計的資料列數目會不會跟著改變時，卻發現怎麼沒有變呢？發生甚麼事了？來看以下例子。
再告訴你，基本上估計與實際的差距過大有幾個可能性，一個是統計資訊過期了需要更新，再不然就是查詢優化器在某些狀況下會無法正確的分析述詞，以至於無法做準確的預估囉，這個我之後會在別篇會在介紹。
另外，也許你自己試著改變查詢條件(述詞)，看執行計畫裡估計的資料列數目會不會跟著改變時，卻發現怎麼沒有變呢？發生甚麼事了？來看以下例子。
--先清空執行計畫快取
DBCC FREEPROCCACHE
GO
--一樣我們一次執行下面的兩個查詢--Query 1
SELECT [TerritoryID]
FROM [Sales].[Customer]
where [TerritoryID] = 2
GO
--Query 2
--Query 2
SELECT [TerritoryID]
FROM [Sales].[Customer]
where [TerritoryID] = 4
GO
檢查一下叢集索引搜尋的工具提示吧，如下圖
照理說查詢優化器會去分析篩選條件(述詞)來預測有多少筆資料回傳，兩個查詢其篩選的條件是不一樣的，一個2一個4，實際的資料列數目不一樣是可預期的，但你看到兩個查詢估計的資料列數目居然都是113耶，怎會兩個查詢都一樣？這是因為發生執行計畫重用的結果，若你從搜尋述詞可以看到搜尋索引鍵[1]: 前置詞: [AdventureWorks2008R2].[Sales].[Customer].TerritoryID = 純量運算子(CONVERT_IMPLICIT(int,[@1],0))，注意似乎發生隱含轉換，然後原本的數值1,2都被轉換成參數@1了，這轉換的過程就是簡單參數化囉，像我為了示範避免發生執行計畫重用的狀況，才會在每個查詢的後面加上查詢提示OPTION(RECOMPILE)囉。
就先分享到這，有錯請指證喔，下篇我會再繼續分享其他影響執行計畫的因素。
對了，忘了提以上例子在SQL Server Denali與SQL Server 2008 R2測試過，你看到的圖片若是英文表示我是截SQL Server Denali的畫面，中文的就是2008 R2的。
good~~tks!
回覆刪除