SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

検索条件を指定した件数取得の最適化について考えてみる

leave a comment

以前、クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由 という投稿を書きました。

クラスター化インデックスのみのテーブルに対して、件数取得をした場合、テーブルスキャンが発生し、全権取得が行われるため、非クラスター化インデックスを設定、または、sys.dm_os_partition_stats から件数を取得したほうが効率的です。

前回の投稿では、テーブルの全件数を取得していましたので、今回の投稿では、単一のテーブルに対して特定の条件にマッチする件数の取得の最適化について考えてみたいと思います。

今回は通常の行ストアを対象にしており、列ストアの場合は考え方が変わってきます。

基本的なアプローチは冒頭の投稿と同じです。
検索を効率的に行うためには [読み取られるページ数を抑えるにはどうするか] を考えます。
検索が 1 ページの読み取りで完了するのであれば、8KB の I/O で済みますが、1,000 ページ読み取りをしないといけないのであれば、8MB の I/O が発生することになります。

今回は以下のようなテーブルを例にして考えてみます。

image

このテーブルに対して 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] が行われます。

image

以下のクエリで、キャッシュの状態を確認してみると、テーブルスキャンにより全件キャッシュされていることが確認できます。

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

image

このクエリを最適化するためにはどうすればよいかですが、上記の実行プランにも表示されているように、検索項目に対してインデックスを設定します。

Index Seek だけで完了するのであれば、そのクエリはインデックスの読み込みのみになりますので、読み取り時の I/O は削減されます。

# データ変更時のコストは上がりますのでトレードオフではありますが。

CREATE NONCLUSTERED INDEX IX_Table_1_Col3 
ON dbo.Table_1

 

この状態でクエリを実行すれば Index Seek になりますので読み取りに必要となるページ数は大幅に削減されます。

image

image

さらにページ数を削減したい場合には、[データ圧縮] [フィルタされたインデックス] の利用も検討するとよいかと思います。

今回は数字のですので効果は薄いですが、年月日 のような項目で検索し、その日付範囲が特定の年度に集中する (今年度や当月) 場合には、その範囲にのみインデックスを付与すれば、変更時の 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 の読み込み数が減っていることが確認できます。

image

以下のデータはインデックスで使用されている領域となります。

image

  • Index Id : 3 = 通常の非クラスター化インデックス
  • Index Id : 4 = フィルタされたインデックス
  • Index id : 5 = フィルタされたインデックス + ページ圧縮

となり、ページの使用量 (in_row_used_page_count) が減っていることが確認できます。

検索の基本的な考えとしては読み取るページ数を抑える (そのためにインデックス等で検索を効率化する) になるかと思いますので、そのためにどのようなアプローチが取れるかはいろいろと意識しておきたいですね。

Written by masayuki.ozawa

2月 24th, 2014 at 8:23 am

Posted in SQL Server

Tagged with

Leave a Reply

*