SQL Server のIndexの効果検証1

SQL Server のIndexの設定有無によるSELECT文の実行プランを比較してみる。

環境

• 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

テスト１（完全一致）

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

USE[SalesDB]
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	0	0
論理読み取り数	101	2	3
物理読み取り数	0	0	1
先行読み取り数	101	0	0

実行プランのコスト比較

	None	Clustered	Non Clustered
1行検索Cost	0.0776806	0.0032831	0.0065704

• Clusterd Index のコスト比：4％
• Non-Clustered Indexのコスト比：8%

メモ

• Index無し(None)の場合はTable Scanが実行されすべてのPage(101)を読み取っている。
• Index有りの場合はCluster Index, Non-Cluster Index 共にIndex Scanが実行されており読取りPage数が非常に少なくなっている。 (Tableの規模が大きくなるほど効果が差が大きくなる)

テスト2（全件検索/OrderBy）

以下の全件検索クエリ（結果セットが295行、OrderBy付き）に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
ORDER BY ProductID

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	1	1
論理読み取り数	101	103	101
物理読み取り数	0	0	0
先行読み取り数	101	0	101

実行プランのコスト比較

	None	Clustered	Non Clustered
全件検索（295行：OrderBy）	0.0928176	0.0776806	0.0928176

• Clusterd Index のコスト比：84％
• Non-Clustered Indexのコスト比：100%

メモ

• Tableの全件検索ではIndex無し(None)とNon-Clustered Indexが同じ結果になる。（Non-Clustered Indexの効果が無い）
• Index無し、Non-Clustered Indexのコストを見るとTable Scan(84%) と Sort(16%)でコストが消費されている。
• Clusterd Index では、リーフページがProductIDでSortされているのでSortのコストがかからない。
• Clusterd Index ではコスト低減の効果があるが、1行検索のような大きな低減効果はない。

テスト3（部分検索）

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

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

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	1	1
論理読み取り数	101	5	7
物理読み取り数	0	0	1
先行読み取り数	101	0	0

実行プランのコスト比較

	None	Clustered	Non Clustered
部分検索（5行）	0.0776806	0.0040282	0.0164145

• Clusterd Index のコスト比：5％
• Non-Clustered Indexのコスト比：21%

メモ

• 部分検索の場合は Non-Clustered Index の効果が得られる。ただし、RID Lookupのコストが高いのでTableの規模が大きくなると悪化すると思われる。

まとめ

単純なSELECTクエリについてIndexの有無によるPage検索数、コストについて調べてました。
現実的にはTableのPrimary Key がClustered Index になってしまうので実運用上のIndexは Non-Clustered で作成する必要がありそうです。
Non-Clustered IndexについてはRID Lookupのコストをどのように下げるかが問題になりそうです。
これについては、複合Index、付加Indexを構築してカバーインデックスにすることで改善されるようです。その辺については別途まとめようと思います。

参考

なぜBTreeがIndexに使われているのか
SQLServerのインデックスについてざっくりとまとめてみた