[[204523]]

索引設(shè)計(jì)是數(shù)據(jù)庫(kù)設(shè)計(jì)中比較重要的一個(gè)環(huán)節(jié)，對(duì)數(shù)據(jù)庫(kù)的性能其中至關(guān)重要的作用，但是索引的設(shè)計(jì)卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術(shù)人員因?yàn)椴磺‘?dāng)?shù)膭?chuàng)建索引，***使得其效果適得其反，可以說(shuō)“成也索引，敗也索引”。

創(chuàng)建、修改和刪除索引是屬于索引維護(hù)部分中的內(nèi)容，作為數(shù)據(jù)庫(kù)對(duì)象，索引同樣也用CREATE, ALTER和 DROP這三個(gè)DDL語(yǔ)句進(jìn)行操作。但不同的是，對(duì)于索引來(lái)說(shuō)這幾個(gè)語(yǔ)句所能提供的功能要遠(yuǎn)遠(yuǎn)超過(guò)其名字所示，允許你創(chuàng)建、整理、刪除甚至修改索引的metadata。

當(dāng)你創(chuàng)建或是修改索引時(shí)，你可以設(shè)置一些參數(shù)，這些參數(shù)作為索引的一部分存儲(chǔ)在系統(tǒng)表中，你可以通過(guò)sys.indexes系統(tǒng)視圖進(jìn)行查看。當(dāng)SQL Server查詢或更新數(shù)據(jù)以及維護(hù)索引時(shí)需要這些數(shù)據(jù)來(lái)幫助更好的完整任務(wù)。本篇文章將會(huì)講到這些參數(shù)，但不會(huì)深入細(xì)節(jié)。

索引所在的表越大時(shí)，對(duì)其索引的DLL語(yǔ)句影響也就越大。這個(gè)影響表現(xiàn)在對(duì)于服務(wù)器資源的消耗和降低查詢執(zhí)行速度。所以通過(guò)學(xué)習(xí)當(dāng)執(zhí)行DDL或DML語(yǔ)句時(shí)，索引內(nèi)部的執(zhí)行過(guò)程，你可以:

1.理解為什么經(jīng)常需要維護(hù)索引

2.執(zhí)行維護(hù)操作的過(guò)程盡量不降低性能

3.減少維護(hù)索引過(guò)程對(duì)于其它查詢的影響

4.減少索引維護(hù)的頻率

創(chuàng)建索引：

我們首先創(chuàng)建聚集索引，然后創(chuàng)建非聚集索引。

創(chuàng)建聚集索引的內(nèi)部過(guò)程取決于當(dāng)前表的狀態(tài)以及創(chuàng)建聚集索引過(guò)程中指定的參數(shù)。

假如:

表已經(jīng)是聚集索引了:

發(fā)生錯(cuò)誤，一個(gè)表中不能含有兩個(gè)聚集索引，因?yàn)橥粫r(shí)間使得數(shù)據(jù)按照不同的物理順序排列是不可能的。

表是空的:

SQL Server僅僅更新系統(tǒng)表來(lái)讓自己知道這個(gè)表是聚集索引結(jié)構(gòu).不需要分配空間。

表中有數(shù)據(jù)，但表上沒(méi)有非聚集索引:

SQL Server更新系統(tǒng)表來(lái)讓自己知道這個(gè)表是聚集索引結(jié)構(gòu).

SQL Server將表中的行按照索引鍵的數(shù)據(jù)進(jìn)行排序，根據(jù)指定的填充因子將數(shù)據(jù)填充進(jìn)頁(yè)，然后生成索引的非葉子節(jié)點(diǎn)。這個(gè)過(guò)程幾乎不存在外部碎片。

表中有數(shù)據(jù)，表上存在非聚集索引:

SQL Server釋放由非聚集索引占用的所有空間，但不刪除其metadata。

SQL Server更新系統(tǒng)表來(lái)讓自己知道這個(gè)表是聚集索引結(jié)構(gòu)。

SQL Server創(chuàng)建聚集索引(過(guò)程看上面)。

非聚集索引通過(guò)剛才沒(méi)有刪除的metadata進(jìn)行重建,沒(méi)有其它選擇。非聚集索引必須完全重建，因?yàn)橹胺蔷奂饕臅?shū)簽指向的是rowid，但現(xiàn)在書(shū)簽需要存儲(chǔ)鍵值。

因此，如果你需要在表上創(chuàng)建多個(gè)索引，先建立聚集索引，然后再建立非聚集索引，這樣更加節(jié)省時(shí)間。[[204525]]

創(chuàng)建非聚集索引：

表是空的：

SQL Server僅僅更新系統(tǒng)表來(lái)讓自己知道這個(gè)表上含有非聚集索引.不需要分配空間。

表中存在數(shù)據(jù)：

SQL Server更新系統(tǒng)表來(lái)讓自己知道這個(gè)表上含有非聚集索引.不需要分配空間。

SQL Server掃描表，或是其它可以包含這個(gè)索引的非聚集索引。為表中的每一行創(chuàng)建索引條目，按照索引鍵排序，根據(jù)指定的填充因子將這些條目填充進(jìn)頁(yè)，然后生成索引的葉子節(jié)點(diǎn)。這個(gè)步驟幾乎不會(huì)產(chǎn)生外部碎片。

修改索引： 

ALTER INDEX語(yǔ)句可以被用來(lái)做如下四件事:

1.停用索引

2.重建索引

3.整理索引

4.修改索引選項(xiàng)

注意;ALTER INDEX語(yǔ)句不能修改索引中的索引鍵的組合，如果想要實(shí)現(xiàn)這點(diǎn)只能通過(guò)刪除索引再建立索引，也可以通過(guò)CREATE INDEX語(yǔ)句配上DROP_EXISTING選項(xiàng)。

停用索引：

停用索引只需要使用DISABLE關(guān)鍵字，比如:

ALTER INDEX PK_FragTest_PKCol ON FragTest DISABLE; GO 

停用一個(gè)索引并不會(huì)使得索引的定義信息從索引表中被移除。所有被停用的索引都可以之后執(zhí)行重建或刪除操作。

停用一個(gè)非聚集索引可以將非聚集索引所占用的空間釋放出來(lái)，因此當(dāng)索引被停用之后，SQL Server上運(yùn)行的查詢就會(huì)當(dāng)作這個(gè)索引不存在。

對(duì)于停用聚集索引來(lái)說(shuō)，則是釋放掉聚集索引非葉子節(jié)點(diǎn)所占用的空間。因?yàn)槿~子節(jié)點(diǎn)就是表本身，所以不會(huì)釋放葉子節(jié)點(diǎn)，但由于沒(méi)有非葉子節(jié)點(diǎn)進(jìn)行索引，所以被停用的聚集索引(也就是表本身)不能再用于查詢或更新。

停用索引涉及到釋放磁盤空間，因此這個(gè)過(guò)程需要一些IO操作以及寫(xiě)入日志文件。

存在索引停用的最重要的目的是為了節(jié)省磁盤空間。假如重建索引的時(shí)候不停用索引，則SQL Server需要維護(hù)兩個(gè)版本的索引，新建的索引成功后才會(huì)刪除老索引，因此造成磁盤空間的浪費(fèi)。而重建索引之前首先刪除索引的話，就能剩下磁盤空間了。通常來(lái)說(shuō)，重建一個(gè)已經(jīng)刪除的索引需要的空間是重建沒(méi)有刪除索引的五分之一。[[204525]]

重建索引：

重建索引不僅可以重建索引，還可以改變選項(xiàng)，比如:

ALTER INDEX PK_FragTest_PKCol 
 
ON FragTest 
 
REBUILD 
 
WITH ( FILLFACTOR = 75, SORT_IN_TEMPDB = ON, MAXDOP = 3 ) ;  

上面重建索引重新指定的選項(xiàng)會(huì)更新到系統(tǒng)表中，其它沒(méi)有指定的選項(xiàng)保持不變。此外，索引重建之后，外部碎片幾乎為0.所有頁(yè)內(nèi)都填充到填充因子所指定的值。如果上面參數(shù)你還指定了填充因子，這個(gè)填充因子在重建索引時(shí)立刻生效。[[204525]]

整理索引：

整理索引的目的只有一個(gè):消除碎片。整理索引被用于消除外部碎片，并將頁(yè)中填滿到填充因子所指定的程度。雖然整理索引所能提供的選項(xiàng)要小于重建索引，但同時(shí)整理索引消耗的資源以及對(duì)用戶查詢的影響也是小于重建索引的。

整理索引時(shí)要記住的四件事:

1.整理索引不會(huì)增加索引的大小，也不需要額外的存儲(chǔ)空間，相反，整理索引會(huì)減少索引的大小，并釋放不需要的頁(yè)所占的空間。

2.索引在整理的過(guò)程中可以繼續(xù)使用。

3.整理索引唯一能修改的選項(xiàng)是LOB_COMPACTION,整理索引不能修改填充因子的值。

4.整理索引需要索引允許頁(yè)鎖，這是建立索引時(shí)的默認(rèn)值。因?yàn)檎硭饕倪^(guò)程中，索引依然可用，SQL Server需要在其它查詢使用索引時(shí)對(duì)索引中的特定頁(yè)進(jìn)行加鎖。而如果ALLOW_PAGE_LOCKS選項(xiàng)設(shè)置成了OFF，則無(wú)法整理索引。

因此，常見(jiàn)的整理索引的語(yǔ)句比如:

或是:

SQL Server將整理索引分為兩個(gè)階段執(zhí)行。

階段一：主要整理內(nèi)部碎片

這個(gè)階段所能做的是非常有限的，因?yàn)檎缜懊嫣岬降?，整理索引不能增加額外的頁(yè)。因此如果每頁(yè)平均的數(shù)據(jù)小于填充因子標(biāo)識(shí)的數(shù)據(jù)，則可以通過(guò)整理索引減少索引大小，但平均數(shù)據(jù)如果大于索引因子的填充值的話，則不能通過(guò)整理索引增長(zhǎng)索引的大小。

階段1按照邏輯順序處理索引。一次處理八個(gè)頁(yè)。比如從***頁(yè)到第八頁(yè)，從第二頁(yè)到第九頁(yè)，從第三頁(yè)到第十頁(yè)，直到整個(gè)索引被檢查完。對(duì)于一次八個(gè)頁(yè)的檢查來(lái)說(shuō)，SQL Server會(huì)看這八個(gè)頁(yè)中的內(nèi)容是否可以在特定填充因子的情況下壓縮到7個(gè)頁(yè)中，如果可以，則將這八個(gè)頁(yè)壓縮到七個(gè)頁(yè)中并釋放第8個(gè)頁(yè)。

階段二:主要處理外部碎片

階段二主要按照索引的邏輯順序來(lái)整理物理順序。SQL Server讀取邏輯上的***頁(yè)和物理上的***頁(yè)，如果它們不是同一個(gè)頁(yè)，則交換其內(nèi)容，每次一頁(yè)，直到整理完索引的***一頁(yè)。這個(gè)過(guò)程完成后，則索引的外部碎片被降到了***。

整理索引完成后，外部碎片和內(nèi)部碎片都會(huì)降到可以接受的程度。

整理索引和重建索引相比起來(lái)雖然功能有限，但這個(gè)過(guò)程不需要額外的磁盤空間，并只需要非常少量的內(nèi)存消耗。最重要的一點(diǎn)是在整理的過(guò)程中索引依然可以使用。

所以對(duì)于處理索引碎片的選擇包括了：重建，停用和重建，整理索引。在本系列的第15篇中我將會(huì)詳細(xì)講述關(guān)于索引的***實(shí)踐。

修改索引的Metadata?

有一些索引選項(xiàng)可以在不用重建或是整理索引的情況下進(jìn)行修改。

下面的示例語(yǔ)句顯示了如何修改這些選項(xiàng):

刪除索引： 

刪除索引后，索引所占用的空間被釋放，并且從系統(tǒng)表中刪除索引的metadata。

我們第八篇關(guān)于唯一索引的部分提到過(guò)，你不能在有主鍵或唯一約束的情況下刪除對(duì)應(yīng)的索引。

值得注意的是，刪除聚集索引并不會(huì)刪除其表，僅僅釋放非葉子節(jié)點(diǎn)。但等同于表本身的葉子節(jié)點(diǎn)并不會(huì)被刪除，這些葉子頁(yè)將會(huì)按堆存放，同時(shí)所有的非聚集索引也會(huì)被自動(dòng)重建。因此，如果刪除多個(gè)索引時(shí)，首先要?jiǎng)h除非聚集索引，然后再刪除聚集索引。[[204525]]

選項(xiàng)：

在使用CREATE INDEX語(yǔ)句時(shí)可以設(shè)置的選項(xiàng)分為三類:

1.影響索引創(chuàng)建，但并影響索引使用的選項(xiàng)，大多數(shù)選項(xiàng)都屬于這一類。

2.影響索引的使用，但不影響索引的創(chuàng)建的選項(xiàng)。ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS 選項(xiàng)都屬于這一類。

3.既影響索引創(chuàng)建又影響索引使用的選項(xiàng)，比如說(shuō)DATA_COMPRESSION選項(xiàng)。

下面就是這些選項(xiàng)的說(shuō)明，除非特別注明了，否則都屬于上面提到的***類選項(xiàng)。

FILLFACTOR:

指定頁(yè)面的填充因子，僅僅影響葉子節(jié)點(diǎn)，默認(rèn)值是0，也就是每一頁(yè)允許完全填滿。

PAD_INDEX:

指定填充因子是否可以存在于非葉子節(jié)點(diǎn)。

SORT_IN_TEMPDB：

指定創(chuàng)建索引過(guò)程的排序操作實(shí)在數(shù)據(jù)庫(kù)空間操作還是TempDB上操作。

IGNORE_DUP_KEY:

在第8篇關(guān)于唯一索引的文章說(shuō)已經(jīng)說(shuō)過(guò)了。

STATISTICS_NORECOMPUTE:

在第14篇索引統(tǒng)計(jì)中會(huì)詳細(xì)說(shuō)明。

DROP_EXISTING:

注意：這個(gè)選項(xiàng)僅僅可以在CREATE INDEX中使用。

DROP_EXISTING = ON:

如果創(chuàng)建過(guò)程中已經(jīng)存在了同名的索引和索引類型(類型指的是聚集或是非聚集)，則刪除掉舊的索引并重新創(chuàng)建新的索引。

如果已經(jīng)有了同名索引，但類型不同，則會(huì)報(bào)錯(cuò)。

如果沒(méi)有同名索引，則根據(jù)定義直接創(chuàng)建新的索引。

DROP_EXISTING = OFF:

如果存在同名索引，則報(bào)錯(cuò)。

如果不存在同名索引，則根據(jù)索引定義直接創(chuàng)建新的索引。

ONLINE:

這個(gè)選項(xiàng)可以指定當(dāng)重建索引的時(shí)候其它SPID是否可以訪問(wèn)這個(gè)索引。如果創(chuàng)建的是非聚集索引，則SELECT語(yǔ)句都可以訪問(wèn)底層表。這個(gè)選項(xiàng)只能在企業(yè)版，開(kāi)發(fā)版和評(píng)估版中使用。

ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS:

自動(dòng)SQL Server 2005開(kāi)始，允許根據(jù)這個(gè)選項(xiàng)來(lái)控制鎖升級(jí)，詳細(xì)如表1所示。 

 

這兩個(gè)選項(xiàng)都不會(huì)影響索引的創(chuàng)建，它們都是創(chuàng)建之后影響索引使用的選項(xiàng)，如果隔離等級(jí)允許行版本控制，則這個(gè)選項(xiàng)無(wú)關(guān)緊要。

整理索引需要ALLOW_ROW_LOCKS 設(shè)置為ON。

兩個(gè)都設(shè)置為OFF，或是設(shè)置其中一個(gè)為OFF，使得在大量負(fù)載的情況下減少鎖升級(jí)。指定這個(gè)選項(xiàng)對(duì)于大量查詢，很少更新的索引非常有用。

這兩個(gè)選項(xiàng)需要你對(duì)數(shù)據(jù)庫(kù)的原理和鎖的原理有比較透徹的了解。

MAXDOP:

指定創(chuàng)建索引的時(shí)候可以使用幾個(gè)CPU內(nèi)核。

DATA_COMPRESSION:

數(shù)據(jù)壓縮選項(xiàng)。這個(gè)選項(xiàng)不僅影響索引的創(chuàng)建，還會(huì)影響索引的使用。有關(guān)數(shù)據(jù)壓縮的話題已經(jīng)超出了本篇文章的范圍。 

總結(jié)

CREATE INDEX語(yǔ)句允許你創(chuàng)建索引并設(shè)置選項(xiàng)。

ALTER INDEX可以創(chuàng)建，停用，重建，整理和刪除索引。

ALTER INDEX不能為索引添加或刪除列，只有通過(guò)CREATE INDEX語(yǔ)句。

整理索引所需的時(shí)間和資源更少，并且在整理的過(guò)程中允許繼續(xù)使用索引。

停用非聚集索引使得其占用的空間被釋放，并且不能夠在被SQL Server使用。停用聚集索引使得非葉子節(jié)點(diǎn)所占的空間被釋放并且表不能繼續(xù)被訪問(wèn)。被停用的索引只能執(zhí)行重建或刪除操作。重建一個(gè)已經(jīng)存在的索引所需的空間要大于重建被停用的索引。

很多選項(xiàng)只能在重建索引的過(guò)程中應(yīng)用。

創(chuàng)建或刪除聚集索引導(dǎo)致其相關(guān)聯(lián)的所有非聚集索引重建。

當(dāng)一個(gè)表需要多個(gè)索引時(shí)，要先創(chuàng)建聚集索引，再創(chuàng)建非聚集索引。而刪除的過(guò)程則相反。

刪除聚集索引并不會(huì)導(dǎo)致刪除表，而是使得表中數(shù)據(jù)按堆存放以及相關(guān)的非聚集索引被重建。