[MySQL] 파티셔닝 전략과 파티션 프루닝(RANGE/LIST/HASH/KEY)
개요
수억 건의 데이터를 가진 테이블에서는 인덱스를 하나 추가했을 뿐인데 테이블이 여섯 시간 동안 잠기고, 특정 기간의 데이터만 조회하려 해도 옵티마이저가 테이블 전체를 훑는다. 이런 상황에서 데이터를 미리 구획별로 나누어 관리하는 방법이 파티셔닝(Partitioning)이다.
파티셔닝, 하나의 논리 테이블과 여러 물리 파일
파티셔닝은 하나의 테이블을 여러 개의 파티션(물리적)으로 나누어 특정 기준에 따라 데이터를 적재하는 방법이다. 애플리케이션 코드에서는 여전히 하나의 테이블로 보이지만, 물리적으로는 N개의 파일로 나뉘어 저장된다.
첫째, 검색 범위가 줄어든다. 파티션 키가 WHERE 절에 포함되면 옵티마이저가 해당 파티션만 스캔하는데, 이를 파티션 프루닝이라 부른다. 둘째, 운영 단위가 파티션으로 분리된다. 오래된 데이터 정리가 ALTER TABLE … DROP PARTITION 한 줄로 끝나고, 이 명령은 DELETE로 같은 양을 지우는 것과 비교할 수 없을 정도로 빠르다.
셋째, 인덱스가 파티션 단위로 나뉘어 각각의 크기가 작아진다. MySQL은 글로벌 인덱스를 지원하지 않고 모든 인덱스가 파티션 단위의 로컬 인덱스이므로, 인덱스 하나가 작아질수록 버퍼 풀(Buffer Pool) 안에 올라가는 빈도가 높아진다.
- 모든 UNIQUE 키는 파티션 키를 포함해야 한다. 기본 키(PK, Primary Key)도 마찬가지라, 단독
id AUTO_INCREMENT PRIMARY KEY로는 파티셔닝을 적용할 수 없고 보통PRIMARY KEY (id, partition_key_col)형태의 복합 키가 된다. - 파티션된 테이블은 외래키(FK, Foreign Key)를 가질 수도, FK의 참조 대상이 될 수도 없다.
- 파티션 키는 결정적(deterministic)이어야 한다. 같은 입력이면 항상 같은 파티션으로 가야 하므로
RAND(),NOW()는 파티션 키에 쓸 수 없다.
파티셔닝 전략별 비교: RANGE, LIST, HASH, KEY
MySQL이 지원하는 파티셔닝 방식은 네 가지다.
RANGE, 순서 있는 값을 구간으로 나눈다
RANGE 파티셔닝은 날짜나 숫자처럼 순서가 있는 값을 구간으로 나눠 파티션에 배정한다.
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
amount INT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p_future VALUES LESS THAN MAXVALUE
);각 파티션은 위에서부터 처음 매치되는 곳으로 데이터가 들어간다. p_future 같은 default 파티션을 관례적으로 둔다. 새 연도가 시작되기 전에 다음 해 파티션을 미리 추가하지 않으면, 그 해의 데이터가 전부 p_future로 몰리는 핫 파티션 문제가 생긴다. RANGE는 시계열 데이터와 주기적인 데이터 만료에 가장 잘 맞는다.
LIST, 고정된 카테고리로 나눈다
LIST 파티셔닝은 열거형처럼 고정된 카테고리 값을 기준으로 데이터를 분배한다.
CREATE TABLE order_logs (
id BIGINT AUTO_INCREMENT,
region VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
PARTITION p_seoul VALUES IN ('SEOUL'),
PARTITION p_busan VALUES IN ('BUSAN'),
PARTITION p_etc VALUES IN ('DAEGU', 'INCHEON', 'GWANGJU')
);카테고리별로 데이터를 분리하고 지역마다 다른 백업 정책을 적용하는 것처럼, 운영 방식 자체를 격리하고 싶을 때 적합하다.
HASH와 KEY, 균등하게 나눈다
HASH 파티셔닝은 해시 함수로 N개 파티션 중 하나에 데이터를 균등하게 배정한다.
CREATE TABLE user_events (
id BIGINT AUTO_INCREMENT,
user_id BIGINT NOT NULL,
payload JSON,
PRIMARY KEY (id, user_id)
)
PARTITION BY HASH(user_id) PARTITIONS 8;내부적으로 MOD(user_id, 8)로 파티션 번호가 정해지므로 분포가 거의 균등하고 핫 파티션이 잘 생기지 않는다. KEY 파티셔닝은 MySQL 내장 해시 함수를 쓴다는 점만 다르고 동작 원리는 같으며, 정수형이 아닌 문자열 PK나 UNIQUE 컬럼도 균등하게 분배할 수 있다. 두 방식 모두 특정 그룹만 골라 DROP하는 것 같은 파티션 단위 운영은 어렵다는 공통 단점이 있다.
| 전략 | 파티션 키 | 적합한 경우 | 분포 균등성 |
|---|---|---|---|
| RANGE | 순서 있는 값(날짜, 숫자) | 시계열, 주기적 만료 | 트래픽에 따라 불균등 가능 |
| LIST | 고정 카테고리 | 지역이나 타입별 운영 분리 | 카테고리별 데이터 양에 따라 불균등 가능 |
| HASH | 임의 정수형 | 사용자별 균등 분배 | 매우 균등 |
| KEY | PK 또는 UNIQUE(모든 타입) | 문자열 PK 균등 분배 | 매우 균등 |
복합 파티셔닝: RANGE 파티션 안에서 HASH로 한 번 더 쪼개기
RANGE로 나눈 파티션 하나가 여전히 너무 크면, 그 안에서 HASH나 KEY로 한 번 더 쪼개는 서브파티셔닝(Subpartitioning)을 쓸 수 있다. RANGE와 LIST만 상위 파티션이 될 수 있고, 그 아래 서브파티션은 HASH나 KEY로만 나눌 수 있다.
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at, user_id)
)
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027)
);이 테이블은 물리적으로 파티션 2개 곱하기 서브파티션 4개, 총 8개의 파일로 나뉜다. 연 단위 RANGE 파티션 하나에 특정 사용자의 데이터가 몰려 다시 핫 파티션이 되는 상황에서, 그 파티션 내부를 사용자 단위로 한 번 더 고르게 흩어 놓는 용도로 쓴다.
서브파티셔닝은 파일 수를 곱으로 늘리는 대신 얻는 것도 명확해야 한다. DROP PARTITION은 여전히 상위 RANGE 파티션 단위로만 동작하고, 서브파티션 하나만 선택적으로 비울 수는 없다. 상위 RANGE 파티션 하나의 크기가 단일 인덱스로 감당하기 어려울 때만 서브파티셔닝을 고려하고, 그렇지 않다면 RANGE 하나로 충분하다.
파티션 프루닝과 EXPLAIN
파티셔닝의 핵심은 옵티마이저가 WHERE 조건에서 파티션 키를 보고 필요한 파티션만 스캔하는 것이다.
-- 파티션 키(created_at)가 조건에 있으면 프루닝된다
EXPLAIN SELECT * FROM orders WHERE created_at = '2026-03-15';
-- 파티션 키 없이 PK(id)로만 조회하면 프루닝되지 않는다
EXPLAIN SELECT * FROM orders WHERE id = 12345;첫 번째 쿼리의 결과에서는 partitions 컬럼에 p2026 하나만 찍힌다. 두 번째 쿼리는 파티션 키를 조건에 두지 않았으므로 partitions 컬럼에 p2024, p2025, p2026, p_future가 전부 나열되고, 옵티마이저는 네 파티션의 로컬 인덱스를 각각 조회한 뒤 결과를 병합한다. 조회할 인덱스가 넷으로 늘고 병합 비용까지 붙으므로, 파티셔닝하지 않은 단일 인덱스보다 느려진다.
파티션 키를 조건에 넣지 않으면 프루닝이 걸리지 않는다. 파티션 키가 주요 쿼리 패턴의 90퍼센트 이상에 포함되지 않으면, 파티셔닝은 검색 범위를 줄이지 못한 채 인덱스 조회 횟수만 파티션 수만큼 늘린다. JOIN에서도 마찬가지라, 조인 조건이나 WHERE 절 양쪽에 파티션 키를 명시해야 프루닝이 걸린다.
-- 프루닝 안 됨: orders 의 파티션 키가 조건에 없어 전체 파티션을 스캔한다
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.id = 100;
-- 프루닝됨: orders 의 파티션 키를 WHERE 절에 명시했다
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01' AND o.created_at < '2027-01-01';k6 부하 테스트로 검증한 파티셔닝 효과
상품 1,000만 건을 놓고 쿼리 형태별로 파티셔닝 효과를 측정했다.
결과 요약
| 쿼리 형태 | 파티셔닝 효과 |
|---|---|
범위 조회 + LIMIT 100 | 7.7배 |
| 한 해 전체 집계 | 7.1배 |
WHERE id = ? 점 조회 | 25.5% 느려짐 |
| JOIN, 댓글에 파티션 키 명시 | 6.5배 |
| 날짜 인덱스를 대신 걸면 | 범위 조회에서 6,215배 — 파티셔닝이 무의미해진다 |
| 데이터가 한 파티션에 90% 쏠린 경우 | 집계 이득 소멸 |
측정 대상
같은 1,000만 행을 담은 세 벌을 비교한다. 셋 다 날짜 인덱스가 없고, 앞 행과 딱 하나씩만 다르다.
| 테이블 | PK | 파티셔닝 | 앞 행과의 차이 |
|---|---|---|---|
| 비파티션 | (id) | 없음 | 기준 |
| 비파티션 복합 PK | (id, created_date) | 없음 | PK 폭 |
| 파티션 | (id, created_date) | 연도별 RANGE 7개 | 파티셔닝 |
데이터는 2020년부터 2025년까지 걸쳐서 생성한다.
테스트 환경
- 데이터: 상품 1,000만건, 댓글 2,000만건
- DB: MySQL 8.0.46
- 테스트 도구: k6 v1.4.0
- 부하: 50 VU(Virtual User) 동시 사용자, think time 없음
- 테스트 시간: 각 70초 (20초 ramp-up + 40초 steady + 10초 ramp-down)
- 프로토콜: 케이스마다 MySQL 재기동 → 40초 워밍업 → 3회 반복 중앙값
쿼리 1: 범위 조회
SELECT id, name, created_date
FROM product
WHERE created_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY created_date, id
LIMIT 100;| 테이블 | TPS (최소~최대) | 역순 TPS | 평균 레이턴시 | 기준 대비 |
|---|---|---|---|---|
| 비파티션 | 0.8 (0.8~0.9) | 0.9 | 32.4초 | 기준 |
| 비파티션 복합 PK | 0.9 (0.9~1.0) | 0.9 | 30.7초 | 1.1배 |
| 파티션 | 6.3 (5.6~6.6) | 6.4 | 6.1초 | 7.7배 |
쿼리 2: 한 해 전체 집계
SELECT COUNT(*), SUM(price), AVG(stock_quantity)
FROM product
WHERE created_date BETWEEN '2024-01-01' AND '2024-12-31';| 테이블 | TPS (최소~최대) | 역순 TPS | 평균 레이턴시 | 스캔 행 수 |
|---|---|---|---|---|
| 비파티션 | 1.0 (1.0~1.0) | 1.0 | 28.0초 | 993만 |
| 비파티션 복합 PK | 0.9 (0.9~1.0) | 1.0 | 28.9초 | 993만 |
| 파티션 | 7.1 (7.1~7.1) | 7.0 | 5.7초 | 166만 |
날짜 인덱스를 대신 걸면
여기까지가 파티셔닝을 넣으면 얼마나 빨라지나를 살펴 본 것이고, 날짜 컬럼에 인덱스를 걸고 테스트한 결과는 다음과 같다.
| 무엇을 추가하나 | 범위 조회 | 한 해 집계 |
|---|---|---|
PK 를 (id, created_date) 로 확장 | 없음 | 없음 |
| 파티셔닝 | 7.7배 | 7.1배 |
| 날짜 인덱스 | 6,215배 | 없음 |
범위 조회에서 인덱스는 6,215 TPS(6ms)를 낸다. 파티셔닝의 7.7배와 비교하면 1,000배 차이다. 인덱스는 B트리 하강으로 시작 위치를 찾아 100행만 읽고 끝내기 때문이고, 파티셔닝은 읽을 양을 1/6로 줄이고, 전체를 스캔했기 때문에 큰 차이를 보인다. 만약 파티셔닝 + 인덱스로 대상을 조회한다면, 큰 효과를 얻을 수 있다.
쿼리 3: 파티션 키 없는 점 조회
SELECT id, name, created_date
FROM product
WHERE id = ?; -- 1 부터 1,000만 사이 난수| 테이블 | TPS | 평균 레이턴시 | 탐색 파티션 |
|---|---|---|---|
| 비파티션 PK(id) | 6,706 | 5.7ms | 해당 없음 |
| 비파티션 복합 PK | 5,703 | 6.7ms | 해당 없음 |
| 파티션 | 4,999 | 7.7ms | 7개 전부 |
PK가 (id, created_date)라 id만으로는 파티션을 선택할 수 없기 때문에 레이턴시 상에서 큰 차이를 보이지 않는다.
쿼리 5: JOIN 에서 댓글 프루닝
SELECT p.id, p.name, c.id AS comment_id, c.rating
FROM product_partitioned p
JOIN comment c ON c.product_id = p.id
WHERE p.created_date BETWEEN '2024-01-01' AND '2024-12-31'
AND p.id BETWEEN ? AND ? -- 2024년 id 구간에서 고른 1,000개 창
-- AND c.created_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY p.id, c.id
LIMIT 100;| 댓글 날짜 조건 | TPS | 평균 레이턴시 | 탐색 파티션 |
|---|---|---|---|
| 없음 | 48.5 | 814ms | 7개 전부 |
| 있음 | 314.5 | 125ms | p2024 1개 |
조인 순서도 접근 방식도 같고 반환하는 100행도 완전히 동일하다. 상품 1,000건마다 댓글 인덱스를 일곱 번씩 검색하느냐 한 번만 검색하느냐 차이가 6.5배이다.
정리하면
읽기 성능만 놓고 보면 파티셔닝이 줄여 주는 것은 프루닝이 걸러 내는 데이터 양뿐이다. 여섯 해로 나뉜 테이블에서 한 해를 조회하면 7배이다. 파티셔닝을 도입하는 이유는 1 / N의 효과, 데이터를 기간 혹은 조건 별로 나누어 drop 등으로 데이터를 관리하기 위함도 있다.
실험 코드와 k6 원본 결과, 각 쿼리의 EXPLAIN은 PR #160, 파티셔닝 벤치마크 재검증과 PR #161, 날짜 인덱스 유무 대조 실험에 있고, 검증 문서 mysql-partition.md와 scripts/rebench에서 재현 절차를 확인할 수 있다.
파티션을 운영하는 DDL: 추가, 제거, 재구성
파티션은 데이터가 쌓인 뒤에도 추가하거나 제거할 수 있고, 다른 테이블과 통째로 맞바꿀 수도 있다.
파티션 추가와 제거
-- 다음 해 파티션을 미리 추가
ALTER TABLE orders ADD PARTITION (
PARTITION p2027 VALUES LESS THAN (2028)
);
-- 오래된 파티션 제거
ALTER TABLE orders DROP PARTITION p2024;RANGE 파티션은 끝에만 추가할 수 있고, p_future처럼 MAXVALUE 파티션이 이미 있으면 곧바로 추가할 수 없어 REORGANIZE가 필요하다. DROP PARTITION은 데이터 파일을 통째로 제거하는 것이라 행 수와 무관하게 일정한 시간이 걸리는데, 1억 건짜리 파티션도 몇 초면 끝난다. 이 속도가 RANGE 파티셔닝의 가장 큰 장점이다.
REORGANIZE로 파티션 경계를 다시 그리기
기존 파티션의 경계 자체를 바꾸는 REORGANIZE는 대상 파티션의 모든 데이터를 재배치하므로 비용이 크다.
-- MAXVALUE catch-all 파티션이 이미 있으면 REORGANIZE로 새 파티션을 끼워 넣어야 한다
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
PARTITION p2027 VALUES LESS THAN (2028),
PARTITION p_future VALUES LESS THAN MAXVALUE
);REORGANIZE 대상은 재구성되는 파티션에 속한 데이터뿐이라 catch-all 파티션에 데이터가 아직 없다면 이 작업은 순간적으로 끝난다. 연 단위 파티션을 월 단위로 쪼개는 것처럼 이미 데이터가 가득 찬 파티션의 경계를 바꾸는 작업은 모든 행을 재배치해야 하므로 운영 트래픽이 흐르는 상황에서는 실행하기 어렵다. 무중단으로 구조를 바꾸려면 새 테이블을 만들어 데이터를 옮기는 방식이 필요하다.
Partition Exchange, 메타데이터만 바꿔 순식간에 교체한다
Partition Exchange는 비파티션 테이블과 특정 파티션을 통째로 맞바꾸는 기능이다.
-- 1. 임시 테이블 생성 (동일 구조, 파티셔닝 없음)
CREATE TABLE orders_2024_archive LIKE orders;
ALTER TABLE orders_2024_archive REMOVE PARTITIONING;
-- 2. 파티션 교체 (메타데이터만 바뀌므로 즉시 끝난다)
ALTER TABLE orders EXCHANGE PARTITION p2024 WITH TABLE orders_2024_archive;EXCHANGE PARTITION은 데이터를 복사하지 않고 메타데이터만 바꾸므로 매우 빠르다. 오래된 파티션을 즉시 삭제하기 부담스러울 때, 별도 아카이브 테이블로 옮겨 두고 나중에 천천히 처리하는 용도로 특히 유용하다.
파티션 유지보수 명령: ANALYZE, CHECK, OPTIMIZE PARTITION
파티션 단위로 통계 정보를 갱신하거나 손상 여부를 점검하는 명령도 별도로 있다.
-- 파티션별 통계 정보 갱신. 옵티마이저가 파티션 단위 행 수와 카디널리티를 다시 계산한다
ALTER TABLE orders ANALYZE PARTITION p2026;
-- 파티션 손상 여부 점검
ALTER TABLE orders CHECK PARTITION p2026;
-- 파티션 파일 재구성으로 단편화를 줄인다
ALTER TABLE orders OPTIMIZE PARTITION p2026;세 명령 모두 지정한 파티션에만 작업이 걸리므로 유지보수 비용이 테이블 전체 크기가 아니라 그 파티션의 크기에 비례한다. 다만 InnoDB에서 OPTIMIZE PARTITION은 내부적으로 그 파티션을 다시 만드는 것과 같은 재작성 비용을 치르므로, 대상 파티션이 여전히 크다면 트래픽이 적은 시간대에 실행하는 편이 안전하다.
파티셔닝을 적용하기 전에 확인할 것
지금까지 다룬 선택지를 실제로 적용하기 전에 확인할 목록은 다음과 같다.
| 상황 | 선택 | 근거 |
|---|---|---|
| 주요 쿼리의 대부분이 같은 컬럼으로 필터링됨 | 그 컬럼을 파티션 키로 설계 | 프루닝이 걸려야 검색 범위가 실제로 줄어든다 |
| 날짜 컬럼에 인덱스가 아직 없음 | 파티셔닝보다 인덱스를 먼저 | 같은 범위 조회에서 인덱스는 6,215배, 파티셔닝은 7.7배다 |
| 이미 인덱스가 걸려 있고 앞부분만 읽는 조회임 | 파티셔닝하지 않음 | 인덱스 하강이 이미 시작 위치를 찾으므로 프루닝이 줄일 것이 없다 (실측 차이 없음) |
| 그 쿼리가 구간 전체를 읽는 집계임 | 파티셔닝 적용 | 인덱스는 안 듣고 파티셔닝만 듣는다 (균등 분포에서 실측 7.1배) |
| 데이터가 특정 파티션에 몰려 있음 | 파티셔닝 이득을 기대하지 않음 | 프루닝이 걸려도 그 파티션 안에 대부분이 있어 읽을 양이 그대로다 (실측 차이 없음) |
| 자동 증가 id 하나만 PK로 쓰고 있음 | 복합 PK(id, 파티션 키)로 변경 | 모든 UNIQUE 키와 PK는 파티션 키를 포함해야 한다 |
| 외래키로 참조하거나 참조받는 테이블 | 파티셔닝을 적용하지 않음 | 파티션된 테이블은 외래키 관계를 가질 수 없다 |
| 오래된 시계열 데이터를 주기적으로 정리해야 함 | RANGE 파티셔닝과 DROP PARTITION | 청크 단위 DELETE보다 훨씬 빠르고 트래픽에 안전하다 |
| 사용자별로 데이터를 고르게 분산하고 싶음 | HASH 또는 KEY 파티셔닝 | 파티션 단위 선택적 정리는 어렵지만 분포는 항상 균등하다 |
| 한 RANGE 파티션 안에서도 여전히 크기가 큼 | RANGE 파티션 안에 HASH 서브파티션 추가 | 물리 파일 수는 늘지만 큰 파티션 하나를 더 잘게 쪼갤 수 있다 |