本文將介紹微軟SQL Server 2008索引的存儲(chǔ)結(jié)構(gòu)，包括索引的分類，什么是好的聚集鍵等等。希望本文能對(duì)大家有所幫助。

The Dependancy on the Clustering key

從上面的學(xué)習(xí)我們了解到：索引結(jié)構(gòu)被分為兩類索引組件：葉級(jí)(leaf level)和非葉級(jí)(non-leaf Level(s)) 。

一個(gè)聚集索引的葉級(jí)包含索引鍵和數(shù)據(jù)。“聚集索引的葉級(jí)除了鍵值還有什么？”答案是“其他的任何東東(everything else)”。也就是說(shuō)，表中的所有行的列都在一個(gè)聚集索引的葉級(jí)中。換種說(shuō)法是：當(dāng)一個(gè)聚集索引被創(chuàng)建的時(shí)候，數(shù)據(jù)(data)變成了聚集索引的葉級(jí)， 同 時(shí)，表中的數(shù)據(jù)被聚集鍵復(fù)制和排序。一旦被創(chuàng)建，一個(gè)聚集索引被邏輯維護(hù)而不是物理維護(hù)，排序被通過(guò)一個(gè)雙鏈列表（稱為頁(yè)鏈page Chain)維護(hù)。（注意：在一個(gè)堆(Heap)中頁(yè)是不會(huì)有任何方式互相鏈接的。 ）在頁(yè)鏈中的頁(yè)的排序和數(shù)據(jù)頁(yè)中行的排序，均是基于聚集 索引的定義。決定哪些列用于索引是一個(gè)重要的性能參考指標(biāo)。

由于數(shù)據(jù)頁(yè)的實(shí)際頁(yè)鏈只能被一種方式排序，所以一個(gè)表只能有一個(gè)聚集索引。而且，通常情況下，大多數(shù)在使用聚集索引的時(shí)候性能更佳。然而，聚集鍵需 要被慎重選擇，為了適當(dāng)選擇索引鍵，你必須理解索引是如何工作的。即聚集鍵的內(nèi)部依賴，特別是非聚集索引。

位于聚集鍵上的非聚集索引的依賴(dependency)在SQL Server 7.0時(shí)代存儲(chǔ)引擎被重新架構(gòu)時(shí)就已經(jīng)存在了，它最初是用于（當(dāng)使用一個(gè)非聚集索引去引用表中相應(yīng)的行時(shí)）行如何被標(biāo)識(shí)。如果一個(gè)表有聚集索引，行被聚集 鍵標(biāo)記(和查找）。如果沒(méi)有聚集索引，則行被物理行標(biāo)識(shí)(identifier RID)標(biāo)記（和查找）。查找相應(yīng)的數(shù)據(jù)行的處理就好像查找書的書簽。

非聚集索引僅僅包含索引定義的數(shù)據(jù) 。當(dāng)在一個(gè)非聚集索引中查找一行的時(shí)候，你通常不得不到實(shí)際的數(shù)據(jù)行以得到?jīng)]有包含在非聚集索引部 分的數(shù)據(jù)。為了檢索這部分附加數(shù)據(jù)，你必須深入表的內(nèi)部。

首先 ，最重要的是，所有聚集索引必須是惟一的。為什么必須是惟一的首要原因是非聚集索引項(xiàng)能夠準(zhǔn)確地指向一個(gè)特定的行?？紤]到如果一 個(gè)表被最后一個(gè)名字（last name)的不惟一的值聚集，問(wèn)題來(lái)了。如果一個(gè)非聚集索引存在于一個(gè)惟一的值，比如社會(huì)安全號(hào)（對(duì)我們而言就是身份證號(hào)），一個(gè)查詢社會(huì)安全號(hào)為 123-45-6789的索引，結(jié)果，聚集鍵是“Smith”,那么多個(gè)最后名字為Smith的行出現(xiàn)，到底是哪個(gè)？這個(gè)社會(huì)安全號(hào)為 123-45-6789的特定行，如何被有效定位？

為了聚集鍵能被有效使用，所有非聚集索引項(xiàng)必須準(zhǔn)確地指向一行。因?yàn)镾QL Server中的指針是聚集索引，所以，聚集索引必須是惟一的。如果你創(chuàng)建一個(gè)聚集索引時(shí)沒(méi)有使用Unique關(guān)鍵字，系統(tǒng)在必要時(shí)會(huì)自動(dòng)向這些行添加一 個(gè)惟一標(biāo)志列以確保內(nèi)部的惟一性。這個(gè)惟一標(biāo)志(uniquifier)是一個(gè)4字節(jié)的整數(shù)，當(dāng)行的聚集鍵不惟一時(shí)，自動(dòng)被回到數(shù)據(jù)行，一旦被添加，它變 成了聚集鍵的一部分，意味著它在每一個(gè)非聚集索引中被復(fù)制?！?/p>

第二 ，如果一個(gè)聚集鍵(Clustering key)被用于從一個(gè)非聚集索引到一個(gè)聚集索引的（數(shù)據(jù)）內(nèi)部查找相應(yīng)的數(shù)據(jù)行，那么,這個(gè)聚集鍵是表中最被過(guò)多使用的數(shù)據(jù)。所有列都組織索引鍵，這些 鍵被包含于每一個(gè)非聚集索引附加于實(shí)際數(shù)據(jù)行中。結(jié)果，索引鍵的寬度變得重要了?？紤]一個(gè)擁有12個(gè)非聚集索引和一百萬(wàn)數(shù)據(jù)的表的一個(gè)聚集索引擁有64字 節(jié)的聚集健。不計(jì)算內(nèi)部和結(jié)構(gòu)頭部，僅僅用于存儲(chǔ)在每一個(gè)非聚集索引鍵的索引鍵將是732MB,而如果聚集鍵只有8字節(jié)的話，這開銷是92MB，而如果4 字節(jié)的話，將只有46MB。盡管這只是一個(gè)粗略的估計(jì)，它表明如果你使用了一個(gè)過(guò)寬的聚集鍵，將浪費(fèi)大量的空間（以及潛在的緩 存池內(nèi)存）,性能的損耗是比較可觀的。因此，一個(gè)過(guò)寬的非聚集索引是應(yīng)該避免的。

第三，因?yàn)榫奂饕I是整個(gè)表中最冗余的數(shù)據(jù)，你應(yīng)該確信你的聚集鍵是不易變的(not volatile）,如果一個(gè)聚集健改變，它有以下幾個(gè)副作用：1、它會(huì)引起聚集索引內(nèi)的記錄的搬遷，造成頁(yè)page的分離與碎片。2、它引起每一個(gè)非聚 集索引被修改，以便于所有相關(guān)的非聚集索引的行的索引鍵的值被糾正。這既浪費(fèi)時(shí)間和空間，導(dǎo)致需要整理的碎片，增加了不必要的開銷（每個(gè)列重組聚集鍵）。

這三個(gè)屬性：惟一、窄和靜態(tài)也被用一個(gè)好的主鍵（但不總是）。因?yàn)槟阒挥幸粋€(gè)主鍵（僅僅一個(gè)聚集鍵）。SQL Server使用一個(gè)惟一的聚集索引以強(qiáng)化主鍵約束。然而，并不是每一個(gè)表的創(chuàng)建者（唉，別睡著了，說(shuō)你呢！）都知道這一點(diǎn)。于是，主鍵沒(méi)有被堅(jiān)持這些標(biāo) 準(zhǔn)(舉例，當(dāng)主鍵被選為數(shù)據(jù)的自然健(natural key)，即，如一個(gè)寬的7個(gè)列的100個(gè)字節(jié)組合)，那么使用一個(gè)聚集索引去強(qiáng)化惟一性，并在每一個(gè)非聚集索引復(fù)制完全的100字節(jié)的列組合將具有極強(qiáng) 的反作用。l因此，對(duì)一些不知情的（unsuspecting）數(shù)據(jù)庫(kù)開發(fā)人員（具體就是剛才睡覺(jué)的那幾位),一個(gè)非常寬的聚集健在創(chuàng)建表時(shí)就被系統(tǒng)不知 不覺(jué)的加上了，好消息是：你可以定義主鍵是非聚集索引，并且輕易地在一個(gè)不同的列上創(chuàng)建一個(gè)聚集索引 。當(dāng)然，你必須知道什么時(shí)候和怎樣做這 些。

最后，一個(gè)表的聚集鍵也應(yīng)當(dāng)以Insert時(shí)產(chǎn)生最小碎片為依據(jù)。盡管一旦聚集索引被創(chuàng)建后只有一個(gè)邏輯排序被維護(hù)，這個(gè)結(jié)構(gòu)的維護(hù)確實(shí)有開銷。如 果行需要持續(xù)地被插入在隨機(jī)點(diǎn)（如插入一個(gè)以last name排序的表中），那么這個(gè)表的邏輯排序的維護(hù)成本比總是插入一個(gè)以自增長(zhǎng)列排序的表（總是在表的末尾處）略微有些提高。

小結(jié)：表的聚集鍵應(yīng)該基于表的用途，同時(shí)也應(yīng)當(dāng)基于SQL Server在聚集健的內(nèi)部依賴。聚集鍵應(yīng)該是惟一的、窄的、靜態(tài)的，最好，不斷增長(zhǎng)？（ ever-increasing)。

好的聚集鍵的例子如下：

◆單個(gè)列鍵被定義為一個(gè)不斷增長(zhǎng)的自增長(zhǎng)列(如int 或bigint)

◆一個(gè)不斷增長(zhǎng)的日期列(date而不是datetime)，緊跟著一個(gè)惟一的行標(biāo)識(shí)（如自增長(zhǎng)列）組成一個(gè)復(fù)合鍵，這對(duì)基于日期分區(qū)的表非常有用。如SalesDate(8字 節(jié)）+SalesNumber(4字節(jié)）組合為12字節(jié)，注意在SQL Server 2008中，一個(gè)日期列(date)不包含時(shí)間。但是單獨(dú)的一個(gè)日期列(date)并不是一個(gè)好的聚集鍵，因?yàn)樗晃┮弧?/p>

◆一個(gè)GUID能被成功地用作一個(gè)聚集鍵，因?yàn)樗旧砭褪俏┮坏?。相?duì)的窄(12字節(jié)）,可能是靜態(tài)的。然而，僅僅在不斷增長(zhǎng)的模式下，GUID 才適合被用作聚集健。在一些情況下，GUID是在SQL Server的外部被生成或在SQL Server內(nèi)部用 NieID()生成，這種模式下產(chǎn)生的碎片抵消了這個(gè)列作為聚集健的作用。如果可能，請(qǐng)選擇NEWSEQUENTIALID()函數(shù)或選擇其他列。如果你 想用GUID作為一個(gè)主鍵，并且不是自增長(zhǎng)的，你可以把這個(gè)列作為一個(gè)非聚集索引代替聚集索引。

總之，沒(méi)有一個(gè)通用的最好的絕對(duì)有效的法則來(lái)決定一個(gè)聚集鍵的選擇。然而，一個(gè)表如果只有一個(gè)索引，并且是非聚集索引，那么在聚集健上的非聚集索引 依賴將不再相關(guān)。如果是聚集索引，可以采取任何形式。幸運(yùn)的是，大多數(shù)表有一個(gè)以上的非聚集索引，大多數(shù)的表有一個(gè)聚集索引性能更佳。因此，第一步：精心 選擇聚集鍵，第二步，查找一個(gè)正確的平衡，選擇適當(dāng)?shù)?，最小?shù)量的非聚集索引。

非聚集索引 （NonClustering Indexes)

正如前面所述，所有索引中有兩類主要組件：葉級(jí)(leaf level)和非葉級(jí)(non-leaf Level(s)) 。一個(gè)聚集索引的葉級(jí)就是數(shù)據(jù)。一個(gè)非聚集索引葉級(jí)是一個(gè)獨(dú)立的、額外的結(jié)構(gòu)（一些數(shù)據(jù)的copy),特別地，一個(gè)非聚集索引取決于它的葉級(jí)形式的定義， 非聚集索引由索引鍵，任何包含性列、數(shù)據(jù)行的書簽(bookmark)值,,一個(gè)蜚聚集索引的數(shù)量跟表中的數(shù)據(jù)行一樣多，除非索引定義時(shí)使用了過(guò)濾謂詞 (Filter predicate)。過(guò)濾謂詞在SQl Server 2008是新增的。

非聚集索引的工作方式有兩種：

１、輔助指向數(shù)據(jù)或直接回答查詢。當(dāng)一個(gè)非聚集索引擁有查詢中請(qǐng)求的全部數(shù)據(jù)時(shí)，這就是所謂的“查詢覆蓋(query covering)”，這時(shí)索引被稱為覆蓋索引，此時(shí)，非聚集索引被用于直接回答查詢而避免了書簽查詢(bookmark lookup)，一種對(duì)于非聚集索引異常昂貴的查詢。

２、當(dāng)一個(gè)非聚集索引沒(méi)有包含查詢中請(qǐng)求的全部數(shù)據(jù)時(shí)，這個(gè)查詢被一個(gè)索引能找到的謂詞驅(qū)動(dòng)，此時(shí)，書簽查詢發(fā)生。如果一個(gè)表有一個(gè)聚集索引，非聚 集索引被用于驅(qū)動(dòng)查詢?nèi)ネㄟ^(guò)聚集鍵找到相應(yīng)的數(shù)據(jù)行。如果是Heap(沒(méi)有聚集索引），查詢值是一個(gè)8字節(jié)的RID，實(shí)際行位置如下格式 FileID:PageID:SlotNumber。這個(gè)8字節(jié)值：2字節(jié):4字節(jié):2字節(jié)。后面還有深入了解。

非聚集索引的設(shè)置與否并不會(huì)影響數(shù)據(jù)頁(yè)被組織。SQL Server　2008中，一個(gè)表中可以有999個(gè)非聚集索引。SQL Server 2005中是249個(gè)。實(shí)際使用環(huán)境中，你盡可能少數(shù)量的使用（因?yàn)橐恍╁e(cuò)誤，比如Filtered Indexes）。

總而言之，非聚集索引不會(huì)影響基表，相反，基表的結(jié)構(gòu)會(huì)影響非聚集索引的結(jié)構(gòu)。如果你想減少開銷和架構(gòu)最佳性能，你最好理解這些。

約束和索引(Constaits and Indexes)

有關(guān)Primary Key約束和UNIQUE 約束，請(qǐng)查看MSDN:

http://msdn.microsoft.com/zh-cn/library/ms191236.aspx

http://msdn.microsoft.com/zh-cn/library/ms191166.aspx

一個(gè)基本的區(qū)別是后者可以是NUll,前者不允許NUll,兩者都不允許重復(fù)。

Indexes Creation Options

CREATE INDEX命令的更多選項(xiàng)，請(qǐng)查看MSDN:

http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

原文標(biāo)題：《Microsoft Sql server 2008 Internal》讀書筆記--第六章Indexes:Internals and Management(2)

鏈接：http://www.cnblogs.com/downmoon/archive/2010/02/04/1663956.html