마이위키

테마

난이도 문제은행 5

파티션 ​

파티셔닝(partitioning)은 테이블 하나를 칼럼 값 기준으로 여러 조각으로 나눠 저장하는 기능이고, 나뉜 조각 하나를 파티션(partition)이라 부른다. 애플리케이션은 여전히 테이블 하나에 쿼리하지만, MySQL은 조건을 보고 필요한 파티션만 읽거나 쓴다. 파티션은 오래된 데이터를 통째로 버리기 쉽게 만들 때 가장 큰 효과를 낸다.

파티션을 쓰는 이유 ​

로그·주문 이력처럼 시간이 지나면 쌓이기만 하는 테이블을 떠올려 보자. 보통 최근 데이터만 자주 읽고, 일정 기간이 지난 데이터는 지운다. 이런 테이블을 월 단위 파티션으로 나누면 두 가지가 달라진다.

  • 삭제가 싸진다. 석 달 지난 데이터를 지울 때 DELETE로 행을 하나씩 지우지 않고, 그 달의 파티션 하나를 떼어 버린다.
  • 읽을 범위가 줄어든다. "이번 달 주문"을 조회하면 이번 달 파티션만 읽는다. 조건에 맞는 값이 있을 수 없는 파티션을 건너뛰는 이 최적화를 파티션 프루닝(partition pruning)이라 한다.

MySQL 8.4의 파티셔닝은 행을 나누는 수평 파티셔닝(horizontal partitioning)만 지원하고, 칼럼을 나누는 수직 파티셔닝(vertical partitioning)은 지원하지 않는다. 파티션마다 데이터와 인덱스를 따로 가진다는 점이 파티션 2에서 다루는 기본 키·유니크 인덱스(unique index) 제약으로 이어진다.

sql
CREATE TABLE access_log (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  user_id    BIGINT NOT NULL,
  url        VARCHAR(500),
  PRIMARY KEY (id, created_at)          -- 파티션 키를 기본 키에 포함해야 한다
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
  PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
  PARTITION p202609 VALUES LESS THAN ('2026-10-01')
);

위 예제처럼 값의 구간으로 나누는 방식이 RANGE 파티션이다. 파티션마다 VALUES LESS THAN으로 '이 값 미만' 경계를 정하고, 행은 자기 값이 속한 구간에 들어간다. created_at처럼 행이 어느 파티션에 갈지 정하는 칼럼(또는 식)을 파티션 키(partition key)라 한다. 값 목록으로 나누는 LIST, 해시로 고르게 흩는 HASH·KEY는 파티션 2에서 다룬다.

쿼리가 파티션을 고르는 방식 ​

MySQL은 문장마다 파티션 키 값을 보고 어느 파티션을 건드릴지 먼저 정한다.

  • INSERT: 새 행의 파티션 키 값을 계산해 들어갈 파티션 하나를 정하고 그곳에만 쓴다. 맞는 파티션이 없으면(예: 위 테이블에 10월 행을 넣음) 에러가 난다. 그래서 RANGE 파티션은 미래 구간을 미리 만들어 두거나, 어느 구간에도 들지 않는 큰 값을 모두 받는 VALUES LESS THAN (MAXVALUE) 파티션을 마지막에 둔다.
  • UPDATE·DELETE: WHERE에 파티션 키 조건이 있으면 해당 파티션에서만 대상 행을 찾는다. 파티션 키 값 자체를 바꾸는 UPDATE는 새 값이 다른 구간에 들면 행이 다른 파티션으로 옮겨질 수 있다.
  • SELECT: 파티션 키 조건으로 프루닝이 되면 남은 파티션만 읽고, 조건이 없으면 모든 파티션을 읽는다. 파티션 안에서는 그 파티션의 인덱스를 쓴다.

파티션 프루닝과 EXPLAIN ​

프루닝은 WHERE가 파티션 키를 =, IN, 범위 비교(<, >, BETWEEN)로 비교해 읽을 파티션을 좁힐 수 있을 때 적용된다. 파티션 키를 PARTITION BY RANGE (TO_DAYS(created_at))처럼 날짜 칼럼에 YEAR()·TO_DAYS()·TO_SECONDS()를 씌운 식으로 정의한 테이블도, 쿼리가 created_at에 날짜 범위 조건을 걸면 프루닝된다. 반대로 쿼리 쪽에서 파티션 키 칼럼을 함수로 가공하면 옵티마이저(optimizer)가 값의 범위를 알기 어려워지므로, 조건은 칼럼 자체에 건다.

실제로 어느 파티션을 읽는지는 실행 계획(execution plan)의 partitions 칼럼으로 확인한다(실행 계획 분석 참고).

sql
EXPLAIN SELECT * FROM access_log
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-15';
-- partitions: p202609   ← 한 파티션만 읽는다

partitions에 모든 파티션 이름이 나열되면 프루닝이 안 된 것이다.

파티션 추가와 삭제 ​

RANGE·LIST 파티션은 ALTER TABLE로 조각 단위 관리를 한다.

sql
ALTER TABLE access_log ADD PARTITION
  (PARTITION p202610 VALUES LESS THAN ('2026-11-01'));  -- 다음 달 구간 추가
ALTER TABLE access_log DROP PARTITION p202607;          -- 오래된 달 통째 삭제

DROP PARTITION이 DELETE보다 빠른 이유는 일하는 단위가 다르기 때문이다. DELETE는 행을 하나씩 찾아 지우면서 행마다 언두 로그(undo log)와 리두 로그(redo log)를 남기고, 모든 인덱스에서 항목을 뺀다. InnoDB는 기본 설정에서 파티션마다 별도 테이블스페이스(tablespace) 파일을 두므로, DROP PARTITION은 그 저장 단위를 통째로 버리고 행 수와 거의 상관없이 끝난다.

주의할 점도 있다. DROP PARTITION은 DDL(Data Definition Language, 데이터 정의어)이라 암시적 커밋(implicit commit)을 일으키고 롤백(rollback)할 수 없다(트랜잭션 기초와 주의사항 참고). DELETE와 달리 지운 행 수도 알려 주지 않는다. 또 테이블 구조를 바꾸는 작업이라 메타데이터 락(metadata lock)을 잡는데, 긴 쿼리가 테이블을 쓰고 있으면 그 쿼리가 끝날 때까지 기다린다(MySQL 엔진의 잠금 참고).

  • RANGE 파티션의 ADD PARTITION은 맨 끝에만 붙일 수 있다. 이미 MAXVALUE 파티션이 있으면 그 뒤에 붙일 수 없으므로 REORGANIZE PARTITION으로 MAXVALUE 파티션을 둘로 나눈다. 이 명령은 데이터를 잃지 않고 파티션을 나누거나 합친다.
  • 파티션은 남기고 안의 데이터만 비우려면 ALTER TABLE ... TRUNCATE PARTITION을 쓴다.
  • 지울 데이터를 따로 보관해야 하면, 파티션 하나를 같은 구조의 일반 테이블과 맞바꾸는 EXCHANGE PARTITION으로 빼낸 뒤 보관한다.

면접 포인트 ​

  • "파티션을 왜 쓰나요?" → 오래된 데이터 삭제를 DROP PARTITION 한 번으로. 파티션 키 조건 쿼리는 프루닝으로 읽을 범위 축소.
  • "DROP PARTITION이 DELETE보다 빠른 이유?" → 행 단위 삭제·로그·인덱스 정리 대신 조각째 제거. 단 DDL이라 롤백 불가, 메타데이터 락 대기 주의.
  • "프루닝이 됐는지 어떻게 아나요?" → EXPLAIN의 partitions 칼럼.

관련 문서

파파티션 2소주제›실실행 계획 분석소주제›트트랜잭션 기초와 주의사항소주제›MMySQL 엔진의 잠금소주제›
🎙 인터뷰 연습퀴즈 풀기 5문항