索引架構和設計指南

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫

設計高效的索引是實現良好資料庫和應用程式效能的關鍵。 缺乏索引、過度索引或設計不當的索引是資料庫效能問題的主要來源。

本指南說明索引架構和基本概念，並提供最佳實務來協助您設計有效的索引，以符合應用程式的需求。

如需可用索引類型的詳細資訊，請參閱 索引。

本指南涵蓋以下類型的索引：

主要儲存體格式	索引類型
磁碟型資料列存放區
	Clustered
	Nonclustered
	Unique
	Filtered
Columnstore
	叢集資料行存放區
	非叢集資料行存放區
Memory-optimized
	Hash
	記憶體最佳化非叢集

如需 XML 索引的資訊，請參閱 XML 索引 和 選擇性 XML 索引 (SXI)。

如需空間索引的資訊，請參閱空間索引概觀。

如需全文檢索索引的資訊，請參閱擴展全文檢索索引。

索引基本概念

思考一下一般的書籍：在書籍的結尾會有一個索引，可協助您快速找到書籍內的資訊。 索引是排序的關鍵字清單，每個關鍵字旁會有頁碼，指出可以在哪些頁面找到這些關鍵字。

資料列存放區索引類似：它是值的有序清單，每個值都有指向這些值所在資料 頁面 的指標。 索引本身也儲存在頁面上，稱為 索引頁面。 在普通書籍中，如果索引跨越多個頁面，並且您必須找到指向包含該單詞 SQL 的所有頁面的指標，則必須從索引的開頭翻閱，直到找到包含關鍵字 SQL的索引頁面。 從那裡，您可以遵循指向所有書籍頁面的指標。 如果您在索引的開頭建立一個頁面，包含可在哪裡找到每個字母的字母順序清單，就可以讓過程更有效率。 例如：「A 到 D - 第 121 頁」、「E 到 G - 第 122 頁」等等。 這個額外的頁面，可以免除翻閱索引來找出開始位置的步驟。 這類頁面不會存在於一般書籍中，而是存在於資料列存放區索引中。 此單一頁面稱為索引的根頁面。 根頁面是索引所使用的樹狀結構起始頁。 按照樹狀結構的類比，包含實際資料指標的結尾頁面，稱為樹狀結構的「分葉頁面」。

索引是一種與資料表或檢視有關的磁碟內存或記憶體內部結構，它會加快從該資料表或檢視中擷取資料列的速度。 資料列存放區索引包含從資料表或視圖中的一或多個資料行的值構成的索引鍵。 針對資料列存放區索引，這些索引鍵會儲存在樹狀結構 （B+ 樹狀結構） 中，讓資料庫引擎能夠快速且有效率地尋找與索引鍵值相關聯的資料列。

資料列存放區索引會以邏輯方式儲存資料，以邏輯方式組織為具有資料列和資料行的資料表，並以稱為 資料列存放區¹ 的資料列格式實際儲存。 還有另一種方式可以按欄儲存資料，稱為 columnstore。

為資料庫及其工作負載設計正確的索引是查詢速度、索引更新成本和儲存成本之間的複雜平衡行為。 基於磁碟的窄資料列索引，或索引鍵中只有少數資料行的索引，需要較少的儲存空間和較小的更新額外負擔。 另一方面，寬索引可能會改善更多查詢。 在找到最有效的索引集之前，您可能必須嘗試數種不同的設計。 隨著應用程式的發展，索引可能需要變更才能維持最佳效能。 可以新增、修改和刪除索引，而不會影響資料庫綱目或應用程式設計。 所以，請勿吝於嘗試各種不同的索引。

資料庫引擎中的查詢最佳化工具通常會選擇最有效的索引來執行查詢。 若要查看查詢最佳化工具用於特定查詢的索引，請在 SQL Server Management Studio 的 [查詢] 功能表上，選取 [顯示預估執行計劃 ] 或 [包含實際執行計劃]。

使用索引不一定就會有良好的效能，良好的效能和有效率地使用索引也不能劃上等號。 如果使用索引對產生最佳效能一定有幫助，查詢最佳化工具的作業就很單純。 但事實上，選擇不正確的索引可能得不到最佳效能。 因此，查詢最佳化工具的任務是只有在提高效能時才選擇索引或索引組合，並在阻礙效能時避免索引檢索。

一個常見的設計錯誤是推測性地創建許多索引以“給優化器選擇”。 由此產生的過度索引會減慢資料修改速度，並可能導致並行問題。

¹ 資料列存放區一直是傳統儲存關聯式資料表資料的方式。 Rowstore 表 是指基礎資料儲存格式為堆疊、B+ 樹狀結構（叢集索引）或記憶體最佳化資料表的表格。 磁碟型資料列存放區不包括經記憶體最佳化的資料表。

索引設計工作

下列的工作構成了我們建議的索引設計策略：

1. 了解資料庫和應用程式的特性。

   例如，在線上交易處理 (OLTP) 資料庫中，需要頻繁進行資料修改且必須維持高效能，一些以最關鍵查詢為目標的窄行存儲索引會是良好的初始索引設計。 如需極高的輸送量，請考慮記憶體最佳化的資料表和索引，它們提供無鎖定和閂鎖設計。 如需詳細資訊，請參閱本指南中的 記憶體最佳化非叢集索引設計指導方針 和 雜湊索引設計指導方針 。

   相反地，對於必須快速處理非常大型資料集的分析或資料倉儲 （OLAP） 資料庫，使用叢集資料行存放區索引會特別合適。 如需詳細資訊，請參閱本指南中的 資料行存放區索引：概觀 或 資料行存放區索引架構 。

2. 了解最常用查詢的特性。

   例如，知道常用查詢會聯結兩個或多個資料表，有助於您判斷這些資料表的索引集。

3. 瞭解查詢述詞中使用的資料行中的資料分佈。

   例如，索引可能對具有許多不同資料值的資料行有用，但對於具有許多重複值的資料行則不太有用。 對於具有許多 NULL 或具有明確定義資料子集的資料行，您可以使用篩選索引。 如需詳細資訊，請參閱本指南中的 篩選索引設計指導方針。

4. 確定哪些索引選項可以增強效能。

   例如，在現有的大型資料表上建立叢集索引可能會受益於索引選項 ONLINE。 ONLINE 選項會在建立或重建索引的同時，允許繼續進行基礎資料上的並行活動。 使用資料列或頁面 資料壓縮 可以減少索引的 I/O 和記憶體使用量，以增進效能。 如需詳細資訊，請參閱 CREATE INDEX。

5. 檢查資料表上的現有索引，以防止建立重複或非常相似的索引。

   修改現有索引通常比建立新的但大部分重複的索引更好。 例如，請考慮將一或兩個額外的包含欄新增至現有索引，而不是使用這些欄建立新索引。 當您 調整缺少索引建議的非叢集索引時，或當您使用 資料庫引擎調整顧問時，這尤其重要，因為在相同的資料表和資料行上，您可能會被建議類似的索引變化。

一般索引設計指導方針

了解資料庫、查詢和表格資料行的特性可以幫助您最初設計最佳索引，並隨著應用程式的發展修改設計。

資料庫考量

當您設計索引時，請考慮下列資料庫指導方針：

• 資料表上的大量索引會影響 INSERT、UPDATE、DELETE 和 MERGE 陳述式的效能，因為索引中的資料可能必須隨著資料表中的資料變更而變更。 例如，如果在多個索引中使用一個直欄，並且您執行修改 UPDATE 該直欄資料的陳述式，則也必須更新包含該直欄的每個索引。

  • 請避免對時常更新的資料表過度索引，保持索引窄小，愈少資料行愈好。

  • 您可以在資料修改很少但資料量較大的資料表上擁有更多索引。 對於這類資料表，各種索引可協助查詢效能，而索引更新額外負荷仍可接受。 不過，請勿以推測方式建立索引。 監控索引使用情況，並隨時間移除未使用的索引。

• 為小型資料表編製索引可能不是最佳選擇，因為資料庫引擎周遊索引搜尋資料的時間可能比執行基底資料表掃描花費更長的時間。 因此，小型表格上的索引可能永遠不會使用，但仍必須在表格中的資料更新時進行更新。

• 當檢視包含彙總和/或聯結時，檢視上的索引可以提供顯著的效能增益。 如需詳細資訊，請參閱建立索引檢視表。

• Azure SQL Database 中主要複本上的資料庫會自動為索引產生資料庫建議程式效能建議。 您可以選擇性地啟用自動索引微調。

• 查詢存放區可協助識別效能不佳的查詢 ，並提供 查詢執行計劃 的歷程記錄，讓您查看最佳化工具所選取的索引。 您可以使用此資料，透過專注於最頻繁和最耗用資源的查詢，使索引調整變更最具影響力。

查詢考量

當您設計索引時，請考慮下列查詢指導方針：

• 在 述詞 中經常使用的資料行上建立非叢集索引，並在查詢中聯結運算式。 這些是您的 SARGable 列。 不過，您應避免將不必要的資料行新增到索引中。 新增太多索引直欄可能會對磁碟空間及索引更新效能產生負面影響。

  關聯式資料庫中的術語 SARGable 是指可以使用索引來加速查詢執行的 Search ARGument 述詞。 如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

  Tip

  一律請確定您建立的索引實際上是由查詢工作負載使用。 捨棄未使用的索引。

  索引使用統計資料可在 sys.dm_db_index_usage_stats 和 sys.dm_db_index_operational_stats中使用。

• 涵蓋索引可以增進查詢效能，因為查詢就存在於索引本身裡面，所有需要的資料都符合查詢的需求。 也就是說，擷取要求的資料時只需要索引頁，非資料表或叢集索引的資料頁；因此，可以減少整體的磁碟 I/O。 例如，某個資料表在 A 、 B 和 A資料行上已建立複合的索引，則 B和 C 資料行的查詢可以單獨從索引擷取指定的資料。

  Note

  涵蓋索引是非叢集索引，可直接滿足查詢的所有資料存取，而不需要存取基底資料表。

  這類索引包含在索引鍵中的所有必要的 SARGable 直欄，以及作為附加直欄的 非SARGable 直欄。 這表示查詢所需的所有資料行（無論是在 WHERE、JOIN 和 GROUP BY 子句中，還是在 SELECT 或 UPDATE 子句中）都存在於索引中。

  如果索引相較於資料表本身的資料列和資料行足夠窄，這意味著它只包含資料行的一小部分，那麼執行查詢的 I/O 可能會少得多。

  在從大型表格中擷取一小部分資料時，如果該小部分是由固定述詞定義的，請考慮使用涵蓋索引。

  避免建立具有太多資料行的涵蓋索引，因為這會降低其優點，同時膨脹資料庫儲存、I/O 和記憶體使用量。

• 撰寫的查詢應盡可能在一個陳述式中插入或修改最多資料列，而不是使用多個查詢來更新同樣的資料列。 這可減少索引更新額外負荷。

資料行考量

當您設計索引時，請考慮下列資料行指導方針：

• 保持索引鍵的長度較短，特別是叢集索引。

• 無法將 ntext、text、image、varchar（max）、nvarchar（max）、varbinary（max）、json 和 vector 資料類型的資料行指定為索引鍵資料行。 不過，具有這些資料類型的資料行可以新增至非叢集索引，做為非鍵資料行（包含於索引中）。 如需詳細資訊，請參閱本指南中的在 非叢集索引中使用包含的資料行 一節。

• 檢驗資料行唯一性。 唯一索引，而不是相同索引鍵資料行上的非唯一索引，會為查詢最佳化工具提供其他資訊，讓索引更有用。 如需詳細資訊，請參閱本指南中的 唯一索引設計指導方針。

• 檢驗資料行中的資料分散情形。 在具有許多資料列但相異值很少的資料行上建立索引，即使查詢最佳化工具使用索引，也可能無法改善查詢效能。 打個比方，如果城市中的所有人都叫史密斯或瓊斯，則按姓氏字母順序排序的實體電話簿並不能加快找到一個人的速度。 如需有關資料分散的詳細資訊，請參閱 統計資料。

• 請考慮在具有明確定義子集的直欄上使用過濾索引，例如具有許多 NULL 的直欄、具有值類別的直欄，以及具有不同值範圍的直欄。 如果該子集與許多查詢相關，設計良好的篩選索引可以透過在資料表中儲存所有資料列的一小部分來提高查詢效能、降低索引更新成本並降低儲存成本。

• 如果索引鍵包含多個資料行，請考慮索引鍵資料行的順序。 在相等 （=）、不等式 （>，>=<<=） 或BETWEEN運算式中的查詢述詞中使用的直欄，或參與聯結的直欄應該放在最前面。 其他資料行應該按照它們的區分程度排序，亦即，從最能區分的排到最不能區分的。

  例如，如果索引定義為 LastName，則當子句中的FirstName查詢述詞為 WHERE 或 WHERE LastName = 'Smith'WHERE LastName = Smith AND FirstName LIKE 'J%'時，索引很有用。 不過，查詢最佳化工具不會針對僅搜尋WHERE FirstName = 'Jane'的查詢使用索引，或者索引不會提升這類查詢的效能。

• 如果計算資料行包含在查詢述詞中，請考慮為它們編製索引。 如需詳細資訊，請參閱 計算資料行的索引。

指數特徵

當確定適合某一查詢的索引之後，便可選取最符合您需要的索引類型。 指數特徵包括：

• 叢集或非叢集
• 唯一或非唯一
• 單欄或多欄
• 索引的主要欄位按照遞增或遞減順序排列
• 所有資料列或已篩選，適用於非叢集索引
• 欄式儲存或列式儲存
• 記憶體最佳化資料表的雜湊或非叢集

檔案群組或資料分割配置上的索引位置

在您開發索引設計策略時，您應該考慮在資料庫關聯的檔案群組上之索引位置。

根據預設，索引會儲存在與建立索引的基底資料表 （叢集索引或資料堆積） 相同的檔案群組中。 其他配置是可能的，包括：

• 在基底資料表檔案群組以外的檔案群組上建立非叢集索引。

• 將叢集與非叢集索引分割以跨越多個檔案群組。

針對非分割的資料表，最簡單的方法通常是最好的方法：在相同的檔案群組上建立所有資料表，並視需要將盡可能多的資料檔案新增至檔案群組，以利用所有可用的實體儲存體。

當分層儲存可用時，可以考慮更進階的索引放置方法。 例如，您可以為經常存取的資料表建立檔案群組，其中包含較快磁碟上的檔案，並為較慢磁碟上的封存資料表建立檔案群組。

您可以刪除叢集索引，並在MOVE TO子句的DROP INDEX陳述式中指定新的檔案群組或分割區配置，或者在CREATE INDEX子句中使用DROP_EXISTING陳述式，將具有叢集索引的資料表從一個檔案群組移至另一個檔案群組。

資料分割索引

您也可以考慮將基於磁碟的堆積、聚集的索引和非聚集的索引分割到多個檔案群組中。 分割的索引會根據分割區函數進行水平切分（依列）。 分割區函數會根據您指定的特定資料行 （稱為分割資料行） 的值，定義如何將每一列對映至分割區。 分割區配置會指定一組分割區與檔案群組的對應。

分割索引將可提供下列優點：

• 讓大型資料庫更易於管理。 例如，OLAP 系統可以實作分割區感知 ETL，以大幅簡化大量新增和移除資料。

• 讓特定類型的查詢 （例如長時間執行的分析查詢） 執行得更快。 當查詢使用分割索引時，資料庫引擎可以同時處理多個分割區，並略過 （消除） 查詢不需要的分割區。

警告

分割區很少能改善 OLTP 系統中的查詢效能，但如果交易式查詢必須存取許多分割區，則可能會造成重大額外負荷。

如需詳細資訊，請參閱 資料分割資料表和索引。

索引排序順序設計指導方針

在定義索引時，請考慮每個索引鍵列是否應該以升序或降序儲存。 遞增是預設值。 CREATE INDEX、CREATE TABLE 和 ALTER TABLE 陳述式的語法，可在索引及條件約束的個別資料行上支援關鍵字 ASC (遞增) 與 DESC (遞減)。

指定在索引中將以何種順序儲存索引鍵值，在遇到下列情況時會很有用：參考資料表的查詢具有 ORDER BY 子句，且此子句指定的排序方向與該索引中索引鍵資料行的方向不同。 在這些情況下，索引可以去除查詢計劃中 Sort運算子 的需要。

例如，Adventure Works Cycles 公司採購部門的採購員必須評估他們向供應商採購的產品品質。 買家最感興趣的是尋找拒絕率高的供應商發送的產品。

如以下針對 AdventureWorks 範例資料庫的查詢所示，要擷取符合這項條件的資料，需要將 RejectedQty 資料表中的 Purchasing.PurchaseOrderDetail 資料行依遞減順序 (由大到小) 儲存，並將 ProductID 資料行則依遞增順序 (由小到大) 儲存。

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

此查詢的 下列執行計劃 顯示查詢最佳化工具使用 Sort 運算子，以子句指定的 ORDER BY 順序傳回結果集。

如果建立的磁碟型資料列存放區索引的索引鍵資料行與查詢中的 ORDER BY 子句相符，則查詢計劃中的 Sort 運算子將被消除，使其更加有效率。

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

再次執行查詢之後，下列執行計劃會顯示 Sort 運算子不再存在，而且會使用新建立的非叢集索引。

資料庫引擎可以在任一方向掃描索引。 定義為 RejectedQty DESC, ProductID ASC 的索引仍可用於子句中 ORDER BY 直欄排序方向反轉的查詢。 例如，具有子句ORDER BY的ORDER BY RejectedQty ASC, ProductID DESC查詢可以使用相同的索引。

排序次序只能針對索引中的索引鍵資料行指定。 sys.index_columns 型錄視圖報告索引欄位的儲存順序是遞增還是遞減。

叢集索引設計指引

叢集索引會儲存資料表的所有資料列和資料行。 資料列會依索引鍵值的順序排序。 每個資料表只能有一個叢集索引。

基底資料表一詞可以指叢集索引或資料堆。 堆積是磁碟上未排序的資料結構，包含資料表的所有列和所有欄。

除了少數例外情況外，每個資料表都應該有一個叢集索引。 叢集索引的預期屬性如下：

房產	Description
窄	叢集索引鍵是相同基底資料表上任何非叢集索引的一部分。 狹窄的索引鍵，或是鍵資料行總長度較小的索引鍵，可減少資料表上所有索引的儲存、I/O，以及記憶體的額外負荷。 若要計算索引鍵長度，請將索引鍵資料行所使用資料類型的儲存大小相加。 如需詳細資訊，請參閱 資料類型類別。
獨特	如果叢集索引不是唯一的，則會自動將 4 位元組的內部唯一值資料行新增至索引鍵，以確保唯一性。 將現有的唯一資料行新增至叢集索引鍵，可以避免資料表上所有索引中唯一化資料行所產生的存儲、I/O 和記憶體的額外負荷。 此外，查詢最佳化工具可以在索引是唯一的時產生更有效率的查詢計劃。
不斷增加	在不斷增加的索引中，資料一律會新增至索引的最後一頁。 這可避免索引中間的頁面分割，從而降低 頁面密度 並降低效能。
不可變	叢集索引鍵是任何非叢集索引的一部分。 修改叢集索引的索引鍵資料行時，也必須在所有非叢集索引中進行變更，這會增加 CPU、記錄、I/O 和記憶體額外負荷。 如果叢集索引的索引鍵資料行是不可變的，則可以避免額外負荷。
僅有非空欄位	如果某資料列包含可為 Null 的資料行，則必須納入一種稱為 NULL 區塊的內部結構，這會在索引中每列增加 3 至 4 位元組的儲存空間。 讓叢集索引的所有欄位皆為非空值，可避免此額外負荷。
只有固定寬度的欄位	相較於固定寬度資料類型，使用可變寬度資料類型 （例如 varchar 或 nvarchar ） 的資料行會使用每個值額外的 2 個位元組。 使用固定寬度資料類型 （例如 int ） 可避免資料表上所有索引中的此額外負荷。

在設計叢集索引時，盡可能滿足這些屬性，不僅會讓叢集索引，而且會讓相同資料表上的所有非叢集索引更有效率。 透過避免儲存體、I/O 和記憶體額外負荷來改善效能。

例如，具有單一不可為 null 的 int 或 bigint 資料行的叢集索引鍵，如果是透過使用IDENTITY的子句或預設條件約束進行填入，且在插入資料列後未更新，則具有所有這些屬性。

相反地，具有單一 uniqueidentifier 資料行的叢集索引鍵會更寬，因為它使用 16 個位元組的儲存體，而不是 int 的 4 個位元組和 bigint 的 8 個位元組，而且除非依序產生值，否則不會滿足 單調遞增 的屬性。

Tip

當您建立 PRIMARY KEY 限制時，會自動建立支援限制的唯一索引。 根據預設，此索引是叢集的;不過，如果此索引不符合叢集索引所需的屬性，您可以將條件約束建立為非叢集，並改為建立不同的叢集索引。

如果您未建立叢集索引，資料表會儲存為堆積，通常不建議這樣做。

叢集索引架構

資料列存放區索引會組織為 B+ 樹狀結構。 索引 B+ 樹狀目錄中的每個分頁稱為索引節點。 B+ 樹狀目錄的頂部節點稱為根節點。 索引中的最下層節點稱為分葉節點。 根與分葉節點之間的任何索引層級通稱為中繼層級。 在叢集索引中，分葉節點包含基礎資料表的資料頁。 根和中繼層級節點包含保存索引資料列的索引頁。 每個索引資料列都包含索引鍵值，以及指向 B+ 樹狀目錄之中繼層級分頁或索引分葉層級之資料列的指標。 索引每個層級中的分頁都以雙向連結清單方式連結。

叢集索引在每個索引使用的分割區中都有一個資料列 sys.partitions，index_id = 1。 根據預設，叢集索引只有一個資料分割。 當叢集索引有多個分割區時，每個分割區都有個別的 B+ 樹狀結構，其中包含該特定分割區的資料。 例如，如果叢集索引有四個分割區，則有四個 B+ 樹狀結構，每個分割區中一個。

視叢集索引中的資料類型而定，每個叢集索引結構有一個或多個配置單位來儲存並管理特定資料分割的資料。 在每個叢集索引中，每個資料分割至少有一個 IN_ROW_DATA 配置單位。 如果叢集索引包含大型物件 （LOB） 資料行，例如 LOB_DATA則每個分割區也有一個配置單位。 如果每個分割區包含可變長度直欄，而這些直欄超過 8,060 位元組的資料列大小限制，則每個分割區也會有一個 ROW_OVERFLOW_DATA 配置單位。

B+ 樹狀結構中的頁面會依叢集索引鍵的值排序。 所有插入都會在插入列中的索引鍵值符合現有頁面之間排序順序的頁面上進行。 在頁面中，列不一定以任何實體順序儲存。 不過，頁面會使用稱為 插槽陣列的內部結構來維護資料列的邏輯順序。 插槽陣列中的項目會以索引鍵順序維護。

下圖顯示單一資料分割中的叢集索引結構。

非叢集索引設計指導方針

叢集索引與非叢集索引之間的主要差異在於，非叢集索引包含資料表中資料行的子集，通常排序方式與叢集索引不同。 或者，可以篩選非叢集索引，這表示它包含資料表中所有資料列的子集。

磁碟型資料列存放區非叢集索引包含指向基底資料表中資料列儲存位置的資料列定位器。 您可以在資料表或索引檢視表上建立多個非叢集索引。 一般而言，非叢集索引的設計應該是改善經常使用查詢的效能，否則需要掃描基底資料表。

如同您使用書中索引的方式一樣，查詢最佳化工具會先搜尋非叢集索引以找出資料值在資料表中的位置，然後再直接從該位置擷取資料，即可搜尋資料值。 這讓非叢集索引成為執行完全符合的查詢之最佳選擇，因為此種索引所包含的項目會描述查詢中所要搜尋的資料值在資料表中的確定位置。

例如，若要查詢資料表 HumanResources.Employee 中所有向特定經理報告的員工，查詢最佳化工具可能會使用非叢集索引 IX_Employee_ManagerID，其第一個索引鍵資料行是 ManagerID。 因為值 ManagerID 是在非叢集索引中排序，所以查詢最佳化工具可以快速找到索引中符合指定 ManagerID 值的所有項目。 每個索引項目都指向基底表中的確切頁面和列，其中可以從所有其他資料行擷取對應的資料。 查詢最佳化工具找到索引中的所有項目後，可以直接前往確切的頁面和行來擷取數據，而不是掃描整個基表。

非叢集索引架構

磁碟型資料列存放區非叢集索引具有與叢集索引相同的 B+ 樹狀結構，但有下列差異：

• 非叢集索引不一定包含資料表的所有資料行和資料列。

• 非叢集索引的分葉層級是由索引頁 (而不是資料頁) 所組成。 非叢集索引的葉層級索引頁面包含鍵資料行。 或者，它們也可能包含表格中其他資料行的子集，作為 包含的資料行，以避免從基底資料表擷取它們。

非叢集索引資料列中的資料列定位器是資料列的指標，或是資料列的叢集索引索引鍵，說明如下：

• 如果資料表有叢集索引，或索引位於索引檢視中，則資料列定位器為資料列的叢集索引鍵。

• 如果資料表為堆積 (表示其沒有叢集索引)，則資料列定位器為資料列的指標。 該指標將以檔案識別碼 (識別碼)、頁碼與分頁中的資料列編號來建立。 整個指標也稱為資料列識別碼 (RID)。

資料列定位器也可確保非叢集索引資料列的唯一性。 下表描述資料庫引擎如何將資料列定位器新增至非叢集索引中：

基底表類型	非叢集索引類型	列定位器
Heap
	Nonunique	加入索引鍵資料行的 RID
	Unique	加入內含資料行的 RID
唯一叢集索引
	Nonunique	加入索引鍵資料行的叢集索引鍵
	Unique	加入內含資料行的叢集索引鍵
非唯一叢集索引
	Nonunique	加入索引鍵資料行的叢集索引鍵和唯一識別值 (若存在)
	Unique	加入內含資料行的叢集索引鍵和唯一識別值 (若存在)

資料庫引擎絕不會在非叢集索引中多次儲存指定的資料行。 每當使用者建立非叢集索引時，皆會遵循其所指定的索引鍵順序：所有需要新增至非叢集索引鍵的資料列定位器資料行，皆會在索引定義中指定的資料行之後，新增至索引鍵的結尾。 非叢集索引中的叢集索引鍵資料列定位器可用於查詢處理，不論它們是在索引定義中明確指定或隱含新增。

下列範例示範如何在非叢集索引中實作資料列定位器：

叢集索引	非叢集索引定義	具有資料列定位器的非叢集索引定義	Explanation
具有索引鍵資料行 (A、B、C) 的唯一叢集索引	具有索引鍵資料行 (B、A) 和內含資料行 (E、G) 的非唯一非叢集索引	索引鍵資料行 (B、A、C) 和內含資料行 (E、G)	非叢集索引是非唯一的，因此資料列定位器必須存在於索引鍵中。 資料列定位器中的資料行 B 和 A 已存在，因此只會新增資料行 C。 資料行 C 會加入索引鍵資料行清單的結尾。
具有索引鍵資料行 (A) 的唯一叢集索引	具有索引鍵資料行 (B、C) 和內含資料行 (A) 的非唯一非叢集索引	索引鍵資料行 (B、C、A)	非叢集索引是非唯一的，因此會將資料列定位器加入索引鍵中。 資料行 A 尚未指定為索引鍵資料行，因此它會加入索引鍵資料行清單的結尾。 資料行 A 現在位於索引鍵中，因此不需要將它儲存為內含資料行。
具有索引鍵資料行 (A、B) 的唯一叢集索引	具有索引鍵資料行 (C) 的唯一非叢集索引	索引鍵資料行 (C) 和內含資料行 (A、B)	非叢集索引是唯一的，因此會將資料列定位器加入內含資料行中。

非叢集索引在索引所使用的每個分割區的 sys.partitions 中都有一列，其中 index_id > 1有 。 根據預設，非叢集索引只有一個資料分割。 當非叢集索引有多個資料分割時，每個資料分割都有一個 B+ 樹狀目錄結構來包含該特定資料分割的索引資料列。 例如，如果非叢集索引有四個分割區，則有四個 B+ 樹狀結構，每個分割區中一個。

視非叢集索引中的資料類型而定，每個非叢集索引結構有一個或多個配置單位來儲存並管理特定資料分割的資料。 每個非叢集索引至少每個分割區都有一個 IN_ROW_DATA 配置單位，用於儲存索引 B+ 樹狀結構頁面。 如果非叢集索引包含大型物件 （LOB） 資料行，例如 LOB_DATA則每個分割區也有一個配置單位。 此外，如果每個分割區包含超過 8,060 位元組資料列大小限制的可變長度資料行，則每個分割區都有一個分配單元 ROW_OVERFLOW_DATA。

下圖顯示單一資料分割中非叢集索引的結構。

在非叢集索引中使用包含的資料行

除了索引鍵資料行之外，非叢集索引也可以在分葉層級中儲存非索引鍵資料行。 這些非鍵欄稱為包含的直欄，並在INCLUDE語句的CREATE INDEX子句中指定。

包含非索引鍵資料行的索引在涵蓋查詢時，也就是當查詢中使用的所有資料行都以索引鍵或非索引鍵資料行的形式出現在索引中時，可以大幅改善查詢效能。 之所以能取得效能提升，是因為資料庫引擎可以找出索引內的所有資料行值;不會存取基底資料表，因此磁碟 I/O 作業較少。

如果查詢需要擷取資料行，但該資料行未用於查詢描述詞、彙總和排序中，請將它新增為包含資料行，而不是作為關鍵資料行。 這有以下優點：

• 包含的資料行可以使用不允許的資料類型作為索引鍵資料行。

• 資料庫引擎在計算索引鍵資料行數目或索引鍵大小時，不會考慮包含的資料行。 使用包含的資料行時，您不會受到 900 位元組金鑰大小上限的限制。 您可以建立涵蓋更多查詢的更廣泛索引。

• 當您將資料行從索引鍵移至包含的資料行時，索引建置所花費的時間會更少，因為索引排序作業會變得更快。

如果資料表具有叢集索引，則叢集索引索引鍵中定義的一或多個資料行會自動新增至資料表上的每個非唯一非叢集索引。 不需要在非叢集索引鍵中指定它們，或指定為包含的資料行。

包含資料行的索引準則

當您設計具有包含資料行的非叢集索引時，請考慮下列指導方針：

• 包含的欄位只能在資料表或索引檢視的非叢集索引中定義。

• 允許所有的資料類型，除了 text、 ntext和 image以外。

• 具決定性之精確或非精確的計算資料行都可以當做內含資料行。 如需詳細資訊，請參閱 計算資料行的索引。

• 與索引鍵資料行一樣，衍生自 image、ntext 和 text 資料類型的計算資料行可以作為包含資料行，但前提是計算資料行的資料類型在包含資料行中是被允許的。

• 無法同時在 INCLUDE 清單與索引鍵資料行清單中指定資料行名稱。

• 資料行名稱在 INCLUDE 清單中不得重複。

• 索引中必須至少定義一個主要欄位。 包含的資料行數目上限為 1,023。 這是資料表資料行數目的上限減 1。

• 不論是否包含併入直欄，索引鍵直欄都必須遵循現有的索引大小限制，即最多 16 個索引鍵直欄，以及總索引鍵大小 900 個位元組。

包含資料行的索引設計建議

請考慮重新設計具有大型索引鍵大小的非叢集索引，以便只有查詢述詞、彙總和排序中使用的資料行是索引鍵資料行。 讓涵蓋查詢的所有其他資料行都作為內含非索引鍵之索引資料行。 如此一來，您就擁有涵蓋查詢所需的所有資料行，但是索引鍵本身會變得很小而且很有效率。

例如，假設您要設計能夠涵蓋下列查詢的索引。

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';

若要涵蓋查詢，必須在索引中定義每個資料行。 雖然您可以將所有資料行定義為索引鍵資料行，但是索引鍵大小應是 334 個位元組。 由於只有作為搜尋條件的資料行才是 PostalCode 資料行，且長度為 30 個位元組，所以較佳的索引設計方式應該是將 PostalCode 定義為索引鍵資料行，並將所有其他的資料行作為非索引鍵之索引資料行包含在內。

下列陳述式會建立具有內含資料行的索引，可以涵蓋查詢。

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

若要驗證索引是否涵蓋查詢，請建立索引，然後顯示估計的執行計畫。 如果執行計劃顯示索引搜尋運算子並且針對IX_Address_PostalCode的話，則該索引已涵蓋查詢。

包含資料行的索引效能考量

避免建立包含大量資料行的索引。 即使索引可能涵蓋更多查詢，其效能優勢也會降低，因為：

• 頁面上可放入的索引資料列變少。 這會增加磁碟 I/O 並降低快取效率。

• 需要更多磁碟空間來儲存索引。 特別是，在包含的資料行中新增 varchar（max）、nvarchar（max）、varbinary（max） 或 xml 資料類型，可以大幅增加磁碟空間需求。 這是因為資料行的值複製到索引的分葉層級。 因此，它們會同時存在於索引與基底資料表中。

• 資料修改效能會降低，因為必須在基礎資料表和非叢集索引中修改許多資料行。

您必須判斷查詢效能的增益是否超過資料修改效能的降低和磁碟空間需求的增加。

唯一索引設計指導方針

唯一索引可確保索引鍵不包含重複值。 只有當唯一性是資料本身的特徵時，才有可能建立唯一索引。 例如，若要確定 NationalIDNumber 資料表中 HumanResources.Employee 資料行的值是唯一的，則當主索引鍵為 EmployeeID 時，請在 UNIQUE 資料行上建立 NationalIDNumber 條件約束。 限制不允許引入具有重複國民身分證號碼的資料列。

利用多重資料行唯一索引，此索引可保證索引鍵的每一個值組合都是唯一的。 例如，如果在 LastName、FirstName 和 MiddleName 欄位的組合上建立唯一索引，則表中沒有兩列的這些欄位可以具有相同的值。

叢集與非叢集索引都可以是唯一的。 您可以在相同的資料表上建立唯一的叢集索引和多個唯一的非叢集索引。

獨特索引的好處包括：

• 會強制執行需要資料唯一性的商務規則。
• 提供對查詢最佳化工具有幫助的其他資訊。

建立 PRIMARY KEY 或 UNIQUE 條件約束時，會自動在指定的資料行上建立唯一索引。 建立 UNIQUE 條件約束和不使用條件約束而建立唯一索引，兩者之間並沒有顯著的差異。 資料驗證的方式相同，且查詢最佳化工具不會區分唯一索引是由條件約束所建立還是手動建立。 不過，當強制執行商務規則是目標時，您應該在欄位上建立 UNIQUE 或 PRIMARY KEY 限制。 如此一來，索引的目標就很明確。

獨特索引考量

• 如果資料中已存在重複的索引鍵值，則無法建立唯一索引、UNIQUE 條件約束或 PRIMARY KEY 條件約束。

• 如果資料是唯一的，而且您想要強制唯一性，則在相同資料行組合上建立唯一索引而不是非唯一索引，可提供其他資訊給查詢最佳化工具，來產生更有效率的執行計畫。 在此情況下，建議建立 UNIQUE 限制或唯一索引。

• 唯一非叢集索引可有內含的非索引鍵之索引資料行。 如需詳細資訊，請參閱 在非叢集索引中使用包含的資料行。

• 與 PRIMARY KEY 條件約束不同，UNIQUE 條件約束或唯一索引可以在索引鍵中包含可為 Null 的資料行來建立。 為了強制執行唯一性，兩個 NULL 被視為相等。 例如，這表示在單一欄位的唯一索引中，該欄位在整個表格中只能有一列的數值為 NULL。

篩選索引設計指導方針

篩選索引是最佳化的非叢集索引，特別適合需要資料表中一小部分資料的查詢。 它使用索引定義中的過濾器述詞來索引表格中部分列。 與全表索引相比，設計良好的過濾索引可以提高查詢效能、降低索引更新成本以及降低索引儲存成本。

篩選索引可以提供全資料表索引所不及的下列優勢：

• 提升的查詢效能和計畫品質

  設計良好的篩選索引可改善查詢效能和執行計劃品質，因為它比完整資料表非叢集索引小。 過濾索引具有過濾統計資料，這些 統計資料比完整表格統計資料更準確，因為它們只涵蓋過濾索引中的列。

• 降低索引更新成本

  只有在資料操作語言 （DML） 陳述式影響索引中的資料時，才會更新索引。 相較於完整資料表非叢集索引，篩選索引可降低索引更新成本，因為它較小，而且只有在索引中的資料受到影響時才會更新。 篩選索引的數量可能很多，特別是當其包含不常受到影響的資料時。 同樣地，如果過濾的索引只包含經常受影響的資料，則較小的索引大小會降低更新統計資料的成本。

• 降低的索引儲存成本

  在不需要完整資料表索引的情況下，建立篩選索引可以降低非叢集索引的磁碟儲存量。 您也許可以將完整資料表非叢集索引取代為多個篩選索引，而不會大幅增加儲存體需求。

當資料行包含定義完善的資料子集時，篩選索引很有用。 範例如下：

• 包含許多空值的資料行。

• 包含資料類別的異質資料行。

• 包含數值範圍的資料行，例如金額、時間和日期。

與完整表格索引相比，當索引中的資料列數目較少時，篩選索引的更新成本降低最為明顯。 如果篩選索引包含資料表中大部分的資料列，則其維護成本可能要比全資料表索引高。 在此種情況下應該使用全資料表索引，而不是篩選索引。

篩選索引定義於單一資料表，僅支援簡單比較運算子。 如果您需要具有複雜邏輯或參考多個資料表的篩選運算式，您應該建立 索引計算資料行 或 索引檢視。

篩選索引設計考量

為了設計有效的篩選索引，必須了解應用程式所使用的查詢以及這些查詢與資料子集的關聯。 具有明確定義子集的資料範例包括具有許多 NULL 的資料行、具有異質值類別的直行，以及具有不同值範圍的直行。

下列設計考慮提供數個案例，說明篩選索引何時可以提供相對於完整資料表索引的優勢。

資料子集的篩選索引

當資料行僅具有少數的查詢相關值時，您可以在值的子集上建立篩選索引。 例如，當資料行大部分為 NULL 且查詢只需要非 NULL 值時，您可以建立包含非 NULL 資料列的篩選索引。

例如，AdventureWorks 範例資料庫有一個含 2679 個資料列的 Production.BillOfMaterials 資料表。 該 EndDate 資料行只有 199 列包含非 NULL 值，其他 2480 列包含 NULL。 下列篩選索引涵蓋傳回索引中定義的資料行的查詢，且只需要具有非 NULL 值 EndDate的資料列。

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

篩選索引 FIBillOfMaterialsWithEndDate 對下列查詢有效。 顯示估計的執行計畫，以判斷查詢最佳化工具是否使用了篩選索引。

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';

如需有關如何建立篩選索引以及如何定義篩選索引述詞運算式的詳細資訊，請參閱 建立篩選的索引。

異質資料的篩選索引

當資料表具有異質資料列時，您可以針對一或多個資料類別建立篩選索引。

例如，列於 Production.Product 資料表中的每個產品都會被指派 ProductSubcategoryID，此 ID 又會與產品類別 Bikes、Components、Clothing 或 Accessories 等產生關聯。 這些類別都是異質性的，因為它們在 Production.Product 資料表中的資料行值並沒有緊密關聯。 例如，對個別產品類別來說， Color、 ReorderPoint、 ListPrice、 Weight、 Class和 Style 等資料行都有其唯一的特性。 假設經常對子類別介於 27 到 36 (含) 的配件進行查詢。 您只要在配件子類別上建立篩選索引，就可以改善查詢該配件的效能，如下列範例所示。

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
INCLUDE (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

篩選的索引 FIProductAccessories 涵蓋下列查詢，因為查詢結果包含在索引中，而且查詢計劃不需要存取基底資料表。 例如，查詢述詞運算式 ProductSubcategoryID = 33 是篩選之索引述詞 ProductSubcategoryID >= 27 的子集，而且查詢述詞中的 ProductSubcategoryID <= 36、 ProductSubcategoryID 與 ListPrice 資料行都是索引中的索引鍵資料行，因此會將名稱當做包含的資料行，儲存在索引的分葉層級中。

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;

篩選索引中的索引鍵和包含資料行

最佳做法是僅在查詢最佳化工具需要篩選索引來選擇查詢執行計劃時，於篩選的索引定義中新增少量必要的資料行。 查詢最佳化工具可以選擇查詢的篩選索引，不論它是否涵蓋該查詢。 然而，查詢最佳化工具如果涵蓋該查詢，則更可能選擇篩選索引。

在某些情況下，篩選索引會涵蓋查詢，而不需將資料行以篩選索引定義中的索引鍵或內含資料行方式包含在篩選索引運算式中。 下列指導方針說明篩選索引運算式中的資料行何時應該是篩選索引定義中的索引鍵或內含資料行。 其中的範例會參考先前所建立的篩選索引 FIBillOfMaterialsWithEndDate 。

如果篩選索引運算式相等於查詢述詞，且查詢並未以篩選索引運算式中的資料行傳回查詢結果，則篩選索引運算式中的資料行不需要是篩選索引定義中的索引鍵或內含資料行。 例如，FIBillOfMaterialsWithEndDate 涵蓋下列查詢，因為查詢述詞相當於篩選條件運算式，而且查詢結果未傳回 EndDate。 FIBillOfMaterialsWithEndDate索引EndDate不需要做為篩選索引定義中的索引鍵或包含資料行。

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

如果查詢述詞在與篩選索引運算式相異的比較中，使用篩選索引運算式中的資料行，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。 例如， FIBillOfMaterialsWithEndDate 對下列查詢有效，因為它會從篩選索引選取資料列子集。 不過，它並不會涵蓋下列查詢，因為 EndDate 用於比較 EndDate > '20040101' 中，而後者並不等同於篩選索引運算式。 查詢處理器無法在不檢查 的 EndDate值的情況下執行此查詢。 因此， EndDate 應該是篩選索引定義中的索引鍵或內含資料行。

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

如果篩選索引運算式中的資料行在查詢結果集中，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。 例如，FIBillOfMaterialsWithEndDate 並不涵蓋下列查詢，因為它會在查詢結果中傳回 EndDate 資料行。 因此， EndDate 應該是篩選索引定義中的索引鍵或內含資料行。

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

資料表的叢集索引鍵並不需要是篩選索引定義中的索引鍵或內含資料行。 叢集索引鍵會自動包含在所有非叢集的索引中 (包含篩選索引在內)。

篩選述詞中的資料轉換運算子

如果在篩選索引的篩選索引運算式中指定的比較運算子產生隱含或明確的資料轉換，則若該轉換發生在比較運算子的左側，就會發生錯誤。 解決方案是以資料轉換運算子 (CAST 或 CONVERT) 在比較運算子的右側寫入篩選索引運算式。

下列範例會建立具有不同資料類型資料行的資料表。

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY(4)
);

在下列篩選索引定義中，資料行 b 會隱含地轉換成整數資料類型，以將其與常數 1 進行比較。 這會產生錯誤訊息 10611，因為轉換發生在篩選述詞中運算子的左側。

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = 1;

解決方案是將右側的常數轉譯為與資料行 b相同的類型，如下列範例中所示：

CREATE INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = CONVERT (VARBINARY(4), 1);

將資料轉換從比較運算子的左側移至右側可能會變更轉換的意義。 在上一個範例中，當將CONVERT運算子新增至右側時，比較會從int比較變更為varbinary比較。

資料行存放區索引架構

資料行存放區索引是一種使用資料欄式資料格式 （稱為資料行存放區） 來儲存、擷取和管理資料的技術。 如需詳細資訊，請參閱 數據行存放區索引：概觀。

如需版本資訊並了解新增功能，請瀏覽資料行存放區索引的新增功能。

了解這些基本知識可以更輕鬆地理解其他解釋如何有效使用此技術的欄存放區文章。

資料儲存使用欄式儲存和列式儲存

討論資料行存放區索引時，我們使用「資料列存放區」和「資料行存放區」等字詞強調資料儲存格式。 資料行存放區索引會使用這兩種儲存類型。

• 「資料行存放區」是以邏輯方式組織成資料表的資料，其中包含資料列和資料行，並且會以資料行取向的資料格式實際儲存。

  資料行存放區索引是以資料行存放區格式實際儲存大部分的資料。 在資料行存放區格式中，資料會以資料行的方式進行壓縮和解壓縮。 不需要解壓縮每個資料列中查詢未要求的其他值。 如此一來，便可快速掃描大型資料表的整個資料行。

• 「資料列存放區」是以邏輯方式組織成資料表的資料，其中包含資料列和資料行，並且會以資料列取向的資料格式實際儲存。 這是儲存關聯式資料表資料的傳統方式，例如叢集 B+ 樹索引或堆疊。

  資料行存放區索引也會以稱為 Deltastore 的資料列存放區格式實際儲存一些資料列。 差異存放區 (也稱為差異資料列群組) 是一種保存空間，用來保存數量太少而沒有資格壓縮到資料行存放區的資料列。 每個 Delta 資料列群組都實作為叢集 B+ 樹索引，也就是列式存儲。

作業是在資料列群組和資料行區段上執行

資料行存放區索引會將資料列分組成可管理的單位。 每個單位稱為資料列群組。 為了獲得最佳效能，資料列群組中的資料列數目要足夠大，以改善壓縮比，又要小到足以從記憶體作業中獲益。

例如，資料行存放區索引會對資料列群組執行下列作業：

• 將資料列群組壓縮到資料行存放區。 對資料列群組內的每個資料行區段執行壓縮。

• 在ALTER INDEX ... REORGANIZE作業期間合併列群組，並包括移除已刪除的資料。

• 在執行 ALTER INDEX ... REBUILD 操作時，重建所有資料列群組。

• 在動態管理檢視 (DMV) 中報告資料列群組健全狀況和片段。

差異存放區是由一或多個資料列群組所組成，稱為差異資料列群組。 每個增量資料列群組都是叢集 B+ 樹索引，它儲存小型的批量載入資料和插入資料，直到資料列群組包含 1,048,576 個資料列。此時，一個稱為 元組移動器 的程序會自動將已滿的資料列群組壓縮到資料行存儲中。

如需有關資料列群組狀態的詳細資訊，請參閱 sys.dm_db_column_store_row_group_physical_stats。

Tip

太多小型資料列群組會降低資料行存放區索引的品質。 遵循內部閾值原則 (決定如何移除已刪除的資料列以及合併已壓縮的資料列群組)，重新組織操作會合併較小的資料列群組。 合併後，索引品質會改善。

在 SQL Server 2019（15.x）及更高版本中，元組移動器的工作會由背景合併任務協助完成。這項任務會自動壓縮已經存在超過某段時間（由內部閾值決定）的較小開啟差異資料列群組，或合併已刪除大量資料列的壓縮資料列群組。

在每個資料列群組中，每個資料行都有一些資料行值。 這些值稱為資料行區段。 每一個資料列群組會針對資料表中的每一個資料行包含一個資料行區段。 在每個資料列群組中，每個資料行都有一個資料行區段。

當資料行存放區索引壓縮資料列群組時，它會個別壓縮每一個資料行區段。 若要解壓縮整個資料行，資料行存放區索引只需要解壓縮每個資料列群組中的一個資料行區段。

少量載入和插入會進入差異存放區

藉由一次至少將 102,400 個資料列壓縮到資料行存放區索引，資料行存放區索引可改善資料行存放區壓縮和效能。 為了大量壓縮資料列，資料行存放區索引會在差異存放區中累積少量載入和插入。 deltastore 操作會在背景中進行處理。 若要傳回查詢結果，叢集資料行存放區索引會結合資料行存放區和異動存放區的查詢結果。

資料列會進入差異存放區的情況：

• 和 INSERT INTO ... VALUES 陳述式一起插入。

• 位於大量載入結尾，而且數量小於 102,400。

• Updated. 每項更新的實作方式為刪除和插入。

差異存放區也會儲存已刪除資料列的識別碼清單，已刪除資料列是標示為已刪除，但尚未從資料行存放區中實際刪除的資料列。

當差異資料列群組已滿時，即會將其壓縮到資料行存放區

叢集資料行存放區索引在將資料列群組壓縮到資料行存放區之前，在每個差異資料列群組中最多可收集 1,048,576 個資料列。 如此可改善資料行存放區索引的壓縮。 差異資料列群組一旦達到資料列數目上限，就會從 OPEN 轉換為 CLOSED 狀態。 名為 Tuple Mover 的背景處理序會檢查已關閉的資料列群組。 如果處理序發現已關閉的資料列群組，便會壓縮資料列群組，並將其儲存至資料行存放區中。

當差異資料列群組已壓縮時，現有的差異資料列群組會轉換成 TOMBSTONE 狀態，供 Tuple Mover 稍後在其沒有參考時移除，且新的已壓縮資料列群組會標記為 COMPRESSED。

如需有關資料列群組狀態的詳細資訊，請參閱 sys.dm_db_column_store_row_group_physical_stats。

您可以使用 ALTER INDEX 強制將差異資料列群組壓縮到資料行存放區，以重建或重新組織索引。 如果在壓縮期間有記憶體不足的壓力，資料行存放區索引可能會減少已壓縮資料列群組中的資料列數量。

每個資料表資料分割都有自己的資料列群組和差異資料列群組

資料分割的概念在叢集索引、堆積和資料行存放區索引中是相同的。 分割資料表作業會根據一系列的資料行值，將資料表分割成較小的資料列群組。 這通常用於管理資料。 例如，您可以為每年的資料建立分割區，然後使用分割區切換將舊資料封存到較便宜的儲存體。

資料列群組一律定義在資料表資料分割內。 當分割資料行存放區索引時，每個資料分割都有自己的壓縮資料列群組和差異資料列群組。 非分割表格包含一個分割區。

Tip

如果需要從資料行存放區移除資料，請考慮使用資料表分割。 更換和截斷不再需要的分割區，是刪除資料的有效策略，而不會在欄儲存庫中引入碎片。

每個資料分割可以有多個差異資料列群組

每個資料分割可以有多個差異資料列群組。 當欄存索引需要將資料新增至暫存資料列群組，且該暫存資料列群組已被另一個交易鎖住時，欄存索引會嘗試鎖定不同的暫存資料列群組。 如果沒有任何可用的差異資料列群組，資料行存放區索引就會建立新的差異資料列群組。 例如，具有 10 個資料分割的資料表可以輕鬆擁有 20 多個差異資料列群組。

合併相同資料表上的資料行存放區索引和資料列存放區索引

非叢集索引包含基礎資料表中部分或所有資料列和資料行的複本。 此索引會定義為資料表的一或多個資料行，並具有篩選資料列的選用條件。

您可以在資料列存放區資料表上建立可更新的非叢集資料行存放區索引。 資料行存放區索引會儲存資料的複本，因此您需要額外的儲存空間。 不過，儲存列索引中的資料會壓縮成比列存儲表所需的大小小得多的大小。 藉由這樣做，您可以同時在資料列存放區索引上執行資料行存放區索引和 OLTP 工作負載的分析。 當資料列存放區資料表中的資料變更時，會更新資料行存放區，讓兩個索引會針對相同的資料執行。

資料列儲存表最多可以擁有一個非叢集式資料行存放區索引。 如需詳細資訊，請參閱 數據行存放區索引 - 設計指引。

您可以在叢集資料行存放區資料表上建立一個或多個非叢集列存放區索引。 如此一來，您就可以對基礎資料行存放區執行有效率的資料表搜尋。 其他選項現在也可以使用。 例如，您可以在資料列存放區資料表上使用 UNIQUE 條件約束來強制執行唯一性。 當非唯一值無法插入資料列存放區資料表時，資料庫引擎也不會將值插入資料行存放區。

非叢集列存儲效能考量

非叢集資料行存放區索引定義支援使用篩選的條件。 若要將新增資料行存放區索引的效能影響降到最低，請使用篩選運算式，僅在分析所需的資料子集上建立非叢集資料行存放區索引。

記憶體最佳化資料表可以有一個資料行存放區索引。 您可以在建立資料表時建立它，或稍後使用 ALTER TABLE 新增它。

如需詳細資訊，請參閱 數據行存放區索引 - 查詢效能。

記憶體最佳化雜湊索引設計指導方針

使用 In-Memory OLTP 時，所有記憶體最佳化資料表都必須至少有一個索引。 對於記憶體最佳化資料表，每個索引也會進行記憶體最佳化。 雜湊索引是記憶體最佳化資料表中可能有的索引類型之一。 如需詳細資訊，請參閱記憶體最佳化資料表的索引。

記憶體最佳化哈希索引架構

雜湊索引由指標陣列組成，而陣列的每個項目稱為雜湊值區。

• 每個值區是 8 位元組，可用來儲存索引鍵項目連結清單的記憶體位址。
• 每個項目都是一個索引鍵的值，加上其基礎記憶體最佳化資料表中對應資料列的位址。
• 每個項目指向項目連結清單中的下一個項目，全都連結到目前的值區。

儲存貯體數目必須在索引建立時指定：

• 值區與資料表資料列或相異值的比率越低，平均值區連結清單就越長。
• 短連結清單的執行速度比長連結清單還快。
• 雜湊索引的值區數目上限是 1,073,741,824。

Tip

若要判斷您資料的正確 BUCKET_COUNT，請參閱設定雜湊索引值區計數。

索引鍵資料行要套用雜湊函式，且函式的結果會決定索引鍵落入哪些值區。 每個值區都有一個資料列指標，這些資料列的雜湊索引鍵值會對應到該值區。

用於雜湊索引的雜湊函數具有下列特性：

• 資料庫引擎有一個雜湊函數可用於所有雜湊索引。
• 該雜湊函數具決定性。 相同的輸入索引鍵值一律對應到雜湊索引中的相同值區。
• 多個索引鍵可能對應至相同的雜湊值區。
• 平衡雜湊函數，表示索引鍵值在雜湊值區上的分配通常會遵循波氏或常態分佈，不是線性分佈。
• 波氏分配不是平均分配。 索引鍵值不是平均分佈在雜湊值區中。
• 如果兩個索引鍵對應到相同雜湊值區，就會發生雜湊衝突。 大量的雜湊衝突可能會對讀取作業產生效能影響。 實際目標是讓儲存桶中有30%包含兩個不同的鍵值。

下圖摘要說明雜湊索引和值區的相互作用。

設定雜湊索引值區計數

雜湊索引值區計數是在索引建立時間指定，並可使用 ALTER TABLE...ALTER INDEX REBUILD 語法來變更。

在大多數情況下，分桶數應介於索引鍵中相異值數目的 1 到 2 倍之間。 您可能不一定能夠預測特定索引鍵具有多少個值。 如果 BUCKET_COUNT 值在實際索引鍵值數目的 10 倍內，效能通常仍然不錯，高估一般而言會比低估好。

值區太少會有下列缺點：

• 有更多相異索引鍵值的雜湊衝突。
• 每個相異值都會強制與不同的相異值共用相同的值區。
• 每個值區的平均鏈結長度都會增加。
• 值區鏈結越長，索引中的等號比較查閱的速度就越慢。

值區太多會有下列缺點：

• 值區計數過高可能會導致更多的空值區。
• 空值區會影響完整索引掃描的效能。 如果定期執行掃描，請考慮挑選接近相異索引鍵值數目的值區計數。
• 空值區會使用記憶體，但每個值區只使用 8 個位元組。

Note

新增更多值區，對於減少將共用重複值的項目鏈結在一起的情況，沒有任何助益。 值重複率可用來決定雜湊索引或非叢集索引是否為適當的索引類型，而不是計算儲存貯體計數。

雜湊索引的效能考量

雜湊索引的效能如下︰

• 當 WHERE 子句的述詞為雜湊索引鍵中的每個資料行指定確切的值時極佳。 若是不相等的述詞，雜湊索引會還原至掃描。
• 當 WHERE 子句的述詞在索引鍵中尋找某個值範圍時效能不佳。
• 當 WHERE 子句的述詞為有兩個資料行的雜湊索引鍵的第一個資料行指定一個特定值，但未針對該索引鍵的其他資料行指定值時，效能不佳。

Tip

述詞必須包含雜湊索引鍵中的所有欄。 雜湊索引需要整個索引鍵才能訪問索引。

如果使用雜湊索引，且唯一索引鍵的數目比資料列數目少超過 100 倍，請考慮增加分桶數來避免長資料列鏈結，或者改用 非叢集索引。

建立雜湊索引

建立雜湊索引時，請考慮：

• 雜湊索引只能存在於記憶體最佳化資料表上。 它無法存在於以磁碟為基礎的資料表上。
• 哈希索引預設為非唯一，但可以宣告為唯一。

下列範例會建立唯一的雜湊索引：

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE HASH (Column2) WITH (BUCKET_COUNT = 64);

記憶體最佳化資料表中的資料列版本和垃圾回收

在記憶體最佳化資料表中，當資料列受到陳述式影響 UPDATE 時，資料表會建立資料列的更新版本。 在更新交易期間，其他工作階段或許能夠讀取舊版資料列，藉此避免發生與資料列鎖定相關聯的效能低落。

雜湊索引可能也有不同版本的項目來容納更新。

不再需要較舊的版本之後，記憶體回收 (GC) 執行緒會周遊值區及其連結清單來清除舊項目。 如果連結清單鏈結長度很短，GC 執行緒的執行效能更好。 如需詳細資訊，請參閱記憶體內部 OLTP 記憶體回收。

記憶體最佳化的非叢集索引設計指導方針

除了雜湊索引之外，非叢集索引是記憶體最佳化資料表中其他可能的索引類型。 如需詳細資訊，請參閱記憶體最佳化資料表的索引。

記憶體最佳化非叢集索引架構

記憶體最佳化資料表上的非叢集索引是使用稱為 Bw 樹狀結構的資料結構來實作，該結構最初由 Microsoft Research 在 2011 年設想和描述。 Bw 型樹狀結構是鎖定且不需閂鎖的 B 型樹狀結構變化。 如需詳細資料，請參閱 Bw 型樹狀結構：新硬體平台的 B 型樹狀結構。

在高層級上，Bw 樹狀結構可以理解為依頁面 ID （PidMap） 組織的頁面地圖、分配和重複使用頁面 ID （PidAlloc） 的工具，以及頁面地圖中連結的一組頁面，以及彼此連結的頁面。 這三個高層級的子元件構成 Bw 型樹狀結構的基本內部結構。

結構類似於一般的 B 型樹狀結構，即每個頁面都有一組排序的索引鍵值，而且每個指向較低層級及分葉層級的索引都有指向資料列的層級。 但是，有數個相異之處。

就像雜湊索引一樣，可以將多個資料列連結在一起以支援版本控制。 層級之間的分頁指標是邏輯頁面識別碼，也就是頁面對應表中的位移，依次具有每個頁面的實體位址。

索引頁沒有就地更新。 為此推出新的差異頁面。

• 頁面更新不需要閂鎖或鎖定。
• 索引頁沒有固定大小。

每個非分葉層級頁面中的索引鍵值是它所指向的子項所包含的最高值，而且每一列也包含該頁面邏輯頁面識別碼。 在分葉層級頁面以及索引鍵值中，包含資料列的實體位址。

點查閱類似於 B 樹狀結構，不同之處在於，因為頁面只以一個方向連結，所以資料庫引擎會遵循正確的頁面指標，其中每個非分葉頁面都有其子系的最高值，而不是像 B 樹狀結構中的最低值。

如果分葉層級頁面必須變更，資料庫引擎不會修改頁面本身。 相反地，資料庫引擎會建立描述變更的異動記錄，並將其附加至前一頁。 然後也會將前一頁的頁面對應表位址更新成現在已成為此頁面實體位址的差異記錄位址。

管理 Bw 型樹狀結構可能需要執行三項不同的作業：彙總、分割及合併。

Delta 盤整

大量的增量記錄 (delta records) 最終會降低搜尋效能，因為在搜尋索引時可能需要長鏈遍歷。 如果新的差異記錄新增到已有 16 個項目的鏈結，則差異記錄中的變更會彙總至所參考的索引頁，並重建該頁面，包括觸發彙總之新差異記錄指出的變更。 最近重建的頁面上會有相同的頁面識別碼，但有新的記憶體位址。

分割頁面

Bw 型樹狀結構中的索引頁可視需要從儲存單一資料列成長，最多儲存 8 KB。 一旦索引頁成長至 8 KB，新插入單一資料列就會分割索引頁。 若為內部頁面，這表示沒有可以新增其他索引鍵值和指標的空間；若為分葉頁面，這表示資料列可能太大，頁面無法容納所有差異記錄。 分葉頁面標頭中的統計資訊會追蹤需要多少空間才能合併差異記錄。 該資訊會在新增每個新的差異記錄時調整。

分割作業是在兩個不可部分完成的步驟中完成。 下圖中，假設分葉頁面會強制分割，因為插入值為 5 的索引鍵，而且有非分葉頁面指向目前分葉層級頁面 (索引鍵值 4) 的結尾。

步驟 1：配置兩個新的頁面 P1 和 P2，並將資料列從舊的 P1 頁面分割到這些新的頁面，包括新插入的資料列。 頁面對應表中新的位置是用來儲存頁面 P2 的實體位址。 頁面 P1 和 P2 尚無法被任何並行作業存取。 此外，已設定從 P1 到 P2 的邏輯指標。 接著，在一個不可部分完成的步驟中更新頁面對應表，將指標從舊的 P1 變更為新的 P1。

步驟 2：非分葉頁面指向 P1，但是沒有從非分葉頁面指向 P2 的直接指標。 只能透過 P2 連線到 P1。 若要從非分葉頁面建立指向 P2 的指標，請配置新的非分葉頁面 (內部索引頁)、複製所有舊的非分葉頁面的資料列，再新增新的資料列指向 P2。 完成後，在一個不可部分完成的步驟中更新頁面對應表，將指標從舊的非分葉頁面變更為新的非分葉頁面。

合併頁面

當作業導致頁面的頁面大小小於最大頁面大小 （8 KB） 的 10%，或頁面上只有單一資料列時 DELETE ，該頁面會與連續頁面合併。

從頁面刪除一個資料列時，會新增此項刪除的差異記錄。 此外，還會進行檢查，以判斷索引頁面 （非分葉頁面） 是否符合合併資格。 這項檢查會確認刪除資料列後的剩餘空間是否會小於頁面大小上限的 10%。 如果符合條件，則合併會以三個原子步驟執行。

下圖中，假設 DELETE 作業刪除索引鍵值 10。

步驟 1：建立代表索引鍵值 10 (藍色三角形) 的差異頁面，它在非分葉頁面 Pp1 中的指標設定為指向新的差異頁面。 另建立特殊的合併差異頁面 (綠色三角形)，連結指向差異頁面。 在此階段，任何並行交易都看不到這兩個頁面 (差異頁面和合併差異頁面)。 在某個不可部分完成的步驟中，頁面對應表中指向分葉層級頁 P1 的指標會更新為指向合併差異頁面。 這個步驟之後，10 中的索引鍵值 Pp1 項目現在會指向合併差異頁面。

步驟 2：必須移除非分葉頁面 7 中代表索引鍵值 Pp1 的資料列，並更新索引鍵值 10 項目指向 P1。 為此，應配置新的非分葉頁面 Pp2，並複製 Pp1 中除代表索引鍵值 7 以外的所有資料列，然後更新索引鍵值 10 的資料列指向頁面 P1。 完成之後，在一個不可部分完成的步驟中，更新指向 Pp1 的頁面對應表項目指向 Pp2。 無法再連線到 Pp1。

步驟 3：合併分葉層級頁面 P2 和 P1，並移除差異頁面。 若要這樣做，要配置新的頁面 P3，合併 P2 和 P1 的資料列，在新的 P3 中包含差異頁面變更。 然後，在一個不可部分完成的步驟中，更新指向 P1 頁面的頁面對應表項目指向頁面 P3。

記憶體最佳化非叢集索引的效能考量

當查詢具有不等式述詞的記憶體最佳化資料表時，非叢集索引的效能優於雜湊索引。

記憶體最佳化資料表中的資料行，可以是雜湊索引和非叢集索引的一部分。

當非叢集索引中的索引鍵資料行有許多重複的值時，效能會因為更新、插入及刪除作業而降低。 在此情況下，改善效能的一種方法是新增一個在索引鍵中具有較佳選擇性的資料行。

索引中繼資料

若要檢查索引中繼資料，例如索引定義、內容及資料統計資料，請使用下列系統檢視：

• sys.objects
• sys.indexes
• sys.index_columns
• sys.columns
• 系統類型
• sys.partitions
• sys.internal_partitions
• sys.dm_db_index_usage_stats
• sys.dm_db_partition_stats
• sys.dm_db_index_operational_stats

先前的檢視會套用至所有索引類型。 針對資料行存放區索引，另外使用下列視圖：

• sys.column_store_row_groups
• sys.column_store_segments
• sys.column_store_dictionaries
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats

若為資料行存放區索引，所有資料行都會儲存於中繼資料內成為內含資料行。 資料行存放區索引沒有索引鍵資料行。

對於記憶體最佳化資料表上的索引，請額外使用下列檢視：

• sys.hash_indexes
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.memory_optimized_tables_internal_attributes

相關內容

• 創建索引（Transact-SQL）
• 將索引維護最佳化以改善查詢效能並降低資源耗用量
• 資料分割資料表與索引
• 記憶體最佳化資料表上的索引
• 列存儲索引：概觀
• 計算資料行的索引
• 使用遺漏索引建議調整非叢集索引