Open棟梁Project - マイクロソフト系技術情報 Wiki
目次 †
概要 †
インデックスがないテーブルには基本的にデータの並び順に保証がないため、
これ検索する場合は、性能的に遅い「テーブル スキャン」を実行する。
このため、検索処理の効率化のために、検索条件に対応した「インデックス」を作成する。
SQL Serverのインデックスには、
- 「クラスタ化インデックス」
- 「非クラスタ化インデックス」
の２種類のインデックスがある。
インデックス種類 †
「非クラスタ化インデックス」 †
「非クラスタ化インデックス」はOracleの「索引」と同じであり、
「書籍の索引」のように、データとは別の領域に作られたインデックスのことを言う。
- SQL Serverでは、１テーブルに対し「非クラスタ化インデックス」を249個まで作成可能。
「クラスタ化インデックス」 †
「クラスタ化インデックス」はOracleの「索引構成表」と同じであり、
「電話帳の50音順索引」のように、データが順番に並べられたインデックスのことを言う。
- SQL Serverでは、１テーブルに対し「クラスタ化インデックス」を１つだけ作成可能。
- SQL Serverではテーブルに「主キー」を設定すると、自動的に「クラスタ化インデックス」が作成される。
- 「主キー」を「クラスタ化インデックス」にしたくないのであれば、主キー作成時に「NONCLUSTEREDキーワード」を指定する。
インデックスの構造 †
インデックス ページ †
インデックスの構造について説明する。
- インデックスは「インデックス ページ」から構成されており、「インデックス ページ」は、
- 同位層のページを繋ぐ「ポインタ」と、
- 下位層のページへの「ポインタ」
- および「キー値」
によって構成される。
- 「インデックス ページ」の
- 最上位層は「ルート レベル ページ」
- 最下位層は「リーフ レベル ページ」
- 「ルート レベル ページ」と「リーフ レベル ページ」の
中間のレベルは「中間レベル ページ」と呼ぶ。
クラスタ化インデックス †
次に、「クラスタ化インデックス」の構造について説明する。
「クラスタ化インデックス」は、テーブルで「クラスタ化キー（クラスタ化インデックスを作成する際に使用したキー）」を設定すると、
そのキー値の昇順にデータが並び替えられて、「リーフ レベル ページ」が実際の「データ ページ」として構成される。
- 「クラスタ化インデックス」は、「クラスタ化キー」の値の昇順にデータが並べられる。
- このため、テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、
目的のデータが同じ「データ ページ」にある確率が多くなりディスク ヘッドの移動が少なくなる。
- このように、「クラスタ化インデックス」でディスク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]")
