從效能的角度談SQL Server叢集索引鍵的選擇，

從效能的角度談SQL Server叢集索引鍵的選擇，
簡介

    在SQL Server中，資料是按頁進行存放的。而為表加上叢集索引後，SQL Server對於資料的尋找就是按照叢集索引的列作為關鍵字進行了。因此對於叢集索引的選擇對效能的影響就變得十分重要了。本文從旨在從效能的角度來談叢集索引的選擇，但這僅僅是從效能方面考慮。對於有特殊業務要求的表，則需要按實際情況進行選擇。

 

叢集索引所在的列或列的組合最好是唯一的

    這個原因需要從資料的存放原理來談。在SQL Server中，資料的存放方式並不是以行(Row)為單位，而是以頁為單位。因此，在尋找資料時，SQL Server尋找的最小單位實際上是頁。也就是說即使你只尋找一行很小的資料，SQL Server也會將整個頁尋找出來，放到緩衝池中。

    每一個頁的大小是8K。每個頁都會有一個對於SQL Server來說的物理地址。這個地址的寫法是 檔案號：頁號(理解檔案號需要你對檔案和檔案組有所瞭解).比如第一個檔案的第50頁。則頁號為1：50。當表沒有叢集索引時，表中的資料頁是以堆(Heap)進行存放的,在頁的基礎上，SQL Server通過一個額外的行號來唯一確定每一行，這也就是傳說中的RID。RID是檔案號:頁號：行號來進行表示的，假設這一行在前面所說的頁中的第5行，則RID表示為1:50:5，1所示。

    

    圖1.RID的樣本

  

    從RID的概念來看，RID不僅僅是SQL Server唯一確定每一行的依據，也是存允許存取的存放位置。當頁通過堆（Heap）進行組織時，頁很少進行移動。

    而當表上建立叢集索引時，表中的頁按照B樹進行組織。此時，SQL Server尋找行不再是按RID進行尋找，轉而使用了關鍵字,也就是叢集索引的列作為關鍵字進行尋找。假設圖1的表中，我們設定DepartmentID列作為叢集索引列。則B樹的非葉子節點的行中只包含了DepartmentID和指向下一層節點的書籤(BookMark)。

    而當我們建立的叢集索引的值不唯一時，SQL Server則無法僅僅通過叢集索引列（也就是關鍵字）唯一確定一行。此時，為了實現對每一行的唯一區分，則需要SQL Server為相同值的叢集索引列產生一個額外的標識資訊進行區分，這也就是所謂的uniquifiers。而使用了uniquifier後，對效能產生的影響分為如下兩部分：

•     SQL Server必須在插入或者更新時對現在資料進行判斷是否和現有的鍵重複，如果重複，則需要產生uniquifier,這個是一筆額外開銷。
•     因為需要對相同值的鍵添加額外的uniquifier來區分，因此鍵的大小被額外的增加了。因此無論是葉子節點和非葉子節點，都需要更多的頁進行儲存。從而還影響到了非叢集索引，使得非叢集索引的書籤列變大，從而使得非叢集索引也需要更多的頁進行儲存。

    下面我們進行測試，建立一個測試表，建立叢集索引。插入10萬條測試資料，其中每2條一重複，2所示。

    

    圖2.插入資料的測試代碼

    

   此時，我們來查看這個表所佔的頁數,3所示。

    

    圖3.插入重複鍵後10萬資料佔了359頁

 

    我們再次插入10萬不重複的資料，4所示。

    

    圖4.插入10萬不重複的建的代碼

 

    此時，所佔頁數縮減為335頁,5所示。

    

    圖5.插入不重複鍵後縮減為335頁

 

     因此，推薦叢集索引所在列使用唯一鍵。

 

最好使用窄列或窄列組合作為叢集索引列

    這個道理和上面減少頁的原理一樣,窄列使得鍵的大小變小。使得叢集索引的非葉子節點減少，而非叢集索引的書籤變小，從而葉子節點頁變得更少。最終提高了效能。

 

使用值很少變動的列或列的組合作為叢集索引列

    在前面我們知道。當為表建立叢集索引後。SQL Server按照鍵尋找行。因為在B數中，資料是有序的，所以當叢集索引鍵發生改變時，不僅僅需要改變值本身，還需要改變這個鍵所在行的位置（RID），因此有可能使得行從一頁移動到另一頁。從而達到有序。因此會帶來如下問題：

•     行從一頁移動到另一頁，這個操作是需要開銷的,不僅如此，這個操作還可能影響到其他行，使得其他行也需要移動位置，有可能產生分頁
•     行在頁之間的移動會產生索引片段
•     鍵的改變會影響到非叢集索引，使得非叢集索引的書籤也需要改變，這又是一筆額外的開銷

     這也就是為什麼很多表建立一列與資料本身無關的列作為主鍵比如AdventureWorks資料庫中的Person.Address表，使用AddressID這個和資料本身無關的列作為叢集索引列,6所示。而使用AddressLine1作為主鍵的話，員工地址的變動則可能造成上面列表的問題。

    

    圖6.建立和資料本身無關的一列作為叢集索引列

 

最好使用自增列作為叢集索引列

    這個建議也同樣推薦建立一個和資料本身無關的自增列作為叢集索引列。我們知道，如果新添加進來的資料如果叢集索引列需要插入當前有序的B樹中，則需要移動其它的行來給新插入的行騰出位置。因此可能會造成分頁和索引片段。同樣的，還會造成修改非叢集索引的額外負擔。而使用自增列，新行的插入則會大大的減少分頁和片段。

   最近我碰到過一個情況。一個表每隔幾個月效能就奇慢無比，初步查看是由於有大量的索引片段。可是每隔幾個月重建一次索引讓我無比厭煩。最終我發現，問題是由於當時設計資料庫的人員將叢集索引建在了GUID上，而GUID是隨機產生的，則可能插入到表的任何位置，從而大大增加了片段的數量。因此造成上面這種情況。

 

總結

    本文簡單介紹了SQL Server儲存的原理和應該規避的幾種叢集索引建立情況，但這僅僅是從效能的角度來談叢集索引的選擇。對於叢集索引的選擇，還是需要全面的考慮進行決定。