All Products
Search

This Product

    PolarDB:KEY partitioning

    Document Center

    PolarDB:KEY partitioning

    Last Updated:Jun 06, 2024

    KEY partitioning is similar to HASH partitioning, which partitions data based on the built-in consistent hashing algorithm of PolarDB-X. Therefore, the routing algorithm for KEY partitioning in PolarDB-X is different from that in MySQL.

    The differences between KEY partitioning and HASH partitioning lie in the following aspects:

    • No partition key column may be specified in KEY partitioning. In this case, primary key columns are used as the partition key by default. If no primary key column is specified, partitioning is performed based on the unique key.

    • KEY partitioning supports vector partition keys. If a vector partition key that consists of multiple partition key columns is used in KEY partitioning, the first partition key column is used to route data by default.

    • KEY partitioning supports the following data types: INT, STRING, DATE, and DATETIME.

    Syntax

    CREATE TABLE ... 
PARTITION BY KEY(partition_column_list) 
PARTITIONS number;

partition_column_list:
  partition_column_list[, partition_column, partition_column, ...]
    Note

    For more information about the differences between HASH partitioning and KEY partitioning, see the Comparison between KEY partitioning and HASH partitioning table of the "Overview" topic.

    Usage notes

    • KEY partitioning does not support partitioning functions.

    • 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.

    Examples

    Use a single-column partition key

    Specify the id column as the single-column partition key for KEY partitioning and set the number of partitions to 8. 

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

    Use a vector partition key

    Use the vector partition key that consists of the bid and id columns for KEY partitioning and set the number of partitions to 8.

    Important

    By default, the first partition key column is used to calculate hash values. If a query contains the equality condition that involves the prefix partition key column, partition pruning conditions are met. The remaining partition key columns that are not used to route data such as id are used for hot data partition splitting. 

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

    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, DATE, and TIMESTAMP

    • String types: CHAR, VARCHR, and BINARY

    • Fixed-point type: DECIMAL, for which the number of digits in the fractional part must be 0

    Data distribution 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 if the number of different values of the partition key is greater than 3,000 based on the MurmurHash3 algorithm. Data is distributed in a more balanced way if the partition key has more different values.