[ 개요 ]
MySQL Partitioning은 큰 테이블을 논리적으로 작은 단위(파티션)로 나누어 관리하는 기술입니다.
테이블 데이터를 파티션 단위로 나누어 저장하여 데이터 처리 성능을 개선하고, 특정 쿼리를 최적화할 수 있습니다.
[ 장단점 ]
장점
성능 향상 :
데이터가 파티션 단위로 분리되어 저장되므로 특정 파티션에만 접근하는 쿼리의 성능이 향상됩니다.
특히, 읽기 작업(SELECT)에 유리하며, 대규모 데이터에서 효율적입니다.
관리 용이성 :
특정 파티션만 드롭하거나 백업 및 복원할 수 있습니다.
병렬 처리 :
MySQL은 파티션을 병렬로 처리하므로, 대규모 데이터를 효과적으로 처리할 수 있습니다.
단점
제약 사항 :
외래 키(Foreign Key) 사용 불가합니다.
파티션 키가 반드시 Primary Key 또는 Unique Key에 포함되어야 합니다.
( Primary Key 또는 Unique Key가 없으면, 단순히 파티션 키를 기준으로 파티셔닝이 가능 )
추가 복잡성 :
설계 시 데이터 분포와 사용 패턴을 신중히 분석해야 하며, 부적절한 설계는 성능 저하를 초래할 수 있습니다.
쓰기 성능 저하 가능성 :
특정 파티션에 데이터가 몰리는 경우(스큐 데이터), 쓰기 작업의 성능이 저하될 수 있습니다.
파티션별로 병렬 처리가 되지만, 특정 파티션에 몰리면 병렬성이 저하됩니다.
또한 특정 파티션을 과도하게 쓰기 작업이 일어나면 락 경합(Lock Contention)이 일어납니다.
[ 사용 케이스 ]
- 대규모 테이블에서 데이터가 시간이 지남에 따라 축적되는 경우: 로그 데이터
- 시간 기반 데이터 관리: 특정 날짜 또는 기간별로 대규모 데이터를 자주 삭제하거나 관리해야 하는 경우
- 스키마 구조가 안정적이고, 데이터 분포가 균일한 경우: 데이터를 적절히 분할하면 효율적
서비스에서 업데이트 트랜잭선 처리(OLTP)를 하고,
수백 GB ~ 수 TB까지의 중소 규모의 데이터이면 mysql partitioning을 적용하는게 효율적이고,
RBMS를 대체할 수 있거나 데이터 사이즈가 커지면 Hive, Spark(OLAP)를 사용하는 것이 좋습니다.
[ MySQL Partitioning 유형 ]
RANGE Partitioning
특정 열(column)의 값이 범위에 따라 데이터를 분할 합니다.
날짜, 숫자와 같이 순차적인 값이 있는 데이터를 활용합니다.
날짜 기반 파티션 분할 예제
CREATE TABLE logs (
id INT NOT NULL,
log_date DATE NOT NULL,
message TEXT,
PRIMARY KEY (id, log_date)
)
PARTITION BY RANGE (YEAR(log_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_future VALUES LESS THAN MAXVALUE
);log_date = '2023-06-15' → p2023에 저장.
log_date = '2024-03-10' → p2024에 저장.
log_date = '2025-01-01' → p_future에 저장.
LIST Partitioning
특정 열(column)의 값이 명신된 값 목록에 따라 데이터를 분할합니다.
지역, 카테고리, 상태코드 등 특정 값이 반복적으로 나타내는 경우 사용합니다.
지역 기반 파티션 예제
CREATE TABLE sales (
id INT NOT NULL,
region_code CHAR(2) NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY (id, region_code)
)
PARTITION BY LIST (region_code) (
PARTITION p_asia VALUES IN ('KR', 'JP', 'CN'),
PARTITION p_europe VALUES IN ('FR', 'DE', 'IT'),
PARTITION p_others VALUES IN ('US', 'AU')
);region_code = 'KR' → p_asia에 저장.
region_code = 'FR' → p_europe에 저장.
region_code = 'US' → p_others에 저장.
HASH Partitioning
해시 함수(Hash Funciton)을 사용하여 데이터를 균등하게 분할합니다.
데이터가 불균형하게 분포되었을 때나, 특정 범위가 없이 임의로 파티셔닝해야 할 때 사용합니다.
ID값으로 파티션 예제
CREATE TABLE logs (
id INT NOT NULL,
log_date DATE NOT NULL,
message TEXT,
PRIMARY KEY (id)
)
PARTITION BY HASH (id) PARTITIONS 4;해시 함수로 id 값을 계산하여 4개의 파티션 중 하나에 저장.
예: id % 4 = 0 → 첫 번째 파티션에 저장.
[ 실행 속도 비교 ]
파티션을 적용할 때와 적용하지 않을 때의 쿼리 실행 속도를 비교해 보겠습니다.
단순히 SELECT - WHERE절의 경우 데이터 사이즈에 따라서 실행속도는 비슷할 수 있습니다.
아래는 비교시 사용한 쿼리입니다.
테이블은 no, member_id 라는 두개의 컬럼이 있고 no 기준으로 파티션을 지정하였습니다.
쿼리 내용은 동일한 테이블의 각각 다른 no에서, member_id의 교집합을 추출합니다.
파티션을 적용하지 않은 테이블에 실행한 쿼리
SELECT a.member_id
FROM user_member a
WHERE a.no = 2
AND EXISTS (
SELECT 1
FROM user_member b
WHERE b.no = 1
AND b.member_id = a.member_id
);
결과 : 약 5분
파티션을 적용한 테이블에 실행한 쿼리
SELECT a.member_id
FROM user_member PARTITION (p2) a
WHERE EXISTS (
SELECT 1
FROM user_member PARTITION (p1) b
WHERE b.member_id = a.member_id
)
결과 : 약 1분
Explain으로 실행 계획을 확인
