2016年12月1日 星期四

SQL SERVER的索引鍵查閱(key lookup)


當對一個table建立clustered-index時,如果是non-unique,那麼除了key欄位之外,系統還會自動加上一個 uniquifier欄位。uniquifier是一個流水編號,從0開始。它用於當clustered-index欄位不是唯一時,加上這個編號,以識別出唯一性。如果是unique clustered-index,則不會有uniquifier。

對一個cluetred table建立索引時,系統會自動在索引鍵欄位後,加上「clustered index欄位(若有uniquifier也會加上)」,以取代heap table時所用的RID。。

舉例來看:
首先建立一個table,並建立nonunique clustered-index
create table testtbl(c1 int identity,c2 int,c3 varchar(1000))
go
create clustered index idx1 on testtbl(c1)
go
create index idx2 on testtbl(c2)
go
insert into testtbl values(1,'abcdefg')
go 1000  à新增1000筆資料

檢查idx1的頁面,由於是non-unique clustered index,可以看到系統自動加上了一個欄位「uniquifier」















再檢查idx2的頁面,我們可以發現,在C2這個key欄位後面,系統會自動的將clustered的key欄位加進來















如果索引是建立在clustered table之上,那麼clustered index的key欄位,會自動會附加在新建索引的key欄位中
create index idx2 on testtbl(c2)
相當於
create index idx2 on testtbl(c2,c1)
c1為clustered index欄位,由系統自動附加在一般索引之後。

我們執行下列查詢語法:
select c1,c2 from testtbl with(index(idx2)) where c2=30
強制系統走idx2,由於idx2只以c2為key建立索引,如果是在一般的heap table上,由於select c1,c2兩個欄位,所以應該會形成RID查閱。但在clustered table中,則因clustered key欄位自動附加,所以idx2這個index事實上已經包含c2,c1兩個欄位,因此上面這句查詢語法,不會有「查閱」的動作。如下圖所示。

















接著我們修改一下查詢語法
select c1,c2,c3 from testtbl with(index(idx2)) where c2=30
c3欄位,不在idx2的資料內,所以會透過「Clustered key」(也就是c1+uniquifier),至來源Table取得c3的資料。




















結語
一、在clustered table上,不會有RID查閱,取而代之的是「索引鍵查閱」。兩者的用處一樣,都是為了至來源tabel取得索引中未涵蓋的資料。索引鍵查閱的好處在於,如果table資料的實體儲存位址有變更,在其它index中是不必跟著變動的,因為它不再是紀錄RID
二、如果clustered index不是唯一(non-unique),系統會自動加上uniquifier,以建立其唯一性。
三、clustered indexkey,會自動附加在其它的一般索引之後。
如果有一個叢集索引idx1
create clustered index idx1 on testtbl(c1)
底下兩句建立索引的指令,其實是一模一樣的
create index idx2 on testtbl(c2)   à即使不指定,系統也會自動附加c1欄位
等於:create index idx2 on testtbl(c2,c1)