[MySQL] Partition 과 Unique Key

 

Partitioning 실습을 따라하다가 강의 내용과 다르게 Primary key를 설정했다가 Partitioning을 실패하는 결과를 보았다.

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

원인을 찾던 중 MySQL 공식 문서에 해당 문제화 관련된 글을 발견하여 해석 및 정리해 보았다.

---

https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html

MySQL :: MySQL 8.0 Reference Manual :: 24.6.1 Partitioning Keys, Primary Keys, and Unique Keys

 

Partitioning key(partition 을 나누는 기준이 되는 column(들)을 의미하는 듯.) 와 Unique key(Primary key 까지 포함) 의 관계는 다음처럼 표현할 수 있다: Partitioning 수식에 사용된 모든(all) column들은 반드시 각(every) Unique key 들의 일부여야 한다. 

즉, Table의 모든 Unique Key들은 각자 Partitioning 수식에 사용된 모든 Column들을 사용하여 구성되어야 한다.

다음 사례들은 Partitioning Key로 사용된 모든 Column 들이 모든 Unique Key들에 각각 포함되지 않아 유효하지 않다.

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;

위 사례들은 다음처럼 변경하면 유효해진다. (Unique key 를 한 개만 설정하여 유효하게 하는 방법)

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;

 

Unique Key가 두 개 이상일 때는 다음처럼 사용해야 한다.

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

위 예시는 다음처럼 변경되어야 유효해진다.

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)

 

Partitioning key를 구성하려는 모든 Column들이 모든 Unique key들에 포함될 수 없다면 그 Table은 Partitioning을 진행할 수 없다. (아래의 경우)

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

Primary key도 Unique key에 속하므로 위와 동일한 규칙을 적용 받는다.

 

(내가 추가)

Partitioning 수식에 사용되는 Column(들)은 수식 내에서 내장 함수의 Parameter로 사용될 수도 있다. = 모든 Unique key 각각에 포함되기만 한다면 각 Column들은 어떠한 형태로 사용되든 상관이 없다.

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;

 

Unique key가 없는 Table에서는 Partitioning type으로 사용만 가능하다면 어떠한 Coloumn이든 Partition key로 사용할 수 있다.

같은 이유로 Partitioning 이 이뤄진 Table에는 함부로 Unique key(Primary key 포함)를 추가할 수 없다. 하지만 Partitioning에 사용된 모든 Column을 포함하는 Unique key라면 사후에도 추가가 가능하다.

# Partitiong을 먼저 진행한 Table
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)

# 다음의 경우에는 사후에라도 Unique key 추가가 가능하다.

#  possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1); # 모든 Primary key가 Partitioning key로 사용된 Column을 포함함.
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); # 모든 Primary key가 Partitioning key로 사용된 Column을 포함함.
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

# 하지만 아래의 경우 추가하려는 Primary key에 Partitiong key로 사용된 Column이 포함되지 않으므로 유효하지 않다.
#  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

위의 논리는 ALTER TABLE을 이용한 Partitioning에도 그대로 적용이 된다.

---

⇒ Partitioning 과 Primary key 간 관계가 생각보다 밀접하다. Table 생성 단계에서부터 Partitioning을 고려하지 않으면 나중에 Partitioning이 필요한 상황이 닥쳤을 때, Primary key 부터 수정해야 하는 상황이 올 수도 있어 보인다.

⇒ 그럼 왜 Partitioning key는 Unique key에 일부여야 한다는 제약 사항이 존재하는 것일까? Unique key가 없는 Table에서는 Partitioning이 자유로우면서: Real MySQL8.0 2권에서 해당 질문에 대한 답처럼 보이는 문장을 발견했다. 그러나 아직 지금의 수준으로서는 완전히 이해할 수가 없었다…