[查詢優化]影響執行計畫的因素2-Density(密度)

 　　上篇介紹了述詞，本篇接著介紹另一個影響執行計畫的因素-Density(密度)，密度是用來描述欄位其重複值發生的頻率，密度介於０到１之間，欄位密度愈高代表唯一值的數量愈少，在既定的查詢下會回傳愈多的資料

公式如右：　　Density(密度) = 平均重複筆數/總筆數

也就是如右圖：

最後上下抵銷掉就是：　　Density(密度) = 1 / 唯一鍵值的筆數

而寫成SQL有兩種算式

1. SELECT 1.0　/　COUNT(*) FROM (SELECT DISTINCT <ColumnName> FROM <TableName>)　t
2. SELECT 1.0 / COUNT(DISTINCT <ColumnName>) FROM <TableName>

　　來看範例吧，先建立測試用資料表

IF OBJECT_ID('TestTable') IS NOT NULL

   BEGIN

   DROP TABLE TestTable

   END

GO

CREATE 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

　　目前TestTable有100000筆資料，那City欄位的密度是多少？依據上列的兩個算式

1. SELECT 1.0/COUNT(DISTINCT (City)) FROM TestTable
2. SELECT 1.0/COUNT(*) FROM (SELECT DISTINCT City FROM TestTable) a

　　不論用第一個或第二個算出來密度都是0.2，第一個的查詢比較簡潔有力，那為何要介紹第二個呢，如果要算複合欄位的密度時，就得用第二個！

　　從City欄位的密度0.2，我們還可以得到甚麼資訊呢？

• City欄位有多少筆唯一值？答案是1/0.2 = 5
• City欄位唯一值平均的重複次數是多少呢？答案是100000*0.2 = 20000

　　知道以上兩個值要做什？看看以下例子，執行完畢去檢查執行計畫

DECLARE @I VARCHAR(100)

SET @I = 'TAIWAN'

SELECT * FROM TestTable WHERE City = @I

　　我們看執行計畫的資料表掃描運算子，如下圖

　　因為沒有此條件的資料，所以實際的資料列數目為０，這沒有疑問，但是估計的資料列數目是20000，這數字不就是剛算出的唯一值平均的重複次數嗎！

　　繼續看下一個例子

SELECT COUNT(*) FROM TestTable

    GROUP BY City

　　一樣檢查執行計畫，看看資料表掃描運算子與湊雜比對運算子，如下圖

　　是不是看到100000與5這兩個數字！這不就是總筆數與唯一值的筆數嗎！

　　從以上兩個例子，我們可以想見欄位密度的相關資訊一定有儲存在SQL Server裡，查詢最佳化工具才得以使用這些資訊來作預估，而實際上密度的相關資訊就是儲存在統計資料裡的，BOL也提到SQL Server使用統計資料來改善查詢效能，所以我們有必要對統計資料有一定的了解

　　怎麼查看資料表有哪些統計資料呢？可以使用下列查詢

--傳回指定資料表之資料行和索引的統計資料。

EXEC sp_helpstats 'TestTable','ALL'

GO

　　由上圖可以看到目前這個資料表的City欄位，有個統計資料為_WA_Sys_00000004_17236851，你的統計資料名應該會跟我不一樣，因為這是系統自建的

　　接著利用DBCC SHOW_STATISTICS陳述式來顯示查詢最佳化統計資料

--顯示目前的查詢最佳化統計資料(記得替換_WA_Sys_00000004_17236851)
DBCC SHOW_STATISTICS("TestTable",'_WA_Sys_00000004_17236851')

GO

　　上圖統計資料的詳細解釋可參考DBCC SHOW_STATISTICS的說明，可以分為三個部分，標頭、密度及長條圖，標頭我通常只注意Rows，Rows是資料列總數的近似值，注意是近似值喔；密度只注意All density，就是我們前面算的密度；長條圖就注意RANGE_HI_KEY與EQ_ROWS，如果述詞尋找的值剛好落在RANGE_HI_KEY上，那EQ_ROWS的值就是估計的資料列數目，這樣你應該可以約略了解估計的資料列數目那值是從何而來的吧

　　長條圖上的每個值其實也是可以算的，可參考BOL的說明，我舉RANGE_HI_KEY為Taichung的來看，查詢如下

SELECT RANAGE_HI_KEY = 'Taichung',

   RANGE_ROW = (SELECT COUNT(0) FROM TestTable WHERE City > 'PingTung' AND City < 'Taichung' ),

   EQ_ROWS = (SELECT COUNT(0) FROM TestTable WHERE City = 'Taichung' ),

   DISTINCT_RANGE_ROWS = (SELECT COUNT(DISTINCT City) FROM TestTable WHERE City > 'PingTung' AND City < 'Taichung'),

   AVG_RANGE_ROWS = (

   CASE (SELECT COUNT(DISTINCT City) FROM TestTable WHERE City > 'PingTung' AND City < 'Taichung')

   WHEN 0 THEN 1

   ELSE ((SELECT COUNT(0) FROM TestTable WHERE City > 'PingTung' AND City < 'Taichung' )/(SELECT COUNT(DISTINCT City) FROM TestTable WHERE City > 'PingTung' AND City < 'Taichung'))

   END )

　　我們已經大致理解了統計資料的內容，那統計資料如何建立的呢？有以下三種：

1. 預設（AUTO_CREATE_STATISTICS 為ON），Database Engine將會自動替述詞中未使用索引的資料行建立統計資料
2. 手動建立統計資料
3. 建立索引時，查詢最佳化工具會自動儲存索引資料行的統計資料

--1.預設，查詢優化器會針對無索引欄位建立統計資料 

SELECT * FROM TestTable WHERE FirstName = 'John'

--多了FirstName的統計資料(注意statistics_name若為_WA開頭的為系統自建的)

EXEC sp_helpstats 'TestTable','ALL'

GO

--2.手動建立統計資料

CREATE STATISTICS Stat ON TestTable(LastName)

GO

--又多了LastName的統計資料

EXEC sp_helpstats 'TestTable','ALL'

GO

--3.建立索引時

CREATE INDEX idx_TestTable ON TestTable(ID)

GO

--又多了ID的統計資料

EXEC sp_helpstats 'TestTable','ALL'

GO

　　接著，我們來看述詞與統計資料間的關係，剛剛有提到若述詞尋找的值落在長條圖的RANGE_HI_KEY上，那EQ_ROWS的值就等於是估計的資料列數目，看下面的例子，一個述詞查Taichung，一個述詞查PingTung，對照統計資料的長條圖，你可以猜的到估計的資料列數目為多少吧！

SELECT * FROM TestTable WHERE City = 'Taichung'

GO

SELECT * FROM TestTable WHERE City = 'PingTung'

GO

　　檢查兩個查詢的執行計畫，如下圖Taichung的估計資料列數目為9900，PingTung的為70000，沒錯吧

　　不知你有沒有想到剛剛有個述詞查詢TAIWAN的，為什麼估計的資料列數目值是20000?
看以下兩個例子，查詢結果都一樣，沒有使用區域變數的，估計值是1，但是有使用區域變數的，估計值是20000。沒有使用區域變數的估計值為1很合理，因為查詢最佳化工具在統計資料裡找不到對應TAIWAN的值，不過統計畢竟是統計，是有誤差的，無法確認是不是真的沒有值，所以保守估計只有1筆

DECLARE @I VARCHAR(100)

SET @I = 'TAIWAN'

SELECT * FROM TestTable WHERE City = @I

GO

SELECT * FROM TestTable WHERE City = 'TAIWAN'

GO

　　BOL有提到當查詢最佳化工具建立查詢執行計畫時，無法得知區域變數的值，所以如果查詢述詞使用區域變數，查詢最佳化工具無法使用查詢述詞來做預估，就會用猜的囉

　　猜並不是隨機亂猜的喔，聽說是寫死在SQL Server裡的，出現的比率依述詞的條件大概有3種情況，如下：（這我試過從SQL 2005到SQL 2012(RC)好像都沒變）

-- 1. For "=",  uses COUNT(*) * column Density,  translating COUNT(*) * (1.0 / (COUNT (DISTINCT <column>)))

SELECT  *  FROM  <TABLE>  WHERE  <COLUMN>  =  @I

-- 2. For "BETWEEN",  uses 9%,  translating (COUNT(*) * 9.0) / 100

SELECT  *  FROM  <TABLE>  WHERE  <COLUMN>  BETWEEN  @I  AND  @X

-- 3. For ">, >=, < and <=",  uses 30%,  translating (COUNT(*) * 30.0) / 100

SELECT * FROM <TABLE> WHERE <COLUMN> “>, >=, < and <=” @I

 

　　現在我們已經知道估計的資料列數目其值是來自於統計資料裡，但是並沒有感受到查詢最佳化工具如何利用它來改善查詢效能，因為預設是自動建立統計資料的，你沒事不會去關閉它，所以沒法感受到沒有統計資料對查詢會有何不良影響！那我們就來比較有沒有統計資料對查詢上的影響吧

　　首先建立測試資料表，再建立一個複合欄位索引，語法如下

--建立測試資料表

IF OBJECT_ID('Contact') IS NOT NULL

   BEGIN

   DROP TABLE Contact

   END

GO

SELECT *

INTO Contact FROM Person.Person

GO

--建立複合欄位索引

CREATE INDEX NameIndex ON Contact(LastName, FirstName)

GO

　　檢查索引及統計資料，目前只有索引NameIndex的統計資料

EXEC sp_helpstats 'Contact','ALL'

--statistics_name  statistics_keys

--NameIndex  LastName, FirstName

　　一次執行以下兩個查詢

SELECT * FROM Contact WHERE FirstName LIKE 'J%'

SELECT * FROM Contact WHERE FirstName LIKE 'Q%'

GO

　　檢查一下執行計畫，你會看到如下圖的結果，兩個查詢僅差在述詞而已，但是一個查詢用資料表掃描，一個查詢用索引掃描＋RID查閱，相對於批次的查詢成本使用資料表掃描的為96%，使用索引掃描的僅為3%，成本差很多啊！

　　雖然剛建立的複合欄位索引並沒有統計資料在FirstName欄位，不過SQL Server仍然自動建立FirstName欄位統計值，查詢優化器才得以知道Q開頭的資料夠少(估計大約4筆)，可用較有效率的非叢集索引的leaf level掃描，而J開頭的資料夠多(估計大約2398筆)，此時查詢優化器判斷使用資料表掃描是較有效率的囉

　　此時檢查索引及統計資料，你會發現，系統的確自動建立了FirstName欄位的統計資料來幫助查詢

EXEC sp_helpstats 'Contact','ALL'

--statistics_name  statistics_keys

--_WA_Sys_00000005_31D75E8D  FirstName

--NameIndex  LastName, FirstName

　　我們順便看一下這兩個查詢的磁碟IO統計資訊，使用下面的查詢

SET STATISTICS IO ON

SELECT * FROM Contact WHERE FirstName LIKE 'J%'

SELECT * FROM Contact WHERE FirstName LIKE 'Q%'
SET STATISTICS IO OFF

GO

　　一個邏輯讀取(從資料快取中讀取的頁數)頁數為3807頁，一個邏輯賭取頁數為111頁，成本真的差很多
　

　　接著我們把資料庫的自動建立統計資料選項關掉，再來看一遍

--關掉auto_create_statistics

ALTER DATABASE AdventureWorks2008R2 SET auto_create_statistics OFF

　　重新建立測試資料表與複合欄位索引

--建立測試資料表

IF OBJECT_ID('Contact') IS NOT NULL

   BEGIN

   DROP TABLE Contact

   END

GO

SELECT *

INTO Contact FROM Person.Person

GO

CREATE INDEX NameIndex ON Contact(LastName, FirstName)

GO

　　檢查索引及統計資料，目前只有索引NameIndex的統計資料

EXEC sp_helpstats 'Contact','ALL'

--statistics_name  statistics_keys

--NameIndex  LastName, FirstName

　　一樣一次執行以下兩個查詢

SELECT * FROM Contact WHERE FirstName LIKE 'J%'

SELECT * FROM Contact WHERE FirstName LIKE 'Q%'

GO

　　檢查一下執行計畫，你有沒有發現，兩個查詢的執行計畫都是用資料表掃描囉，因為我們關掉自動建立統計資料的選項，查詢優化器不會自動替FirstName欄位建立統計資訊了，因為沒有足夠的統計資料，所以查詢優化器沒法選擇較佳的執行計畫啦，看到這裡，你說，統計資料重不重要啊！

　　檢查索引及統計資料，的確沒有FirstName欄位統計資料喔

EXEC sp_helpstats 'Contact','ALL'

--statistics_name  statistics_keys

--NameIndex  LastName, FirstName

--記得還原設定喔

ALTER DATABASE AdventureWorks2008R2 SET auto_create_statistics ON

GO

　　最後再次強調統計資料的重要，沒有它，查詢優化器就無法有足夠的資訊來產生較佳的執行計畫喔，所以有人會建議要每天排程自動檢查AUTO_CREATE_STATISTICS選項時不是打開的，若關閉的話，就要檢查看看是不是另外有機制在建立統計資料囉，這就不在討論的範圍了