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

クラスタ化インデックス

クラスタ化インデックスでは、テーブルのデータ行がインデックスキー列の値によって
物理的に並べ替えられるので、以下に該当するものに利用すると効果が期待できる。

１．BETWEEN、＜、＞、＜＝、＞＝といった演算子を使用する範囲検索クエリのWHERE句に使用される列
２．ORDER BY または、GROUP BY句に指定された列
３．JOIN句で結合する列

テーブルにPRIMARY KEY制約を設定する際、クラスタ化インデックスが作成されていない場合は、
既定で自動的にクラスタ化インデックスが作成される。がしかし、主キー列以外のインデックスの方が
クラスタ化インデックスとしてふさわしい場合もある。そのような場合は、
PRIMARY KEY 制約の宣言にNONCLUSTERDを追加し、クラスタ化インデックスは、
より必然性の高い利用目的のためにとっておいたほうがよい。
主キーで検索する場合は非クラスタ化インデックスによる検索でもそれほど遅くはないので、
クラスタ化インデックスが威力を発揮するBETWEEN等を使った範囲検索クエリの列に対して
付与することを検討した方がパフォーマンスの向上を期待できるケースもあるということは、念頭に置いておいたほうがいいだろう。
「だったらわざわざデフォルトで主キーに自動でクラスタ化インデックス張らないでくれよ〜」って思ってしまうんだが。

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

非クラスタ化インデックスのリーフレベルには、非クラスタ化キーの値および、行ロケータが含まれている。
非クラスタ化インデックスは完全一致検索に対しては高いパフォーマンスが期待できるが、
範囲検索クエリにおいては非効率となる場合が多い。
範囲検索の場合、クエリがデータ行に含まれる値を必要とすると、この行ロケータを使用した
行データへのランダムアクセスが発生してしまう。たとえば顧客テーブルに対して、
顧客の生年月日を範囲としたクエリにより、顧客ＩＤ、顧客名、住所などを取得するクエリを考える。
この場合、条件に該当するデータを見つけるには、非クラスタ化インデックスのリーフレベルに置かれた
行ロケータ(テーブルにクラスタ化インデックスがある場合にはクラスタ化キー、ない場合は、行へのポインタ)を使用して、
データページを何度も検索する必要がでてきてしまい、余計にコストがかかってしまうことになる。

このような問題に対しては、複数列を含む非クラスタ化インデックスを作成することで改善することができる。
クエリによって参照される列がすべてクラスタ化インデックスに含まれていれば、クエリオプティマイザーは
データページからデータを取得する必要がなくなり、インデックスページからすべての検索結果を返せるため、
クエリのパフォーマンスが向上する。この場合のことを、インデックスはクエリをカバーしていると言う。
顧客テーブルの例の場合、生年月日だけではなく、社員名と住所も非クラスタ化インデックスに含めてしまい、
複合インデックス化してしまえば解決するように見える。しかし、それでは不十分。
なぜなら、インデックスキーの列のサイズがインデックスページ数に影響を与えるからだ。
インデックスのBツリーは出来る限り小さくなるように設計したほうがよい。
顧客名と住所の列はいずれも文字列型で、今回の検索条件には使用されないので、複合インデックス化するよりも、
非クラスタ化インデックスの付加列として指定した方がより効果的なインデックスとなる。

付加列は、CREATE NONCLUSTERD INDEXステートメントにINCLUDE区を記述することで定義できる。

CREATE NONCLUSTERD INDEX [IX_CUSTOMER_BIRTHDAY] ON [TEST].[CUSTOMER]([BIRTHDAY] ASC)
INCLUDE ( [NAME] , [ADDRESS] )
WITH (SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , IGNORE_DUP_KEY = OFF , ONLINE = OFF) ON [PRIMARY]

IGNORE_DUP_KEY オプションを使用した重複値の処理

一意インデックスまたは制約を作成または変更する場合、IGNORE_DUP_KEY オプションを ON または OFF に設定する。
このオプションでは、インデックスが作成された後、複数行の INSERT ステートメントに重複したキー値が存在する場合の
エラー応答を指定することができる。IGNORE_DUP_KEY を OFF (既定値) に設定すると、
１つ以上の行に重複したキー値が含まれている場合、ステートメントのすべての行が拒否されまる。
また、ON に設定すると、重複したキー値が含まれた行のみが拒否され、重複していないキー値は追加されます。

たとえば、1 つのステートメントで、一意インデックスが含まれたテーブルに 20 行を挿入する場合、
これらの行のうちの 10 行に重複したキー値が含まれているとする。
このとき、IGNORE_DUP_KEYがOFF(既定値)の場合、20 行すべてが既定で拒否される。
しかし、インデックスオプション IGNORE_DUP_KEY が ON に設定されている場合は、
重複している 10 個のキー値のみが拒否され、重複していないその他の 10 個のキー値はテーブルに挿入される。