SQL Server Index 架構介紹 

  最近因為公司需求開始重新接觸到SQL Server，因為之前接觸的都是Oracle，所以就到MSDN 惡補了一些觀念，其實以前就對DBA有濃厚的興趣了，可是專案實在太忙了。如今只好利用閒暇之餘猛上網K原文技術文件。因為工作主要的是調整效能，所以就從SQL Server 的索引看起，順便把心得寫下來放在自己的部落格，充充人頭。因為我是讀原文的有些有解釋錯誤或是觀念錯誤也煩請指正一下喔，接下來就開始介紹吧。 

SQL Server 的索引分類

SQL Server 的索引分類，我想只要開發過以SQL Server 為Database 軟體的人，多少會知道SQL Server Index 分成 叢集索引(Clustered-Index) 與 非叢集索引(NonClustered-Index)，Clusterd-Index 每個資料表只能建立一個，為什麼呢? 這是因為Clustered-Index就是硬碟存放實體資料的地方，實體資料只能在一個架構下存放一份的關係，如果同一份資料存放兩份，是非常耗資源的，除非是用來備份。當資料有異動時，系統會先依索引的建值進行資料的排序，排序後，再存入到硬碟中。如果一個資料表並沒有建立Clustered-Index，則資料會直接存入硬碟，並不會經過任何排序，我們稱之為Heap。Clustered除了存放實體的資料外也包含存放索引的指標。

每個資料表可以建立多個NonClustered-Index，此類的索引單純存放著指標資料，而該指標所對應到的是Clustered-Index或是Heaper 內實體資料所放的位置。

Clustered-Index 架構

Clustered Index 儲存區塊的資訊，是由「經過排序的實體資料」與「連結到這些實體資料位址的索引」以B-Tree的方式排序後所組成的。

Clustered-Index 可以由數個分割(Partition)儲存體組成，簡單的說，就是可以將資料分別放在不同的硬碟目錄中，系統預設是一個Clustered-Index對應一個分割，索引的每個分割在sys.partitions中都會定義一個index_id = 1的記錄，用來存放分割的資訊，如下圖。 

個別的分割內容已經是依據Clustered-Key以B-Tree方式排序過的記錄，並且由根節點(Root Node)、中間節點(Intermediate Node )、葉節點(Leaf Node) 三種類型的節點組成，大家可以把節點當成是一組資料集，資料集中的每筆記錄，同時存放著『指標』與『索引鍵值，『指標』是用來指向其他的索引頁或是資料頁，『索引鍵值』是用來提供系統搜尋。根節點是分割的進入點，又稱為ROOT PAGE。 「根節點」內的每筆資料記錄著「中間節點」的指標。

「中間節點」是一組單純用來記錄「葉節點」指標及鍵值的資料集，而 ｢葉節點」才是真正存放實體資料的地方，假設我們的Clutered-key是由公司(2bytes)+門市(4bytes)+商品號碼(3bytes) 三個欄位組成，而在單一分割內的架構會如下圖所示：

由上圖可以看出一個Partition由下列節點的組成：

「根節點」：是一種存放索引資訊的索引頁，記載著「中間節點」的進入指標，如圖中的PID1、PID2…等以及一個提供查詢時比對的索引鍵值，如00 (公司別)+A001(門市)+001(商品號碼)=’00A001001’。此外，SQL Server 透過系統內建view 「sys.system_internals_allocation_units」內的root page欄位，記錄著每個分割的根節點的位置。

「中間節點」：是一種存放索引資訊的索引頁，記載著「葉節點」的進入指標，以及一個提供查詢時比對的索引鍵值，此外「中間節點」節點也會將自己的上一頁及下一頁分別記錄在「previos」及「next」指標欄位中。

「葉節點」：是一種存放實體資料的資料頁，記載著實體資料，此節點也會將自己的上一頁及下一頁分別記錄在「previos」及「next」指標欄位中。 

Clustered Index 設計守則

當要設計一個Clustered-Index時，鍵值欄位的取捨是很重要的，在設計時盡量排除下列狀況： 

一、更新頻率過高的欄位：
因為Clustered Index每次更新時都會對實體資料進行排序，如果資料量較大的資料表，系統會在排序上會多花不少時間處理，所以並不建議以此類的欄位當索引鍵。

二、獨特性過高的欄位：
因為實體資料是經過排序存入到硬碟中，若欄位中每筆記錄都沒甚麼順序性，則無法有效利用到此排序索引，例如，一個門市的銷售檔內，只以「稅別」當索引鍵，當查詢時若用到的是日期、商品或是公司來進行查詢時，都不會使用到Clustered-Index排序的功能，除此外也可能因一千萬筆的銷售資料中，只有一筆是免稅，其他都是含稅，導致無法有效的利用到排序。 

甲、查詢上的設計考量

當要建立一個Clustered-index以前，必需先考量資料表未來的用途、未來使用者可能會查詢的各種狀況。以下的查詢狀況，建議列入設計時的考量

一、連續性的範圍查詢結果：

因為實體資料是按照鍵值依序得存入硬碟中，若依Between  >=  <= 等符號查詢時，當系統找到第一筆資料後，依序逐筆往下讀取，查詢的結果是連續性的範圍，則執行的速度也會提升。

二、會用於JOIN指令中欄位建議加入，一般都是foreign Key欄位。

三、會用於ORDER BY或GROUP BY指令中，若索引鍵值常用於ORDER BY 與GROUP BY中時，因為實體資料已經排序好了，系統不會再進行排序的動作，因此會增加執行的速度。 

乙、欄位上的考量
一般來說，索引鍵值 所定義的欄位盡可能越少越好，下列的欄位情形，可以列入考慮，將其設計為索引鍵值： 

一、單一性(unique)或多個獨特性質欄位的組合：
單一性的欄位，例如銷售的單號、發票的號碼等，這些欄位的特性就是每筆記錄欄位內容值都不一樣，排序完可以有效的區分個別的記錄，所以建議可以拿來當作索引鍵值
當針對某個資料表建立主鍵值(Primary Key)時，SQL Server都會自動加一個 Unique index給對應的主鍵，若該資料表沒有設定其他的Clustered index ，系統會預設這個Unique index為 Clustered index。
除了上述的單一性外，還記得先前所說過的，盡量不要使用獨特性過高的欄位嗎? 但是當多個獨特性欄例如，稅別+幣別+..，只要組合起來可以有效的區分出每筆記錄，這樣的組合仍然可以有效得運用到實體排序。

二、欄位具有順序性範圍的查詢：
就是使用者常用between 或 >= ,<=來查詢的頻率很高的欄位。

三、被定義成IDENTITY欄位。

四、常常被用來ORDER BY 或是 GROUP BY的欄位，因為實體排序已經排序好了，系統不需再多作排序的動作。

    下列的欄位組合的性質不宜拿來當成索引鍵值

一、更新頻率過高的欄位，因為系統每次都要排序。

二、過多或過大的欄位所組成鍵值，因為過多或過大都會造成系統在進行排序時的負擔。

Nonclustered Index 架構 

 Nonclustered Index也是以B-Tree的方式排序，可以分割成數個Partition組成，每個Partition使用自己的B-Tree排序，這些都與Clustered Index架構相同，但仍有下列的不同點：

一、指標對應到的實體資料並不是按照Nonclustered Index Key排序，在這邊我們先回顧一下 Clutered Index實體資料是按照Clustered Index Key以B-Tree的方式排序後存放到硬碟內的，而Nonclustered Index 則是將指標指到 Clustered Index葉節點內的實體資料。

二、Nonclustered Index葉節點的層級是單純記錄索引後的指標，這點與Clutered Index不同，Clustered Index的葉節點是存放著實體資料。 

  Nonclustered Index 可定義在已建立Clustered Index的資料表或是檢視表上面，也可以定義在一個無建立任何Clustered Index的資料表或檢視表上，而Nonclustered Index 索引頁中的每筆記錄，同時存放著『指標』與『Noncluster index Key』 

  Nonclustered Index 的指標型態有兩種，一為指向Heap的實體資料指標，因為沒有Clustered Index Key所以存放的是系統ID，否則就存放Cluster Index Key指向實體排序。若想要將Clustered Index 設定成Unique時，而 Cluster Index Key 內包含重覆的資料，則系統會自動產生4bytes Uniquefiler 用來區分重覆的鍵值資料。 

 我們在此先建立一個Nonclustered Index Key為「銷售日期」(8byes)欄位的Nonclustered index 以及一個Clutered Index Key是由公司(2bytes)+門市(4bytes)+商品號碼(3bytes) 三個條件組成的Clustered Index，整體的架構如下圖

由上圖我們可以看出來，Nonclustered Index的根節點內指標存放的是「葉節點」的進入位址，如PID1、PID2…等，而葉節點內存放的是資料列的指標，若這個資料表有建立Clustered Index 則會存放Clustered Index Key ，若無的話則會存放系統ID，並且指到Clustred Index 內的實體存放記錄 或是 Heap內的實體資料存放記錄，因為實體存放的方式並非是按照 Nonclustered Index Key「銷售日期」排序，所以實體存放位置是分散在不同的地方。

還記得一位資深公司同事出的DBA考題，我們可以藉此拿出來複習一下： 

假設有一個資料表SALE_H 為銷售檔

試問下列SQL 是否可以用到Clustered Index Seek：

Select sl_amt from sale_h

where s_no between ‘7001’ and ‘7022’

答案是無法用到Clustered Index Seek，為什麼呢? 因為S_NO是Nonclustered Index Key 按照我們剛剛所說的，因為實體資料是按照Clustered Index Key 排列的而非NonClustered Index Key，所以NonClustered  Key作範圍查詢時，尋找Clustered Index 資料頁時是以Clustered Index Scan掃描的方式進行，但網路上曾經有人測試，當查詢的資料筆數小於實體資料筆數的0.4%時，會變成NonCluster Index Seek，神奇吧。

Nonclustered Index 的加包欄位(Included Columns) 

NonCluster Index除了索引鍵外，也可以外加一些非索引鍵的欄位到「葉節點」內，一般稱之為Nonkey，Nonkey可以用來解決索引鍵值超過最大限度900bytes的問題。設計時有以下的守則

一、Nonkey欄位是透過Create　Index指令中的Include子句定義

    的。

 二、Included Columns只能定義在NonClusteredKey的葉節點(Leaf

    Node)中

三、除了text、ntext以及images外的資料型態都可以成為Nonkey

    欄位。

四、無倫精確性或是非精確性的公式欄位都可以成為加包欄位。

五、來源由image, ntext以及text所衍生的公式欄位，只要計算出

    的欄位符合Nonkey所允許的欄位格式，也可以成為Nonkey欄

    位。

六、無法指定一個欄位同時是Nonclusted Key也是Nonkey。

七、在Include子句中不可以出現重複的Nonkey欄位。

欄位長度的設計守則

1. 一、當下達Include子句時至少要一個欄位，而Nonkey最多可以

2.     由1023個欄位組成。

3. 二、Nonkey欄位以外的Nonclustered索引鍵最多只有16個欄位

4.     並且總長度不可超過900 bytes。

5. 三、所有Nonkey欄位定義的總長，不可以超過2GB，例如，

6.     nonkey1 varchar(max)+nonkey2 varchar(max)…累加起來不

7.     可以已超過2GB。

8.   

9.  欄位異動的設計守則

10.  

一、Nonkey欄位不可以任意的被單獨移除，只能刪除整個

    Nonclustered Index才可以重建。

二、Nonkey欄位不可以被任意修改，但是還是可以設定成是否為

    null或是增加欄位長度。

Nonkey設計時的建議

當設計Nonclutered Index時，若需要大量的鍵值應付所有的查詢欄位，此時就可以使用Nonkey(Included)欄位，此時只要將常用來搜尋的少許欄位當成Nonclutered Index鍵值就可以，而其它欄位都可以加成Nonkey欄位，例如當有一個常用查詢如下：

SELECT  SL_DATE,  S_NO, SL_KEY,P_NO,SL_QTY

FROM SALE_D

WHERE SL_KEY BETWEEN  '20060301001' and '200605233001';

以上設計Nonclutered Index時，把用來搜尋或尋找的鍵值，此例為SL_KEY，當成 Nonclutered Index的鍵值就可以，而其它欄位都可以加成Nonkey，所以當下答Create Index指令時，作法如下:

CREATE INDEX IX_SLKEY_SALED

ON SALE_D (SL_KEY)

INCLUDE (  SL_DATE,  S_NO,P_NO,SL_QTY );

以上我們在建立SALE_D的IX_SLKEY_SALED的Nonclustered Index時，指定SL_KEY當鍵值，其它select中的欄位我們通通放到include子句中。

像這種具有含蓋所有查詢欄位的NonClutered Index我們也稱之為 「Covering Index」。