2026年2月10日火曜日

SQL Server インデックスの応用と内部構造の理解｜ヒープ構造・ページ分割・フィルターインデックス最適化

インデックスの“中身”を理解して真のチューニングへ

SQL Serverのインデックスは、単なる検索高速化の仕組みにとどまらず、データの物理配置とI/O性能に直結する重要な構造体です。
前回の基礎編では「クラスタ化／非クラスタ化インデックス」の違いを学びましたが、今回は内部動作に踏み込み、ヒープ構造・ページ分割・フィルターインデックスといった応用設計を通して、より効率的なデータアクセスを実現する方法を解説します。

目次

第1章　ヒープ構造とクラスタ化インデックスの違い

SQL Serverでは、テーブルがクラスタ化インデックスを持たない場合、そのデータはヒープ構造（Heap）として格納されます。
ヒープはデータ行が挿入順に並ぶだけで、物理的な順序は保証されません。検索には必ず非クラスタ化インデックスか全表スキャンが必要になります。

-- ヒープテーブルの作成
CREATE TABLE Sales.Logs (
  LogID INT IDENTITY PRIMARY KEY NONCLUSTERED,
  Message NVARCHAR(200)
);

一方、クラスタ化インデックスはB-Tree構造を持ち、行データ自体がキー順に並びます。 検索効率が高く、範囲検索にも強い構造ですが、更新頻度が高いテーブルではページ分割が発生しやすくなります。

第2章　ページ構造とページ分割の仕組み

SQL Serverのストレージ単位は8KBのデータページで、8ページをまとめたものが1エクステント（64KB）です。
インデックスはB-Tree構造で管理され、ルートページ→中間ページ→リーフページという3層構成をとります。

-- ページ情報の確認（物理レベルの構造分析）
DBCC IND ('SalesDB', 'Sales.Orders', 1);
DBCC PAGE ('SalesDB', 1, 200, 3);

ページ分割（Page Split）は、既存ページに新しいデータを挿入する余地がなくなった際、ページが分割されて新しいページが割り当てられる現象です。 これが多発すると断片化が進み、I/Oコストが増加します。

第3章　FILLFACTORとページ分割の最適化

FILLFACTORは、インデックス再構築時に各ページにどれだけの空き領域を残すかを指定するパラメータです。
デフォルトは100（空きなし）ですが、更新・挿入が多いテーブルでは適切な余白を残すことでページ分割を抑制できます。

-- FILLFACTORを80%に設定したインデックス作成
CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON Sales.Orders (OrderDate)
WITH (FILLFACTOR = 80);

定期的な断片化分析とFILLFACTOR調整により、性能を安定化させることが可能です。 運用環境では、挿入負荷が高いテーブルほどFILLFACTORを低め（70〜90%）に設定するのが一般的です。

第4章　フィルターインデックスの活用

フィルターインデックス（Filtered Index）は、WHERE句を条件として部分的にデータをインデックス化する機能です。
部分的なデータ（例：アクティブユーザー、在庫あり商品など）を対象にすることで、サイズ削減と性能向上を両立できます。

-- フィルターインデックスの作成
CREATE NONCLUSTERED INDEX IX_Products_Active
ON dbo.Products (CategoryID)
WHERE IsActive = 1;

フィルターインデックスは条件が明確な業務シナリオに有効で、例えば「頻繁に参照されるデータは限定的」「アーカイブデータは参照頻度が低い」ケースで特に効果を発揮します。

第5章　インデックスの断片化監視とメンテナンス

断片化を放置すると、実行計画上でIndex Scanが頻発し、読み取り性能が低下します。 SQL Serverでは以下のDMVを使って断片化率を確認できます。

SELECT 
  dbschemas.name AS SchemaName,
  dbtables.name AS TableName,
  dbindexes.name AS IndexName,
  indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
JOIN sys.tables dbtables ON dbtables.object_id = indexstats.object_id
JOIN sys.schemas dbschemas ON dbtables.schema_id = dbschemas.schema_id
JOIN sys.indexes dbindexes ON dbindexes.object_id = indexstats.object_id
  AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20;

断片化率 10〜30% の場合は REORGANIZE、30%以上 なら REBUILD を実施するのが目安です。

ALTER INDEX ALL ON Sales.Orders REBUILD WITH (FILLFACTOR = 90);

まとめ：構造を知ればインデックスは味方になる

インデックスを「作れば速くなる」だけで終わらせず、内部構造を理解して設計・運用することが真のチューニングです。
ヒープ構造とB-Treeの違い、ページ分割の仕組み、FILLFACTORの最適化、フィルターインデックスの活用を理解すれば、 SQL Serverのパフォーマンスは一段上のレベルに安定します。

SQL Server学習シリーズ

参考リンク（公式ドキュメント）

SQL Server 解説用イメージ

on
Labels: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)