重要

このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

パーティショニング

このドキュメントでは、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 に導入されました。

警告

これは実験的機能であり、予告なしに変更または削除される可能性があります。構文と実装は、GA の前に変更される可能性があります。バグを見つけた場合は、 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 が Linear Hash パーティションを処理する方法

v6.4.0 より前では、TiDB でMySQL 線形ハッシュパーティションの DDL ステートメントを実行すると、TiDB は非パーティションテーブルしか作成できませんでした。この場合、TiDB で分割されたテーブルを引き続き使用する場合は、DDL ステートメントを変更する必要があります。

v6.4.0 以降、TiDB は MySQL PARTITION BY LINEAR HASH構文の解析をサポートしていますが、その中のLINEARキーワードを無視します。 MySQL Linear Hash パーティションの既存の DDL および DML ステートメントがいくつかある場合は、変更せずに TiDB でそれらを実行できます。

MySQL 線形ハッシュパーティションのCREATEステートメントの場合、TiDB は非線形ハッシュパーティションテーブルを作成します (TiDB には線形ハッシュパーティションテーブルがないことに注意してください)。パーティションの数が 2 の累乗である場合、TiDB ハッシュパーティションテーブルの行は、MySQL リニアハッシュパーティションテーブルの行と同じように分散されます。それ以外の場合、TiDB でのこれらの行の分散は MySQL とは異なります。これは、非線形分割テーブルが単純な「分割のモジュラス数」を使用するのに対し、線形分割テーブルは「次の 2 の累乗を法とし、分割数と次の 2 の累乗の間で値を折り畳む」ことを使用するためです。詳細については、 #38450を参照してください。
MySQL Linear Hash パーティションの他のすべてのステートメントについては、TiDB でも MySQL と同じように機能しますが、パーティションの数が 2 の累乗でない場合は行が異なる方法で分散され、パーティションの選択、 TRUNCATE PARTITION 3 に対して異なる結果が得られます。とEXCHANGE PARTITION 。

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

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

TiFlash: 分割テーブルと非分割テーブルのTiFlashレプリカ定義が異なる場合、 EXCHANGE PARTITION操作は実行できません。
TiCDC: TiCDC は、パーティション化されたテーブルとパーティション化されていないテーブルの両方に主キーまたは一意のキーがある場合に、 EXCHANGE PARTITION操作をレプリケートします。そうしないと、TiCDC は操作を複製しません。
TiDB LightningおよびBR: TiDB 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条件では機能しません。

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

パーティションのプルーニングはパーティションテーブルのクエリ条件を使用するため、プランナーの最適化ルールに従ってクエリ条件をパーティションテーブルにプッシュダウンできない場合、パーティションのプルーニングはこのクエリには適用されません。
例えば：
```
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パーティションテーブルにプッシュダウンできないため、このクエリではパーティションのプルーニングは有効になりません。
パーティションのプルーニングはプランの最適化フェーズで行われるため、実行フェーズまでフィルター条件が不明な場合には適用されません。
例えば：
```
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式の恩恵を受ける可能性がありますが、実行フェーズで発生するため、そこでは効果がありません。
現在の実装の制限により、クエリ条件を TiKV にプッシュダウンできない場合、パーティションプルーニングで使用できません。
例としてfn(col)式を取り上げます。 TiKV コプロセッサがこのfn関数をサポートしている場合、 fn(col) 、計画最適化フェーズ中に述語プッシュダウンルールに従ってリーフノード (つまり、パーティションテーブル) にプッシュダウンされる可能性があり、パーティションプルーニングで使用できます。
TiKV コプロセッサがこのfn機能をサポートしていない場合、 fn(col)リーフノードにプッシュされません。代わりに、リーフノードのSelectionノード上になります。現在のパーティションプルーニングの実装では、この種のプランツリーはサポートされていません。
ハッシュパーティションの場合、パーティションのプルーニングでサポートされる唯一のクエリは、等しい条件です。
範囲パーティションの場合、パーティションのプルーニングを有効にするには、パーティション式が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 id > 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 dt > '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 ts > '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)         | 12.49    | root      | partition:all          |                                                                                                                     |
|   ├─Selection_9(Build)          | 12.49    | cop[tikv] |                        | not(isnull(test.t1.id))                                                                                             |
|   │ └─IndexRangeScan_7          | 12.50    | 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)     | 12.49    | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                                      |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

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

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

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

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

SELECT DISTINCT CONCAT(TABLE_SCHEMA,'.', TABLE_NAME)
    FROM information_schema.PARTITIONS
    WHERE TIDB_PARTITION_ID IS NOT NULL
    AND 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)

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

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を必要な列に変更できます。

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

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

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

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

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

SET session tidb_partition_prune_mode = dynamic;
source gatherGlobalStats.sql

パーティショニング