インデックスの設計の全般的なガイドライン
経験豊富なデータベース管理者であれば適切なインデックス セットをデザインできますが、それほど複雑でないデータベースとワークロードであっても、この作業はきわめて複雑で、時間がかかり、間違いを犯しやすいものです。使用するデータベース、クエリ、データ列の特性を理解することが、最適なインデックスをデザインする際に役に立ちます。
データベースに関する注意点
インデックスをデザインするときは、次のデータベースのガイドラインを考慮してください。
- 1 つのテーブルに多数のインデックスがあると、テーブル内のデータが変更された場合にインデックスをすべて調整する必要があるので、INSERT、UPDATE、および DELETE の各ステートメントのパフォーマンスに影響します。
- 頻繁に更新するテーブルにはインデックスをデザインしすぎないようにし、インデックスの幅を狭く、つまり列数を可能な限り少なくします。
- 更新の必要が少なく、容量の大きいテーブルの場合、クエリのパフォーマンスを向上させるにはインデックスを多数使用します。SELECT ステートメントなど、データを変更しないクエリの場合は、多数のインデックスを使用することで、クエリ オプティマイザが最速のアクセス方法を決定する際に選択できるインデックスが多くなるため、クエリのパフォーマンスを向上できる可能性があります。
- 小さなテーブルではインデックスを作成しない方がよい場合もあります。これは、クエリ オプティマイザが単純なテーブル スキャンを実行するよりデータのインデックスを検索する方に時間がかかることがあるためです。そのため、小さなテーブルのインデックスがまったく使用されない可能性があっても、テーブルのデータの変更に合わせてメンテナンスする必要があります。
- ビューが集計、テーブル結合、または集計と結合の組み合わせを使用している場合、ビューにインデックスを設定すると、パフォーマンスが大幅に向上します。クエリで明示的に参照しなくても、クエリ オプティマイザはそのビューを使用します。詳細については、「インデックス付きビューのデザイン」を参照してください。
- データベース エンジン チューニング アドバイザを使用してデータベースを分析し、推奨インデックスを作成します。詳細については、「データベース エンジン チューニング アドバイザの概要」を参照してください。
クエリに関する注意点
インデックスをデザインするときは、次のクエリのガイドラインを考慮してください。
- クエリの述語や結合条件で頻繁に使用されるすべての列に対して非クラスタ化インデックスを作成します。
.gif "ms191195.note(ja-jp,SQL.90).gif")
重要 :不要な列は追加しないでください。インデックス列を追加しすぎると、必要なディスク領域が増え、インデックスのメンテナンスのパフォーマンスも低下する可能性があります。 - クエリの対象にインデックスを含めると、クエリのパフォーマンスを向上できます。これは、クエリの要件を満たすために必要なデータがすべて、インデックス自体に保持されているためです。つまり、要求されたデータの取得に必要なのはインデックス ページだけで、テーブルやクラスタ化インデックスのデータ ページは必要ありません。このため、全体的にディスク I/O を削減できます。たとえば、テーブルの列 a と b に対するクエリは、このテーブルに列 a、b、c に基づく複合インデックスが作成されていれば、インデックスのみから指定したデータを取得できます。
- 複数のクエリを使用して同じ行を更新するよりも、1 つのステートメントでできるだけ多くの行を挿入または変更するクエリを作成します。ステートメントを 1 つだけ使用することで、インデックスのメンテナンスを最適化できます。
- クエリの種類とクエリ内での列の使用方法を評価します。たとえば、完全一致検索クエリで使用される列は、非クラスタ化インデックスまたはクラスタ化インデックスにする適切な候補になります。詳細については、「クエリの種類とインデックス」を参照してください。
列に関する注意点
インデックスをデザインするときは、次の列のガイドラインを考慮してください。
- クラスタ化インデックスのインデックス キー長は長くならないようにします。また、クラスタ化インデックスは一意列や非 NULL 列に作成すると効率的です。詳細については、「クラスタ化インデックスの設計ガイドライン」を参照してください。
- ntext、text、image, varchar(max)、nvarchar(max)、および varbinary(max) データ型の列をインデックス キー列に指定することはできません。ただしvarchar(max)、nvarchar(max)、varbinary(max)、および xml データ型は、インデックスの非キー列として非クラスタ化インデックスに含めることができます。詳細については、「付加列インデックス」を参照してください。
- xml データ型は、XML インデックスでのみキー列にできます。詳細については、「xml データ型列のインデックス」を参照してください。
- 列の一意性を調べます。同じ列の組み合わせに対して一意でないインデックスを作成するよりも一意インデックスを作成する方が、クエリ オプティマイザに追加情報が提供され、インデックスの利用価値が高まります。詳細については、「一意インデックスのデザイン ガイドライン」を参照してください。
- 列内のデータの分布を調べます。インデックスを設定した列にほとんど一意の値がない場合や、このような列を結合する場合、クエリに時間がかかることがよくあります。これは、データとクエリにかかわる根本的な問題で、通常はこのような状況を特定しなければ解決できません。たとえば、姓がアルファベット順に並んだ電話帳では、対象地域のすべての人が Smith や Jones という姓である場合、特定の人を探すときに役に立ちません。データ分布の詳細については、「インデックス統計」を参照してください。
- インデックスに複数の列が含まれる場合は、列の順序を考慮します。等しい (=)、より大きい (>)、より小さい (<)、BETWEEN などの検索条件の WHERE 句で使用されるか、結合に含まれる列は、先頭に配置します。その他の列は、差異の程度、つまり最も差異の大きいものから最も差異の小さいものの順に配置します。
たとえば、インデックスがLastName,FirstNameとして定義されている場合、このインデックスは、検索条件がWHERE LastName = 'Smith'またはWHERE LastName = Smith AND FirstName LIKE 'J%'である場合に効果があります。ただし、クエリ オプティマイザでは、FirstName (WHERE FirstName = 'Jane')のみで検索するクエリには、このインデックスが使用されません。 - 計算列のインデックス設定を検討します。詳細については、「計算列に対するインデックスの作成」を参照してください。
インデックスの特性
クエリにインデックスを設定することが適切であると判断した場合は、状況に応じて最適な種類のインデックスを選択します。インデックスの特性は、次のとおりです。
- クラスタ化と非クラスタ化
- 一意と非一意
- 単一列と複数列
- 昇順と降順 (インデックス内の列の並び)
インデックスを最初に保存したときの特性をカスタマイズし、FILLFACTOR などのオプションを設定してパフォーマンスやメンテナンスを最適化できます。詳細については、「インデックス オプションの設定」を参照してください。また、パフォーマンスを最適化するために、ファイル グループやパーティション構成を使用してインデックスの保存場所を決定することもできます。詳細については、「ファイル グループへのインデックスの格納」を参照してください。