聚集索引和非聚集索引

《聚集索引和非聚集索引》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。

1、聚集索引的區(qū)別　　聚集索引:物理存儲(chǔ)按照索引排序　　非聚集索引:物理存儲(chǔ)不按照索引排序優(yōu)勢(shì)與缺點(diǎn)聚集索引：插入數(shù)據(jù)時(shí)速度要慢（時(shí)間花費(fèi)在“物理存儲(chǔ)的排序”上，也就是首先要找到位置然后插入）,查詢數(shù)據(jù)比非聚集數(shù)據(jù)的速度快聚集索引的區(qū)別　　聚集索引:物理存儲(chǔ)按照索引排序　　非聚集索引:物理存儲(chǔ)不按照索引排序優(yōu)勢(shì)與缺點(diǎn)聚集索引：插入數(shù)據(jù)時(shí)速度要慢（時(shí)間花費(fèi)在“物理存儲(chǔ)的排序”上，也就是首先要找到位置然后插入）,查詢數(shù)據(jù)比非聚集數(shù)據(jù)的速度快索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)

2、仍然是索引節(jié)點(diǎn)，只不過有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。如下圖：非聚集索引聚集索引一、索引塊與數(shù)據(jù)塊的區(qū)別大家都知道，索引可以提高檢索效率，因?yàn)樗亩鏄浣Y(jié)構(gòu)以及占用空間小，所以訪問速度塊。讓我們來算一道數(shù)學(xué)題：如果表中的一條記錄在磁盤上占用1000字節(jié)的話，我們對(duì)其中10字節(jié)的一個(gè)字段建立索引，那么該記錄對(duì)應(yīng)的索引塊的大小只有10字節(jié)。我們知道，SQLServer的最小空間分配單元是“頁(yè)（Page）”，一個(gè)頁(yè)在磁盤上占用8K空間，那么這一個(gè)頁(yè)可以存儲(chǔ)上述記錄8條，但可以存儲(chǔ)索引800條。現(xiàn)在我們要從一個(gè)有8000條記錄的表中檢索符合某個(gè)條件的

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

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

5、面的一個(gè)截圖：聚簇索引與非聚簇索引的本質(zhì)區(qū)別到底是什么？什么時(shí)候用聚簇索引，什么時(shí)候用非聚簇索引？這是一個(gè)很復(fù)雜的問題，很難用三言兩語(yǔ)說清楚。我在這里從SQLServer索引優(yōu)化查詢的角度簡(jiǎn)單談?wù)?如果對(duì)這方面感興趣的話，可以讀一讀微軟出版的《MicrosoftSQLServer2000數(shù)據(jù)庫(kù)編程》第3單元的數(shù)據(jù)結(jié)構(gòu)引論以及第6、13、14單元)。從圖中我們可以看到，SQLServer自動(dòng)維護(hù)統(tǒng)計(jì)信息，這些統(tǒng)計(jì)信息包括數(shù)據(jù)密度信息以及數(shù)據(jù)分布信息，這些信息幫助SQLServer決定如何制定查詢計(jì)劃以及查詢是是否使用索引以及使用什么樣的索引

6、（這里就不再解釋它們到底如何幫助SQLServer建立查詢計(jì)劃的了）。我們還是來做個(gè)實(shí)驗(yàn)。建立一張表：tabTest(ID,unqValue，intValue)，其中ID是整形自動(dòng)編號(hào)主索引，unqValue是uniqueidentifier類型，在上面建立普通索引，intValue是整形，不建立索引。之所以掛上一個(gè)沒有索引的intValue字段，就是防止SQLServer使用索引覆蓋查詢優(yōu)化技術(shù)，這樣實(shí)驗(yàn)就起不到作用了。向表中錄入10000條隨機(jī)記錄，代碼如下：Code然后我們執(zhí)行兩個(gè)查詢并查看執(zhí)行計(jì)劃，如圖：（在查詢分析器的查詢菜單中

7、可以打開查詢計(jì)劃，同時(shí)圖上第一個(gè)查詢的GUID是我從數(shù)據(jù)庫(kù)中找的，大家做實(shí)驗(yàn)的時(shí)候可以根據(jù)自己數(shù)據(jù)庫(kù)中的值來定）：從圖中可以看出，在第一個(gè)查詢中，SQLServer使用了IX_tabTest_unqValue索引，根據(jù)箭頭方向，計(jì)算機(jī)先在索引范圍內(nèi)找，找到后，使用BookmarkLookup將索引節(jié)點(diǎn)映射到數(shù)據(jù)節(jié)點(diǎn)上，最后給出SELECT結(jié)果。在第二個(gè)查詢中，系統(tǒng)直接遍歷表給出結(jié)果，不過它使用了聚簇索引，為什么呢？不要忘了，聚簇索引的頁(yè)節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)！這樣使用聚簇索引會(huì)更快一些（不受數(shù)據(jù)刪除、更新留下的存儲(chǔ)空洞的影響，直接遍歷數(shù)據(jù)是要跳

8、過這些空洞的）。下面，我們?cè)赟QLServer中將ID字段的聚簇索引更改為非聚簇索引，然后再執(zhí)行select*fromtabTest，這回我們看到的執(zhí)行計(jì)劃變成了：SQLServer沒有使用任