🎃

TiDB のパーティショニングその２：List、List Columns

2025/03/07に公開

前回の記事では範囲、範囲列、範囲インターバルという３つのパーティショニングを見ていきました。今日はList、List Columnsの２つを見ていきます。

TiDB におけるパーティショニングの有益性

TiDBはMySQL互換ですが、NewSQLにカテゴライズされる分散データベースです。（というよりその潮流を起こした、という方がいいのかもしれないです）
先日開催されたJAWS DAYS 2025のセッションここがつらいよ分散データベースで、PingCAPの中の人小板橋さんの資料がわかりやすかったので共有します。

通常のリレーショナルデータベースにおけるパーティションの一つのメリットとしてスケーラビリティがあります。特定テーブルにデータが大量に蓄積されている場合、クエリの負荷をパーティション単位で分散できます。一方TiDBはもともとが分散データベースなのでデフォルトでクラスターの複数ノードに処理は分散されます。このためTiDBのドキュメントではパーティションのメリットを以下と記載しています。

範囲パーティション分割、範囲列パーティション分割、List パーティショニング、およびList COLUMNS パーティショニングは、アプリケーションでの大量の削除によって発生するパフォーマンスの問題を解決し、パーティションの迅速な削除をサポートするために使用されます。

つまり通常のRDBMSのおけるパーティションのメリットの一つであるクエリの最適化そのものを謳ってはおらず、削除の高速化をメリットとしています。とはいえテーブルがパーティション分割されていれば、以下のようにSELECTクエリで明示的に特定パーティションへのクエリを実行できますので、そのあたりにおける処理負荷軽減のメリットは期待できると思います。

SELECT * FROM orders PARTITION (p2022) WHERE order_date = '2022-05-10';

さっそくやってみる

今日はまずListパーティショニングを見ていきます。
範囲パーティションと異なりまずはシステム変数レベルでその機能を有効化させる必要があります。

set tidb_enable_list_partition=ON;
SHOW VARIABLES LIKE 'tidb_enable_list_partition';

tidb_enable_table_partitionも同じように有効化が必要なのですが、これはデフォルトでオンになっているはずです。

ではListパーティション付きのテーブルを作成していきます。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    department_id INT,
    hire_date DATE
) 
PARTITION BY LIST (department_id) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4)
);

この場合4つのDepartment（部署)ごとにパーティションが作成されます。

INSERT INTO employees (employee_id, name, department_id, hire_date) VALUES
(1, 'Alice', 1, '2023-01-15'),
(2, 'Bob', 2, '2022-07-23'),
(3, 'Charlie', 3, '2021-03-11'),
(4, 'David', 4, '2020-05-30');

このINSERT分は１個のパーティションに１個のデータをINSERTします。(department_idが1,2,3,4と１個指定されているため）
パーティションが存在しないため以下のSQLは失敗します。

INSERT INTO employees (employee_id, name, department_id, hire_date) VALUES
(5, 'Kame', 5, '2023-02-15');

ALTER TABLE employees ADD PARTITION (PARTITION Def DEFAULT);

このようにフォールバックパーティション（どこにもマッチしない場合に使われるパーティション）を指定しておけば先ほどのSQLは実行が成功されListパーティションDefにKameは格納されます）
デフォルトパーティションを作成したくない場合、つまりパーティションが存在しないデータのINSERTが発生した場合、意図的に失敗させたい場合IGNOREオプションというものもあります。

INSERT IGNORE INTO employees (employee_id, name, department_id, hire_date) VALUES
(1, 'Alice', 1, '2023-01-15'),
(2, 'Bob', 2, '2022-07-23'),
(3, 'Charlie', 3, '2021-03-11'),
(4, 'David', 4, '2020-05-30');
(5, 'Kame', 5, '2023-02-15');

とした場合KameだけINSERTが失敗します。

Partitionの条件には複数の値を含めることもできますが、値は重複できません。例えば以下のSQLは成功します。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    department_id INT,
    hire_date DATE
) 
PARTITION BY LIST (department_id) (
    PARTITION p1 VALUES IN (1,5),
    PARTITION p2 VALUES IN (2,6,7),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4)
    PARTITION Def DEFAULT
);

一方以下は失敗になります。Listパーティションの条件が重複するためです。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    department_id INT,
    hire_date DATE
) 
PARTITION BY LIST (department_id) (
    PARTITION p1 VALUES IN (1,5),
    PARTITION p2 VALUES IN (2,6,7),
    PARTITION p3 VALUES IN (3,4),
    PARTITION p4 VALUES IN (4)
    PARTITION Def DEFAULT
);

指定したリスト配列が重複しているためです。

LIST Columns パーティション

LIST ColumnsパーティションはLISTパーティションの派生です。LISTパーティションと異なり、整数列以外のカラムをして可能で、かつ、複数カラムのAND条件指定が可能です。

CREATE TABLE sales (
    id INT NOT NULL,
    product_name VARCHAR(50),
    amount DECIMAL(10,2),
    region VARCHAR(10),
    sales_date DATE,
    PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_east VALUES IN ('East', 'Northeast'),
    PARTITION p_west VALUES IN ('West', 'Southwest'),
    PARTITION p_central VALUES IN ('Central', 'Midwest'),
    PARTITION p_other VALUES IN (DEFAULT)
);

このように文字列のカラムを対象にすることが可能です。また日付カラムを条件として追記し、範囲パーティションと同様に関数指定も可能です。

CREATE TABLE sales (
    id INT NOT NULL,
    product_name VARCHAR(50),
    amount DECIMAL(10,2),
    region VARCHAR(10),
    sales_date DATE,
    PRIMARY KEY (id, region, sales_date)
)
PARTITION BY LIST COLUMNS(region, YEAR(sales_date)) (
    PARTITION p_east_2023 VALUES IN (('East', 2023), ('Northeast', 2023)),
    PARTITION p_east_2024 VALUES IN (('East', 2024), ('Northeast', 2024)),
    PARTITION p_west_2023 VALUES IN (('West', 2023), ('Southwest', 2023)),
    PARTITION p_west_2024 VALUES IN (('West', 2024), ('Southwest', 2024)),
    PARTITION p_central_2023 VALUES IN (('Central', 2023), ('Midwest', 2023)),
    PARTITION p_central_2024 VALUES IN (('Central', 2024), ('Midwest', 2024))
);

TiDB におけるパーティショニングの有益性

さっそくやってみる

LIST Columns パーティション

Discussion