[アーティクル]
10/31/2012

付加列インデックス

非クラスター化インデックスのリーフレベルに非キー列を追加することにより、非クラスター化インデックスの機能を拡張できます。非キー列を含めることにより、より多くのクエリをカバーする非クラスター化インデックスを作成できます。これは、非キー列には次の利点があるためです。

非キー列には、インデックスキー列として許可されていないデータ型を設定できる。
インデックスキー列の数やインデックスキーのサイズを計算するときに、データベースエンジンでは非キー列が考慮されない。

クエリ内のすべての列が、キー列または非キー列のいずれかとしてインデックスに含まれるているとき、非キー付加列を含むインデックスにより、クエリパフォーマンスが大幅に向上します。クエリオプティマイザーではインデックス内のすべての列値を参照できるので、テーブルやクラスター化インデックスのデータにアクセスすることがなく、ディスク I/O 操作が少なくて済むため、パフォーマンスが向上します。

注
クエリによって参照されるすべての列がインデックスに含まれているときは、一般的に、そのインデックスはクエリをカバーしていると呼ばれます。

キー列がインデックスのすべてのレベルに格納されている場合は、非キー列はリーフレベルだけに格納されます。インデックスレベルの詳細については、「テーブルとインデックスの編成」を参照してください。

サイズ制限を回避するための付加列の使用

非クラスター化インデックスに非キー列を含めることで、現在のインデックスサイズの制限 (最大 16 個のキー列と最大 900 バイトのインデックスキーのサイズ) を超えないようにすることができます。インデックスキー列の数やインデックスキーのサイズを計算するときに、データベースエンジンでは非キー列が考慮されません。

たとえば、AdventureWorks2008R2 サンプルデータベースの Document テーブルにある次の列にインデックスを設定するとします。

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

nchar データ型および nvarchar データ型は各文字に 2 バイトが必要なので、これら 3 つの列が含まれるインデックスは 900 バイトのサイズ制限を 10 バイト超えてしまいます (455 * 2)。CREATE INDEX ステートメントの INCLUDE 句を使用することにより、インデックスキーを (Title, Revision) として定義し、FileName を非キー列として定義できます。その結果、インデックスキーのサイズが 110 バイト (55 * 2) になりましたが、インデックスには必要な列がすべて含まれています。このようなインデックスは、次のステートメントで作成されます。

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Document_Title 
ON Production.Document (Title, Revision) 
INCLUDE (FileName);

付加列インデックスのガイドライン

付加列非クラスター化インデックスを設計するときは、次のガイドラインについて考慮してください。

非キー列は、CREATE INDEX ステートメントの INCLUDE 句で定義されます。
非キー列は、テーブルやインデックス付きビューの非クラスター化インデックスにのみ定義できます。
text、ntext、および image を除く、すべてのデータ型を使用できます。
決定的な計算列、および正確または不正確な計算列を、付加列にできます。詳細については、「計算列に対するインデックスの作成」を参照してください。
キー列と同様に、計算列が image、ntext、および text の各データ型から派生している場合は、計算列のデータ型が非キーインデックス列として許可されている限り、非キー (付加) 列にできます。
INCLUDE リストとキー列リストの両方に、列名を指定することはできません。
INCLUDE リスト内で列名を繰り返すことはできません。

列サイズのガイドライン

キー列は少なくとも 1 つ定義する必要があります。非キー列の最大数は 1,023 列です。これは、テーブルの最大列数から 1 を引いた数です。
非キーを除くインデックスキー列は、既存のインデックスサイズの制限 (最大 16 個のキー列、インデックスキーの合計サイズ 900 バイト) に従う必要があります。
すべての非キー列の合計サイズは、INCLUDE 句で指定された列のサイズによってのみ制限されます。たとえば、varchar(max) 列は 2 GB に制限されます。

列の変更のガイドライン

付加列として定義されたテーブル列を変更するときには、次の制限が適用されます。

インデックスを先に削除しない限り、非キー列をテーブルから削除できません。
次の操作以外に、非キー列は変更できません。
- 列の NULL 値の許容を NOT NULL から NULL に変更する。
- varchar、nvarchar、または varbinary の各列の長さを拡張する。
  
  注
  
  これらの列の変更の制限は、インデックスキー列にも適用されます。

注
これらの列の変更の制限は、インデックスキー列にも適用されます。

設計上の推奨事項

検索や参照に使用される列のみがキー列になるように、大きなサイズのインデックスキーを使用して、非クラスター化インデックスを設計し直します。クエリをカバーする他のすべての列を、非キー付加列にします。その結果、クエリをカバーするために必要なすべての列を含むことができますが、インデックスキー自体は小さく、効率的です。

たとえば、次のクエリをカバーするインデックスを設計するとします。

USE AdventureWorks2008R2;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

クエリをカバーするには、インデックスに各列を定義する必要があります。すべての列をキー列として定義でき、その場合キーのサイズは 334 バイトになります。実際に検索条件に使用されている唯一の列は、30 バイトの長さの PostalCode 列なので、より効果的な設計のインデックスにするには、キー列として PostalCode を定義し、他のすべての列を非キー列として含めます。

次のステートメントにより、クエリをカバーする付加列インデックスが作成されます。

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

パフォーマンスに関する注意点

不要な列は追加しないでください。キーまたは非キーのインデックス列を追加しすぎると、次のようなパフォーマンス上の問題が発生することがあります。

1 ページに収まるインデックス行が少なくなります。これにより、ディスク I/O が増加しキャッシュ効率が低下します。
インデックスを格納するために、さらに多くのディスク領域が必要になります。特に、varchar(max)、nvarchar(max)、varbinary(max)、または xml のデータ型を非キーインデックス列として追加すると、必要なディスク領域が大幅に増加します。これは、列の値がインデックスのリーフレベルにコピーされるためです。そのため、列の値がインデックスとベーステーブルの両方に存在します。
インデックスのメンテナンスによって、基になるテーブルやインデックス付きビューに対する変更、挿入、更新、削除にかかる時間が長くなる場合があります。

データ変更によるパフォーマンスへの影響や追加ディスク領域の要件よりも、クエリのパフォーマンスから得られる利点の方が大きいかどうかを判断する必要があります。クエリのパフォーマンスの評価に関する詳細については、「クエリのチューニング」を参照してください。

次の方法で共有