SQL Server聚集索引的選擇

先聲明文章非原創，摘自博客園：http://www.cnblogs.com/CareySon/archive/2012/03/06/2381582.html

簡介

?? 在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所示：

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

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

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

1、SQL Server必須在插入或者更新時對現在的數據進行判讀是否和現有的鍵重復，如果重復，則需要生成uniqifier,這個是一筆額外開銷。

2、因為需要對相同值的鍵添加額外的uniquifier來區分，因此鍵的大小被額外的增加了。因此無論是葉子節點和非葉子節點，都需要更多的頁進行存儲。從而還影響到非聚集索引，使得非聚集索引的書簽列變大，從而使得非聚集索引也需要進行更多的頁進行存儲。

下面我們進行測試，創建一個測試表，創建聚集索引。插入10W條測試，其中每2條一重復，如圖2所示。

--創建測試表
create table [dbo].[TestP]
([id] int,[Name] varchar(100)
)
go--在id上創建聚集索引
create clustered index testp_cindex on TestP(id)
go
--插入10W條數據測試，每2條一重復
begin tran
declare  @index int
set @index=0
while(@index<100000)
begin insert into dbo.TestP(id,Name)values(@index,'測試數據')insert into dbo.TestP(id,Name)values(@index,'測試數據')
set @index=@index+1
end
commitexec sp_spaceused 'TestP'

我們插入了測試數據
此時，我們來查看這個表所占的頁數,如圖3所示。

插入重復鍵后10W數據占了359頁

我們再次插入10W不重復的數據，如圖所示

??? 此時，所占頁數縮減為335頁,如圖5所示。

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

二、最好使用窄列或窄列組合作為聚集索引列

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

?

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

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

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

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

???

??? 圖6.創建和數據本身無關的一列作為聚集索引列

?

四、最好使用自增列作為聚集索引列

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

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

?

總結

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

?

?

?