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

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