パフォーマンス比較；クラスタ化インデックス　vs.　ヒープ

マイクロソフトの植田です。

SQL Server 2005の特徴の一つに、Primary key制約を指定してテーブル作成するとデフォルトでクラスタ化インデックスが作成される点があります。基本的にSQL Server ではクラスタ化インデックスを作成するメリットがあるためこのような動作を行いますが、非クラスタ化インデックスのみのテーブルと比べてどれほどの性能差があるのでしょうか。

今回はこの点についてテストした結果をご紹介させて頂きます。

本内容は米国のTechNetのサイトにて公開されておりますが、ここでは要点をいくつかピックアップして解説したいと思います。

注）本内容に関する詳細は以下のWebサイトで取り扱われております。

https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx

今回の検証の主な目的は以下を明らかにすることでした。

1. クラスタ化インデックスを持つテーブルと、非クラスタ化インデックスを持つテーブルに対してDML（SELECT, INSERT, UPDATE, DELETE）操作を行った時、パフォーマンスにどの程度差が生じるか

2. ID列を持つテーブルにクラスタ化インデックスを設定し、マルチスレッドで行の挿入を行った時、並列スレッド数とパフォーマンスの間にどのような関係があるか

3. 大量の行の挿入／削除を行った時、クラスタ化インデックスを持つテーブルと、非クラスタ化インデックスを持つテーブルとでは、ディスクスペースの使用率（効率性）に違いがあるか

本ブログでは上記の１について説明させていただきます。

クラスタ化インデックスと、非クラスタ化インデックスの大きな違いは、クラスタ化インデックスでは、インデックス・ツリーの最下層の部分（リーフノード）に実際の行のデータが収められているデータ・ページ（行データはインデックスキーの順序でソートされている）がリンクされるのに対し、非クラスタ化インデックスでは、インデックス・ツリーのリーフノードにインデックスキーが存在する行の行IDが格納されていて、実際の行データはデータ・ページ上でランダムに配置されている（インデックスキーの順序に沿って並んでいるわけではない）点です。両者の構造の詳細についてはSQL Server 2005のBook Onlineをご参照ください。

クラスタ化インデックスの構造：https://msdn2.microsoft.com/ja-jp/library/ms177443.aspx

非クラスタ化インデックスの構造：https://msdn2.microsoft.com/ja-jp/library/ms177484.aspx

テスト環境

テストでは以下のテーブルを使用しました。

CREATE TABLE Tab1

(

ORG_KEY BIGINT,

PROD_KEY BIGINT,

TIME_KEY BIGINT,

CST_NON FLOAT,

CST_RPL FLOAT,

RTL_NON FLOAT,

RTL_RPL FLOAT,

UNT_NON FLOAT,

UNT_RPL FLOAT,

UPDATE_DATE DATETIME

)

インデックス作成時のT-SQL文は以下の通りです。

CREATE CLUSTERED INDEX c_idx1 ON Tab1 (ORG_KEY, PROD_KEY, TIME_KEY)

CREATE INDEX nc_idx1 ON Tab1 (ORG_KEY, PROD_KEY, TIME_KEY)

テーブル、および、インデックスのサイズは以下の表のようになっています。

テーブル／インデックス	行数	ページ数	サイズ（MB）
クラスタ化インデックス	16,125,000	181,992	1,421
非クラスタ化インデックス	16,125,000	75,708	591
ヒープ	16,125,000	181,182	1,415

表１：テーブル／インデックスサイズ

注)ヒープとはクラスタ化インデックスが存在しないテーブルです。クラスタ化インデックスではデータ・ページがインデックスのツリー構造に含まれるためテーブルとインデックスを明確に区別していません。

ハードウェア、および、ソフトウェアのテスト環境は以下の通りです。

Server:　HP DL385, AMD Opteron 280 (2.4Ghz Dual Core) x 2, 8GM RAM

Storage: HP StorageWorks Modular Smart Array 30

OS: Windows 2003 Server Enterprise Edition (R2)

SQL Server: SQL Server 2005 Enterprise Edition for x86 with SP1

テスト方法

今回のテストでは以下の方法でパフォーマンスを計測しました。

No.	概要
1	INSERT Performance ランダムに値を作成した1,000,000行のデータをテーブルに挿入し、単位時間当たりの平均処理速度を測定します。1回のINSERTで1行挿入するため、INSERT文を1,000,000回処理して平均を求めます。 サンプルT-SQL文は以下です。 INSERT INTO Tab1(ORG_KEY, PROD_KEY, TIME_KEY, UPDATE_DATE) VALUES (9717, 46273, 206, ‘2007-02-01 00:00:00:000’)
2	UPDATE Performance テーブル中のランダムに選んだ1,000,000行のデータを更新し、単位時間当たりの平均処理速度を測定します。1回のUPDATEで1行更新するため、UPDATE文を1,000,000回処理して平均を求めます。 サンプルT-SQL文は以下です。 UPDATE Tab1 SET UPDATE_DATE=‘2007-02-01 00:00:00:000’ WHERE ORG_KEY=14517 AND PROD_KEY=19417 AND TIME_KEY=225
3	DELETE Performance テーブル中のランダムに選んだ1,000,000行のデータを削除し、単位時間当たりの平均処理速度を測定します。1回のDELETEで1行削除するため、DELETE文を1,000,000回処理して平均を求めます。 サンプルT-SQL文は以下です。 DELETE Tab1 WHERE ORG_KEY=5718 AND PROD_KEY=18193 AND TIME_KEY=52
4	SELECT Performance a. テーブル中のランダムに選んだ1,000,000行のデータを抽出し、単位時間当たりの平均処理速度を測定します。1回のSELECTで1行選択するため、SELECT文を1,000,000回処理して平均を求めます。サンプルT-SQL文は以下です。SELECT * FROM Tab1 WHERE ORG_KEY=2717 AND PROD_KEY=15385 AND TIME_KEY=3 b. テーブル中であるインデックスキーの範囲にあるデータを抽出し、その処理を1,000,000回行って単位時間当たりの平均処理速度を測定します。1回のSELECTで選択される行数は258行でした。サンプルT-SQL文は以下です。SELECT * FROM Tab1 WHERE (ORG_KEY=117 OR ORG_KEY=118 OR ORG_KEY=119) AND PROD_KEY=45505

表２：テスト概要

上記のDML操作を行うためのクライアントとして、ODBCライブラリを使用してデータベースにアクセスする簡単なプログラムを用意しました。

テスト手順

パフォーマンス計測の手順は以下の通りです。

1． バックアップより新規作成したテーブルにデータをロードする

2． テーブルにクラスタ化インデックスを作成する

3． クライアントプログラムを走らせ、パフォーマンス測定を行う

4． テーブルを削除、再作成しバックアップよりデータのロードを行う

5． テーブルに非クラスタ化インデックスを作成する

6． クライアントプログラムを走らせ、3と同じDMLについてパフォーマンス測定を行う

7． 1－6を表2の全てのテストケースについて行う

テスト結果

今回得られた結果は以下の通りでした。

テストケース	クラスタ化インデックス付テーブル	非クラスタ化インデックス付テーブル（ヒープ）	差（％）
Test 1: INSERT Throughput (rows/sec)	4653	4515	-3.0
Test 2: UPDATE Throughput (rows/sec)	4683	4330	-7.5
Test 3: DELETE Throughput (rows/sec)	4610	3896	-15.5
Test 4-a: SELECT(row-by-row) Throughput (rows/sec)	4136	3564	-13.8
Test 4-b: SELECT(range scan) Throughput (selects/sec)	587	454	-22.7

結果の考察

このようにすべてのDML操作においてクラスタ化インデックス付テーブルの方が、非クラスタ化インデックス付テーブル（ヒープ）よりもパフォーマンスが良いという結果になりました。これらの差は、WHERE句で指定された行を検索する際に、クラスタ化インデックス付テーブルの場合は、インデックスを走査してそのまま行が格納されているデータ・ページにたどり着くのに対し、ヒープでは非クラスタ化インデックスを走査して行IDを取得し、その後データ・ページへアクセスする必要があるために生じていると考えられます。また、一定範囲に対するSELECT（Test 4-b）において特に顕著な差が生じているのは、このテストケースにおいて、（インデックスキーの順序でデータが並んでいる）クラスタ化インデックス付テーブルの利点が最も有効に活用されているためと考えられます。また、通常インデックスのメンテナンスコストが懸念されるINSERT処理においても、クラスタ化インデックス付テーブルにおけるパフォーマンスがヒープにおけるパフォーマンスよりも良い、という結果になりました。その理由は、クラスタ化インデックスのメンテナンスのコストがヒープにおけるデータページアクセスのコストよりも低かったためと考えられます。ただし、両者のコストはインデックスキーに指定する列のデータサイズ等に依存し、今回の検証結果においてもわずかな差しかないことを考慮すると、環境によっては逆の結果になる可能性もあります。

結論

1千万行程度のテーブルにおいて、クラスタ化インデックスを作成した場合と非クラスタ化インデックスを作成した場合でDML操作のパフォーマンスにどのような違いが生じるかを検証しました。その結果、すべてのDML操作においてクラスタ化インデックス付テーブルに対するパフォーマンスが、非クラスタ化インデックス付テーブルに対するパフォーマンスを上回ることを観測しました。

上記の結果は今回のテスト環境、および、テスト方法において得られたもので、他の環境やテスト方法において同様の結果が出るとは限りません。上記の結果はSQL Server 2005においてデータベースを設計する際の、基礎的なデータとして利用されることをお勧めします。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。