SQL Server 2008の注目機能、フィルタ選択されたインデックス

文:Tim Chapman(TechRepublic) 翻訳校正:石橋啓一郎
2009-07-24 08:00:00
  • このエントリーをはてなブックマークに追加

 フィルタ選択されたインデックスは、SQL Server 2008の素晴らしい機能であり、データのサブセットに対するインデックスを定義することを可能にしてくれる。

 フィルタ選択されたインデックスは、SQL Serverのテーブルオブジェクト中にある適切に定義されたデータのサブセットに対して作成された非クラスタ化インデックスだ。「適切に定義された」とは、そのデータのサブセットに、クエリ条件を満たすレコードがすべて含まれているということを意味している。例えば、テーブルのあるフィールドの内容がほとんどがNULL値であるような場合、NULL値ではない値だけを含むフィルタ選択されたインデックスを作成することで、便利になる可能性がある。フィルタでクラスタ化インデックスを定義することはできないことに注意して欲しい。

フィルタ選択されたインデックスを使う理由

 フィルタ選択されたインデックスを使って、性能上の利点を得ることができるのは、クエリのほとんどがデータの特定のサブセットに対するフィルタリングを行っているような場合だ。フィルタ選択されたインデックスはフィールド全体に対するインデックスよりもずっと小さい可能性が高く、インデックスに必要なストレージも小さくなる。また、フィルタ選択されたインデックスは、維持にかかるコストも少なくて済む。フィルタ選択されたインデックスはサイズが小さいため、データ操作もインデックスの一部分にしか影響せず、データベースへの入出力の面から見ると、これらの操作のコストは小さくなる。

フィルタ選択されたインデックスの作成

 では、フィルタ選択されたインデックスの作り方と、それを利用することによって性能がどう向上するかについて見てみよう。まず、次のスクリプトを実行して、SalesHistoryテーブルを作成し、そのテーブルの中身を埋める。

IF OBJECT_ID(’SalesHistory’, ‘U’) IS NOT NULL
DROP TABLE SalesHistory
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1),
Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL,
CONSTRAINT PK_SalesHistory_SaleID PRIMARY KEY CLUSTERED (SaleID ASC)
)
GO
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1
WHILE (@i <=5000)
BEGIN
INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES (’Computer’, DATEADD(ww, @i, ‘3/11/1919′),
DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES(’BigScreen’, DATEADD(ww, @i, ‘3/11/1927′),
DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES(’PoolTable’, DATEADD(ww, @i, ‘3/11/1908′),
DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
COMMIT TRANSACTION
GO

 このSalesHistoryテーブルのレコードに対し、テーブル中の7件ごとに6件のSalesDateをNULLにアップデートすることにする。これによって、SalesDateフィールドにはまばらにしか値が存在しない状態になる。このアップデートを行った後、SalesDateフィールドに対し通常の非クラスタ化インデックスを作成する。

UPDATE SalesHistory
SET SaleDate = NULL
WHERE (SaleID % 7) > 0
GO
CREATE INDEX idx_SalesHistory_SaleDate
ON SalesHistory(SaleDate)

 次に以下のコマンドを実行し、I/O統計をオンにする。これによって、TSQLコマンドごとのI/Oの統計値を見ることができるようになる。

SET STATISTICS IO ON

 次のクエリは、SalesHistoryテーブルの中の、SalesDateに値のあるすべてのレコードを返す。このクエリでは、上で作成したidx_SalesHistory_SaleDateインデックスが使われており、Index Seekを使用して2142件のレコードを返す。このクエリで該当するレコードを返すには、データベースからの8回の論理読み込みを必要とする。

 この記事中で扱っているクエリは、結果セットの中のSaleDateフィールドだけを返すようになっていることに注意して欲しい。SELECTリストから他のフィールドを除外した理由は、それによって実行プランが変わってしまうためだ。ここでは、本記事の目的に合わせて、私が条件を設定したフィールドだけを返すことにした。

SELECT SaleDate
FROM SalesHistory
WHERE SaleDate IS NOT NULL
  • 新着記事
  • 特集
  • ブログ