背景
サービス成長に伴ってデータは増え続けます。テーブルのレコード数が増えていったときに、「インデックスの構造はどう変わるか」が理解できると「テーブルサイズの変化に伴ってクエリの実行速度はどう変わるか」について予測できるようになるので、説明したいと思います。
レコード数の違いによるインデックス構造の変化を確認
テーブル定義は同じで、レコード数だけが異なるDBを3つ用意します。
次に、各DBでインデックスの再構築を実施し、断片化をできる限り抑えた状態にします。
use MyTuningDB_small
alter index PK_MemberEMail on MemberEMail rebuild
go
use MyTuningDB_middle
alter index PK_MemberEMail on MemberEMail rebuild
go
use MyTuningDB_large
alter index PK_MemberEMail on MemberEMail rebuild
go
そのあと、各DBごとにインデックスの構造を確認します。
DECLARE @OBJECT_ID int
set @OBJECT_ID = OBJECT_ID('MemberEMail')
SELECT
index_id
,index_type_desc
,index_depth
,index_level
,page_count
,record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), @OBJECT_ID, NULL , NULL, 'DETAILED') as A
JOIN sys.objects as B on A.object_id = B.object_id
ORDER BY index_id, index_level
結果は以下のようになりました。
この結果から、以下のことがわかります。
- 1000万単位のレコード数の差でも、ツリーの深さはほぼ同じ(index_depth = 3 or 4)
- リーフノードのページ数(page_count)はレコード数にほぼ比例
レコード数の違いによるクエリ実行速度の変化を確認
Index ScanとIndex Seekの両方で、テーブルのレコード数が増加するに伴って実行時間がどう変化するのかを計測してまとめました。
Index Scanのとき
↑のように、Index Scanでクエリ実行される場合の、テーブルレコード数の変化と実行速度の関係を計測して以下にまとめました。
実行時間まとめ
Index Scanのときのクエリ実行速度変化のポイント
- レコード数が増えるほど「論理読み取り数」が増加
- リーフノードのページ数にほぼ一致
- レコード数が増えるほど如実に実行時間が増加
- 1.5秒→3秒→5秒
Index Seekのとき
↑のように、Index Seekでクエリ実行される場合の、テーブルレコード数の変化と実行速度の関係を計測して以下にまとめました。
実行時間まとめ
Index Seekのときのクエリ実行速度変化のポイント
- レコード数が増えても「論理読み取り数」がほぼ同じ
- インデックスの階層数にほぼ一致
- 故に、レコードが大幅に増加しても実行時間はほぼ同じ
- Index Seekの強力な特徴
まとめ
まず、レコード数増加に伴ってインデックスの構造がどのように変化するのかを確認しました。
1000万単位でレコードが増えても、クエリの階層数（深さ）はほとんど変わらず、一方で、リーフページ数はレコード数に比例して増えていく傾向を確認できました。
次に、Index Scan / Index Seekにおけるレコード数変化が及ぼす実行時間への影響について確認していきました。Index Scanはレコード数が増えていくと実行時間も伸びていくのに対して、Index Seekについてはレコード数が増えても実行時間は増えないことが確認できました。
このことから、データ量増加に伴うクエリ実行時間の変化を推定するときは、「そのクエリの実行はScanなのか、Seekなのか」を理解しておくことが重要だと分かります。
「データ量が増えたから遅くなった/遅くなりそう」ではなく、
- 「データ量が増えてもSeek処理なので速度は変わらないはず」
- 「データ量が増えるとScan処理なので徐々に遅くなって行く懸念がある」
といった推定ができると負荷に関する未来予測の精度が上がると思います。