索引設計基礎知識

       索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。 索引包含由表或視圖中的一列或多列生成的鍵。 這些鍵存儲在一個結構（B 樹）中，使 SQL Server 可以快速高效地找到與鍵值關聯的行。

       為資料庫及其工作負荷選擇正確的索引是一項需要在查詢速度與更新所需開銷之間取得平衡的複雜任務。 如果索引較窄，或者說索引關鍵字中只有很少的幾列，則需要的磁碟空間和維護開銷都較少。 而另一方面，寬索引可覆蓋更多的查詢。 您可能需要試驗若幹不同的設計，才能找到最有效的索引。 可以添加、修改和刪除索引而不影響資料庫架構或應用程式設計。 因此，應試驗多個不同的索引而無需猶豫。

       SQL Server 中的查詢優化器可在大多數情況下可靠地選擇最高效的索引。 總體索引設計策略應為查詢優化器提供可供選擇的多個索引，並依賴查詢優化器做出正確的決定。 這在多種情況下可減少分析時間並獲得良好的性能。 若要查看查詢優化器對特定查詢使用的索引，請在 SQL Server Management Studio 中的“查詢”菜單上選擇“包括實際的執行計劃”。

        不要總是將索引的使用等同於良好的性能，或者將良好的性能等同於索引的高效使用。 如果只要使用索引就能獲得最佳性能，那查詢優化器的工作就簡單了。 但事實上，不正確的索引選擇並不能獲得最佳性能。 因此，查詢優化器的任務是只在索引或索引組合能提高性能時才選擇它，而在索引檢索有礙性能時則避免使用它。

索引分類規則 

• 聚集還是非聚集。
• 唯一還是非唯一。
• 單列還是多列。
• 索引中的列是升序排序還是降序排序。
• 非聚集索引是全表還是經過篩選。

索引簡介

聚集索引

聚簇索引是一種對磁碟上實際數據重新組織以按指定的一個或多個列的值排序。

聚集索引基於數據行的鍵值在表內排序和存儲這些數據行，在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。

每個表只能有一個聚集索引，因為數據行本身只能按一個順序存儲。

註意事項

一般情況下，定義聚集索引鍵時使用的列越少越好。

適用情況：

• 唯一或包含許多不重覆的值。

• 按順序被訪問。
• 定義為 IDENTITY。
• 經常用於對錶中檢索到的數據進行排序。

不適用情況：

• 頻繁更改的列

  這將導致整行移動，因為資料庫引擎必須按物理順序保留行中的數據值。 這一點要特別註意，因為在大容量事務處理系統中數據通常是可變的。

• 寬鍵

  寬鍵是若幹列或若幹大型列的組合。 所有非聚集索引將聚集索引中的鍵值用作查找鍵。 為同一表定義的任何非聚集索引都將增大許多，這是因為非聚集索引項包含聚集鍵，同時也包含為此非聚集索引定義的鍵列。

非聚集索引

索引理解為一種特殊的目錄，目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。

非聚集索引包含索引鍵值和指向表數據存儲位置的行定位器。

可以對錶或索引視圖創建多個非聚集索引，一個表如果沒有聚簇索引時，可有250個非聚簇索引。

通常，設計非聚集索引是為改善經常使用的、沒有建立聚集索引的查詢的性能。

與使用書中索引的方式相似，查詢優化器在搜索數據值時，先搜索非聚集索引以找到數據值在表中的位置，然後直接從該位置檢索數據。 這使非聚集索引成為完全匹配查詢的最佳選擇，因為索引包含說明查詢所搜索的數據值在表中的精確位置的項。

每個非聚簇索引提供訪問數據的不同排序順序。

註意事項

適用於：

• 某列常用於集合函數(如Sum,....)。
• 覆蓋查詢。
• 大量非重覆值，如姓氏和名字的組合（前提是聚集索引被用於其他列）。
• 查尋出的數據不超過表中數據量的20%。

唯一索引

唯一索引能夠保證索引鍵中不包含重覆的值，從而使表中的每一行從某種方式上具有唯一性。

只有當唯一性是數據本身的特征時，指定唯一索引才有意義。

使用多列唯一索引，索引能夠保證索引鍵中值的每個組合都是唯一的。

聚集索引和非聚集索引都可以是唯一的。 只要列中的數據是唯一的，就可以為同一個表創建一個唯一聚集索引和多個唯一非聚集索引。

註意事項

優點：

• 能夠確保定義的列的數據完整性。
• 提供了對查詢優化器有用的附加信息。

主鍵索引

主鍵是一個約束（constraint），它依附在一個索引上，這個索引可以是聚集索引，也可以是非聚集索引。

在資料庫關係圖中為表定義一個主鍵將自動創建主鍵索引，只能說明它上面有個索引，但不一定就是聚集索引。

主鍵具有唯一性，而只有唯一性索引才具有唯一性，主鍵索引要求主鍵中的每個值是唯一的。

當在查詢中使用主鍵索引時，它還允許快速訪問數據。

主鍵索引是唯一索引的特殊類型。

主鍵是索引，但索引不一定是主鍵。

篩選索引

篩選索引是一種經過優化的非聚集索引，尤其適用於涵蓋從定義完善的數據子集中選擇數據的查詢。

篩選索引使用篩選謂詞對錶中的部分行進行索引。

與全表索引相比，設計良好的篩選索引可以提高查詢性能、減少索引維護開銷並可降低索引存儲開銷。

篩選索引與全表索引相比具有以下優點：

• 提高了查詢性能和計劃質量
• 減少了索引維護開銷
• 減少了索引存儲開銷
• 僅包含少量非 NULL 值的稀疏列。
• 包含多種類別的數據的異類列。
• 包含多個範圍的值（如美元金額、時間和日期）的列。
• 由列值的簡單比較邏輯定義的表分區。

註意事項

適用於：

• 數據子集的篩選索引

  在列中只有少量相關值需要查詢時，可以針對值的子集創建篩選索引。

• 異類數據的篩選索引

          表中含有異類數據行時，可以為一種或多種類別的數據創建篩選索引。

• 鍵列

          最好在篩選索引定義中包含少量的鍵或包含列，並且只包含查詢優化器為查詢執行計劃選擇篩選索引所需的列。 無論某一篩選索引是否涵蓋了查詢，查詢優化器都可以為查詢選擇此篩選索引。 但是，如果某一篩選索引涵蓋了查詢，則查詢優化器更有可能選擇此篩選索引。

• 篩選謂詞中的數據轉換運算符

          如果篩選索引結果的篩選索引表達式中指定的比較運算符會導致隱式或顯式數據轉換，則轉換髮生在比較運算符的左邊時，會出現錯誤。 解決方法是在比較運算符的右邊編寫包含數據轉換運算符（CAST 或 CONVERT）的篩選索引表達式。

全文索引概念

全文引擎使用全文索引中的信息來編譯可快速搜索表中的特定詞或片語的全文查詢。 全文索引將有關重要的詞及其位置的信息存儲在資料庫表的一列或多列中。 全文索引是一種特殊類型的基於標記的功能性索引，它是由 SQL Server全文引擎生成和維護的。 生成全文索引的過程不同於生成其他類型的索引。 全文引擎並非基於特定行中存儲的值來構造 B 樹結構，而是基於要編製索引的文本中的各個標記來生成倒排、堆積且壓縮的索引結構。 全文索引大小僅受運行 SQL Server 實例的電腦的可用記憶體資源限制。

從 SQL Server 2008開始，全文索引與資料庫引擎集成在一起，而不是像 SQL Server早期版本那樣位於文件系統中。對於新資料庫，全文目錄現在為不屬於任何文件組的虛擬對象；它僅是一個表示一組全文索引的邏輯概念。

註意事項

• 全文索引是針對數據表，只能對錶創建全文索引，不能對資料庫創建全文索引。
• 每個資料庫可以不包含全文目錄或包含多個全文目錄，一個全文目錄可以包含多個全文索引，但一個全文索引只能用於構成一個全文目錄。
• 一個數據表只能創建一個全文索引，一個全文索引可以包含多個欄位。
• 創建全文索引的表必須要有一個唯一的非空索引，並且這個唯一的非空的索引只能是一個欄位，不能是組合欄位。
• 若要對某個表創建全文索引，該表必須具有一個唯一且非 Null 的列。您可以對以下類型的列創建全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max)，從而可對這些列進行全文搜索。對數據類型為 varbinary、varbinary(max)、image 或 xml 的列創建全文索引需要您指定類型列。類型列是用來存儲每行中文檔的文件擴展名（.doc、.pdf、xls 等）的表列。

XML索引

XML 索引分為下列類別：主 XML 索引和輔助 XML 索引

主 XML 索引

主 XML 索引對 XML 列中 XML 實例內的所有標記、值和路徑進行索引。 若要創建主 XML 索引，相應 XML 列所在的表必須對該表的主鍵創建了聚集索引。 SQL Server 使用此主鍵將主 XML 索引中的行與包含此 XML 列的表中的行關聯起來。

主 XML 索引是中的 XML Blob 的已拆分和持久的表示形式xml數據類型列。 對於列中的每個 XML 二進位大型對象 (BLOB)，索引將創建數個數據行。 該索引中的行數大約等於 XML 二進位大型對象中的節點數。 當查詢檢索完整的 XML 實例時， SQL Server 會提供此 XML 列中的實例。 XML 實例中的查詢使用主 XML 索引，並可以通過使用索引本身返回標量值或 XML 子樹。

輔助 XML 索引

為了增強搜索性能，可以創建輔助 XML 索引。 必須有了主 XML 索引才能創建輔助索引。 輔助索引的類型如下：

• PATH 輔助 XML 索引

　　   如果查詢通常對 xml 類型列指定路徑表達式，則 PATH 輔助索引可以提高搜索的速度。

• VALUE 輔助 XML 索引

          如果查詢是基於值的查詢，例如 /Root/ProductDescription/@*[. = "Mountain Bike"] 或 //ProductDescription[@Name = "Mountain Bike"]，且沒有完全指定路徑或路徑包含有通配符，則生成基於主 XML 索引中的節點值所創建的輔助 XML 索引可以更快地獲得結果。

          VALUE 索引的鍵列是主 XML 索引的節點值和路徑。 如果您的工作負荷涉及到查詢 XML 實例中的值，但不知道包含這些值的元素名稱或屬性名稱，則 VALUE 索引可能會很有用。

• PROPERTY 輔助 XML 索引

          從單個 XML 實例檢索一個或多個值的查詢適用 PROPERTY 索引。 通過檢索對象屬性時，會發生這種value （) 方法的xml類型並且知道對象的主鍵值。

          PROPERTY 索引是對主 XML 索引的列（PK、Path 和節點值）創建的，其中 PK 是基表的主鍵。

以下為創建一個或多個輔助索引的一些準則：

• 如果工作負荷對 XML 列大量使用路徑表達式，則 PATH 輔助 XML 索引可能會提高工作負荷的處理速度。 最常見的情況是在 Transact-SQL 的 WHERE 子句中對 XML 列使用 exist() 方法。

• 如果工作負荷通過使用路徑表達式從單個 XML 實例中檢索多個值，則在 PROPERTY 索引中聚集各個 XML 實例中的路徑可能會很有用。 這種情況通常出現在屬性包方案中，此時提取對象的屬性並且已知其主鍵值。

• 如果工作負荷涉及查詢 XML 實例中的值，但不知道包含那些值的元素名稱或屬性名稱，則您可能希望創建 VALUE 索引。 這通常出現在 descendant 軸查找中，例如 //author[last-name="Howard"]，其中 <author> 元素可以出現在層次結構的任何級別上。 這種情況也出現在通配符查詢中，例如 /book [@* = "novel"]，其中查詢將查找具有某個值為“novel”的屬性的 <book> 元素。