RiCo技術農場

很久以前我大概有簡單介紹建立Clustered Index 的重要性，大多數的情況下，

資料表建立好的 Clustered Index 都會帶來不錯的查詢效能(但不總是)，

這次因為自己專業知識不足產生了一個查詢效能問題，

所以紀錄一下提醒自己往後還須小心注意。

我先說自己選擇 Cluster Key一些條件(因人而異)，基本上Cluster Key需符合以下條件

1. 唯一性。

2. 不可 null。

3. 寬度窄。

4. 資料異動少。

寬度窄:

當資料表中沒有合適的單一欄位來達到唯一性，基本上就會尋找複合欄位

來達到唯一性，如OKIP(char(30))+NKIP(char(30))，但這樣Cluster Key基本最低需花費 60 bytes 空間大小，

而且NonClustered Index 也會包含Cluster Key，如果該資料表超過上百萬筆資料，

你可以想像這會浪費多少空間和Buffer Pool，而且查詢效能的花費也相當嚇人，

所以你應該避免選擇過寬的Cluster Key。

關於Cluster and NonClustered Index空間的估算大家可以參考 估計資料庫的大小。

資料異動少

如果你選擇的Cluster Key經常執行資料異動，基本上會有幾個可怕副作用。

1. SQL Server會調整移動Clustered Index內的資料位置，進而造成更多的索引碎片，

也有可能產生頁面分割的處理(使用更多 page)。

2. 由於每個NonClustered Index都包含Cluster Key，所以當Clustered Index資料有所異動，

將連動影響NonClustered Index Key異動更新，這當然也會造成更多的索引碎片和頁面分割。

資料異動頻繁的欄位實在不適合建立 Clustered Index，因為這無疑是浪費Server時間和空間的殺手，

而且也會造成查詢效能不必要的I/O花費，所以Cluster Key 應該減少執行資料異動。

接下來，說一個困惱我很久的問題…..

兩年多前有個專案，其中有個查詢效能三不五時(有時半年，有時3~5月..)都會被客戶關照一下，

雖然我執行rebuild Index都能解決問題，但當時我還是無法確切知道造成龐大索引碎片的主要原因，

由於該資料表初期資料約90萬筆，兩年後資料大概也不超過95萬筆，

所以資料異動比例不算太高，而且客戶覺得每次rebuild index相當浪費時間，

希望我能找出真正原因，當然最後終於讓我找到真正問題點(所以才有這篇文阿…Orz)，

下面我簡單模擬一下。

當初該資料表 Layout如下

create table myguidnew

(

id uniqueidentifier not null default newid(),

c2 tinyint,

c3 char(4)

)

create clustered index cidx_1 on myguidnew(id)

--新增資料

set nocount on

insert into myguidnew(c2,c3)

select 4,'rico'

go 500

--顯示資料表索引碎片資訊

dbcc showcontig('myguidnew') with tableresults

你可以看到目前平均頁面密度(AveragePageDensity)相當高(超過90%算還不賴)。

總共使用 2 pages。

每頁平均可用位元組數(AverageFreeBytes)596。

接下來我大概模擬資料成長

--再次新增5000筆資料

set nocount on

insert into myguidnew(c2,c3)

select 5,'rico'

go 5000

--顯示資料表索引碎片資訊

dbcc showcontig('myguidnew') with tableresults

新增5000筆資料後，使用 pages 從 2 增為 33，AberagePageDensity從92.63降低為61.75，

AverageFreeBytes從596暴增為3096，

這表示每頁空間沒有好好利用(碎片過多)，可想而知這也將連帶影響查詢效能。

--查詢彙總

select c3,COUNT(1) as 'Totals' 

from myguidnew

group by c3

整體查詢成本:0.086，經過時間(ms)=23。

為了不想三不五時就被客戶關心，我開始檢查當初自己的Table and Index Design是否出了問題，

當我看了newid和 NEWSEQUENTIALID兩者差別後，我做了以下測試...

create table myguidnewseq

(

id uniqueidentifier not null default NEWSEQUENTIALID(),

c2 tinyint,

c3 char(4)

)

create clustered index cidx_A on myguidnewseq(id)

--新增資料

set nocount on

insert into myguidnewseq(c2,c3)

select 4,'rico'

go 500

--顯示資料表索引碎片資訊

dbcc showcontig('myguidnewseq') with tableresults

新增前500筆資料的AveerageFreebytes、pages和AveragePageDessity都和 myguidnew 資料表花費相同。

--再次新增5000筆資料

set nocount on

insert into myguidnewseq(c2,c3)

select 5,'rico'

go 5000

--顯示資料表索引碎片資訊

dbcc showcontig('myguidnewseq') with tableresults

再次新增5000筆資料，pages 從2 增為21，AveerageFreebytes 從596減少為238.857，

AveragePageDessity 從92.63增加為97.04，LogicalFragementation也減少為9.52。

--查詢彙總

select c3,COUNT(1) as 'Totals'

from myguidnewseq

group by c3

整體查詢成本:0.077，經過時間(ms)=3。

結果比較表(5500筆資料)

結論:

雖然資料異動免不了會產生索引碎片和頁面分割，

但我們在設計資料表或索引時應該就要考慮盡量減少索引碎片和頁面分割，

這個案例中，我忽略newid是隨機產生的，造成新增資料時，SQL Server為了要維護Clustered Index邏輯排序，

所以額外花費成本來處理，而且也產生更多的索引碎片，也造成大量的頁面分割(導致I/O大增)。

最近處理這問題有個很深的感觸，資料庫、資料表或索引結構設計不良，

可能是由於本身專業知識不足進而對系統產生不小的殺傷力，

而這也好像開發人員應該對自己寫的程式碼負責，

因為開發人員撰寫不良的程式碼對系統傷害的程度也不能小看。

雖然沒有一個很好通用的準則來選擇 Cluster Key(因為還需要考慮索引填滿因子-- index fill factor)，

但希望透過本文可以讓你知道建立Clustered Index是一件相當重要的事(主要是專業知識...XD)，

最後~針對 Cluster Key條件整理如下:

1. 唯一性。

2. 不可 null。

3. 寬度窄。

4. 資料異動少。

5.考慮 Insert 資料排序(或最小碎片)。

參考

NEWSEQUENTIALID() (Transact-SQL)

NEWID (Transact-SQL)

DBCC SHOWCONTIG (Transact-SQL)

uniqueidentifier (Transact-SQL)