KEY partitioning concept, syntax, and usage - PolarDB

Use KEY partitioning when your natural partition key is a string, date, or composite column. Unlike HASH partitioning, which requires an integer expression, KEY partitioning supports non-integer column types and multi-column partition keys — making it the better fit for most real-world schemas.

Note

The routing algorithm for KEY partitioning in PolarDB-X uses MurmurHash3, which differs from MySQL's KEY partitioning algorithm.

How KEY partitioning differs from HASH partitioning

Behavior	KEY partitioning	HASH partitioning
Partition key required	No — omitting the partition key uses the primary key by default; if no primary key exists, the unique key is used	Yes — must specify an integer expression or column
Multi-column partition keys	Supported (vector partition key)	Not supported
Supported data types	INT, STRING, DATE, DATETIME (and their subtypes)	Integer types only
Partitioning functions	Not supported	Supported

For a full comparison, see Comparison between KEY partitioning and HASH partitioning.

Syntax

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

partition_column_list:
  column_name [, column_name ...]

Parameter	Description
`partition_column_list`	One or more columns used as the partition key. If omitted, the primary key is used. If no primary key exists, the unique key is used.
`number`	Number of partitions. Maximum: 8,192.

Examples

Single-column partition key

Partition the table by the id column into 8 partitions:

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;

Vector partition key (multi-column)

Use bid and id as a two-column partition key, with 8 partitions:

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;

Important

With a vector partition key, only the first column (bid in this example) determines which partition a row goes to. The remaining columns (id) are not used for routing — they exist to support hot data partition splitting. Partition pruning applies when a query includes an equality condition on the leading column (bid).

Limits

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, BINARY
Fixed-point	DECIMAL (fractional digits must be 0)

Other limits

KEY partitioning does not support partitioning functions.
By default, a partitioned table can have at most 8,192 partitions.
By default, a partition key can include at most five columns.

Data distribution

KEY partitioning uses MurmurHash3, which has low hash collision rates and high performance. Distribution across partitions becomes balanced when the partition key has more than 3,000 distinct values; more distinct values produce more even distribution.