[SQL SERVER][Memo]Clustered VS NonClustered Indexes

2010-01-08

18115
0
SQL_SERVER
2011-12-16

今天被朋友問到叢集索引和非叢集索引有何不同，其實我還滿想告訴他請看線上叢書

但通常會得到看不懂的回答，所以這裡自己記錄一下兩者差別。

叢集索引:

Table資料本身就是叢集索引的一部分(含所有欄位)

即Table本身就是叢集索引的分葉節點(leaf level)

而Table資料存放順序依照所選定的欄位來排序(大到小或小到大)

通常一個Table只能存在一個叢集索引，因為資料存放順序只能遵守一種準則(大到小或小到大)

這也代表決定叢集索引欄位是很重要的事情。

上面提到建立叢集索引後，資料即享有排序同時Table資料本身就是叢集索引的一部分

所以挑選叢集索引欄位應該符合常使用來排序的欄位(order by、group by)

或唯一，也就是該欄位資料你不會輕易更改都適合建立叢集索引

以及不可NULL等特性(但不總是)。

select * from sales where SalesOrderDetailID>1 and SalesOrderDetailID<10000

因為要顯示所有欄位，所以SalesOrderDetailID適合建立叢集索引。

叢集索引結構

非叢集索引:

非叢集索引與叢集索引有下列差異：

1.Table的資料列並未依據非叢集索引鍵的順序，進行排序與儲存。

2.非叢集索引的分葉層是由索引頁 (而不是資料頁) 所組成。

3.如果Table有建立叢集索引則非叢集索引也包含叢集索引的鍵值。

非叢集索引資料列中的資料列定位器是資料列的指標，或資料列的叢集索引鍵，如下所述：

1.如果資料表為Heap (沒有叢集索引)，則分葉節點存放的是指向Table鍵值與欄位鍵值的Row Id(資料列的指標)。

2.如果資料表有叢集索引，則資料列定位器為資料列的叢集索引鍵，

SQL Server 藉由使用非叢集索引之分葉資料列所儲存的叢集索引鍵來搜尋叢集索引，以便擷取資料列。

第一種狀況:查詢條件選擇性不高。

--create Nonclustered Indexes

create index idx_1 on dbo.sale(SalesOrderDetailID)

go

select * from dbo.sale t where t.SalesOrderDetailID >600 and t.SalesOrderDetailID<700

可以看到執行計畫完全不使用非叢集索引，全部的查詢成本都在RID查詢(Heap)。

第二種狀況:已存在叢集索引

--刪除非叢集索引

drop index idx_1 on dbo.sale

--建立叢集索引
--注意!請先建立叢集索引
--在建立非叢集索引
--否則會耗費大量建立時間

CREATE UNIQUE CLUSTERED INDEX idx_1 ON dbo.sale(SalesOrderDetailID);

--建立非叢集索引

CREATE nonclustered index idx_2 on dbo.sale(ProductID) 
go

select * from dbo.sale t where t.ProductID >=500 and t.ProductID<=700