DBMS 파티셔닝

내가 다루는 DB중에 150만 row가 있는 테이블이 있다. 그런데 UPSERT를 할 때 중복을 찾기 위해 전체 테이블 서치를 돌린다. 비록 Binary Search지만 해당 DB는 row수가 가파르게 늘어나는 테이블이었고 이는 합리적이지 않는 구조로 보였다. 이런 문제를 해결하기 위한 방법은 여러가지가 있다.

• DB 파티셔닝 (아카이브 테이블 만들기)
• 공간적 비용 역시 최소화하기 위해 150만 row중 사용하지 않는 데이터 다이어트
• IDX를 DESC옵션으로 새로 만들기 (Oracle의 경우 PK INDEX는 ASC로만 제공한다)
  ...

모든 방법에는 장단점이 있지만, 이 포스팅에서는 첫번째 DB 파티셔닝을 진행하면서 파티셔닝에 대해 공부하고, 실제로 파티셔닝을 진행하는 일에 대한 기록을 남긴다.
이 포스팅은 아래 공식 문서를 정리한 내용을 다수 포함하였다.

DBMS 파티셔닝이란?

이전에는 horizontal partitioning과 vertical partitioning을 모두 지원했고, 배웠다. 그러나 MySQL 8부터는 vertical partitioning을 지원하지 않으며, 앞으로도 지원할 계획이 없다고 한다. 따라서 이 포스팅에서는 horizontal partitioning만 다룬다.

• horizontal partitioning
  • -MAXVALUE<intval<=MAXVALUE 사이에서 수평으로 테이블을 자른다.
  • MySQL 8에서 파티셔닝을 하려면 DB엔진을 사용해야 한다. 현재 파티셔닝을 지원하는 엔진은 InnoDB와 NDB이다. MyISAM Merge csv FEDERATED는 지원하지 않는다.
    • 한국에서는 대부분의 경우 InnoDB를 사용한다. InnoDB가 디폴트 엔진이다. 간혹 MyISAM을 사용하는 경우도 있다. NDB는 InnoDB의 클러스터 분산공유 아키텍쳐이다.
  • NDB의 경우, key와 linear key를 기준으로만 나눌 수 있다.
    • 즉, 문자열 기준으로 A~K, L~Z같은 꼴로는 나눌 수 없다는 의미이다. 1~1000, 1001~ 같은 linear id 기준으로는 가능하다.
  • 만약에 tr_date에 대한 HASH를 기준으로 6개의 테이블로 파티셔닝한다고 하면 코드는 다음과 같다. 이 때, ENGINE을 명시하지 않으면 기본 DB인 InnoDB로 설정된다.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

• 단, partition의 기준이 되는 key는 unique해야 한다. 따라서 다음과 같은 partition 구문은 허용되지 않는다.

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;

• 대신 다음과 같은 경우는 valid하다.

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;

• 특이한 점은 Parititon key는 모든 Unique key에 속해 있어야 한다는 점이다. 가령 이 구문은 정상적으로 파티션이 되지만,

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;

• 이 테이블은 영영 파티션될 수 없다. 유니크 키 끼리 겹치는 컬럼이 하나도 없기 때문이다.

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

• PK도 유니크 키에 속하므로, 이 구문들도 정상적으로 파티션되지 않는다.

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)

• ALTER_TABLE을 사용해서 PK를 설정할 수 있다.

#  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

• 하지만 다음과 같이 c2를 기준으로 PK를 설정하려고 하면 c2가 Partition key에 포함되어 있지 않기 때문에 에러가 발생한다. 즉, partition key는 모든 unique key를 포함해야만 한다.

#  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

• 그렇기에 대부분의 경우, 파티셔닝은 여러 개의 유니크 키를 가진 테이블에 적합하지 않다.
  • 자료를 찾는 도중, 파티셔닝 자체가 정말 커다란ㅡ100M이상의 줄을 가진 테이블이 아니라면 크게 필요하지 않다는 의견도 있었다. 파티셔닝은 2008년에 나온 기술이고 무어의 법칙에 따라 프로세서의 성능이 크게 향상되었으며, 지금은 파티셔닝을 함으로서 발생하는 오버헤드가 DB 쿼리를 향상하는 것보다 더 크다는 의견이다.

📚MySQL 공식 문서 - Partitioning
📚MySQL 공식 문서 - 파티셔닝 키와 유니크 키에 대한 제약점
📰O. Jones - 파티셔닝과 유니크 키
📰Brent Ozar - 정말 파티셔닝이 필요한지 판단하는 방법
📰오라클, 파티션 테이블