Create a partitioned table with HASH partitioning - PolarDB

HASH partitioning distributes data evenly across partitions using the MurmurHash3 consistent hashing algorithm. This topic describes the HASH partitioning policy for PolarDB-X databases in automatic partitioning mode. Unlike KEY partitioning, HASH partitioning supports partitioning functions on single-column partition keys, giving you control over how values are transformed before hashing.

For a side-by-side comparison of HASH partitioning and KEY partitioning, see the Comparison between KEY partitioning and HASH partitioning table in the partition types overview.

Note

The HASH partitioning routing algorithm in PolarDB-X differs from MySQL.

Syntax

CREATE TABLE ...
PARTITION BY HASH(partition_expr)
PARTITIONS number;

partition_expr accepts either of the following forms:

Form	Description	Example
`partition_column_list`	One or more partition key columns	`HASH(id)`, `HASH(id, bid)`
`partition_func(partition_column)`	A partitioning function applied to a single column	`HASH(TO_DAYS(birthday))`

Supported partitioning functions

TO_DAYS, TO_MONTHS, TO_WEEKS, TO_SECOND, UNIX_TIMESTAMP, MONTH, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, SUBSTR, SUBSTRING, RIGHT, LEFT

Note

Partitioning functions apply only to single-column partition keys. Nested partitioning functions are not supported.

Usage notes

Partitioning functions are supported only for single-column partition keys. The supported data types for partition key columns depend on the specific partitioning function used.
Vector (multi-column) partition keys do not support partitioning functions.
HASH partitioning does not support hot data partition splitting.
A partitioned table can contain up to 8,192 partitions by default.
A partition key can consist of up to five columns by default.
If a partition key column uses a time zone-sensitive type such as TIMESTAMP, use the UNIX_TIMESTAMP partitioning function for that column.

Examples

Use a partitioning function

Partition by the birthday column (DATETIME type) using TO_DAYS to convert date values to day counts before hashing. The table is split into 8 partitions.

CREATE TABLE tb_h_fn(
  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;

Use a single-column partition key

Partition by the id column directly. The table is split into 8 partitions.

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

Use a vector partition key

Partition by both the id and bid columns. The table is split into 8 partitions.

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

Important

When using a vector partition key, partition pruning only applies to queries that include equality conditions on all columns in the partition key. In this example, a query must filter on both id and bid to benefit from partition pruning.

Limitations

Supported data types

Category	Types
Integer	`BIGINT`, `BIGINT UNSIGNED`, `INT`, `INT UNSIGNED`, `MEDIUMINT`, `MEDIUMINT UNSIGNED`, `SMALLINT`, `SMALLINT UNSIGNED`, `TINYINT`, `TINYINT UNSIGNED`
Date and time	`DATETIME`, `DATE`, `TIMESTAMP`
String	`CHAR`, `VARCHAR`

Data distribution and partition key selection

HASH partitioning uses MurmurHash3, a consistent hashing algorithm with low collision rates and high performance. However, MurmurHash3 does not guarantee balanced distribution when the partition key has few distinct values.

Distribution becomes balanced when the number of distinct partition key values exceeds 3,000. More distinct values produce more balanced distribution across partitions.