今日も元気に(?)付加列インデクスで遊んでみました。
今日はクラスタ化インデクスと、非クラスタ化で付加列のあるインデクスの比較をしてみました。
作成したテーブルは以下のとおりです。
CREATE TABLE table1(
num decimal(8, 0) NOT NULL,
customid decimal(8, 0) NOT NULL,
dt datetime NOT NULL,
[value] varchar(500) NULL
)
また、テーブルに格納したデータは以下のSQLで入れました。
insert into table1
select num,
(select num from ( select top 1 NEWID() as nid , num from generate_series(1,100,1) order by nid ) t),
cast('2007/01/07' as datetime) + num,
REPLICATE('0123456789',50)
from generate_series(1,100000,1)
;
まずは、インデクスなしでデータの全件取得をした時のコストを見てみます。
取得列はnum, customid, dtの3列を対象にしました。
select num, customid, dt from table1 order by dt;
テーブル 'table1'。スキャン回数 1、論理読み取り数 6667、物理読み取り数 0、先行読み取り数 6313、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
次にクラスタインデクスを作成してから全件取得をしてみます。
クラスタインデクスを作成するSQLは以下のものになります。
CREATE CLUSTERED INDEX IX_Test1
ON table1(dt,num,customid)
クラスタ化インデクスの作成後にSQLを実行した結果です。
select num, customid, dt from table1 order by dt;
テーブル 'table1'。スキャン回数 1、論理読み取り数 6692、物理読み取り数 88、先行読み取り数 6588、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
今度は作成したクラスタインデクスを削除した後、
非クラスタ化の付加列インデクスを作成してから全件取得をしてみます。
クラスタインデクスを作成するSQLは以下のものになります。
CREATE INDEX IX_Test1
ON table1(dt)
INCLUDE (num,customid)
非クラスタ化の付加列インデクスの作成後にSQLを実行した結果です。
select num, customid, dt from table1 order by dt;
テーブル 'table1'。スキャン回数 1、論理読み取り数 363、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
全件取得では、クラスタ化インデクスよりも付加列インデクスの方がディスクアクセスが少なく済みました。
これは、クラスタ化インデクスを作成すると、リーフページにレコードデータが格納されているため、
1つのリーフに格納されるデータの件数が非クラスタ化インデクスに比べ少なくなってしまうためです。
今回作成したテーブルには[value]列という大きなバイト数を消費する列があるので、顕著にこの差が出たのだと思います。
インデクスの構造については、SQL Server 2005 Books Online上に記載されている
クラスタ化インデックスの構造と
非クラスタ化インデックスの構造に詳しく載っていますので参照してみてください。
それでは次に発行するSQLに条件を付加して実行してみたいと思います。
条件として、dt列が2007年のものを取得するように指定しました。
まずはクラスタ化インデクスがある状態でSQLを実行してみます。
select num, customid, dt
from table1
where dt between cast('2007/01/01' as datetime) and cast('2007/12/31' as datetime)
order by dt;
テーブル 'table1'。スキャン回数 1、論理読み取先り数 27、物理読み取り数 0、行読み取り数 24、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
次に非クラスタ化で付加列インデクスがある状態でSQLを実行してみます。
select num, customid, dt
from table1
where dt between cast('2007/01/01' as datetime) and cast('2007/12/31' as datetime)
order by dt;
テーブル 'table1'。スキャン回数 1、論理読み取り数 5、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
この場合でも付加列インデクスの方が多少ディスクアクセスが少なく済みました。
テーブルにクラスタ化インデクスを張るときは行長を気にしてあげないといけないということが見えるかと思います。
このページに書かれている情報が
クラスタ化、非クラスタ化(付加列の有、無)でのインデクスをうまく使い分けるヒントになる情報となったらうれしいです。