Partitioning

파티셔닝은 논리적으로 하나인 테이블을 물리적으로 여러 파티션으로 나눠 저장하면서, 애플리케이션은 파티션을 의식하지 않고 동일한 SQL로 접근할 수 있게 하는 기능이다.

• MySQL 8.0은 InnoDB 위에서 네이티브 파티셔닝 기본 제공
• 각 파티션은 독립된 .ibd 테이블스페이스 파일로 존재
• 단일 테이블을 다수의 작은 인덱스 트리로 나눠 쿼리 비용·관리 비용을 분산하는 것이 목적

도입 배경

대규모 테이블이 단일 B+Tree로 운영될 때 발생하는 문제를 분리해 해결하려는 시도다.

• 인덱스 트리 깊이 증가: 행 수가 많아질수록 B+Tree 높이가 늘어나 평균 조회 비용 상승
• 대용량 삭제 비용: 오래된 데이터를 DELETE하면 트랜잭션 로그 폭발 + 인덱스 단편화
• 백업·아카이빙 어려움: 한 테이블 내에서 일부 데이터만 분리 보관하기 까다로움

파티셔닝은 위 문제를 데이터 분할 단위로 풀어낸다.

• 파티션마다 별도의 인덱스를 가지므로 트리 깊이가 분산
• 오래된 파티션은 ALTER TABLE ... DROP PARTITION으로 즉시 제거 가능
• 파티션 단위로 백업·아카이빙·삭제 정책을 운용 가능

파티션 키

파티션 키는 어떤 행을 어느 파티션에 저장할지 결정하는 컬럼(또는 컬럼 조합)으로, CREATE TABLE의 PARTITION BY <타입>(<컬럼 표현식>) 구문에 지정한다.

• 표현식 허용: PARTITION BY RANGE (YEAR(created_at))처럼 컬럼에 함수를 적용한 결과도 키로 사용 가능
• 타입 제약: 기본 RANGE·HASH·LIST는 결과가 정수여야 하며, 날짜·문자열 원시값으로 나누려면 RANGE COLUMNS·LIST COLUMNS 구문 사용
• 다중 컬럼: PARTITION BY RANGE COLUMNS (region, created_at)처럼 복수 컬럼 조합 허용
• 불변성: 파티션 키 자체는 정의 시 확정, 변경하려면 ALTER TABLE ... PARTITION BY로 테이블 전체 재구성 필요

CREATE TABLE orders
(
    id         BIGINT   NOT NULL,
    user_id    BIGINT   NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
        PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
        PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
        PARTITION p_current VALUES LESS THAN (MAXVALUE)
        );

MySQL 파티셔닝에는 모든 유니크 인덱스(PK 포함)가 반드시 파티션 키를 포함해야 한다는 제약이 걸리기 때문에, (id, created)로 정의를 해야한다.

제약이 필요한 이유

MySQL 파티셔닝은 글로벌 인덱스를 지원하지 않고 로컬 인덱스만 둘 수 있다.

• 로컬 인덱스: 각 파티션이 자기 데이터에 대해서만 별도의 B+Tree 보유
• 글로벌 인덱스: 테이블 전체를 단일 인덱스 트리로 관리 (MySQL 미지원)

유니크 보장은 본질적으로 “전체 테이블에서 중복 없음”을 검사해야 하는데, 로컬 인덱스만으로는 다른 파티션에 같은 값이 있는지 알 수 없다.

만약 id만 PK로 두고 created_at으로 파티션을 나눴다고 가정하면 다음과 같은 일이 벌어진다.

1. p_2024_01에 id=100, created_at=2024-01-15 삽입 → 자기 파티션의 로컬 인덱스에서 중복 없음 확인 → OK
2. p_2024_02에 id=100, created_at=2024-02-15 삽입 → 자기 파티션의 로컬 인덱스에서도 중복 없음 → OK
3. 결과: 같은 PK 값 id=100이 두 파티션에 동시에 존재 → 유니크 무결성 깨짐

제약이 해결하는 방식

PK가 (id, created_at)이고 파티션도 created_at 기반으로 나뉘면, 어떤 (id, created_at) 조합이든 정확히 한 파티션으로만 라우팅된다.

• INSERT 시 파티션 키(created_at) 값으로 대상 파티션이 단일하게 결정
• 다른 파티션은 created_at 범위가 겹치지 않으므로, 같은 (id, created_at) 조합이 들어갈 자리가 원천적으로 없음
• 따라서 그 한 파티션의 로컬 인덱스만 검사하면 글로벌 유니크 보장 성립

즉 이 제약은 사용자에게는 “PK에 파티션 키도 끼워 넣어야 한다”는 불편함이지만, MySQL 입장에서는 글로벌 인덱스를 만들지 않고도 무결성을 지킬 수 있게 된다.

세컨더리 인덱스 구조

MySQL 파티셔닝의 가장 큰 특징은 세컨더리 인덱스를 파티션별 로컬 B+Tree로만 관리한다는 점이다.

• 글로벌 세컨더리 인덱스 미지원: 파티션 경계를 가로지르는 단일 인덱스는 만들 수 없음
• 각 파티션은 자신만의 (secondary_key) B+Tree를 독립 보유

이 구조의 결과로 파티션 키가 아닌 조건 조회는 모든 파티션의 인덱스를 각각 탐색해야 한다.

• 60개 월별 파티션이라면 WHERE user_id = ? 조회 시 60개 로컬 인덱스 스캔
• 단일 인덱스 1회 탐색이면 끝날 작업이 파티션 수만큼 곱해짐
• 파티셔닝이 항상 빠른 게 아니라, 쿼리 패턴이 파티션 키와 일치할 때만 이득

Partition Pruning

옵티마이저가 WHERE 조건을 분석해 관련 파티션만 읽도록 대상 파티션 집합을 좁히는 최적화로, 파티셔닝의 성능 이득은 사실상 Pruning에서 나온다.

 orders (Range Partition by created_at)
   ├── p_2024_01  [2024-01-01 ~ 2024-01-31]
   ├── p_2024_02  [2024-02-01 ~ 2024-02-29]
   ├── p_2024_03  [2024-03-01 ~ 2024-03-31]
   └── p_current  [그 이후]

 SELECT * FROM orders WHERE created_at >= '2024-02-15'
   → 옵티마이저가 p_2024_02, p_2024_03, p_current만 스캔

Pruning이 동작하려면 WHERE 절에 파티션 키가 연산 없이 들어와야 한다.

• 동작: WHERE created_at >= '2024-02-15' (원시 컬럼 비교)
• 미동작: WHERE YEAR(created_at) = 2024 (함수로 감싸 옵티마이저가 범위 추론 실패)
• 확인: EXPLAIN PARTITIONS SELECT ...의 partitions 컬럼에서 실제 스캔 대상 확인 가능

쿼리 패턴이 파티션 키와 일치하지 않으면 모든 파티션 스캔(전체 테이블 스캔보다도 비용 증가) 위험이 있다.

• 파티션 키와 자주 사용되는 조건을 같이 실어 Pruning 활성화
• 비-파티션 키 조회가 지배적이면 별도 조회 전용 테이블이나 비정규화 검토

타입별 선택 기준

파티션 타입은 분할 기준에 따라 RANGE, HASH, LIST, KEY 네 가지가 있다.

타입	분할 기준	장점	주의	대표 용도
Range	연속된 범위(날짜·ID)	시계열 Pruning 명확, DROP PARTITION으로 초 단위 아카이빙	최신 파티션에 Hotspot	주문·로그성 시계열
Hash	파티션 키의 해시값	쓰기 I/O 균등 분산	범위 쿼리 시 전 파티션 스캔	균등 분산이 최우선
List	명시적 값 집합	도메인 축으로 물리 격리	정의에 없는 값 유입 시 INSERT 에러	국가 코드·서비스 상태
Key	내장 해시 함수	문자열 키에도 적용 가능	Hash와 동일한 범위 쿼리 제약	정수가 아닌 PK

Range 파티셔닝

연속된 범위로 분할하므로 시계열 데이터에 가장 적합하다.

• 시계열 Pruning이 가장 정확하게 동작
• 오래된 파티션 제거가 DROP PARTITION 한 줄로 즉시 처리
• 단점: 최신 파티션에 쓰기·조회가 몰리는 Hotspot 발생 가능

Hash 파티셔닝

키의 해시값으로 분할해 쓰기 부하를 균등 분산한다.

• 파티션 수만 정해 두면 균등 분산이 자연스럽게 성립
• 단점: 범위 쿼리는 모든 파티션을 스캔해야 하므로 Pruning 불가능

List 파티셔닝

명시한 값 집합으로 파티션을 결정해 도메인 축으로 물리 격리한다.

• 사용 예: region_code IN ('KR', 'JP') → KR/JP 파티션으로 격리
• 단점: 정의되지 않은 값이 들어오면 INSERT 에러 발생, 새 값 추가 시 ALTER 필요

Key 파티셔닝

Hash와 유사하지만 MySQL 내장 해시 함수를 사용해 정수가 아닌 키에도 적용 가능하다.

• Hash가 정수만 받는 제약을 우회하는 용도
• 트레이드오프는 Hash와 동일

쿼리 패턴별 비용

파티셔닝의 성능은 쿼리 패턴이 파티션 키와 어떻게 맞물리느냐로 결정된다.

쿼리 패턴	동작	비용
파티션 키 = 단일 값	단일 파티션 Pruning + 인덱스	단일 인덱스 1회
파티션 키 BETWEEN 범위	다중 파티션 Pruning + 인덱스	파티션 N개 × 인덱스
비-파티션 키 = 단일 값	모든 파티션 로컬 인덱스 스캔	파티션 전체 × 인덱스
파티션 키 함수 적용	Pruning 실패, 전체 스캔	테이블 풀 스캔

대표적인 안티패턴은 비-파티션 키 단일 조회다.

• 예: 월별 Range 파티셔닝된 orders 테이블에 WHERE user_id = ? 단독 조회
• 60개월 파티션 모두에서 (user_id) 인덱스를 각각 탐색해야 함
• 우회 1 — 쿼리 패턴 조정: WHERE user_id = ? AND created_at >= ?로 파티션 키를 함께 실어 Pruning 활성화
• 우회 2 — 조회 전용 비정규화: 별도 테이블(user_orders_recent)을 두고 user_id로 직접 조회

파티셔닝 vs 샤딩

파티셔닝과 샤딩은 모두 데이터를 분할하지만 분할 단위와 한계가 다르다.

구분	파티셔닝	샤딩
분할 위치	단일 DB 인스턴스 내	여러 DB 인스턴스로 분리
목적	단일 테이블의 인덱스·관리 비용	단일 DB의 쓰기 한계 돌파
분산 효과	I/O·CPU는 같은 DB	네트워크 너머의 별도 자원
쿼리 라우팅	DB 내부 옵티마이저 처리	애플리케이션·미들웨어 구현 필요
복잡도	낮음 (DDL만)	높음 (라우팅·재샤딩·조인 우회)

파티셔닝은 단일 DB의 한계 안에서 관리·조회 효율을 끌어올리는 도구이며, 단일 DB의 쓰기 처리량을 넘어서야 한다면 샤딩이 필요하다.

참고자료

• MySQL 8.0 Reference Manual - Partitioning
• Real MySQL 8.0 (1권)