Previous: Initial knowledge of MySQL HASH partition and LINEAR HASH partition

KEY partition

The KEY partition is similar to the HASH partition. Of course, there are differences

(1) In the HASH partition, you can use integer columns or expressions based on column values, that is, PARTITION BY HASH(expr), while in the KEY partition, you can directly use columns, PARTITION BY KEY(column_list), column_ LIST is a LIST of column names, which is a bit like LIST partition and LIST COLUMNS partition. One is based on column value or column value expression, and the other is directly based on column value or column value LIST;

(2) In the HASH partition, use the expression expr to modulo the partition number num MOD(expr,num) to calculate the partition number of the data row. In the KEY partition, use the MySQL server's HASH function, and the NDB cluster uses MD5(). For tables using other storage engines, the server uses its own internal HASH function, which is based on the same algorithm as PASSWORD();

(2) The KEY partition can receive 0 or more columns, that is, PARTITION BY KEY() or PARTITION BY KEY(column_list);

Create a KEY partition based on the INT type field:

CREATE TABLE k1 (
    id INT,
    name VARCHAR(20)
)
PARTITION BY KEY(id)
PARTITIONS 2;

Create a KEY partition based on VARCHAR type:

CREATE TABLE k1 (
    id INT,
    name VARCHAR(20)
)
PARTITION BY KEY(name)
PARTITIONS 2;

Create partitions based on multiple columns:

CREATE TABLE k1 (
    id INT ,
    name VARCHAR(20)
)
PARTITION BY KEY(id,name)
PARTITIONS 2;

(3) if the table has a primary key, any column used as the partition key must be the primary key of the table. If the column name is not specified as the partition key, the primary key of the table (if there is a primary key), for example

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

If no specific column is specified in KEY(), the primary key id is used as the partition key. Equivalent to

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY(id)
PARTITIONS 2;

Creating a KEY partition in the following way will report an error:

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY(name)
PARTITIONS 2;

As shown in the figure:

 

(4) If there is no primary key but there is a unique key, any column of the partition key must be part of the unique key. For example:

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

There is no primary key in k1, but a unique key id is defined. The unique key is used as the partition key.

If the only key is not defined as NOT NULL, an error will be reported as follows:

You can create a KEY partition table in the following three ways:

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id,name)
)
PARTITION BY KEY(id)
PARTITIONS 2;

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id,name)
)
PARTITION BY KEY(name)
PARTITIONS 2;

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id,name)
)
PARTITION BY KEY(id,name)
PARTITIONS 2;

However, creating a partition table in the following way will also report an error:

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id,name)
)
PARTITION BY KEY()
PARTITIONS 2;

The reason is that no column is specified in KEY().

Note: when inserting data into the KEY partition table, the partition to which the data is stored does not necessarily use modular operation in HASH partition.

LINEAR KEY partition

Similar to the LINEAR HASH partition, the LINEAR KEY partition uses the powers of two algorithm. The partition creation syntax is as follows:

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;