Skip to content

Description of MatrixOne DDL statement partition support

1. Partition types supported by MatrixOne

Currently, the 6 partition types supported by MatrixOne's DDL statements are basically the same as the MySQL official website, as follows:

  • KEY Partitioning
  • HASH Partitioning

Currently, subpartitioning syntax is supported, but planned construction is not supported.

2. Description of partition keys

The relationship between Partition Keys, Primary Keys and Unique Keys

The relationship rules of Partition Keys, Primary Keys and Unique Keys can be summarized as:

All columns used in the partition expression of a partition table must be part of each unique key that the table may have.

Note

The only keys include PrimaryKey and Unique KEY.

​ That is, each unique key on the table must use each column in the table's partition expression. The unique key also includes the primary key of the table, because by definition, the primary key of the table is also a unique key.

Example Description

For example, each of the following table creation statements is invalid:

> CREATE TABLE t1 (
      col1 INT NOT NULL,
      col2 DATE NOT NULL,
      col3 INT NOT NULL,
      col4 INT NOT NULL,
      UNIQUE KEY (col1, col2)
  )
  PARTITION BY HASH(col3)
  PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

> CREATE TABLE t2 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
     UNIQUE KEY (col1),
     UNIQUE KEY (col3)
 )
  PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

About KEY Partition Key is NULL

  1. KEY accepts only lists of zero or more column names. In the case where the table has a primary key, any column used as a partition key must contain part or all of the table primary key.

    If no column name is specified as partition key, the primary key of the table is used (if any). For example, the following CREATE TABLE statement is valid in MySQL.

  2. If there is no primary key, but UNIQUE KEY, then UNIQUE KEY is used for partition keys.

For example, in the following table creation statement, the KEY partition partition key is NULL, and no primary key is defined, but it contains a unique key. When building a partition expression, a unique key is used as the partition key:

CREATE TABLE t1 (
 col1 INT NOT NULL,
 col2 DATE NOT NULL,
 col3 INT NOT NULL,
 col4 INT NOT NULL,
 UNIQUE KEY (col1, col2)
)
PARTITION BY KEY()
PARTITIONS 4;

Note

Other partition rules are basically consistent with MySQL.

3. Description of MatrixOne partition expressions

When a DDL statement builds a partition table, a partition expression is generated for each partition definition, which can be used to calculate the partition to which the data belongs.

During the planning construction phase, the partition information data structure in the DDL statement is plan.PartitionInfo:

type PartitionInfo struct {
 Type PartitionType
 Expr *Expr
 PartitionExpression *Expr
 Columns []*Expr
 PartitionColumns []string
 PartitionNum uint64
 Partitions []*PartitionItem
 Algorithm int64
 IsSubPartition bool
 PartitionMsg string
}

Where PartitionExpression is a partition expression. The partition expression is MatrixOne. The method of converting the partition clause into an expression for processing. The construction method of each partition expression is as follows:

KEY Partitioning

The KEY partition will construct a partition expression based on the partition key and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY (col1, col2)
)
PARTITION BY KEY(col1)
PARTITIONS 4;

HASH Partitioning

Similar to KEY partition, the HASH partition will build a partition expression based on the partition function and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR HASH( YEAR(col3))
PARTITIONS 6;