MySQL Partitioning: RANGE·LIST – 테오의 저장소

파티셔닝이란: 하나의 논리 테이블, 여러 물리 저장소

MySQL 파티셔닝은 하나의 논리적 테이블을 여러 개의 물리적 파티션으로 분할하여 저장하는 기능입니다. 애플리케이션은 여전히 단일 테이블로 쿼리하지만, MySQL 엔진은 내부적으로 필요한 파티션만 접근하여(partition pruning) 성능을 크게 향상시킵니다.

파티셔닝이 효과적인 핵심 시나리오:

시계열 데이터: 로그, 이벤트, 주문 이력 — 최근 데이터만 자주 조회하고 오래된 데이터는 삭제
대용량 테이블: 수천만~수억 행 이상에서 인덱스 크기가 메모리를 초과할 때
데이터 생명주기 관리: 오래된 파티션을 DROP PARTITION으로 즉시 삭제 (DELETE + VACUUM 대비 수천 배 빠름)

파티셔닝 타입 4가지: RANGE·LIST·HASH·KEY

타입	분할 기준	적합한 사용 케이스	Pruning 효과
RANGE	연속 범위 (날짜, ID 등)	시계열 데이터, 로그, 주문	매우 높음
LIST	이산 값 목록	지역, 상태 코드별 분류	높음
HASH	해시 함수 결과 (MOD)	균등 분산이 목적일 때	낮음 (정확한 값만)
KEY	MySQL 내부 해시 함수	PK 기반 균등 분산	낮음

RANGE 파티셔닝: 시계열 데이터의 정석

RANGE는 가장 많이 사용되는 파티셔닝 타입으로, 연속 범위를 기준으로 행을 분할합니다.

CREATE TABLE orders (
  id          BIGINT NOT NULL AUTO_INCREMENT,
  user_id     INT NOT NULL,
  total_price DECIMAL(10, 2) NOT NULL,
  status      VARCHAR(20) NOT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)        -- ⚠️ 파티션 키를 PK에 포함 필수
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  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
);

RANGE COLUMNS: 날짜 컬럼 직접 사용

RANGE COLUMNS는 함수 호출 없이 DATE/DATETIME 컬럼을 직접 사용할 수 있어 더 직관적이고 월 단위 파티셔닝에 적합합니다.

CREATE TABLE event_logs (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(50) NOT NULL,
  payload    JSON,
  created_at DATE NOT NULL,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
  PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
  PARTITION p202503 VALUES LESS THAN ('2025-04-01'),
  PARTITION p202504 VALUES LESS THAN ('2025-05-01'),
  PARTITION p202505 VALUES LESS THAN ('2025-06-01'),
  PARTITION p202506 VALUES LESS THAN ('2025-07-01'),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

LIST 파티셔닝: 이산 값 기반 분할

CREATE TABLE user_activities (
  id        BIGINT NOT NULL AUTO_INCREMENT,
  user_id   INT NOT NULL,
  region    VARCHAR(10) NOT NULL,
  action    VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, region)
) ENGINE=InnoDB
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_asia    VALUES IN ('KR', 'JP', 'CN', 'SG'),
  PARTITION p_europe  VALUES IN ('DE', 'FR', 'GB', 'NL'),
  PARTITION p_america VALUES IN ('US', 'CA', 'BR', 'MX'),
  PARTITION p_others  VALUES IN ('AU', 'IN', 'OTHER')
);

주의: LIST 파티셔닝에서 정의되지 않은 값이 INSERT되면 에러가 발생합니다. RANGE의 MAXVALUE와 달리 catch-all이 없으므로, 가능한 모든 값을 포함하거나 기본 파티션을 반드시 만들어야 합니다.

HASH·KEY 파티셔닝: 균등 분산

-- HASH: 사용자 정의 표현식
CREATE TABLE sessions (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  user_id    INT NOT NULL,
  token      VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, user_id)
) ENGINE=InnoDB
PARTITION BY HASH (user_id)
PARTITIONS 8;                    -- 8개 파티션에 MOD로 분산

-- KEY: MySQL 내부 해시 함수 사용 (문자열 컬럼도 가능)
CREATE TABLE cache_entries (
  cache_key  VARCHAR(255) NOT NULL,
  value      MEDIUMBLOB,
  expires_at DATETIME,
  PRIMARY KEY (cache_key)
) ENGINE=InnoDB
PARTITION BY KEY (cache_key)
PARTITIONS 16;

HASH/KEY는 pruning 효과가 제한적입니다. 정확한 값으로 조회할 때만 단일 파티션을 찾을 수 있고, 범위 조건에서는 모든 파티션을 스캔합니다.

파티션 키와 Primary Key 제약: 가장 흔한 실수

MySQL InnoDB에서 파티셔닝의 가장 중요한 제약은 다음과 같습니다:

파티션 표현식에 사용되는 모든 컬럼은 테이블의 모든 Unique Index(Primary Key 포함)에 포함되어야 합니다.

-- ❌ 실패: created_at이 PK에 포함되지 않음
CREATE TABLE orders (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATE NOT NULL,
  PRIMARY KEY (id)               -- created_at 없음!
) PARTITION BY RANGE COLUMNS (created_at) (...);
-- ERROR 1503: A PRIMARY KEY must include all columns in the partition function

-- ✅ 성공: PK에 파티션 키 포함
CREATE TABLE orders (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATE NOT NULL,
  PRIMARY KEY (id, created_at)   -- 복합 PK
) PARTITION BY RANGE COLUMNS (created_at) (...);

이 제약은 Unique Index에도 동일하게 적용됩니다:

-- ❌ 실패
ALTER TABLE orders ADD UNIQUE INDEX idx_order_no (order_no);
-- order_no만으로는 Unique를 보장할 수 없음 (파티션 간 중복 가능)

-- ✅ 성공: 파티션 키를 Unique Index에 포함
ALTER TABLE orders ADD UNIQUE INDEX idx_order_no (order_no, created_at);

이 제약으로 인해 “순수 비즈니스 키(order_no) 단독 Unique”를 보장할 수 없다는 것이 파티셔닝의 가장 큰 설계 트레이드오프입니다. 애플리케이션 레벨에서 추가 검증이 필요할 수 있습니다.

Partition Pruning: EXPLAIN으로 검증하기

Partition pruning은 쿼리 조건에 따라 불필요한 파티션을 스캔에서 제외하는 최적화입니다. pruning이 동작하지 않으면 파티셔닝의 의미가 없으므로, 반드시 EXPLAIN으로 확인해야 합니다.

-- ✅ Pruning 동작: 2026년 데이터만 조회
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

-- EXPLAIN 결과에서 partitions 컬럼 확인:
-- partitions: p2026        ← 단일 파티션만 스캔
-- type: range
-- rows: 50000

-- ❌ Pruning 미동작: 파티션 키 컬럼에 함수 적용
EXPLAIN SELECT * FROM orders
WHERE YEAR(created_at) = 2026;

-- RANGE COLUMNS 파티셔닝에서 YEAR() 함수를 쓰면 pruning이 실패할 수 있음
-- partitions: p2023,p2024,p2025,p2026,p_future  ← 전체 스캔!

조건	Pruning	이유
`WHERE created_at = '2026-03-15'`	✅ 동작	파티션 키 직접 비교
`WHERE created_at BETWEEN ... AND ...`	✅ 동작	범위가 파티션 경계와 매칭
`WHERE created_at IN (...)`	✅ 동작	각 값의 파티션을 개별 판단
`WHERE YEAR(created_at) = 2026`	⚠️ 타입 의존	RANGE(YEAR(…))이면 동작, RANGE COLUMNS면 실패
`WHERE user_id = 100` (파티션 키 아님)	❌ 미동작	파티션 키가 조건에 없음

파티션 관리: ADD·DROP·REORGANIZE

새 파티션 추가

-- p_future를 분할하여 2027년 파티션 추가
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
  PARTITION p2027 VALUES LESS THAN (2028),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

주의: ADD PARTITION은 MAXVALUE 파티션이 있으면 실패합니다. REORGANIZE PARTITION을 사용하여 MAXVALUE 파티션을 분할해야 합니다.

오래된 파티션 삭제 (데이터 퍼지)

-- 2023년 데이터 즉시 삭제 — DELETE보다 수천 배 빠름
ALTER TABLE orders DROP PARTITION p2023;

-- 확인
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

DROP PARTITION은 해당 파티션의 데이터 파일 자체를 제거하므로 행 수와 무관하게 거의 즉시 완료됩니다. 반면 DELETE FROM orders WHERE created_at < '2024-01-01'은 수억 행을 하나씩 삭제해야 하고, 이후 디스크 공간 회수를 위해 OPTIMIZE TABLE이 필요합니다.

파티션 병합 (REORGANIZE)

-- 월별 파티션을 분기별로 병합
ALTER TABLE event_logs REORGANIZE PARTITION p202501, p202502, p202503 INTO (
  PARTITION p2025q1 VALUES LESS THAN ('2025-04-01')
);

파티션 자동 관리: 크론으로 월별 파티션 자동 생성

프로덕션 환경에서는 파티션을 수동으로 관리하면 누락 위험이 있습니다. 크론 스크립트로 자동화하는 것이 안전합니다.

-- 프로시저: 다음 달 파티션이 없으면 자동 생성
DELIMITER $$
CREATE PROCEDURE maintain_partitions(IN table_name VARCHAR(64))
BEGIN
  DECLARE next_month DATE;
  DECLARE partition_name VARCHAR(20);
  DECLARE boundary VARCHAR(20);

  SET next_month = DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);
  SET partition_name = CONCAT('p', DATE_FORMAT(next_month, '%Y%m'));
  SET boundary = DATE_FORMAT(DATE_ADD(next_month, INTERVAL 1 MONTH), '%Y-%m-%d');

  -- p_future를 REORGANIZE하여 새 파티션 추가
  SET @sql = CONCAT(
    'ALTER TABLE ', table_name,
    ' REORGANIZE PARTITION p_future INTO (',
    'PARTITION ', partition_name, ' VALUES LESS THAN (''', boundary, '''),',
    'PARTITION p_future VALUES LESS THAN MAXVALUE)'
  );

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 매월 1일 실행
-- crontab: 0 0 1 * * mysql -e "CALL maintain_partitions('event_logs')"

기존 테이블 파티셔닝 적용: ALTER TABLE

-- 1) PK에 파티션 키 추가 (기존 PK 변경 필요)
ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);

-- 2) 파티셔닝 적용
ALTER TABLE orders PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
  PARTITION p2026 VALUES LESS THAN ('2027-01-01'),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

⚠️ 운영 주의사항:

ALTER TABLE ... PARTITION BY는 테이블을 전체 재작성합니다. 대용량 테이블에서는 수 시간이 걸릴 수 있습니다.
MySQL 8.0+에서는 ALGORITHM=INPLACE가 일부 파티션 작업에 지원되지만, 초기 파티셔닝 적용은 여전히 COPY 알고리즘입니다.
프로덕션에서는 pt-online-schema-change 또는 gh-ost로 무중단 적용을 권장합니다.

파티셔닝의 함정과 트레이드오프

함정	설명	대응
FK 미지원	InnoDB 파티션 테이블은 Foreign Key를 사용할 수 없음	애플리케이션 레벨 참조 무결성 또는 FK 없이 설계
Unique 제약	모든 Unique Index에 파티션 키 포함 필수	비즈니스 키 단독 Unique 불가 — 앱 레벨 검증 추가
Cross-partition 쿼리	파티션 키 없는 WHERE 조건은 전체 파티션 스캔	주요 쿼리에 반드시 파티션 키 포함
파티션 수 제한	MySQL 8.0 기준 최대 8192개	일별 파티셔닝 시 약 22년 — 충분하지만 관리 부담 고려
오버 파티셔닝	파티션이 너무 많으면 파일 디스크립터 낭비, DDL 느려짐	일별보다 월별/분기별 권장, 오래된 파티션 주기적 DROP

INFORMATION_SCHEMA로 파티션 상태 모니터링

-- 파티션별 행 수·데이터 크기 확인
SELECT
  PARTITION_NAME,
  PARTITION_EXPRESSION,
  PARTITION_DESCRIPTION AS boundary,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb'
  AND TABLE_NAME = 'orders'
ORDER BY PARTITION_ORDINAL_POSITION;

-- 파티션이 없는(비파티션) 대용량 테이블 찾기
SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
  AND TABLE_ROWS > 10000000
  AND CREATE_OPTIONS NOT LIKE '%partitioned%'
ORDER BY TABLE_ROWS DESC;

파티셔닝 vs 샤딩 vs 아카이빙: 선택 기준

전략	적용 범위	장점	단점
파티셔닝	단일 서버, 단일 테이블	투명한 쿼리, DROP 퍼지	FK 불가, Unique 제약
샤딩	여러 서버에 데이터 분산	수평 확장, 쓰기 분산	앱 복잡성 증가, 크로스 샤드 쿼리
아카이빙	오래된 데이터를 별도 테이블/DB로 이동	원본 테이블 크기 유지	아카이브 조회 시 별도 로직 필요

판단 기준: 단일 서버에서 처리 가능한 데이터량이고 시계열 접근 패턴이면 파티셔닝, 쓰기 부하가 단일 서버를 초과하면 샤딩, 오래된 데이터를 거의 조회하지 않으면 아카이빙이 적합합니다.

실무 설계 체크리스트

파티션 키 선택: 대부분의 쿼리 WHERE 절에 포함되는 컬럼을 선택합니다. 시계열 데이터는 날짜 컬럼이 정석입니다.
PK/Unique 제약: 파티션 키를 반드시 포함해야 합니다. 기존 테이블 전환 시 PK 변경이 필요할 수 있습니다.
EXPLAIN 검증: 주요 쿼리에서 partitions 컬럼이 필요한 파티션만 표시하는지 확인합니다.
자동 관리: 크론/프로시저로 미래 파티션 생성, 오래된 파티션 DROP을 자동화합니다.
파티션 수: 월별 기준 최대 수백 개 이내로 유지합니다. 일별은 관리 부담이 큽니다.
FK 포기: 파티션 테이블에는 FK를 사용할 수 없습니다. 참조 무결성은 앱 레벨에서 보장합니다.

참고 자료

📥 관련 무료 이북

NestJS + TypeORM 실전 가이드 — 실전 가이드 무료 제공

무료로 받기 →