Open棟梁Project - マイクロソフト系技術情報 Wiki
目次 †
概要 †
インデックスがないテーブルには基本的にデータの並び順に保証がないため、
これ検索する場合は、性能的に遅い「テーブル スキャン」を実行する。
このため、検索処理の効率化のために、検索条件に対応した「インデックス」を作成する。
SQL Serverのインデックスには、
- 「クラスタ化インデックス」
- 「非クラスタ化インデックス」
の２種類のインデックスがある。
インデックス種類 †
「クラスタ化インデックス」 †
「クラスタ化インデックス」はOracleの「索引構成表」と同じであり、
「電話帳の50音順索引」のように、データが順番に並べられたインデックスのことを言う。
- SQL Serverでは、１テーブルに対し「クラスタ化インデックス」を１つだけ作成可能。
- SQL Serverではテーブルに「主キー」を設定すると、自動的に「クラスタ化インデックス」が作成される。
- 「主キー」を「クラスタ化インデックス」にしたくないのであれば、主キー作成時に「NONCLUSTEREDキーワード」を指定する。
「非クラスタ化インデックス」 †
「非クラスタ化インデックス」はOracleの「索引」と同じであり、
「書籍の索引」のように、データとは別の領域に作られたインデックスのことを言う。
- SQL Serverでは、１テーブルに対し「非クラスタ化インデックス」を249個まで作成可能。
インデックスの構造 †
インデックス ページ †
インデックスの構造について説明する。
- インデックスは「インデックス ページ」から構成されており、「インデックス ページ」は、
- 同位層のページを繋ぐ「ポインタ」と、
- 下位層のページへの「ポインタ」
- および「キー値」
によって構成される。
- 「インデックス ページ」の
- 最上位層は「ルート レベル ページ」
- 最下位層は「リーフ レベル ページ」
- 「ルート レベル ページ」と「リーフ レベル ページ」の
中間のレベルは「中間レベル ページ」と呼ぶ。
クラスタ化インデックス †
次に、「クラスタ化インデックス」の構造について説明する。
「クラスタ化インデックス」は、テーブルで「クラスタ化キー（クラスタ化インデックスを作成する際に使用したキー）」を設定すると、
そのキー値の昇順にデータが並び替えられて、「リーフ レベル ページ」が実際の「データ ページ」として構成される。
- 「クラスタ化インデックス」は、「クラスタ化キー」の値の昇順にデータが並べられる。
- このため、テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、
目的のデータが同じ「データ ページ」にある確率が多くなりディスク ヘッドの移動が少なくなる。
- このように、「クラスタ化インデックス」でディスクI/Oのチューニングが可能である。
非クラスタ化インデックス †
次に、「非クラスタ化インデックス」の構造について説明する。
- 「非クラスタ化インデックス」は、一般的かつ汎用的なインデックスであり、「リーフ レベル ページ」には、行識別子が格納される。
- 「非クラスタ化インデックス」では、「リーフ レベル ページ」からヒープ（のページ）上の行情報を引くための、RID LookUp?と言う処理が必要となる。
- このため、「リーフ レベル ページ」 → ヒープ（のページ）へのジャンプ
（これをRID LookUp?と言い、場合によってはディスク ヘッドの移動を要する）
が必要になるため、キーを使用した範囲スキャン検索で、
データを収集するクエリの性能は、件数が多くなるほど向上しない。
- また、「選択度の低い情報」 も同様に、範囲スキャン検索性能が向上しないため効果が出ない。
- また、「非クラスタ化インデックス」は、
- 「クラスタ化インデックス」が存在しない場合
- 「クラスタ化インデックス」が存在する場合
で構造が異なる。
「クラスタ化インデックス」が存在しない場合の「非クラスタ化インデックス」 †
- 「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」の「リーフ レベル ページ」は「インデックス ページ」である。
- 「データ ページ」は「クラスタ化インデックス」を作成した場合の「データ ページ」とは構造が異なり、「リンク リスト」はもたない。
- このような「クラスタ化インデックス」が存在しない場合の「データ ページ」の集まりを「ヒープ 」と呼ぶ。
- 「ヒープ」では、データの行の順番は特定の順序では格納されず、「データ ページ」にも特定の順序はない。
- 「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」での「リーフ レベル（インデックス ページ）」では
ポインタとして行識別子（ファイルID、ページID、行ID）を格納しており、その行識別子を使って「ヒープ」へジャンプし、検索対象データを探し出す。
「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 †
「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフ レベル ページ」は同様に
「インデックス ページ」であるが、「ポインタ」として「行識別子」ではなく「クラスタ化キー」の値を格納している。
このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、
- 最初に「非クラスタ化インデックス」を使用して検索し、
- 「リーフ レベル ページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。
インデックスと選択度 †
- 一般的にインデックスは、
- 選択度が高い項目を検索条件に使用する場合に有用である。
- これとは逆に、選択度の低い項目では不利になることが多い。
- 選択度
- 選択度が高い＝重複が少ない
（主キー、ユニーク キーなど）
- 選択度が低い＝重複が多い。
（例えば、"男性"、"女性"というデータのみ格納する）
「非クラスタ化インデックス」と選択度 †
「非クラスタ化インデックス」は、選択度の低い項目に対しては不利である。
- 例えば、"男性"、"女性"というデータのみ格納する項目に対して、
「非クラスタ化インデックス」を作成し、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」が指定されている場合にのみ有効になる。
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
