MySQL 파티션을 왜 사용하는가?!

파티션이란?

데이터를 물리적으로 분할하여 쿼리 성능과 데이터 관리 및 유지보수 작업을 용이하게 하기 위해서이다.

데이터베이스 서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 효과를 가질 수 있다.

 

 

 

인덱스에 용이

테이블의 크기가 커질수록 인덱스의 크기도 같이 커지기 때문에 읽기/쓰기에 대한 IO에 같이 늘어난다.

이렇게 커짐으로써 인덱스에 처리되는 시간이 늘어나고 인덱스가 물리적인 메모리보다 커지는 경우도 발생한다.

 

파티션을 분할하면 이런 효과를 가져온다고 말할 수 있다.

 

파티션을 어떻게 나눌  수 있는가? 

우선 방법론에 대해 알아보고 실험을 해보자.

 

RANGE Partitioning (범위 분할)

RANGE 분할은 주어진 열의 값을 기준으로 연속적인 범위에 따라 테이블을 분할하는 방법이다.

주로 날짜 또는 숫자 범위를 기준으로 분할하는 경우에 사용된다.

 

1. 다음과 같이 테이블을 생성해보자

CREATE TABLE partition_range (
  id INT(10) NOT NULL,
  event_date DATE NOT NULL,
  event_title VARCHAR(50) NOT NULL
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(event_date)) (
  PARTITION p1 VALUES LESS THAN (2021),
  PARTITION p2 VALUES LESS THAN (2022),
  PARTITION p3 VALUES LESS THAN (2023),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

2. 데이터를 넣어보자. 2019 ~ 2024년까지 1개의 데이터씩 넣어보자.

INSERT INTO partition_range(id, event_date, event_title) VALUES(1, '2019-01-01', '2019년 새해');
INSERT INTO partition_range(id, event_date, event_title) VALUES(2, '2020-01-01', '2020년 새해');
INSERT INTO partition_range(id, event_date, event_title) VALUES(3, '2021-01-01', '2021년 새해');
INSERT INTO partition_range(id, event_date, event_title) VALUES(4, '2022-01-01', '2022년 새해');
INSERT INTO partition_range(id, event_date, event_title) VALUES(5, '2023-01-01', '2023년 새해');
INSERT INTO partition_range(id, event_date, event_title) VALUES(6, '2024-01-01', '2024년 새해');

3. INSERT로 밀어 넣은 데이터가 어느 파티션에 저장되어 있는지 확인해보자. 결과값은 아래와 같다.

 

2019, 2020년 데이터는 PARTITION p1 VALUES LESS THAN (2021) 로 설정했으므로 2021년 이전이므로 p1에 저장된다.

2021년 데이터는 PARTITION p2 VALUES LESS THAN (2022) 로 설정했으므로 2022년 이전이므로 p2에 저장된다.

2022년 데이터는 PARTITION p3 VALUES LESS THAN (2023) 로 설정했으므로 2023년 이전이므로 p3에 저장된다.

2023,  2024년 데이터는 PARTITION p4 VALUES LESS THAN MAXVALUE 로 설정했으므로 2023년 이후의 모든 데이터는 p4에 저장된다.

 

event_date	partition (파티션)
2019-01-01	p1
2020-01-01	p1
2021-01-01	p2
2022-01-01	p3
2023-01-01	p4
2024-01-01	p4

 

LIST Partitioning (목록 분할)

LIST 분할은 주어진 열의 값을 기준으로 목록에 따라 테이블을 분할하는 방법이다.

각 목록에 해당하는 값의 데이터를 별도의 파티션에 저장한다.

예를 들어, 국가 코드에 따라 테이블을 분할하여 각 국가의 데이터를 별도의 파티션에 저장할 수 있다.

 

1. 위의 예시처럼 테이블을 만들고

CREATE TABLE partition_list (
  id INT(10) NOT NULL,
  category_id INT(10) NOT NULL
) ENGINE=INNODB
PARTITION BY LIST (category_id) (
  PARTITION c1 VALUES IN (1),
  PARTITION c2 VALUES IN (2),
  PARTITION c3 VALUES IN (3),
  PARTITION c_others VALUES IN (4, NULL)
);

2. 샘플 데이터를 밀어 넣어보자.

INSERT INTO partition_list(id, category_id) VALUES(1, 1);
INSERT INTO partition_list(id, category_id) VALUES(2, 2);
INSERT INTO partition_list(id, category_id) VALUES(3, 3);
INSERT INTO partition_list(id, category_id) VALUES(4, 4);
INSERT INTO partition_list(id, category_id) VALUES(5, 5);

아래와 같은 오류가 발생했다. 5라고 지정한 파티션이 없기때문에 오류를 발생시킨다.

<e>쿼리: INSERT INTO partition_list(id, category_id) VALUES(5, 5)

오류 코드: 1526
Table has no partition for value 5

3. 아래와 같이 어느 파티션에 들어갔는지 확인해 볼 수 있다.

EXPLAIN PARTITIONS SELECT * FROM partition_list WHERE category_id = 1;
EXPLAIN PARTITIONS SELECT * FROM partition_list WHERE category_id = 2;
EXPLAIN PARTITIONS SELECT * FROM partition_list WHERE category_id = 3;
EXPLAIN PARTITIONS SELECT * FROM partition_list WHERE category_id = 4;

category_id	partition (파티션)
1	c1
2	c2
3	c3
4	c_others

HASH Partitioning (해시 분할)

HASH 분할은 주어진 열의 해시 값을 기준으로 테이블을 분할하는 방법이다..

해시 함수를 사용하여 데이터를 고루 분산시키며, 임의의 해시 값에 해당하는 파티션에 데이터를 저장한다.

이 방법은 데이터를 균등하게 분산시키는 데 적합합니다.

CREATE TABLE partition_hash (
  id INT(10) NOT NULL,
  user_id INT(10) NOT NULL
) ENGINE=INNODB
PARTITION BY HASH(user_id)
PARTITIONS 4;

INSERT INTO partition_hash (id, user_id) VALUES(1, 1);
INSERT INTO partition_hash (id, user_id) VALUES(2, 2);
INSERT INTO partition_hash (id, user_id) VALUES(3, 3);
INSERT INTO partition_hash (id, user_id) VALUES(4, 4);
INSERT INTO partition_hash (id, user_id) VALUES(5, 5);

EXPLAIN PARTITIONS SELECT * FROM partition_hash WHERE user_id = 1;
EXPLAIN PARTITIONS SELECT * FROM partition_hash WHERE user_id = 2;
EXPLAIN PARTITIONS SELECT * FROM partition_hash WHERE user_id = 3;
EXPLAIN PARTITIONS SELECT * FROM partition_hash WHERE user_id = 4;
EXPLAIN PARTITIONS SELECT * FROM partition_hash WHERE user_id = 5;

user_id	partition (파티션)
1	p1
2	p2
3	p3
4	p0
5	p1

KEY Partitioning (키 분할)

KEY 분할은 주어진 열의 값에 대한 해시 또는 키를 기준으로 테이블을 분할하는 방법이다. 데이터를 특정 키 값에 대한 해시 값이나 목록에 따라 분할한다. 

이 방법은 주로 고유한 키 값을 기준으로 데이터를 분할하는 데 사용된다.

CREATE TABLE partition_key (
  id INT,
  user_id INT,
  UNIQUE KEY(user_id)
) ENGINE=INNODB
PARTITION BY KEY(user_id)
PARTITIONS 3;

INSERT INTO partition_key (id, user_id) VALUES(1, 1);
INSERT INTO partition_key (id, user_id) VALUES(2, 2);
INSERT INTO partition_key (id, user_id) VALUES(3, 3);
INSERT INTO partition_key (id, user_id) VALUES(4, 4);
INSERT INTO partition_key (id, user_id) VALUES(5, 5);

EXPLAIN PARTITIONS SELECT * FROM partition_key WHERE user_id = 1;
EXPLAIN PARTITIONS SELECT * FROM partition_key WHERE user_id = 2;
EXPLAIN PARTITIONS SELECT * FROM partition_key WHERE user_id = 3;
EXPLAIN PARTITIONS SELECT * FROM partition_key WHERE user_id = 4;
EXPLAIN PARTITIONS SELECT * FROM partition_key WHERE user_id = 5;

user_id	partition (파티션)
1	p1
2	p2
3	p2
4	p0
5	p0
6	p1