「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
概要 †
インデックスがないテーブルには基本的にデータの並び順に保証がないため、
これ検索する場合は、性能的に遅い「テーブル スキャン」を実行する。
このため、検索処理の効率化のために、検索条件に対応した「インデックス」を作成する。
SQL Serverのインデックスには、
- 「クラスタ化インデックス」
- 「非クラスタ化インデックス」
- 「カバリング インデックス」
- 「付加列インデックス」
- 「パーティション インデックス」
- 「インデックス付きビュー」
の6種類のインデックスがある。
インデックス種類 †
「クラスタ化インデックス」 †
「クラスタ化インデックス」はOracleの「索引構成表」と同じであり、
「電話帳の50音順索引」のように、データが順番に並べられたインデックスのことを言う。
- SQL Serverでは、1テーブルに対し「クラスタ化インデックス」を1つだけ作成可能。
- SQL Serverではテーブルに「主キー」を設定すると、自動的に「クラスタ化インデックス」が作成される。
- 「主キー」を「クラスタ化インデックス」にしたくないのであれば、主キー作成時に「NONCLUSTEREDキーワード」を指定する。
「非クラスタ化インデックス」 †
「非クラスタ化インデックス」はOracleの「索引」と同じであり、
「書籍の索引」のように、データとは別の領域に作られたインデックスのことを言う。
- SQL Serverでは、1テーブルに対し「非クラスタ化インデックス」を249個まで作成可能。
「カバリング インデックス」 †
- 「カバリング インデックス」とは、いわゆる、複合インデックスを指す。
- 「カバリング インデックス」は、性能の向上が期待できる。
「付加列インデックス」 †
- カバリンク列がルート・中間・リーフ ページに含まれるため、
- インデックス サイズが大きくなり、
- スキャンや、シーク時のI/O数が多くなり、
- インデックス更新時のオーバーヘッドも高くなる。
といった性能上の問題が存在する。
- これを回避するために、
SQL Server 2005からサポートされた「付加列インデックス」が使用できる。
- 「付加列インデックス」は、
「カバリング インデックス」の欠点を補った機能であるため、
基本的に「付加列インデックス」を利用することが推奨される。
「インデックス付きビュー」 †
- ビューに一意「クラスタ化インデックス」を付与することで、
「クラスタ化インデックス」を持つテーブルのように、
ビューに結果セットを格納するものである(つまり実体が存在する)。
- このため、特に結合・集計処理を伴う参照クエリで性能向上が期待できる。
- 「インデックス付きビュー」には、「非クラスタ化インデックス」を追加できる。
インデックスの構造 †
インデックス ページ †
インデックスの構造について説明する。
- インデックスは「インデックス ページ」から構成されており、「インデックス ページ」は、
- 同位層のページを繋ぐ「ポインタ」と、
- 下位層のページへの「ポインタ」
- および「キー値」
によって構成される。
- 「インデックス ページ」の
- 最上位層は「ルート レベル ページ」
- 最下位層は「リーフ レベル ページ」
- 「ルート レベル ページ」と「リーフ レベル ページ」の
中間のレベルは「中間レベル ページ」と呼ぶ。
「クラスタ化インデックス」 †
次に、「クラスタ化インデックス」の構造について説明する。
- 「クラスタ化インデックス」は、テーブルで「クラスタ化キー(クラスタ化インデックスを作成する際に使用したキー)」を設定すると、
そのキー値の昇順にデータが並び替えられて、「リーフ レベル ページ」が実際の「データ ページ」として構成される。
ディスクI/Oのチューニング †
「クラスタ化インデックス」でディスクI/Oのチューニングが可能である。
- 「非クラスタ化インデックス」で必要となるRID LookUp?という処理が不要で、その分性能が良い。
- テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、
目的のデータが同じ「データ ページ」にある確率が多くなりディスク ヘッドの移動が少なくなる。
- 「選択度の低い情報」(後述)であっても、
「範囲検索」、「順次アクセス」で、効果を出し得るインデックスであると言える。
- SQL Serverでは検索で多用される(と想定される)主キーには、
デフォルトで「クラスタ化インデックス」が付与される。
- しかし、この方法が必ずしも適切であるということにはならない。
例えば、主キー以外のキーを使用した範囲スキャン検索の性能の向上が優先されるようなテーブルでは、
主キーに「非クラスタ化インデックス」を付与し、「範囲スキャン検索」処理用のキーに「クラスタ化インデックス」を使用した方が、全体最適化に繋がることがある。
- インサイド Microsoft SQL Server 2005 クエリチューニング&最適化編
第4章 : クエリパフォーマンスのトラブルシューティング
テーブルを主キー制約で宣言すると、規定でクラスタ化インデックスが主キー列に作成されますが、この方法が常に最適であるとは限りません。
その名が示すとおり、主キーは一意であり、条件を満たす単一行を検索する場合は、非クラスタ化インデックスが非常に効率的です。
『主キーの一意性は、非クラスタ化インデックスでも適用できるため、クラスタ化インデックスは、主キー制約を宣言するときに
NONCLUSTEREDのキーワードを追加して、クラスタ化インデックスが有効なものに対して確保しておきます。』
適合しないケース †
また、以下のキーには適していないと言われている
- 頻繁に変更される列
物理的な並び替えが必要になるため。
- 広範なキー(複数の列・複数のサイズの大きな列を組み合わせたキー)
- 「クラスタ化インデックス」を持つテーブルに追加した「非クラスタ化インデックス」のリーフ ページには、
行識別子ではなく、「クラスタ化インデックス」のキー参照が格納される(後述)。
- このため、「クラスタ化インデックス」のキーのサイズが大きくなると、
「非クラスタ化インデックス」のサイズが大きくなるため。
その他 †
「クラスタ化インデックス」作成時には、
実際のデータ(ヒープ)を並べ替えた結果を格納しておくための作業領域として、
テーブル サイズの約 1.5 倍の空き領域が必要になるため注意が必要である。
参考 †
「非クラスタ化インデックス」 †
次に、「非クラスタ化インデックス」の構造について説明する。
- 「非クラスタ化インデックス」は、一般的かつ汎用的なインデックスであり、「リーフ レベル ページ」には、行識別子が格納される。
- 「非クラスタ化インデックス」では、「リーフ レベル ページ」からヒープ(のページ)上の行情報を引くための、RID LookUp?と言う処理が必要となる。
- このため、「リーフ レベル ページ」 → ヒープ(のページ)へのジャンプ
(これをRID LookUp?と言い、場合によってはディスク ヘッドの移動を要する)
が必要になるため、キーを使用した範囲スキャン検索で、データを収集するクエリの性能は、件数が多くなるほど向上しない。
- また、「選択度の低い情報」(後述) も同様に、範囲スキャン検索性能が向上しないため効果が出ない。
- また、「非クラスタ化インデックス」は、
- 「クラスタ化インデックス」が存在しない場合
- 「クラスタ化インデックス」が存在する場合
で構造が異なる。
「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」 †
- 「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」の「リーフ レベル ページ」は「インデックス ページ」である。
- 「データ ページ」は「クラスタ化インデックス」を作成した場合の「データ ページ」とは構造が異なり、「リンク リスト」はもたない。
- このような「非クラスタ化インデックス」の「データ ページ」の集まりを「ヒープ 」と呼ぶ。
- 「ヒープ」では、データの行の順番は特定の順序では格納されず、「データ ページ」にも特定の順序はない。
- 「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」での「リーフ レベル(インデックス ページ)」では
ポインタとして行識別子(ファイルID、ページID、行ID)を格納しており、その行識別子を使って「ヒープ」へジャンプし、検索対象データを探し出す。
「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 †
- 「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフ レベル ページ」は同様に
「インデックス ページ」であるが、「ポインタ」として「行識別子」ではなく「クラスタ化キー」の値を格納している。
- このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、
- 最初に「非クラスタ化インデックス」を使用して検索し、
- 「リーフ レベル ページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。
- 「非クラスタ化インデックス」のキーを使用して「クラスタ化インデックス」のキーのみ取得する場合は、非常に高速。
「カバリング インデックス」 †
- 「カバリング インデックス」は、以下により性能の向上が期待できる。
- 最初に指定された列をキーにして、木構造を構築し、
- 以降に指定された列(カバリンク列)をルート・中間・リーフ ページに含める。
- これにより、カバリンク列に対してはRID LookUp?をせずに処理が可能となる。
- この場合、
- 「column1」をキーにして、木構造が構築され、
- カバリンク列として「column2、column3」が
ルート・中間・リーフ ページに含められる。
「付加列インデックス」 †
- この場合、
- 「column1」をキーにして、木構造が構築され、
- 付加列として、「column2、column3」がリーフ ページにのみ含められる。
「インデックス付きビュー」 †
- GROUP BY句を使用した集計処理で指定されるキーの
選択度が高い(若しくは一意の)場合は、性能向上は期待できない。
- また、「インデックス付きビュー」の基テーブルの更新がされると、
- ビューに格納されている結果セットの更新が必要となるため、
更新処理が頻繁なビューに対して「インデックス付きビュー」を作成すると
余計にコストがかかる場合があるので注意する。
- なお、条件を満たしていれば「インデックス付きビュー」の更新も可能であり、
「インデックス付きビュー」の更新が行われた場合、基テーブルも更新される。
- 考慮点
- 「インデックス付きビュー」は、FROM句で「インデックス付きビュー」を
直接指定していないクエリからも、オプティマイザにより、使用されることがある 。
- 「インデックス付きビュー」を「パーティション テーブル」とすると、
さらにクエリ速度、効率を高められる可能性がある。
インデックスと選択度 †
- 一般的にインデックスは、
- 選択度が高い項目を検索条件に使用する場合に有用である。
- これとは逆に、選択度の低い項目では不利になることが多い。
- 選択度
- 選択度が高い=重複が少ない
(主キー、ユニーク キーなど)
- 選択度が低い=重複が多い。
(例えば、"男性"、"女性"というデータのみ格納する)
「非クラスタ化インデックス」と選択度 †
「非クラスタ化インデックス」は、選択度の低い項目に対しては不利である。
- 例えば、"男性"、"女性"というデータのみ格納する項目に対して、
「非クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に
「非クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。
- この場合、「非クラスタ化インデックス」では、
「リーフ レベル ページ」の「インデックス ページ」から「データ ページ」にアクセスするため
「データ ページ」に対して、最大で1000回ものI/Oが発生する可能性がある。
「クラスタ化インデックス」と選択度 †
「クラスタ化インデックス」は、選択度の低い項目に対して"も"有効である。
- 例えば、"男性"、"女性"というデータのみ格納する項目に対して、
「クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に
「クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。
- 「クラスタ化インデックス」を作成したテーブルでは、
「クラスタ化キー」の値(この場合、"男性"、"女性")毎にデータがまとまっているため、
- "男性"社員情報を読み込むページ数は最小化され、I/O回数も最小化される。
- また、「非クラスタ化インデックス」と異なり、
「リーフ レベル ページ」の「データ ページ」を直接スキャンすることができる。
- 例えば、「データ ページ」に10レコードが格納できる場合、
- 1000名の "男性" 社員のレコードは100ページに格納され、
- これが1つのエクステントに規則正しく格納されていれば、
- 最小で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