聚集索引和非聚集索引[转]
? 作者：min10??來源：博客園??時(shí)間：2008-12-22??閱讀：562 次??原文鏈接?? [收藏]??
聚集索引的區(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)仍然是索引節(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é)。我們知道，SQL Server的最小空間分配單元是“頁（Page）”，一個(gè)頁在磁盤上占用8K空間，那么這一個(gè)頁可以存儲(chǔ)上述記錄8條，但可以存儲(chǔ)索引800條。現(xiàn)在我們要從一個(gè)有8000條記錄的表中檢索符合某個(gè)條件的記錄，如果沒有索引的話，我們可能需要遍歷8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面才能夠找到結(jié)果。如果在檢索字段上有上述索引的話，那么我們可以在8000條×10字節(jié)/8K字節(jié)=10個(gè)頁面中就檢索到滿足條件的索引塊，然后根據(jù)索引塊上的指針逐一找到結(jié)果數(shù)據(jù)塊，這樣IO訪問量要少的多。
二、索引優(yōu)化技術(shù)
是不是有索引就一定檢索的快呢？答案是否。有些時(shí)候用索引還不如不用索引快。比如說我們要檢索上述表中的所有記錄，如果不用索引，需要訪問8000條×1000 字節(jié)/8K字節(jié)=1000個(gè)頁面，如果使用索引的話，首先檢索索引，訪問8000條×10字節(jié)/8K字節(jié)=10個(gè)頁面得到索引檢索結(jié)果，再根據(jù)索引檢索結(jié)果去對(duì)應(yīng)數(shù)據(jù)頁面，由于是檢索所有數(shù)據(jù)，所以需要再訪問8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面將全部數(shù)據(jù)讀取出來，一共訪問了1010個(gè)頁面，這顯然不如不用索引快。
SQL Server內(nèi)部有一套完整的數(shù)據(jù)檢索優(yōu)化技術(shù)，在上述情況下，SQL Server的查詢計(jì)劃（Search Plan）會(huì)自動(dòng)使用表掃描的方式檢索數(shù)據(jù)而不會(huì)使用任何索引。那么SQL Server是怎么知道什么時(shí)候用索引，什么時(shí)候不用索引的呢？SQL Server除了日常維護(hù)數(shù)據(jù)信息外，還維護(hù)著數(shù)據(jù)統(tǒng)計(jì)信息，下圖是數(shù)據(jù)庫屬性頁面的一個(gè)截圖：
聚簇索引與非聚簇索引的本質(zhì)區(qū)別到底是什么？什么時(shí)候用聚簇索引，什么時(shí)候用非聚簇索引？
這是一個(gè)很復(fù)雜的問題，很難用三言兩語說清楚。我在這里從SQL Server索引優(yōu)化查詢的角度簡單談?wù)?如果對(duì)這方面感興趣的話，可以讀一讀微軟出版的《Microsoft SQL Server 2000數(shù)據(jù)庫編程》第3單元的數(shù)據(jù)結(jié)構(gòu)引論以及第6、13、14單元)。
從圖中我們可以看到，SQL Server自動(dòng)維護(hù)統(tǒng)計(jì)信息，這些統(tǒng)計(jì)信息包括數(shù)據(jù)密度信息以及數(shù)據(jù)分布信息，這些信息幫助SQL Server決定如何制定查詢計(jì)劃以及查詢是是否使用索引以及使用什么樣的索引（這里就不再解釋它們到底如何幫助SQL Server建立查詢計(jì)劃的了）。我們還是來做個(gè)實(shí)驗(yàn)。建立一張表：tabTest(ID, unqValue，intValue)，其中ID是整形自動(dòng)編號(hào)主索引，unqValue是uniqueidentifier類型，在上面建立普通索引，intValue 是整形，不建立索引。之所以掛上一個(gè)沒有索引的intValue字段，就是防止SQL Server使用索引覆蓋查詢優(yōu)化技術(shù)，這樣實(shí)驗(yàn)就起不到作用了。向表中錄入10000條隨機(jī)記錄，代碼如下：
CREATE TABLE [dbo].[tabTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[unqValue] [uniqueidentifier] NOT NULL ,
[intValue] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD
CONSTRAINT [PK_tabTest] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] ADD
CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR [unqValue]
GO
CREATE INDEX [IX_tabTest_unqValue] ON [dbo].[tabTest]([unqValue]) ON [PRIMARY]
GO
declare @i int
declare @v int
set @i=0
while @i<10000
begin
set @v=rand()*1000
insert into tabTest ([intValue]) values (@v)
set @i=@i+1
end
然后我們執(zhí)行兩個(gè)查詢并查看執(zhí)行計(jì)劃，如圖：（在查詢分析器的查詢菜單中可以打開查詢計(jì)劃，同時(shí)圖上第一個(gè)查詢的GUID是我從數(shù)據(jù)庫中找的，大家做實(shí)驗(yàn)的時(shí)候可以根據(jù)自己數(shù)據(jù)庫中的值來定）：
?從圖中可以看出，在第一個(gè)查詢中，SQL Server使用了IX_tabTest_unqValue索引，根據(jù)箭頭方向，計(jì)算機(jī)先在索引范圍內(nèi)找，找到后，使用Bookmark Lookup將索引節(jié)點(diǎn)映射到數(shù)據(jù)節(jié)點(diǎn)上，最后給出SELECT結(jié)果。在第二個(gè)查詢中，系統(tǒng)直接遍歷表給出結(jié)果，不過它使用了聚簇索引，為什么呢？不要忘了，聚簇索引的頁節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)！這樣使用聚簇索引會(huì)更快一些（不受數(shù)據(jù)刪除、更新留下的存儲(chǔ)空洞的影響，直接遍歷數(shù)據(jù)是要跳過這些空洞的）。
下面，我們?cè)赟QL Server中將ID字段的聚簇索引更改為非聚簇索引，然后再執(zhí)行select * from tabTest，這回我們看到的執(zhí)行計(jì)劃變成了：
SQL Server沒有使用任何索引，而是直接執(zhí)行了Table Scan，因?yàn)橹挥羞@樣，檢索效率才是最高的。
三、聚簇索引與非聚簇索引的本質(zhì)區(qū)別
現(xiàn)在可以討論聚簇索引與非聚簇索引的本質(zhì)區(qū)別了。正如本文最前面的兩個(gè)圖所示，聚簇索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚簇索引的頁節(jié)點(diǎn)仍然是索引檢點(diǎn)，并保留一個(gè)鏈接指向?qū)?yīng)數(shù)據(jù)塊。
還是通過一道數(shù)學(xué)題來看看它們的區(qū)別吧：假設(shè)有一8000條記錄的表，表中每條記錄在磁盤上占用1000字節(jié)，如果在一個(gè)10字節(jié)長的字段上建立非聚簇索引主鍵，需要二叉樹節(jié)點(diǎn)16000個(gè)（這16000個(gè)節(jié)點(diǎn)中有8000個(gè)葉節(jié)點(diǎn)，每個(gè)頁節(jié)點(diǎn)都指向一個(gè)數(shù)據(jù)記錄），這樣數(shù)據(jù)將占用8000條×1000字節(jié) /8K字節(jié)=1000個(gè)頁面；索引將占用16000個(gè)節(jié)點(diǎn)×10字節(jié)/8K字節(jié)=20個(gè)頁面，共計(jì)1020個(gè)頁面。
同樣一張表，如果我們?cè)趯?duì)應(yīng)字段上建立聚簇索引主鍵，由于聚簇索引的頁節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，所以索引節(jié)點(diǎn)僅有8000個(gè)，占用10個(gè)頁面，數(shù)據(jù)仍然占有1000個(gè)頁面。
下面我們看看在執(zhí)行插入操作時(shí)，非聚簇索引的主鍵為什么比聚簇索引主鍵要快。主鍵約束要求主鍵不能出現(xiàn)重復(fù)，那么SQL Server是怎么知道不出現(xiàn)重復(fù)的呢？唯一的方法就是檢索。對(duì)于非聚簇索引，只需要檢索20個(gè)頁面中的16000個(gè)節(jié)點(diǎn)就知道是否有重復(fù)，因?yàn)樗兄麈I鍵值在這16000個(gè)索引節(jié)點(diǎn)中都包含了。但對(duì)于聚簇索引，索引節(jié)點(diǎn)僅僅包含了8000個(gè)中間節(jié)點(diǎn)，至于會(huì)不會(huì)出現(xiàn)重復(fù)必須檢索另外8000個(gè)頁數(shù)據(jù)節(jié)點(diǎn)才知道，那么相當(dāng)于檢索10+1000=1010個(gè)頁面才知道是否有重復(fù)。所以聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多。
讓我們?cè)賮砜纯磾?shù)據(jù)檢索的效率，如果對(duì)上述兩表進(jìn)行檢索，在使用索引的情況下（有些時(shí)候SQL Server執(zhí)行計(jì)劃會(huì)選擇不使用索引，不過我們這里姑且假設(shè)一定使用索引），對(duì)于聚簇索引檢索，我們可能會(huì)訪問10個(gè)索引頁面外加1000個(gè)數(shù)據(jù)頁面得到結(jié)果（實(shí)際情況要比這個(gè)好），而對(duì)于非聚簇索引，系統(tǒng)會(huì)從20個(gè)頁面中找到符合條件的節(jié)點(diǎn)，再映射到1000個(gè)數(shù)據(jù)頁面上（這也是最糟糕的情況），比較一下，一個(gè)訪問了1010個(gè)頁面而另一個(gè)訪問了1020個(gè)頁面，可見檢索效率差異并不是很大。所以不管非聚簇索引也好還是聚簇索引也好，都適合排序，聚簇索引僅僅比非聚簇索引快一點(diǎn)。
結(jié)語
關(guān)于聚簇索引與非聚簇索引效率問題的實(shí)驗(yàn)就不做了，感興趣的話可以自己使用查詢分析器對(duì)查詢計(jì)劃進(jìn)行分析。SQL Server是一個(gè)很復(fù)雜的系統(tǒng)，尤其是索引以及查詢優(yōu)化技術(shù)，Oracle就更復(fù)雜了。了解索引以及查詢背后的事情不是什么壞事，它可以幫助我們更為深刻的了解我們的系統(tǒng)。
-------------------------------------
非聚簇對(duì)于更新肯定是有優(yōu)勢(shì)的
而它在檢索的性能損失也不會(huì)太大
所以能不用聚簇當(dāng)然是最好的了
但是如果使用\order by的話
聚簇的優(yōu)勢(shì)也應(yīng)該是很明顯的
-------------------------------------
?? 索引有兩種類型：聚簇索引和非聚簇索引。
在聚簇索引中，索引樹的葉級(jí)頁包含實(shí)際的數(shù)據(jù)：記錄的索引順序與物理順序相同。
在非聚簇索引中，葉級(jí)頁指向表中的記錄：記錄的物理順序與邏輯順序沒有必然的聯(lián)系。
聚簇索引非常象目錄表，目錄表的順序與實(shí)際的頁碼順序是一致的。非聚簇索引則更象書的標(biāo)準(zhǔn)索引表，索引表中的順序通常與實(shí)際的頁碼順序是不一致的。一本書也許有多個(gè)索引。例如，它也許同時(shí)有主題索引和作者索引。同樣，一個(gè)表可以有多個(gè)非聚簇索引。
通常情況下，你使用的是聚簇索引，但是你應(yīng)該對(duì)兩種類型索引的優(yōu)缺點(diǎn)都有所理解。
每個(gè)表只能有一個(gè)聚簇索引，因?yàn)橐粋€(gè)表中的記錄只能以一種物理順序存放。通常你要對(duì)一個(gè)表按照標(biāo)識(shí)字段建立聚簇索引。但是，你也可以對(duì)其它類型的字段建立聚簇索引，如字符型，數(shù)值型和日期時(shí)間型字段。
從建立了聚簇索引的表中取出數(shù)據(jù)要比建立了非聚簇索引的表快。當(dāng)你需要取出一定范圍內(nèi)的數(shù)據(jù)時(shí)，用聚簇索引也比用非聚簇索引好。例如，假設(shè)你用一個(gè)表來記錄訪問者在你網(wǎng)點(diǎn)上的活動(dòng)。如果你想取出在一定時(shí)間段內(nèi)的登錄信息，你應(yīng)該對(duì)這個(gè)表的DATETIME型字段建立聚簇索引。
對(duì)聚簇索引的主要限制是每個(gè)表只能建立一個(gè)聚簇索引。但是，一個(gè)表可以有不止一個(gè)非聚簇索引。實(shí)際上，對(duì)每個(gè)表你最多可以建立249個(gè)非聚簇索引。你也可以對(duì)一個(gè)表同時(shí)建立聚簇索引和非聚簇索引。
假如你不僅想根據(jù)日期，而且想根據(jù)用戶名從你的網(wǎng)點(diǎn)活動(dòng)日志中取數(shù)據(jù)。在這種情況下，同時(shí)建立一個(gè)聚簇索引和非聚簇索引是有效的。你可以對(duì)日期時(shí)間字段建立聚簇索引，對(duì)用戶名字段建立非聚簇索引。如果你發(fā)現(xiàn)你需要更多的索引方式，你可以增加更多的非聚簇索引。
非聚簇索引需要大量的硬盤空間和內(nèi)存。另外，雖然非聚簇索引可以提高從表中取數(shù)據(jù)的速度，它也會(huì)降低向表中插入和更新數(shù)據(jù)的速度。每當(dāng)你改變了一個(gè)建立了非聚簇索引的表中的數(shù)據(jù)時(shí)，必須同時(shí)更新索引。因此你對(duì)一個(gè)表建立非聚簇索引時(shí)要慎重考慮。如果你預(yù)計(jì)一個(gè)表需要頻繁地更新數(shù)據(jù)，那么不要對(duì)它建立太多非聚簇索引。另外，如果硬盤和內(nèi)存空間有限，也應(yīng)該限制使用非聚簇索引的數(shù)量。
索引屬性
這兩種類型的索引都有兩個(gè)重要屬性：
你可以用兩者中任一種類型同時(shí)對(duì)多個(gè)字段建立索引（復(fù)合索引）；
兩種類型的索引都可以指定為唯一索引。
你可以對(duì)多個(gè)字段建立一個(gè)復(fù)合索引，甚至是復(fù)合的聚簇索引。假如有一個(gè)表記錄了你的網(wǎng)點(diǎn)訪問者的姓和名字。如果你希望根據(jù)完整姓名從表中取數(shù)據(jù)，你需要建立一個(gè)同時(shí)對(duì)姓字段和名字字段進(jìn)行的索引。這和分別對(duì)兩個(gè)字段建立單獨(dú)的索引是不同的。當(dāng)你希望同時(shí)對(duì)不止一個(gè)字段進(jìn)行查詢時(shí)，你應(yīng)該建立一個(gè)對(duì)多個(gè)字段的索引。如果你希望對(duì)各個(gè)字段進(jìn)行分別查詢，你應(yīng)該對(duì)各字段建立獨(dú)立的索引。
兩種類型的索引都可以被指定為唯一索引。如果對(duì)一個(gè)字段建立了唯一索引，你將不能向這個(gè)字段輸入重復(fù)的值。一個(gè)標(biāo)識(shí)字段會(huì)自動(dòng)成為唯一值字段，但你也可以對(duì)其它類型的字段建立唯一索引。假設(shè)你用一個(gè)表來保存你的網(wǎng)點(diǎn)的用戶密碼，你當(dāng)然不希望兩個(gè)用戶有相同的密碼。通過強(qiáng)制一個(gè)字段成為唯一值字段，你可以防止這種情況的發(fā)生。?
http://hi.baidu.com/guobeilei/blog/item/51f55afbda311e116c22eb0e.html
?
聚集索引基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲(chǔ)這些數(shù)據(jù)行。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序存儲(chǔ)。有關(guān)聚集索引體系結(jié)構(gòu)的詳細(xì)信息，請(qǐng)參閱聚集索引結(jié)構(gòu)。
每個(gè)表幾乎都對(duì)列定義聚集索引來實(shí)現(xiàn)下列功能：
- 可用于經(jīng)常使用的查詢。
- 提供高度唯一性。
注意： 創(chuàng)建 PRIMARY KEY 約束時(shí)，將在列上自動(dòng)創(chuàng)建唯一索引。默認(rèn)情況下，此索引是聚集索引，但是在創(chuàng)建約束時(shí)，可以指定創(chuàng)建非聚集索引。 ?
?
- 可用于范圍查詢。
如果未使用 UNIQUE 屬性創(chuàng)建聚集索引，數(shù)據(jù)庫引擎將向表自動(dòng)添加一個(gè) 4 字節(jié)的 uniqueifier 列。必要時(shí)，數(shù)據(jù)庫引擎將向行自動(dòng)添加一個(gè) uniqueifier 值以使每個(gè)鍵唯一。此列和列值供內(nèi)部使用，用戶不能查看或訪問。
查詢注意事項(xiàng)
在創(chuàng)建聚集索引之前，應(yīng)先了解數(shù)據(jù)是如何被訪問的。考慮對(duì)具有以下特點(diǎn)的查詢使用聚集索引：
- 使用運(yùn)算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。
使用聚集索引找到包含第一個(gè)值的行后，便可以確保包含后續(xù)索引值的行物理相鄰。例如，如果某個(gè)查詢?cè)谝幌盗袖N售訂單號(hào)間檢索記錄，SalesOrderNumber 列的聚集索引可快速定位包含起始銷售訂單號(hào)的行，然后檢索表中所有連續(xù)的行，直到檢索到最后的銷售訂單號(hào)。 - 返回大型結(jié)果集。
- 使用 JOIN 子句；一般情況下，使用該子句的是外鍵列。
- 使用 ORDER BY 或 GROUP BY 子句。
在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使數(shù)據(jù)庫引擎不必對(duì)數(shù)據(jù)進(jìn)行排序，因?yàn)檫@些行已經(jīng)排序。這樣可以提高查詢性能。
列注意事項(xiàng)
一般情況下，定義聚集索引鍵時(shí)使用的列越少越好。考慮具有下列一個(gè)或多個(gè)屬性的列：
- 唯一或包含許多不重復(fù)的值
例如，雇員 ID 唯一地標(biāo)識(shí)雇員。EmployeeID 列的聚集索引或 PRIMARY KEY 約束將改善基于雇員 ID 號(hào)搜索雇員信息的查詢的性能。另外，可對(duì) LastName、FirstName、MiddleName 列創(chuàng)建聚集索引，因?yàn)榻?jīng)常以這種方式分組和查詢雇員記錄，而且這些列的組合還可提供高區(qū)分度。 - 按順序被訪問
例如，產(chǎn)品 ID 唯一地標(biāo)識(shí) AdventureWorks 數(shù)據(jù)庫的 Production.Product 表中的產(chǎn)品。在其中指定順序搜索的查詢（如 WHERE ProductID BETWEEN 980 and 999）將從 ProductID 的聚集索引受益。這是因?yàn)樾袑丛撴I列的排序順序存儲(chǔ)。 - 由于保證了列在表中是唯一的，所以定義為 IDENTITY。
- 經(jīng)常用于對(duì)表中檢索到的數(shù)據(jù)進(jìn)行排序。
按該列對(duì)表進(jìn)行聚集（即物理排序）是一個(gè)好方法，它可以在每次查詢?cè)摿袝r(shí)節(jié)省排序操作的成本。
聚集索引不適用于具有下列屬性的列：
- 頻繁更改的列
這將導(dǎo)致整行移動(dòng)，因?yàn)閿?shù)據(jù)庫引擎必須按物理順序保留行中的數(shù)據(jù)值。這一點(diǎn)要特別注意，因?yàn)樵诖笕萘渴聞?wù)處理系統(tǒng)中數(shù)據(jù)通常是可變的。 - 寬鍵
寬鍵是若干列或若干大型列的組合。所有非聚集索引將聚集索引中的鍵值用作查找鍵。為同一表定義的任何非聚集索引都將增大許多，這是因?yàn)榉蔷奂饕?xiàng)包含聚集鍵，同時(shí)也包含為此非聚集索引定義的鍵列。
索引選項(xiàng)
創(chuàng)建聚集索引時(shí)，可指定若干索引選項(xiàng)。因?yàn)榫奂饕ǔ６己艽?#xff0c;所以應(yīng)特別注意下列選項(xiàng)：
- SORT_IN_TEMPDB
- DROP_EXISTING
- FILLFACTOR
- ONLINE
轉(zhuǎn)載于:https://www.cnblogs.com/flystone/archive/2009/02/09/1386993.html
總結(jié)
以上是生活随笔為你收集整理的聚集索引和非聚集索引[转]的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ASP实例：利用缓存提高数据显示效率
- 下一篇： SqlHelper 数据库操作类