참여중인 오픈채팅방에서 샤딩과 파티셔닝을 주제로 대화하는 것을 보게 되었습니다.
하지만 대강 키워드만 들어본지라 대화 내용을 전부 이해하지는 못했습니다. 그래서 파티셔닝부터 공부하기로 했습니다.
이번 포스팅에서는 MySQL 공식 문서를 보면서 공부한 내용을 정리합니다.
MySQL :: MySQL 8.4 Reference Manual :: 26 Partitioning
MySQL 8.4 Reference Manual / Partitioning This chapter discusses user-defined partitioning. In MySQL 8.4, partitioning support is provided by the InnoDB and NDB storage engines. MySQL 8.4 does not currently support partitioning of tables using any stor
dev.mysql.com
Partitioning (파티셔닝)이란
유저의 정보를 보관하는 유저 테이블이 있습니다. 그런데 유저 수가 천만명이 넘을정도로 계속해서 데이터가 저장 된다고 하면
유저 테이블의 크기는 더욱 더 커지게 됩니다. 그런데 테이블의 데이터 크기가 커질수록 성능 저하 문제가 발생할 수도 있습니다.
1. 저장된 데이터만큼 사용되고 있는 인덱스의 크기도 점점 증가 됩니다.
조회 작업을 하는 경우에는 많은 인덱스를 탐색해야하고 성능 저하에 문제가 발생할 수 있습니다.
쓰기 작업을 하는 경우에는 인덱스 쓰기 작업도 발생하며 중간에 쓰기 작업이 발생하는 경우 페이지 교체가 발생하게 됩니다..
데이터 크기가 클수록 많은 페이지가 존재하기에 교체 작업에 오버헤드가 더 크게 발생할 수 있습니다.
2. 풀스캔을 해야하는 경우 읽어야 할 데이터가 많아집니다.
만약 옵티마이저가 풀스캔을 해야한다고 판단하면 테이블의 전체 데이터를 스캔하게 됩니다. 즉 테이블의 데이터가 많아질수록
풀스캔을 해야하는 작업은 매우 무거운 작업이 됩니다.
위 그림처럼 테이블의 데이터를 분할해서 여러 공간에 나누어서 보관하는 방법을 파티셔닝이라고 표현합니다.
예시 그림은 범위를 기준으로 분할을 했지만 범위 이외에도 다양한 기준으로 파티셔닝을 진행할 수 있습니다.
공식문서가 설명하는 파티셔닝의 이점은 아래와 같습니다.
1. 단일 디스크 또는 파일 시스템 파티션에 저장할 수 있는 것보다 더 많은 데이터를 하나의 테이블에 저장할 수 있음
테이블의 파티셔닝을 설정할 때 각 파티션이 저장될 디스크/위치를 지정할 수 있습니다.
예를 들어 현재 데이터베이스의 디스크 A의 용량은 1TB인데 User Table의 데이터 크기가 1TB가 넘으려고 한다면
새로운 B 디스크를 추가한 뒤 User Table Partition 1은 기존 디스크, Partition 2는 새로 추가한 B 디스크에 저장되도록 할 수 있습니다.
2. 유효성을 잃은 데이터를 쉽게 삭제할 수 있으며 반대로 특정 데이터를 저장하는 과정이 간소화 됨
탈퇴한 날짜를 기준으로 유저 탈퇴 테이블의 파티션닝을 진행하였다고 가정하겠습니다.
Partition1에는 2021년, Partition2에는 2022년, Partition3에는 2023년, Partition4에는 2024년, Partition5에는 2025년에 탈퇴한 유저들의 데이터가 존재합니다.
데이터 보관 정책에 의해 2021년에 탈퇴한 유저의 정보를 제거해야 한다면 2021년에 탈퇴한 유저들의 정보는 Partition1에 분할되어
있으므로 단순히 Partition1을 이용해서 제거하면 됩니다.
파티셔닝을 사용하지 않았다면 where 조건을 통해 해당하는 데이터를 찾은 다음 제거하는 과정으로 수행되기에 비교적 더 복잡합니다.
또한 2025년에 탈퇴하는 유저가 추가 된다면 단순히 Partition5로 Insert되기만 하면 되기에 Insert 작업 또한 단순해집니다.
Index는 Partition 개별적으로 생성되기에 자연스럽게 Index 쓰기 작업의 오버헤드도 감소할 수 있습니다.
3. Where 조건에 파티션을 특정할 수 있다면 조회 성능이 매우 상승 됨 (파티션 프루닝)
Partition1에는 2021년, Partition2에는 2022년, Partition3에는 2023년, Partition4에는 2024년, Partition5에는 2025년에 가입한 유저들이 존재한다고 가정하겠습니다. 그리고 해당 테이블에는 가입년도를 의미하는 joined_year 컬럼을 기준으로 파티셔닝 되었습니다.
만약 SELECT * from user where joined_year = 2025로 조회한다면 파티셔닝이 joined_year로 진행되었기에
DB는 2025년에 가입한 유저들의 정보가 존재하는 Partition5만 조회하면 된다는 사실을 알게됩니다.
이처럼 조회해야할 파티셔닝을 가지치기 한다는 의미로 파티션 프루닝이 사용되면 파티셔닝의 장점이 매우 높아지게 됩니다.
즉 파티셔닝은 파티션 프루닝을 유용하게 쓸 수 있는 상황이 자주 발생하면 매우 효과적입니다.
반면에 파티셔닝을 했지만 파티션 프루닝을 사용하는 상황이 발생되지 않는다면 결과적으론 나누어진 모든 파티션을 수행하면서
해당하는 데이터를 찾아야하기에 오히려 더 오버헤드가 발생할 수 있습니다.
Partitioning Types - 파티셔닝 종류
MySQL InnoDB은 여러 종류의 파티셔닝을 지원합니다.
1. Range Partitioning - 범위 파티셔닝
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
범위 파티셔닝은 말 그대로 특정 컬럼을 기준으로 범위를 나누어 저장합니다.
위 SQL문을 보면 store_id를 기준으로 범위를 나누는 것을 볼 수 있습니다.
store_id가 6보다 작으면 p0에, 11보다 작으면 p1에, 16보다 작으면 p2에, 21보다 작으면 p3에 저장되도록 파티셔닝을 지정합니다.
그런데 만약 store_id가 30인 값이 Insert 된다면 어떻게 될까요? 제일 마지막 파티셔닝인 p3은 21보다 작은 값이 저장됩니다.
즉 파티셔닝의 정의로는 store_id가 30인 데이터는 어느 파티션에도 해당되지 않습니다. 따라서 Insert를 하게 되면 오류가 발생합니다.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
MAXVALUE라는 키워드를 통해 처리한다면 모든 정수 데이터가 포함되기에 특정 데이터가 그 어느 파티션에도 속하지 않는 상황을
방지할 수 있습니다.
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
일반적으로 범위 파티셔닝은 시간 범위, 간격 기반을 기준으로 할 때 유용하게 사용될 수 있습니다.
2. List Partitioning - 리스트 파티셔닝
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
리스트 파티셔닝은 특정 컬럼이 어느 데이터에 속하는지를 기준으로 합니다.
위 SQL에서는 store_id의 값이 (3,5,6,9,17)이라면 pNorth 파티션에, (1,2,10,11,19,20)이라면 pEast 파티션에,
(4,12,13,14,18)이라면 pWest 파티션에, (7,8,15,16)이라면 pCentral 파티션에 저장되도록 지정합니다.
범위 파티셔닝과 동일하게 그 어느 파티션에도 속하지 않는 store_id 값이 Insert 되는 경우 (예를 들어 100) 오류가 발생합니다.
하지만 범위 파티셔닝의 경우 MAXVALUE처럼 모든 데이터를 포함하도록 할 수는 없습니다.
따라서 리스트 파티셔닝을 사용하는 테이블의 경우 기준이 되는 컬럼의 데이터가 유효한지 검사를 하는 과정이 중요할 것 같습니다.
3. Range Columns Partitioning - 범위 컬럼 파티셔닝
범위 컬럼 파티셔닝은 일반 범위 파티셔닝과 유사합니다. 하지만 기준 컬럼이 여러개 지정될 수 있으며 꼭 정수형이 아니여도 됩니다.
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c) (
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.15 sec)
위 SQL은 범위 컬럼 파티셔닝을 사용하는 테이블을 생성합니다. 파티셔닝 기준 컬럼은 a,d,c로 총 3개이며 c의 경우는 정수형이 아닌
문자열 컬럼입니다. 다중 컬럼 기준이기에 동작방식은 인덱스, 사전 순처럼 동작합니다.
CREATE TABLE r1 (
a INT,
b INT
)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
# Result p1 Count = 3
일반 범위 파티셔닝 경우에는 LESS THAN 이라는 표현 때문에 위 SQL 기준 5와 동일한 값이 Insert 되면 p1에 저장되었습니다.
하지만 범위 컬럼 파티셔닝의 경우 컬럼이 여러개 일수 있기 때문에 컬럼들을 튜플로 취급하여 튜플의 대소 관계를 비교합니다.
CREATE TABLE rc4 (
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
튜플 자체를 비교하는 성질 때문에 위와 같이 일부 컬럼이 중복 되는 범위의 파티셔닝 지정이 가능합니다.
(다만 파티션 범위 선언의 경우 증가하는 순서여야 함. p0 < p1 < p2 < p3)
예시로 (-1, 24, 40)가 Insert 된다면 제일 먼저 p0과의 비교를 진행하게 됩니다.
튜플의 대소 비교는 왼쪽 원소부터 진행되므로 -1과 0을 비교하게 됩니다.
첫번째 원소 비교의 경우 -1 < 0 이므로 튜플 대소 비교 원칙에 의해 Insert < p0을 만족합니다. 따라서 p0에 저장됩니다.
(10, 20, 80)이 Insert 된다면 제일 먼저 p0과의 비교를 하게 됩니다.
첫번째 원소 비교의 경우 10 > 0 이므로 튜플 대소 비교 원칙에 의해 Insert > p0이 됩니다. 따라서 Insert 되고자 하는 값이
p0의 범위보다 크기에 저장될 수 없습니다.
이제 p1과 비교를 하게 됩니다.
첫번째 원소 비교의 경우 10 == 10으로 동일합니다. 따라서 두번째 원소 비교를 진행합니다.
두번째 원소 또한 20 == 20으로 동일합니다. 따라서 세번째 원소 비교를 진행합니다.
80 < 100 이므로 튜플 대소 비교 원칙에 의해 Insert < p1을 만족합니다. 따라서 p1에 저장됩니다.
(10, 20, 110)이 Insert 된다면 제일 먼저 p0과 비교를 하게 됩니다.
위와 동일하게 p0은 범위에 포함되지 못하므로 p1과 비교하게 됩니다.
p1과 첫번째 원소 및 두번째 원소는 동일하므로 세번째 원소를 비교합니다.
110 > 100 이므로 p1의 범위 조건에도 충족되지 못하므로 p2와 비교하게 됩니다.
p2와 첫번째 원소는 동일하므로 두번째 원소를 비교하게 됩니다. 20 < 30 이므로 Insert < p2이므로 p2에 저장됩니다.
이처럼 Insert 하고자 하는 값을 첫번째 파티션부터 순차적으로 순회하며 범위에 만족하는 파티션에 저장됩니다.
4. List Columns Partitioning - 리스트 컬럼 파티셔닝
리스트 컬럼 파티셔닝은 리스트 파티셔닝과 유사합니다. 하지만 기준 컬럼이 2개 이상일수 있으며 꼭 정수형이 아닌 문자열 유형도
가능합니다.
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
기준이 되는 컬럼이 정수형이 아니여도 되기에 위 처럼 IN 절에 문자열이 사용될 수 있습니다.
5. Hash Partitioning - 해시 파티셔닝
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
해시 파티셔닝은 해시 함수를 사용하여 최대한 데이터를 골고루 분배할 수 있도록 합니다.
위의 경우는 store_id를 해시키로 만드는 데 사용합니다. 해시키의 결과에 따라 4개의 파티션 중 한 곳으로 정해져 저장됩니다.
일반적으로 별도의 해시 함수를 따로 사용하지 않는 이상 InnoDB는 MOD 연산을 통해 처리 합니다.
예를 들어 store_id가 100이라면 MOD(100, 4) = 0이므로 파티션 0에 저장됩니다.
store_id가 10이라면 MOD(10, 4) = 2이므로 파티션 2에 저장됩니다.
따라서 해시의 결과는 정수로 반환되어야 합니다.
6. Key Partitioning - 키 파티셔닝
CREATE TABLE k1 (
id INT,
name VARCHAR(20)
)
PARTITION BY KEY(name)
PARTITIONS 2;
키 파티셔닝은 해시 파티셔닝과 유사합니다. 하지만 키 파티셔닝의 경우 데이터베이스에서 자체적으로 해싱 처리 하기 때문에
꼭 값을 정수로 반환 해야 할 필요가 없습니다. 따라서 단순히 파티셔닝의 기준을 문자열로 설정할 수 있습니다.
위 SQL은 name 컬럼을 기준으로 파티셔닝을 진행합니다. 어차피 저장될 name 값에 따라 내부적으로 알아서 해싱처리가 되기에
추가적으로 필요한 작업은 없습니다.
CREATE TABLE k1 (
id INT,
name VARCHAR(20)
)
PARTITION BY KEY(id, name)
PARTITIONS 2;
키 파티셔닝의 경우 기준 컬럼을 여러 개 선택할 수 도 있습니다. 위 SQL로 인해 id, name 값을 이용하여 내부적으로 해싱처리하고
그 값의 결과에 따라 어느 파티션에 저장될 지 정해지게 됩니다.
7. Sub Partitioning - 서브 파티셔닝
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
InnoDB는 파티셔닝 된 테이블을 또 파티셔닝 하는 서브 파티셔닝을 지원합니다.
위 SQL의 경우 주 파티셔닝 기준은 purchased의 년도입니다. 1990년도 미만이면 p0 파티션에, 2000년도 미만이면 p1 파티션에,
그 외 년도는 p2 파티션에 저장됩니다.
그런데 이 p0, p1, p2를 또 파티셔닝 합니다. 즉 서브 파티셔닝을 진행하게 되는데요. 위 SQL을 보면 서브 파티셔닝은 해시 파티셔닝으로 진행되며 기준은 purchased의 날짜입니다. 따라서 p0, p1, p2이 또 날짜에 의해 2개로 분리 되어 총 6개의 공간이 생기게 됩니다.
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
위 SQL 처럼 직접 서브 파티셔닝을 명시적으로 정의할 수 있습니다.
현재 서브 파티셔닝은 메인 파티셔닝이 RANGE, LIST일 때만 가능하고 서브 파티셔닝으로 HASH, KEY만 사용이 가능합니다.
Partitioning의 NULL 처리
파티셔닝 된 테이블에 Insert 될 때 기준 컬럼의 값이 NULL로 전달 된다면 각 파티셔닝 종류에 따라 어떻게 처리되는지 설명합니다.
Range
범위 파티셔닝의 경우 NULL이 전달되면 제일 낮은 파티션에 저장됩니다.
mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (10),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
위와 같이 t1, t2 테이블을 범위 파티셔닝으로 생성합니다.
t1의 경우 0 미만일 경우 가장 낮은 p0 파티션에, t2의 경우 -5 미만일 경우 가장 낮은 p0 파티션에 저장됩니다.
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 1 | 20 | 20 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 1 | 20 | 20 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
t1, t2에 insert를 할 때 기준이 되는 컬럼 id 값을 null로 전달한 결과 모두 제일 낮은 파티션 p0에 저장되었음을 볼 수 있습니다.
List
리스트 파티셔닝의 경우 위에서 언급된 것 처럼 MAXVALUE와 같이 모든 범위를 지정할 수 있는 방법도 없기에 정확히 정해진 값만
저장될 수 있다고 하였습니다.
mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL
따라서 정의되지 않은 값이 insert 될 때 오류가 발생하는 것과 동일하게 NULL도 정의되어 있지 않았다면 오류가 발생합니다.
mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
그러나 위 SQL처럼 특정 파티션에 NULL을 포함되도록 지정하는 것은 가능합니다.
Hash / Key
해시 파티셔닝과 키 파티셔닝의 경우 NULL이 전달되면 0으로 반환됩니다.
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
만약 c1 컬럼을 해시키를 생성하는데 사용하는데 c1이 NULL 값으로 전달되면 0으로 표현됩니다.
따라서 (0 MOD 2) = 0 으로 처리되어 partition 0에 저장됩니다.
Partitioning Pruning - 파티션 프루닝
파티션 프루닝은 파티셔닝을 사용할 때 꼭 사용되어야 할 존재입니다. 파티션 프루닝은 파티셔닝 된 테이블을 조회하는 과정을
최적화하는 것으로 탐색될 필요가 없는 파티션 테이블을 유추할 수 있도록 합니다.
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
region_code를 사용한 범위 파티셔닝 테이블 t1이 있다고 가정하겠습니다.
SELECT fname, lname, region_code, dob
FROM t1
WHERE region_code > 125 AND region_code < 130;
만약 SELECT SQL를 사용할 때 기준이 된 컬럼 region_code의 범위를 where로 필터링 했다고 가정하겠습니다.
즉 region code가 125보다 크고 130보다 작은 데이터만 조회하라고 힌트를 주었습니다.
따라서 DB 입장에서는 p1, p2 파티션만 접근하면 됩니다. p0, p3은 접근할 필요가 없습니다.
이처럼 탐색할필요 없는 파티션 테이블을 제거하는 것이 파티션 프루닝입니다.
범위 파티셔닝 이외에도 리스트 파티셔닝, 해시 파티셔닝, 키 파티셔닝 모두 가능합니다.
CREATE TABLE t4 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;
SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;
DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;
위 2개의 SELECT는 where 조건에 의해 region_code의 범위가 3,4,5로 정해지게 되었습니다. 따라서 DB는 3, 4, 5를 해싱 함수에
사용하여 어떤 파티션에 존재하는지 확인하고 해당 파티션에만 접근하게 됩니다.
마지막 DELETE 문은 where문에 의해 4, 5, 6, 7, 8, 9, 10, 11, 12를 해싱 함수로 사용하게 되는데요.
파티션 프루닝이 진행될 것이라고 생각할 수 있지만 그렇지 않습니다.
공식문서에서는 파티션 프루닝의 조건은 범위 크기가 파티션 수보다 작은 경우에만 동작한다고 되어있습니다.
DELETE 문의 where 문 범위는 총 9개이지만 파티션의 개수는 8개입니다.
즉 where문의 범위(9개)가 파티션의 개수(8개)보다 크기 때문에 높은 확률로 모든 파티션을 탐색하게 될 수 있기 때문입니다.
만약 파티셔닝 테이블을 조회할 때 파티션 프루닝이 사용되지 않는다면 모든 파티션 테이블에 접근해서 해당되는 데이터를 조회해야 하기에
성능이 매우 저하될 수 있습니다. 따라서 파티셔닝을 한다면 파티션 프루닝이 잘 사용될 수 있는지 판단하는 것도 중요합니다.
Partitioning Keys, Primary Keys, and Unique Keys
지금까지 사용된 대부분의 예제들은 PK 및 Unique Key가 사용되지 않았습니다. 사실 사용되지 않은 이유는 테이블을 파티셔닝 할 때
테이블에 PK 혹은 Unique Key가 사용되는 경우 약간의 제약이 생기기 때문입니다.
공식문서에서는 파티셔닝에 키로 사용되는 모든 열은 테이블에 있는 모든 고유 키의 일부여야한다고 설명합니다.
즉 테이블의 모든 고유 키(PK, Unique)는 파티셔닝 키의 모든 열을 사용해야 합니다.
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;
먼저 위 SQL은 유효한 SQL 입니다. t1 테이블은 UNIQUE KEY (col1, col2, col3)을 사용합니다.
그리고 col3을 기준으로 해시 파티셔닝을 하려고 합니다. 고유 키는 파티셔닝 키의 모든 열을 사용해야 한다는 제약조건을 다시 생각해보면
UNIQUE KEY (col1, col2, col3)은 col3을 사용하고 있으므로 만족합니다. 따라서 파티셔닝이 수행됩니다.
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;
t2 테이블도 마찬가지로 UNIQUE KEY (col1, col3)은 해시키로 사용되는 col1, col3을 모두 포함하고 있으므로 사용가능 합니다.
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;
위 t1 테이블은 파티셔닝 되지 못합니다. UNIQUE KEY (col1, col2)가 해시 파티셔닝에 사용되는 col3을 포함하고 있지 않기 때문입니다.
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;
위 t2 테이블도 파티셔닝 되지 못합니다. t2 테이블은 2개의 UNIQUE KEY가 존재합니다.
첫번째 col1 UNIQUE KEY는 해시 파티셔닝에 키로 사용되는 col1을 포함하지만 col3을 포함하고 있지는 않습니다.
두번째 col3 UNIQUE KEY는 해시 파티셔닝에 키로 사용되는 col3을 포함하지만 col1을 포함하고 있지는 않습니다.
파티셔닝의 제약 조건 "고유 키는 파티셔닝 키로 사용되는 모든 컬럼을 포함해야한다는"을 지키지 못하고 있기에 파티셔닝 되지 못합니다.
대부분의 테이블은 PK가 사용됩니다. 따라서 파티셔닝을 할 때 PK를 기준 컬럼으로 사용하지 않는다면 단일 PK 사용이 불가능하기 때문에
기준 컬럼을 잘 설정해야합니다.
Hot Spot
파티셔닝 혹은 샤딩을 찾아보면 듣게 되는 hotspot 문제가 있습니다. hotspot 문제는 특정 파티션에 데이터가 몰리는 현상입니다.
예를 들어 나라별로 user 테이블을 파티셔닝 했으나 대한민국의 유저가 1000만명이고 나머지 유저는 100명 이하면 대한민국의 유저를
보관하는 파티션에 부하가 심하게 발생합니다.
혹은 년도별로 파티셔닝을 했는데 2025년이 되어서 갑자기 유저 수가 증가하면 2025년 파티션에 데이터가 몰리게 됩니다.
따라서 해시, 키 파티셔닝을 통해서 최대한 동일한 값이라도, 유사한 값이라도 최대한 골고루 여러 파티션에 나뉘어 저장되도록 하면 hotspot 문제를 방지할 수 있습니다.
그런데 파티셔닝을 어떤 목적으로 쓰냐에 따라 관점이 달라질 수 도 있다고 생각이 듭니다.
파티셔닝을 데이터 분산을 위해 사용하는가? 혹은 의미있는 집합으로 묶기 위해 사용하는가?
데이터 분산을 위해 사용한다면 해시, 키 파티셔닝을 통해 최대한 골고루 분포시키면 됩니다. 그러나 이 경우 조회 시 여러 파티션을
거쳐야하기 읽기 성능에 오버헤드가 발생할 수 있지 않을까라는 의문이 들긴 합니다.
예를 들어 게시글 목록 조회를 위해 페이징을 통해 1,2,3,4,5을 조회한다고 할 때 각 게시글이 서로 다른 파티션에 존재한다면
1,2,3,4,5를 조회하기 위해 5개의 파티션에 접근해야 합니다.
과연 이런 오버헤드를 감당하면서도 사용해야하는지에 대해서는 좀 더 생각 해봐야겠습니다.
hotspot 문제가 발생하지 않기 위해 최대한 데이터를 골고루 분포시키면 각 파티션의 요소들은 관계성이 적어집니다.
따라서 관계성이 적어질수록 hotspot 위험은 적어지지만 관계성을 이용해 데이터를 조회하는 경우에는 여러 파티션에 접근해야합니다.
반면에 관계성이 높을수록 hotspot 위험은 높아지지만 관계성을 이용해 데이터를 조회하는 경우 효율적으로 파티션에 접근합니다.
이 트레이드 오프에 대해서는 고려해봐야 할 듯 합니다.
'database' 카테고리의 다른 글
| 알고 쓰자 - for update (feat. Gap Lock) (0) | 2025.05.03 |
|---|---|
| Mysql, MariaDB 그리고 MVCC의 쓰기 충돌 감지 (0) | 2025.04.29 |