Create a KEY-RANGE partitioned table - PolarDB

KEY-RANGE is a composite partitioning strategy that combines two levels of data organization: PARTITION BY KEY routes each row to a top-level partition based on a hash of the specified columns, then SUBPARTITION BY RANGE divides each partition into subpartitions based on a numeric boundary value. This lets you spread data across key-based partitions while applying range-based segmentation within each partition.

KEY-RANGE (KEY as the primary partition, RANGE as the subpartition) is a PolarDB for MySQL extension. Standard MySQL only supports the reverse combination — RANGE or LIST as the primary partition with HASH or KEY as the subpartition.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY [LINEAR] KEY (column_list)
  SUBPARTITION BY RANGE (expr)
  (partition_definition [, partition_definition] ...)

partition_definition:

PARTITION partition_name
  (subpartition_definition [, subpartition_definition] ...)

subpartition_definition:

SUBPARTITION subpartition_name
  VALUES LESS THAN {value | MAXVALUE}

Parameters

Parameter	Description
`table_name`	The name of the table.
`column_list`	The columns used to compute the KEY partition hash. Specify column names directly — expressions are not supported.
`expr`	The expression that determines which subpartition a row belongs to. Must be of the INT type; the string type is not supported.
`value`	The upper boundary value of a subpartition. Rows with a value less than this boundary fall into this subpartition.
`MAXVALUE`	A catchall boundary that matches any value greater than all explicit boundaries. Use this as the last subpartition boundary in each partition.
`partition_name`	The name of the top-level partition. Must be unique across the entire table.
`subpartition_name`	The name of the subpartition. Must be unique across the entire table.

Usage notes

Every partition must contain the same number of subpartitions. If you define subpartitions explicitly on any partition, you must define them on all partitions.
partition_name and subpartition_name must each be unique across the entire table, not just within a single partition.
The LINEAR keyword is optional in PARTITION BY KEY. Adding it changes the partitioning algorithm to use a power-of-two scheme, which can improve performance when adding or dropping partitions at the cost of less even data distribution.

In PARTITION BY KEY, you must explicitly specify the columns in column_list. Unlike some other partitioning types, KEY subpartitioning does not support a default column selection.

Example

The following example creates a sales_key_range table partitioned by dept_no using KEY partitioning, with each partition subdivided into four RANGE subpartitions on amount.

CREATE TABLE sales_key_range
(
  dept_no  VARCHAR(20),
  part_no  INT,
  country  VARCHAR(20),
  date     DATE,
  amount   INT
)
PARTITION BY KEY(dept_no)
SUBPARTITION BY RANGE(amount)
(
  PARTITION p0 (
    SUBPARTITION p0s0 VALUES LESS THAN (10000),
    SUBPARTITION p0s1 VALUES LESS THAN (20000),
    SUBPARTITION p0s2 VALUES LESS THAN (30000),
    SUBPARTITION p0s3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p1 (
    SUBPARTITION p1s0 VALUES LESS THAN (10000),
    SUBPARTITION p1s1 VALUES LESS THAN (20000),
    SUBPARTITION p1s2 VALUES LESS THAN (30000),
    SUBPARTITION p1s3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p2 (
    SUBPARTITION p2s0 VALUES LESS THAN (10000),
    SUBPARTITION p2s1 VALUES LESS THAN (20000),
    SUBPARTITION p2s2 VALUES LESS THAN (30000),
    SUBPARTITION p2s3 VALUES LESS THAN (MAXVALUE)
  )
);