前回の内容をおさらいしてみる
SQL Server の構造について記事にしてみる - その1 (インデックスの基礎知識) - - 都内で働くSEの技術的なひとりごと では、インデックスの基礎および SQL Server のクラスタ化インデックスと非クラスタ化インデックスについて説明を行いました。
いろいろ確認してみる
実際のテーブルで、Bツリー、クラスタ化インデックス、非クラスタ化インデックスおよび付加列の確認したいと思います。
使用する環境を列挙してみる
Microsot Azure 上で 動作している Windows Server 2012 R2、SQL Server 2014 で動作確認をします。データベースは、AdventureWorks2012 上の Sales.SalesOrderHeader テーブルを使用します。
実際に確認してみる
1. Bツリーがどんな構成かを確認してみる
インデックスがどのように構成されているかを実際に確認してみましょう。SQL Server Mangement Studio で下記のクエリを実行します。実行結果は図1です。
SELECT [テーブル名] = o.name,
[インデックスの深さ] = dips.index_depth,
[インデックスレベル] = dips.index_level,
[インデックスのタイプ] = dips.index_type_desc,
[レコード数] = dips.record_count,
[ページ数] = dips.page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(),
OBJECT_ID('[Sales].[SalesOrderHeader]'),
NULL,
NULL,
'DETAILED') dips
JOIN sys.objects o on o.object_id =
dips.object_id where dips.index_type_desc = 'CLUSTERED INDEX'
図1：SQL Server Management Studio の実行結果
上図の結果から、Bツリー、各階層のノード構成は下記のようになります。
- ルートノードは、index レベルは 2、レコード数は 2、ページ数は 1になります。
- 中間ノードは、index レベルは 1、レコード数は 685、ページ数は 2になります。
- リーフノードは、index レベルは 0、レコード数は 31465、ページ数は 685 になります。
図示すると図2のようになります。
図2：Sales.SalesOrderHeader テーブルのクラスタ化インデックス B-Tree 構造
B+Tree の構成を深く知るには、ページ数、ページ同士の関係などを参照する必要がありますが、結構深い内容になりますので、今回はここまでにしておきます。勘のいい方は上図をみてなんなく構造が分かったりするかも...です。
2. インデックスの種類を確認してみる
オブジェクトカタログビュー であれば、sys.indexes で確認することができます。
Management Studio で下記の SQL を実行してみましょう。実行結果は図3です。
select * from sys.indexes
図3：sys.indexes の結果
SQL Server Management Studio であれば、オブジェクトエクスプローラーから確認することができます。
図4：オブジェクトエクスプローラーでインデックスを確認
3. 実行プランをみて、インデックスの使われ方を確認してみる
まずは、クラスタ化インデックスを使った検索をしてみましょう。下記の SQL を SQL Server Management Studio で実行してみましょう。実行結果は図5です。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT * FROM Sales.SalesOrderHeader Where SalesOrderID = 43659
図5：クラスタ化インデックスを利用した検索の実行結果
Clustered Index Seek が発生してますね。次に、非クラスタ化インデックスを使った検索をやってみましょう。下記の SQL を SQL Server Mangement Studio で実行しましょう。実行結果は図6です。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT OrderDate,
DueDate,
ShipDate
FROM Sales.SalesOrderHeader
Where rowguid='79B65321-39CA-4115-9CBA-8FE0903E12E6'
図6：非クラスタ化インデックスを利用した検索の実行結果
非クラスタ化インデックスに対して、Index Seek が実行され、OrderDate,DueDate および ShipDate を取得するため、クラスタ化インデックスに対して Clustered Index Seek が発生しています。
4. ついでに、付加列の効果も実感してみる
3.で下記のクエリを実行しました。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT OrderDate,
DueDate,
ShipDate
FROM Sales.SalesOrderHeader
Where rowguid='79B65321-39CA-4115-9CBA-8FE0903E12E6'
付加列の効果を見るために、下記の DDL 文を実行します。
USE [AdventureWorks2012]
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader
(
rowguid ASC
)
INCLUDE (OrderDate,
DueDate,
ShipDate) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
3. で実行した SQL を SQL Server Managemen Studio で実行します。実行結果は図7です。
図7：付加列作成後の非クラスタ化インデックスを利用した検索の実行結果
OrderDate,DueDate および ShipDate を取得するために、クラスタ化インデックスに対して Clustered Index Seek が発生していません。非クラスタ化インデックスに対しての Index Seek のみが実行されており、付加列の効果が表れていることがわかります。
次回、何をするか宣言してみる
B*Tree、クラスタ化インデックス、非クラスタ化インデックスおよび付加列の概要は大体理解できたと思います。( できました？ ) 次回は、何にしようかな？B*Tree と ページとテーブルの構造とかでしょうか？ 『図2：Sales.SalesOrderHeader テーブルのクラスタ化インデックス B-Tree 構造』を深堀してみましょう。
※なんとか、名古屋 - 新横浜間で書き上げることができました！文章構成がおかしい、誤字脱字はコメントお願いします！
