MySQL Partition, 제대로 이해하기 (1)

MySQL의 Parition 개념과 특징을 알아보는 것이 본 포스팅의 목표입니다.

 

 

안녕하세요. 이번 포스팅은 Parition에 대해 다루고자 합니다.

Parition은 대용량 데이터를 처리해야한다면 반드시 알아둘 필요가 있습니다.

이번 포스팅에서는 전반적인 이해와 원리를 다루며,

이어지는 포스팅 MySQL Partition, 제대로 사용하기 (2) 에서 실제 사용법에 대해 다룹니다.

 

해당 포스팅의 모든 도식은 직접 그린 것으로 출처를 명시하셔야 합니다.

 

 

Partition?

Partition이란 논리적으로 하나의 테이블이지만 실제로는 여러 개의 테이블로 분리해서 관리하는 기능입니다.

마치 동일한 형식의 한 개의 테이블을 사용하고 있는 것 같지만,

실제적으로는 여러 개의 물리 파일로 데이터들을 분할하여 보관하는 방식입니다. 

 

Partition은 주로 대용량의 테이블을 물리적으로 여러 개의 소규모 테이블로 분산하는 목적으로 사용합니다.

가령 유저 분석을 위한 유저의 활동 기록을 저장하는 DB를 생각해보세요.

하루 활동 유저 수가 천 명일 때, 한 달 만해도 많은 수의 데이터가 쌓이겠죠.

이렇게 쌓인 데이터에서 특정 월을 기준으로 모든 데이터를 검색하거나, 

혹은 특정 활동을 한 유저를 통계를 위한 검색을 하면 DB 부하를 크게 주게 됩니다.

 

이 때 모든 데이터에서 검색하는 것보다 특정 기준 값으로 분리된 파일들 중에서 찾는 게

성능상 유리할 수 있기 때문에 partition으로 나누는 것을 고려할 수 있습니다.

partition은 유리한 상황이 분명 존재하지만, 어떤 쿼리가 사용될지에 따라 오히려 성능이 나빠질 수도 있습니다.

그렇다면 언제 Parition 사용을 고려해볼만할까요?

 

 

Case 1. 무거운 인덱스

: 인덱스 많이 걸려 무거워진 경우

 

UPDATE나 DELETE 쿼리를 처리하기 위해서는 인덱스를 통한 검색이 필수적입니다.

하지만 인덱스가 커질수록 SELECT, INSERT, UPDATE, DELETE 작업이 함께 느려집니다.

 

 

 

 

 

위의 그림을 보면 파티션하지 않고 하나의 큰 테이블로 사용하여 인덱스도 커지면,

그만큼 물리적인 메모리 공간도 많이 필요해진다는 사실을 알 수 있습니다.

 

 

Case 2. Working Set

: Working Set 기준으로 나눌 수 있는 경우

 

데이터의 특성에는 전체 데이터 셋에서 자주 찾는 데이터 그룹이 존재하게 되는데,

이렇게 모든 데이터 중 활발히 사용되는 데이터를 Working Set이라고 합니다.

 

테이블의 데이터는 실질적인 물리 메모리보다 큰 것이 일반적이겠지만 

인덱스의 Working Set이 실질적인 물리 메모리보다 크다면 쿼리 처리가 상당히 느려질 것입니다.

 

따라서 테이블의 데이터를 활발하게 사용 되는 Working Set과 그 외의 부분으로 나눠서 파티션할 수 있다면 상당히 효과적으로 성능을 개선할 수 있겠죠?

 

 

PARTITION BY

연월 별 데이터를 나누고 싶다면 아래와 같이 정의할 수 있습니다.

 

mysql> CREATE TABLE users (
    user_id INT NOT NULL, 
    reg_at DATETIME NOT NULL,
    ...
    PRIMARY KEY(user_id, reg_at)
) PARTITION BY RANGE (TO_DAYS(reg_at)) (
	PARTITION P_202206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
	PARTITION P_202207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
	PARTITION P_202208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
	PARTITION P_maxvalue VALUES LESS THAN MAXVALUE
);

 

유저의 테이블의 reg_at 컬럼은 파티션 키가 되어 레코드가 어떤 파티션에 저장될지를 결정합니다.

Parition의 키의 역할이 되는 중요한 역할을 하게 되죠.

각 데이터를 고루 분할하고, 명확한 기준으로 나누는 값을 사용해야만 합니다.

 

참고로, 파티션은 동일한 테이블의 형식으로 나눌 뿐, 파티션 별 다른 형태를 갖지 않습니다.

가령 각각 다른 인덱스를 생성하는 등의 형태는 지원하지 않습니다.

 

 

Functions

users 테이블은 reg_at 칼럼에서 TO_DAYS( ) 라는 MySQL 내장 함수를 이용해 날짜만 추출하고,

그 날짜를 이용해 테이블을 연도 범위별로 파티션합니다.

 

ABS(), CEILING(), EXTRACT(), FLOOR(), MOD(),
DATEDIFF(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), HOUR(), MICROSECOND(), MINUTE(),
MONTH(), QUARTER(), SECOND(), TIME_TO_SEC(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP(),
WEEKDAY(), YEAR(), YEARWEEK()

 

위와 같은 MySQL 내장 함수를 사용하여 파티션에 사용할 수 있습니다.

 

 

 

 

Search

파티션을 사용하면서 오히려 성능이 더 떨어질 수도 있다고 했는데요.

사용할 쿼리가 어떤 것일지를 고민해보고, 좋은 성능이 기대된다면 파티션을 사용할 수 있습니다.

 

데이터 검색을 먼저 구분해보자면, WHERE 절의 조건으로 아래와 같은 사항을 따져볼 수 있습니다.

 

✔️ 검색할 파티션을 찾을 수 있는지
✔️ 인덱스를 효율적으로 사용할 수 있는지 (Index Range Scan)

 

 

CASE 1 📌 파티션 선택 O + 인덱스 효율 O

이때는 파티션의 개수와 관계없이 검색을 위해 꼭 필요한 파티션의 Index Range Scan하는 경우를 의미합니다.

두 조건이 모두 가능할 때 쿼리가 가장 효율적으로 처리될 수 있습니다.

 

CASE 2 📌 파티션 선택 X + 인덱스 효율 O

이 경우 우선 테이블의 모든 파티션을 대상으로 검색해야 하지만, 각 파티션에 Index Range Scan을 사용할 수 있습니다.

최종적으로 테이블에 존재하는 모든 파티션의 개수만큼 Index Range Scan 검색 하게 됩니다.

이 작업은 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음, 결과를 병합해서 가져오는 것과 같습니다.

 

CASE 3 📌  파티션 선택 O + 인덱스 효율 X

검색하려는 레코드가 저장된 파티션을 선별할 수 있기 때문에,

파티션 개수와 관계없이 검색을 위해 필요한 파티션만 읽으면 됩니다.

 

하지만 인덱스는 이용할 수 없어서 대상 파티션에 대해 Full Table Scan 하기 때문에,

각 파티션의 레코드 건수가 많다면 상당히 느리게 처리됩니다.

 

 

CASE 4 📌   파티션 선택 X + 인덱스 효율 X

테이블의 모든 파티션을 검색해야 하고 각 파티션에서도 Full Table Scan을 수행해야 합니다.

최악의 경우가 되겠죠.

 

 

 

CASE 3, 4와 같은 경우가 발생한다면 파티션을 지양하는 것이 좋습니다.

 

 

 

 

INSERT

Partition을 통해 데이터를 조회할 때는 파티션 키로 정한 데이터를 기준으로 파티션이 결정한 후,

파티션이 결정되면 나머지 과정은 파티션되지 않은 일반 테이블과 동일하게 처리됩니다.

 

 

 

 

 

데이터를 조회할 때 파티션 키 값을 통해 조회하면 좋은 성능을 기대할 수 있지만,

반대로 없다면 테이블의 모든 파티션에서 검색해야 합니다.

파티션을 사용하는 의미가 사라질 수 있으니,

최대한 파티션 키를 통해 데이터를 찾을 수 있게끔 조건을 걸어주는 것을 권장드립니다.

 

 

UPDATE

데이터를 변경할 때는 파티션 키를 변경하는지 아닌지로 구분해서 설명할 수 있습니다.

 

 

#1. 파티션 키 외의 데이터 수정

먼저, 파티션 키 외의 데이터가 수정될 때에는 파티션이 적용되지 않은 일반 테이블과 마찬가지로 칼럼 값만 변경하면 됩니다.

 

 

#2. 파티션 키 칼럼이 변경될 때

아래와 같이 기존의 레코드가 저장된 파티션에서 해당 레코드를 삭제합니다. 그리고 변경되는 파티션 키 칼럼의 표현식을 평가한 후,그 결과를 이용해 레코드를 이동시킬 새로운 파티션을 결정해서 레코드를 새로 저장합니다.

 

 

 

 

Index

모든 인덱스는 파티션 단위로 생성됩니다.

MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스로, 테이블 단위의 글로벌한 인덱스는 지원하지 않습니다.

 

 

 

 

파티션되지 않은 테이블에서는 인덱스를 순서대로 읽으면 그 칼럼으로 정렬된 결과를 바로 얻을 수 있지만,

파티션된 테이블은 인덱스가 분리되어 있기 때문에 다르게 동작합니다.

 

 

먼저, 검색하려는 대상 데이터를 각 파티션에서 가져옵니다.

각 파티션 별로 데이터를 가져왔다면 가져온 데이터들을 병합을 해야 할텐데, 이 데이터들을 어떻게 합쳐야 할까요?

 

일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라,

여러 파티션에 대해 인덱스 스캔을 수행할 때 각 파티션으로부터

조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐(Priority Queue)에 임시로 저장하여 가져옵니다.

 

각 파티션 에서 읽은 데이터가 이미 정렬되어 있기 파티션에 접근한 후 그 순서대로만 가져오며, 내부적으로 큐 처리를 합니다.

 

 

 

Partition pruning

: 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것 들은 실행 계획에서 배제

 

옵티마이저가 다수의 파티션 중 일부만 읽어도 된다고 판단되면 불필요한 파티션에는 전혀 접근하지 않습니다.

가령 202208 월 범위 내의 해당하는 데이터를 읽고자 한다면 그 이외의 파티션 (202207, 202209 ...)에는 전혀 접근하지 않습니다.

실제로 쿼리 플랜을 확인해보면 partition 에 해당하는 데이터로는 P_202208밖에 없음을 알 수 있습니다.

 

mysql> EXPLAIN SELECT * FROM users WHERE reg_at > '20220801' AND reg_at > '20220801';
+----+------------+------------+-------+---------+---------+------+--------------------------+
| id | table      | partitions | type  | key     | key_len | rows | Extra                    |
+----+------------+------------+-------+---------+---------+------+--------------------------+
|  1 | tb_article | P_202208      | index | PRIMARY | 9       |    1 | Using where; Using index |
+----+------------+------------+-------+---------+---------+------+--------------------------+

 

 

 

 

그럼 지금까지 MySQL Partition에 대해 알아보았습니다.

오타나 잘못된 내용을 댓글로 남겨주세요!

감사합니다 ☺️