セージ の メモ書き

メモこそ命の恩人だ

SQL Server - クラスター化/非クラスター化インデックス

概要

docs.microsoft.com

docs.microsoft.com

インデックス ( Index : 索引 )

SQL Serverのインデックス構造(前編):SQL Server 2000 チューニング全工程(4)(1/2 ページ) - @IT

SQL Serverのインデックス構造(後編):SQL Server 2000 チューニング全工程(5)(1/2 ページ) - @IT

  • SELECT を高速化するための仕組み。
  • SQL Server のインデックスは B-tree の構造で作成される。( B : Baranced )
  • ページ数により、ツリー階層が変わる。
  • インデックスの種類により、リーフノードの内容が異なる。
  • クラスター化とは、データを同じ場所に格納すること。

クラスター化インデックス(主索引)

  • 特徴
    • リーフに実データが格納される。
    • データの並びが考慮される。
  • 利点
    • 特徴より、検索処理が高速。ソートや範囲検索も高速。
  • 欠点
    • テーブルに1つのみしか作成できない。

クラスター化インデックス(副次索引、二次索引)

  • 特徴
    • リーフにマッピング情報が格納される。
    • ポインターを経由してアクセスするイメージ。
    • データの並びが考慮されない。
  • 利点
    • テーブルに複数作成できる。
  • 欠点
    • 特徴より、検索処理が低速(クラスター化と比較して)

インデックスの自動付与

制約 動作
主キー制約 クラスター化インデックスがない場合
 → クラスター化インデックスが付与される。
クラスター化インデックスがある場合
 → 非クラスター化インデックスが付与される。
一意制約 クラスター化インデックスが付与される。


確認用クエリ

インデックスの一覧情報

SELECT 
    name, 
    index_id, 
    type_desc 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('SampleTable1');

インデックスの階層情報(レコード数、断片化率)

  • DETAILED 指定で階層ごとの情報がわかる。
  • index_level
    • 0:リーフページ
    • 1以上:中間ページ or ルートページ
      • 1 のみの場合、それがルートページ
SELECT 
    DB_NAME(database_id) AS DB名, 
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    index_type_desc,
    index_depth,
    index_level,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('SampleTable1'), NULL, NULL, 'DETAILED')
ORDER BY index_id, index_level DESC;

ページごとのインデックス情報

  • allocated_page_page_id
    • ページID
    • ここからインデックスページの内容をダンプできる。
    • ページダンプは、セージブログの "ページ" で検索。
  • page_type_desc
    • INDEX_PAGE or DATA_PAGE など
  • next_page_page_id/previous_page_page_id
    • DATA_PAGE 間のインデックスの関連
SELECT 
    DB_NAME(database_id) AS DB名, 
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    allocation_unit_type_desc,
    allocated_page_page_id,
    page_type_desc,
    page_level,
    next_page_page_id,
    previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),  OBJECT_ID('SampleTable1'),  -1,  NULL, 'DETAILED')
ORDER BY index_id, allocated_page_page_id;

データレコードがどのインデックスページに含まれるか

  • 前述の dm_db_database_page_allocations を利用すればわかる。


クラスター化インデックス

  • 以下のテーブルで確認する。
  • 主キーがクラスター化インデックスのケース。
USE SampleDb;
IF object_id(N'SampleTable1') is not null DROP TABLE SampleTable1;
CREATE TABLE SampleTable1 (Id INT PRIMARY KEY, Data1 VARCHAR (8000));  

インデックスページの形式

  • 数件のデータを登録し、インデックスページに格納されるデータを確認。
  • インデックスページを作成するため、1行のサイズを大きくする。
    • ※ 1ページ2行の構造で登録する。
USE SampleDb;
TRUNCATE TABLE SampleTable1;
GO

-- データ登録(1ページ2行になる)
INSERT INTO SampleTable1 (id, Data1) VALUES (1, REPLICATE('a', 4000));
INSERT INTO SampleTable1 (id, Data1) VALUES (2, REPLICATE('b', 4000));
INSERT INTO SampleTable1 (id, Data1) VALUES (3, REPLICATE('c', 4000));
INSERT INTO SampleTable1 (id, Data1) VALUES (4, REPLICATE('d', 4000));
INSERT INTO SampleTable1 (id, Data1) VALUES (5, REPLICATE('e', 4000));

-- ページの表示
SELECT
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    allocated_page_page_id,
    page_type_desc,
    page_level
FROM sys.dm_db_database_page_allocations(DB_ID(),  OBJECT_ID('SampleTable1'),  -1,  NULL, 'DETAILED')
ORDER BY index_id, allocated_page_page_id;

  • ダンプした結果、このテーブル定義の場合、以下のフォーマットと推測できる。
    • サイズ:11バイト
    • 1バイト目:インデックス行のヘッダー (参照:テーブルサイズ見積もり)
    • 2~5バイト目:インデックス列の値 (int 4バイト)
    • 6~9バイト目:ページID (多分、4バイト。ページIDの値は結構大きいケースもある。多分 int)
    • 10~11バイト目:多分、フッター(01 00)
    インデックス列の値 ページID
    0x0108(264)
    3 0x010a(266)
    5 0x010b(267)

ページID:264 の値が確認できない。
先頭リーフページへの値は、インデックスページに乗らない仕様???

インデックスページの階層

  • 実験:ページ数により、Bツリーが深くなるか確認する。
  • 前述と同様、1ページ2行の構造で登録する。
  • 前述よりこのテーブル定義の場合...
    • インデックス行:11バイト(あと、オフセット分で+2バイト)
    • 1データ行あたりのインデックス行:6.5バイト/データ行
      • データ2行で上記サイズなので。
      • 13÷2 = 6.5
  • 予想:インデックスページの境界は...
    • 8192 - 96 = 8096バイト が使用できる領域。
    • 8096 ÷ 6.5 = 1245.54データ行
    • 1245行が境界のはず。ここで階層が変わる。
  • 補足:1ページ1レコードなら...
    • 13バイト/データ行
    • 8096 ÷ 13 = 622.76 データ行
    • 623行が境界で、階層変わる。
    • Azure SQL Database でも同じなこと確認。
-- 初期化
TRUNCATE TABLE SampleTable1;
GO

-- データ登録
DECLARE @Data VARCHAR (8000) = REPLICATE('a', 4000);
DECLARE @Count INT = 1;
WHILE @Count <= 1245 BEGIN
    INSERT INTO SampleTable1 (id, Data1) VALUES (@Count, @Data);
    SET @Count += 1;
END

-- インデックスページの確認
SELECT
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    allocated_page_page_id,
    page_type_desc,
    page_level
FROM sys.dm_db_database_page_allocations(DB_ID(),  OBJECT_ID('SampleTable1'),  -1,  NULL, 'DETAILED')
WHERE page_type_desc = 'INDEX_PAGE' 
ORDER BY page_level DESC;

以下の結果となる。予想通り 1245行 で階層に変化あり。

データ行数 インデクス階層
1244 1
1245 3
  • ついでにルートノードのページダンプする。
  • 中間ノードへの参照を確認する。
  • 補足:16進数表記(ページダンプの場合、順序が逆転する)
    • 265:0x0109
    • 1040 0x0410


クラスター化インデックス(複合主キー)

USE SampleDb;
IF object_id(N'SampleTable1') is not null DROP TABLE SampleTable1;
CREATE TABLE SampleTable1 (Id INT NOT NULL, Id2 INT NOT NULL, Data1 VARCHAR (8000));
GO
ALTER TABLE SampleTable1 ADD CONSTRAINT PK_SampleId1 PRIMARY KEY(Id, Id2);
GO

インデックスページの形式

USE SampleDb;
TRUNCATE TABLE SampleTable1;
GO

-- データ登録(1ページ2行になる)
INSERT INTO SampleTable1 (id, id2, Data1) VALUES (1, 6, REPLICATE('a', 4000));
INSERT INTO SampleTable1 (id, id2, Data1) VALUES (2, 7, REPLICATE('b', 4000));
INSERT INTO SampleTable1 (id, id2, Data1) VALUES (3, 8, REPLICATE('c', 4000));
INSERT INTO SampleTable1 (id, id2, Data1) VALUES (4, 9, REPLICATE('d', 4000));
INSERT INTO SampleTable1 (id, id2, Data1) VALUES (5, 10, REPLICATE('e', 4000));

-- ページの表示
SELECT
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    allocated_page_page_id,
    page_type_desc,
    page_level,
    previous_page_page_id, 
    next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),  OBJECT_ID('SampleTable1'),  -1,  NULL, 'DETAILED')
ORDER BY index_id, allocated_page_page_id;

  • ダンプした結果、このテーブル定義の場合、以下のフォーマットと推測できる。

    • サイズ:15バイト
    • 1バイト目:インデックス行のヘッダー (参照:テーブルサイズ見積もり)
    • 2~5バイト目:インデックス列の値1 (int 4バイト)
    • 6~9バイト目:インデックス列の値2 (int 4バイト)
    • 10~13バイト目:ページID (多分、4バイト)
    • 14~15バイト目:多分、フッター(01 00)
    インデックス列の値 ページID
    0x00 07 ae 68 (503400)
    3, 8 0x00 07 ae 6a (503402)
    5, 10 0x00 07 ae 6b (503403)
  • 複合主キーの場合、単純にキーが複数格納される。

  • 前述と比較して、4バイト増えた。


クラスター化+非クラスターインデックス

USE SampleDb;
CREATE TABLE SampleTable1 (
    Id INT NOT NULL, 
    CIndex CHAR NOT NULL,
    Data1 VARCHAR(8000));  

-- CIndex がクラスター化インデックス
CREATE CLUSTERED INDEX SampleIndex ON SampleTable1(CIndex);

-- Id が主キー(非クラスター化インデックス)
ALTER TABLE SampleTable1 ADD CONSTRAINT PK_SampleId PRIMARY KEY(Id);
GO
  • 数件のデータを登録し、インデックスページに格納されるデータを確認。
  • インデックスページを作成するため、1行のサイズを大きくする。
  • 1ページ2行の構造で登録する。
-- データ登録
INSERT INTO SampleTable1 (Id, CIndex, Data1) VALUES (1, 'a', REPLICATE('x', 4000));
INSERT INTO SampleTable1 (Id, CIndex, Data1) VALUES (2, 'b', REPLICATE('x', 4000));
INSERT INTO SampleTable1 (Id, CIndex, Data1) VALUES (3, 'c', REPLICATE('x', 4000));
INSERT INTO SampleTable1 (Id, CIndex, Data1) VALUES (4, 'd', REPLICATE('x', 4000));
INSERT INTO SampleTable1 (Id, CIndex, Data1) VALUES (5, 'e', REPLICATE('x', 4000));

-- インデックスの一覧を確認
-- 1:クラスター化インデックス
-- 1以外:非クラスター化インデックス
SELECT 
    name, 
    index_id, 
    type_desc 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('SampleTable1');

-- ページごとのインデックス情報を確認
SELECT
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    allocated_page_page_id,
    page_type_desc,
    page_level
FROM sys.dm_db_database_page_allocations(DB_ID(),  OBJECT_ID('SampleTable1'),  -1,  NULL, 'DETAILED')
ORDER BY index_id, allocated_page_page_id;

データページの形式

  • ページID"264" をダンプ(データページ)
  • クラスター化インデックスの値が先行してセットされた。

インデックスページの形式(クラスター)

  • ページID"265" をダンプ(インデックスページ:クラスター)
  • ダンプした結果、このテーブル定義の場合、以下のフォーマットと推測できる。
    • サイズ:8バイト
    • 1バイト目:06? 不明。
    • 2バイト目:インデックス列の値 (char 1バイト)
    • 3~6バイト目:ページID (int 4バイト)
    • 7~8バイト目:多分、フッター(01 00)

インデックスページの形式(非クラスター)

  • ページID"265" をダンプ(インデックスページ:クラスター)
  • ダンプした結果、このテーブル定義の場合、以下のフォーマットと推測できる。
    • サイズ:9バイト
    • 1バイト目:16? 不明。
    • 2~5バイト目:非クラスター化インデックス列の値 (int 4バイト)
    • 6バイト目:クラスター化インデックス列の値(char 1バイト)
    • 7~9バイト目:不明。



以上