インデックスって何だ？
まず簡単に基礎知識。インデックスには、クラスタ化インデックスと非クラスタ化インデックスがある。
大雑把に言えば、クラスタ化インデックスとはレコードデータ自体のディスク上での順序を表す。だから、テーブル毎に一つしか作れない。クラスタ化インデックスの順序が隣接しているという事は、データが物理的に隣接されてディスクに格納されているという事である。
SQL Serverは、データを「ページ」という概念で格納する。I/Oは全てこの8KBのページが最小単位である。レコードをたった一行取得する場合でもページ毎に読み出される。クラスタ化インデックスの順序が近いデータは一つのページに含まれている可能性が高く、ページ読み込み数が少なくなり効率が良い。
インデックス経由で検索する場合は、B-Tree（バランスドツリー）を辿る。クラスタ化インデックスの場合、リーフノードに到達すればデータ本体がある。隣り合うリーフノードはリンクリストで繋がれている。
非クラスタ化インデックスの場合はリーフノードにデータはなく、データを識別する行ロケータがある（ヒープの場合は行ロケータ値、クラスタ化テーブルの場合はクラスタリングキー）。行ロケータからクラスタ化インデックスのキーとユニークキーを取得し、更にクラスタ化インデックスを辿りデータに到達する。（※1）。
図からもわかる通り、範囲検索をする場合、クラスタ化インデックスは圧倒的に速い。一度レコードに到達すれば、あとは下方にあるレコードをスキャンしていくだけである。ページが異なっても隣のページにリンクされており、I/Oコストは最小限に抑えられる。
一方、非クラスタ化インデックスは範囲検索にめっぽう弱い。非クラスタ化インデックス内のキーは一度にスキャンされるとはいえ、データ本体を探すためにクラスタ化インデックスのBツリーを何度も辿る。この時のI/Oコストは非常に大きい。テーブルスキャンの方が遥かににマシな場合もある。非クラスタ化インデックスは、特定の数件のレコードを探す事に適しているのだ。
以上が基本知識。続けてインデックスの設計基準を。
主キーにクラスタ化インデックスを作成するな
主キーとなる列にクラスタ化インデックスを作成してはいけない。90%以上（むしろ100%と言たいぐらいだ）のシナリオでこのルールは成立すると断言する。
テーブルの主キーとは、レコードを一意に特定するものだ。サロゲートキー（特に意味を持たない連番）であることも多々ある。後の仕様変更のリスクを避けるために、好んでサロゲートキーを使う設計者も多いだろう。そして大事なことであるが、主キーは範囲検索に滅多に（あるいは全く）使われない（※2）。
SQL Serverでは、デフォルトでクラスタ化インデックスをテーブルの主キーに設定してしまう。デフォルトの挙動なので多くの場合そのまま使われるかもしれない。これがパフォーマンスに悪影響を与える。
前述通り、クラスタ化インデックスとはレコードデータがディスクに格納される順番である。ということは、クラスタ化インデックスを主キーに設定してしまうと、範囲検索に殆ど使われないにもかかわらず、データが主キーの順番でディスクに並んでいる事になる！パフォーマンスの観点からは最悪の設計である。
では、クラスタ化インデックスとすべき列の基準は何なのか？
「データが物理的にかたまっていてほしい単位を表す列」である。例えば、「日付」「顧客」「商品カテゴリ」など。全ての仕様を考慮して、最も重要なデータの順番をクラスタ化インデックスにすべきである。主キーは非クラスタ化インデックスが適切だ。
安易に非クラスタ化インデックスを作成するな
「インデックスを作成するとデータの選択は速くなるがデータの更新にコストがかかる」と説明される事もあるが、それは非クラスタ化インデックスのコストの全容を説明しきれていない。非クラスタ化インデックスのせいで検索が遅くなる場合もある。それは「選択度が低い」ときだ。
選択度（Selectivity）とは何か。
例えば、10万行のデータのうち、たった1件のレコードを取得するクエリは「選択度が高い」、5万行を取得するクエリは「選択度が低い」と言う。
前述した通り、非クラスタ化インデックス経由の検索は、更にクラスタ化インデックスを経由しなければデータ本体は取得できない。選択度が低いクエリがこれをするとものすごくI/Oコストがかかる。単純にテーブルスキャンする方が圧倒的に速い。
ただ、実際にはクエリオプティマイザがインデックスの選択度がそのクエリに対して有効かどうか判定してくれるだろう。それには統計情報が必要ではあるが。
カバリングインデックス、付加列インデックスを使い分けろ
先ほどから述べているように、非クラスタ化インデックスは範囲検索に全く向いていない。では、範囲検索はクラスタ化インデックスしか使えないのか、というと当然そんなことはない。
カバリングインデックスとは、WHERE句で指定する列だけでSELECTの列を満たせるときに作る非クラスタ化インデックスである。
SELECT key1, key2 FROM Table1 WHERE key1 = 1, key2 = 'A'
このような場合は、クラスタ化インデックスを経由せずともクエリを満たせるためクラスタ化インデックスとほぼ同様のパフォーマンスを出せる事になる。「ほぼ」と言ったのは、実はクラスタ化インデックスよりも効率が良いからだ。
クラスタ化インデックスは列をすべて含むため、データサイズが大きい。必然的にリーフページ内に収まるレコード数が少なくなる。一方、非クラスタ化インデックスは特定の列しか含んでいないためデータサイズが小さく、リーフページ内に収まるレコード数が多い。という事は、クラスタ化インデックス経由のクエリよりページ読み込み数が小さくなる可能性があり、I/Oコストが下がる。良いこと尽くしである。
ところで、先ほどのクエリはWHERE句とSELECTの列が同じだったから良いが、以下のようなクエリではどうか。
SELECT key1, key2 FROM Table1 WHERE key1 = 1
こういう場合にカバリングインデックスを作成すると中間ノードに不要な列（key2）ができる。代わりに付加列インデックスを作成すると良い。
図からもわかる通り、リーフページにのみ列が追加される。よって中間ノードのページ内のデータ量が増え、カバリングインデックスよりI/Oコストが下がる。
インデックスの設計は開発後期に行うのが正しい
まとめると、テーブル設計時にはインデックスを決定できない事が普通である。主キーをクラスタ化インデックスにしたり、WHERE句に使われる列を単純に非クラスタ化インデックスにしていはいけない。
インデックスの基準は「選択度」である。選択度が高い場合は非クラスタ化インデックスを、低い場合はカバリングインデックスや付加列インデックスを作成する。選択度が低く最重要のものをクラスタ化インデックスとする。
以下の図は、「選択度」「必要とする列」を大雑把に分類した場合にどのインデックスが向いているか示したもの。
データの特性、どういうクエリがあるのか、そういった事が明確になる開発後期にインデックス設計を熟考する。運用後に再度見直しを要することもあるだろう。
もっと詳しく知りたい人は、「インサイド SQL Serverシリーズ」を。インサイドMicrosoft SQL Server 2005 クエリチューニング&最適化編 (マイクロソフト公式解説書)
※1 説明を省くが、必ずしもクラスタ化インデックスを作る必要はない。テーブルは、クラスタ化インデックスがないときは「ヒープ」、クラスタ化インデックスがあるときは「クラスタ化テーブル」と呼ばれる状態になっている。ここでは常にクラスタ化テーブルであるという前提で話を進める。
※2 勿論、主キーの設計次第である。主キーとなり得ながら順番も重要な列は範囲検索に使われるだろう。
投稿
