SQL Server のIndexの効果検証2

前回の投稿で単純なSELECTクエリに対してIndexの効果を検証した。
今回は完全一致、部分検索（Where句で範囲を指定）についてNon-Clustered Indexの複合Index、付加Indexの作成と効果検証を行う。

環境

※前回の投稿と同じ

• DB Instance： SQL Server 2017 (14.0.2002.14) Developer
• DB： AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)

検証対象のTable： SalesLT.Product
Base Query:

SELECT ProductID, ProductNumber, Color 
FROM SalesLT.Product

ProductID	ProductNumber	Color
680	FR-R92B-58	Black
706	FR-R92R-58	Red
707	HL-U509-R	Red
708	HL-U509	Black
709	SO-B909-M	White
710	SO-B909-L	White
…	…	…

計：295行
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
（スキーマは移る）
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。

USE[SalesDB]

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
GO

テストするNon-Clustered Indexの種類は以下の通り

• ProductIDのみ（前回テスト済）
• 複合Index：ProductID, ProductNumber, Color の3列を複合Indexとして設定
• 付加列Index：ProductIDをIndex Keyとして設定し、付加列としてProductNumber, Color を設定

テスト1（完全一致）

以下の完全一致クエリ（結果セットが1行）に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

統計情報の結果

STATISTICS IO	ProductIDのみ	複合Index	付加列Index
スキャン回数	0	1	0
論理読み取り数	3	2	2
物理読み取り数	1	0	0
先行読み取り数	0	0	0

実行プランのコスト比較

	ProductIDのみ	複合Index	付加列Index
完全一致（1行）	0.0065704	0.0032831	0.0032831

• 複合Indexと付加列Indexではコストが同じ
• ProductIDのみのIndexに比べコスト比：50%

メモ

• 複合Indexと付加列Indexでコストが同じになる。（今回の例では？）
• 付加列Indexの方がスキャン回数が少なくなるので規模が大きくなると有利か。
• 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

テスト2（部分検索）

以下の部分検索クエリ（結果セットが5行）に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID Between 710 AND 714;

統計情報の結果

STATISTICS IO	ProductIDのみ	複合Index	付加列Index
スキャン回数	1	1	1
論理読み取り数	7	2	2
物理読み取り数	1	0	0
先行読み取り数	0	0	0

実行プランのコスト比較

	ProductIDのみ	複合Index	付加列Index
部分検索（5行）	0.0164145	0.0032875	0.0032875

• 複合Indexと付加列Indexではコストが同じ
• ProductIDのみのIndexに比べコスト比：20%

メモ

• 完全一致の場合と同じく複合Indexと付加列Indexでコストが同じになる。（今回の例では？）
• 完全一致より部分検索の方が複合Index、付加列Indexの効果が大きい。
• 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

まとめ

今回の事例では、パフォーマンス的に複合Indexと付加列Indexは同等になっている。Indexの構成を考えると、付加列Indexの方がIndexサイズが小さく出来るので付加列Indexの方がよいのかと思う。
今回の事例でIndexのサイズをシステムストアドプロシージャ「sp_spaceused」で計算すると、どちらも 1040KBとなってしまう…
（テーブルのサイズが小さいことが要因か？）
・Indexのサイズ計算（sp_spaceused）

DECLARE @Object_Name nvarchar(1000) = '';
USE SalesDB  
SET @Object_Name =  N'SalesLT.Product';

EXEC sp_spaceused @Object_Name;

今回の事例のようにSELECT文の結果セットを包含するIndexのことを「カバリングインデックス」と呼ぶ。
カバリングインデックスにすることでリーフページからデータを参照することがなくなるので、RID Lookupが削減できるということらしい。
実際、上記Index（ProductID, ProductNumber, Color）以外の列をSELECT文に追加するとパフォーマンスが悪化する。（下図参照）

結果セットを増やした場合

SELECT  ProductID,  ProductNumber, Color, Size,  SellStartDate
FROM  SalesLT.Product
WHERE  ProductID  Between 710 AND 714;

複合IndexでProductID, ProductNumber, Color の3列を設定している状態で、結果セットとして「Size」、「SellStartDate」を追加すると実行プランでは RID Lookupが発生してしまい実質的には「ProductID」のみをNon-Clustered Indexとした場合と同じになる。
複合Index、付加列Indexは上手く設定できればパフォーマンスの向上が図れるが結果セットの作成側（アプリケーション開発側）との調整が必要かと。
また、現実的には単独のTableからの結果セットではなく、複数のTableの結合、集計が組み合わされるのでトータルでどのようにIndexを付与するのが良いのかは難しい問題と思います。
DB管理者側の視点だけでは意味がないので…
（今までちゃんとしてこなかった事への言い訳）

参考

SQLServerのインデックスについてざっくりとまとめてみた
付加列インデックスの作成