いまさら聞けないSQL Server 2005のインデックス　その２

クラスタ化インデックス

クラスタ化インデックスは、テーブルにつき１つ作成することができる。
クラスタ化インデックスの特徴は、テーブルでクラスタ化キーを設定すると、
そのキー値の昇順にデータが並び替えられて、クラスタ化インデックスの
リーフノードが実際のデータページ（リンクリストと実際のデータによって構成）として構成される点だ。
つまり、テーブル内の行データはクラスタ化インデックスによって定められた順序に従って、
ディスクドライブ内で物理的に配置される。このためクラスタ化インデックスは、
クラスタ化キー値で範囲検索をする場合および、クラスタ化キー値の順番でアクセスするときに優位性を持つ。

これは、データにアクセスする場合に目的のデータが同じページにある確率が高まり、
ディスクに対する物理 I/O が少なくなったり、大量の行を取り出すような場合、
連続的な I/O が行われることによって I/O のパフォーマンスが向上するからである。*1

しかし、クラスタ化インデックスを最初に作成した時点では
連続したページであっても、頻繁なデータ更新によってページ分割が発生してしまい、
I/O 処理の連続性が失われてしまう場合がある。このような環境ではクラスタ化インデックスは有効に働かない。
クラスタ化インデックスを有効に利用するためには、FILL FACTOR オプションや
PAD INDEX オプションを使ってリーフページ（クラスタ化インデックスの場合はデータページ）や
非リーフページ（インデックスページ）のインデックス初期作成時の充填率を少なく指定して
データ分割をなるべく行わせないようにしたり、DBCC INDEXDEFRAG ステートメントを使って
インデックスの断片化を解消したり、DBCC DBREINDEX ステートメントあるいは
CREATE INDEX ステートメントの DROP_EXISTING 句を使ってインデックスの再構築を行うことで、
インデックスを最適化してインデックスページの連続性を保つことを考慮に入れなければならない。

非クラスタ化インデックス

非クラスタ化インデックスは、テーブルに２４９個まで作成することができる。
SQL Server 7.0 以降の非クラスタ化インデックスは、クラスタ化インデックスが存在しない場合と
クラスタ化インデックスが存在する場合で大きく構造が異なる。

クラスタ化インデックスが存在しない場合の非クラスタ化インデックス

クラスタ化インデックスが存在しない非クラスタ化インデックスのリーフレベルはインデックスページとなる。
データページはクラスタ化インデックスを作成した場合のデータページとは構造が異なり、
リンクリスト（前後ページを指すポインタのリスト）はもたない。
このような、クラスタ化インデックスが存在しないデータページの集まりをヒープと呼ぶ。

ヒープでは、データの行の順番は特定の順序では格納されず、データページにも特定の順序はない。
クラスタ化インデックスが存在しない非クラスタ化インデックスでのリーフレベル（インデックスページ）では
ポインタとして行識別子（ファイルID、ページID、行ID）を格納していて、その行識別子を使って
ヒープへジャンプし直接、検索対象データを探し出すこととなる。

このためクラスタ化インデックスが存在しない非クラスタ化インデックスは、一般的に
選択度が高い検索条件を使用する（テーブルから比較的少数のデータを検索する）場合に優位性をもつことがある。
逆に言えば、選択度の低い検索条件では効率性が悪くなることがあると言える。

つまり、重複する値を多く含んだ大きなテーブルに非クラスタ化インデックスを作成した場合、
データ取得のために非クラスタ化インデックスを使ったほうが
単にテーブルスキャンをするよりも I/O が増えてしまう可能性があるからだ。
例えば 1000 名の '男性' 社員を検索したい時に非クラスタ化インデックスを使用すると、
データページに対して1000回の I/O が発生する可能性が考えられるが、
実際の男性社員情報が 100 ページに格納されている場合、テーブルスキャンでは 100 回のI/Oで済むこととなる。
このようなケースでは非クラスタ化インデックスは、有効に働かないといえる。
したがって非クラスタ化インデックスを決定する際には DBCC SHOW_STATISTICS ステートメントで
インデックスの選択度を表示し、非クラスタ化インデックスとして有効かどうかを事前に判断しておく必要がある。

クラスタ化インデックスが存在する場合の非クラスタ化インデックス

クラスタ化インデックスが存在する場合の非クラスタ化インデックスのリーフレベルは、
ヒープと同様にインデックスページとなるが、リーフレベルにはポインタとして「行識別子」ではなく、
「クラスタ化キー」を格納している。検索では、そのクラスタ化キーを用いて再度クラスタ化インデックスを
ルートページから探索し、検索対象データを探し出す。

このため、クラスタ化インデックスが存在する非クラスタ化インデックスは、
ページ分割がデータページに対して行われる場合および、選択度が高い検索条件を使用するに優位性をもつことがある。
通常、データページにページ分割が発生すると、ページデータの約半分が新しく割り当てられたページに移動し、
そのとき「クラスタ化インデックスが存在しない非クラスタ化インデックス」の場合は、
リーフページに行識別子を格納しているためデータの物理的な移動に伴い、行識別子を変更しなければいけなくなる。
しかし「クラスタ化インデックスが存在する非クラスタ化インデックス」の場合は、
リーフページにクラスタ化キーを格納しているためにデータの物理的な移動に影響を受けず、余分なオーバーヘッドが少なくて済む。

また、「クラスタ化インデックスが存在しない非クラスタ化インデックス」同様に
検索条件の選択度が高い場合は有利だが、クラスタ化インデックスを検索するため余分な I/O が発生するので、
なるべく最低限の I/O で済むように「クラスタ化インデックスが存在する非クラスタ化インデックス」は構成しないと優位性は保てない。
そのためにはインデックスのサイズを小さくするとよい。例えば int ではなく tinyint などの
出来る限り小さいデータ型を使用するようにしたり、char ではなく、varchar を使用して文字の平均サイズを小さくするなど。
このことは非クラスタ化インデックス自身も考慮しなければならないが、非クラスタ化インデックスのリーフページに
クラスタ化キー値が格納されるため、クラスタ化インデックスに関しても同様に考える必要がある。

複合インデックス

インデックスには１つまたは複数のカラムを含めることができる。
複数カラムに対するインデックスを指定するとき、これを「複合インデックス」と呼ぶ。
クエリが参照するすべての列がインデックスに含まれている場合、「インデックスはクエリをカバーしている」と言う。
このとき、クエリオプティマイザ*2は、インデックスページから検索結果を取得できるため、データページにアクセスする必要がなくなり、
ディスクI/Oとメモリ消費量が減少し、クエリパフォーマンスを向上させることができる。
ただし、サイズの大きなカラムをインデックスに含めると、インデックス自体のサイズが肥大化し
効率が上がらなくなる可能性があるので、インデックスに含める列は慎重に選択する必要がある*3。

付加列インデックス

SQL Server 2005 では、非クラスタ化インデックスのリーフレベルに非キー列を追加し、
非クラスタ化インデックスの機能を拡張することができる。非キー列を含めることにより、
より多くのクエリをカバーする非クラスタ化インデックスを作成できる。
これは、非キー列の場合は次の利点があるからです。

・非キー列には、インデックスキー列として許可されていないデータ型を設定できる。
・インデックスキー列の数やインデックスキーのサイズを計算するときに、データベースエンジンでは非キー列が考慮されない。

クエリ内のすべての列が、キー列または非キー列のいずれかとしてインデックスに含まれるているとき、
非キー付加列を含むインデックスにより、クエリパフォーマンスが大幅に向上する。
クエリオプティマイザではインデックス内のすべての列値を参照できるので、テーブルやクラスタ化インデックスの
データにアクセスすることがなく、ディスク I/O 操作が少なくて済むため、パフォーマンスが向上する。

ＸＭＬインデックス

ＸＭＬドキュメントは、xmlデータ型の列に格納される際、内部にＸＭＬインスタンスを
バイナリ表記したものをバイナリラージオブジェクト(BLOB)として格納します。
xmlデータ型のインスタンスは最大オブジェクトを評価するために細分化するが、
この処理に時間がかかる場合があります。ＸＭＬデータに対するクエリを頻繁に行うような場合は、
xmlデータ型の列にインデックスを設定すると効果的である。

*1:SQL Server 2005の特徴の一つに、Primary key制約を指定してテーブル作成すると、デフォルトでクラスタ化インデックスが作成される。基本的にSQLServerではクラスタ化インデックスを作成するメリットがあるためこのような動作が既定となっている。

*2:クエリオプティマイザ：クエリの実行要求に対して、Transact-SQLステートメントとテーブルに作成されたインデックス、および統計情報を評価し、最も効率のよい実行プランを決定するRDBエンジンのコンポーネント。

*3:SQL Server 2005 では、最大 16 個のキー列が設定でき、非キー列の最大数は 1,023 列である。また、すべてのインデックスキー列の最大合計サイズは 900 バイトに制限される。