パーティショニング
このドキュメントでは、TiDB のパーティショニングの実装について説明します。
パーティションの種類
このセクションでは、TiDBにおけるパーティショニングの種類について説明します。現在、TiDBは範囲分割 、 範囲列パーティション 、 List パーティショニング 、 List 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パーティショニングは、レンジパーティショニングの一種です。1つ以上の列をパーティショニングキーとして使用できます。パーティション列のデータ型は、整数、文字列（ CHARまたはVARCHAR ）、 DATE DATETIMEいずれかです。COLUMNS以外のパーティショニングなどの式はサポートされていません。
レンジパーティションと同様に、レンジ列パーティションでもパーティション範囲は厳密に増加する必要があります。次の例のパーティション定義はサポートされていません。
CREATE TABLE t(
a int,
b datetime,
c varchar(8)
) PARTITION BY RANGE COLUMNS(`c`,`b`)
(PARTITION `p20240520A` VALUES LESS THAN ('A','2024-05-20 00:00:00'),
PARTITION `p20240520Z` VALUES LESS THAN ('Z','2024-05-20 00:00:00'),
PARTITION `p20240521A` VALUES LESS THAN ('A','2024-05-21 00:00:00'));
Error 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
名前でパーティション分割し、古くて無効なデータを削除する場合は、次のようにテーブルを作成できます。
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 `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 `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'))
上記のSQL文は[ ('M', '2024-01-01 00:00:00'), ('S', '2023-01-01 00:00:00') ) [ ('', ''), ('G', '2023-01-01 00:00:00') ) [ ('G', '2024-01-01 00:00:00'), ('M', '2023-01-01 00:00:00') )範囲でデータを年と名前でパーティション分割します。これにより、 name [ ('M', '2023-01-01 00:00:00'), ('M', '2024-01-01 00:00:00') ) [ ('G', '2023-01-01 00:00:00'), ('G', '2024-01-01 00:00:00') ) [ ('S', '2023-01-01 00:00:00'), ('S', '2024-01-01 00:00:00') )目の両方でパーティションプルーニングのメリットを活用しながら、無効なデータを簡単に削除できvalid_until 。この例では、 [,)左閉じ、右開きの範囲を示しています。例えば、 [ ('G', '2023-01-01 00:00:00'), ('G', '2024-01-01 00:00:00') ) 、名前が'G' 、年に2023-01-01 00:00:00が含まれ、 2023-01-01 00:00:00より大きく2024-01-01 00:00:00より小さいデータの範囲を示しています。29 (G, 2024-01-01 00:00:00)含まれません。
範囲INTERVALパーティション分割
レンジ・インターバル・パーティショニングはレンジ・パーティショニングの拡張版であり、指定した間隔のパーティションを簡単に作成できます。TiDB v6.3.0以降、インターバル・パーティショニングはシンタックスシュガーとして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 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パーティションには影響しません。
ALTER TABLE table_name FIRST PARTITION LESS THAN (<expression>)
次の文は最後のパーティションを変更します。つまり、より広い範囲を持つパーティションを追加し、新しいデータのためのスペースを確保します。指定された式を含む、現在のINTERVALを持つ新しいパーティションが追加されます。1 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パーティションにINTERVAL構文を使用する場合、パーティション キーとして指定できるのはINTEGER、DATE、またはDATETIMEタイプの 1 つの列のみです。
List パーティショニング
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）のすべての店舗が別の会社に売却されたとします。この場合、 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ステートメントを実行する必要があります。
デフォルトのリストパーティション
バージョン7.3.0以降では、リストまたはリスト列パーティションテーブルにデフォルトパーティションを追加できます。デフォルトパーティションはフォールバックパーティションとして機能し、どのパーティションの値セットにも一致しない行を配置できます。
注記：
この機能は、MySQL構文に対するTiDBの拡張です。デフォルトパーティションを持つリストまたはリスト列パーティションテーブルの場合、テーブル内のデータはMySQLに直接複製できません。
次のリストパーティションテーブルを例に挙げます。
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)
次のように、 pDefという名前のデフォルトのリスト パーティションをテーブルに追加できます。
ALTER TABLE t ADD PARTITION (PARTITION pDef DEFAULT);
または
ALTER TABLE t ADD PARTITION (PARTITION pDef VALUES IN (DEFAULT));
この方法では、どのパーティションの値セットにも一致しない新しく挿入された値は、自動的にデフォルトのパーティションに移動されます。
INSERT INTO t VALUES (7, 7);
Query OK, 1 row affected (0.01 sec)
リストまたはリスト列パーティションテーブルを作成するときに、デフォルトのパーティションを追加することもできます。例:
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),
PARTITION pDefault DEFAULT
);
デフォルトパーティションのないリストまたはリスト列パーティションテーブルの場合、 INSERTステートメントを使用して挿入する値は、テーブルのPARTITION ... VALUES IN (...)番目の句で定義された値セットと一致する必要があります。挿入する値がどのパーティションの値セットとも一致しない場合、ステートメントは失敗し、次の例に示すようにエラーが返されます。
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)
INSERT INTO t VALUES (7, 7);
ERROR 1525 (HY000): Table has no partition for value 7
上記のエラーを無視するには、 INSERTステートメントにキーワードIGNOREを追加します。このキーワードを追加すると、 INSERTステートメントはパーティション値セットに一致する行のみを挿入し、一致しない行は挿入せず、エラーは返されません。
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 パーティショニングは、 DATETIMEList パーティショニングのバリエーションです。複数の列をパーティションキーとして使用できます。整数データ型に加えて、文字列、 DATEデータ型の列もパーティション列として使用できます。
次の表に示すように、次の 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))
);
ハッシュパーティショニング
ハッシュ・パーティション分割は、データが一定数のパーティションに均等に分散されるようにするために使用されます。レンジ・パーティション分割を使用する場合は、各パーティションの列値の範囲を指定する必要がありますが、ハッシュ・パーティション分割を使用する場合は、パーティションの数のみを指定すれば済みます。
ハッシュパーティションテーブルを作成するには、 CREATE TABLE文にPARTITION BY HASH (expr)句を追加する必要があります。5 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)によってデータが格納されるパーティションが決定されます。9 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はv7.0.0以降、キーパーティションをサポートしています。v7.0.0より前のバージョンのTiDBでは、キーパーティションテーブルを作成しようとすると、非パーティションテーブルとして作成され、警告が返されます。
キーパーティショニングとハッシュパーティショニングはどちらも、データを一定数のパーティションに均等に分散できます。違いは、ハッシュパーティショニングは指定された整数式または整数列に基づいてデータの分散のみをサポートするのに対し、キーパーティショニングは列リストに基づいてデータの分散をサポートし、パーティション列は整数型に限定されないことです。TiDBのキーパーティショニングにおけるハッシュアルゴリズムはMySQLのそれとは異なるため、テーブルデータの分散も異なります。
キーパーティションテーブルを作成するには、 CREATE TABLE文にPARTITION BY KEY (columnList)句を追加する必要があります。 columnList 、1つ以上の列名を含む列リストです。リスト内の各列のデータ型は、 BLOB 、 JSON 、 GEOMETRYを除く任意の型にすることができます（TiDBはGEOMETRYサポートしていないことに注意してください）。さらに、 PARTITIONS num （ numテーブルがパーティションに分割されている数を示す正の整数）を追加したり、パーティション名の定義を追加したりする必要がある場合もあります。たとえば、 (PARTITION p0, PARTITION p1)を追加すると、テーブルがp0とp1という2つのパーティションに分割されます。
次の操作は、 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 KEY(store_id)
PARTITIONS 4;
PARTITIONS numが指定されていない場合、デフォルトのパーティション数は 1 になります。
VARCHARなどの非整数列に基づいてキーパーティションテーブルを作成することもできます。例えば、 fname番目の列でテーブルをパーティション分割できます。
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 KEY(fname)
PARTITIONS 4;
複数の列に基づいてキーパーティションテーブルを作成することもできます。例えば、 fnameと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 KEY(fname, store_id)
PARTITIONS 4;
MySQLと同様に、TiDBはPARTITION BY KEYで指定された空のパーティション列リストを持つキーパーティションテーブルの作成をサポートしています。例えば、次の文は、主キーidパーティションキーとしてパーティションテーブルを作成します。
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY,
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 KEY()
PARTITIONS 4;
テーブルに主キーがなく、一意のキーが含まれている場合は、一意のキーがパーティション キーとして使用されます。
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
ただし、一意のキー列がNOT NULLとして定義されていない場合、前のステートメントは失敗します。
TiDBが線形ハッシュパーティションを処理する方法
v6.4.0より前のバージョンでは、TiDBでMySQL 線形ハッシュパーティションのDDL文を実行すると、TiDBは非パーティションテーブルしか作成できませんでした。この場合、TiDBでパーティションテーブルを引き続き使用したい場合は、DDL文を変更する必要があります。
TiDBはv6.4.0以降、MySQL PARTITION BY LINEAR HASH構文の解析をサポートしていますが、その中のキーワードLINEARは無視されます。MySQL線形ハッシュパーティションの既存のDDLおよびDML文がある場合は、変更せずにTiDBで実行できます。
MySQL の線形ハッシュ パーティションの
CREATEステートメントに対して、TiDB は非線形ハッシュパーティションテーブルを作成します (TiDB には線形ハッシュパーティションテーブルはありません)。パーティション数が 2 の累乗の場合、TiDB ハッシュパーティションテーブルの行は、MySQL の線形ハッシュパーティションテーブルと同じように分散されます。それ以外の場合、TiDB でのこれらの行の分散は MySQL とは異なります。これは、非線形パーティション テーブルでは単純な「パーティション数を係数とする」のに対し、線形パーティション テーブルでは「次の 2 の累乗を係数とし、パーティション数と次の 2 の累乗の間の値を畳む」ためです。詳細については、 ＃38450参照してください。MySQL リニア ハッシュ パーティションのその他のすべてのステートメントは、パーティション数が 2 の累乗でない場合、行が異なる方法で分散され、 パーティションの選択 、
TRUNCATE PARTITION、EXCHANGE PARTITIONで異なる結果になることを除いて、TiDB でも MySQL と同じように機能します。
TiDBがリニアキーパーティションを処理する方法
TiDB v7.0.0以降、キーパーティショニングにおいてMySQL PARTITION BY LINEAR KEY構文の解析をサポートしています。ただし、TiDBはLINEARキーワードを無視し、代わりに非線形ハッシュアルゴリズムを使用します。
v7.0.0 より前のバージョンでは、キーパーティションテーブルを作成しようとすると、TiDB はそれを非パーティションテーブルとして作成し、警告を返します。
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の扱い
ハッシュでテーブルをパーティション分割する場合、 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 の実際の動作はこのドキュメントの説明と一致します。
キーパーティショニングによるNULLの扱い
キーパーティショニングでは、 NULL値の扱い方はハッシュパーティショニングと同じです。パーティショニングフィールドの値がNULLの場合、 0として扱われます。
パーティション管理
RANGE 、 RANGE COLUMNS 、 LIST 、およびLIST COLUMNSパーティション化されたテーブルの場合、次のようにパーティションを管理できます。
ALTER TABLE <table name> ADD PARTITION (<partition specification>)ステートメントを使用してパーティションを追加します。ALTER TABLE <table name> DROP PARTITION <list of partitions>ステートメントを使用してパーティションを削除します。ALTER TABLE <table name> TRUNCATE PARTITION <list of partitions>ステートメントを使用して、指定されたパーティションからすべてのデータを削除します。3TRUNCATE PARTITIONロジックはTRUNCATE TABLEと似ていますが、パーティションを対象としています。ALTER TABLE <table name> REORGANIZE PARTITION <list of partitions> INTO (<new partition definitions>)ステートメントを使用して、パーティションを結合、分割、またはその他の変更を行います。
HASHおよびKEYパーティション化されたテーブルの場合、次のようにパーティションを管理できます。
ALTER TABLE <table name> COALESCE PARTITION <number of partitions to decrease by>ステートメントを使用してパーティション数を減らします。この操作では、テーブル全体を新しいパーティション数にオンラインでコピーすることで、パーティションを再編成します。ALTER TABLE <table name> ADD PARTITION <number of partitions to increase by | (additional partition definitions)>ステートメントを使用してパーティション数を増やします。この操作では、テーブル全体を新しいパーティション数にオンラインでコピーすることで、パーティションを再編成します。ALTER TABLE <table name> TRUNCATE PARTITION <list of partitions>ステートメントを使用して、指定されたパーティションからすべてのデータを削除します。3TRUNCATE PARTITIONロジックはTRUNCATE TABLEと似ていますが、パーティションを対象としています。
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条件満たしていることを確認する必要があります。また、これらの特定の機能がパーティションテーブルと非パーティションテーブルの両方で同じように定義されていることを確認してください。これらの特定の機能には、以下のものが含まれます。
- TiFlash : TiFlashレプリカの数は同じです。
- クラスター化インデックス : パーティション化されたテーブルとパーティション化されていないテーブルの両方が
CLUSTERED、または両方がNONCLUSTERED。
さらに、 EXCHANGE PARTITION他のコンポーネントとの互換性には制限があります。パーティションテーブルと非パーティションテーブルの両方の定義が同じである必要があります。
- TiFlash: パーティション化されたテーブルとパーティション化されていないテーブルのTiFlashレプリカ定義が異なる場合、
EXCHANGE PARTITION操作は実行できません。 - TiCDC: TiCDCは、パーティションテーブルと非パーティションテーブルの両方に主キーまたは一意キーがある場合、
EXCHANGE PARTITION操作を複製します。それ以外の場合、TiCDCは操作を複製しません。 - TiDB LightningおよびBR: TiDB Lightningを使用したインポート中、またはBRを使用した復元中に
EXCHANGE PARTITION操作を実行しません。
範囲、範囲列、リスト、リスト列のパーティションを管理する
このセクションでは、次の SQL ステートメントによって作成されたパーティション テーブルを例として使用して、範囲パーティションとリスト パーティションを管理する方法を示します。
CREATE TABLE members (
id int,
fname varchar(255),
lname varchar(255),
dob date,
data json
)
PARTITION BY RANGE (YEAR(dob)) (
PARTITION pBefore1950 VALUES LESS THAN (1950),
PARTITION p1950 VALUES LESS THAN (1960),
PARTITION p1960 VALUES LESS THAN (1970),
PARTITION p1970 VALUES LESS THAN (1980),
PARTITION p1980 VALUES LESS THAN (1990),
PARTITION p1990 VALUES LESS THAN (2000));
CREATE TABLE member_level (
id int,
level int,
achievements json
)
PARTITION BY LIST (level) (
PARTITION l1 VALUES IN (1),
PARTITION l2 VALUES IN (2),
PARTITION l3 VALUES IN (3),
PARTITION l4 VALUES IN (4),
PARTITION l5 VALUES IN (5));
パーティションを削除する
ALTER TABLE members DROP PARTITION p1990;
ALTER TABLE member_level DROP PARTITION l5;
パーティションを切り捨てる
ALTER TABLE members TRUNCATE PARTITION p1980;
ALTER TABLE member_level TRUNCATE PARTITION l4;
パーティションを追加する
ALTER TABLE members ADD PARTITION (PARTITION `p1990to2010` VALUES LESS THAN (2010));
ALTER TABLE member_level ADD PARTITION (PARTITION l5_6 VALUES IN (5,6));
範囲パーティションテーブルの場合、 ADD PARTITION既存の最後のパーティションの後に新しいパーティションを追加します。既存のパーティションと比較して、 VALUES LESS THANで定義された新しいパーティションの値は大きくなければなりません。そうでない場合、エラーが報告されます。
ALTER TABLE members ADD PARTITION (PARTITION p1990 VALUES LESS THAN (2000));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
パーティションを再編成する
パーティションを分割します。
ALTER TABLE members REORGANIZE PARTITION `p1990to2010` INTO
(PARTITION p1990 VALUES LESS THAN (2000),
PARTITION p2000 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2030),
PARTITION pMax VALUES LESS THAN (MAXVALUE));
ALTER TABLE member_level REORGANIZE PARTITION l5_6 INTO
(PARTITION l5 VALUES IN (5),
PARTITION l6 VALUES IN (6));
パーティションを結合する:
ALTER TABLE members REORGANIZE PARTITION pBefore1950,p1950 INTO (PARTITION pBefore1960 VALUES LESS THAN (1960));
ALTER TABLE member_level REORGANIZE PARTITION l1,l2 INTO (PARTITION l1_2 VALUES IN (1,2));
パーティション スキームの定義を変更します。
ALTER TABLE members REORGANIZE PARTITION pBefore1960,p1960,p1970,p1980,p1990,p2000,p2010,p2020,pMax INTO
(PARTITION p1800 VALUES LESS THAN (1900),
PARTITION p1900 VALUES LESS THAN (2000),
PARTITION p2000 VALUES LESS THAN (2100));
ALTER TABLE member_level REORGANIZE PARTITION l1_2,l3,l4,l5,l6 INTO
(PARTITION lOdd VALUES IN (1,3,5),
PARTITION lEven VALUES IN (2,4,6));
パーティションを再編成するときは、次の重要な点に注意する必要があります。
パーティションの再編成 (パーティションの結合や分割を含む) により、リストされたパーティションを新しいパーティション定義セットに変更できますが、パーティションのタイプを変更することはできません (たとえば、リスト タイプを範囲タイプに変更したり、範囲 COLUMNS タイプを範囲タイプに変更したりすることはできません)。
範囲パーティション テーブルの場合、テーブル内の隣接するパーティションのみを再編成できます。
ALTER TABLE members REORGANIZE PARTITION p1800,p2000 INTO (PARTITION p2000 VALUES LESS THAN (2100));ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitionsレンジパーティションテーブルの場合、範囲の終了位置を変更するには、
VALUES LESS THANで定義した新しい終了位置が最後のパーティションの既存の行をカバーする必要があります。そうでない場合、既存の行が収まらなくなり、エラーが報告されます。INSERT INTO members VALUES (313, "John", "Doe", "2022-11-22", NULL); ALTER TABLE members REORGANIZE PARTITION p2000 INTO (PARTITION p2000 VALUES LESS THAN (2050)); -- This statement will work as expected, because 2050 covers the existing rows. ALTER TABLE members REORGANIZE PARTITION p2000 INTO (PARTITION p2000 VALUES LESS THAN (2020)); -- This statement will fail with an error, because 2022 does not fit in the new range.ERROR 1526 (HY000): Table has no partition for value 2022リストパーティションテーブルの場合、パーティションに定義された値のセットを変更するには、新しい定義がそのパーティション内の既存の値をカバーする必要があります。そうでない場合は、エラーが報告されます。
INSERT INTO member_level (id, level) values (313, 6); ALTER TABLE member_level REORGANIZE PARTITION lEven INTO (PARTITION lEven VALUES IN (2,4));ERROR 1526 (HY000): Table has no partition for value 6パーティションが再編成されると、対応するパーティションの統計情報が古くなるため、以下の警告が表示されます。この場合、
ANALYZE TABLEステートメントを使用して統計情報を更新できます。+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1105 | The statistics of related partitions will be outdated after reorganizing partitions. Please use 'ANALYZE TABLE' statement if you want to update it now | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
ハッシュとキーのパーティションを管理する
このセクションでは、以下のSQL文で作成されたパーティションテーブルを例に、ハッシュパーティションの管理方法を説明します。キーパーティションの場合も、同様の管理文を使用できます。
CREATE TABLE example (
id INT PRIMARY KEY,
data VARCHAR(1024)
)
PARTITION BY HASH(id)
PARTITIONS 2;
パーティションの数を増やす
exampleテーブルのパーティション数を 1 つ増やします (2 から 3 へ)。
ALTER TABLE example ADD PARTITION PARTITIONS 1;
パーティション定義を追加することで、パーティションオプションを指定することもできます。例えば、次のステートメントを使用して、パーティション数を3から5に増やし、新しく追加したパーティションの名前をpExample4とpExample5に指定できます。
ALTER TABLE example ADD PARTITION
(PARTITION pExample4 COMMENT = 'not p3, but pExample4 instead',
PARTITION pExample5 COMMENT = 'not p4, but pExample5 instead');
パーティションの数を減らす
範囲List パーティショニングとは異なり、ハッシュパーティション分割やキーパーティション分割ではDROP PARTITIONサポートされていませんが、 COALESCE PARTITIONを使用してパーティション数を減らしたり、 TRUNCATE PARTITIONを使用して特定のパーティションからすべてのデータを削除したりすることができます。
exampleテーブルのパーティション数を 1 つ減らします (5 から 4 に)。
ALTER TABLE example COALESCE PARTITION 1;
注記：
ハッシュまたはキーでパーティション化されたテーブルのパーティション数を変更するプロセスでは、すべてのデータを新しいパーティション数にコピーすることでパーティションが再編成されます。そのため、ハッシュまたはキーでパーティションテーブルのパーティション数を変更すると、統計情報が古くなっているという次の警告が表示されます。この場合、
ANALYZE TABLEステートメントを使用して統計情報を更新できます。
exampleテーブルが現在どのように構成されているかをよりよく理解するために、 exampleテーブルを再作成するために使用される SQL ステートメントを次のように示します。
SHOW CREATE TABLE\G
*************************** 1. row ***************************
Table: example
Create Table: CREATE TABLE `example` (
`id` int NOT NULL,
`data` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`id`)
(PARTITION `p0`,
PARTITION `p1`,
PARTITION `p2`,
PARTITION `pExample4` COMMENT 'not p3, but pExample4 instead')
1 row in set (0.01 sec)
パーティションを切り捨てる
パーティションからすべてのデータを削除します。
ALTER TABLE example TRUNCATE PARTITION p0;
Query OK, 0 rows affected (0.03 sec)
パーティションテーブルを非パーティションテーブルに変換する
パーティションテーブルを非パーティションテーブルに変換するには、次のステートメントを使用します。このステートメントは、パーティションを削除し、テーブルのすべての行をコピーし、テーブルのインデックスをオンラインで再作成します。
ALTER TABLE <table_name> REMOVE PARTITIONING
たとえば、 membersパーティションテーブルを非パーティションテーブルに変換するには、次のステートメントを実行します。
ALTER TABLE members REMOVE PARTITIONING
既存のテーブルをパーティション分割する
既存の非パーティションテーブルをパーティション化したり、既存のパーティションテーブルのパーティション タイプを変更したりするには、次のステートメントを使用します。このステートメントは、すべての行をコピーし、新しいパーティション定義に従ってオンラインでインデックスを再作成します。
ALTER TABLE <table_name> PARTITION BY <new partition type and definitions> [UPDATE INDEXES (<index name> {GLOBAL|LOCAL}[ , <index name> {GLOBAL|LOCAL}...])]
例:
既存のmembersテーブルを 10 個のパーティションを持つ HASHパーティションテーブルに変換するには、次のステートメントを実行します。
ALTER TABLE members PARTITION BY HASH(id) PARTITIONS 10;
既存のmember_levelテーブルを RANGEパーティションテーブルに変換するには、次のステートメントを実行します。
ALTER TABLE member_level PARTITION BY RANGE(level)
(PARTITION pLow VALUES LESS THAN (1),
PARTITION pMid VALUES LESS THAN (3),
PARTITION pHigh VALUES LESS THAN (7)
PARTITION pMax VALUES LESS THAN (MAXVALUE));
パーティションテーブルをパーティション化する場合、またはすでにパーティションテーブルを再パーティション化する場合は、必要に応じてインデックスをグローバルまたはローカルに更新できます。
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY uidx12(col1, col2),
UNIQUE KEY uidx3(col3)
);
ALTER TABLE t1 PARTITION BY HASH (col1) PARTITIONS 3 UPDATE INDEXES (uidx12 LOCAL, uidx3 GLOBAL);
パーティションプルーニング
パーティションプルーニング 、一致しないパーティションをスキャンしないという非常に単純な考えに基づいた最適化です。
パーティションテーブル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条件を通じてパーティションをプルーニングできます。
- パーティション列 = 定数
- パーティション列 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 > 5t1パーティションテーブルにプッシュダウンできないため、このクエリではパーティション プルーニングは有効になりません。パーティション プルーニングはプランの最適化フェーズで実行されるため、実行フェーズまでフィルター条件が不明な場合には適用されません。
例えば：
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()EXTRACT(<time unit> FROM <DATETIME/DATE/TIME column>)。2列およびDATEDATETIMEの場合、YEARおよびYEAR_MONTH時間単位は単調関数とみなされます。10TIMEの場合、HOUR、およびHOUR_SECONDHOUR_MICROSECOND単調関数とみなされます。パーティションプルーニングでは、EXTRACTではWEEK時間単位としてサポートされていないことHOUR_MINUTE注意してください。
たとえば、パーティション式は単純な列です。
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 のパーティション テーブルに関するいくつかの制限と制約について説明します。
ALTER TABLE ... CHANGE COLUMNステートメントを使用してパーティション テーブルの列タイプを変更することはサポートされていません。ALTER TABLE ... CACHEステートメントを使用してパーティション テーブルをキャッシュ テーブルに設定することはサポートされていません。- TiDB の一時テーブルはパーティション テーブルと互換性がありません。
- パーティションテーブルに外部キー作成することはサポートされていません。
- パーティション テーブル上のインデックスは順番に読み取ることができないため、ヒント
ORDER_INDEX(t1_name, idx1_name [, idx2_name ...])はパーティション テーブルとその関連インデックスでは機能しません。
パーティションキー、主キー、一意キー
このセクションでは、パーティションキーと主キーおよび一意キーの関係について説明します。この関係を規定するルールは次のとおりです。パーティションテーブル上のすべての一意キー（主キーを含む）は、テーブルのパーティション式のすべての列を使用する必要があります。これは、主キーが定義上一意キーでもあるためです。
注記：
グローバルインデックス使用する場合、このルールは無視できます。
たとえば、次のテーブル作成ステートメントは無効です。
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 8264 (HY000): Global Index is needed for index 'col1', since the unique index is not including all partitioning columns, and GLOBAL is not given as IndexOption
CREATE TABLE文は、提案されたパーティションキーにcol1とcol3両方が含まれているため失敗します。しかし、これらの列はどちらもテーブル上の両方の一意キーの一部ではありません。以下の変更を加えると、 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 8264 (HY000): Global Index is needed for index 'a', since the unique index is not including all partitioning columns, and GLOBAL is not given as IndexOption
グローバルインデックス
グローバルインデックスが導入される前は、TiDBは各制限にローカルインデックスを作成していたため、データの一意性を確保するために、主キーと一意キーにパーティションキーを含める必要がありました。さらに、複数のパーティションにまたがるデータに対してクエリを実行する場合、TiDBは結果を返すために各パーティションのデータをスキャンする必要がありました。
これらの問題に対処するため、TiDBはv8.3.0でグローバルインデックス機能を導入しました。グローバルインデックスは、単一のインデックスでテーブル全体のデータをカバーするため、すべてのパーティションキーを対象とすることなく、主キーと一意キーのグローバルな一意性を維持できます。さらに、グローバルインデックスは複数のパーティションにまたがるインデックスデータに単一の操作でアクセスできるため、パーティションごとに1つのローカルインデックスを参照する代わりに、パーティション化されていないキーのクエリパフォーマンスを大幅に向上させます。
主キーまたは一意キーのグローバル インデックスを作成するには、インデックス定義にGLOBALキーワードを追加します。
注記：
グローバル インデックスはパーティション管理に影響します。
DROP、TRUNCATE、およびREORGANIZE PARTITION操作では、テーブル レベルのグローバル インデックスの更新もトリガーされます。つまり、これらの DDL 操作では、対応するテーブルのグローバル インデックスが完全に更新された後にのみ結果が返されます。
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY uidx12(col1, col2) GLOBAL,
UNIQUE KEY uidx3(col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
前の例では、一意のインデックスuidx12グローバル インデックスですが、 uidx3通常の一意のインデックスです。
次の例に示すように、クラスター化インデックスはグローバル インデックスにはできないことに注意してください。
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
PRIMARY KEY (col2) CLUSTERED GLOBAL
) PARTITION BY HASH(col1) PARTITIONS 5;
ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function
理由は、クラスター化インデックスがグローバルインデックスの場合、テーブルはパーティション分割されなくなるためです。これは、クラスター化インデックスのキーがパーティションレベルのレコードキーでもあるのに対し、グローバルインデックスはテーブルレベルのキーであるため、競合が発生するためです。主キーをグローバルインデックスとして設定する必要がある場合は、明示的に非クラスター化インデックス（例： PRIMARY KEY(col1, col2) NONCLUSTERED GLOBAL ）として定義する必要があります。
グローバル インデックスは、 SHOW CREATE TABLE出力のGLOBALインデックス オプションで識別できます。
SHOW CREATE TABLE t1\G
Table: t1
Create Table: CREATE TABLE `t1` (
`col1` int NOT NULL,
`col2` date NOT NULL,
`col3` int NOT NULL,
`col4` int NOT NULL,
UNIQUE KEY `uidx12` (`col1`,`col2`) /*T![global_index] GLOBAL */,
UNIQUE KEY `uidx3` (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`col3`) PARTITIONS 4
1 row in set (0.00 sec)
あるいは、テーブルINFORMATION_SCHEMA.TIDB_INDEXESをクエリし、出力の列IS_GLOBAL確認することもできます。
SELECT * FROM INFORMATION_SCHEMA.TIDB_INDEXES WHERE table_name='t1';
+--------------+------------+------------+----------+--------------+-------------+----------+---------------+------------+----------+------------+-----------+-----------+
| TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | KEY_NAME | SEQ_IN_INDEX | COLUMN_NAME | SUB_PART | INDEX_COMMENT | Expression | INDEX_ID | IS_VISIBLE | CLUSTERED | IS_GLOBAL |
+--------------+------------+------------+----------+--------------+-------------+----------+---------------+------------+----------+------------+-----------+-----------+
| test | t1 | 0 | uidx12 | 1 | col1 | NULL | | NULL | 1 | YES | NO | 1 |
| test | t1 | 0 | uidx12 | 2 | col2 | NULL | | NULL | 1 | YES | NO | 1 |
| test | t1 | 0 | uidx3 | 1 | col3 | NULL | | NULL | 2 | YES | NO | 0 |
+--------------+------------+------------+----------+--------------+-------------+----------+---------------+------------+----------+------------+-----------+-----------+
3 rows in set (0.00 sec)
パーティションテーブルをパーティション化する場合、またはすでにパーティションテーブルを再パーティション化する場合、必要に応じてインデックスをグローバル インデックスに更新したり、ローカル インデックスに戻したりできます。
ALTER TABLE t1 PARTITION BY HASH (col1) PARTITIONS 3 UPDATE INDEXES (uidx12 LOCAL, uidx3 GLOBAL);
グローバルインデックスの制限
インデックス定義で
GLOBALキーワードが明示的に指定されていない場合、TiDB はデフォルトでローカル インデックスを作成します。キーワード
GLOBALとLOCALはパーティションテーブルにのみ適用され、非パーティションテーブルには影響しません。つまり、非パーティションテーブルでは、グローバルインデックスとローカルインデックスに違いはありません。現在、TiDBは一意の列に一意のグローバルインデックスの作成のみをサポートしています。一意でない列にグローバルインデックスを作成する必要がある場合は、グローバルインデックスに主キーを追加して複合インデックスを作成できます。例えば、一意でない列が
col3で主キーがcol1場合、次の文を使用して一意でない列col3にグローバルインデックスを作成できます。ALTER TABLE ... ADD UNIQUE INDEX(col3, col1) GLOBAL;DROP PARTITIONなどの DDL 操作も、グローバルインデックスの更新をトリガーします。これらREORGANIZE PARTITIONDDL 操作はTRUNCATE PARTITION結果を返す前にグローバルインデックスの更新が完了するのを待つ必要があるため、実行時間が長くなります。これは、DROP PARTITIONやTRUNCATE PARTITIONなどのデータアーカイブのシナリオで特に顕著です。グローバルインデックスがない場合、これらの操作は通常すぐに完了します。しかし、グローバルインデックスがある場合、更新が必要なインデックスの数が増えるにつれて実行時間が長くなります。グローバル インデックスを持つテーブルは
EXCHANGE PARTITION操作をサポートしません。デフォルトでは、パーティションテーブルの主キーはクラスター化インデックスであり、パーティションキーを含める必要があります。主キーからパーティションキーを除外する必要がある場合は、テーブル作成時に主キーを非クラスター化グローバルインデックスとして明示的に指定できます（例：
PRIMARY KEY(col1, col2) NONCLUSTERED GLOBAL）。グローバル インデックスが式列に追加される場合、またはグローバル インデックスがプレフィックス インデックスでもある場合 (たとえば
UNIQUE KEY idx_id_prefix (id(10)) GLOBAL)、このグローバル インデックスの統計を手動で収集する必要があります。
関数に関するパーティションの制限
パーティション式では、次のリストに示す関数のみが許可されます。
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はレンジパーティショニング、レンジ列パーティショニング、List パーティショニング、List COLUMNS パーティショニング、ハッシュパーティショニング、キーパーティショニングをサポートしています。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は、パーティション化されたテーブルにモードdynamicまたはモードstaticでアクセスします。バージョン6.3.0以降、デフォルトでモードdynamic使用されます。ただし、動的パーティション分割は、テーブルレベルの統計情報（グローバル統計）が完全に収集された後にのみ有効になります。グローバル統計の収集が完了する前にプルーニングモードdynamic有効にした場合、TiDBはグローバル統計が完全に収集されるまでモードstaticのままになります。グローバル統計の詳細については、 動的プルーニングモードでパーティションテーブルの統計を収集する参照してください。
set @@session.tidb_partition_prune_mode = 'dynamic'
手動ANALYZEと通常のクエリでは、セッションレベルtidb_partition_prune_mode設定が使用されます。バックグラウンドで実行されるauto-analyze操作では、グローバル設定のtidb_partition_prune_modeが使用されます。
モードstaticでは、パーティション テーブルはパーティション レベルの統計を使用します。モードdynamicでは、パーティション テーブルはテーブル レベルのグローバル統計を使用します。
モードstaticからモードdynamicに切り替える際は、統計情報を手動で確認・収集する必要があります。これは、モードdynamicへの切り替え後、パーティションテーブルにはパーティションレベルの統計情報しかなく、テーブルレベルの統計情報は含まれないためです。グローバル統計情報は、次の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手動でトリガーして、グローバル統計を取得する必要があります。
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)
上記のクエリ結果から、パーティション プルーニングは引き続き有効であるものの、実行プラン内のUnion演算子は消え、実行プランはp0とp1のみにアクセスすることがわかります。
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, 1 warning (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プルーニング モードでは、準備済みステートメントと非準備済みステートメントの両方のプラン キャッシュはサポートされていません。
動的プルーニングモードでパーティションテーブルの統計を更新する
パーティション化されたすべてのテーブルを見つけます。
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 TIDB_PARTITION_ID IS NOT NULL AND 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 TIDB_PARTITION_ID IS NOT NULL \ AND 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 --- linuxSET session tidb_partition_prune_mode = dynamic; source gatherGlobalStats.sql