RDBMS - Mysql Partitioning 파티셔닝

[ 개요 ]

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으로 실행 계획을 확인