mysql(5.6)パーティション(一)パーティションキー、プライマリキー、一意キー

このセクションでは、パーティション・キーとプライマリ・キーと一意キーの関係について説明します.この関係を管理するルールは、パーティション表のパーティション式で使用されるすべての列が、表が持つ可能性のある各一意のキーの一部である必要があることを示します.
すなわち、テーブル上の各一意のキーは、テーブルのパーティション式の各カラムを使用する必要があります.(定義に基づいて一意のキーであるため、この特定の状況はこのセクションで後述する)たとえば、次の各テーブル作成文は無効です.

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;

いずれの場合も、提案されたテーブルには、パーティション式で使用されるすべてのカラムを含まない少なくとも1つの一意のキーが含まれます.
次の各文は有効であり、対応する無効なテーブルで文を作成する方法を示します.

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文は、両方のcol 1とcol 3が推奨パーティションキーに含まれているため、失敗しました.ただし、テーブル上の唯一のキーの一部である列はありません.これにより、無効なテーブル定義の修復が可能になります.

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)

この場合、提案されたパーティションキーcol 3は一意のキーの一部であり、テーブル作成文は成功した.
次の表は、2つの一意のキーに属する列をパーティション・キーに含めることができないため、完全にパーティション化できません.

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)
);

各プライマリ・キーは一意のキーであるため、この制限にはテーブルのプライマリ・キーも含まれます(存在する場合).たとえば、次の2つの文は無効です.

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;

どちらの場合も、プライマリ・キーにはパーティション式で参照されるすべてのカラムは含まれません.ただし、次の2つの文は有効です.

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

ただし、次の文は失敗しました.c 1はパーティションキーの一部ですが、提案されたプライマリキーの一部ではありません.

#  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はc 1のみのパーティション式で、c 2単独で一意のキーを追加しようとしたが失敗した.ただし、2つのユニークなキーc 1とc 2を追加することができます.
これらの規則は、パーティションを使用する既存の非パーティションテーブル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である.パーティション式の他のカラムまたはカラムを使用してテーブルをパーティション化する場合は、必要なカラムまたはカラムをプライマリ・キーに追加するか、プライマリ・キーを完全に削除するか、テーブルを変更する必要があります.