SQL Server 資料庫索引其索引的小技巧

一、什麼是索引

減少磁碟I/O和邏輯讀次數的最佳方法之一就是使用【索引】
索引允許SQL Server在表中尋找資料而不需要掃描整個表。

1.1、索引的好處：

當表沒有叢集索引時，成為【堆或堆表】
【堆】是一堆未加工的資料，以行標識符作為指向儲存位置的指標。表資料沒有順序，也不能搜尋，除非逐行遍曆。這個過程稱為【掃描】。當存在叢集索引時，非叢集索引的指標由叢集索引所定義的值組成，所以叢集索引變得非常重要。
因為頁面大小固定，所以列越少，所能儲存的行就越多。由於非叢集索引通常不包含所有列，所以一般一個頁麵包含有更多的非叢集索引。所以SQLServer能從一個非叢集索引的頁面中讀到比包含該列的表也頁面更多的值。
非叢集索引的另一個好處：獨立於資料表的結構，可以放到不同的檔案組，使用不同的I/O。
索引使用B-樹作為儲存結構，所以查詢特定行所需的操作被最小化。

1.2、索引開銷：

索引過多會引起（INSERT/UPDATE/DELETE/CRUD中的CUD部分）花費更長的時間。
在設計索引時，要從兩個角度進行：
對現有的生產系統，需要測量索引的總體影響，應保證效能帶來的好處超過處理資源的額外成本。可以使用Profiler工具進行整體工作負載最佳化。
當專註與索引立刻帶來的好處時，可以使用DMV查看：
Sys.dm_db_index_operational_stats或sys.dm_db_index_usage_stats
Sys.dm_db_index_operational_stats：顯示正在使用的一個索引的低級活動，比如I/O和鎖。
Sys.dm_db_index_usage_stats：隨時發生咋一個索引中的各種操作的統計數字。
雖然對於DML，維護索引所需要的開銷會增加，但是，SQLServer在更新或刪除之前必須首先找到一行，所以索引對使用複雜的where子句的update和delete語句可能有協助。

二、索引設計建議

索引設計建議如下：
l 檢查where子句和串連條件列；
l 使用窄索引；
l 檢查列的唯一性；
l 檢查列的資料類型；
l 考慮列順序；
l 考慮索引類型（叢集索引VS 非叢集索引）

2.1、檢查where子句和串連條件列：
當一個查詢提交到SQLServer時，最佳化器會做以下步驟：
1) 最佳化器識別WHERE子句和串連條件中包含的列。
2) 接著最佳化器檢查這些列上的索引。
3) 最佳化器通過從索引上維護的統計確定子句的選擇性（也就是返回多少行）評估每個索引的有效性。
4) 最終，最佳化器根據前面幾個步驟中的收集資訊，估計讀取所限定的行開銷最低的方法。
當沒有合適的where和串連列時，最佳化器會做全表掃描。
建議：在where子句或串連條件中頻繁使用的列上建索引，以避免表掃描。當一個表的資料總量非常小以至可以放入一個單獨的頁面（8KB）時，表掃描可能比索引尋找工作得更好。

2.2、使用窄索引：
為了最好的效能，盡量在索引中使用較少的列。還應當避免寬資料類型的列。
窄索引可以在8KB的索引頁面中容納比寬索引更多的行，可以達到以下效果：
l 減少I/O數量（讀取更少的8KB頁面）
l 使用資料庫緩衝更有效，因為SQLServer可以緩衝更少的索引頁面，減少記憶體中索引頁面所需的邏輯讀操作。
l 減少資料庫儲存空間。

2.3、檢查列的唯一性：
在一個很小範圍的可能值的列（如性別）上建立索引對效能沒有好處。因為最佳化器不能使用索引有效地減少返回的行。因為小範圍的值可能引起【全表掃描】或者【叢集索引掃描】。使where子句中的列具有大量的唯一行（或者高選擇性）以限制訪問的行數始終是首選的方案。應該在這些列上建立索引協助訪問小的結果集。
另外，對於建立在多個列上的索引時，順序是有關係的。在某些情況下，使用最有選擇性的列將是索引更有效。

2.4、檢查列資料類型：
對數值型建索引會很快，因為尺寸小，算術操縱很容易。但是字元型尺寸大，且需要字串匹配操作，通常開銷更大。

2.5、考慮列順序：
複合索引中，列順序是索引效率的重要因素：
l 列唯一性；
l 列寬度；
l 列資料類型；
查詢利用了索引的前沿來執行尋找操作以檢索資料。把最有效索引放到前沿，能儘快篩選資料。減少資料量。

2.6、考慮索引類型：
叢集索引和非叢集索引都以B-樹儲存資料。下面將詳細介紹

三、叢集索引（聚簇索引）

聚簇索引的葉子頁面和表的資料頁面相同。因此表行物理上按照聚簇索引列排序，因為從物力上只能有一種物理順序，所以只有一個聚簇索引。

3.1、堆表：
沒有聚簇索引的表叫堆表。資料列沒有任何順序，串連到表的相鄰頁面。與訪問非堆表相比，無組織的結構增大了訪問的開銷。
3.2、與非聚簇索引的關係：
非聚簇索引的一個索引行包含指向表的對應資料行的指標。這個指標被稱為【行定位器（row locator）】。它的值取決於資料頁是儲存在堆當中還是被彙總。對於非聚簇索引，行定位器指向堆中資料行的RID的指標。對於聚簇索引，行定位器是聚簇索引的索引索引值。當有新資料行進入時，可能導致非聚簇索引重定位、分頁等等，影響效能。
3.3、聚簇索引建議：
1) 首先建立聚簇索引：
因為所有非聚簇索引在其索引行上儲存聚簇索引索引值，所以建立順序非常重要。為了最好的效能，建議在建立任何非聚簇索引前建立聚簇索引。
2) 保持窄索引：
應保持聚簇索引總體的長度儘可能小。因為聚簇索引長度太大，那麼非聚簇索引也會跟著增大。因此，大的聚簇索引索引值不僅影響本身寬度，而且擴大表上的所有非聚簇索引，增加索引頁面數量，增加邏輯讀和磁碟I/O。
3) 一步重建聚簇索引：
由於聚簇索引和非聚簇索引關聯，所以使用DROP INDEX再CREATE INDEX將導致非聚簇索引建立兩次，此時可以使用CREATE INDEX 語句的DROP_EXISTING子句在一個單獨的原子步驟中重建聚簇索引，相似地可以在非聚簇索引中使用。
4) 何時使用一個聚簇索引：
a) 檢索一定範圍的資料：
由於聚簇索引是按物理順序建立，索引合理利用能減少磁頭的移動，減少物理I/O量。
b) 讀取預先排序的資料：
對於需要排序的資料，聚簇索引非常有效，能減少資料讀取後的排序開銷。
對於讀取大範圍行和/或排序輸出的查詢，聚簇索引通常是比非聚簇索引更有效選擇。
5) 何時不使用聚簇索引：
在某些情況下最好不要使用聚簇索引：
a) 頻繁更新的列：
如果列更新頻繁，將導致非聚簇索引重新置放，增加相關操作查詢的開銷。還將阻塞這段時間引用相同部分和非聚簇索引的其他查詢，從而影響資料並行性。
b) 寬的關鍵字：前面已經說明原因
c) 太多並行的順序插入：
如果想並行插入新行，那麼把它們分布在多個頁面中會更好，有聚簇索引的話，所有插入都會集中在最後一頁，形成巨大的“熱點”，可以通過建立另一列上的索引（該索引不會將行按照新行相同的順序來排序）來將插入操作隨機分布在整個表，這個問題只在大量的同時插入時發生。如果磁碟熱點成為效能瓶頸，那麼可以通過降低表的填滿因數來容納到中間頁面。這樣熱的頁面將在記憶體中，也有利於效能。

四、非聚簇索引

非聚簇索引不影響表頁面中資料的順序，對於堆表，行定位器指向資料行的RID的指標。對於非堆表，指向聚簇索引的索引鍵。

4.1、非聚簇索引維護：
為最佳化維護開銷，SQLServer添加一個指向舊資料頁的指標，以在頁面分割之後指向新的資料頁面，而不是更新所有相關非聚簇索引的行定位器。將聚簇索引作為行定位器降低了非聚簇索引相關的開銷。
4.2、定義書籤尋找：
當查詢請求不是最佳化器選擇的非聚簇索引一部分時，需要一個尋找，這對一個聚簇索引來說是一個關鍵字尋找，對堆表來說是一個RID尋找。成為：書籤尋找。
這種尋找根據索引行的行定位器值，從表中讀取對應的資料行，除了索引頁面上的邏輯讀操作以外，還需要一個資料頁面的邏輯讀。但是如果查詢需要列中的索引，那麼不需要訪問資料頁面，這種叫做【覆蓋索引】，這些書籤尋找是大結果集最好使用聚簇索引的原因。聚簇索引不需要書籤尋找，因為葉子頁面和資料頁面相同。
4.3、非聚簇索引建議：
1. 何時使用非聚簇索引：
在需要從一個大表中讀取少量行時最有效。隨著行數增多，書籤尋找的開銷成比例增加。索引列應該有很高的選擇性。
有一些索引需求不適合於聚簇索引：
l 頻繁更新的列
l 寬關鍵字
2. 何時不使用非聚簇索引：
非聚簇索引不適合檢索大量行的查詢。此時使用聚簇索引更好。因為不需要單獨的書籤尋找來檢索資料行。如果需要從表上讀取大量的結果集，那麼在過濾和串連條件中的非聚簇索引沒有協助，除非使用非聚簇索引——覆蓋索引。

五、聚簇索引VS 非聚簇索引

選擇聚簇索引或非聚簇索引主要考慮因素：
l 檢索的行數量；
l 資料排序需求；
l 索引鍵寬度；
l 列更新頻度；
l 書籤開銷；
l 任何磁碟熱點；

5.1、聚簇索引相對非聚簇索引的好處：
在沒有索引的表上選擇索引的類型時，聚簇索引通常是首選。
盡量使用具有高選擇性的列讀取小的結果集是該列上建立非聚簇索引很好的啟示，但在同意列上的聚簇索引可能同樣有利甚至更好。
注意：儘管許多資料檢索中聚簇索引勝過非聚簇索引，但是一個表只有一個聚簇索引，因此，應當將聚簇索引保留在最有力的情況下。
5.2、非聚簇索引相對聚簇索引的好處：
非聚簇索引在以下情況優先於聚簇索引：
l 索引鍵尺寸很大。
l 為了避免聚簇索引重建時需要重建所有非聚簇索引的相關開銷。
l 是資料庫讀取程式工作於非聚簇索引頁面上，同時寫入程式對資料頁面中的其他列（不包括非聚簇索引中）進行修改以避免阻塞。
l 當查詢所有引用列（來自一個表）可以安全地容納非聚簇索引中時。
在不需要跳轉到資料行的情況下，非聚簇索引的效能應該和聚簇索引一樣好（甚至更好）。非聚簇索引鍵包含所有表中需要的列是有可能的。

六、進階索引技術

l 覆蓋索引：
l 索引交叉：使用多個非聚簇索引以滿足查詢的所有列需求（來自一個表）
l 索引串連：使用索引交叉和覆蓋索引技術來避免觸及基本表。
l 過濾索引：為了能夠索引具有零散資料分布的欄位或者稀疏的列，可以在索引上應用過濾，這樣它只索引一些資料。
l 索引檢視表：在磁碟上將視圖輸出實體化

6.1、覆蓋索引：
在所有為滿足SQL查詢不用到達基礎資料表所需的列上建立非聚簇索引。如果查詢遇到一個索引並且完全不需要引用底層資料表，那麼該索引可以被認為是覆蓋索引。使用INCLUDE操作符使索引編程覆蓋索引，浙江儲存資料和索引而不需要修改索引結構本身。
覆蓋索引本身對於減少邏輯讀是一種遊泳的技術。在以下情況使用最好：
l 你不希望增加索引鍵的大小，但仍然希望有一個覆蓋索引；
l 你打算索引一種不能被索引的資料類型（除了文本、ntext和映像）；
l 你已經超過了一個索引的關鍵字列的最大數量（但是最好避免這個問題）。
1、 偽聚簇索引（Pseudoclustered index）：
覆蓋索引物理上順序地組織所有索引列。從I/O角度看，沒有使用包含列的覆蓋索引編程一種聚簇索引，用於所有完全滿足於覆蓋索引中列的查詢。如果查詢結果集需要排序，那麼覆蓋索引可以用於物理地按照結果集所需的順序維護列資料。
2、 建議：
利用覆蓋索引，要注意SELECT語句中的列清單。應儘可能使用較少的列來保持小的覆蓋索引鍵尺寸。如果索引中所有列的位元組數相比表的單個資料行來說較小，而且確定利用覆蓋索引的查詢經常執行，那麼覆蓋索引是有效。
在建立許多覆蓋索引之前，考慮SQLServer如何有效和自動地使用索引交叉為查詢即時建立覆蓋索引。

6.2、索引交叉：
如果一個表有很多索引，那麼SQLServer可以使用多個索引來執行一個查詢。根據每個索引選擇小的資料子集，然後執行兩個子集的交叉（即只返回滿足所有條件的那些行）
但在現實世界中，修改現有索引時要考慮以下問題：
l 因為各種原因，可能不允許修改現有索引；
l 現有非聚簇索引鍵可能已經相當寬；
l 使用現有索引的查詢開銷將被這個修改所影響。
為了增進一個查詢的效能，SQLServer可以在表上使用多個索引，因此，考慮建立多個窄索引代替寬的索引鍵。
有時候，可能必須為以下原因建立一個單獨的非聚簇索引：
l 重新排列現有索引中的列不被允許；
l 覆蓋索引所需要的一些列不能被包含在現有的非聚簇索引中；
l 兩個現有非聚簇索引中的總列數可能多餘覆蓋索引所需要的列數；
在這些情況下，可以在剩下的列上建立非聚簇索引。

6.3、索引串連：
索引串連是索引交叉的變種，將覆蓋索引技術應用到索引交叉。如果沒有單個覆蓋查詢的索引而多個索引一齊可以覆蓋該查詢。SQLServer可以使用索引串連完全滿足查詢而不需要轉到基本表。

6.4、過濾索引：
是使用過濾器的非聚簇索引，基本上上一個where子句。用倆在可能沒有很好選擇性的一個或多個列上建立一個高選擇性的關鍵字組。對於大量null值時比較適用。
過濾索引在許多方面帶來回報：
l 減少索引尺寸從而增進查詢效率。
l 建立更小的索引降低儲存開銷；
l 因為尺寸減少，降低了索引維護的成本。
過濾索引需要在訪問或者建立時的一組特殊ANSI設定：
ON:ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER
OFF:NUMERIC_ROUNDABORT

6.5、索引檢視表：
SQLServer可以在視圖上建立唯一的聚簇索引來磁碟上實體化。這樣的索引成為索引檢視表或實體化視圖。在建立以後可以建立非聚簇索引。
1、 好處：
l 彙總可以預先計算並被儲存在索引檢視表中，以在查詢執行期間最小化昂貴的計算；
l 表可以預先串連，結果集可以實物化；
l 串連或彙總的組成可以被實物化。

2、 開銷：
l 基本表中的任何修改必須執行事務的select語句反映到索引檢視表中；
l 對索引檢視表定義的基本表上的任何修改可能發起索引檢視表的非聚簇索引中的修改，如果聚簇鍵被更新，聚簇索引也將必須更新；
l 索引檢視表增加資料庫的維護開銷；
l 資料庫中需要更多的儲存；
建立索引檢視表包括如下限制：
l 視圖的第一個索引必須是唯一聚簇索引。
l 索引檢視表上的非聚簇索引只可以在唯一聚簇索引建立之後建立。
l 視圖定義必須是確定性——即，它對一個給定的查詢只能返回一個可能的結果；
l 索引檢視表必須只引用相同資料庫中的基本表，而不是其他視圖；
l 索引檢視表可以包含浮點列但是這樣的列不能包含在聚簇索引鍵中；
l 索引檢視表必須是綁定到列所參考資料表的一個架構，以免表架構的修改；
l 視圖定義的文法有很多限制
l 必須確定的SET選項列表：
ON:ARITHABORT,CONCAT_NULL_YIELDS_NULL,ANSI_NULLS,ANSI_PADDING和ANSI_WARNING
OFF:NUMERIC_ROUNDABORT

3、 使用環境：
OLAP能從索引檢視表中獲益，OLTP就比較難從中獲益。

6.6、索引壓縮：
從2008引入。壓縮索引能造成重大效能改進，但是也會造成CPU和記憶體開銷。不是適合所有索引的方案。
預設情況下，索引不會被壓縮。必須明確地在建立索引時要求索引被壓縮。分為行級和頁級壓縮。索引中的非葉子頁面不接受頁面類型下的壓縮。

七、特殊索引類型

7.1、全文索引：
對文本型的欄位索引
7.2、空間索引：
對於空間類型的資料進行索引
7.3、XML：
從2005引入XML後，對XML類型

八、索引的附件特性

8.1、不同的列排序次序：
可對一個索引中的不同列進行升降序排列。
8.2、在計算資料行上的索引：
可以在計算資料行上建立索引，只要計算資料行的運算式符合一定的限制，比如來源表是確定的。
8.3、BIT資料類型列上的索引：
建立在BIT資料列上的索引本身不是很好的優點，但是對於覆蓋索引，當涵蓋了BIT列時就很有用。
8.4、作為一個查詢處理的CREATE INDEX語句：

8.5、並行索引建立：
可以在max degree of parallelism配置參數來控制CREATE INDEX語句中的處理器數量，也可以使用exec sp_configure ‘maxdegree of parallelism'
8.6、線上索引建立：
可以在建立索引時減少鎖的機會。
8.7、考慮資料庫引擎調整顧問

九、小結

為了決定特殊查詢的索引鍵列，需要評估查詢的WHERE子句和串連條件。像列選擇性、寬度、資料類型和列順序這些因素。因為索引主要是為了檢索少量行，所以索引選擇性必須非常高。
為了獲得更好效能，嘗試使用覆蓋索引完全覆蓋查詢。

SQL Server資料庫最佳化其索引的小技巧

關於索引的常識：影響到資料庫效能的最大因素就是索引。由於該問題的複雜性，我只可能簡單的談談這個問題，不過關於這方面的問題，目前有好幾本不錯的書籍可供你參閱。我在這裡只討論兩種SQL Server索引，即clustered索引和nonclustered索引。當考察建立什麼類型的索引時，你應當考慮資料類型和儲存這些資料的column。同樣，你也必須考慮資料庫可能用到的查詢類型以及使用的最為頻繁的查詢類型。

索引的類型
如果column儲存了高度相關的資料，並且常常被順序訪問時，最好使用clustered索引，這是因為如果使用clustered索引，SQL Server會在物理上按升序（預設）或者降序重排資料列，這樣就可以迅速的找到被查詢的資料。同樣，在搜尋控制在一定範圍內的情況下，對這些column也最好使用clustered索引。這是因為由於物理上重排資料，每個表格上只有一個clustered索引。

與上面情況相反，如果columns包含的資料相關性較差，你可以使用nonculstered索引。你可以在一個表格中使用高達249個nonclustered索引——儘管我想象不出實際應用場合會用的上這麼多索引。

當表格使用主關鍵字（primary keys），預設情況下SQL Server會自動對包含該關鍵字的column(s)建立一個專屬的cluster索引。很顯然，對這些column(s)建立專屬索引意味著主關鍵字的唯一性。當建立外關鍵字（foreign key）關係時，如果你打算頻繁使用它，那麼在外關鍵字cloumn上建立nonclustered索引不失為一個好的方法。如果表格有clustered索引，那麼它用一個鏈表來維護資料頁之間的關係。相反，如果表格沒有clustered索引，SQL Server將在一個堆棧中儲存資料頁。

資料頁
當索引建立起來的時候，SQLServer就建立資料頁（datapage），資料頁是用以加速搜尋的指標。當索引建立起來的時候，其對應的填滿因數也即被設定。設定填滿因數的目的是為了指示該索引中資料頁的百分比。隨著時間的推移，資料庫的更新會消耗掉已有的空閑空間，這就會導致頁被拆分。頁面分割的後果是降低了索引的效能，因而使用該索引的查詢會導致資料存放區的支離破碎。當建立一個索引時，該索引的填滿因數即被設定好了，因此填滿因數不能動態維護。

為了更新資料頁中的填滿因數，我們可以停止舊有索引並重建索引，並重新設定填滿因數（注意：這將影響到當前資料庫的運行，在重要場合請謹慎使用）。DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引片段的兩個命令。INDEXDEFRAG是一種線上操作（也就是說，它不會阻塞其它表格動作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數情況下，重建索引可以更好的消除片段，但是這個優點是以阻塞當前發生在該索引所在表格上其它動作為代價換取來得。當出現較大的片段索引時，INDEXDEFRAG會花上一段比較長的時間，這是因為該命令的運行是基於小的互動塊（transactional block）。

填滿因數
當你執行上述措施中的任何一個，資料庫引擎可以更有效返回編入索引的資料。關於填滿因數（fillfactor）話題已經超出了本文的範疇，不過我還是提醒你需要注意那些打算使用填滿因數建立索引的表格。

在執行查詢時，SQL Server動態選擇使用哪個索引。為此，SQL Server根據每個索引上分布在該關鍵字上的統計量來決定使用哪個索引。值得注意的是，經過日常的資料庫活動（如插入、刪除和更新表格），SQL Server用到的這些統計量可能已經“到期”了，需要更新。你可以通過執行DBCC SHOWCONTIG來查看統計量的狀態。當你認為統計量已經“到期”時，你可以執行該表格的UPDATE STATISTICS命令，這樣SQL Server就重新整理了關於該索引的資訊了。

建立資料庫維護計劃
SQL Server提供了一種簡化並自動維護資料庫的工具。這個稱之為資料庫維護計劃嚮導（Database Maintenance Plan Wizard ，DMPW）的工具也包括了對索引的最佳化。如果你運行這個嚮導，你會看到關於資料庫中關於索引的統計量，這些統計量作為日誌工作並定時更新，這樣就減輕了手工重建索引所帶來的工作量。如果你不想自動定期重新整理索引統計量，你還可以在DMPW中選擇重新組織資料和資料頁，這將停止舊有索引並按特定的填滿因數重建索引。