以前、クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由 という投稿を書きました。
クラスター化インデックスのみが設定されているテーブルに対しての COUNT(*) はテーブルスキャンが発生してしまうため、非クラスター化インデックスを設定して、非クラスター化インデックススキャンまたは、sys.dm_db_partition_stats を使用してテーブル全体の件数 (概算件数) を取得するという内容です。
この投稿はテーブル全体の件数を取得する際の方法でしたので、今回の投稿では WHERE で条件を絞った場合の件数取得について少し考えてみようかと。
SQL Database に 50GB 程度データを格納したテーブルがあります。
このテーブルに以下のようなクエリを実行してみます。
SELECT COUNT(*) FROM sample_table WHERE c3 >= '2000-01-01' AND c3 < '2001-01-01'
現状はクラスター化インデックスしか設定されていないため、[Clustered Index Scan] (テーブルスキャン) が発生しています。
この時の I/O と処理時間は以下のようになります。
Table ‘sample_table’. Scan count 1, logical reads 6917911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 8719 ms, elapsed time = 8826 ms.
それでは検索に使用している c3 に非クラスター化インデックスを設定してみたいと思います。
CREATE INDEX IX_sample_table ON sample_table (c3) WITH (ONLINE=ON) GO
非クラスター化インデックス追加後の情報がこちらになります。
Table ‘sample_table’. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
非クラスター化インデックスを設定したので、大幅に処理時間が短くなっています。
# データ圧縮をするとさらに論理読み込み数が減るかと。
さらに非クラスター化インデックスを効率よく使用するためには、以下のようなフィルタされたインデックスを設定するとよいのかと。
CREATE INDEX IX_sample_table_CY2000 ON sample_table (c3)WHERE c3 >= '2000-01-01' AND c3 < '2001-01-01' WITH (ONLINE=ON) GO
Table ‘sample_table’. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
使っているデータの分布がいまいちなので、8KB リードしか減っていませんが、うまく検索範囲が制御できるならフィルタされたインデックスを使ってみてもよいかもしれないですね。