以前、クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由 という投稿を書きました。
クラスター化インデックスのみのテーブルに対して、件数取得をした場合、テーブルスキャンが発生し、全権取得が行われるため、非クラスター化インデックスを設定、または、sys.dm_os_partition_stats から件数を取得したほうが効率的です。
前回の投稿では、テーブルの全件数を取得していましたので、今回の投稿では、単一のテーブルに対して特定の条件にマッチする件数の取得の最適化について考えてみたいと思います。
今回は通常の行ストアを対象にしており、列ストアの場合は考え方が変わってきます。
基本的なアプローチは冒頭の投稿と同じです。
検索を効率的に行うためには [読み取られるページ数を抑えるにはどうするか] を考えます。
検索が 1 ページの読み取りで完了するのであれば、8KB の I/O で済みますが、1,000 ページ読み取りをしないといけないのであれば、8MB の I/O が発生することになります。
今回は以下のようなテーブルを例にして考えてみます。
このテーブルに対して 100,000 件のデータを格納します。
SET NOCOUNT ON Truncate Table Table_1 GO DECLARE @i int = 1 WHILE (@i <= 100000) BEGIN INSERT INTO Table_1 VALUES(@i, @i+1, @i+2, @i+3, NEWID()) SET @i+=1 END
現在はクラスター化インデックスしか設定されていませんので、この状態の件数取得は [Clusterd Index Scan] が行われます。
以下のクエリで、キャッシュの状態を確認してみると、テーブルスキャンにより全件キャッシュされていることが確認できます。
SELECT page_type, COUNT(*) * 8 AS [Page Size (KB)], SUM(row_count) AS [Row Count] FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID(N'TEST') GROUP BY page_type ORDER BY page_type
このクエリを最適化するためにはどうすればよいかですが、上記の実行プランにも表示されているように、検索項目に対してインデックスを設定します。
Index Seek だけで完了するのであれば、そのクエリはインデックスの読み込みのみになりますので、読み取り時の I/O は削減されます。
# データ変更時のコストは上がりますのでトレードオフではありますが。
CREATE NONCLUSTERED INDEX IX_Table_1_Col3 ON dbo.Table_1
この状態でクエリを実行すれば Index Seek になりますので読み取りに必要となるページ数は大幅に削減されます。
さらにページ数を削減したい場合には、[データ圧縮] [フィルタされたインデックス] の利用も検討するとよいかと思います。
今回は数字のですので効果は薄いですが、年月日 のような項目で検索し、その日付範囲が特定の年度に集中する (今年度や当月) 場合には、その範囲にのみインデックスを付与すれば、変更時の I/O の抑制や、ディスク領域の削減にもつながります。
CREATE NONCLUSTERED INDEX IX_Table_1_Col3 ON dbo.Table_1 (Col3) WHERE Col3 >= 0 AND Col3 <= 50000 WITH (DATA_COMPRESSION = PAGE) GO
先ほどと比較して、Index Page の読み込み数が減っていることが確認できます。
以下のデータはインデックスで使用されている領域となります。
- Index Id : 3 = 通常の非クラスター化インデックス
- Index Id : 4 = フィルタされたインデックス
- Index id : 5 = フィルタされたインデックス + ページ圧縮
となり、ページの使用量 (in_row_used_page_count) が減っていることが確認できます。
検索の基本的な考えとしては読み取るページ数を抑える (そのためにインデックス等で検索を効率化する) になるかと思いますので、そのためにどのようなアプローチが取れるかはいろいろと意識しておきたいですね。