次の方法で共有

付加列インデックス

  • [アーティクル]

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

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

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

ms190806.note(ja-jp,SQL.90).gifメモ :
クエリによって参照されるすべての列がインデックスに含まれているときは、一般的に、そのインデックスはクエリをカバーしていると呼ばれます。

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

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

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

たとえば、AdventureWorks サンプル データベースの 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 AdventureWorks;
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 の各列の長さを拡張する。
      ms190806.note(ja-jp,SQL.90).gifメモ :
      これらの列の変更の制限は、インデックス キー列にも適用されます。

設計上の推奨事項

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

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

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

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

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

USE AdventureWorks;
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 のデータ型を非キー インデックス列として追加すると、必要なディスク領域が大幅に増加します。これは、列の値がインデックスのリーフ レベルにコピーされるためです。そのため、列の値がインデックスとベース テーブルの両方に存在します。
  • インデックスのメンテナンスによって、基になるテーブルやインデックス付きビューに対する変更、挿入、更新、削除にかかる時間が長くなる場合があります。

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

参照

概念

インデックスの作成 (データベース エンジン)
付加列インデックスの作成
インデックスの設計の全般的なガイドライン
インデックスのデザインの基礎
インデックス キーの最大サイズ
インデックス情報の表示

ヘルプおよび情報

SQL Server 2005 の参考資料の入手