スケールに応じたパーティション テーブル

完了済み

パーティション分割の決定はほぼ永続的です。 パーティション キーが不適切に選択されると、パーティション分割されていないテーブルよりもパフォーマンスが低下し、マルチテラバイト テーブルを再パーティション分割するには、何時間ものダウンタイムで完全に再構築する必要があります。 設計時に選択するパーティション キーとインデックスの配置によって、パーティション分割によってシステムが改善されるか、メンテナンスの悪夢が発生するかが決まります。簡単に元に戻すことはできません。

テーブルパーティション分割では 、大きなテーブルを単一の論理テーブルとして保持しながら、より小さく管理しやすい部分 (パーティション) に分割されます。 アプリケーションには 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');

パーティション分割のベスト プラクティスを適用する

次のベスト プラクティスは、実装後に修正が困難な一般的なパーティション分割ミスを回避するのに役立ちます。

  • インデックスをテーブル パーティションに配置する: テーブルとインデックスに同じパーティション 構成を使用して、パーティションの切り替えとメンテナンスを有効にする
  • データ分散の監視: パーティション統計を定期的に確認して不均衡なパーティションを特定し、パーティションの削除を確認する
  • パーティション管理の自動化: 境界に達する前に新しいパーティションを追加し、古いパーティションをアーカイブするようにジョブをスケジュールする
  • 過剰なパーティション分割を回避する: パーティションごとに数百万行をターゲットにし、数千行ではなく、パーティションが多すぎるとオーバーヘッドが発生する
  • 主キーにパーティション キーを含める: パーティション構成でのクラスター化インデックスの配置に必要

パーティション分割には慎重な計画が必要です。 選択したパーティション キーとインデックスの配置によって、パフォーマンスが向上するか複雑になるかが決まります。 パーティション分割を正しく実装すると、より高速なクエリ、効率的なアーカイブ、および簡略化されたメンテナンスによって、大規模なテーブル管理が変換されます。