MySQL 파티셔닝은 언제 써야 하는가

인덱스 대체라는 오해부터 프루닝 조건, 로컬 인덱스의 함정, 운영 DDL 비용까지 — MySQL 파티셔닝의 설계 결정을 추적한다.

MySQL 파티셔닝은 종종 “대용량 테이블의 만병통치약”처럼 소개된다. 5억 건 테이블에 RANGE 파티션을 걸었는데 쿼리 속도가 그대로인 이유는 무엇인가? 그리고 왜 DROP PARTITION 한 줄이 수천만 건 DELETE보다 수백 배 빠른가?

파티셔닝 ≠ 인덱스 대체

파티셔닝의 역할은 정확히 하나다 — 탐색 대상 파티션 수를 줄이는 것(파티션 프루닝). 파티션 내부 탐색은 여전히 인덱스가 담당한다.

일반 테이블 (5억 건):
  단일 B-Tree, 높이 ≈ 6~8 레벨
  루트 → 리프: 6~8번 디스크 I/O

파티션 테이블 (월별 12 파티션):
  파티션당 약 4,200만 건
  파티션별 독립 B-Tree, 높이 ≈ 5 레벨
  → 1개 파티션만 접근 시 탐색 레벨 감소
  → 각 파티션은 독립적인 .ibd 파일

WHERE user_id = 12345 AND created_at >= '2024-01-01'에서 파티션 프루닝이 p2024만 골라내더라도, 그 안에서 user_id 인덱스가 없으면 p2024 전체를 Full Scan한다. 파티셔닝이 효과적인 세 가지 조건은 명확하다: ① 1억 건 이상 대용량 테이블, ② 파티션 키로 범위 조회가 주 패턴, ③ 주기적 TTL 삭제가 필요한 경우. 이 중 2개 이상이 아니라면 인덱스 최적화가 먼저다.

프루닝이 동작하는 조건

파티셔닝의 핵심 이점인 프루닝은 WHERE 절 작성 방식 하나로 12배 성능 차이를 만든다.

-- ❌ 프루닝 실패: 함수 적용
WHERE YEAR(created_at) = 2024
-- partitions: p2022,p2023,p2024,pmax (ALL Scan)

-- ✅ 프루닝 동작: 직접 비교
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- partitions: p2024 (1개)

Optimizer는 파티션 경계 값과 직접 비교할 수 있어야 프루닝을 적용한다. YEAR(col), DATE_FORMAT(col, ...), col + INTERVAL 1 DAY — 컬럼에 함수나 연산이 붙으면 어느 파티션에 해당하는지 계산이 불가능해 모든 파티션을 순회한다. OR 조건도 마찬가지다: created_at >= '2024-01-01' OR user_id = 999는 user_id 조건이 어느 파티션에 있는지 알 수 없어 ALL Scan이 된다. UNION ALL로 분리하면 각 조건에 독립적으로 프루닝을 적용할 수 있다.

파티션 종류별로 프루닝 가능한 조건도 다르다. RANGE는 =, >, <, BETWEEN 전부 프루닝이 동작한다. LIST는 =과 IN만. HASH와 KEY는 등치(=)에서만 단일 파티션에 접근하고, 범위 조건에서는 ALL Scan이 된다. 날짜 범위 쿼리에 HASH 파티션을 걸면 파티셔닝 전보다 오히려 느려진다.

로컬 인덱스의 현실

MySQL 파티션 인덱스는 파티션별 로컬 인덱스다. 글로벌 인덱스를 지원하지 않는다.

파티션 물리 구조:
  logs#P#p2022.ibd  ← p2022 데이터 + 인덱스
  logs#P#p2023.ibd  ← p2023 데이터 + 인덱스
  logs#P#p2024.ibd  ← p2024 데이터 + 인덱스

WHERE user_id = 999 실행:
  p2022.ibd의 user_id 인덱스 탐색
  p2023.ibd의 user_id 인덱스 탐색
  p2024.ibd의 user_id 인덱스 탐색
  → 파티션 수 × 인덱스 탐색

파티션 키 없이 WHERE user_id = 999만 조회하면 인덱스가 있어도 모든 파티션의 로컬 인덱스를 각각 순회한다. 파티션이 12개면 12배 비용이다. 비파티션 테이블의 단일 인덱스 탐색보다 느릴 수 있다. 쿼리에 파티션 키 조건을 항상 포함하고, 복합 인덱스에 파티션 키를 넣어 프루닝 범위 안에서 인덱스 효율을 극대화하는 것이 현실적인 해법이다.

UNIQUE 인덱스도 제약이 있다. MySQL은 UNIQUE 인덱스에 반드시 파티션 키 컬럼을 포함하도록 강제한다. email UNIQUE는 (email, created_at) UNIQUE로 바뀌는데, 이는 “같은 이메일이 다른 날짜에 중복 가능”을 의미한다. 진정한 컬럼 유일성은 애플리케이션 레벨에서 보장해야 한다.

파티셔닝 도입 전 확인해야 할 함정들

⚠ 도입 전 체크리스트

① FOREIGN KEY: 파티션 테이블은 FK를 지원하지 않는다. FK를 제거할 수 있는가, 무결성 보장을 애플리케이션이 담당할 수 있는가.
② PRIMARY KEY: 파티션 키 컬럼이 PK에 포함되어야 한다. AUTO_INCREMENT id 단독 PK는 (id, created_at) 복합 PK로 변경해야 한다.
③ Hot Partition: RANGE(created_at)은 현재 시점 파티션에 INSERT가 집중된다. 이를 수용할 수 있는가.
④ 파티션 키 UPDATE: 파티션 키 컬럼을 UPDATE하면 내부적으로 DELETE + INSERT가 발생한다.

FK를 제거하기 어려운 레거시 시스템에서 파티셔닝 도입은 현실적으로 어렵다. 파티셔닝 없이 아카이브 테이블 분리만으로도 TTL 문제를 해결할 수 있는 경우가 많다.

TTL 삭제와 운영 DDL

파티셔닝의 가장 강력한 실용적 이점은 대용량 TTL 삭제다.

DELETE FROM logs WHERE created_at < '2022-01-01':
  Row별 처리: X Lock + Undo Log + 인덱스 수정 × 수천만
  수천만 건 기준: 수십 분 ~ 수 시간
  Undo Log 폭발 → ibdata1 팽창

ALTER TABLE logs DROP PARTITION p2022:
  .ibd 파일 삭제 + 메타데이터 업데이트
  수천만 건이어도: 밀리초 ~ 수 초
  Undo Log 없음, 다른 파티션 영향 없음

MAXVALUE 파티션은 항상 유지해야 한다. 없으면 정의된 범위를 벗어난 INSERT가 즉시 실패하고 서비스 장애로 이어진다. 매월 REORGANIZE PARTITION pmax INTO (새 파티션, pmax)로 분리하는 배치를 자동화하되, 실패 시 알람이 필수다.

기존 대용량 테이블에 파티셔닝을 추가하는 ALTER TABLE ... PARTITION BY ...는 ALGORITHM=COPY로 동작해 전체 테이블을 재구성한다. 1억 건이면 수십 분에서 수 시간, 이 동안 쓰기가 차단된다. pt-online-schema-change를 사용하거나 새 파티션 테이블을 생성해 청크 단위로 이관한 뒤 RENAME하는 방식이 현실적이다.

정리

파티셔닝은 인덱스를 대체하지 않는다. 프루닝으로 탐색 파티션을 줄이고, 그 안에서 인덱스가 Row를 찾는다.
프루닝은 파티션 키 컬럼을 함수 없이 직접 비교할 때만 동작한다. YEAR(col) = 2024는 ALL Scan이다.
MySQL의 파티션 인덱스는 로컬 인덱스다. 비파티션 키 컬럼 조회는 파티션 수 × 인덱스 탐색 비용이다.
FK 미지원, PK 재설계, Hot Partition — 도입 전 이 세 가지를 먼저 확인하라.
DROP PARTITION은 파일 삭제다. 수천만 건 TTL이 밀리초로 줄어드는 이유가 이것이다.

다음 글에서는 파티셔닝과 함께 대용량 MySQL 운영의 양대 축인 Replication 아키텍처와 binlog 복제 메커니즘을 추적한다.