索引允許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 數據庫索引