パーティショニング

このドキュメントでは、TiDB のパーティショニングの実装について紹介します。

パーティショニングの種類

このセクションでは、TiDB のパーティショニングの種類を紹介します。現在、TiDB は範囲分割 、 範囲 COLUMNS パーティショニング 、 List パーティショニング 、 List COLUMNS パーティショニング 、およびハッシュパーティショニングをサポートしています。

範囲パーティション化、範囲 COLUMNS パーティション化、List パーティショニング、およびList COLUMNS パーティショニングを使用して、アプリケーションでの大量の削除によって引き起こされるパフォーマンスの問題を解決し、パーティションの高速削除操作をサポートします。ハッシュ パーティショニングは、大量の書き込みがある場合にデータを分散させるために使用されます。

範囲分割

テーブルが範囲でパーティション分割されている場合、各パーティションには、パーティション式の値が特定の範囲内にある行が含まれます。範囲は連続している必要がありますが、重複してはなりません。 VALUES LESS THANを使用して定義できます。

次のように、人事レコードを含むテーブルを作成する必要があるとします。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
);

必要に応じて、さまざまな方法で範囲によってテーブルを分割できます。たとえば、 store_id列を使用して分割できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

このパーティション・スキームでは、 store_idが 1 から 5 である従業員に対応するすべての行がp0パーティションに格納され、 store_idが 6 から 10 であるすべての従業員がp1に格納されます。レンジ・パーティション化では、パーティションを最低から最高の順に並べる必要があります。

データ(72, 'Tom', 'John', '2015-06-25', NULL, NULL, 15)の行を挿入すると、それはp2パーティションに分類されます。しかし、 store_idが 20 より大きいレコードを挿入すると、エラーが報告されます。これは、TiDB がこのレコードを挿入する必要があるパーティションを認識できないためです。この場合、テーブルを作成するときにMAXVALUEを使用できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUEは、他のすべての整数値より大きい整数値を表します。これで、 store_idが 16 (定義された最大値) 以上のすべてのレコードがp3パーティションに格納されます。

従業員の職務コード ( job_code列の値) でテーブルを分割することもできます。 2 桁のジョブ コードは正社員、3 桁のコードはオフィスおよびカスタマー サポート担当者、4 桁のコードは管理職を表すとします。次に、次のようにパーティション分割されたテーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

この例では、正社員に関連するすべての行がp0パーティションに格納され、すべてのオフィスおよび顧客サポート担当者がp1パーティションに格納され、すべての管理職がp2パーティションに格納されます。

テーブルをstore_idで分割する以外に、テーブルを日付で分割することもできます。たとえば、従業員の離職年で分割できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

範囲分割では、 timestamp列の値に基づいて分割し、 unix_timestamp()関数を使用できます。次に例を示します。

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

タイムスタンプ列を含む他のパーティション式を使用することはできません。

レンジ パーティション分割は、次の条件の 1 つ以上が満たされている場合に特に役立ちます。

• 古いデータを削除したい。前の例でemployeesテーブルを使用すると、 ALTER TABLE employees DROP PARTITION p0;を使用するだけで、1991 年より前にこの会社を退職した従業員のすべてのレコードを削除できます。 DELETE FROM employees WHERE YEAR(separated) <= 1990;の操作を実行するよりも高速です。
• 時刻または日付の値を含む列、または他の系列から生じる値を含む列を使用したい。
• パーティション分割に使用される列に対してクエリを頻繁に実行する必要があります。たとえば、 EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;のようなクエリを実行すると、TiDB はp2パーティションのデータのみをスキャンする必要があることをすぐに認識できます。これは、他のパーティションがWHERE条件に一致しないためです。

範囲 COLUMNS パーティショニング

レンジ COLUMNS パーティショニングは、レンジ パーティショニングの一種です。パーティション分割キーとして 1 つ以上の列を使用できます。パーティション列のデータ型は、整数、文字列 ( CHARまたはVARCHAR )、 DATE 、およびDATETIMEです。非 COLUMNS パーティショニングなどの式はサポートされていません。

名前でパーティション化し、古い無効なデータを削除すると仮定すると、次のようにテーブルを作成できます。

CREATE TABLE t (
  valid_until datetime,
  name varchar(255) CHARACTER SET ascii,
  notes text
)
PARTITION BY RANGE COLUMNS(name,valid_until)
(PARTITION `p2022-g` VALUES LESS THAN ('G','2023-01-01 00:00:00'),
 PARTITION `p2023-g` VALUES LESS THAN ('G','2024-01-01 00:00:00'),
 PARTITION `p2024-g` VALUES LESS THAN ('G','2025-01-01 00:00:00'),
 PARTITION `p2022-m` VALUES LESS THAN ('M','2023-01-01 00:00:00'),
 PARTITION `p2023-m` VALUES LESS THAN ('M','2024-01-01 00:00:00'),
 PARTITION `p2024-m` VALUES LESS THAN ('M','2025-01-01 00:00:00'),
 PARTITION `p2022-s` VALUES LESS THAN ('S','2023-01-01 00:00:00'),
 PARTITION `p2023-s` VALUES LESS THAN ('S','2024-01-01 00:00:00'),
 PARTITION `p2024-s` VALUES LESS THAN ('S','2025-01-01 00:00:00'),
 PARTITION `p2022-` VALUES LESS THAN (0x7f,'2023-01-01 00:00:00'),
 PARTITION `p2023-` VALUES LESS THAN (0x7f,'2024-01-01 00:00:00'),
 PARTITION `p2024-` VALUES LESS THAN (0x7f,'2025-01-01 00:00:00'))

データは、['', 'G')、['G', 'M')、['M', 'S') および ['S',)] の範囲の年および名前で分割されます。これにより、 name列とvalid_until列の両方でパーティションのプルーニングを利用しながら、無効なデータを簡単に削除できます。この例では、 [,)は左が閉じ、右が開いている範囲を示します。たとえば、 ['G', 'M') は、 GとGからMを含み、 Mを除く範囲を示します。

範囲 INTERVAL パーティショニング

Range INTERVAL パーティショニングは、Range パーティショニングを拡張したもので、指定した間隔のパーティションを簡単に作成できます。 v6.3.0 から、INTERVAL パーティショニングがシンタックス シュガーとして TiDB に導入されました。

構文は次のとおりです。

PARTITION BY RANGE [COLUMNS] (<partitioning expression>)
INTERVAL (<interval expression>)
FIRST PARTITION LESS THAN (<expression>)
LAST PARTITION LESS THAN (<expression>)
[NULL PARTITION]
[MAXVALUE PARTITION]

例えば：

CREATE TABLE employees (
    id int unsigned NOT NULL,
    fname varchar(30),
    lname varchar(30),
    hired date NOT NULL DEFAULT '1970-01-01',
    separated date DEFAULT '9999-12-31',
    job_code int,
    store_id int NOT NULL
) PARTITION BY RANGE (id)
INTERVAL (100) FIRST PARTITION LESS THAN (100) LAST PARTITION LESS THAN (10000) MAXVALUE PARTITION

次の表が作成されます。

CREATE TABLE `employees` (
  `id` int unsigned NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date DEFAULT '9999-12-31',
  `job_code` int DEFAULT NULL,
  `store_id` int NOT NULL
)
PARTITION BY RANGE (`id`)
(PARTITION `P_LT_100` VALUES LESS THAN (100),
 PARTITION `P_LT_200` VALUES LESS THAN (200),
...
 PARTITION `P_LT_9900` VALUES LESS THAN (9900),
 PARTITION `P_LT_10000` VALUES LESS THAN (10000),
 PARTITION `P_MAXVALUE` VALUES LESS THAN (MAXVALUE))

範囲 INTERVAL パーティショニングは、 範囲列パーティショニングでも機能します。

例えば：

CREATE TABLE monthly_report_status (
    report_id int NOT NULL,
    report_status varchar(20) NOT NULL,
    report_date date NOT NULL
)
PARTITION BY RANGE COLUMNS (report_date)
INTERVAL (1 MONTH) FIRST PARTITION LESS THAN ('2000-01-01') LAST PARTITION LESS THAN ('2025-01-01')

次のテーブルを作成します。

CREATE TABLE `monthly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(`report_date`)
(PARTITION `P_LT_2000-01-01` VALUES LESS THAN ('2000-01-01'),
 PARTITION `P_LT_2000-02-01` VALUES LESS THAN ('2000-02-01'),
...
 PARTITION `P_LT_2024-11-01` VALUES LESS THAN ('2024-11-01'),
 PARTITION `P_LT_2024-12-01` VALUES LESS THAN ('2024-12-01'),
 PARTITION `P_LT_2025-01-01` VALUES LESS THAN ('2025-01-01'))

オプションのパラメーターNULL PARTITIONは、定義がPARTITION P_NULL VALUES LESS THAN (<minimum value of the column type>)のパーティションを作成し、パーティション式がNULLに評価された場合にのみ一致します。 レンジ分割でのNULLの扱いを参照してください。これは、 NULLが他のどの値よりも小さいと見なされることを説明しています。

オプションのパラメーターMAXVALUE PARTITIONは、最後のパーティションをPARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)として作成します。

ALTER INTERVAL 分割テーブル

INTERVAL パーティショニングは、パーティションを追加および削除するためのより単純な構文も追加します。

次のステートメントは、最初のパーティションを変更します。値が指定された式より小さいすべてのパーティションを削除し、一致したパーティションを新しい最初のパーティションにします。 NULL PARTITION には影響しません。

ALTER TABLE table_name FIRST PARTITION LESS THAN (<expression>)

次のステートメントは、最後のパーティションを変更します。つまり、より高い範囲と新しいデータ用のスペースを持つパーティションをさらに追加します。指定された式までの現在の間隔で新しいパーティションを追加します。データの再編成が必要なため、 MAXVALUE PARTITIONが存在する場合は機能しません。

ALTER TABLE table_name LAST PARTITION LESS THAN (<expression>)

INTERVAL パーティショニングの詳細と制限

• INTERVAL パーティショニング機能には、 CREATE/ALTER TABLE構文のみが含まれます。メタデータに変更はないため、新しい構文で作成または変更されたテーブルは引き続き MySQL と互換性があります。
• MySQL の互換性を維持するために、 SHOW CREATE TABLEの出力形式に変更はありません。
• 新しいALTER構文は、INTERVAL に準拠する既存のテーブルに適用されます。これらのテーブルをINTERVAL構文で作成する必要はありません。
• RANGE COLUMNSの場合、integer、date、および datetime 列タイプのみがサポートされます。

List パーティショニング

リスト分割テーブルを作成する前に、セッション変数tidb_enable_list_partitionの値をONに設定する必要があります。

set @@session.tidb_enable_list_partition = ON

また、 tidb_enable_table_partitionがデフォルト設定のONに設定されていることを確認してください。

List パーティショニングは、レンジ パーティショニングに似ています。レンジ パーティション分割とは異なり、List パーティショニングでは、各パーティション内のすべての行のパーティション式の値が特定の値セットに含まれます。各パーティションに定義されたこの値セットは、任意の数の値を持つことができますが、重複する値を持つことはできません。 PARTITION ... VALUES IN (...)句を使用して、値セットを定義できます。

人事記録表を作成するとします。次のようにテーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
);

次の表に示すように、4 つの地区に 20 の店舗が分布しているとします。

| Region  | Store ID Numbers     |
| ------- | -------------------- |
| North   | 1, 2, 3, 4, 5        |
| East    | 6, 7, 8, 9, 10       |
| West    | 11, 12, 13, 14, 15   |
| Central | 16, 17, 18, 19, 20   |

同じ地域の従業員の人事データを同じパーティションに格納する場合は、 store_idに基づいてリスト パーティション テーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
)
PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1, 2, 3, 4, 5),
    PARTITION pEast VALUES IN (6, 7, 8, 9, 10),
    PARTITION pWest VALUES IN (11, 12, 13, 14, 15),
    PARTITION pCentral VALUES IN (16, 17, 18, 19, 20)
);

上記のようにパーティションを作成した後、テーブル内の特定の地域に関連するレコードを簡単に追加または削除できます。たとえば、East リージョン (East) のすべての店舗が別の会社に売却されたとします。次に、この地域の店舗従業員に関連するすべての行データをALTER TABLE employees TRUNCATE PARTITION pEastを実行することで削除できます。これは、同等のステートメントDELETE FROM employees WHERE store_id IN (6, 7, 8, 9, 10)よりもはるかに効率的です。

ALTER TABLE employees DROP PARTITION pEastを実行して関連するすべての行を削除することもできますが、このステートメントはテーブル定義からpEastパーティションも削除します。この状況では、 ALTER TABLE ... ADD PARTITIONステートメントを実行して、テーブルの元のパーティション スキームを復元する必要があります。

レンジ パーティション分割とは異なり、List パーティショニングには、他のパーティションに属さないすべての値を格納するための同様のMAXVALUEつのパーティションはありません。代わりに、パーティション式のすべての予期される値をPARTITION ... VALUES IN (...)句に含める必要があります。 INSERTステートメントに挿入される値がどのパーティションの列値セットとも一致しない場合、ステートメントは実行に失敗し、エラーが報告されます。次の例を参照してください。

test> CREATE TABLE t (
    ->   a INT,
    ->   b INT
    -> )
    -> PARTITION BY LIST (a) (
    ->   PARTITION p0 VALUES IN (1, 2, 3),
    ->   PARTITION p1 VALUES IN (4, 5, 6)
    -> );
Query OK, 0 rows affected (0.11 sec)

test> INSERT INTO t VALUES (7, 7);
ERROR 1525 (HY000): Table has no partition for value 7

上記のエラー タイプを無視するには、 IGNOREキーワードを使用できます。このキーワードを使用した後、どのパーティションの列値セットとも一致しない値が行に含まれている場合、この行は挿入されません。代わりに、値が一致する行が挿入され、エラーは報告されません。

test> TRUNCATE t;
Query OK, 1 row affected (0.00 sec)

test> INSERT IGNORE INTO t VALUES (1, 1), (7, 7), (8, 8), (3, 3), (5, 5);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

test> select * from t;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
|    1 |    1 |
|    3 |    3 |
+------+------+
3 rows in set (0.01 sec)

List COLUMNS パーティショニング

List COLUMNS パーティショニングは、 List パーティショニングの一種です。複数の列をパーティション キーとして使用できます。整数データ型のほかに、string、 DATE 、およびDATETIMEデータ型の列をパーティション列として使用することもできます。

次の表に示すように、次の 12 都市の従業員を 4 つの地域に分割するとします。

| Region | Cities                         |
| :----- | ------------------------------ |
| 1      | LosAngeles,Seattle, Houston    |
| 2      | Chicago, Columbus, Boston      |
| 3      | NewYork, LongIsland, Baltimore |
| 4      | Atlanta, Raleigh, Cincinnati   |

以下に示すように、 List COLUMNS パーティショニングを使用してテーブルを作成し、各行を従業員の都市に対応するパーティションに格納できます。

CREATE TABLE employees_1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('LosAngeles', 'Seattle', 'Houston'),
    PARTITION pRegion_2 VALUES IN('Chicago', 'Columbus', 'Boston'),
    PARTITION pRegion_3 VALUES IN('NewYork', 'LongIsland', 'Baltimore'),
    PARTITION pRegion_4 VALUES IN('Atlanta', 'Raleigh', 'Cincinnati')
);

List パーティショニングとは異なり、 List COLUMNS パーティショニングでは、列の値を整数に変換するためにCOLUMNS()句で式を使用する必要はありません。

List COLUMNS パーティショニングは、次の例に示すように、 DATE型とDATETIME型の列を使用して実装することもできます。この例では、前のemployees_1テーブルと同じ名前と列を使用していますが、 hired列に基づいてList COLUMNS パーティショニングを使用しています。

CREATE TABLE employees_2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(hired) (
    PARTITION pWeek_1 VALUES IN('2020-02-01', '2020-02-02', '2020-02-03',
        '2020-02-04', '2020-02-05', '2020-02-06', '2020-02-07'),
    PARTITION pWeek_2 VALUES IN('2020-02-08', '2020-02-09', '2020-02-10',
        '2020-02-11', '2020-02-12', '2020-02-13', '2020-02-14'),
    PARTITION pWeek_3 VALUES IN('2020-02-15', '2020-02-16', '2020-02-17',
        '2020-02-18', '2020-02-19', '2020-02-20', '2020-02-21'),
    PARTITION pWeek_4 VALUES IN('2020-02-22', '2020-02-23', '2020-02-24',
        '2020-02-25', '2020-02-26', '2020-02-27', '2020-02-28')
);

さらに、 COLUMNS()句に複数の列を追加することもできます。例えば：

CREATE TABLE t (
    id int,
    name varchar(10)
)
PARTITION BY LIST COLUMNS(id,name) (
     partition p0 values IN ((1,'a'),(2,'b')),
     partition p1 values IN ((3,'c'),(4,'d')),
     partition p3 values IN ((5,'e'),(null,null))
);

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

ハッシュ パーティショニングは、データが特定の数のパーティションに均等に分散されるようにするために使用されます。レンジ パーティショニングでは、レンジ パーティショニングを使用する場合は各パーティションの列値の範囲を指定する必要がありますが、ハッシュ パーティショニングを使用する場合はパーティションの数を指定するだけで済みます。

ハッシュによるパーティション分割では、 PARTITION BY HASH (expr)句をCREATE TABLEステートメントに追加する必要があります。 exprは整数を返す式です。この列のタイプが整数の場合は、列名にすることができます。さらに、 PARTITIONS numを追加する必要がある場合もあります。ここでnumは、テーブルが分割されているパーティションの数を示す正の整数です。

次の操作では、 store_idで 4 つのパーティションに分割されたハッシュ パーティション テーブルが作成されます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH(store_id)
PARTITIONS 4;

PARTITIONS numが指定されていない場合、デフォルトのパーティション数は 1 です。

exprの整数を返す SQL 式を使用することもできます。たとえば、雇用年でテーブルを分割できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

最も効率的なハッシュ関数は、単一のテーブル列で動作し、その値が列の値に応じて一貫して増加または減少するものです。

たとえば、 date_colは型がDATEの列であり、 TO_DAYS(date_col)式の値はdate_colの値によって異なります。 YEAR(date_col)はTO_DAYS(date_col)とは異なります。これは、 date_colで考えられるすべての変更がYEAR(date_col)で同等の変更を生成するわけではないためです。

対照的に、タイプがINTのint_col列があるとします。式POW(5-int_col,3) + 6について考えてみましょう。ただし、 int_colの値が変化しても式の結果が比例して変化しないため、これは適切なハッシュ関数ではありません。 int_colでの値の変更は、式の結果に大きな変化をもたらす可能性があります。たとえば、 int_colが 5 から 6 に変化すると、式の結果の変化は -1 になります。しかし、 int_colが 6 から 7 に変わると、結果の変化は -7 になる可能性があります。

結論から言うと、式がy = cxに近い形であればハッシュ関数の方が適しています。式が非線形であるほど、パーティション間でデータが不均一に分散される傾向があります。

理論的には、プルーニングは複数の列値を含む式でも可能ですが、そのような式のどれが適切かを判断することは非常に難しく、時間がかかる場合があります。このため、複数の列を含むハッシュ式の使用は特にお勧めしません。

PARTITION BY HASHを使用すると、TiDB は、式の結果のモジュラスに基づいて、データがどのパーティションに分類されるかを決定します。つまり、分割式がexprで分割数がnumの場合、データを格納する分割はMOD(expr, num)で決まります。 t1が次のように定義されているとします。

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

データ行をt1に挿入し、 col3の値が「2005-09-15」の場合、この行はパーティション 1 に挿入されます。

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

TiDB パーティショニングが NULL を処理する方法

TiDB では、分割式の計算結果としてNULLを使用できます。

ノート：

NULLは整数ではありません。 TiDB のパーティショニングの実装では、 ORDER BYと同様に、 NULLを他の整数値より小さいものとして扱います。

レンジ分割でのNULLの扱い

範囲によってパーティション化されたテーブルに行を挿入し、パーティションを決定するために使用される列の値がNULLである場合、この行は最下位のパーティションに挿入されます。

CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY RANGE(c1) (
    PARTITION p0 VALUES LESS THAN (0),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Query OK, 0 rows affected (0.09 sec)

select * from t1 partition(p0);

+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
+------|--------+
1 row in set (0.00 sec)

select * from t1 partition(p1);

Empty set (0.00 sec)

select * from t1 partition(p2);

Empty set (0.00 sec)

p0つのパーティションを削除し、結果を確認します。

alter table t1 drop partition p0;

Query OK, 0 rows affected (0.08 sec)

select * from t1;

Empty set (0.00 sec)

ハッシュ分割によるNULLの扱い

テーブルを Hash で分割する場合、 NULLの値を処理する別の方法があります。分割式の計算結果がNULLの場合、それは0と見なされます。

CREATE TABLE th (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY HASH(c1)
PARTITIONS 2;

Query OK, 0 rows affected (0.00 sec)

INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');

Query OK, 2 rows affected (0.04 sec)

select * from th partition (p0);

+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
|    0 | gigan  |
+------|--------+
2 rows in set (0.00 sec)

select * from th partition (p1);

Empty set (0.00 sec)

挿入されたレコード(NULL, 'mothra')が(0, 'gigan')と同じパーティションに分類されることがわかります。

ノート：

TiDB のハッシュ パーティションによるNULL値はMySQL パーティショニングが NULL を処理する方法で説明されているのと同じ方法で処理されますが、これは MySQL の実際の動作とは一致しません。つまり、この場合の MySQL の実装は、そのドキュメントと一致していません。

この場合、TiDB の実際の動作は、このドキュメントの説明に沿っています。

パーティション管理

LISTおよびRANGEパーティション テーブルの場合、 ALTER TABLE <table name> ADD PARTITION (<partition specification>)またはALTER TABLE <table name> DROP PARTITION <list of partitions>ステートメントを使用してパーティションを追加および削除できます。

LISTおよびRANGE分割テーブルの場合、 REORGANIZE PARTITIONはまだサポートされていません。

HASH分割テーブルの場合、 COALESCE PARTITIONとADD PARTITIONはまだサポートされていません。

EXCHANGE PARTITIONは、 RENAME TABLE t1 TO t1_tmp, t2 TO t1, t1_tmp TO t2のようにテーブルの名前を変更するのと同様に、パーティションとパーティション化されていないテーブルを交換することで機能します。

たとえば、 ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_tableはpartitioned_tableテーブルp1パーティションをnon_partitioned_tableテーブルとスワップします。

パーティションに交換するすべての行がパーティション定義と一致していることを確認してください。そうしないと、ステートメントは失敗します。

TiDB には、影響を与える可能性のある特定の機能がいくつかあることに注意してくださいEXCHANGE PARTITION 。テーブル構造にそのような機能が含まれている場合、 EXCHANGE PARTITIONがMySQL の EXCHANGE PARTITION 条件を満たしていることを確認する必要があります。一方、これらの特定の機能が、パーティション化されたテーブルとパーティション化されていないテーブルの両方で同じように定義されていることを確認してください。これらの特定の機能には、次のものが含まれます。

• SQL の配置規則 : 配置ポリシーは同じです。

• ティフラッシュ : TiFlash レプリカの数は同じです。
• クラスタ化インデックス : パーティション化されたテーブルとパーティション化されていないテーブルの両方がCLUSTERED 、または両方がNONCLUSTEREDです。

さらに、 EXCHANGE PARTITIONと他のコンポーネントとの互換性には制限があります。パーティション化されたテーブルとパーティション化されていないテーブルの両方が同じ定義を持っている必要があります。

• TiFlash: 分割テーブルと非分割テーブルの TiFlash レプリカ定義が異なる場合、 EXCHANGE PARTITIONの操作は実行できません。
• TiCDC: TiCDC は、パーティション化されたテーブルとパーティション化されていないテーブルの両方に主キーまたは一意のキーがある場合に、 EXCHANGE PARTITIONの操作をレプリケートします。そうしないと、TiCDC は操作を複製しません。
• TiDB Lightningと BR: TiDB Lightning Lightning を使用したインポート中、または BR を使用した復元中は、 EXCHANGE PARTITIONの操作を実行しないでください。

レンジパーティション管理

分割テーブルを作成します。

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)

PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

パーティションをドロップします。

ALTER TABLE members DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

パーティションを空にします。

ALTER TABLE members TRUNCATE PARTITION p1;

Query OK, 0 rows affected (0.03 sec)

ノート：

ALTER TABLE ... REORGANIZE PARTITIONは現在、TiDB ではサポートされていません。

パーティションを追加します。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

テーブルを範囲で分割する場合、 ADD PARTITIONはパーティション リストの最後にのみ追加できます。既存の Range パーティションに追加すると、エラーが報告されます。

ALTER TABLE members
    ADD PARTITION (
    PARTITION n VALUES LESS THAN (1970));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

ハッシュパーティション管理

レンジ パーティショニングとは異なり、ハッシュ パーティショニングではDROP PARTITIONはサポートされません。

現在、 ALTER TABLE ... COALESCE PARTITIONは TiDB でもサポートされていません。現在サポートされていないパーティション管理ステートメントの場合、TiDB はエラーを返します。

alter table members optimize partition p0;

ERROR 8200 (HY000): Unsupported optimize partition

パーティションの剪定

パーティションの剪定は、非常に単純なアイデアに基づく最適化です。一致しないパーティションはスキャンしません。

パーティション化されたテーブルt1を作成するとします。

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)

PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

このSELECTステートメントの結果を取得したい場合:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

結果がp1またはp2パーティションのいずれかに該当することは明らかです。つまり、 p1およびp2で一致する行を検索するだけで済みます。不要なパーティションを除外することは、いわゆる「プルーニング」です。オプティマイザがパーティションの一部をプルーニングできる場合、パーティション化されたテーブルでのクエリの実行は、パーティション化されていないテーブルでのクエリの実行よりもはるかに高速になります。

オプティマイザは、次の 2 つのシナリオでWHEREつの条件を使用してパーティションをプルーニングできます。

• partition_column = 定数
• partition_column IN (定数 1、定数 2、...、定数 N)

現在、パーティションプルーニングはLIKEの条件では機能しません。

パーティションのプルーニングが有効になるケース

1. パーティション プルーニングは、パーティション テーブルのクエリ条件を使用するため、プランナーの最適化ルールに従ってクエリ条件をパーティション テーブルにプッシュ ダウンできない場合、パーティション プルーニングはこのクエリには適用されません。

   例えば：

   create table t1 (x int) partition by range (x) (
           partition p0 values less than (5),
           partition p1 values less than (10));
   create table t2 (x int);
   

   explain select * from t1 left join t2 on t1.x = t2.x where t2.x > 5;
   

   このクエリでは、左アウト結合が内部結合に変換され、次にt1.x = t2.xとt2.x > 5からt1.x > 5が導出されるため、パーティションのプルーニングで使用でき、パーティションp1のみが残ります。

   explain select * from t1 left join t2 on t1.x = t2.x and t2.x > 5;
   

   このクエリでは、 t2.x > 5をt1パーティション テーブルにプッシュ ダウンできないため、このクエリではパーティションのプルーニングは有効になりません。

2. パーティションのプルーニングはプランの最適化フェーズで行われるため、実行フェーズまでフィルター条件が不明な場合には適用されません。

   例えば：

   create table t1 (x int) partition by range (x) (
           partition p0 values less than (5),
           partition p1 values less than (10));
   

   explain select * from t2 where x < (select * from t1 where t2.x < t1.x and t2.x < 2);
   

   このクエリはt2から行を読み取り、その結果をt1のサブクエリに使用します。理論的には、パーティションのプルーニングはサブクエリのt1.x > valの式の恩恵を受ける可能性がありますが、実行フェーズで発生するため、そこでは効果がありません。

3. 現在の実装の制限により、クエリ条件を TiKV にプッシュ ダウンできない場合、パーティション プルーニングで使用できません。

   例としてfn(col)式を取り上げます。 TiKV コプロセッサがこのfn機能をサポートしている場合、 fn(col)は、プラン最適化フェーズ中に述語プッシュダウン規則に従ってリーフ ノード (つまり、パーティション分割されたテーブル) にプッシュ ダウンされる可能性があり、パーティション プルーニングで使用できます。

   TiKV コプロセッサがこのfnの機能をサポートしていない場合、 fn(col)はリーフ ノードにプッシュされません。代わりに、リーフ ノードのSelectionノード上になります。現在のパーティション プルーニングの実装では、この種のプラン ツリーはサポートされていません。

4. ハッシュ パーティションの場合、パーティションのプルーニングでサポートされる唯一のクエリは、等しい条件です。

5. 範囲パーティションの場合、パーティションのプルーニングを有効にするには、パーティション式がcolまたはfn(col)の形式である必要があり、クエリ条件が> 、 < 、 = 、 >= 、および<=のいずれかである必要があります。パーティション式がfn(col)の形式の場合、 fn関数は単調でなければなりません。

   fn関数が単調な場合、任意のxおよびyに対して、 x > yの場合、 fn(x) > fn(y) .すると、このfn関数は厳密に単調であると言えます。任意のxおよびyの場合、 x > yの場合はfn(x) >= fn(y) 。この場合、 fnは「単調」とも言えます。理論的には、すべての単調な関数はパーティション プルーニングによってサポートされます。

   現在、TiDB でのパーティションのプルーニングは、次のような単調な関数のみをサポートしています。

   unix_timestamp
   to_days
   

   たとえば、パーティション式は単純な列です。

   create table t (id int) partition by range (id) (
           partition p0 values less than (5),
           partition p1 values less than (10));
   select * from t where t > 6;
   

   または、パーティション式はfn(col)の形式で、 fnはto_daysです。

   create table t (dt datetime) partition by range (to_days(id)) (
           partition p0 values less than (to_days('2020-04-01')),
           partition p1 values less than (to_days('2020-05-01')));
   select * from t where t > '2020-04-18';
   

   例外は、パーティション式としてfloor(unix_timestamp())です。 TiDB はケースバイケースで最適化を行うため、パーティションのプルーニングによってサポートされます。

   create table t (ts timestamp(3) not null default current_timestamp(3))
   partition by range (floor(unix_timestamp(ts))) (
           partition p0 values less than (unix_timestamp('2020-04-01 00:00:00')),
           partition p1 values less than (unix_timestamp('2020-05-01 00:00:00')));
   select * from t where t > '2020-04-18 02:00:42.123';
   

パーティションの選択

SELECTステートメントは、 PARTITIONオプションを使用して実装されるパーティション選択をサポートします。

SET @@sql_mode = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)

PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

p1パーティションに格納されている行を表示できます。

SELECT * FROM employees PARTITION (p1);

+----|-------|--------|----------|---------------+
| id | fname | lname  | store_id | department_id |
+----|-------|--------|----------|---------------+
|  5 | Mary  | Jones  |        1 |             1 |
|  6 | Linda | Black  |        2 |             3 |
|  7 | Ed    | Jones  |        2 |             1 |
|  8 | June  | Wilson |        3 |             1 |
|  9 | Andy  | Smith  |        1 |             3 |
+----|-------|--------|----------|---------------+
5 rows in set (0.00 sec)

複数のパーティションの行を取得する場合は、コンマで区切られたパーティション名のリストを使用できます。たとえば、 SELECT * FROM employees PARTITION (p1, p2)はp1およびp2パーティションのすべての行を返します。

パーティション選択を使用する場合でも、 WHERE条件とORDER BYやLIMITなどのオプションを使用できます。 HAVINGやGROUP BYなどの集計オプションの使用もサポートされています。

SELECT * FROM employees PARTITION (p0, p2)
    WHERE lname LIKE 'S%';

+----|-------|-------|----------|---------------+
| id | fname | lname | store_id | department_id |
+----|-------|-------|----------|---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----|-------|-------|----------|---------------+
2 rows in set (0.00 sec)

SELECT id, CONCAT(fname, ' ', lname) AS name
    FROM employees PARTITION (p0) ORDER BY lname;

+----|----------------+
| id | name           |
+----|----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----|----------------+
4 rows in set (0.06 sec)

SELECT store_id, COUNT(department_id) AS c
    FROM employees PARTITION (p1,p2,p3)
    GROUP BY store_id HAVING c > 4;

+---|----------+
| c | store_id |
+---|----------+
| 5 |        2 |
| 5 |        3 |
+---|----------+
2 rows in set (0.00 sec)

パーティションの選択は、レンジ パーティション分割やハッシュ パーティション分割など、すべてのタイプのテーブル パーティション分割でサポートされています。ハッシュ パーティションの場合、パーティション名が指定されていない場合、 p0 、 p1 、 p2 、...、またはpN-1がパーティション名として自動的に使用されます。

SELECT in INSERT ... SELECTはパーティション選択も使用できます。

パーティションに関する制限と制限

このセクションでは、TiDB のパーティション分割されたテーブルに関するいくつかの制限と制限を紹介します。

分割キー、主キー、一意キー

このセクションでは、分割キーと主キーおよび一意キーとの関係について説明します。この関係を管理するルールは、次のように表現できます。テーブルのすべての一意のキーは、テーブルのパーティション式のすべての列を使用する必要があります。これにはテーブルの主キーも含まれます。これは定義上、一意のキーであるためです。

たとえば、次のテーブル作成ステートメントは無効です。

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

いずれの場合も、提案されたテーブルには、パーティション式で使用されるすべての列を含まない一意のキーが少なくとも 1 つあります。

有効なステートメントは次のとおりです。

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

次の例では、エラーが表示されます。

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

col1とcol3の両方が提案されたパーティション化キーに含まれているため、 CREATE TABLEステートメントは失敗しますが、これらの列はどちらもテーブルの両方の一意のキーの一部ではありません。次の変更後、 CREATE TABLEステートメントが有効になります。

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3),
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
    PARTITIONS 4;

両方の一意のキーに属する列をパーティション化キーに含める方法がないため、次のテーブルはまったくパーティション化できません。

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

すべての主キーは定義上一意のキーであるため、次の 2 つのステートメントは無効です。

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)

PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

上記の例では、パーティション式で参照されるすべての列が主キーに含まれているわけではありません。主キーに欠落している列を追加すると、 CREATE TABLEステートメントが有効になります。

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col3),
    UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

テーブルに一意キーも主キーもない場合、この制限は適用されません。

DDL ステートメントを使用してテーブルを変更する場合、一意のインデックスを追加するときに、この制限も考慮する必要があります。たとえば、次のようにパーティション テーブルを作成するとします。

CREATE TABLE t_no_pk (c1 INT, c2 INT)
    PARTITION BY RANGE(c1) (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20),
        PARTITION p2 VALUES LESS THAN (30),
        PARTITION p3 VALUES LESS THAN (40)
    );

Query OK, 0 rows affected (0.12 sec)

ALTER TABLEステートメントを使用して、一意でないインデックスを追加できます。ただし、一意のインデックスを追加する場合は、 c1列を一意のインデックスに含める必要があります。

分割されたテーブルを使用する場合、プレフィックス インデックスを一意の属性として指定することはできません。

CREATE TABLE t (a varchar(20), b blob,
    UNIQUE INDEX (a(5)))
    PARTITION by range columns (a) (
    PARTITION p0 values less than ('aaaaa'),
    PARTITION p1 values less than ('bbbbb'),
    PARTITION p2 values less than ('ccccc'));

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

関数に関するパーティショニングの制限事項

パーティショニング式では、次のリストに示す関数のみを使用できます。

ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()

MySQL との互換性

現在、TiDB は、Range パーティショニング、Range COLUMNS パーティショニング、 List パーティショニング、 List COLUMNS パーティショニング、および Hash パーティショニングをサポートしています。キー パーティショニングなど、MySQL で利用可能な他のパーティショニング タイプは、TiDB ではまだサポートされていません。

パーティション管理に関しては、下位の実装でデータを移動する必要がある操作は現在サポートされていません。これには、ハッシュ パーティション テーブルのパーティション数の調整、レンジ パーティション テーブルの範囲の変更、パーティションのマージ、およびパーティションを交換します。

サポートされていないパーティショニング タイプの場合、TiDB でテーブルを作成すると、パーティショニング情報は無視され、通常の形式でテーブルが作成され、警告が報告されます。

LOAD DATA構文は、現在 TiDB でのパーティション選択をサポートしていません。

create table t (id int, val int) partition by hash(id) partitions 4;

通常のLOAD DATA操作がサポートされています。

load local data infile "xxx" into t ...

ただし、 Load Dataはパーティションの選択をサポートしていません。

load local data infile "xxx" into t partition (p1)...

パーティション化されたテーブルの場合、 select * from tによって返される結果はパーティション間で順序付けされていません。これは、パーティション間では順序付けされているが、パーティション内では順序付けられていない MySQL の結果とは異なります。

create table t (id int, val int) partition by range (id) (
    partition p0 values less than (3),
    partition p1 values less than (7),
    partition p2 values less than (11));

Query OK, 0 rows affected (0.10 sec)

insert into t values (1, 2), (3, 4),(5, 6),(7,8),(9,10);

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

TiDB は毎回異なる結果を返します。次に例を示します。

select * from t;

+------|------+
| id   | val  |
+------|------+
|    7 |    8 |
|    9 |   10 |
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------|------+
5 rows in set (0.00 sec)

MySQL に返される結果:

select * from t;

+------|------+
| id   | val  |
+------|------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
|    9 |   10 |
+------|------+
5 rows in set (0.00 sec)

tidb_enable_list_partition環境変数は、分割テーブル機能を有効にするかどうかを制御します。この変数がOFFに設定されている場合、テーブルの作成時にパーティション情報は無視され、このテーブルは通常のテーブルとして作成されます。

この変数は、テーブルの作成でのみ使用されます。テーブルが作成された後、この変数の値を変更しても効果はありません。詳細については、 システム変数を参照してください。

動的プルーニングモード

TiDB は、パーティション化されたテーブルにdynamicまたはstaticモードでアクセスします。 v6.3.0 以降、デフォルトでdynamicモードが使用されます。ただし、動的パーティション分割は、完全なテーブル レベルの統計 (GlobalStats) が収集された後にのみ有効になります。 GlobalStats が収集される前に、TiDB は代わりにstaticモードを使用します。 GlobalStats の詳細については、 動的プルーニング モードで分割されたテーブルの統計を収集するを参照してください。

set @@session.tidb_partition_prune_mode = 'dynamic'

手動の ANALYZE と通常のクエリでは、セッション レベルtidb_partition_prune_modeの設定が使用されます。バックグラウンドでのauto-analyze操作は、グローバルtidb_partition_prune_mode設定を使用します。

staticモードでは、パーティション テーブルはパーティション レベルの統計を使用します。 dynamicモードでは、分割されたテーブルはテーブル レベルの GlobalStats を使用します。

staticモードからdynamicモードに切り替える場合は、手動で統計を確認して収集する必要があります。これは、 dynamicモードに切り替えた後、パーティション化されたテーブルにはパーティション レベルの統計のみがあり、テーブル レベルの統計がないためです。 GlobalStats は、次のauto-analyze操作時にのみ収集されます。

set session tidb_partition_prune_mode = 'dynamic';
show stats_meta where table_name like "t";

+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | p0             | 2022-05-27 20:23:34 |            1 |         2 |
| test    | t          | p1             | 2022-05-27 20:23:34 |            2 |         4 |
| test    | t          | p2             | 2022-05-27 20:23:34 |            2 |         4 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.01 sec)

グローバルdynamicプルーニング モードを有効にした後、SQL ステートメントで使用される統計が正しいことを確認するには、テーブルまたはテーブルのパーティションでanalyzeを手動でトリガーして、GlobalStats を取得する必要があります。

analyze table t partition p1;
show stats_meta where table_name like "t";

+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-05-27 20:50:53 |            0 |         5 |
| test    | t          | p0             | 2022-05-27 20:23:34 |            1 |         2 |
| test    | t          | p1             | 2022-05-27 20:50:52 |            0 |         2 |
| test    | t          | p2             | 2022-05-27 20:50:08 |            0 |         2 |
+---------+------------+----------------+---------------------+--------------+-----------+
4 rows in set (0.00 sec)

analyzeの処理中に次の警告が表示された場合は、パーティションの統計に一貫性がないため、これらのパーティションまたはテーブル全体の統計を再度収集する必要があります。

| Warning | 8244 | Build table: `t` column: `a` global-level stats failed due to missing partition-level column stats, please run analyze table to refresh columns of all partitions

スクリプトを使用して、分割されたすべてのテーブルの統計を更新することもできます。詳細については、 動的プルーニング モードで分割されたテーブルの統計を更新するを参照してください。

テーブル レベルの統計の準備ができたら、グローバルな動的プルーニング モードを有効にできます。これは、すべての SQL ステートメントとauto-analyzeの操作に有効です。

set global tidb_partition_prune_mode = dynamic

staticモードでは、TiDB は複数の演算子を使用して各パーティションに個別にアクセスし、次にUnionを使用して結果をマージします。次の例は、TiDB がUnionを使用して 2 つの対応するパーティションの結果をマージする単純な読み取り操作です。

mysql> create table t1(id int, age int, key(id)) partition by range(id) (
    ->     partition p0 values less than (100),
    ->     partition p1 values less than (200),
    ->     partition p2 values less than (300),
    ->     partition p3 values less than (400));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where id < 150;

+------------------------------+----------+-----------+------------------------+--------------------------------+
| id                           | estRows  | task      | access object          | operator info                  |
+------------------------------+----------+-----------+------------------------+--------------------------------+
| PartitionUnion_9             | 6646.67  | root      |                        |                                |
| ├─TableReader_12             | 3323.33  | root      |                        | data:Selection_11              |
| │ └─Selection_11             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_18             | 3323.33  | root      |                        | data:Selection_17              |
|   └─Selection_17             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
|     └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+------------------------+--------------------------------+
7 rows in set (0.00 sec)

dynamicモードでは、各オペレーターが複数のパーティションへの直接アクセスをサポートするため、TiDB はUnionを使用しなくなりました。

mysql> set @@session.tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 where id < 150;
+-------------------------+----------+-----------+-----------------+--------------------------------+
| id                      | estRows  | task      | access object   | operator info                  |
+-------------------------+----------+-----------+-----------------+--------------------------------+
| TableReader_7           | 3323.33  | root      | partition:p0,p1 | data:Selection_6               |
| └─Selection_6           | 3323.33  | cop[tikv] |                 | lt(test.t1.id, 150)            |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1        | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------+--------------------------------+
3 rows in set (0.00 sec)

上記のクエリ結果から、パーティションのプルーニングがまだ有効であり、実行プランがp0とp1にのみアクセスしている間に、実行プランのUnion演算子が消えていることがわかります。

dynamicモードでは、実行計画がより単純かつ明確になります。 Union 操作を省略すると、実行効率が向上し、Union 同時実行の問題を回避できます。さらに、 dynamicモードでは、 staticモードでは使用できない IndexJoin を使用した実行プランも可能です。 (以下の例を参照)

例 1 : 次の例では、IndexJoin を使用した実行プランを使用して、クエリがstaticモードで実行されます。

mysql> create table t1 (id int, age int, key(id)) partition by range(id)
    -> (partition p0 values less than (100),
    ->  partition p1 values less than (200),
    ->  partition p2 values less than (300),
    ->  partition p3 values less than (400));
Query OK, 0 rows affected (0,08 sec)

mysql> create table t2 (id int, code int);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@tidb_partition_prune_mode = 'static';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| id                             | estRows  | task      | access object          | operator info                                  |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| HashJoin_13                    | 12.49    | root      |                        | inner join, equal:[eq(test.t1.id, test.t2.id)] |
| ├─TableReader_42(Build)        | 9.99     | root      |                        | data:Selection_41                              |
| │ └─Selection_41               | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))   |
| │   └─TableFullScan_40         | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                 |
| └─PartitionUnion_15(Probe)     | 39960.00 | root      |                        |                                                |
|   ├─TableReader_18             | 9990.00  | root      |                        | data:Selection_17                              |
|   │ └─Selection_17             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo                 |
|   ├─TableReader_24             | 9990.00  | root      |                        | data:Selection_23                              |
|   │ └─Selection_23             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_22       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo                 |
|   ├─TableReader_30             | 9990.00  | root      |                        | data:Selection_29                              |
|   │ └─Selection_29             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_28       | 10000.00 | cop[tikv] | table:t1, partition:p2 | keep order:false, stats:pseudo                 |
|   └─TableReader_36             | 9990.00  | root      |                        | data:Selection_35                              |
|     └─Selection_35             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|       └─TableFullScan_34       | 10000.00 | cop[tikv] | table:t1, partition:p3 | keep order:false, stats:pseudo                 |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
17 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1, t2) */ or /*+ TIDB_INLJ(t1, t2) */ is inapplicable |
+---------+------+------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

例 1 から、 TIDB_INLJヒントを使用しても、分割テーブルに対するクエリは IndexJoin で実行プランを選択できないことがわかります。

例 2 : 次の例では、クエリは IndexJoin を使用した実行プランを使用してdynamicモードで実行されます。

mysql> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object          | operator info                                                                                                       |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| IndexJoin_11                    | 12.49    | root      |                        | inner join, inner:IndexLookUp_10, outer key:test.t2.id, inner key:test.t1.id, equal cond:eq(test.t2.id, test.t1.id) |
| ├─TableReader_16(Build)         | 9.99     | root      |                        | data:Selection_15                                                                                                   |
| │ └─Selection_15                | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))                                                                        |
| │   └─TableFullScan_14          | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                                                                                      |
| └─IndexLookUp_10(Probe)         | 1.25     | root      | partition:all          |                                                                                                                     |
|   ├─Selection_9(Build)          | 1.25     | cop[tikv] |                        | not(isnull(test.t1.id))                                                                                             |
|   │ └─IndexRangeScan_7          | 1.25     | cop[tikv] | table:t1, index:id(id) | range: decided by [eq(test.t1.id, test.t2.id)], keep order:false, stats:pseudo                                      |
|   └─TableRowIDScan_8(Probe)     | 1.25     | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                                      |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

例 2 から、 dynamicモードでは、クエリを実行すると IndexJoin を使用した実行プランが選択されることがわかります。

現在、プルーニング モードstaticとdynamicのどちらも、準備済みステートメントのプラン キャッシュをサポートしていません。

動的プルーニング モードで分割されたテーブルの統計を更新する

1. 分割されたすべてのテーブルを見つけます。

   select distinct concat(TABLE_SCHEMA,'.',TABLE_NAME)
       from information_schema.PARTITIONS
       where TABLE_SCHEMA not in('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
   

   +-------------------------------------+
   | concat(TABLE_SCHEMA,'.',TABLE_NAME) |
   +-------------------------------------+
   | test.t                              |
   +-------------------------------------+
   1 row in set (0.02 sec)
   

2. すべての分割テーブルの統計を更新するためのステートメントを生成します。

   select distinct concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;')
       from information_schema.PARTITIONS
       where TABLE_SCHEMA not in ('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
   +----------------------------------------------------------------------+
   | concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') |
   +----------------------------------------------------------------------+
   | ANALYZE TABLE test.t ALL COLUMNS;                                    |
   +----------------------------------------------------------------------+
   1 row in set (0.01 sec)
   

   ALL COLUMNSを必要な列に変更できます。

3. バッチ更新ステートメントをファイルにエクスポートします。

   mysql --host xxxx --port xxxx -u root -p -e "select distinct concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') \
       from information_schema.PARTITIONS \
       where TABLE_SCHEMA not in ('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');" | tee gatherGlobalStats.sql
   

4. バッチ更新を実行します。

   sourceコマンドを実行する前に SQL ステートメントを処理します。

   sed -i "" '1d' gatherGlobalStats.sql --- mac
   sed -i '1d' gatherGlobalStats.sql --- linux
   

   SET session tidb_partition_prune_mode = dynamic;
   source gatherGlobalStats.sql