クラスター化インデックス
- クラスター化インデックスは、テーブルまたはビュー内のデータ行をそのキー値に基づいて並べ替え、格納します。 クラスター化インデックスは、インデックス定義に含まれる列です。 データ行自体は 1 つの順序でしか並べ替えられないので、1 つのテーブルに設定できるクラスター化インデックスは 1 つだけです。
- テーブル内のデータ行が並べ替えられた順に格納されるのは、テーブルにクラスター化インデックスが含まれているときだけです。 テーブルにクラスター化インデックスが含まれている場合、そのテーブルをクラスター化テーブルと呼びます。 クラスター化インデックスが含まれないテーブルのデータ行は、ヒープと呼ばれる順序付けられていない構造に格納されます。
非クラスター化インデックス
- 非クラスター化インデックスは、データ行とは独立した構造になっています。 非クラスター化インデックスには、非クラスター化インデックスのキー値が含まれており、各キー値のエントリにはキー値が含まれているデータ行へのポインターが含まれています。
- 非クラスター化インデックス内のインデックス行からデータ行を指すポインターを、行ロケーターと呼びます。 行ロケーターの構造は、データ ページがヒープまたはクラスター化テーブルのどちらに格納されているかによって異なります。 ヒープに格納されている場合、行ロケーターは行を指すポインターです。 クラスター化テーブルに格納されている場合、行ロケーターはクラスター化インデックス キーです。 * 非キー列をリーフ レベルの非クラスター化インデックスに追加することで、サイズが 900 バイトまで、キー列が 16 個までという、既存のインデックス キーの制限を回避して、すべてを対象とするインデックスが設定されたクエリを実行できます。
- クラスター化にするか非クラスター化にするかでパフォーマンスに大きく影響する
- クラスター化インデックスはScanが速い、非クラスター化インデックスはSeekが速いイメージ
一意インデックスを作成すると、キー値を複製しようとしても失敗します。UNIQUE 制約を作成することと、制約に依存しない一意インデックスを作成することの間に大きな違いはありません。データ検証動作も同じ方式で行われます。また、クエリ オプティマイザーでは、制約によって作成された一意インデックスと手動で作成された一意インデックスは区別されません。ただし、データの整合性を維持することが目的である場合は、列に UNIQUE 制約を作成する必要があります。これにより、インデックスの目的が明確になります。
- 主キーや一意制約を設定した時にセットで自動的に作成される
列ストアインデックスは、列ストアと呼ばれる列指向データ形式を使用してデータを格納、取得、および管理するためのテクノロジです。
- Sql server の 列ストアインデックス 入門 がわかりやすい
- SQL Server2016から非クラスター化列ストアインデックスが利用できるようになり、行ストアと列ストアのハイブリットが可能になった
インデックス キー列の他に非キー列を含めることにより、非クラスター化インデックスを拡張できます。非キー列は、インデックス B-Tree のリーフ レベルに格納されます。 非キー列を含むインデックスは、クエリに対応している場合に、最大の効力を発揮します。つまり、そのインデックスに、クエリで参照されるすべての列が含まれる場合です。
- クラスター化インデックスは全ての列を含んだ付加列インデックスのようなもの
フルテキスト インデックスの情報は、特定の単語や単語の組み合わせをすばやく検索できるフルテキスト クエリをコンパイルするために Full-Text Engine で使用されます。フルテキスト インデックスには、データベース テーブルの 1 つ以上の列の重要な語およびその場所に関する情報が保存されます。フルテキスト インデックスは、Full-Text Engine for SQL Server により構築および管理されるトークンベースの特殊な機能インデックスです。フルテキスト インデックスの作成手順は、他のタイプのインデックスの作成手順とは異なります。特定の行に格納された値に基づいて B ツリー構造を作成するのではなく、Full-Text Engine は、インデックスを作成するテキストの個々のトークンに基づいて、反転、スタック、および圧縮されたインデックス構造を作成します。SQL Server 2008 では、フルテキスト インデックスのサイズを制限する要因となるのは、SQL Server のインスタンスが実行されているコンピューターで使用できるメモリ リソースのみです。
- フルテキストインデックスを作成した項目に対してのみ、CONTAINS句での検索が可能
- 日本語を検索しようとすると色々と制約がある(らしい)
-
CI:従業員番号、NCI:従業員名のテーブルに対して、「すずき」さんで検索した場合
-
CI:なし、NCI:従業員名のテーブルに対して、「すずき」さんで検索した場合
- 1テーブルに1つしか作成できないので、以下の要件を最も良く満たす列に対してインデックスを作成するべき
- 頻繁に並べ替えを行う場合
- 頻繁にグループ化をする場合
- 範囲検索など広範囲な照会を頻繁に行う場合
- ソート無しMerge Joinで結合を高速化したい場合
- データの並び順を気にしないケースではソートが発生しない分ヒープのほうが性能が良いと思われるが、
クラスター化インデックスを作成する代わりにテーブルをヒープのままにしておくとよい場合もありますが、ヒープを効果的に使用するには高度なスキルが必要です。 テーブルをヒープのままにしておく妥当な理由がない限り、ほとんどのテーブルには、慎重に選択されたクラスター化インデックスが必要です。
- とあるので、多少の性能差であればクラスター化インデックスを作成しておいたほうが良いかも
- データの絞込に良く使用される項目に作成すると良い
- ただし、同値が多い列の検索や、BETWEEN句での範囲検索は、Seekだと遅いので作成するべきではない(作成してもオプティマイザが使おうとしない)
複数の非クラスタ化インデックスが存在するヒープにクラスタ化インデックスを作成する場合、すべての非クラスタ化インデックスを再構築して、行識別子 (RID) の代わりにクラスタ化キー値が含まれるようにする必要があります。同様に、複数の非クラスタ化インデックスを持つテーブルのクラスタ化インデックスを削除すると、DROP 操作の一部として非クラスタ化インデックスがすべて再構築されます。大きなテーブルでは、この操作に相当な時間がかかる場合があります。 大きなテーブルにインデックスを構築する場合、最初にクラスタ化インデックスを構築してから、非クラスタ化インデックスを構築することをお勧めします。既存のテーブルにインデックスを作成するときは、ONLINE オプションを ON に設定することを検討します。ON に設定すると、テーブル ロックは長時間保持されません。これにより、基になるテーブルに対するクエリまたは更新を続行できます。
インデックスはCI→NCIの順番で作成すると覚えておけばOK(削除するときは逆にNCI→CIの順番)
- インデックスを作成すると、自動的にキー項目の統計情報が作成される
- インデックスを再構築した場合、統計情報も自動的に更新される
- この時統計情報のサンプル率が100%で作成されるので、そのあとにsp_updatestatsなどで統計情報を更新してしまうと、サンプル率が下がってしまうことがあるので注意
主キーや一意制約を作成する時に自動作成されるケースも含めると、基本的なインデックスを作成する方法は以下の８パターンになる。
| # | インデックス作成方法 | 列にNOT NULL制約が必要 | 重複値を許容しない | 1テーブルに1つのみ |
|---|---|---|---|---|
| 1 | Clustered Primary Key | ○ | ○ | ○ |
| 2 | Non-Clustered Primary Key | ○ | ○ | ○ |
| 3 | Clustered Unique Constraint | - | ○ | ○ |
| 4 | Non-Clustered Unique Constraint | - | ○ | - |
| 5 | Clustered Unique Index | - | ○ | ○ |
| 6 | Non-Clustered Unique Index | - | ○ | - |
| 7 | Clustered Index | - | - | ○ |
| 8 | Non-Clustered Index | - | - | - |
CREATE TABLE [SAMPLE_TABLE] (
COL1 VARCHAR(1) NOT NULL,
COL2 VARCHAR(1)
)ALTER TABLE [SAMPLE_TABLE] ADD CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED (COL1)
- 主キーを設定することが目的
- 一意インデックスが自動作成される(インデックス名の括弧内に「一意」と書かれていないので分かりにくいが一意インデックスが作成されている)
- CREATE TABLE時に主キーを設定する方法もあるが結果は同じなので省略
ALTER TABLE [SAMPLE_TABLE] ADD CONSTRAINT [PK_NC] PRIMARY KEY NONCLUSTERED (COL1)
- CIを作りたくない場合や、主キーとCIを別カラムにしたい場合にこれを選択する
ALTER TABLE [SAMPLE_TABLE] ADD CONSTRAINT [UNIQUE_C] UNIQUE CLUSTERED (COL2)
- 一意制約を設定することが目的
- 主キーとは違いNullを許容するが、Nullを複数件インサートしようとすると一意制約エラーとなる
- しかしNullを1件だけ許容するというケースはほとんどないと思うので、結局NOT NULL制約との組み合わせ(機能的には主キーと同じ)か、フィルター選択されたインデックスでNull以外の値で一意性を担保したい場合に選択すると思われる。
- CIは1テーブルに1つしか作成できないので、1.Clustered Primary Keyとは共存できないが、2.Non-Clustered Primary Keyとは共存できる。
ALTER TABLE [SAMPLE_TABLE] ADD CONSTRAINT [UNIQUE_NC1] UNIQUE NONCLUSTERED (COL1)
ALTER TABLE [SAMPLE_TABLE] ADD CONSTRAINT [UNIQUE_NC2] UNIQUE NONCLUSTERED (COL2)
- CIにはしたくなく、主キーでもなく、一意制約を設定したい場合に選択
CREATE UNIQUE CLUSTERED INDEX [IDX_UNIQUE_C] ON [SAMPLE_TABLE] (COL2)
- 基本的に一意インデックスは主キーや一意制約をした際に副次的に作成されるものであり、制約に紐付かない一意インデックスを単独で作成することもできるが、機能的には一意制約と変わらないので、目的を考えると主キーもしくは一意制約を設定するほうが望ましい
- したがって、あえてこれを選択するケースは無いと思われる
CREATE UNIQUE NONCLUSTERED INDEX [IDX_UNIQUE_NC1] ON [SAMPLE_TABLE] (COL1)
CREATE UNIQUE NONCLUSTERED INDEX [IDX_UNIQUE_NC2] ON [SAMPLE_TABLE] (COL2)
- 5.Clustered Unique Indexと同じく、あえてこれを選択するケースは無いと思われる
CREATE CLUSTERED INDEX [IDX_C] ON [SAMPLE_TABLE] (COL1)
- 性能向上が目的
- 主キーや一意制約のカラムはNCIにして、その他のカラムに対してCIを作成したい場合にこの方法をとる
- CIなので1テーブルに1つだけ作成可能(CIはデータを格納する並び順を指定するものであるため)
CREATE NONCLUSTERED INDEX [IDX_NC1] ON [SAMPLE_TABLE] (COL1)
CREATE NONCLUSTERED INDEX [IDX_NC2] ON [SAMPLE_TABLE] (COL2)
- 性能向上が目的
- 主キーや一意制約の列以外に、頻繁にデータの絞込に使用される項目がある場合に、この方法でインデックスを作成する
- 1テーブルに複数作成可能
今度、次の内容をマージしてみる。
https://docs.microsoft.com/ja-jp/troubleshoot/sql/database-design/filtered-index-with-column-is-null