みなさん、こんにちは。
さて 3 回目となる今回は、どの列をインデックスに含めれば良いかということを考えたいと思います。
クラスタ化インデックス
Dynamics CRM では、クラスタ化インデックスは既定で作成されます。基本的には列を一意に特定でき、かつ更新が 極力ない列、または、そのような列の組み合わせをクラスタ化インデックスにすると良いとされています。そのような列が テーブルに無い場合は、guid 列を作成してクラスタ化インデックスにすると良いでしょう。並べ替えが発生しないように、 guid を作成する場合は newsequentialid() という関数が使えますので、参考にしてください。
非クラスタ化インデックス
さて、問題となるのが非クラスタ化インデックスですが、どのように含める列を考えればいいのでしょうか。インデックスを 作成する理由は、データを並べ替えておいて検索時のパフォーマンスをあげることが目的ですから、基本的には Where 句 に含まれる列のうち、インデックスを有効に使えるものを含めることになります。しかしそれ以外の目的で列を含めることも あります。
非クラスタ化インデックスに列を含める場所は、実は2箇所あります。まず非クラスタ化インデックスの構造を見てみましょう。 非クラスタ化インデックスは、以下のようなツリーの形式をとっています。 （図は MSDN より）
上記に示されるように、非クラスタ化インデックスはルート ノードがあり、最下部にリーフ ノードがあります。またデータの 数によっては、複数の中間ノードが、ルートとリーフの間に存在します。インデックスに列を含めるということは、基本的には 全てのページに、対象となる列のデータが含まれます。つまり、列A、列B、列C を非クラスタ化インデックスに含めると、 全てのノードの全てのページに、この 3 つの列のデータが含まれます。
この一方で、一番下の層であるリーフ ノードにだけ列を含めるテクニックも存在します。それが付加列インデックスです。 ではなぜそのようなテクニックが必要か、以下に具体例を挙げて説明します。
付加列インデックスが有効な理由
以下のクエリを考えて見ましょう。
select firstname, lastname, address, phone from contact where lastname = “中村”
この場合、通常は lastname 列に非クラスタ化インデックスを作成します。では、その状態で、SQL Server はどのような 動きをするでしょうか。基本的には、以下のような動作を行います。
1. lastname 列に作成された非クラスタ化インデックスを検索して、中村に該当する列のキー列を取得する。
2. contact テーブルに対して、取得したキー列を使って表示に必要な列のデータを取得する。
ある意味シンプルな動作ですが、ここで SQL Server は非クラスタ化インデックスのデータと、実際のテーブルのデータの 2 箇所を検索しています。この動作を Key Lookup 操作と言います。
では、非クラスタ化インデックス自体に、表示する列も含めるとどうなるでしょうか。答えは簡単で、実際のテーブルへの 検索は行いません。なぜなら、結果の表示に必要な全ての情報が、非クラスタ化インデックスに存在するからです。
しかし、前述したとおり、非クラスタ化インデックスに列を含めるということは、ページに実データを持つこととなります。 今回の場合、検索を絞り込むためには lastname 列しか使っていないため、他の列までルートや中間ノードにデータを 持っても、意味がありません。そこでこれらの列を付加列インデックスとして追加することで、ルートや中間ノードには、 lastname のデータだけを、リーフ ノードには、lastname 列と他の列のデータを含めることができます。
全ての表示列を付加列インデックスに含めるべき？
インデックスに列を含めるということは、実際のデータをインデックスのデータに持つということですから、例えば select 文が テーブルのほぼ全ての列を含んでいるとすると、インデックスのデータ量は、実際のテーブルのデータ量に匹敵します。 よってバランスが必要になります。また含める列が増えれば増えるほど、更新時などにメンテナンスが必要となる可能性が 高くなりますので、そのあたりも考慮に入れましょう。
今回はここまでです。
次回は実際に Dynamics CRM にクエリを投げてみて、どのようなインデックスがパフォーマンスを改善するか具体的に 紹介したいと思います。
参考URL：
非クラスタ化インデックスの構造 http://msdn.microsoft.com/ja-jp/library/ms177484.aspx
付加列インデックス http://msdn.microsoft.com/ja-jp/library/ms190806.aspx
NEWSEQUENTIALID() http://msdn.microsoft.com/ja-jp/library/ms189786(SQL.90).aspx
Key Lookup プラン表示操作 http://msdn.microsoft.com/ja-jp/library/bb326635(SQL.90).aspx
- Dynamics CRM サポート 中村 憲一郎