今回は完全一致、部分検索（Where句で範囲を指定）についてNon-Clustered Indexの複合Index、付加Indexの作成と効果検証を行う。
環境
※前回の投稿と同じ- DB Instance： SQL Server 2017 (14.0.2002.14) Developer
- DB： AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)
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 |
| … | … | … |
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
（スキーマは移る）
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。
USE[SalesDB]
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
GO
- 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;
カバリングインデックスにすることでリーフページからデータを参照することがなくなるので、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のインデックスについてざっくりとまとめてみた付加列インデックスの作成
