スケールに応じたパーティション テーブル
パーティション分割の決定はほぼ永続的です。 パーティション キーが不適切に選択されると、パーティション分割されていないテーブルよりもパフォーマンスが低下し、マルチテラバイト テーブルを再パーティション分割するには、何時間ものダウンタイムで完全に再構築する必要があります。 設計時に選択するパーティション キーとインデックスの配置によって、パーティション分割によってシステムが改善されるか、メンテナンスの悪夢が発生するかが決まります。簡単に元に戻すことはできません。
テーブルパーティション分割では 、大きなテーブルを単一の論理テーブルとして保持しながら、より小さく管理しやすい部分 (パーティション) に分割されます。 アプリケーションには 1 つのテーブルが表示されますが、データベース エンジンは、個別に保守、アーカイブ、または照会できる複数の物理セグメントを管理します。
パーティション分割の概念を理解する
パーティション分割には、データの分割方法を定義する パーティション関数 、パーティションをファイル グループにマップする パーティション構成 、各行が属するパーティションを決定する パーティション分割列 など、いくつかの重要なコンポーネントが含まれます。 これらの概念を理解することは、効果的なパーティション分割戦略を設計するのに役立ちます。
パフォーマンスと運用上の利点を評価する
パーティション分割を使用すると、パーティションの除外によりクエリパフォーマンスが向上します。これは、クエリがパーティションキーによって関連するパーティション (120か月ではなく1か月) のみをフィルター処理できること、CPUコア間で同時に複数のパーティションを処理できる並列処理、テーブル全体ではなくパーティションごとに計算される統計が高速に得られること、そして小さいパーティションの場合、インデックス探索でBツリーが浅くなることを意味します。
運用上の利点には、古いパーティションがオンラインのままで現在のパーティションのインデックスを再構築する細かいメンテナンス、メタデータ操作によって古いパーティションを数秒でアーカイブ テーブルに切り替えることで高速アーカイブする、パーティションを個別に維持する可用性の向上、古いパーティションをより安価で低速なストレージに移動して階層化されたストレージが含まれます。
たとえば、日付 (クエリの 90%) でフィルター処理されたクエリがテーブル全体をスキャンする、1.2 TB のトランザクション テーブルを持つ金融サービス企業を考えてみましょう。 月単位のパーティション分割を実装した後、クエリのパフォーマンスはパーティションの削除によって 10 ~ 20 倍向上し、インデックスの再構築はパーティションごとに 6 時間から 20 分に進み、古いデータをアーカイブするとパーティション切り替えを使用して 4 時間のロックから秒に短縮され、古いパーティションをより安価なストレージに移動することで、ストレージ コストが 40% 減少します。
パーティション分割を使用するタイミングを理解する
次の表は、パーティション分割が役立つ場合と、不要な複雑さを増す場合を示しています。
| Scenario | パーティションを使用しますか? | なぜでしょうか |
|---|---|---|
| 特定の列 (日付、リージョン) に対するクエリは、80%以上の頻度でフィルターされます。 | イエス | パーティションの削除は、関連するパーティションにのみアクセスします |
| 古いデータの定期的なアーカイブ (月単位、四半期単位) | イエス |
DELETE操作ではなく、数秒でパーティションを切り替える |
| 最近のデータに対してのみインデックスを再構築する必要がある | イエス | 古いパーティションをオンラインのままにして、現在のパーティションを再構築する。 |
| マルチテラバイトの階層化ストレージが必要な大量のデータを持つテーブル | イエス | 古いパーティションをより安価なストレージに移動する |
| ほとんどのクエリでは、テーブル全体をスキャンするか、さまざまな列でフィルター処理します | いいえ | すべてのパーティションがスキャンされました。パーティション分割されていない場合よりもパフォーマンスが低下します |
| 1 行の検索または小規模な範囲のスキャンが一般的である | いいえ | パーティション分割により、利点のないオーバーヘッドが増加する |
| 明確な列がクエリ パターンと一致しない | いいえ | 有効なパーティション キーを選択できない |
パーティション分割のコンポーネントを作成する
パーティション関数、パーティション構成、パーティション テーブルの 3 つのコンポーネントの例を次に示します。
-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
AS RANGE RIGHT FOR VALUES
('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');
-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);
-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
OrderID INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
CustomerID INT,
Amount DECIMAL(10,2),
CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);
この例では 、Orders テーブルの四半期ごとのパーティションを作成します。 パーティション関数は、4 つの境界値 (1 月、4 月、7 月、10 月) を定義し、2024 年より前のデータ用に 1 つ、2024 年の四半期ごとに 4 つのパーティションを作成します。 パーティション構成では、すべてのパーティションが PRIMARY ファイル グループにマップされます。 Orders テーブルでは、パーティション キーとして OrderDate 列が使用されます。この列は、適切なインデックス配置のために主キーに含める必要があります。
パーティション戦略を選択する
パーティション キーは、最も重要な決定事項です。 適切でない方法を選ぶと、パーティション分割は役立つどころか、かえって問題を引き起こします。 理想的なパーティション キーは、ほとんどのクエリの WHERE 句に表示され、合理的にバランスの取れたパーティションが作成され、メンテナンス パターンに合わせて調整されます。
次のキー選択基準は、適切なパーティション キーを選択するのに役立ちます。
- クエリ パターン: 80%以上のクエリがこの列でフィルターされています
- データ分散: パーティション間の均等な分散 (90% のデータを含む単一パーティションなし)
- メンテナンスアラインメント: アーカイブ/消去パターンに一致します (時間ベースのアーカイブの日付列)
- 安定性: INSERT 後に値が変更されない (更新可能な列でのパーティション分割を避ける)
レンジパーティションを理解する
範囲のパーティション分割では、値の範囲 (最も一般的な日付) に基づいてデータが分割されます。 各パーティションには、特定の範囲 (1 月のデータ、2 月のデータなど) が含まれています。 これは最も広く使用されている戦略です。
範囲のパーティション分割が最適な場所を次に示します。
- 時系列データ (注文、ログ、トランザクション)
- シーケンシャル データ (請求書番号、注文 ID)
- 数値範囲 (給与帯、価格レベル)
次の表は、一般的なパーティション パターンを示しています。
| パターン | 使用するタイミング |
|---|---|
| 毎日 | 大量のシステム、短いリテンション期間 |
| Weekly | 中量、6 ~ 12 か月のリテンション期間 |
| 毎月 | 最も一般的で、パーティションの数とサイズをバランス良く調整する方法 |
| 毎四半期 | ボリュームの削減、複数年のリテンション期間 |
| 毎年 | アーカイブ シナリオ、長期的な履歴データ |
たとえば、毎月の e コマース プラットフォームのパーティション分割注文では、現在の月のクエリが 1 つのパーティションにヒットし、四半期ごとのレポートが 3 つのパーティションにアクセスし、年末分析で 12 個のパーティションを使用して古い年を排除できます。
範囲パーティションを作成するには、パーティション関数で境界を定義します。
-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
AS RANGE RIGHT FOR VALUES
(100000, 200000, 300000, 400000);
カテゴリ値によるパーティション分割
RANGEパーティション分割は、文字列や地域などのカテゴリ値で使用できます。 パーティション関数は、並べ替え順序に基づいて値を配置します。 このアプローチは、クエリがカテゴリ別にフィルター処理されることが多い地理的分散、マルチテナント システム、または部門データに対して機能します。
次の例では、リージョン別にデータをパーティション分割します。
-- Partition by region
CREATE PARTITION FUNCTION PF_Region (NVARCHAR(50))
AS RANGE LEFT FOR VALUES ('East', 'North', 'South', 'West');
CREATE PARTITION SCHEME PS_Region
AS PARTITION PF_Region ALL TO ([PRIMARY]);
CREATE TABLE RegionalData (
DataID INT NOT NULL,
Region NVARCHAR(50) NOT NULL,
Value DECIMAL(10,2),
CONSTRAINT PK_RegionalData PRIMARY KEY (DataID, Region)
) ON PS_Region(Region);
インデックスのパーティション分割を実装する
テーブルをパーティション分割する場合、インデックスは整列または非整列であることができます。 アラインインデックスはテーブルと同じパーティション構成を使用しますが、アラインされていないインデックスでは異なるパーティション分割を使用するか、パーティション分割を使用しません。 既定では、パーティション テーブルの非クラスター化インデックスは、テーブルのパーティション構成を継承します。
アラインインデックスと非アラインインデックスを理解する
アラインされたインデックスでは、 テーブルと同じパーティション関数が使用されます。 各インデックス パーティションはテーブル パーティションと一致するため、高速なパーティション切り替え、メンテナンスの簡略化、パーティションの削除の向上が可能になります。
非アライメント インデックスでは、 異なるパーティション分割が使用されるか、パーティション分割が使用されません。 パーティション切り替えを使用することはできません。また、パーティション数が 1,000 を超えるテーブルではサポートされていません。
アーカイブ用のパーティション切り替えが必要な場合、特定のパーティションを個別に再構築する場合、またはパーティション キーでクエリ パターンがフィルター処理される場合は、アラインインデックスを使用します。
たとえば、CustomerID の非クラスター化インデックスを使用して OrderDate によってパーティション分割された Orders テーブルを想像してください。 同じ OrderDate スキームでアラインパーティション分割を使用すると、パーティションを切り替え、現在のインデックスを個別に再構築し、テーブル全体に影響を与えずに古いパーティションを削除することで、古い月をアーカイブできます。
ベース テーブルと同じパーティション構成を使用して、パーティション インデックスを作成できます。
-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
ON Orders(CustomerID)
ON PS_OrderDate(OrderDate);
-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
ON SalesData(Revenue, Region)
ON PS_SalesDate(SaleDate);
パーティション操作の管理
パーティション テーブルを作成した後は、時間の経過と同時にパーティション テーブルを管理する必要があります。 一般的な操作には、パーティション メタデータのクエリ、データの増加に伴う新しいパーティションの追加、アーカイブ中の古いパーティションの削除などがあります。 これらの操作では、 $PARTITION 関数と ALTER PARTITION FUNCTION ステートメントが使用されます。
パーティション情報のクエリ
$PARTITION関数を使用してパーティション情報を表示できます。 次に例を示します。
-- View partition information
SELECT
$PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
MIN(OrderDate) AS MinDate,
MAX(OrderDate) AS MaxDate,
COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;
新しいパーティション境界を追加する
パーティションを分割して、 ALTER PARTITION FUNCTIONを使用して新しい境界値を追加できます。 次に例を示します。
-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
SPLIT RANGE ('2024-11-01');
このステートメントは、パーティション関数に新しい境界値 (2024 年 11 月 1 日) を追加し、既存のパーティションを 2 つのパーティションに分割します。 10 月から 12 月までの日付を含むパーティションは、10 月用と 11 月から 12 月の 2 つのパーティションになります。
パーティションのアーカイブと削除
ALTER PARTITION FUNCTIONでMERGE RANGEを使用して、パーティションをマージして古いデータをアーカイブできます。 次に例を示します。
-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
MERGE RANGE ('2023-12-31');
パーティション分割のベスト プラクティスを適用する
次のベスト プラクティスは、実装後に修正が困難な一般的なパーティション分割ミスを回避するのに役立ちます。
- インデックスをテーブル パーティションに配置する: テーブルとインデックスに同じパーティション 構成を使用して、パーティションの切り替えとメンテナンスを有効にする
- データ分散の監視: パーティション統計を定期的に確認して不均衡なパーティションを特定し、パーティションの削除を確認する
- パーティション管理の自動化: 境界に達する前に新しいパーティションを追加し、古いパーティションをアーカイブするようにジョブをスケジュールする
- 過剰なパーティション分割を回避する: パーティションごとに数百万行をターゲットにし、数千行ではなく、パーティションが多すぎるとオーバーヘッドが発生する
- 主キーにパーティション キーを含める: パーティション構成でのクラスター化インデックスの配置に必要
パーティション分割には慎重な計画が必要です。 選択したパーティション キーとインデックスの配置によって、パフォーマンスが向上するか複雑になるかが決まります。 パーティション分割を正しく実装すると、より高速なクエリ、効率的なアーカイブ、および簡略化されたメンテナンスによって、大規模なテーブル管理が変換されます。