SQL Server における Clustered Index と Non-Clustered Index の使われ方

Clustered Index (CI) と Non-Clustered Index (NCI) とはそもそも何か

SQL ServerのインデックスはCIとNCIに分けられます。CIはデータベースの行そのものをCIキーに沿って並べ替えます。NCIはCIをバリューとする参照テーブルを作成します。CIは一般的にテーブルの主キーと同一であり、ひとつのテーブルに一つしか設定できません。NCIはひとつのテーブルに複数設定することができます。
一例として、StudentID, SchoolID, Name の3カラムを持つStudentテーブルを考えます。これに対し、StudentID を CIキー、SchoolID をNCIキーとして設定すると、データベース構造は以下のようになります。

Student テーブル

StudentID	SchoolID	Name
001	103	鈴木一郎
002	101	田中二郎
003	102	佐藤三郎
004	101	高橋四郎

StudentテーブルはCIキーでソートされます。

SchoolID インデックス

SchoolID	StudentID
101	002
101	004
102	003
103	001

NCIをキー、CIをバリューとしたテーブルが作られます。
これにより、WHERE句でインデックスキーが指定された場合、すべての行を参照する必要なく、求めたいデータに効率的にアクセスすることができます。

NCI が使われるとき

NCIによる検索が行われるのは、以下の2条件を満たす場合です。

1. SELECT 句にCIキーまたはNCIキーのみが含まれている
2. WHERE 句がない、またはNCIキーのみが含まれている

この場合、CIを使うよりNCIを使うほうが、必要な情報に速くアクセスできます。以下のSQLを考えてみましょう。

SELECT SchoolID, StudentID
FROM Student
WHERE SchoolID = '102'

SchoolIDはNCIキー、StudentIDはCIキーです。既に示したSchoolIDインデックスを見ると、なんとこれらのカラムが含まれています。つまりStudentテーブルそのものを参照する必要はないのです。

CIが使われるとき

NCIが使われない時なのですが、具体例を見るために以下のSQLを考えてみます。

SELECT SchoolID, StudentID
FROM Student
WHERE StudentID = '002'

SELECT句に含まれるカラムはNCIからでも取得できますが、WHERE句にCIキーが指定されています。この場合はStudentテーブルを参照し、CI Seekを行ったほうが高速です。CIを利用すればStudentIDが002の生徒はひとりだけだと即座にわかりますが、NCIを使うとすべての行を参照しなければならないからです。

SELECT SchoolID, StudentID, Name
FROM Student
WHERE SchoolID = '102'

この場合NCI SeekではなくCI Scan が行われます。理由はSELECT句にNameが含まれているので直接Studentテーブルが参照されるからです。NCI Seekしたほうがいいような気もしますが、NCIからテーブルを参照しに行く処理がオーバーヘッドになるのでしょう。

覚え方

実テーブルに含まれる情報を見に行かないといけないときはCI、そうでなければNCIが使われるとおぼえましょう :)