Create KEY Partitioned Tables for Scalable MySQL Distribution - PolarDB

KEY partitioning distributes data across partitions using MySQL's internal hash function. Unlike hash partitioning, KEY partitioning supports multiple partition key columns and a broader set of column types.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY [LINEAR] KEY(column_list) [PARTITIONS number]
  (partition_definition [, partition_definition] ...);

partition_definition is:

PARTITION partition_name

Parameters

Parameter	Description
`column_list`	The partition key columns. Specify zero or more columns. Supported types: INT, string types, DATE, TIME, and DATETIME.
`partition_name`	The partition name. Must be unique within the table.
`number`	The number of partitions.

How KEY partitioning works

KEY partitioning uses the same internal hash algorithm as MySQL. KEY partitioning supports multiple columns and the following types: INT, string types, DATE, TIME, and DATETIME.

When `column_list` is omitted, PolarDB applies this fallback order:

The primary key is used as the partition key.
If no primary key exists, the unique key is used instead.

KEY partitioning also supports the LINEAR KEY extended type.

Examples

Partition by a single column

Partition a sales table by s_id across 11 partitions:

CREATE TABLE sales_key
(
  s_id        VARCHAR(20),
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
) PARTITION BY KEY (s_id)
PARTITIONS 11;

Use LINEAR KEY

Use LINEAR KEY to create a linear key partitioned table:

CREATE TABLE sales_linear_key
(
  s_id        VARCHAR(20),
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
) PARTITION BY LINEAR KEY (s_id)
PARTITIONS 11;