[查詢優化]影響執行計畫的因素3-Cardinality(基數)

 　　基數是用來量測滿足某個條件下的資料列數目，想像一下，如果一個員工資料表總共有500筆資料，這個資料表有單位、姓名及ID等欄位，有一個查詢的條件是"WHERE 單位 = '資訊處' "，那查詢最佳化工具就會找[單位]的統計資料，結果發現直方圖為[資訊處]有50筆，這50筆就是所謂的基數

　　有沒有覺得這個基數很熟悉，沒錯，在執行計畫裡，我們可以把每個運算子的估計的資料列數目當作是基數喔

　　有時查詢最佳化工具沒法正確的預估基數，使得執行計畫的成本估計不正確，會導致選擇次佳的執行計畫呢

　　BOL查詢效能不佳的疑難排解：基數估計裡提到，查詢最佳化工具根據兩個主要因數來判斷執行查詢計畫的成本，基數就是其中的一個因數喔

　　如果改善基數，便能產生更好的估計成本，進而可有更快的執行計畫。

　　這麼重要的基數，難道不值得花一點時間來了解一下呢！所以我們來看一個使用計算欄位來改善基數的例子！

執行以下查詢，然後檢查一下執行計畫

USE AdventureWorks2008R2

GO

--

SELECT * FROM Sales.SalesOrderDetail

WHERE OrderQty * UnitPrice > 27000

GO

 

 　　執行計畫如上圖，那我們直接看篩選運算子的內容（如下圖），估計的資料列數目是總筆數121317的30%，也就是36395.1筆，而實際的資料列數目才3筆，差距如此之大表示查詢最佳化工具無法依據OrderQty * UnitPrice > 27000述詞來做預估，只好用猜的，因為是＞，所以才會用30%，怎麼猜的我在前一篇密度有提到

接著我們使用以下兩個查詢來看有哪些統計資料，第一個查詢可以看出欄位對應，第二個查詢可以看出建立順序，愈後面的建立時間點愈近
 

EXEC sp_helpstats 'Sales.SalesOrderDetail','ALL'

GO

SELECT * FROM sys.stats

WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')

GO

　　從下圖我們知道目前總共有６筆統計資料

　　接著使用下列語法建立計算欄位cc

 

ALTER TABLE Sales.SalesOrderDetail

ADD cc AS OrderQty * UnitPrice

GO


　　再次檢查統計資料，還是6筆，因為建立計算欄位並不會自動建立統計資料，統計資料是在第一次執行時才會建立的

EXEC sp_helpstats 'Sales.SalesOrderDetail','ALL'

GO

SELECT * FROM sys.stats

WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')

GO

 　　接著再執行一次以下查詢，並檢查執行計畫

SELECT * FROM Sales.SalesOrderDetail

WHERE OrderQty * UnitPrice > 27000

　　從下圖你應該可以注意到估計的資料列數目比起之前精準許多了，從36395.1降為25.8132，使用計算欄位在這情況是可以改善基數估計的喔，不過因為沒有可用的索引，所以看不出執行計畫成本有何改善，相信你也發現上圖有出現遺漏索引的提示了，告訴我們建立索引可影響53.2217的成本呢，遺漏索引是一個輕量型的工具，實務上大部分還是會使用使用 Database Engine Tuning Advisor 來微調索引組態

　　既然都提示你建立索引可以改善成本，那當然要測測看囉，不過在建立索引改善執行計畫前，先看一下目前統計資料的變化，使用以下兩個查詢可以發現執行查詢後的確多了cc欄位的統計資料呢

EXEC sp_helpstats 'Sales.SalesOrderDetail','ALL'

GO

SELECT * FROM sys.stats

WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')

GO

 　　接著建立計算欄位的索引來看看，是不是能幫助查詢最佳化工具透過使用索引而產生更好的執行計畫呢？

CREATE INDEX IX_cc ON Sales.SalesOrderDetail(cc)

GO

再檢查一下統計資料，你會發現已自動建立的索引IX_cc的統計資料了

EXEC sp_helpstats 'Sales.SalesOrderDetail','ALL'

GO

SELECT * FROM sys.stats

WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')

GO

再執行一次查詢吧，你會發現執行計畫選擇索引搜尋及索引鍵查閱來查閱囉

SELECT * FROM Sales.SalesOrderDetail

WHERE OrderQty * UnitPrice > 27000

GO

 　　因為建立了有用的計算欄位IX_cc索引，查詢最佳化工具經過分析後，發現使用此索引的成本最低，就拿來使用啦

--最後記得還原喔

DROP INDEX Sales.SalesOrderDetail.IX_cc

GO

ALTER TABLE Sales.SalesOrderDetail DROP COLUMN cc

GO