メモリ最適化テーブルのインデックス

[アーティクル]
04/26/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

すべてのメモリ最適化テーブルには少なくとも 1 つのインデックスが必要です。このインデックスによって行が連結されるためです。メモリ最適化テーブルでは、すべてのインデックスもメモリ最適化されます。メモリ最適化テーブルは、ディスクベーステーブルの従来のインデックスとはさまざまな点で違いがあります。

データ行はページに格納されないため、ページやエクステントのコレクションも、テーブルのすべてのページを取得するために参照できるパーティションやアロケーションユニットもありません。インデックスの使用可能ないずれかの型に対するインデックスページの概念はありますが、これらの格納方法はディスクベースのテーブルのインデックスとは異なります。それらはページ内で断片化の従来の種類を計上しないため、fillfactor を持ちません。
データ操作中にメモリ最適化テーブルのインデックスに行われた変更がディスクに書き込まれることはありません。データ行と、データへの変更のみが、トランザクションログに書き込まれます。
メモリ最適化インデックスは、データベースがオンライに戻ったときに再構築されます。

メモリ最適化テーブルのすべてのインデックスは、データベースの復旧中にインデックスの定義に基づいて作成されます。

インデックスは、次のいずれかにする必要があります。

ハッシュインデックス
メモリ最適化済み非クラスター化インデックス (B ツリーの既定の内部構造を意味します)

ハッシュ インデックスについては、「メモリ最適化テーブルのハッシュインデックス」で、詳しく説明します。
非クラスター化インデックスについては、「Nonclustered Index for Memory-Optimized Tables」(メモリ最適化テーブルの非クラスター化インデックス) で、詳しく説明します。
列ストア インデックスについては、別の記事で説明します。

メモリ最適化インデックスの構文

メモリ最適化テーブル用の各 CREATE TABLE ステートメントには、INDEX によって明示的に、または PRIMAY KEY もしくは UNIQUE 制約によって暗黙的にインデックスを含める必要があります。

既定の DURABILITY = SCHEMA_AND_DATA で宣言するには、メモリ最適化テーブルが主キーを持つ必要があります。次の CREATE TABLE ステートメントの PRIMARY KEY NONCLUSTERED 句は、2 つの要件を満たします。

CREATE TABLE ステートメントの 1 つのインデックスの最小要件を満たすためにインデックスを提供します。

SCHEMA_AND_DATA 句に必要な主キーを提供します。

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

Note

SQL Server 2014 (12.x) と SQL Server 2016 (13.x) には、メモリ最適化テーブルまたはテーブルの種類ごとに 8 個のインデックスの制限があります。 SQL Server 2017 (14.x) 以降および Azure SQL データベースでは、メモリ最適化テーブルおよびテーブル型に固有のインデックス数に制限がなくなりました。

構文のコード例

ここには、メモリ最適化テーブルのさまざまなインデックスを作成する構文を示す Transact-SQL コードブロックが含まれています。コードは、次の操作を示しています。

メモリ最適化テーブルを作成します。
ALTER TABLE ステートメントを使用して、2 つのインデックスを追加します。

数行のデータを挿入します。

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 エントリを超えると、パフォーマンスの低下が無視できないほどになることがあります。

重複するハッシュ値

ハッシュインデックスの場合、重複が目立って問題になります。ハッシュインデックスの場合に問題が大きくなるのは次の理由によります。

ハッシュインデックスの場合、操作あたりのコストが低い。
大きな重複チェーンがハッシュ競合チェーンを干渉する。

インデックス内の重複を減らすためには、次の調整をお試しください。

クラスター化されていないインデックスを使用する。
インデックスキーの末尾に列を追加し、重複の数を減らす。
- たとえば、主キーにもある列を追加できます。

ハッシュ競合の詳細については、メモリ最適化テーブルのハッシュインデックスに関するページを参照してください。

改善例

インデックスで非効率なパフォーマンスを回避する方法の例を紹介します。

CustomerId に主キーが、列 CustomerCategoryIDにインデックスがある Customers テーブルを考えてみてください。一般的に、特定のカテゴリに多くの顧客がいます。そのため、インデックスの特定のキー内には、CustomerCategoryID の重複値がたくさんできます。

このシナリオでは、(CustomerCategoryID, CustomerId) で非クラスター化インデックスを使用することを推奨します。このインデックスは、CustomerCategoryID を伴う述語を使用するクエリに使用できます。それでも、インデックスキーには重複が含まれません。そのため、重複する CustomerCategoryID 値やインデックスの追加列によりインデックスの保守管理が非効率になることはありません。

次のクエリでは、 CustomerCategoryID WideWorldImporters 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 句が等値テストを指定する必要があります。そうしないと、クエリオプティマイザーにとってハッシュインデックスは役に立ちません。

インデックスキーの 2 番目の列のみ WHERE 句が指定した場合は、どちらの種類のインデックスも役に立ちません。

インデックス使用のシナリオを比較する概要表

次の表は、異なるインデックスの種類でサポートされるすべての操作を示しています。はいはインデックスが要求に十分に対応できることを意味し、いいえはインデックスが要求に十分に対応できないことを意味します。

操作	メモリ最適化、 hash	メモリ最適化、非クラスター化	ディスクベース、 (非) クラスター化
インデックススキャン、すべてのテーブルの行を取得する。	はい	イエス	はい
等値述語 (=) でのインデックスシーク。	はい (フルキーが必要です。)	はい	はい
非等値述語と範囲述語でのインデックスシーク (>、<、<=、>=、`BETWEEN`)。	なし (インデックススキャンが実行される)	はい ¹	はい
インデックス定義と一致する行を並べ替え順序で取得する。	いいえ	イエス	はい
インデックス定義の反対と一致する行を並べ替え順序で取得する。	いいえ	番号	はい

¹ メモリ最適化された非クラスター化インデックスの場合、インデックスシークの実行にフルキーは必要ありません。

インデックスと統計の自動管理

Adaptive Index Defrag のようなソリューションを活用し、1 つまたは複数のデータベースに対するインデックスの最適化と統計更新を自動管理します。このプロシージャでは、断片化レベルやその他のパラメーターに基づいてインデックスを再構築または再構成するか、線形しきい値で統計を更新するかが自動的に選択されます。

参照

SQL Server インデックスデザインガイド
 メモリ最適化テーブルのハッシュインデックス
 メモリ最適化テーブルの非クラスター化インデックス
 Adaptive Index Defrag

次の方法で共有

メモリ最適化テーブルのインデックス

メモリ最適化インデックスの構文

構文のコード例

重複するインデックスキー値

重複するハッシュ値

改善例

各種のインデックスを使用する状況の比較

非クラスター化インデックスの使用に関する推奨事項

ハッシュインデックスの使用に関する推奨事項

複数列のインデックス

インデックス使用のシナリオを比較する概要表

インデックスと統計の自動管理

参照

フィードバック

その他のリソース

次の方法で共有

メモリ最適化テーブルのインデックス

メモリ最適化インデックスの構文

構文のコード例

重複するインデックス キー値

重複するハッシュ値

改善例

各種のインデックスを使用する状況の比較

非クラスター化インデックスの使用に関する推奨事項

ハッシュ インデックスの使用に関する推奨事項

複数列のインデックス

インデックス使用のシナリオを比較する概要表

インデックスと統計の自動管理

参照

フィードバック

その他のリソース

重複するインデックスキー値

ハッシュインデックスの使用に関する推奨事項