Similar to subpartitioning in MySQL, PolarDB-X allows you to use the subpartitioning syntax to create a partitioned table that contains subpartitions. Subpartitioning refers to further splitting a partition in a partitioned table based on the specified partition key columns and partitioning policy.
A partitioned table that contains subpartitions has the following characteristics:
Each partition becomes a logical partition, which is a set of subpartitions.
Each subpartition becomes a physical partition, which corresponds to a specific physical table on a data node.
Supported versions
The instance version must be 5.4.17-16952556 or later.
For information about instance versioning, see Release notes.
For information about how to view the version of a PolarDB-X instance, see View and update the version of an instance.
Orthogonal relationships between partitions and subpartitions
PolarDB-X supports the following seven partitioning policies for both partitions and subpartitions:
Therefore, the partitions and subpartitions of PolarDB-X have completely orthogonal relationships. Any two partitioning polices can be combined to compose up to 49 composite partitioning policies.
Templated subpartitioning and non-templated subpartitioning
PolarDB-X supports templated subpartitioning and non-templated subpartitioning.
Each of the 49 composite partitioning policies of PolarDB-X supports templated subpartitioning and non-templated subpartitioning.
Templated subpartitioning
Templated subpartitioning indicates that the number of subpartitions and their boundary values are the same among all partitions of a table. Example:
/*
* Specify templated subpartitions based on LIST COLUMNS partitioning and KEY partitioning.
* A table is split into three partitions based on LIST COLUMNS partitioning.
* Each partition is split into four subpartitions based on KEY partitioning.
* Therefore, the total number of physical partitions is 12.
*/
CREATE TABLE sp_tbl_list_key_tp(
id int,
country varchar(64),
city varchar(64),
order_time datetime not null,
PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city)
SUBPARTITION BY KEY(id) SUBPARTITIONS 4
(
PARTITION p1 VALUES IN (('China','Hangzhou')),
PARTITION p2 VALUES IN (('Russian','Moscow')),
PARTITION pd VALUES IN (DEFAULT)
);Non-templated subpartitioning
Non-templated subpartitioning indicates that the number of subpartitions and their boundary values can be different among all partitions of a table. Example:
/*
* Specify non-templated subpartitions based on LIST COLUMNS partitioning and KEY partitioning.
* A table is split into three partitions based on LIST COLUMNS partitioning.
* Each partition is split into subpartitions based on KEY partitioning.
* Partition 1 is split into two subpartitions, Partition 2 is split into three subpartitions, and Partition 3 is split into four subpartitions.
* Therefore, the total number of physical partitions is 9.
*/
CREATE TABLE sp_tbl_list_key_ntp(
id int,
country varchar(64),
city varchar(64),
order_time datetime not null,
PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city)
SUBPARTITION BY KEY(id)
(
PARTITION p1 VALUES IN (('China','Hangzhou')) SUBPARTITIONS 2,
PARTITION p2 VALUES IN (('Russian','Moscow')) SUBPARTITIONS 3,
PARTITION pd VALUES IN (DEFAULT) SUBPARTITIONS 4
);