GUIDを​インデックスキーに​すると​内部​断片化を​引き起こす

はじめに

DBを効率的に使用するのにインデックスは非常に有効な手段です。
ですがインデックスキーの設計を誤ると、パフォーマンス低下やメモリ使用効率が悪化するといった問題を引き起こすこともあります。

本記事ではMicrosoft SQL Server（以降はSQL Server）に対し、GUID^[1]がインデックスのキーになった場合、どのように内部断片化に影響が出るのか説明していきます。

1. GUIDを​インデックスキーと​して​挿入された​時、​ページ​内部は​どのようになっている？

SQL Serverでは、インデックスを「ページ」と呼ばれる最小の物理単位(8KB/ページ)で管理しており、このページの中にインデックスのキー(以降はキー)と、参照先を示すインデックスのデータ(以降はデータ)を格納しています。

このインデックスの配置の仕組みで、「キーの値に基づいた論理順序」と「実際のインデックスの並び順である物理順序」を一致させようとしますが、この仕組みが、インサートされるキーの性質や値によってはページに内部断片化を引き起こす場合があります。

2-1．​連続した​値を​キーに​した​場合

キーをインサートすると、前述したページに順次配置されていきます。
キーの値が連続していると、挿入位置がページの末尾、または新しいページの先頭になるため、無駄なページ分割は発生しません。

2-2．​GUIDを​キーに​した​場合

GUID をキーとして繰り返しインサートしていくと、キーの値がランダムであるため、論理順序を保とうとして挿入位置がページの途中になる場合があります。
その際、ページ分割が発生し、内部断間化につながります。

3. GUID を​キーに​せざるを​得ない​場合

キーをGUIDにすることが内部断片化の温床になることは説明しましたが

• サーバーや生成元を跨いでもユニークな値にする必要がある
• 今の設計を崩さずに断片化に対処したい
• 外部I/Fとの整合性を保ちたい 等、キーをGUIDにせざるを得ない場合もあるかと思います。

その場合に何かできる対策はないのか、調査してみましたのでご紹介します。

3-1．​順序性を​保った​GUIDの​発行が​可能な​NEWSEQUENTIALID()に​ついて

内部断片化進行の原因はGUIDをキーにすることそのものではなく、ランダムな値であるために、 「インデックスの物理的な順序が、キーの論理順序と一致しなくなる」 ことが原因です。

そこで、SQL Serverには順序付きのGUIDを発行できる NEWSEQUENTIALID() という関数があります。
この関数と、ランダムでGUIDを発行する NEWID() を用いた場合で、どの程度内部断片化の進行に差があるのか、比較検証してみましょう。

3-2．​検証

以下クエリを参考に、2つのサンプルテーブルを作成してみましょう。

-- 順序付きGUIDを使用するテーブルの作成
 CREATE TABLE TestTableSequential (
    Id INT IDENTITY(1,1) NOT NULL,
    Guid UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()
 );
 CREATE NONCLUSTERED INDEX IX_TestTableSequential_Guid ON TestTableSequential (Guid);

-- 通常のGUIDを使用するテーブルの作成
 CREATE TABLE TestTable (
    Id INT IDENTITY(1,1) NOT NULL,
    Guid UNIQUEIDENTIFIER
 );
 CREATE NONCLUSTERED INDEX IX_TestTable_Guid ON TestTable (Guid);

作成したそれぞれのテーブルに対し、以下クエリのように適当なデータを10,000件挿入してみましょう。

TestTableSequential への​10,000件INSERT

-- NEWSEQUENTIALID()で発行したGUIDでデータを挿入
DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
    INSERT INTO TestTableSequential
    DEFAULT VALUES;
    SET @i = @i + 1;
END

TestTable への​10,000件INSERT

-- 通常のGUIDでTestTable にデータを挿入
DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
    INSERT INTO TestTable (Guid)
    VALUES (NEWID());
    SET @i = @i + 1;
END

では、断片化を確認してみます。
断片化率は、avg_fragmentation_in_percent を取得するクエリを実行することで確認することができます。

SELECT 
    a.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestTableSequential'), NULL, NULL, 'SAMPLED') AS a
JOIN 
    sys.indexes AS b
ON 
    a.object_id = b.object_id AND a.index_id = b.index_id;

結果はこちらです。

TestTableSequential

TestTable

※数値は環境や実行状況に応じて変動します

ここから、NEWSEQUENTIALID()で発行した順序付きGUIDでは、断片化が発生しにくくなっていることがわかり、キーにGUIDを用いる場合でも、有効な対策があることがわかりました。

3-2．​NEWSEQUENTIALID()利用時の​注意点

NEWSEQUENTIALID() は前述した通り、断片化対策において非常に有効であることを説明しましたが、利用するにおいての注意点も存在します。
代表的なものをいくつか紹介しますので、これらのような注意点がある旨を理解した上で活用してください。

• DEFAULTでしか使えない
  → UNIQUEIDENTIFIER 列の DEFAULT 制約としてのみ使用可能で、 INSERT 時は該当の列を DEFAULT にしないと順序性が崩れる恐れがあります
• URL等、外部に露出する用途には向いていない
  → シーケンシャルな特性があるため、NEWID() で生成したGUIDに比べると、推測されやすくなります。
  内部システムでの使用を推奨します

4. ​おわりに

インデックスの断片化は、発生していても気付きにくい一方で、パフォーマンスやリソース効率に大きな影響を与える問題です。
特に、ランダムなキー設計や GUID の扱い方次第では、DELETE を伴わなくても断片化が急速に進行するケースもあります。

一方で、

• シーケンシャルなキー設計
• GUID の生成方法を配慮する

といった対策を適切に行うことで、断片化とうまく付き合っていくことは十分可能です。
「最近クエリが遅くなった」「運用コストが増えてきた」と感じたとき、インデックスの断片化という観点から状況を確認してみることで、思わぬ改善点が見つかるかもしれません。

本記事が、インデックス設計や運用を見直す一助となれば幸いです。