[[204523]]

索引設計是數據庫設計中比較重要的一個環節，對數據庫的性能其中至關重要的作用，但是索引的設計卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術人員因為不恰當的創建索引，***使得其效果適得其反，可以說“成也索引，敗也索引”。

創建、修改和刪除索引是屬于索引維護部分中的內容，作為數據庫對象，索引同樣也用CREATE, ALTER和 DROP這三個DDL語句進行操作。但不同的是，對于索引來說這幾個語句所能提供的功能要遠遠超過其名字所示，允許你創建、整理、刪除甚至修改索引的metadata。

當你創建或是修改索引時，你可以設置一些參數，這些參數作為索引的一部分存儲在系統表中，你可以通過sys.indexes系統視圖進行查看。當SQL Server查詢或更新數據以及維護索引時需要這些數據來幫助更好的完整任務。本篇文章將會講到這些參數，但不會深入細節。

索引所在的表越大時，對其索引的DLL語句影響也就越大。這個影響表現在對于服務器資源的消耗和降低查詢執行速度。所以通過學習當執行DDL或DML語句時，索引內部的執行過程，你可以:

1.理解為什么經常需要維護索引

2.執行維護操作的過程盡量不降低性能

3.減少維護索引過程對于其它查詢的影響

4.減少索引維護的頻率

創建索引：

我們首先創建聚集索引，然后創建非聚集索引。

創建聚集索引的內部過程取決于當前表的狀態以及創建聚集索引過程中指定的參數。

假如:

表已經是聚集索引了:

發生錯誤，一個表中不能含有兩個聚集索引，因為同一時間使得數據按照不同的物理順序排列是不可能的。

表是空的:

SQL Server僅僅更新系統表來讓自己知道這個表是聚集索引結構.不需要分配空間。

表中有數據，但表上沒有非聚集索引:

SQL Server更新系統表來讓自己知道這個表是聚集索引結構.

SQL Server將表中的行按照索引鍵的數據進行排序，根據指定的填充因子將數據填充進頁，然后生成索引的非葉子節點。這個過程幾乎不存在外部碎片。

表中有數據，表上存在非聚集索引:

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

SQL Server更新系統表來讓自己知道這個表是聚集索引結構。

SQL Server創建聚集索引(過程看上面)。

非聚集索引通過剛才沒有刪除的metadata進行重建,沒有其它選擇。非聚集索引必須完全重建，因為之前非聚集索引的書簽指向的是rowid，但現在書簽需要存儲鍵值。

因此，如果你需要在表上創建多個索引，先建立聚集索引，然后再建立非聚集索引，這樣更加節省時間。[[204525]]

創建非聚集索引：

表是空的：

SQL Server僅僅更新系統表來讓自己知道這個表上含有非聚集索引.不需要分配空間。

表中存在數據：

SQL Server更新系統表來讓自己知道這個表上含有非聚集索引.不需要分配空間。

SQL Server掃描表，或是其它可以包含這個索引的非聚集索引。為表中的每一行創建索引條目，按照索引鍵排序，根據指定的填充因子將這些條目填充進頁，然后生成索引的葉子節點。這個步驟幾乎不會產生外部碎片。

修改索引： 

ALTER INDEX語句可以被用來做如下四件事:

1.停用索引

2.重建索引

3.整理索引

4.修改索引選項

注意;ALTER INDEX語句不能修改索引中的索引鍵的組合，如果想要實現這點只能通過刪除索引再建立索引，也可以通過CREATE INDEX語句配上DROP_EXISTING選項。

停用索引：

停用索引只需要使用DISABLE關鍵字，比如:

ALTER INDEX PK_FragTest_PKCol ON FragTest DISABLE; GO 

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

停用一個非聚集索引可以將非聚集索引所占用的空間釋放出來，因此當索引被停用之后，SQL Server上運行的查詢就會當作這個索引不存在。

對于停用聚集索引來說，則是釋放掉聚集索引非葉子節點所占用的空間。因為葉子節點就是表本身，所以不會釋放葉子節點，但由于沒有非葉子節點進行索引，所以被停用的聚集索引(也就是表本身)不能再用于查詢或更新。

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

存在索引停用的最重要的目的是為了節省磁盤空間。假如重建索引的時候不停用索引，則SQL Server需要維護兩個版本的索引，新建的索引成功后才會刪除老索引，因此造成磁盤空間的浪費。而重建索引之前首先刪除索引的話，就能剩下磁盤空間了。通常來說，重建一個已經刪除的索引需要的空間是重建沒有刪除索引的五分之一。[[204525]]

重建索引：

重建索引不僅可以重建索引，還可以改變選項，比如:

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

上面重建索引重新指定的選項會更新到系統表中，其它沒有指定的選項保持不變。此外，索引重建之后，外部碎片幾乎為0.所有頁內都填充到填充因子所指定的值。如果上面參數你還指定了填充因子，這個填充因子在重建索引時立刻生效。[[204525]]

整理索引：

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

整理索引時要記住的四件事:

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

2.索引在整理的過程中可以繼續使用。

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

4.整理索引需要索引允許頁鎖，這是建立索引時的默認值。因為整理索引的過程中，索引依然可用，SQL Server需要在其它查詢使用索引時對索引中的特定頁進行加鎖。而如果ALLOW_PAGE_LOCKS選項設置成了OFF，則無法整理索引。

因此，常見的整理索引的語句比如:

或是:

 

SQL Server將整理索引分為兩個階段執行。

階段一：主要整理內部碎片

這個階段所能做的是非常有限的，因為正如前面提到的，整理索引不能增加額外的頁。因此如果每頁平均的數據小于填充因子標識的數據，則可以通過整理索引減少索引大小，但平均數據如果大于索引因子的填充值的話，則不能通過整理索引增長索引的大小。

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

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

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

整理索引完成后，外部碎片和內部碎片都會降到可以接受的程度。

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

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

修改索引的Metadata?

有一些索引選項可以在不用重建或是整理索引的情況下進行修改。

 

下面的示例語句顯示了如何修改這些選項:

刪除索引： 

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

我們第八篇關于唯一索引的部分提到過，你不能在有主鍵或唯一約束的情況下刪除對應的索引。

值得注意的是，刪除聚集索引并不會刪除其表，僅僅釋放非葉子節點。但等同于表本身的葉子節點并不會被刪除，這些葉子頁將會按堆存放，同時所有的非聚集索引也會被自動重建。因此，如果刪除多個索引時，首先要刪除非聚集索引，然后再刪除聚集索引。[[204525]]

選項：

在使用CREATE INDEX語句時可以設置的選項分為三類:

1.影響索引創建，但并影響索引使用的選項，大多數選項都屬于這一類。

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

3.既影響索引創建又影響索引使用的選項，比如說DATA_COMPRESSION選項。

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

FILLFACTOR:

指定頁面的填充因子，僅僅影響葉子節點，默認值是0，也就是每一頁允許完全填滿。

PAD_INDEX:

指定填充因子是否可以存在于非葉子節點。

SORT_IN_TEMPDB：

指定創建索引過程的排序操作實在數據庫空間操作還是TempDB上操作。

IGNORE_DUP_KEY:

在第8篇關于唯一索引的文章說已經說過了。

STATISTICS_NORECOMPUTE:

在第14篇索引統計中會詳細說明。

DROP_EXISTING:

注意：這個選項僅僅可以在CREATE INDEX中使用。

DROP_EXISTING = ON:

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

如果已經有了同名索引，但類型不同，則會報錯。

如果沒有同名索引，則根據定義直接創建新的索引。

DROP_EXISTING = OFF:

如果存在同名索引，則報錯。

如果不存在同名索引，則根據索引定義直接創建新的索引。

ONLINE:

這個選項可以指定當重建索引的時候其它SPID是否可以訪問這個索引。如果創建的是非聚集索引，則SELECT語句都可以訪問底層表。這個選項只能在企業版，開發版和評估版中使用。

ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS:

自動SQL Server 2005開始，允許根據這個選項來控制鎖升級，詳細如表1所示。 

 

這兩個選項都不會影響索引的創建，它們都是創建之后影響索引使用的選項，如果隔離等級允許行版本控制，則這個選項無關緊要。

整理索引需要ALLOW_ROW_LOCKS 設置為ON。

兩個都設置為OFF，或是設置其中一個為OFF，使得在大量負載的情況下減少鎖升級。指定這個選項對于大量查詢，很少更新的索引非常有用。

這兩個選項需要你對數據庫的原理和鎖的原理有比較透徹的了解。

MAXDOP:

指定創建索引的時候可以使用幾個CPU內核。

DATA_COMPRESSION:

數據壓縮選項。這個選項不僅影響索引的創建，還會影響索引的使用。有關數據壓縮的話題已經超出了本篇文章的范圍。 

總結

CREATE INDEX語句允許你創建索引并設置選項。

ALTER INDEX可以創建，停用，重建，整理和刪除索引。

ALTER INDEX不能為索引添加或刪除列，只有通過CREATE INDEX語句。

整理索引所需的時間和資源更少，并且在整理的過程中允許繼續使用索引。

停用非聚集索引使得其占用的空間被釋放，并且不能夠在被SQL Server使用。停用聚集索引使得非葉子節點所占的空間被釋放并且表不能繼續被訪問。被停用的索引只能執行重建或刪除操作。重建一個已經存在的索引所需的空間要大于重建被停用的索引。

很多選項只能在重建索引的過程中應用。

創建或刪除聚集索引導致其相關聯的所有非聚集索引重建。

當一個表需要多個索引時，要先創建聚集索引，再創建非聚集索引。而刪除的過程則相反。

刪除聚集索引并不會導致刪除表，而是使得表中數據按堆存放以及相關的非聚集索引被重建。