26.6.1 Partitioning Keys, Primary Keys, and Unique Keys — MySQL 8.4

26.6.1 Ключи разбиения, первичные ключи и уникальные ключи

В этом разделе рассматривается взаимосвязь между ключами разбиения, первичными ключами и уникальными ключами. Правило, определяющее эту взаимосвязь, можно сформулировать следующим образом: все столбцы, используемые в выражении разбиения для разбиеной таблицы, должны быть частью каждого уникального ключа, которым может обладать таблица.

Другими словами, каждый уникальный ключ в таблице должен использовать каждый столбец в выражении разбиения таблицы. (Это также относится к первичному ключу таблицы, так как он по определению является уникальным ключом. Этот частный случай рассматривается далее в этом разделе.) Например, каждое из следующих операторов создания таблицы является недопустимым:

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;

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;

В каждом случае предлагаемая таблица будет иметь по крайней мере один уникальный ключ, который не включает все столбцы, используемые в выражении разбиения.

Каждое из следующих утверждений является допустимым и представляет собой один из способов, с помощью которых можно привести соответствующее недопустимое утверждение создания таблицы в рабочее состояние:

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

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

Этот пример демонстрирует ошибку, возникающую в таких случаях:

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

Оператор CREATE TABLE завершается с ошибкой, потому что как col1, так и col3 включены в предлагаемый ключ разбиения, но ни один из этих столбцов не входит в состав обоих уникальных ключей в таблице. Это показывает одно возможное исправление недопустимого определения таблицы:

mysql> CREATE TABLE t3 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY (col1, col2, col3),
    ->     UNIQUE KEY (col3)
    -> )
    -> PARTITION BY HASH(col3)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)

В этом случае предлагаемый ключ разбиения col3 входит в состав обоих уникальных ключей, и оператор создания таблицы выполняется успешно.

Следующую таблицу нельзя разбить вообще, потому что нет способа включить в ключ разбиения какие-либо столбцы, которые принадлежат одновременно обоим уникальным ключам:

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

Поскольку каждый первичный ключ по определению является уникальным ключом, это ограничение также включает первичный ключ таблицы, если он есть. Например, следующие два оператора недопустимы:

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

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

В обоих случаях первичный ключ не включает все столбцы, указанные в выражении разбиения. Однако оба следующих оператора являются допустимыми:

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

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

Если у таблицы нет уникальных ключей — это включает в себя отсутствие первичного ключа — то это ограничение не применяется, и вы можете использовать любые столбцы в выражении разбиения, при условии, что тип столбца совместим с типом разбиения.

По той же причине вы не можете позже добавить уникальный ключ к разбиеной таблице, если ключ не включает все столбцы, используемые в выражении разбиения таблицы. Рассмотрим разбиено таблицу, созданную следующим образом:

mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
    ->     PARTITION BY RANGE(c1) (
    ->         PARTITION p0 VALUES LESS THAN (10),
    ->         PARTITION p1 VALUES LESS THAN (20),
    ->         PARTITION p2 VALUES LESS THAN (30),
    ->         PARTITION p3 VALUES LESS THAN (40)
    ->     );
Query OK, 0 rows affected (0.12 sec)

Можно добавить первичный ключ к t_no_pk, используя любой из этих операторов ALTER TABLE:

#  possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

#  use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Однако следующий оператор завершается с ошибкой, потому что c1 является частью ключа разбиения, но не является частью предлагаемого первичного ключа:

#  fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Поскольку у t_no_pk в выражении разбиения есть только c1, попытка добавить уникальный ключ по c2 в одиночку завершается с ошибкой. Однако вы можете добавить уникальный ключ, который использует как c1, так и c2.

Эти правила также применяются к существующим неразбитым таблицам, которые вы хотите разбить с помощью ALTER TABLE ... PARTITION BY. Рассмотрим таблицу np_pk, созданную следующим образом:

mysql> CREATE TABLE np_pk (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(50),
    ->     added DATE,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

Следующий оператор ALTER TABLE завершается с ошибкой, потому что столбец added не входит ни в один уникальный ключ в таблице:

mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH( TO_DAYS(added) )
    ->     PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Однако этот оператор, использующий столбец id в качестве столбца разбиения, является допустимым, как показано здесь:

mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH(id)
    ->     PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

В случае np_pk единственный столбец, который можно использовать в качестве части выражения разбиения, — это id; если вы хотите разбить эту таблицу с помощью других столбцов в выражении разбиения, вы должны сначала изменить таблицу, либо добавив желаемые столбцы в первичный ключ, либо полностью удалив первичный ключ.