22.6.1 Partitioning Keys, Primary Keys, and Unique Keys — MySQL 5.7

22.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; если вы хотите разбить эту таблицу с использованием других столбцов или столбцов в выражении разбиения, вы должны сначала изменить таблицу, либо добавив желаемые столбцы или столбцы к первичному ключу, либо полностью удалив первичный ключ.