SQL Server のインデックスのバックアップ(No.6)

SQL Serverでは検索で多用される（と想定される）主キーには、
デフォルトで「クラスタ化インデックス」が付与される。
- しかし、この方法が必ずしも適切であるということにはならない。
  例えば、主キー以外のキーを使用した範囲スキャン検索の性能の向上が優先されるようなテーブルでは、
  主キーに「非クラスタ化インデックス」を付与し、「範囲スキャン検索」処理用のキーに「クラスタ化インデックス」を使用した方が、全体最適化に繋がることがある。
- インサイド Microsoft SQL Server 2005 クエリチューニング＆最適化編
  第４章：クエリパフォーマンスのトラブルシューティング
  テーブルを主キー制約で宣言すると、規定でクラスタ化インデックスが主キー列に作成されますが、この方法が常に最適であるとは限りません。
  その名が示すとおり、主キーは一意であり、条件を満たす単一行を検索する場合は、非クラスタ化インデックスが非常に効率的です。
  『主キーの一意性は、非クラスタ化インデックスでも適用できるため、クラスタ化インデックスは、主キー制約を宣言するときに
  NONCLUSTEREDのキーワードを追加して、クラスタ化インデックスが有効なものに対して確保しておきます。』

↑

適合しないケース †

また、以下のキーには適していないと言われている

頻繁に変更される列
物理的な並び替えが必要になるため。

広範なキー（複数の列・複数のサイズの大きな列を組み合わせたキー）
- 「クラスタ化インデックス」を持つテーブルに追加した「非クラスタ化インデックス」のリーフページには、
  行識別子ではなく、「クラスタ化インデックス」のキー参照が格納される（後述）。
- このため、「クラスタ化インデックス」のキーのサイズが大きくなると、
  「非クラスタ化インデックス」のサイズが大きくなるため。

↑

その他 †

「クラスタ化インデックス」作成時には、
実際のデータ（ヒープ）を並べ替えた結果を格納しておくための作業領域として、
テーブルサイズの約 1.5 倍の空き領域が必要になるため注意が必要である。

↑

参考 †

クラスタ化インデックスの設計ガイドライン
http://msdn.microsoft.com/ja-jp/library/ms190639.aspx

↑

「非クラスタ化インデックス」 †

次に、「非クラスタ化インデックス」の構造について説明する。

「非クラスタ化インデックス」は、一般的かつ汎用的なインデックスであり、「リーフレベルページ」には、行識別子が格納される。
「非クラスタ化インデックス」では、「リーフレベルページ」からヒープ（のページ）上の行情報を引くための、RID LookUp?と言う処理が必要となる。

このため、「リーフレベルページ」 → ヒープ（のページ）へのジャンプ
（これをRID LookUp?と言い、場合によってはディスクヘッドの移動を要する）
が必要になるため、キーを使用した範囲スキャン検索で、データを収集するクエリの性能は、件数が多くなるほど向上しない。
また、「選択度の低い情報」（後述）も同様に、範囲スキャン検索性能が向上しないため効果が出ない。

また、「非クラスタ化インデックス」は、
- 「クラスタ化インデックス」が存在しない場合
- 「クラスタ化インデックス」が存在する場合

で構造が異なる。

非クラスター化インデックスのデザインガイドライン
http://msdn.microsoft.com/ja-jp/library/ms187019.aspx

↑

「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」 †

「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」の「リーフレベルページ」は「インデックスページ」である。

「データページ」は「クラスタ化インデックス」を作成した場合の「データページ」とは構造が異なり、「リンクリスト」はもたない。
- このような「非クラスタ化インデックス」の「データページ」の集まりを「ヒープ」と呼ぶ。
- 「ヒープ」では、データの行の順番は特定の順序では格納されず、「データページ」にも特定の順序はない。

「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」での「リーフレベル（インデックスページ）」では
ポインタとして行識別子（ファイルID、ページID、行ID）を格納しており、その行識別子を使って「ヒープ」へジャンプし、検索対象データを探し出す。

↑

「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 †

「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフレベルページ」は同様に
「インデックスページ」であるが、「ポインタ」として「行識別子」ではなく「クラスタ化キー」の値を格納している。

このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、
- 最初に「非クラスタ化インデックス」を使用して検索し、
- 「リーフレベルページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。
- 「非クラスタ化インデックス」のキーを使用して「クラスタ化インデックス」のキーのみ取得する場合は、非常に高速。

↑

「カバリングインデックス」 †

「カバリングインデックス」は、以下により性能の向上が期待できる。
- 最初に指定された列をキーにして、木構造を構築し、
- 以降に指定された列（カバリンク列）をルート・中間・リーフページに含める。
- これにより、カバリンク列に対してはRID LookUp?をせずに処理が可能となる。

例えば、下記DDLで、「カバリングインデックス」が作成できる。
```
CREATE INDEX index_name
  ON table_name(column1, column2, column3)
```

この場合、
- 「column1」をキーにして、木構造が構築され、
- カバリンク列として「column2、column3」が
  ルート・中間・リーフページに含められる。

↑

「付加列インデックス」 †

例えば、下記DDLで、「付加列インデックス」が作成できる。
```
CREATE INDEX index_name
  ON table_name (column1)
   INCLUDE(column2, column3)
```

この場合、
- 「column1」をキーにして、木構造が構築され、
- 付加列として、「column2、column3」がリーフページにのみ含められる。

付加列インデックス
http://msdn.microsoft.com/ja-jp/library/ms190806.aspx

↑

「パーティションインデックス」 †

↑

「インデックス付きビュー」 †

GROUP BY句を使用した集計処理で指定されるキーの
選択度が高い（若しくは一意の）場合は、性能向上は期待できない。

また、「インデックス付きビュー」の基テーブルの更新がされると、
- ビューに格納されている結果セットの更新が必要となるため、
  更新処理が頻繁なビューに対して「インデックス付きビュー」を作成すると
  余計にコストがかかる場合があるので注意する。
- なお、条件を満たしていれば「インデックス付きビュー」の更新も可能であり、
  「インデックス付きビュー」の更新が行われた場合、基テーブルも更新される。

考慮点
- 「インデックス付きビュー」は、FROM句で「インデックス付きビュー」を
  直接指定していないクエリからも、オプティマイザにより、使用されることがある。
  - SQL Server 2005インデックス付きビューによるパフォーマンスの向上
    http://technet.microsoft.com/ja-jp/library/cc917715.aspx

「インデックス付きビュー」を「パーティションテーブル」とすると、
さらにクエリ速度、効率を高められる可能性がある。
- インデックス付きビューが定義されている場合のパーティション切り替え
  http://msdn.microsoft.com/ja-jp/library/bb964715.aspx

↑

インデックスと選択度 †

一般的にインデックスは、
- 選択度が高い項目を検索条件に使用する場合に有用である。
- これとは逆に、選択度の低い項目では不利になることが多い。

選択度
- 選択度が高い＝重複が少ない
  （主キー、ユニークキーなど）
- 選択度が低い＝重複が多い。
  （例えば、"男性"、"女性"というデータのみ格納する）

↑

「非クラスタ化インデックス」と選択度 †

「非クラスタ化インデックス」は、選択度の低い項目に対しては不利である。

例えば、"男性"、"女性"というデータのみ格納する項目に対して、
「非クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に
「非クラスタ化インデックス」を使用して「インデックススキャン」した場合を考える。

この場合、「非クラスタ化インデックス」では、
「リーフレベルページ」の「インデックスページ」から「データページ」にアクセスするため
「データページ」に対して、最大で1000回ものI/Oが発生する可能性がある。

↑

「クラスタ化インデックス」と選択度 †

「クラスタ化インデックス」は、選択度の低い項目に対して"も"有効である。

例えば、"男性"、"女性"というデータのみ格納する項目に対して、
「クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に
「クラスタ化インデックス」を使用して「インデックススキャン」した場合を考える。

「クラスタ化インデックス」を作成したテーブルでは、
「クラスタ化キー」の値（この場合、"男性"、"女性"）毎にデータがまとまっているため、
- "男性"社員情報を読み込むページ数は最小化され、I/O回数も最小化される。
- また、「非クラスタ化インデックス」と異なり、
  「リーフレベルページ」の「データページ」を直接スキャンすることができる。

例えば、「データページ」に10レコードが格納できる場合、
- 1000名の "男性" 社員のレコードは100ページに格納され、
- これが１つのエクステントに規則正しく格納されていれば、
- 最小で13回のI/Oで読み取りが完了する。

↑

計算式 †

1000（レコード） / 10（レコード / ページ） / 8（ページ / エクステント） ≒ 13エクステント ≒ 13回のI/O

※ SQL Server は、ディスクI/Oを、ディスク上管理単位である「エクステント」単位で処理する。

↑

選択度とインデックスの「ページ分割」 †

なお、選択度の低いデータでは、どちらのインデックスでも、
データの挿入時に、「ページ分割」が発生しやすくなり、不利である。

「ページ分割」については、
「「インデックスの断片化」の管理」で説明する。

選択度の低い項目をキーにした「クラスタ化インデックス」の作成は、

検索（「範囲検索」・「順次アクセス」）の効率
データ更新時の「ページ分割」のオーバーヘッド

のトレードオフを考慮する形になる。

↑

「インデックスの断片化」の管理 †

↑

「インデックスの断片化」とは †

DBの「データファイル」は、

論理的な「セグメント」、
物理的な「エクステント」

から構成される。

「セグメント」とは、テーブル、インデックスといった、オブジェクトを意味する。

SQL Server は、

ディスクI/Oを、ディスク上管理単位である64KBの「エクステント」単位で処理する。
また、「エクステント」は、メモリ上の管理単位である8KBの「ページ」から構成される。

データの追加、更新処理などで、
- 「インデックスページ」、「データページ」内の空き領域が埋まった場合、
- 「ページ分割」が発生し、一部の「ページ」が、別の「エクステント」に格納されることがある。

例えば、SQL Serverでは
- 「インデックスページ」、「データページ」が埋まると、
  「ページ分割」により新しい行を挿入する余裕を作り出す。
- この作業にはコストがかかるため、DBサーバ全体のパフォーマンスを低下させる。
  「インデックスの断片化」は、「インデックスページ」、「データページ」の「ページ分割」が進んだ状態を指す。

「インデックスの断片化」が進んだ状態では、I/O 処理の連続性が失われ、
別の「エクステント」から断片化した「ページ」を取得するという余分なI/Oが発生する。

一般的に、この状態はセグメント（テーブル、インデックス）を「再構築」することで解消できる。

↑

「ページ密度」とは †

「ページ分割」は、
- DBサーバ全体のパフォーマンスを低下や、
- 「インデックスの断片化」による余分なI/Oの発生に

繋がる。このため、なるべく「ページ分割」が発生しないようにする必要がある。

「ページ分割」の発生を抑止するため、
- 更新と挿入が頻繁に行われる予定のテーブルや、インデックスには
  「ページ密度」を低く設定し、データの増加に対応する空き領域を残しておく。
- 「ページ密度」は、テーブル、インデックスの生成時に設定することができる。

ただし、「ページ密度」の値が低いと、
クエリを処理するために読み取るページ（エクステント）が多くなる可能性があるので、
以下のトレードオフを考慮し、「ページ密度」を決定する必要がある。
- 読み取り処理：読み取りページ（エクステント）数の増加
- 書き込み処理：「ページ分割」の発生

例えば、テーブルが読み取り専用で変更されない場合は、
テーブルや、インデックスの「ページ密度」を高く設定することで、
読み取りページ（エクステント）数を減らすことができる。

↑

「ページ密度」の設定 †

「ページ密度」は、「FILLFACTOR」オプションで設定することができる。

↑

「FILLFACTOR」オプション †

「FILLFACTOR」は、
- 「CREATE INDEX」ステートメント
- 「DBCC DBREINDEX」ステートメント
- 「DBCC INDEXDEFRAG」ステートメント

のオプションで指定できる。

このオプションは、
- 「インデックスページ」
- 「データページ」

の「ページ密度」を制御する。

通常、既定の「FILLFACTOR」で適切なパフォーマンスが得られるが、
場合によっては「FILLFACTOR」を変更することでさらにパフォーマンスが高まる。

↑

「PAD_INDEX」オプション †

「PAD_INDEX」は、「CREATE INDEX」のステートメントのオプションで指定できる。

このオプションは、インデックスの「リーフレベルページ」ではなく、
インデックスの「中間レベルページ」の「ページ密度」を制御する。

「PAD_INDEX」は「FILLFACTOR」で指定されているパーセンテージを使用するので、
「PAD_INDEX」は「FILLFACTOR」が指定されている場合にのみ有効になる。

Tags: :データアクセス, :SQL Server

SQL Server のインデックス のバックアップ(No.6)

目次 †

概要 †

インデックス種類 †

「クラスタ化インデックス」 †

「非クラスタ化インデックス」 †

「カバリング インデックス」 †

「付加列インデックス」 †

「パーティション インデックス」 †

「インデックス付きビュー」 †

インデックスの構造 †

インデックス ページ †

「クラスタ化インデックス」 †

ディスクI/Oのチューニング †

適合しないケース †

その他 †

参考 †

「非クラスタ化インデックス」 †

「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」 †

「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 †

「カバリング インデックス」 †

「付加列インデックス」 †

「パーティション インデックス」 †

「インデックス付きビュー」 †

インデックスと選択度 †

「非クラスタ化インデックス」と選択度 †

「クラスタ化インデックス」と選択度 †

計算式 †

選択度とインデックスの「ページ分割」 †

「インデックスの断片化」の管理 †

「インデックスの断片化」とは †

「ページ密度」とは †

「ページ密度」の設定 †

「FILLFACTOR」オプション †

「PAD_INDEX」オプション †

「カバリングインデックス」 †

「パーティションインデックス」 †

インデックスページ †

「カバリングインデックス」 †

「パーティションインデックス」 †