インデックスの作成 (データベース エンジン)
このトピックでは、インデックス作成の主な作業について説明します。また、インデックスを作成する前に考慮する、実装とパフォーマンスのガイドラインについて説明します。
インデックス作成作業
インデックスを作成するには、次の作業を行うことをお勧めします。
- インデックスの設計。
インデックスの設計は、重要な作業です。インデックスの設計には、使用する列の決定、インデックスの種類の選択 (クラスタ化や非クラスタ化など)、適切なインデックス オプションの選択、ファイル グループやパーティション構成の配置の決定などがあります。詳細については、「インデックスの設計」を参照してください。 - 最適な作成方法を決定します。インデックスは次の方法で作成されます。
- CREATE TABLE や ALTER TABLE を使用して、列に PRIMARY KEY 制約または UNIQUE 制約を定義することで作成する方法。
SQL Server 2005 データベース エンジン では、PRIMARY KEY 制約や UNIQUE 制約の一意性要件を設定する一意インデックスが自動的に作成されます。既定では、テーブルにクラスタ化インデックスが既に存在する場合を除いて、PRIMARY KEY 制約を設定するために一意クラスタ化インデックスが作成されます。それ以外の場合は、一意非クラスタ化インデックスを指定します。既定では、一意クラスタ化インデックスが明示的に指定されている場合、またはテーブルにクラスタ化インデックスが存在しない場合を除き、UNIQUE 制約を設定するために一意非クラスタ化インデックスが作成されます。
インデックス オプションとインデックスの位置、ファイル グループまたはパーティション構成も指定できます。
PRIMARY KEY 制約または UNIQUE 制約の一部として作成されたインデックスには、制約名と同じ名前が自動的に付けられます。詳細については、「PRIMARY KEY 制約」および「UNIQUE 制約」を参照してください。 - CREATE INDEX ステートメント、または SQL Server Management Studio オブジェクト エクスプローラの [新しいインデックス] ダイアログ ボックスを使用して、制約とは無関係にインデックスを作成する方法。
インデックス、テーブル、およびインデックスが適用される列の名前を指定する必要があります。インデックス オプションとインデックスの位置、ファイル グループまたはパーティション構成も指定できます。既定では、クラスタ化オプションまたは一意オプションが指定されていない場合は、一意ではない非クラスタ化インデックスが作成されます。
- CREATE TABLE や ALTER TABLE を使用して、列に PRIMARY KEY 制約または UNIQUE 制約を定義することで作成する方法。
- インデックスを作成する方法。
空のテーブルにインデックスを作成するか、データが含まれるテーブルにインデックスを作成するかを考慮することは重要です。空のテーブルにインデックスを作成する場合、インデックスの作成時点ではパフォーマンスに影響しませんが、データをそのテーブルに追加するときにパフォーマンスに影響することになります。
大きなテーブルにインデックスを作成する場合、データベースのパフォーマンスが低下しないように注意深く計画する必要があります。大きなテーブルにインデックスを作成する場合は、最初にクラスタ化インデックスを作成してから、非クラスタ化インデックスを作成することをお勧めします。既存のテーブルにインデックスを作成するときは、ONLINE オプションを ON に設定することを検討します。このオプションを ON に設定すると、テーブル ロックが長時間保持されず、基になるテーブルへのクエリまたは更新の続行が可能です。詳細については、「オンラインでのインデックス操作の実行」を参照してください。
実装に関する注意点
次の表に、クラスタ化インデックス、非クラスタ化インデックス、および XML インデックスに適用される最大値の一覧を示します。別途指定しない限り、すべての種類のインデックスに次の制限が適用されます。
| インデックスの最大制限 | 値 | 関連情報 |
|---|---|---|
テーブルごとのクラスタ化インデックス数 |
1 |
|
テーブルごとの非クラスタ化インデックス数 |
249 |
この数には、PRIMARY KEY 制約または UNIQUE 制約によって作成された非クラスタ化インデックスも含まれますが、XML インデックスは含まれません。 |
テーブルごとの XML インデックス数 |
249 |
xml データ型の列のプライマリ XML インデックスとセカンダリ XML インデックスが含まれます。 |
インデックスごとのキー列の数 |
16* |
テーブルにプライマリ XML インデックスも含まれている場合、クラスタ化インデックスは 15 列に制限されます。 |
インデックス キーの最大レコード サイズ |
900 バイト* |
XML インデックスには関係しません。 |
* インデックスに非キー列を含めることにより、非クラスタ化インデックスのインデックス キー列とレコード サイズの制限を回避できます。詳細については、「付加列インデックス」を参照してください。
データ型
通常、テーブルやビューの任意の列にインデックスを設定できます。次の表に、インデックスへの関与が制限されるデータ型の一覧を示します。
| データ型 | インデックスへの関与 | 関連情報 |
|---|---|---|
CLR ユーザー定義型 |
その型でバイナリ順がサポートされている場合は、インデックスを設定できます。 |
|
ラージ オブジェクト (LOB) データ型。image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml。 |
インデックス キー列にはできません。ただし、xml 列を、テーブルのプライマリ XML インデックスまたはセカンダリ XML インデックスのキー列にすることはできます。 image、ntext、および text を除く非クラスタ化インデックスに、非キー列 (付加列) として関与できます。 計算列式の一部の場合は、インデックスに関与できます。 |
|
計算列 |
インデックスを設定できます。メソッドが決定的であるとマークされている限り、これには CLR ユーザー定義型列のメソッド呼び出しとして定義される計算列が含まれます。 計算列のデータ型をインデックス キー列または非キー列として使用できる限り、LOB データ型から派生した計算列は、キー列または非キー列としてインデックスを設定できます。 |
|
行外にプッシュされた Varchar |
クラスタ化インデックスのインデックス キーには、ROW_OVERFLOW_DATA アロケーション ユニットに既存のデータが含まれる varchar 型の列を含めることはできません。クラスタ化インデックスが varchar 型の列に作成され、既存のデータが IN_ROW_DATA アロケーション ユニットに含まれる場合に、それ以後にその列でデータを行外にプッシュする挿入処理や更新処理を行うと失敗します。 |
その他の注意点
次に、インデックスの作成でのその他の注意事項を示します。
- テーブルに CONTROL 権限または ALTER 権限があれば、インデックスを作成できます。
- インデックスは作成されると、自動的に有効になり、使用できるようになります。インデックスを無効化することによって、インデックスへのアクセスを削除できます。詳細については、「インデックスの無効化」を参照してください。
必要なディスク領域
インデックスの格納に必要なディスク領域は、次の要因によって異なります。
- テーブル内の各データ行のサイズとページあたりの行数。この値により、インデックスを作成するためにディスクから読み取る必要があるデータ ページ数が決まります。
- インデックスの列数と使用するデータ型。この値により、ディスクに書き込む必要があるインデックス ページ数が決まります。詳細については、「クラスタ化インデックスのサイズの見積もり」および「非クラスタ化インデックスのサイズの算出」を参照してください。
- インデックス作成の処理中には、一時的なディスク領域が必要になります。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。
パフォーマンスに関する注意点
インデックスの物理的な作成にかかる時間は、ディスク サブシステムに大きく依存します。特に次の点に注意してください。
- データベースの復旧モデル。一括ログ復旧モデルはパフォーマンスに優れ、インデックスの作成操作中に使用するログ領域が完全復旧よりも少なくなります。ただし、一括ログ復旧モデルでは特定の時点に復旧できないので、この点に関しては柔軟性がありません。詳細については、「インデックス操作の復旧モデルの選択」を参照してください。
- データベース ファイルとトランザクション ログ ファイルの格納に使用される RAID (Redundant Array of Independent Disks) レベル。通常、ストライピングを使用する RAID レベルは、I/O 帯域幅が優れています。
- RAID を使用する場合、ディスク アレイ内のディスク数。アレイに含まれるドライブが多いほど、それに比例してデータ転送レートが増加します。
- データの中間の並べ替え実行結果が格納される場所。tempdb がユーザー データベースとは異なるディスク セットにある場合、SORT_IN_TEMPDB オプションを使用すると、インデックスの作成に必要な時間を削減できます。詳細については、「tempdb とインデックスの作成」を参照してください。
- オフラインまたはオンラインでのインデックスの作成
インデックスがオフラインで作成されると (既定)、インデックスを作成するトランザクションが完了するまで、基になるテーブルで排他ロックが保持されます。そのため、インデックスの作成中は、ユーザーがテーブルにアクセスできません。
SQL Server 2005 では、オンラインでのインデックスの作成を指定できます。オンライン オプションが ON に設定されていると、インデックス作成中に引き続き基になるテーブルへのクエリや更新が実行できるように、テーブル ロックは長時間保持されません。オンラインでのインデックス操作をお勧めしますが、そのためには環境や特定の要件を評価する必要があります。オフラインでインデックス操作を実行する方が適している場合もあります。オフラインでインデックスを作成すると、インデックス操作中はデータへのアクセスが制限されますが、操作は高速に終了し、使用するリソースも少なくなります。詳細については、「オンラインでのインデックス操作の実行」を参照してください。
テーブルの作成時に PRIMARY KEY 制約または UNIQUE 制約を作成するには
既存のテーブルに PRIMARY KEY 制約または UNIQUE 制約を作成するには
インデックスを作成するには
参照
概念
クラスタ化インデックスの作成
付加列インデックスの作成
非クラスタ化インデックスの作成
一意インデックスの作成
パーティション テーブルとパーティション インデックスの実装
PRIMARY KEY 制約
UNIQUE 制約
xml データ型列のインデックス
その他の技術情報
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
フルテキスト検索の概要