![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]] -[[戻る>SQL Server]] * 目次 [#c756c9b8] #contents *概要 [#x12ae7fc] インデックスがないテーブルには基本的にデータの並び順に保証がないため、~ これ検索する場合は、性能的に遅い「テーブル スキャン」を実行する。~ このため、検索処理の効率化のために、検索条件に対応した「インデックス」を作成する。 SQL Serverのインデックスには、 -「クラスタ化インデックス」 -「非クラスタ化インデックス」 の２種類のインデックスがある。 *インデックス種類 [#u7fe9011] **「クラスタ化インデックス」 [#v844b64f] 「クラスタ化インデックス」はOracleの「索引構成表」と同じであり、~ 「電話帳の50音順索引」のように、データが順番に並べられたインデックスのことを言う。 -SQL Serverでは、１テーブルに対し「クラスタ化インデックス」を１つだけ作成可能。 -SQL Serverではテーブルに「主キー」を設定すると、自動的に「クラスタ化インデックス」が作成される。 -「主キー」を「クラスタ化インデックス」にしたくないのであれば、主キー作成時に「NONCLUSTEREDキーワード」を指定する。 **「非クラスタ化インデックス」 [#c5e07092] 「非クラスタ化インデックス」はOracleの「索引」と同じであり、~ 「書籍の索引」のように、データとは別の領域に作られたインデックスのことを言う。 -SQL Serverでは、１テーブルに対し「非クラスタ化インデックス」を249個まで作成可能。 *インデックスの構造 [#g4004d83] **インデックス ページ [#h2d2b2e3] インデックスの構造について説明する。 -インデックスは「インデックス ページ」から構成されており、「インデックス ページ」は、 --同位層のページを繋ぐ「ポインタ」と、 --下位層のページへの「ポインタ」 --および「キー値」 >によって構成される。 -「インデックス ページ」の --最上位層は「ルート レベル ページ」 --最下位層は「リーフ レベル ページ」 --「ルート レベル ページ」と「リーフ レベル ページ」の~ 中間のレベルは「中間レベル ページ」と呼ぶ。 #ref(IndexPage.png,left,nowrap,インデックス ページ) **クラスタ化インデックス [#k75f817f] 次に、「クラスタ化インデックス」の構造について説明する。 「クラスタ化インデックス」は、テーブルで「クラスタ化キー（クラスタ化インデックスを作成する際に使用したキー）」を設定すると、~ そのキー値の昇順にデータが並び替えられて、「リーフ レベル ページ」が実際の「データ ページ」として構成される。 #ref(ClusteredIndex.png,left,nowrap,クラスタ化インデックス) -「クラスタ化インデックス」は、「クラスタ化キー」の値の昇順にデータが並べられる。 -このため、テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、~ 目的のデータが同じ「データ ページ」にある確率が多くなりディスク ヘッドの移動が少なくなる。 -このように、「クラスタ化インデックス」でディスクI/Oのチューニングが可能である。 **非クラスタ化インデックス [#zd59289b] 次に、「非クラスタ化インデックス」の構造について説明する。 -「非クラスタ化インデックス」は、一般的かつ汎用的なインデックスであり、「リーフ レベル ページ」には、行識別子が格納される。 -「非クラスタ化インデックス」では、「リーフ レベル ページ」からヒープ（のページ）上の行情報を引くための、RID LookUpと言う処理が必要となる。 --このため、「リーフ レベル ページ」 → ヒープ（のページ）へのジャンプ~ （これをRID LookUpと言い、場合によってはディスク ヘッドの移動を要する）~ が必要になるため、キーを使用した範囲スキャン検索で、~ データを収集するクエリの性能は、件数が多くなるほど向上しない。 --また、「選択度の低い情報」 も同様に、範囲スキャン検索性能が向上しないため効果が出ない。 -また、「非クラスタ化インデックス」は、 --「クラスタ化インデックス」が存在しない場合 --「クラスタ化インデックス」が存在する場合 >で構造が異なる。 -非クラスター化インデックスのデザイン ガイドライン~ http://msdn.microsoft.com/ja-jp/library/ms187019.aspx ***「クラスタ化インデックス」が存在しない場合の「非クラスタ化インデックス」 [#kc3ba438] -「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」の「リーフ レベル ページ」は「インデックス ページ」である。 -「データ ページ」は「クラスタ化インデックス」を作成した場合の「データ ページ」とは構造が異なり、「リンク リスト」はもたない。 --このような「クラスタ化インデックス」が存在しない場合の「データ ページ」の集まりを「ヒープ 」と呼ぶ。 --「ヒープ」では、データの行の順番は特定の順序では格納されず、「データ ページ」にも特定の順序はない。 -「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」での「リーフ レベル（インデックス ページ）」では~ ポインタとして行識別子（ファイルID、ページID、行ID）を格納しており、その行識別子を使って「ヒープ」へジャンプし、検索対象データを探し出す。 #ref(NonClusteredIndex.png,left,nowrap,「クラスタ化インデックス」が存在しない場合の「非クラスタ化インデックス」) ***「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 [#m1cd6f28] 「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフ レベル ページ」は同様に~ 「インデックス ページ」であるが、「ポインタ」として「行識別子」ではなく「クラスタ化キー」の値を格納している。 #ref(NonClusteredIndexWithClusteredIndex.png,left,nowrap,「クラスタ化インデックス」が存在する「非クラスタ化インデックス」) このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、 -最初に「非クラスタ化インデックス」を使用して検索し、 -「リーフ レベル ページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。 *インデックスと選択度 [#u8131eb5] -一般的にインデックスは、 --選択度が高い項目を検索条件に使用する場合に有用である。 --これとは逆に、選択度の低い項目では不利になることが多い。 -選択度 --選択度が高い＝重複が少ない~ （主キー、ユニーク キーなど） --選択度が低い＝重複が多い。~ （例えば、"男性"、"女性"というデータのみ格納する） **「非クラスタ化インデックス」と選択度 [#qd900a52] 「非クラスタ化インデックス」は、選択度の低い項目に対しては不利である。 -例えば、"男性"、"女性"というデータのみ格納する項目に対して、~ 「非クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に~ 「非クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。 -この場合、「非クラスタ化インデックス」では、~ 「リーフ レベル ページ」の「インデックス ページ」から「データ ページ」にアクセスするため~ 「データ ページ」に対して、最大で1000回ものI/Oが発生する可能性がある。 **「クラスタ化インデックス」と選択度 [#y89d89a0] 「クラスタ化インデックス」は、選択度の低い項目に対して"も"有効である。 -例えば、"男性"、"女性"というデータのみ格納する項目に対して、~ 「クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に~ 「クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。 -「クラスタ化インデックス」を作成したテーブルでは、~ 「クラスタ化キー」の値（この場合、"男性"、"女性"）毎にデータがまとまっているため、 --"男性"社員情報を読み込むページ数は最小化され、I/O回数も最小化される。 --また、「非クラスタ化インデックス」と異なり、~ 「リーフ レベル ページ」の「データ ページ」を直接スキャンすることができる。 -例えば、「データ ページ」に10レコードが格納できる場合、 --1000名の "男性" 社員のレコードは100ページに格納され、 --これが１つのエクステントに規則正しく格納されていれば、 --最小で13回のI/Oで読み取りが完了する。 ***計算式 [#c0ca9e5d] 1000（レコード） / 10（レコード / ページ） / 8（ページ / エクステント） ≒ 13エクステント ≒ 13回のI/O ※ SQL Server は、ディスクI/Oを、ディスク上管理単位である「エクステント」単位で処理する。 **選択度と「ページ分割」 [#f9ac1e25] なお、選択度の低いデータでは、どちらのインデックスでも、~ データの挿入時に、「ページ分割」が発生しやすくなり、不利である。 >「ページ分割」については、~ 「[[「インデックスの断片化」の管理>#z9ed663c]]」で説明する。 選択度の低い項目をキーにした「クラスタ化インデックス」の作成は、 -検索（「範囲検索」・「順次アクセス」）の効率 -データ更新時の「ページ分割」のオーバーヘッド のトレードオフを考慮する形になる。 *「インデックスの断片化」の管理 [#z9ed663c] **「インデックスの断片化」とは [#m65eaba4] DBの「データ ファイル」は、 -論理的な「セグメント」、 -物理的な「エクステント」 から構成される。 「セグメント」とは、テーブル、インデックスといった、オブジェクトを意味する。 SQL Server は、 -ディスクI/Oを、ディスク上管理単位である64KBの「エクステント」単位で処理する。 -また、「エクステント」は、メモリ上の管理単位である8KBの「ページ」から構成される。 #ref(SegmentExtentPage.png,left,nowrap,「セグメント」、「エクステント」、「ページ」) -データの追加、更新処理などで、 --「インデックス ページ」、「データ ページ」内の空き領域が埋まった場合、 --「ページ分割」が発生し、一部の「ページ」が、別の「エクステント」に格納されることがある。 -例えば、SQL Serverでは --「インデックス ページ」、「データ ページ」が埋まると、~ 「ページ分割」により新しい行を挿入する余裕を作り出す。~ --この作業にはコストがかかるため、DBサーバ全体のパフォーマンスを低下させる。~ 「インデックスの断片化」は、「インデックス ページ」、「データ ページ」の「ページ分割」が進んだ状態を指す。 #ref(Fragmentation.png,left,nowrap,インデックスの断片化（ページ分割が進んだ状態）) -「インデックスの断片化」が進んだ状態では、I/O 処理の連続性が失われ、~ 別の「エクステント」から断片化した「ページ」を取得するという余分なI/Oが発生する。 #ref(ExtraIO.png,left,nowrap,インデックスの断片化による余分なＩ/Ｏの発生) -一般的に、この状態はセグメント（テーブル、インデックス）を「再構築」することで解消できる。 **「ページ密度」とは [#gebab5b2] -「ページ分割」は、 --DBサーバ全体のパフォーマンスを低下や、 --「インデックスの断片化」による余分なI/Oの発生に >繋がる。このため、なるべく「ページ分割」が発生しないようにする必要がある。 -「ページ分割」の発生を抑止するため、 --更新と挿入が頻繁に行われる予定のテーブルや、インデックスには~ 「ページ密度」を低く設定し、データの増加に対応する空き領域を残しておく。 --「ページ密度」は、テーブル、インデックスの生成時に設定することができる。 -ただし、「ページ密度」の値が低いと、~ クエリを処理するために読み取るページ（エクステント）が多くなる可能性があるので、~ 以下のトレードオフを考慮し、「ページ密度」を決定する必要がある。 --読み取り処理：読み取りページ（エクステント）数の増加 --書き込み処理：「ページ分割」の発生 -例えば、テーブルが読み取り専用で変更されない場合は、~ テーブルや、インデックスの「ページ密度」を高く設定することで、~ 読み取りページ（エクステント）数を減らすことができる。 **「ページ密度」の設定 [#s87a04aa] 「ページ密度」は、「FILLFACTOR」オプションで設定することができる。 ***「FILLFACTOR」オプション [#f7eadcd6] -「FILLFACTOR」は、 --「CREATE INDEX」ステートメント --「DBCC DBREINDEX」ステートメント --「DBCC INDEXDEFRAG」ステートメント >のオプションで指定できる。 -このオプションは、 --「インデックス ページ」 --「データ ページ」 >の「ページ密度」を制御する。 -通常、既定の「FILLFACTOR」で適切なパフォーマンスが得られるが、~ 場合によっては「FILLFACTOR」を変更することでさらにパフォーマンスが高まる。 ***「PAD_INDEX」オプション [#z418b05f] -「PAD_INDEX」は、「CREATE INDEX」のステートメントのオプションで指定できる。 -このオプションは、インデックスの「リーフ レベル ページ」ではなく、~ インデックスの「中間レベル ページ」の「ページ密度」を制御する。 -「PAD_INDEX」は「FILLFACTOR」で指定されているパーセンテージを使用するので、~ 「PAD_INDEX」は「FILLFACTOR」が指定されている場合にのみ有効になる。
