簇聚和非簇聚索引

1、在數(shù)據(jù)庫原理里面，對聚簇索引的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的解釋是:索引順序與數(shù)據(jù)物理排列順序無關(guān)。正式因為如此，所以一個表最多只能有一個聚簇索引。不過這個定義太抽象了。在SQL Server中，索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點就是數(shù)據(jù)節(jié)點。而非聚簇索引的葉節(jié)點仍然是索引節(jié)點，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。如下圖：非聚簇索引 聚簇索引聚簇索引與非聚簇索引的本質(zhì)區(qū)別到底是什么？什么時候用聚簇索引，什么時候用非聚簇索引？這是一個很復(fù)雜的問題，很難用三言兩語說清楚。我在這里從SQL Server索引優(yōu)化查詢的角度

2、簡單談?wù)?如果對這方面感興趣的話，可以讀一讀微軟出版的Microsoft SQL Server 2000數(shù)據(jù)庫編程第3單元的數(shù)據(jù)結(jié)構(gòu)引論以及第6、13、14單元)。一、索引塊與數(shù)據(jù)塊的區(qū)別大家都知道，索引可以提高檢索效率，因為它的二叉樹結(jié)構(gòu)以及占用空間小，所以訪問速度塊。讓我們來算一道數(shù)學(xué)題：如果表中的一條記錄在磁盤上占用1000字節(jié)的話，我們對其中10字節(jié)的一個字段建立索引，那么該記錄對應(yīng)的索引塊的大小只有10字節(jié)。我們知道，SQL Server的最小空間分配單元是“頁（Page）”，一個頁在磁盤上占用8K空間，那么這一個頁可以存儲上述記錄8條，但可以存儲索引800條?，F(xiàn)在我們要從一個有80

3、00條記錄的表中檢索符合某個條件的記錄，如果沒有索引的話，我們可能需要遍歷8000條×1000字節(jié)/8K字節(jié)=1000個頁面才能夠找到結(jié)果。如果在檢索字段上有上述索引的話，那么我們可以在8000條×10字節(jié)/8K字節(jié)=10個頁面中就檢索到滿足條件的索引塊，然后根據(jù)索引塊上的指針逐一找到結(jié)果數(shù)據(jù)塊，這樣IO訪問量要少的多。二、索引優(yōu)化技術(shù)是不是有索引就一定檢索的快呢？答案是否。有些時候用索引還不如不用索引快。比如說我們要檢索上述表中的所有記錄，如果不用索引，需要訪問8000條×1000字節(jié)/8K字節(jié)=1000個頁面，如果使用索引的話，首先檢索索引，訪問8000條&#

4、215;10字節(jié)/8K字節(jié)=10個頁面得到索引檢索結(jié)果，再根據(jù)索引檢索結(jié)果去對應(yīng)數(shù)據(jù)頁面，由于是檢索所有數(shù)據(jù)，所以需要再訪問8000條×1000字節(jié)/8K字節(jié)=1000個頁面將全部數(shù)據(jù)讀取出來，一共訪問了1010個頁面，這顯然不如不用索引快。SQL Server內(nèi)部有一套完整的數(shù)據(jù)檢索優(yōu)化技術(shù)，在上述情況下，SQL Server的查詢計劃（Search Plan）會自動使用表掃描的方式檢索數(shù)據(jù)而不會使用任何索引。那么SQL Server是怎么知道什么時候用索引，什么時候不用索引的呢？SQL Server除了日常維護數(shù)據(jù)信息外，還維護著數(shù)據(jù)統(tǒng)計信息，下圖是數(shù)據(jù)庫屬性頁面的一個截圖：從圖

5、中我們可以看到，SQL Server自動維護統(tǒng)計信息，這些統(tǒng)計信息包括數(shù)據(jù)密度信息以及數(shù)據(jù)分布信息，這些信息幫助SQL Server決定如何制定查詢計劃以及查詢是是否使用索引以及使用什么樣的索引（這里就不再解釋它們到底如何幫助SQL Server建立查詢計劃的了）。我們還是來做個實驗。建立一張表：tabTest(ID, unqValue，intValue)，其中ID是整形自動編號主索引，unqValue是uniqueidentifier類型，在上面建立普通索引，intValue 是整形，不建立索引。之所以掛上一個沒有索引的intValue字段，就是防止SQL Server使用索引覆蓋查詢優(yōu)化技

6、術(shù)，這樣實驗就起不到作用了。向表中錄入10000條隨機記錄，代碼如下：CREATE TABLE dbo.tabTest ( ID int IDENTITY (1, 1) NOT NULL , unqValue uniqueidentifier NOT NULL , intValue int NOT NULL ) ON PRIMARYGOALTER TABLE

7、 dbo.tabTest WITH NOCHECK ADD  CONSTRAINT PK_tabTest PRIMARY KEY  CLUSTERED  (  ID )  ON PRIMARY GOALTER TABLE dbo.tabTest ADD  CONSTRAINT DF_tabTest_unqValue DEFAUL

8、T (newid() FOR unqValueGOCREATE  INDEX IX_tabTest_unqValue ON dbo.tabTest(unqValue) ON PRIMARYGOdeclare i intdeclare v intset i=0while i<10000begin    set v=rand()*1000    

9、    insert into tabTest (intValue) values (v)    set i=i+1end然后我們執(zhí)行兩個查詢并查看執(zhí)行計劃，如圖：（在查詢分析器的查詢菜單中可以打開查詢計劃，同時圖上第一個查詢的GUID是我從數(shù)據(jù)庫中找的，大家做實驗的時候可以根據(jù)自己數(shù)據(jù)庫中的值來定）：從圖中可以看出，在第一個查詢中，SQL Server使用了IX_tabTest_unqValue索引，根據(jù)箭頭方向，計算機先在索引范圍內(nèi)找，找到后，使用Bo

10、okmark Lookup將索引節(jié)點映射到數(shù)據(jù)節(jié)點上，最后給出SELECT結(jié)果。在第二個查詢中，系統(tǒng)直接遍歷表給出結(jié)果，不過它使用了聚簇索引，為什么呢？不要忘了，聚簇索引的頁節(jié)點就是數(shù)據(jù)節(jié)點！這樣使用聚簇索引會更快一些（不受數(shù)據(jù)刪除、更新留下的存儲空洞的影響，直接遍歷數(shù)據(jù)是要跳過這些空洞的）。下面，我們在SQL Server中將ID字段的聚簇索引更改為非聚簇索引，然后再執(zhí)行select * from tabTest，這回我們看到的執(zhí)行計劃變成了：SQL Server沒有使用任何索引，而是直接執(zhí)行了Table Scan，因為只有這樣，檢索效率才是最高的。三、聚簇索引與非聚簇索引的本質(zhì)區(qū)別現(xiàn)在可以

11、討論聚簇索引與非聚簇索引的本質(zhì)區(qū)別了。正如本文最前面的兩個圖所示，聚簇索引的葉節(jié)點就是數(shù)據(jù)節(jié)點，而非聚簇索引的頁節(jié)點仍然是索引檢點，并保留一個鏈接指向?qū)?yīng)數(shù)據(jù)塊。還是通過一道數(shù)學(xué)題來看看它們的區(qū)別吧：假設(shè)有一8000條記錄的表，表中每條記錄在磁盤上占用1000字節(jié)，如果在一個10字節(jié)長的字段上建立非聚簇索引主鍵，需要二叉樹節(jié)點16000個（這16000個節(jié)點中有8000個葉節(jié)點，每個頁節(jié)點都指向一個數(shù)據(jù)記錄），這樣數(shù)據(jù)將占用8000條×1000字節(jié)/8K字節(jié)=1000個頁面；索引將占用16000個節(jié)點×10字節(jié)/8K字節(jié)=20個頁面，共計1020個頁面。同樣一張表，如果我們

12、在對應(yīng)字段上建立聚簇索引主鍵，由于聚簇索引的頁節(jié)點就是數(shù)據(jù)節(jié)點，所以索引節(jié)點僅有8000個，占用10個頁面，數(shù)據(jù)仍然占有1000個頁面。下面我們看看在執(zhí)行插入操作時，非聚簇索引的主鍵為什么比聚簇索引主鍵要快。主鍵約束要求主鍵不能出現(xiàn)重復(fù)，那么SQL Server是怎么知道不出現(xiàn)重復(fù)的呢？唯一的方法就是檢索。對于非聚簇索引，只需要檢索20個頁面中的16000個節(jié)點就知道是否有重復(fù)，因為所有主鍵鍵值在這16000個索引節(jié)點中都包含了。但對于聚簇索引，索引節(jié)點僅僅包含了8000個中間節(jié)點，至于會不會出現(xiàn)重復(fù)必須檢索另外1000個頁數(shù)據(jù)節(jié)點才知道，那么相當(dāng)于檢索10+1000=1010個頁面才知道是否有重復(fù)。所以聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多。讓我們再來看看數(shù)據(jù)檢索的效率，如果對上述兩表進行檢索，在使用索引的情況下（有些時候SQL Server執(zhí)行計劃會選擇不使用索引，不過我們這里姑且假設(shè)一定使用索引），對于聚簇索引檢索，我們可能會訪問10個索引頁面外加1000個數(shù)據(jù)頁面得到結(jié)果（實際情況要比這個好），而對于非聚簇索引，系統(tǒng)會從20個頁面中找到符合條件的節(jié)點，再映射到1000個數(shù)據(jù)頁面上（這也是最糟糕的情況），比較一下，一個訪問了1010個頁面而另一個訪問了1020個頁面，可見檢索