【SQL Server】クラスター化インデックスと非クラスター化インデックス

SQLserverのインデックスのうちよく使われる基本的なインデックスであるクラスター化インデックス、非クラスター化インデックス、付加列について解説します。これらはSQLserverのクエリをチューニングするうえでの基礎となるので押さえておくとよいでしょう。
以下のようなテーブルを例に説明します。

1.クラスターインデックス

クラスターインデックスは以下図のような構成となっています。
例)EMPLOYEE_ID = '1002'を検索した場合

ポイントとしては、クラスターインデックスのリーフノードにはテーブルのデータ部分が含まれていることでしょうか。
クラスターインデックスがないテーブルの場合ヒープになりますが、ここではヒープの説明は割愛させていただきます。

2.非クラスターインデックス

非クラスターインデックスは以下図のような構成となっています。
例)FIRST_NAME = 'Arizabeth' を検索した場合

非クラスターインデックスのリーフには、実データへの参照先として、ヒープの場合は行識別子、クラスターインデックスが存在する場合はクラスターインデックスのキー列が含まれています。
非クラスターインデックスのリーフだけで参照列が不足している場合には、行識別子やクラスターインデックスのキー列を使用してデータを取得します。

3.付加列

非クラスターインデックスに付加列を追加した場合は以下図のような構成となります。
例)FIRST_NAME = 'Arizabeth' を検索し、FIRST_NAME、LAST_NAME,EMAILを取得したい場合

WHERE句に使用される列を付加列を追加しても効果はありませんが、取得したい列が追加である場合に有効かと思います。

尚、複合インデックスの場合は中間リーフにもその列は含むことになります。WHERE句に使っていないが、参照したい列がある場合は付加列のインデックスを使うとよいでしょう。
特にその列のデータ長が大きい場合、複合インデックスにすると中間リーフの1ページあたりのデータ量が多くなってしまうので、付加列を使用することで中間リーフの1ページあたりのデータの格納効率がよくなります。

実際にクエリを実行して確認してみます。
各種インデックスは次の通り。

複合インデックスに含まれない列を参照した場合は、下記のようにキー参照(Key Lookup)が発生しています。

非クラスターインデックス(NCIX_1)にはEMAIL列を含んでいないため、先に非クラスターインデックスを参照した後、キー参照にてクラスターインデックスを参照しています。
イメージ的には以下のような形でしょうか。

実際にページの読み取り数を確認すると、論理読み取り数が4であることが確認できます。

複合インデックスに含まれる列だけを参照した場合は、下記のようにキー参照(Key Lookup)が発生していないことがわかります。

実際にページの読み取り数を確認すると、論理読み取り数が2であり、非クラスターインデックスのみでデータを取得できていることがわかります。

ちなみに今回は、下記のとおりインデックスの奥行はどちらも2であるため、各インデックスのページ読み取りは2でしたが、奥行が深くなると読み取り数はそれに応じて増えるかと思います。

キー参照が発生するとその分参照するページ数が増加するので、極力減らすことが基本となります。

同様に複合インデックスと付加列で参照できる場合は、下記のようにキー参照(Key Lookup)が発生していないことがわかります。

ちなみにインデックスの奥行を調べたい場合は以下のような手順で確認できます。
①オブジェクトエクスプローラーで調べたいインデックスを選択して、右クリックを押下し、「プロパティ（R)」を選択

②左ペインの「断片化」を選択

インデックスのページ数やリーフレベルの行、奥行をクエリで調べるなら下記のようなクエリで検索できます。
※他の調査用に使っていたものの使いまわしであまりイケてないですが。

SELECT 
    DB_NAME() AS [db_name]
	, SCHEMA_NAME(so.schema_id) AS [schema_name]
	, OBJECT_NAME(si.object_id) AS [Table_name]
	, si.name
	, si.type_desc
	, ips.alloc_unit_type_desc
	, ips.page_count
	, SUBSTRING(idxcolinfo.idxcolname,1,LEN(idxcolinfo.idxcolname) -1) AS [INDEX_Columns]
	, SUBSTRING(idxinccolinfo.idxinccolname,1,LEN(idxinccolinfo.idxinccolname) -1) AS [INDEX_Columns_Include]
	, dps.row_count
FROM		sys.dm_db_index_physical_stats (DB_ID(), NULL , NULL , NULL , 'DETAILED' ) AS ips,	
			sys.indexes AS si
LEFT JOIN	sys.dm_db_index_usage_stats AS ius
	ON	ius.object_id = si.object_id
	AND	ius.index_id = si.index_id
	AND	ius.database_id = DB_ID()
LEFT JOIN	sys.dm_db_partition_stats AS dps
	ON	si.object_id = dps.object_id
	AND	si.index_id = dps.index_id
LEFT JOIN	sys.objects so
	ON	si.object_id = so.object_id
LEFT JOIN	sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
	ON	ios.object_id = si.object_id
	AND	ios.index_id = si.index_id
	AND	ios.partition_number = dps.partition_number
CROSS APPLY
	(SELECT sc.name + ','
		FROM			sys.index_columns AS sic
		INNER JOIN	sys.columns AS sc
		ON	sic.object_id = sc.object_id
		AND	sic.column_id = sc.column_id
		WHERE	
				sic.object_id = si.object_id
		AND	sic.index_id = si.index_id
		AND	sic.is_included_column = 0
	FOR XML PATH('') ) AS idxcolinfo(idxcolname)
CROSS APPLY
	(SELECT sc.name + ','
		FROM			sys.index_columns AS sic	
		INNER JOIN	sys.columns AS sc
		ON	sic.object_id = sc.object_id
		AND	sic.column_id = sc.column_id
	WHERE
				sic.object_id = si.object_id
		AND	sic.index_id = si.index_id
		AND	sic.is_included_column = 1
	FOR XML PATH('')) AS idxinccolinfo(idxinccolname)
WHERE
			(ius.database_id = DB_ID() OR ius.database_id IS NULL) 
	AND (ips.database_id = DB_ID() OR ius.database_id IS NULL)
	AND	so.schema_id <> SCHEMA_ID('sys')
	AND	ips.object_id	= si.object_id 
	AND ips.index_id	= si.index_id 
	AND ips.object_id	= so.object_id
	AND ips.object_id	= ios.object_id
	AND ips.index_id	= ios.index_id
	AND ips.partition_number = ios.partition_number
ORDER BY
	Table_name,
	name,
	page_count

出力結果は次のようになります。