SE の雑記

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

クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由

leave a comment

テーブルのデータの件数を取得する際に [SELECT COUNT(*) FROM <テーブル名>] というクエリを実行することがあるかと思います。

今回の投稿ではクラスター化インデックスのみのテーブルでこの方法を使用して件数を取得しないほうがよい理由についてまとめてみたいと思います。

■実行プランから確認をする


クラスター化インデックスのみが設定されている以下のテーブルがあります。
image
image

このテーブルに対して [SELECT COUNT(*) FROM Table_1] を実行してみます。

image

この時の実行プランですが [Clustered Index Scan] となっています。
テーブルに対しての [全件走査] が行われている状態ですね。

今回は 50 万件のデータが入った状態にしています。
このテーブルに対して、SELECT COUNT(*) を実行した際のバッファキャッシュ (データのキャッシュ) の状態がこちらになります。
image

585MB のデータがメモリ上に読み込まれているのが確認できます。

単純なテーブルのレコードでデータサイズを考えると、
50 万件× (16 (uniqueidentifier) + 4 (int) + 1000 (char(1000)) = 510,000,000 バイト = 510 MB
となりますので SELECT COUNT(*) をすることで全件走査が行われテーブルのレコードがすべてメモリ上に読み込まれたことになります。

SELECT COUNT(*) ではなく、SELECT COUNT(Col1) にした場合はどうなるでしょう。
この場合の実行プランは SELECT COUNT(*) と同じになります。
image

クラスター化インデックスしか設定されていないテーブルでは、どの列のデータを取得するにしても、Clusterd Index Scan または、Clusterd Index Seek のどちらかでデータを取得する必要があります。

この際の SQL Server のデータの取得方法を考えてみます。
SQL Server はデータをディスクから取得する際には [ページ] を基本としてデータの取得を行います。

そのため、クラスター化インデックスのみのテーブルでは

SELECT Col1 FROM Table_1
SELECT * FROM Table_1

のどちらでも取得されるページ数に変わりはありません。
結果セットを返す際には取得対象とした列を返しますので、上記のクエリはどちらでもディスクから読み取るデータ量は変わりませんが、実行した端末に返すデータ量は列数によって変わりますので不要な列は取得しないのが望ましいです。

それではこのテーブルの Col2 に対して非クラスタ化インデックスを設定して SELECT COUNT(*) を実行してみます。
image

この場合はIndex Scan (非クラスター化インデックススキャン) となります。
メモリの使用状況は以下のようになっています。
image

クラスター化インデックスと非クラスター化インデックスのスキャンでは非クラスター化インデックスのスキャンのほうが読み取るページ数は少なくなり、メモリの使用量を抑えることができます。

SELECT COUNT(*) する場合は非クラスター化インデックスがあったほうが効率的になります。

ただし、この場合の実行プランも Scan になっていますので対象の非クラスター化インデックスを全件走査することになります。

単純なデータ件数の取得であれば、[sys.dm_os_partition_stats] の [row_count] を確認したほうが効率が良いです。
image

移行後のデータ件数確認等にはこちらの DMV が効果的かと。

SELECT COUNT(*) でデータを読んだほうが良い場合の例としてはデータをメモリ上にキャッシュさせたい場合が挙げられます。
データをキャッシュ目的で SELECT する場合は大量のページを読みかつ結果セットを抑えてデータを取得するのが効率的です。
そのため、SELECT COUNT(*) で結果セットとしては件数のみを返すようにして全件走査を行わせデータをキャッシュします。
クラスター化インデックスと非クラスター化インデックスの両方が設定されているテーブルの場合はインデックスヒントを使用してどちらのインデックスを使用するかを指定すると想定していたデータのキャッシュができると思います。
image

シンプルなデータの取得でも奥が深いですね~。

Written by masayuki.ozawa

3月 14th, 2012 at 10:14 pm

Posted in SQL Server

Tagged with

Leave a Reply

*