SQL Server の構造について記事にしてみる - その3 ( インデックスの構造を深堀してみる ) -
前回の内容をおさらいしてみる
SQL Server の構造について記事にしてみる - その2 ( 前回の 『 インデックスの基礎知識 』 を実際のテーブルで実験してみる ) - - 都内で働くSEの技術的なひとりごと では、クラスタ化インデックス、非クラスタ化インデックス、付加列に関する検証を行いました。
今回のやることを説明してみる
B+Tree インデックスの構造を Undocumented な関数、コマンドを使用して詳細に調べていきたいと思います。
使用する環境を説明してみる
Microsot Azure 上で 動作している Windows Server 2012 R2、SQL Server 2014 で動作確認をします。データベースは、AdventureWorks2012 上の Sales.SalesOrderHeader テーブルを使用します。
では早速、説明してみる
前回は、下記の SQL を SQL Server Management Studio で実行しました。
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'
これだけでは、階層の深さ、ページ数などしかわかりません。ページ間の関連などの情報やノード間の関係など詳細な情報はまったくわかりません。詳細な情報を得るには、undocumented なコマンドを使用する必要があります。
Undocumented なコマンドで、詳細を調査してみる
インデックスの情報を調査するには、DBCC IND を使用します、と言いたいところですが、SQL Server 2012 より、DBCC IND の代替手段として、sys.dm_db_database_page_allocations が提供されています。しかし、この関数も今のところ undocumented です。
select database_id,
object_id,
index_id,
partition_id,
rowset_id,
allocation_unit_id,
allocation_unit_type,
allocation_unit_type_desc,
data_clone_id,
clone_state,
clone_state_desc,
extent_file_id,
extent_page_id,
allocated_page_iam_file_id,
allocated_page_iam_page_id,
allocated_page_file_id,
allocated_page_page_id,
is_allocated,
is_iam_page,
is_mixed_page_allocation,
page_free_space_percent,
page_type,
page_type_desc,
page_level,
next_page_file_id,
next_page_page_id,
previous_page_file_id,
previous_page_page_id,
is_page_compressed,
has_ghost_records
FROM sys.dm_db_database_page_allocations( DB_ID(),
OBJECT_ID('Sales.SalesOrderHeader'),
-1,
NULL,
'DETAILED')
図1:sys.dm_db_database_page_allocations の実行結果
DBCC IND で得られる結果より、多くの情報が得られるようです。よくわからない列もありますね。インデックス ( B+Tree ) の構造を得るために、最小限の列に絞ってみます。
select [ページID] = allocated_page_page_id,
[ページタイプ] = page_type,
[ページタイプ詳細] = page_type_desc,
[ページレベル(Index_Level)] = page_level,
[次ページのページID] = next_page_page_id,
[前ページのページID] = previous_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(),
OBJECT_ID('Sales.SalesOrderHeader'),
NULL,
NULL,
'DETAILED')
Where index_id =1
図2: sys.dm_db_database_page_allocations の実行結果
図3 から ページID:6858 がルートページであることが判断できます。
図3:ルートページ
ルートページ( ページID:6858 ) の詳細下記のコマンドを SQL Server Management Studio で実行します。図4の結果から、中間ノードは、ページID:6856,6857だということが判断できます。また、SalesOrderID の分布は、72338未満は ページID:6856で、72338以上はページID:6857 に格納されていることが判断できます。
DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6858,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
図4:DBCC PAGE の実行結果
ページID:6856,6857 に同様のコマンドを実行してみましょう。まず、ページID:6856 を実行してみます。
DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6856,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
図5:ページID:6856の実行結果
ページID:6857 を実行してみます。
DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6857,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
図6:ページID:6856の実行結果
これらの結果を元に、SalesOrderHeader テーブルのクラスタ化インデックスを図示すると図7のようになります。
図7:SalesOrderHeader のクラスタ化インデックス
検索時に、どんな感じでB+Tree が使われるのか説明してみる
下記のクエリを発行してみましょう。
SELECT * FROM Sales.SalesOrderHeader Where SalesOrderID = 43800
各ページの検索方法は、図8のようになります。
図8:インデックスの検索方法
図8の内容を詳細に説明してみましょう。ページID:6858 の内容は図9の通りです。Where 句 に指定された SalesOrderID と SalesOrder(key) を比較します。43800は、72338未満であるため、ページID:6856 が選択されます。
図9:ページID:6858のページ内容
ページID:6856 の内容は図10の通りです。Where 句 に指定された SalesOrderID と SalesOrder(key) を比較します。43800は、43794以上43841未満であるため、ページID:6827に選択されます。
図10:ページID:6856のページ内容
ページID:6827 の内容は図11の通りです。実際にデータが含まれているのが確認できます。
図11:ページID:6827のページ内容
次回何をするのかまたまた宣言してみる
今回までの説明で、インデックスがどのような構造なのかどのように使用されるのかは大体理解できたかと思います。次回はページに関して詳しく説明したいと思います。