PolarDB:CREATE TABLE (AUTO mode)

Partitioning types

HASH partitioning

The HASH partitioning type in PolarDB-X includes HASH partitioning and KEY partitioning. HASH partitioning and KEY partitioning are supported by the native MySQL syntax. PolarDB-X is syntactically compatible with the syntaxes of HASH partitioning and KEY partitioning that are used to create partitioned tables in MySQL to provide flexible and powerful partition management capabilities such as splitting, merging, and migration and support hot partition splitting in vector partition keys. However, PolarDB-X routes data to partitions in a different manner from MySQL. PolarDB-X re-specifies data routing in HASH partitioning and KEY partitioning. For more information about the syntaxes of different partitioning types, see Partitioning Types. The following table lists the differences between KEY partitioning and HASH partitioning.

Example 1-1:KEY partitioning

CREATE TABLE key_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(name, id)
PARTITIONS 8;

## The following SQL statement meets the partition pruning condition and scans data only in one partition.
SELECT id from key_tbl where name='Jack';

If data in the name column is not evenly distributed or specific data is frequently accessed, you can use another partition key column in the vector, such as the id column, to split partitions for performance optimization. For more information, see ALTER TABLEGROUP.

If a vector partition key consists of N partition key columns and data is routed to the first K (1 ≤ K ≤ N) partition key columns, you need to specify a condition for partition pruning only based on the first K columns of the vector partition key in the WHERE clause of the SQL statement.

Example 1-2: HASH partitioning

CREATE TABLE hash_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
partition by hash(id)
partitions 8;

CREATE TABLE hash_tbl_todays(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;

The following partitioning functions are supported by PolarDB-X:

• YEAR

• MONTH

• DAYOFMONTH

• DAYOFWEEK

• DAYOFYEAR

• TO_DAYS

• TO_MONTHS

• TO_WEEKS

• TO_SECONDS

• UNIX_TIMESTAMP

• SUBSTR/SUBSTRING

Therefore, except that the data types of partition key columns of the SUBSTR or SUBSTRING function must be STRING, the data types of partition key columns of other partitioning functions must be the following types: DATE, DATETIME, and TIMESTAMP.

Example 1-3: Extended HASH partitioning

CREATE TABLE hash_tbl2(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(name, birthday)
PARTITIONS 8;

If you use the HASH partitioning type to partition a table, a vector partition key is used. Data in the table is routed to partitions based on the hash values of values in all partition key columns. You must specify a condition for partition pruning based on all partition key columns in the WHERE clause of an SQL statement. In the following examples, SQL1 meets the partition pruning condition of the hash_tbl2 table, and SQL2 does not meet the partition pruning condition of the hash_tbl2 table.

## The following SQL statement SQL1 meets the partition pruning condition. When the statement is executed, only the specified partition is scanned.
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

## The following SQL statement SQL2 does not meet the partition pruning condition. When the statement is executed, all partitions are scanned.
SELECT id from hash_tbl2 where name='Jack';

In HASH partitioning, data is routed based on the hash values of values in all partition key columns. Therefore, data is more evenly distributed in HASH partitioning than in KEY partitioning in which data is routed based on the hash values of values in only specific columns of the vector partition key. HASH partitioning does not support hot partition splitting because all partition key columns are used for data partitioning and no partition key columns are available for hot partition splitting.

Limits

• Limits on data types

  • Integer types: BIGINT, BIGINT UNSINGED, INT UNSINGED, INT, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED.

  • Date and time types: DATETIME, DATE, and TIMESTAMP.

  • String types: CHAR and VARCHAR.

• Limits on syntaxes

  • If the partition key of a table is a single-column partition key and the data type of the partition key column is DATETIME, DATE, or TIMESTAMP, specific partitioning functions are supported.

  • If the partition key of a table is a vector partition key, partitioning functions and hot partition splitting are not supported.

  • By default, a partitioned table can contain up to 8,192 partitions.

  • By default, a partition key can consist of up to five partition key columns.

Data balancing

• KEY partitioning and HASH partitioning are implemented based on the built-in consistent hashing algorithm MurmurHash3. This algorithm is widely tested in the industry and is proven to have low data collision and high performance.

• When you use KEY partitioning or HASH partitioning, data distribution across different partitions becomes balanced when the number of different values of the partition key is larger than 3,000 based on the MurmurHash3 algorithm. Data is distributed in a more balanced manner when the partition key includes more different values.

RANGE partitioning

The RANGE partitioning type in PolarDB-X includes RANGE partitioning and RANGE COLUMNS partitioning. RANGE partitioning and RANGE COLUMNS partitioning are supported by the native MySQL syntax. For more information about the syntaxes of different partitioning types, see Partitioning Types. The following table lists the differences between RANGE partitioning and RANGE COLUMNS partitioning.

Example 2-1: RANGE COLUMNS partitioning

RANGE COLUMNS partitioning supports vector partition keys but does not support partitioning functions. For example, you can execute the following statement to create a table that is partitioned based on the specified ranges of values in the order_id and order_time columns:

CREATE TABLE orders(
 order_id int,
 order_time datetime not null)
PARTITION BY range columns(order_id,order_time)
(
  PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
  PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
  PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
  PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
  PARTITION p5 VALUES LESS THAN (50000,'2021-01-01'),
  PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

Example 2-2: RANGE partitioning

RANGE partitioning supports only single-column partition keys. If you want to use a column that contains date and time values as the partition key, you must use an expression that contains a partitioning function such as YEAR, TO_DAYS, TO_SECONDS, or MONTH to convert the date and time values in the column to integer values.

For example, you can execute the following statement to create a table that is partitioned based on the specified ranges of values in the order_time column and store data in different partitions by quarter:

CREATE TABLE orders_todays(
 id int,
 order_time datetime not null)
PARTITION BY RANGE(to_days(order_time))
(
  PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
  PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
  PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
  PARTITION p5 VALUES LESS THAN (to_days('2022-01-01')),
  PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

Limits

• Limits on data types

  • Integer types: BIGINT, BIGINT UNSINGEDINT, INT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED.

  • Date and time types: DATETIME and DATE.

  • String types: CHAR and VARCHAR.

• Limits on syntaxes

  • RANGE COLUMNS partitioning and RANGE partitioning do not support NULL as the boundary of a value range.

  • RANGE COLUMNS partitioning does not support the columns of the TIMESTAMP type as partition key columns.

  • In RANGE partitioning, only columns that contain integer values can be used as partition key columns. To use a column of the TIMESTAMP type as a partition key column, you must use the UNIX_TIMSTAMP partitioning function to convert the timestamp values to timestamps in the same time zone.

  • RANGE partitioning does not support hot partition splitting.

  • If a NULL value is specified in an SQL request, the NULL value is considered as the minimum value during data routing.

  • By default, a partitioned table can contain up to 8,192 partitions.

  • By default, a partition key can consist of up to five partition key columns.

LIST partitioning

The LIST partitioning type in PolarDB-X includes LIST partitioning and LIST COLUMNS partitioning. LIST partitioning and LIST COLUMNS partitioning are supported by the native MySQL syntax. For more information about the syntaxes of different partitioning types, see Partitioning types. LIST partitioning and LIST COLUMNS partitioning in PolarDB-X also support DEFAULT partitioning. The following table lists the differences between LIST partitioning and LIST COLUMNS partitioning.

Example 3-1: LIST COLUMNS partitioning

LIST COLUMNS partitioning supports vector partition keys. For example, you can execute the following statement to create a table that is partitioned based on values in the country and city columns:

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow'))
);

Example 3-2: LIST partitioning

LIST partitioning supports only single-column partition keys. If you want to use a column that contains date and time values as the partition key column, you must use a partitioning function such as YEAR, MONTH, DAYOFMONTH, TO_DAYS, or TO_SECONDS to convert timestamp values in the partition key column to integers.

For example, you can execute the following statement to create a table that is partitioned based on values in the order_time column and store data in different partitions by year:

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);

Example 3-3: LIST COLUMNS partitioning or LIST partitioning with a DEFAULT partition

PolarDB-X allows you to create a LIST COLUMNS partitioning or LIST partitioning with a DEFAULT partition. Data that is not defined in common partitions is routed to the DEFAULT partition.

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019),
  PARTITION pd VALUES IN (DEFAULT)
);

Limits

• Limits on data types

  • Integer types: BIGINT, BIGINT UNSINGEDINT, INT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED.

  • Date and time types: DATETIME and DATE.

  • String types: CHAR and VARCHAR.

• Limits on syntaxes

  • In LIST COLUMNS partitioning, the columns of the TIMESTAMP type cannot be used as partition key columns.

  • In LIST partitioning, only columns that contain integer values can be used as partition key columns.

  • LIST COLUMNS partitioning and LIST partitioning do not support hot partition splitting.

  • By default, a partitioned table can contain up to 8,192 partitions.

  • By default, a partition key can consist of up to five partition key columns.

COHASH partitioning

The COHASH partitioning type is exclusive to PolarDB-X.

Supported versions

The instance version must be 5.4.18-17047709 or later.

Scenarios

The COHASH partitioning type is suitable for the following scenarios:

A business table contains one or more similar columns. For example, the table contains a c1 and a c2 columns in which the last four characters of the column values are always the same. You want to partition the table by the columns and include the equivalent query conditions of the c1 or c2 column in queries. You also want to route the conditions to the same partition.

To use this partitioning type in this case, you must maintain the similarity between the values of different partition key columns in a partitioned table.

Example 4-1: Use partitioning functions to define the similarity between the values of different partition key columns in a COHASH partitioned table

Assume that the order_id and buyer_id columns of an orders table contain the same last six characters in the same row. If you want to partition the orders table by the last six characters in the order_id and buyer_id columns, and want the equivalent query conditions of the columns in the same row to be routed to the same partition, execute the following statements:

CREATE TABLE t_orders(
 id bigint not null auto_increment, 
 order_id bigint, 
 buyer_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT('order_id',6) /* Partition the table by the last six characters of the c1 column values.*/,
 	RIGHT('buyer_id',6) /* Partition the table by the last six characters of the c2 column values.*/
) 
PARTITIONS 8;

Example 4-2: Use the RANGE_HASH syntactic sugar to migrate a PolarDB-X 1.0 instance to a PolarDB-X 2.0 instance

You want to migrate a PolarDB-X 1.0 instance to a PolarDB-X 2.0 instance. The source instance has an orders table that uses the range_hash sharding schema. The table is defined by using the DBPARTIITION BY RANGE_HASH(`order_id`,`buyer_id`, 6) syntax. In this case, the partitioned table corresponding to the table in the database of destination instance in AUTO mode is defined by using the following statement:

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY RANGE_HASH(order_id, buyer_Id,6) 
PARTITIONS 8;

PolarDB-X automatically converts the RANGE_HASH syntax to the corresponding COHASH partitioning definition. For example, PolarDB-X automatically converts the RANGE_HASH(order_id, buyer_Id,6) clause in the preceding statements to the following COHASH partitioning definition:

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT('order_id',6) /* Partition the table by the last six characters of the c1 column values.*/,
 	RIGHT('buyer_id',6) /* Partition the table by the last six characters of the c2 column values.*/
) 
PARTITIONS 8;

Comparison between COHASH partitioning, HASH partitioning, and KEY partitioning

The COHASH partitioning type is similar to the HASH and KEY partitioning types. The following table compares the types.

Usage notes

• You must ensure the similarity between the values of different partition key columns. Partition tables only check routing results. The values of different partition key columns in a COHASH partitioned table has similarity maintained by you. Therefore, the values of different partition key columns in the table in each row must be routed to the same partition. However, the similarity between the values of different partition key columns may be destroyed. You must ensure the similarity between the values of different partition key columns. PolarDB-X only check routing results but not the similarity.

  For example, you specify that the last four characters of the c1 and c2 column values are the same. If c1=1001234 and c2=1320 are routed to the shard 0, insert (c1,c2) values (100234,1320) is supported. However, the last four characters of the column values are different.

• Limits are imposed when DLM statements are used to modify partition key columns. The values of different partition key columns in a COHASH partitioned table are similar. Therefore, to prevent incorrect data distribution, PolarDB-X imposes the following limits when you use DML statements to modify the values of partition key columns in the table:

  • If the values of different partition key columns in the same row in the VALUES clause of the INSERT or REPLCAE statement are routed to different partitions, the values cannot be inserted and an error is reported.

  • If you want to use the SET clause of the UPDATE or UPSERT statement to modify the values of multiple partition key columns, you must modify the values of all partition key columns. For example, the c1 and c2 columns are partition key columns. You can use the UPDATE t1 SET c1='xx',c2='yy' WHERE id=1 statement to modify the values of the columns. After you use the SET clause to modify the values of multiple partition key columns, if the new values of the columns are routed to different partitions, the UPDATE or UPSERT statement is prohibited and an error is reported.

  • After you use the COHASH partitioning type for GSI tables, if all DML operations performed on the primary table, such as INSERT or UPDATE operations, cause that the values of different partition key columns in the GSI tables of the primary table in the same row are routed to different partitions, the operations are also prohibited and an error is reported.

• The prefix of partition key columns in a COHASH partitioned table is 0. The values of different partition key columns in a COHASH partitioned table are similar. Therefore, the columns need to be defined by partitioning functions such as SUBSTR, LEFT, or RIGHT. After some digits of the integer type are truncated, a prefix 0 generates. For example, you specify that the last four characters of the c1 and c2 column values are the same. If c1=1000034 and c2=34, the last four characters of the c1 column values are '0034'. After all original values of partition key columns in a COHASH partitioned table that use the integer type are truncated, the original values are automatically converted to the integer corresponding to the new values and then are routed. Therefore, the string '0034' is converted to the string '34'. Then, the hash of the string '34' is calculated and routed to partitions. This way, the prefix of the string '0034' is automatically processed.

Limits on partitioning functions

• RIGHT

• LEFT

• SUBSTR

Limits on data types

• Integer types: BIGINT, BIGINT UNSINGEDINT, INT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED.

• Fixed-point type: DECIMAL. The decimal part of this data type must be zero.

• Date and time types: Not supported.

• String types: CHAR and VARCHR.

Limits on syntaxes

• If a partitioning function is used for a partition key column, multiple nested partitioning functions are unavailable for the column. Example: SUBSTR(SUBSTR(c1,-6),-4).

• When you use the RANGE_HASH syntactic sugar, the last length value cannot be a negative.

• The types of all partition key columns must be identical in the following terms:

  • Charsets and collation.

  • Length and precision definition.

• By default, a partitioned table can contain up to 8,192 partitions.

• By default, a partition key can consist of up to five partition key columns.