INDEX-onlyとかカバリングインデックスとか

ちょっと前までT字形で出てくるINDEX-only(http://www.sdi-net.co.jp/sdi_228.htm)ってよくわかっていなかったんですが、MSの説明だとクエリをカバーするインデックス（カバリングインデックス）とか包括するクエリとかいうのにに相当するみたいです。ああ、そういうことなんだーと思いました。
http://www.microsoft.com/japan/msdn/sqlserver/columns/sysbuild/sysbuild3.aspx#sysbuild3-6
http://msdn2.microsoft.com/ja-jp/library/ms179325.aspx

T字型のページにもMSのページにも単一テーブルへのクエリの例がのっていますが、JOINしたときの例がないので試してみます。
EmployeeテーブルとDepartmentテーブルとこれらの関連テーブルとしてBelongToを用意して、BelongToテーブルにインデックスをつけたりつけなかったりしてみます。
（テーブルの構造はすらすらと手が動くようになるSQL書き方ドリルからインスパイアされました）

カバリングインデックスだけに着目したいので今回はクラスタ化インデックスはすべてデフォルトのまま主キーにつけときます。

まずテーブルの定義とサンプルデータを作成します。

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BelongTo]') AND type in (N'U'))
DROP TABLE BelongTo 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE Employee 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE Department
GO

CREATE TABLE [Employee] 
(
	[EmployeeID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[EmployeeNo] nvarchar(32) NOT NULL
)

CREATE TABLE [Department] 
(
	[DepartmentID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[DepartmentNo] nvarchar(32) NOT NULL
)

CREATE TABLE [BelongTo] 
(
	[BelongToID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[EmployeeID] int NOT NULL,
	[DepartmentID] int NOT NULL,
	[StartDate] datetime,
	[EndDate] datetime
)

ALTER TABLE [BelongTo]  WITH CHECK ADD CONSTRAINT [FK_BelongTo_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [Employee] ([EmployeeID])

ALTER TABLE [BelongTo]  WITH CHECK ADD CONSTRAINT [FK_BelongTo_Department] FOREIGN KEY([DepartmentID])
REFERENCES [Department] ([DepartmentID])
GO

--サンプルデータの作成
SET NOCOUNT ON
DECLARE @i int
DECLARE @j int
DECLARE @EmployeeID int
DECLARE @DepartmentID int
SET @i = 0
SET @j = 0

WHILE (@i < 10)
BEGIN
	INSERT INTO Department ([DepartmentNo]) VALUES (N'Department' + CAST(@i as varchar))
	SET @DepartmentID = SCOPE_IDENTITY()
	WHILE (@j < 100)
	BEGIN
		INSERT INTO Employee ([EmployeeNo]) VALUES (N'Employee' + CAST(@i as varchar) + '-'+ CAST(@j as varchar))
		SET @EmployeeID = SCOPE_IDENTITY()
		INSERT INTO BelongTo (EmployeeID, DepartmentID, StartDate) VALUES (@EmployeeID, @DepartmentID, GetDate())		
		SET @j = @j + 1		
	END
	SET @i = @i + 1
	SET @j = 0
END
GO

実行するクエリは次のものにします。

SELECT 
	Department.DepartmentNo
FROM 
	Employee
	INNER JOIN 
	BelongTo
	ON (Employee.EmployeeID  = BelongTo.EmployeeID)
	INNER JOIN
	Department
	ON (BelongTo.DepartmentID = Department.DepartmentID) 
WHERE
	Employee.EmployeeNo = 'Employee8-45'

3パターン試してみます。

パターン1：何のインデックスをつけない場合
実行計画は次のようになりました。インデックスがないのでHash Joinが使われたようです。

  |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[BelongTo].[DepartmentID]))
       |--Hash Match(Inner Join, HASH:([sql].[dbo].[Employee].[EmployeeID])=([sql].[dbo].[BelongTo].[EmployeeID]))
       |    |--Clustered Index Scan(OBJECT:([sql].[dbo].[Employee].[PK__Employee__02925FBF]), WHERE:([sql].[dbo].[Employee].[EmployeeNo]=N'Employee8-45'))
       |    |--Clustered Index Scan(OBJECT:([sql].[dbo].[BelongTo].[PK__BelongTo__0662F0A3]))
       |--Clustered Index Seek(OBJECT:([sql].[dbo].[Department].[PK__Department__047AA831]), SEEK:([sql].[dbo].[Department].[DepartmentID]=[sql].[dbo].[BelongTo].[DepartmentID]) ORDERED FORWARD)

パターン2:BelongToのEmployeeIDにインデックスをつけた場合
インデックをつけてクエリを実行します。

CREATE NONCLUSTERED INDEX [IDX_BelongTo_EmployeeID] ON [BelongTo] 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

実行計画は次のようになりました。

  |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[BelongTo].[DepartmentID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[BelongTo].[BelongTo]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[Employee].[EmployeeID]))
       |    |    |--Clustered Index Scan(OBJECT:([sql].[dbo].[Employee].[PK__Employee__5C6CB6D7]), WHERE:([sql].[dbo].[Employee].[EmployeeNo]=N'Employee8-45'))
       |    |    |--Index Seek(OBJECT:([sql].[dbo].[BelongTo].[IDX_BelongTo_EmployeeID]), SEEK:([sql].[dbo].[BelongTo].[EmployeeID]=[sql].[dbo].[Employee].[EmployeeID]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([sql].[dbo].[BelongTo].[PK__BelongTo__603D47BB]), SEEK:([sql].[dbo].[BelongTo].[BelongTo]=[sql].[dbo].[BelongTo].[BelongTo]) LOOKUP ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([sql].[dbo].[Department].[PK__Department__5E54FF49]), SEEK:([sql].[dbo].[Department].[DepartmentID]=[sql].[dbo].[BelongTo].[DepartmentID]) ORDERED FORWARD)

パターン3:BelongToにEmployeeIDとDepartmentIDの複合インデックス（DepartmentIDをカバーするインデックス）をつけた場合
インデックをつけてクエリを実行します。

CREATE UNIQUE NONCLUSTERED INDEX [IDX_BelongTo_EmployeeID_DepartmentID] ON [BelongTo] 
(
	[EmployeeID] ASC,
	[DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

実行計画は次のようになりました。

  |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[BelongTo].[DepartmentID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([sql].[dbo].[Employee].[EmployeeID]))
       |    |--Clustered Index Scan(OBJECT:([sql].[dbo].[Employee].[PK__Employee__4D2A7347]), WHERE:([sql].[dbo].[Employee].[EmployeeNo]=N'Employee8-45'))
       |    |--Index Seek(OBJECT:([sql].[dbo].[BelongTo].[IDX_BelongTo_EmployeeID_DepartmentID]), SEEK:([sql].[dbo].[BelongTo].[EmployeeID]=[sql].[dbo].[Employee].[EmployeeID]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([sql].[dbo].[Department].[PK__Department__4F12BBB9]), SEEK:([sql].[dbo].[Department].[DepartmentID]=[sql].[dbo].[BelongTo].[DepartmentID]) ORDERED FORWARD)

パターン2と3の違いをみてみます。パターン2ではBelongToテーブルに対するClustered Index Seekがありますがパターン3ではないです。パターン3ではインデックスIDX_BelongTo_EmployeeID_DepartmentIDにアクセスするだけでDepartmentIDがわかるのでBelongToテーブルのデータページにアクセスする必要（BelongToテーブルに対してClustered Index Seekする必要）がなかったということですね、たぶん。

ちなみにサブツリーの推定コストはそれぞれ次のようになりました。

• パターン1: 0.0415365
• パタンー2: 0.0184275
• パターン3: 0.0151403

この例ではあまり差がないですけど、もっとデータが多くなって結合するテーブルも増えれば差がはっきりしそうな気がします。