記憶體最佳化資料表上的索引

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

所有記憶體最佳化資料表都必須至少有一個索引，因為它是將資料列連線在一起的索引。 在記憶體最佳化資料表上，每個索引也會進行記憶體最佳化。 有數種方式可用來區分經記憶體最佳化的資料表上的索引和以磁碟為基礎之資料表上的傳統索引：

• 資料列並未儲存在頁面上，因此不存在頁面或區的集合，也不存在可供參考以取得資料表所有頁面的分割區或配置單位。 索引頁面的概念是可用類型的索引之一，但其儲存方式不同於以磁碟為基礎之資料表的索引。 它們不會在頁面內產生傳統類型的碎片化，因此沒有填滿因子。
• 在資料操作期間，對經記憶體最佳化的資料表上索引的變更永遠不會寫入磁碟。 只有資料列和資料的變更會寫入交易記錄。
• 當資料庫再次上線時，會重建記憶體最佳化索引。

經記憶體最佳化的資料表上的所有索引都會根據資料庫復原期間的索引定義來建立。

索引必須是下列其中一項：

• 雜湊索引
• 記憶體最佳化非叢集索引（意指 B-tree 的預設內部結構）

雜湊索引在記憶體最佳化資料表的雜湊索引中有更詳細的討論。
「非叢集」索引會在記憶體最佳化資料表的非叢集索引中詳細討論。
資料行存放區索引已在另一篇文章中討論。

記憶體最佳化索引的語法

記憶體優化資料表的每個 CREATE TABLE 陳述式都必須包含索引，無論是明確透過 INDEX 指定，還是隱含透過 PRIMARY KEY 或 UNIQUE 條件約束指定。

若要以預設的 DURABILITY = SCHEMA_AND_DATA 宣告，記憶體最佳化資料表必須具有主鍵。 以下 CREATE TABLE 陳述式中的 PRIMARY KEY NONCLUSTERED 子句符合兩項要求：

• 提供一個索引，以符合 CREATE TABLE 陳述式至少有一個索引的最低要求。

• 提供 SCHEMA_AND_DATA 子句所需的主鍵。

  CREATE TABLE SupportEvent  
  (  
      SupportEventId   int NOT NULL  
          PRIMARY KEY NONCLUSTERED,  
      ...  
  )  
      WITH (  
          MEMORY_OPTIMIZED = ON,  
          DURABILITY = SCHEMA_AND_DATA);  
  

語法的程式碼範例

本小節包含 Transact-SQL 程式碼區塊，示範在記憶體最佳化資料表上建立各種索引的語法。 這個程式碼示範下列作業：

1. 建立記憶體最佳化資料表。

2. 使用 ALTER TABLE 語句來增加兩個索引。

3. INSERT 幾行資料。

   DROP TABLE IF EXISTS SupportEvent;  
   go  
   
   CREATE TABLE SupportEvent  
   (  
       SupportEventId   int               not null   identity(1,1)  
       PRIMARY KEY NONCLUSTERED,  
   
       StartDateTime        datetime2     not null,  
       CustomerName         nvarchar(16)  not null,  
       SupportEngineerName  nvarchar(16)      null,  
       Priority             int               null,  
       Description          nvarchar(64)      null  
   )  
       WITH (  
       MEMORY_OPTIMIZED = ON,  
       DURABILITY = SCHEMA_AND_DATA);  
   go  
   
       --------------------  
   
   ALTER TABLE SupportEvent  
       ADD CONSTRAINT constraintUnique_SDT_CN  
       UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
   go  
   
   ALTER TABLE SupportEvent  
       ADD INDEX idx_hash_SupportEngineerName  
       HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
   go  
   
       --------------------  
   
   INSERT INTO SupportEvent  
       (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
       VALUES  
       ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
       ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
       ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
       ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
   go 
   

重複的索引鍵值

重複的索引鍵值可能會降低記憶體最佳化資料表效能。 供系統在大多數索引讀取與寫入作業中遍歷項目鏈的重複項目。 當重複項目的鏈結超過 100 個項目時，效能降低可能會變得很明顯。

重複的雜湊值

在雜湊索引的情況下，這個問題更明顯可見。 基於下列情況，雜湊索引會受到較大的影響：

• 雜湊索引的每次操作成本較低。
• 大型重複鏈結對雜湊碰撞鏈結的干擾。

若要減少索引中的重複項目，請嘗試下列調整：

• 使用非叢集索引。
• 在索引鍵尾端新增額外的資料行，以減少重複項目的數量。
  • 例如，您可以新增也屬於主索引鍵的欄位。

如需雜湊衝突的詳細資訊，請參閱記憶體最佳化資料表的雜湊索引。

改善範例

以下說明如何避免您的索引出現任何效能低落問題的範例。

假設有一個 Customers 資料表，其主鍵為 CustomerId，且在 CustomerCategoryID 資料欄上有索引。 一般來說，指定的類別中會有許多客戶。 因此，所指定索引鍵內會有許多重複的 CustomerCategoryID 值。

在此情況下，最佳做法是在 (CustomerCategoryID, CustomerId) 上使用非叢集索引。 此索引可用於使用 CustomerCategoryID 相關述詞的查詢，但索引鍵不包含重複項目。 因此，無論是重複的 CustomerCategoryID 值，還是索引中的額外資料行，都不會導致索引維護效率低落。

下列查詢會顯示範例資料庫 CustomerCategoryID 中，資料表 Sales.Customers 的 上索引之重複索引鍵值平均數目。

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
	    FROM Sales.Customers
	    GROUP BY CustomerCategoryID) a

若要評估您自己的資料表和索引的索引鍵重複項目平均數目，請使用您的資料表名稱取代 Sales.Customers ，並使用索引鍵資料行的清單取代 CustomerCategoryID 。

比較使用每個索引類型的時機

特定查詢的本質會決定哪個索引類型是最佳選擇。

在現有的應用程式中實作記憶體最佳化資料表時，一般建議是由非叢集索引開始，因為其功能與傳統以磁碟為基礎之資料表上的叢集與非叢集索引之功能更為類似。

使用非叢集索引的建議

在下列情況中，非叢集索引會比雜湊索引更適合︰

• 查詢在索引資料行上有 ORDER BY 子句。
• 僅測試多欄索引之前導欄位的查詢。
• 查詢會使用 WHERE 子句搭配下列項目，以索引資料行作為測試條件：
  • 不等式：WHERE StatusCode != 'Done'
  • 數值範圍掃描：WHERE Quantity >= 100

在下列所有 SELECT 中，非叢集索引會比雜湊索引更適合︰

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';  

使用雜湊索引的建議

雜湊索引主要用於點查閱，而非用於範圍掃描。

當查詢使用等號比較述詞時，雜湊索引會比非叢集索引更合適，且 WHERE 子句會對應至所有索引鍵資料行，如下列範例所示：

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

多欄索引

多資料行索引可以是非叢集索引或雜湊索引。 假設索引資料行是 col1 和 col2。 假設有下列 SELECT 陳述式，則只有非叢集索引會有助於查詢最佳化工具︰

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';  

雜湊索引需要使用 WHERE 子句，為其索引鍵中的每個資料行指定等值測試。 否則雜湊索引對查詢最佳化工具沒有助益。

如果 WHERE 子句只指定索引鍵中的第二個資料行，則兩種索引類型皆無用。

比較索引使用狀況案例的摘要資料表

下表列出各種索引類型支援的所有運算。 「是」表示索引可以有效率地為要求提供服務，「否」則表示索引無法有效率地滿足要求。

¹ 針對經記憶體最佳化的非叢集索引，不需要完整的索引鍵來執行索引搜尋。

自動索引與統計資料管理

利用自適性索引子磁碟重組等解決方案，為一或多個資料庫自動管理索引重組以及統計資料更新。 這項程序會根據索引分散程度與其他參數，自動選擇要進行重建或是重新組織索引，並以線性閾值更新統計資料。

相關內容

• 索引架構和設計指南
• 記憶體最佳化資料表的雜湊索引
• 記憶體最佳化資料表的非叢集索引
• 自適性索引重組