APシナリオにおけるテーブルパーティション設計の実践|V4.4.2|OceanBaseデータベース|ドキュメント|OceanBaseマニュアル

分析系業務では、通常膨大な量のデータを分析・計算する必要があるため、データベースのクエリ性能およびデータ管理能力に対して非常に高い要求が課せられます。OceanBaseはパーティション技術により、テーブルのデータをパーティションキーに基づいて水平方向に複数のデータサブセットに分割することで、クエリ効率の向上とデータ管理能力の強化に寄与します。

クエリ効率の向上： パーティションプルーニングにより、関係のないデータのスキャンを削減できます。
データメンテナンス： データアーカイブやクリーンアップなど、パーティション単位でのデータ管理をサポートします。
データ分散： パーティション単位でデータを分散配置することで、データを複数のノードに分散させ、優れた拡張性を実現します。

パーティションの役割

OceanBaseでは、パーティションは水平シャーディングの基本単位であり、データの分散、ロードバランシング、パラレル処理を行う最小の物理単位です。大規模なテーブルは論理的に複数のより小さく管理しやすい独立したブロックに分割され、各パーティション（あるいはパーティションの別のレプリカ）はクラスタ内の別のOBServerノードに分散して格納することができます。

この設計は分析系業務において根本的な利点をもたらします。単一ノードのストレージまたは計算能力がボトルネックに達した場合、ノードを追加してパーティションを再分散するだけで、ほぼ線形の水平スケーリングを実現し、PB級のデータ規模にも簡単に対応できるようになります。

パーティションプルーニングによるクエリ効率の向上

パーティションを使用すると、特定のパーティション列でクエリを実行する際、場合によってはクエリ条件を満たすパーティションのみを絞り込むことができ、条件を満たさないパーティションを検索する必要がなくなります。

以下の例を参照してください。列 c2 に基づいてハッシュパーティションを作成し、c2 = 1 というクエリ条件を指定することで、p1 パーティションのみを検索するように絞り込むことができます。

-- 4つのハッシュパーティションを持つテーブルt1を作成します。パーティションキーはC2です。
CREATE TABLE t1(c1 INT, c2 INT) PARTITION BY HASH(c2) PARTITIONS 4;

-- c2 = 1 と指定してクエリを実行することで、p1 パーティションに絞り込みます。
EXPLAIN SELECT * FROM t1 WHERE c2 = 1;

実行計画の出力は次のとおりです：

+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ===============================================                                    |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -----------------------------------------------                                    |
| |0 |TABLE FULL SCAN|t1  |1       |3           |                                    |
| ===============================================                                    |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1]), rowset=16                     |
|       access([t1.c2], [t1.c1]), partitions(p1)                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |
+------------------------------------------------------------------------------------+

パーティションプルーニングは不要なデータをフィルタリングできますが、パーティション数が多すぎるとメタデータ量の増加やパーティションプルーニングの効率低下など、他の問題が発生する可能性があります。そのため、OceanBaseのカラムストアテーブルでは、単一パーティションの行数を10万行以上に設定することを推奨します。

データメンテナンス単位としてのパーティション

データベースの運用保守において、パーティションを基本的なデータメンテナンス単位として扱うことで、データクリーンアップやパーティション単位での統計情報収集などの日常管理プロセスを大幅に簡素化できます。

データクリーンアップのシナリオを例にとると、データを時間でパーティション分けした後に、期限切れデータのクリーンアップをする場合は行ごとの削除ではなく、過去のパーティション全体を直接削除することでクリーンアップできます。この操作はメタデータの変更のみで済み、ディスク容量を完全に解放できるため、従来のDMLによる削除操作が生じさせるパフォーマンスオーバヘッドを回避できます。

パーティションキー（例：時間）によってデータを自然に分類することで、メンテナンス操作が「行ごとのスキャン」から「バッチ処理」へと変わり、管理効率が大幅に向上し、運用保守の複雑さが低減します。

データ分布単位としてのパーティション

パーティションはOceanBaseのデータ分散単位であり、各パーティションのレプリカを別のOBServerノードに配置することで、ストレージと計算の拡張を実現します。

ストレージの拡張： パーティションテーブルを作成すると、これらのパーティション及びそのレプリカは、クラスタのリソース状況に応じてOceanBaseによって自動的に別の物理ノードにスケジューリングされます。これにより、単一テーブルの容量は単一マシンのディスクに制限されなくなり、クラスタ全体のストレージ容量に依存するようになります。クラスタのストレージ容量が不足した場合は、ノードを追加することで拡張できます。
計算のパラレル化： これは分析系業務における高性能化の鍵となる要素の一つです。クエリ（特に全表スキャンや大規模集計を伴うクエリ）が実行される際、OceanBaseのオプティマイザーはクエリに関連するパーティションを特定します。クエリタスクは複数のサブタスクに分解され、各データパーティションが配置されているノードにプッシュダウンされてパラレル実行されます。例えば、SUM() 演算では、まず各パーティションでローカルに小計を計算し、その後中間結果を集約して最終的な合計を得るため、複数ノードの計算能力を最大限活用し、クエリを大幅に高速化します。

OceanBaseの基本的なパーティショニング方法

現在、OceanBaseではHash/Key、Range/Range Columns、List/List Columnsの3種類の基本的なパーティショニング方法をサポートしています。これら3つの方法はそれぞれ使用シナリオが異なります。

HASH/KEYパーティション

一般的に、パーティション列のNDV（異なる値の種類）が大きく、明確な範囲に分割することが難しい場合に適しています。利点は、特定のルールがないデータでも異なるパーティションに均等に分散させやすいことです。欠点は、範囲クエリ時のパーティションプルーニングが困難であることです。

適用シナリオ例：

明確なクエリパターンがなく、データを複数ノードに均等に分散させる必要がある場合（ユーザーID、取引IDなど）。

設計ポイント：

パーティションキーの選定：
- NDV（一意の値の数）がパーティション数よりもはるかに大きいこと（例：ユーザーIDのNDVはパーティション数よりもはるかに大きくなるはずです）。
- 偏りがない（またはわずかな偏りがある）整数型／時間列を優先的に選択します（例：user_id、order_time、または自動インクリメント列）。
- 頻繁にクエリされる条件フィールド（例：user_idがJoinキーワードとして使用される場合）。
推奨されるパーティション数：
- リソースの不均衡な割り当てを避けるため、パーティション数がクラスタのマシン数に一致するようにします。

実施例：

-- Hashパーティションを使用し、user_idに基づいて均等に分散します
CREATE TABLE customer (
  user_id BIGINT NOT NULL,
  login_time TIMESTAMP NOT NULL,
  customer_name VARCHAR(100) NOT NULL,
  phone_num BIGINT NOT NULL,
  city_name VARCHAR(50) NOT NULL,
  sex INT NOT NULL,
  id_number VARCHAR(18) NOT NULL,
  home_address VARCHAR(255) NOT NULL,
  office_address VARCHAR(255) NOT NULL,
  age INT NOT NULL
)
PARTITION BY HASH(user_id) PARTITIONS 128;

Range/Range Columnsパーティション

一般的に、パーティションキーが明確な範囲に分割しやすい場合に適しています。例えば、フロー情報を記録する大規模テーブルでは、情報の時間を表す列に基づいてRANGEパーティションを作成できます。

適用シナリオの例：

データが時間または数値の範囲に従って増加する場合（例：order_time、price）。
過去のデータを迅速に絞り込む必要がある場合（例：直近1か月分のデータのみをクエリする場合）。

設計上のポイント：

パーティションキーの選択：
- 時間フィールド（例：order_time）または連続した数値フィールド。
- パーティション境界は業務上のクエリ条件と整合させる必要があります（例：日/月単位で分割する場合）。
推奨されるパーティション数：
- 月単位など、データの増加量に応じてパーティションを設定します。

使用例：

-- システムログテーブルを作成し、ログ時間に基づいて月単位のRANGEパーティションを行い、高速なクエリとデータアーカイブをサポートします
CREATE TABLE system_logs (
    log_id BIGINT,
    log_date TIMESTAMP NOT NULL,
    log_level VARCHAR(10),
    source_system VARCHAR(50),
    user_id BIGINT,
    log_message TEXT,
    client_ip VARCHAR(15)
)
-- プライマリパーティション：月単位のRANGEパーティションにより、日付を直接使用してパーティション境界を表現します
PARTITION BY RANGE COLUMNS(log_date) (
    PARTITION p_202001 VALUES LESS THAN ('2020-02-01'),
    PARTITION p_202002 VALUES LESS THAN ('2020-03-01'),
    PARTITION p_202003 VALUES LESS THAN ('2020-04-01'),
    PARTITION p_202004 VALUES LESS THAN ('2020-05-01'),
    PARTITION p_202005 VALUES LESS THAN ('2020-06-01'),
    PARTITION p_202006 VALUES LESS THAN ('2020-07-01'),
    PARTITION p_202007 VALUES LESS THAN ('2020-08-01'),
    PARTITION p_202008 VALUES LESS THAN ('2020-09-01'),
    PARTITION p_202009 VALUES LESS THAN ('2020-10-01'),
    PARTITION p_202010 VALUES LESS THAN ('2020-11-01'),
    PARTITION p_202011 VALUES LESS THAN ('2020-12-01'),
    PARTITION p_202012 VALUES LESS THAN ('2021-01-01'),
    -- デフォルトパーティションは未来のデータや時刻形式が例外のレコードを処理します
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

LIST/LIST COLUMNSパーティション

各行のデータを特定のパーティションに明示的にマッピングする必要がある場合に一般的に適用されます。利点は、無秩序または関連性のないデータセットに対して精密なパーティショニングができることです。欠点は、範囲クエリ時のパーティションプルーニングが困難であることです。

適用シナリオ例：

离散型フィールド（地域、チャネルタイプなど）。
固定カテゴリに基づいてデータを迅速にプルーニングする必要がある場合（例：華東地区のユーザーをクエリする場合）。

設計ポイント：

パーティションキーの選択：
- 离散値であり、数が限られている場合（例：region フィールドには ['east','west','south','north'] のみが含まれます）。
- パーティション値はすべての可能な値を網羅しており、漏れがないようにする必要があります。
パーティション数の制限：
- 業務ロジックに基づいてパーティション数を設定します。

使用例：

CREATE TABLE orders_by_region (
    order_id BIGINT COMMENT '注文の一意の識別子',
    region_code INT NOT NULL PRIMARY KEY  COMMENT '地域コード（1=north/china, 2=east/china, 3=south/china, 4=west/china）',
    customer_id BIGINT COMMENT '顧客ID',
    order_time DATETIME COMMENT '注文作成日時',
    product_category VARCHAR(50) COMMENT '商品カテゴリ',
    order_amount DECIMAL(18,2) COMMENT '注文金額',
    payment_status VARCHAR(20) COMMENT '支払状況（例：PAID, UNPAID）'
)
PARTITION BY LIST(region_code)  -- 整数型のパーティションキーに変更
(
    PARTITION p_north VALUES IN (1),  -- 地域コード1はnorth/chinaに対応します
    PARTITION p_east VALUES IN (2),
    PARTITION p_south VALUES IN (3),
    PARTITION p_west VALUES IN (4),
    PARTITION p_other VALUES IN (DEFAULT)  -- デフォルトパーティションは未知の地域を処理します
);

柔軟なパーティション管理機能

OceanBaseは非常に柔軟なパーティション管理機能を備えています。データ管理の観点から見ると、データのメンテナンス機能とデータの分散機能の両方を備えています。使用方法については、手動管理と自動管理の2種類があります。パーティションの階層を考慮すると、メインパーティションとサブパーティションの組み合わせ使用をサポートしています。これらのさまざまな組み合わせにより、ユーザーのさまざまなデータ管理ニーズに対応できます。

このセクションでは、データメンテナンスとデータ分散の2つの観点から説明し、それぞれの観点における使用方法とパーティション階層の機能組み合わせについても考察します。

データメンテナンス

業務層では通常、時間軸に沿ってパーティションを管理し、データのアーカイブやクリーンアップなどの操作を容易にします。ここでは、業務のデータライフサイクルプロセス全体と組み合わせて、手動でのパーティション管理機能を説明します：

業務によるテーブル作成： 時間に基づいてパーティション化されたテーブルを作成し、将来一定期間に必要なパーティションを事前に作成します。
業務によるデータインポート： データをインポートします。
業務の運用： 時間の経過に伴い、事前に作成したパーティションが足りなくなる可能性があるため、さらに先の期間に必要なパーティションを事前に作成し続けます。
定期的なデータクリーンアップ： データが一定期間蓄積されると、以前のデータが不要になる場合があります。その際、不要なパーティションを削除できます。

例：

-- 1. パーティションテーブルの作成（日単位でパーティション化し、今後7日間のパーティションを事前作成）します
CREATE TABLE business_data (
    id BIGINT NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    metric_value DECIMAL(10,2),
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE COLUMNS(event_time) (
    PARTITION p20231025 VALUES LESS THAN ('2023-10-26'),
    PARTITION p20231026 VALUES LESS THAN ('2023-10-27'),
    PARTITION p20231027 VALUES LESS THAN ('2023-10-28'),
    PARTITION p20231028 VALUES LESS THAN ('2023-10-29'),
    PARTITION p20231029 VALUES LESS THAN ('2023-10-30'),
    PARTITION p20231030 VALUES LESS THAN ('2023-10-31'),
    PARTITION p20231031 VALUES LESS THAN ('2023-11-01')  -- 今後7日間のパーティションを事前作成します
);
-- 2. データのインポート、ここでは省略します
-- 3. 今後7日間のパーティションを事前作成します
ALTER TABLE business_data ADD PARTITION (
  PARTITION p20231101 VALUES LESS THAN ('2023-11-02'),
  PARTITION p20231102 VALUES LESS THAN ('2023-11-03'),
  PARTITION p20231103 VALUES LESS THAN ('2023-11-04'),
  PARTITION p20231104 VALUES LESS THAN ('2023-11-05'),
  PARTITION p20231105 VALUES LESS THAN ('2023-11-06'),
  PARTITION p20231106 VALUES LESS THAN ('2023-11-07'),
  PARTITION p20231107 VALUES LESS THAN ('2023-11-08')
);

-- 4. 定期的なデータクリーンアップ、例えばデータの有効期限後に7日間のデータを削除します
ALTER TABLE business_data DROP PARTITION p20231025, p20231026, p20231027, p20231028, p20231029, p20231030, p20231031;

データは絶えず書き込まれるため、事前に作成したパーティションの手動メンテナンスや定期的なクリーンアップを行うのは、かなり手間が掛かります。このプロセスを効率化するために、OceanBaseは 動的パーティション機能 を提供しており、固定時間でのパーティション化、事前作成する期間の設定、保持する履歴パーティションの期間設定などの機能をサポートしています。

上記の例で、30日間のデータを保持し、毎回7日間のパーティションを事前作成する場合、以下の構文を使用して作成します：

-- 1. パーティションテーブルを作成し、動的パーティション戦略を設定します
CREATE TABLE t1 (
    id BIGINT NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    metric_value DECIMAL(10,2),
    PRIMARY KEY (id, event_time))
DYNAMIC_PARTITION_POLICY
(
  ENABLE = true,
  TIME_UNIT = 'day',
  PRECREATE_TIME = '7day',
  EXPIRE_TIME = '30day'
)
PARTITION BY RANGE COLUMNS (event_time)(
  PARTITION p20231025 VALUES LESS THAN ('2023-10-26'));

Rangeパーティションモード以外にも、業務ニーズに応じて他の基本的なパーティション方法を選択できます。

動的パーティションの詳細については、動的パーティション、MySQLモードでの動的パーティションテーブルの作成およびOracleモードでの動的パーティションテーブルの作成を参照してください。

データ分散

パーティションはデータ分布管理の単位としても利用できます。通常、データの分散のためにHASHパーティション方法が用いられ、その利点は以下の通りです：

一般的にデータの均等な分布を効果的に実現でき、パーティションキーに基づく精密なパーティションプルーニングをサポートします。
JOIN操作を必要とする複数のテーブルがあり、それぞれが同じJOINキーでHASHパーティションされ、かつパーティション数が一致している場合、OceanBaseのTable Group機能を組み合わせることで、各テーブルのハッシュ値が同じパーティションを同一ノードグループにバインドし、JOIN時にPartition-Wise Joinを有効化することができます。これにより、ノード間のデータShuffleを回避し、クエリ性能を大幅に向上させることができます。

HASHパーティションには制限もあります：

HASHパーティションのパーティション数は設定後、変更するのは比較的大変な操作で、テーブル全体のデータ書き直しが伴います。そのため、通常は初期設定後にHASHパーティション数が変更されることはなく、拡張性の実現が難しいという問題があります。
パーティションキーに対する範囲クエリでは、パーティションをプルーニングできず、すべてのパーティションにアクセスする必要があるため、読み込み増幅（read amplification）が発生する可能性があります。

HASHパーティションの拡張性と範囲クエリの問題を解決するため、OceanBaseは行ストアテーブルの自動パーティション分割・拡張機能をサポートしています。

データのメンテナンスとデータ分散管理のハイブリッド

データメンテナンスとデータ分散の両方の要件を同時にサポートするために、サブパーティションを使用することもできます。一般的によく使われるシナリオは、メインパーティションをデータメンテナンスに、サブパーティションをデータ分散に使用する場合です。各要件に応じて、対応する方法を組み合わせて利用できます。

典型的な手動パーティション管理方法

メインパーティション：
- タイプの選択：RangeまたはListパーティションを使用し、頻繁にクエリされる条件（時間範囲や地域など）に合わせます。
- パーティション数の推奨：クエリ条件の時間的分布やデータメンテナンスの要件に基づいて、適切な範囲を設定します（例：月単位でパーティションを分割し12ヶ月分を保持、または地域ごとに4つのListパーティションに分割）。
サブパーティション：
- タイプの選択：Hashパーティションを使用し、データの分散を保証します。
- 推奨サブパーティション数：
  - 一つのメインパーティションのみに書き込む場合、そのメインパーティションのサブパーティション数は、書き込みの分散に必要なリソース要件を満たす必要があります。
  - 複数のメインパーティションへの書き込みが可能な場合、書き込み可能なメインパーティション数 × サブパーティション数が書き込みの分散に必要なリソース要件を満たせば問題ありません。

以下はRange + HashとList + Hashの2つのシナリオ例です：

Range + Hash

パーティションではRangeパーティションを選択し、order_dateを指定することで、データスキャンが不要なパーティションを迅速に除外でき、パーティション管理操作によるデータメンテナンスも迅速に行えます。サブパーティションではHashパーティションを選択することで、当月の書き込みや読み取りを8つのパーティションに分散させ、ホットスポットを回避できます。

CREATE TABLE orders (
    user_id BIGINT NOT NULL COMMENT 'ユーザーID（サブパーティションキー）',
    order_date DATE NOT NULL COMMENT '注文日（メインパーティションキー）',
    amount DECIMAL(10,2) NOT NULL COMMENT '注文金額',
    status TINYINT NOT NULL COMMENT 'ステータス: 0-キャンセル 1-未払い 2-支払済み 3-出荷済み 4-完了',
    region_code CHAR(6) NOT NULL COMMENT '地域コード（最初の2文字は省コード）',
    product_id INT NOT NULL COMMENT '商品ID',
    payment_method VARCHAR(20) COMMENT '支払方法',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'レコード作成日時')
PARTITION BY RANGE COLUMNS(order_date)
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8
(
  PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
  PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
  ...
  PARTITION p202601 VALUES LESS THAN ('2026-02-01')
);

List + Hash

メインではListパーティションを選択し、省を指定することで該当するパーティションにトリミングでき、省単位でのデータメンテナンスも可能です。サブパーティションではHash / Keyパーティションを選択することで、省単位の読み書きトラフィックを複数のパーティションに分散させ、ロードバランシングを実現できます。

-- メインパーティション：LISTは省単位で分割（31の省級行政区）
CREATE TABLE social_insurance_records (
    record_id BIGINT,
    province_code INT NOT NULL,  -- 省級コード（例：11は北京、31は上海）
    payment_date DATE NOT NULL,
    user_id VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY LIST (province_code)  -- メインLISTパーティション
SUBPARTITION BY KEY(user_id) SUBPARTITIONS 16  -- サブHASHパーティション
(
  PARTITION p_beijing VALUES IN (11),
  PARTITION p_shanghai VALUES IN (31),
  PARTITION p_tianjin VALUES IN (12),
  ...
  PARTITION p_xizang VALUES IN (54)
);

典型的な自動パーティション管理方法

メインパーティション： 動的パーティションを選択し、固定時間でのパーティション分割、事前作成する期間のパーティション、保持する履歴パーティションの期間などのパラメータを設定します。
サブパーティション： 自動Rangeサブパーティション分割を選択すると、自動的に分割が行われ、サブパーティションの数やルールを設定する必要がありません。