2023-03-20

SQLにおけるパーティショニング

SQLにおけるパーティショニングとは

SQLにおけるパーティショニングは、テーブルのデータをより管理しやすい小さな部分、つまりパーティションに分割する技術です。この機能は、テーブルに膨大なレコードが含まれている場合に特に有用です。レコードを特定の領域に分割することで、データベース操作の効率が向上し、データの取得プロセスが大幅に高速化されます。

パーティショニングの利点

SQLにおけるパーティショニングの利点は多岐にわたります。以下にいくつかの利点を挙げます。

SQLパフォーマンスの向上
フルテーブルスキャンがテーブル全体ではなく必要なパーティションのみにアクセスできるため、SQL操作が高速化されます。また、各パーティションへの並列アクセスが可能になり、結果の計算もより速く行えます。
並列処理の増加
パーティション内のレコードを更新する場合、各パーティションは個別にロックされるため、作業を妨げることなく並列で処理を進めることができます。
最適化された統計情報とメンテナンス
パーティショニングでは、統計情報を更新したり各パーティションを個別に最適化したりすることが可能です。この粒度の細かい制御により、定期的なメンテナンス作業を効率的かつ全体のシステムパフォーマンスに影響を与えないように行うことができます。

SQLにおけるパーティショニングの種類

以下では、SQLで利用可能なさまざまなパーティショニング方法について説明します。

レンジパーティショニング

レンジパーティショニングは、値の範囲に基づいてテーブルをパーティションに分割します。この方法は、日付、時刻、数値範囲など、論理的な範囲が存在するデータを扱う場合に一般的に使用されます。

例えば、注文のテーブルを考えてみます。注文日に基づいてテーブルをパーティションしたい場合、各年ごとにパーティションを作成することができます。

sql

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT,
  order_date DATE NOT NULL DEFAULT '1970-01-01',
  order_value DECIMAL(10,2)
)
PARTITION BY RANGE ( YEAR(order_date) ) (
  PARTITION p1970s VALUES LESS THAN (1980),
  PARTITION p1980s VALUES LESS THAN (1990),
  PARTITION p1990s VALUES LESS THAN (2000),
  PARTITION p2000s VALUES LESS THAN MAXVALUE
);

ここでは、ordersテーブルをorder_dateの範囲でパーティションしています。1970年代、1980年代、1990年代、2000年代ごとにパーティションを定義しています。

リストパーティショニング

リストパーティショニングは、事前に定義された値のリストに基づいてデータを分割する方法です。データを範囲ではなく特定のカテゴリにグループ化する必要がある場合に便利です。

例えば、企業がさまざまな地域に店舗を持っており、これらの地域ごとに売上データをパーティションしたいとします。

sql

CREATE TABLE sales (
  sales_id INT NOT NULL,
  product_id INT,
  region_id INT,
  sale_date DATE,
  sale_value DECIMAL(10,2)
)
PARTITION BY LIST (region_id) (
  PARTITION pNorth VALUES IN (1, 2, 3),
  PARTITION pEast VALUES IN (4, 5, 6),
  PARTITION pWest VALUES IN (7, 8, 9),
  PARTITION pSouth VALUES IN (10, 11, 12)
);

この場合、各パーティションはリージョンを表し、リージョンIDのリストで識別されます。

ハッシュパーティショニング

ハッシュパーティショニングは、ハッシュ関数を使用してデータをあらかじめ定義されたパーティション数に均等に分散させる方法です。このタイプのパーティショニングは、データをパーティションするための論理的な範囲やリストがなく、データの均等な分布を確保したい場合に有用です。

例えば、組織内の従業員レコードを複数のパーティションに分割してみます。

sql

CREATE TABLE employees (
  emp_id INT NOT NULL,
  emp_name VARCHAR(30),
  emp_position VARCHAR(30),
  emp_salary DECIMAL(10,2)
)
PARTITION BY HASH (emp_id)
PARTITIONS 5;

この場合、employeesテーブルは従業員ID (emp_id) を使用して5つの均等なパーティションに分割されます。

キーパーティショニング

キーパーティショニングは、パーティショニングキーが主キー全体またはその一部である場合に使用されるハッシュパーティショニングの特殊なケースです。データの均等な分布とテーブルに主キーがある場合に主に使用されます。

例えば、productsテーブルがあり、そのデータを主キーであるproduct_idに基づいてパーティションしたい場合を考えてみます。

sql

CREATE TABLE products (
  product_id INT NOT NULL PRIMARY KEY,
  product_name VARCHAR(30),
  product_price DECIMAL(10,2)
)
PARTITION BY KEY(product_id)
PARTITIONS 3;

この場合、productsテーブルはproduct_idに基づいて3つのパーティションに分割されます。

サブパーティショニング

サブパーティショニングは、パーティションをさらに細分化して、2段階のパーティショニングスキームを作成する方法です。データをより細かく分散したい場合に特に有用です。この方法はしばしば2つの異なるパーティショニング戦略を組み合わせます。例えば、最初に範囲でデータをパーティションし、その後、それらの範囲をリストやハッシュでさらにサブパーティションすることがあります。

例えば、会社が販売データを年ごと（範囲）にパーティションし、さらに地域ごと（リスト）にサブパーティションしたい場合を考えてみます。

sql

CREATE TABLE sales (
  sales_id INT NOT NULL,
  region_id INT,
  sale_date DATE NOT NULL DEFAULT '1970-01-01',
  sale_value DECIMAL(10,2)
)
PARTITION BY RANGE( YEAR(sale_date) )
SUBPARTITION BY LIST(region_id) (
  PARTITION p1970s VALUES LESS THAN (1980) (
    SUBPARTITION sNorth VALUES IN (1, 2, 3),
    SUBPARTITION sEast VALUES IN (4, 5, 6)
  ),
  PARTITION p1980s VALUES LESS THAN (1990) (
    SUBPARTITION sWest VALUES IN (7, 8, 9),
    SUBPARTITION sSouth VALUES IN (10, 11, 12)
  ),
  PARTITION p1990s VALUES LESS THAN (2000) (
    SUBPARTITION sNorth1 VALUES IN (1, 2, 3),
    SUBPARTITION sEast1 VALUES IN (4, 5, 6)
  ),
  PARTITION p2000s VALUES LESS THAN MAXVALUE (
    SUBPARTITION sWest1 VALUES IN (7, 8, 9),
    SUBPARTITION sSouth1 VALUES IN (10, 11, 12)
  )
);

この例では、salesテーブルはまず範囲でパーティションされ、販売の年代ごとにデータが分割されます。それぞれのパーティションは、さらに地域ごとにリストでサブパーティションされます。このアプローチにより、データの分離レベルが細かくなり、データの取得とメンテナンスが効率化されます。