インデックスの効果が出るケースの検証 #SQL

インデックスとは

目的のレコードを効率よく取得するための方法です。
インデックスは探すためのキーとなる値とレコードの場所を示すポイントでできています。

Windows10
SQL Server2019

この記事では一般的に効果が出ると言われている以下の2ケースを検証します。
・少量データを検索する場合
・インデックスが作られている列のデータの種類が多い場合

・レコード数100万件
・実行前にキャッシュクリア
・インデックスあり・なしの状態をそれぞれ5回ずつ実施して、平均値を比較
・それぞれのデータ数が検索でヒットするようなデータを用意(1件、10件、25件、50件、100件、1000件、10000件)

検索ヒット数	インデックス	1回目	2回目	3回目	4回目	5回目	平均	改善率
1件	無	0.162	0.109	0.109	0.140	0.109	0.1258
10件	無	0.117	0.109	0.113	0.111	0.108	0.1116
25件	無	0.113	0.117	0.130	0.110	0.107	0.1154
50件	無	0.127	0.119	0.113	0.138	0.126	0.1246
100件	無	0.133	0.114	0.131	0.142	0.132	0.1304
1000件	無	0.133	0.131	0.128	0.133	0.137	0.1324
10000件	無	0.156	0.153	0.150	0.144	0.143	0.1492
1件	有	0.038	0.049	0.047	0.045	0.034	0.0426	66.14%
10件	有	0.048	0.048	0.034	0.054	0.050	0.0468	58.06%
25件	有	0.052	0.047	0.054	0.057	0.038	0.0496	57.02%
50件	有	0.060	0.046	0.064	0.049	0.062	0.0562	54.90%
100件	有	0.056	0.052	0.068	0.051	0.055	0.0564	56.75%
1000件	有	0.084	0.059	0.066	0.075	0.064	0.0696	47.43%
10000件	有	0.094	0.104	0.094	0.098	0.113	0.1006	32.57%

・レコード数100万件
・実行前にキャッシュクリア
・インデックスなしとありを5回ずつ実施し、平均値を比較
・それぞれのデータ種類数がヒットするようなデータを用意(1種類、10種類、100種類、1000種類、10000種類、100000種類、1000000種類)

データ種類数	インデックス	1回目	2回目	3回目	4回目	5回目	平均	改善率
1種類　(×1000000件)	無	3.523	3.458	3.600	3.524	3.466	3.514
1種類　(×1000000件)	有	3.259	3.397	3.384	3.381	3.625	3.409	2.99%
10種類　(×100000件)	有	0.395	0.401	0.395	0.388	0.399	0.396	88.74%
100種類　(×10000件)	有	0.102	0.099	0.090	0.102	0.087	0.096	97.27%
1000種類　(×1000件)	有	0.051	0.057	0.053	0.051	0.052	0.053	98.50%
10000種類　(×100件)	有	0.045	0.036	0.072	0.061	0.045	0.052	98.53%
100000種類　(×10件)	有	0.020	0.038	0.036	0.041	0.040	0.035	99.00%
1000000種類　(×1件)	有	0.032	0.039	0.040	0.020	0.024	0.031	99.12%

・大量データを検索する場合よりも、少量データを検索する場合の方が効果が高い
・データ種類が少ない項目よりも、多い項目にインデックスを作成する方が効果が高い
・項目数が10種類から効果がかなり大きくなる