都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

SQL Server の構造について記事にしてみる - その2 ( 前回の 『 インデックスの基礎知識 』 を実際のテーブルで実験してみる ) -

前回の内容をおさらいしてみる

 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'

f:id:koogucc11:20140812211729p:plain

図1:SQL Server Management Studio の実行結果

 上図の結果から、Bツリー、各階層のノード構成は下記のようになります。

  • ルートノードは、index レベルは  2、レコード数は 2、ページ数は 1になります。
  • 中間ノードは、index レベルは  1、レコード数は 685、ページ数は 2になります。
  • リーフノードは、index レベルは  0、レコード数は 31465、ページ数は 685 になります。

 図示すると図2のようになります。

f:id:koogucc11:20140812213819p:plain

図2:Sales.SalesOrderHeader テーブルのクラスタ化インデックス B-Tree 構造

 B+Tree の構成を深く知るには、ページ数、ページ同士の関係などを参照する必要がありますが、結構深い内容になりますので、今回はここまでにしておきます。勘のいい方は上図をみてなんなく構造が分かったりするかも...です。

2. インデックスの種類を確認してみる

 オブジェクトカタログビュー であれば、sys.indexes で確認することができます。

Management Studio で下記の SQL を実行してみましょう。実行結果は図3です。

select * from sys.indexes

f:id:koogucc11:20140625223808p:plain

図3:sys.indexes の結果

 SQL Server Management Studio であれば、オブジェクトエクスプローラーから確認することができます。

f:id:koogucc11:20140625223904p:plain

図4:オブジェクトエクスプローラーでインデックスを確認

3. 実行プランをみて、インデックスの使われ方を確認してみる

 まずは、クラスタ化インデックスを使った検索をしてみましょう。下記の SQLSQL Server Management Studio で実行してみましょう。実行結果は図5です。

USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT * FROM Sales.SalesOrderHeader Where SalesOrderID = 43659

f:id:koogucc11:20140813205402p:plain

図5:クラスタ化インデックスを利用した検索の実行結果

 Clustered Index Seek が発生してますね。次に、非クラスタ化インデックスを使った検索をやってみましょう。下記の SQLSQL Server Mangement Studio で実行しましょう。実行結果は図6です。

USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT OrderDate,
       DueDate,
    ShipDate
FROM Sales.SalesOrderHeader
Where rowguid='79B65321-39CA-4115-9CBA-8FE0903E12E6'

f:id:koogucc11:20140813210554p:plain

図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. で実行した SQLSQL Server Managemen Studio で実行します。実行結果は図7です。

f:id:koogucc11:20140813212631p:plain

図7:付加列作成後の非クラスタ化インデックスを利用した検索の実行結果

 OrderDate,DueDate および ShipDate を取得するために、クラスタ化インデックスに対して Clustered Index Seek が発生していません。非クラスタ化インデックスに対しての Index Seek のみが実行されており、付加列の効果が表れていることがわかります。 

次回、何をするか宣言してみる

 B*Tree、クラスタ化インデックス、非クラスタ化インデックスおよび付加列の概要は大体理解できたと思います。( できました? ) 次回は、何にしようかな?B*Tree と ページとテーブルの構造とかでしょうか? 『図2:Sales.SalesOrderHeader テーブルのクラスタ化インデックス B-Tree 構造』を深堀してみましょう。

※なんとか、名古屋 - 新横浜間で書き上げることができました!文章構成がおかしい、誤字脱字はコメントお願いします!