C# Programming

インデックス

クラスタ化インデックス、非クラスタ化インデックスを理解して、正しく設定しておかないと、パフォーマンスが出なくなってしまいます。

１．目次
２．インデックスの種類
　２．１　クラスタ化インデックス
　２．２　非クラスタ化インデックス
　２．３　非クラスタ化インデックスの効果
３．CREATE INDEX 構文
４．インデックスの削除
５．インデックス情報の取得
６．インデックスの再構築
７．インデックスのチューニング

種類	説明
クラスタ化 インデックス	テーブル内のデータを指定の順序で物理的に並べ替える。クラスタ化インデックスはテーブルに１つだけ。 主キーをセットすると、UNIQUEな主キーインデックス（デフォルトでクラスタ化インデックス）が作成される。 単純インデックス・・・１つの列 複合インデックス・・・複数の列
非クラスタ化 インデックス	テーブル内の特定のレコードへのポインタが生成される。

例

USE pubs
GO
IF EXISTS(SELECT name from sysindexes where name = 'ix_discount')
BEGIN
print 'インデックスix_discountを削除'
Drop INDEX discounts.ix_discount
END
GO

CREATE UNIQUE CLUSTERED INDEX ix_discount
ON dbo.discounts(discount)
GO

SELECT * FROM discounts
SELECT name from sysindexes

クラスタ化インデックスを作成したほうが良い場合
種類	例
異なる値を多数含んでいる列	ID, 郵便番号
範囲クエリ
逐次アクセスされる列
大きな結果セットを返すクエリ
結合または GROUP BY 句を含むクエリが頻繁にアクセスする列	このような列は普通は外部キー列です。ORDER BY 句または GROUP BY 句の中で指定された列にインデックスがある場合は、行が既に並べ替えられているので、SQL Server がデータを並べ替える必要がありません。このため、クエリ パフォーマンスが向上します。
主キーによる高速の単一行ルックアップが必要な OLTP 型のアプリケーション。	主キーにクラスタ化インデックスを作成してください。

　

クラスタ化インデックスを作成しないほうが良い場合
種類	例
頻繁に変更される列	各行のデータ値を物理的順序で配列しておく必要があるので、データが変更されると行全体が再配置されることになる。
広範なキー	クラスタ化インデックスからのキー値は、すべての非クラスタ化インデックスで照合キーとして使用されます。したがって、非クラスタ化インデックスの各リーフ エントリに格納されます。

例

USE pubs
GO
IF EXISTS(SELECT name from sysindexes where name = 'ix_sales')
BEGIN
print 'インデックス ix_sales　を削除'
Drop INDEX sales.ix_sales
END
GO

CREATE NONCLUSTERED INDEX ix_sales
ON dbo.sales(ord_date)
GO

SELECT * FROM sales
WHERE sales.ord_date > '1994-01-01'
GO

非クラスタ化インデックスを作成したほうが良い場合
種類	例
完全一致クエリ	WHERE name = 'abc'
範囲クエリ	WHERE val BETWEEN 9 and 12
挿入、更新	PRIMARY KEY 制約と UNIQUE 制約が定義されていること。
2 つのテーブル間の一致行を検索	SELECT titles.title AS Expr1, titles.price, sales.qty, sales.ord_date FROM sales INNER JOIN titles ON sales.title_id = titles.title_id
インデックス ネスト化ループ結合	ネスト化ループ結合は、次がわかりやすい。 MSDN 第 2 回 「クエリの実行方法を理解する」
2 つのテーブル間で一致する行を検索する場合	FOREIGN KEY 制約が定義されている 2 つのテーブル間で一致する行を検索する場合

　

非クラスタ化インデックスを作成しないほうが良い場合
種類	例
テーブルのデータ変更が多い場合	データが変更された場合にインデックスをすべて更新するので、遅くなる。
小さなテーブル	インデックスを作るほうが時間がかかる場合がある。
LIKE %XYZ	LIKE  "abc%" のように特定の文字列で始まる場合はインデックスの効果はるが、LIKE "%xyz" のようにワイルドカード文字で始まる場合には効果無し。
あまり使わない列	リソースを消費するので、あまり使わないインデックスや、１つのテーブルに多くのインデックスを作成しないようにする。
一意な値があまりない	電話帳の目次に「男、女」とあっても意味がない。
特別な型の列	text, image, bit 型の列にはインデックスは作成できない。

何をインデックスとするかは難しいので、インデックス チューニング ウィザードを使用したほうがよい。

pubs テーブルで、ord_date に非クラスタ化インデックスを付加した場合、約５倍高速になっていました。

非クラスタ化 インデックス	見積もり サブツリーコスト	SQL文
無し	0.0376	SELECT * FROM sales WHERE sales.ord_date > '1994-01-01'
有り	0.00652	CREATE NONCLUSTERED INDEX ix_sales ON dbo.sales(ord_date) GO SELECT * FROM sales WHERE sales.ord_date > '1994-01-01' GO

フラグメント（断片化）した場合に、インデックスの再構築をする必要が出てきますが、いくつか方法があります。

フラグメントしているかどうかに関しては、DBCC SHOWCONTIG により、指定されたテーブルのデータとインデックスの断片化に関する情報を表示することができます。

種類	例
削除、再作成	単純にインデックスを削除し、再作成します。
DROP EXISTING	CREATE INDEX時に、DROP EXISTING を指定し、既存のインデックスを削除し、再作成する。 CREATE UNIQUE CLUSTERED INDEX ix_discount ON dbo.discounts(discount) WITH DROP_EXISTING
DBCC DBREINDEX	1 つのテーブルの複数のインデックスを 1 回の操作で再構成できる。 ただし、実行中はテーブル全体にロックがかけられてしまうので、注意！ SQL 2005 では、ALTER INDEXを使う。 USE pubs GO DBCC DBREINDEX (sales)　 GO
DBCC INDEXDEFRAG	インデックスがフラグメントした場合の、デフラグを実行する。インデックスのリーフ レベルで最適化する。 ユーザがテーブルをアクセス中にも実行可能。ただし、テーブル全体にわたるフラグメントの解消が行われるわけではない。 SQL 2005 では、ALTER INDEXを使う。 DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)

インデックスのチューニングは難しいので、インデックスチューニングウィザードを使うのがよい。たとえば

USE ZipCode

SELECT 郵便番号.郵便番号 AS Expr1, 県.県名 AS Expr2, 市区.市区名 AS Expr3, 
郵便番号.町名 AS Expr4
FROM 郵便番号 INNER JOIN
県 ON 郵便番号.県ID = 県.県ID INNER JOIN
市区 ON 市区.市区ID = 郵便番号.市区ID
WHERE (郵便番号.郵便番号 > '9700000')

のようなSQL文に対して、チューニングウィザードを適用すると、次のような推奨設定を教えてくれる。

また、その設定でインデックスを作ってくれたり、次のようにスクリプトも自動作成してくれる。

/* Created by: Index Tuning Wizard */
/* 日付: 2005/09/18 */
/* 時刻: 8:41:18 */
/* サーバー名: (LOCAL) */
/* データベース名: ZipCode */
/* ワークロード ファイル名: ...\Zip.sql */


USE [ZipCode] 
go

SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [郵便番号3] ON 
[dbo].[郵便番号] ([郵便番号] ASC, [県ID] ASC, [市区ID] ASC, [町名] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION