Subpartitioning—also called composite partitioning—divides each partition of a PolarDB-X table into smaller subpartitions. Each partition becomes a logical unit, and each subpartition maps to a physical table on a data node.
Like MySQL, PolarDB-X supports subpartitioning—also known as composite partitioning—which splits each partition of a partitioned table into smaller subpartitions. A partitioned table with 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 seven partitioning policies for both partitions and subpartitions:
The partitions and subpartitions of PolarDB-X are fully orthogonal. Any two of these policies can be combined freely, yielding up to 49 composite partitioning policies.
Templated and non-templated subpartitioning
PolarDB-X supports two modes for defining subpartitions. All 49 composite partitioning policies of PolarDB-X support both modes.
Templated subpartitioning
In templated subpartitioning, all partitions share the same subpartition count and boundary values. Define the subpartition layout once in SUBPARTITION BY; it applies to every partition automatically.
The following example creates three LIST COLUMNS partitions, each with four KEY subpartitions. Total physical partitions: 3 × 4 = 12.
/*
* Templated subpartitioning: LIST COLUMNS + KEY.
* Three partitions x four subpartitions = 12 physical partitions.
*/
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)
);Add SUBPARTITIONS N after SUBPARTITION BY to set a uniform subpartition count for all partitions.
Non-templated subpartitioning
In non-templated subpartitioning, each partition specifies its own subpartition count and boundary values. Use this mode when partitions need different numbers of subpartitions or different boundary values.
The following example creates three LIST COLUMNS partitions with 2, 3, and 4 KEY subpartitions respectively. Total physical partitions: 2 + 3 + 4 = 9.
/*
* Non-templated subpartitioning: LIST COLUMNS + KEY.
* p1: 2 subpartitions, p2: 3 subpartitions, pd: 4 subpartitions = 9 physical partitions.
*/
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
);Add SUBPARTITIONS N to each individual PARTITION clause to set a per-partition subpartition count.