MySQL 기준 테이블 파티션 partition 생성 예제 코드설명

 

 

 

MySQL에서 가장 실무적으로 많이 쓰는 3가지 파티션 방식(월별 RANGE / 일자 RANGE / HASH) 기준으로 예제

설명 + 예제 + 쿼리 패턴까지 한 번에 이해되는 구성

---

✅ 1) 월별 RANGE 파티션 (로그/거래/이벤트 테이블에서 가장 많이 사용)

📌 사용 조건

• created_at 같은 날짜 컬럼 기준 조회가 많을 때
• 월 단위 보관/삭제가 필요할 때
• 파티션 Pruning 효과가 매우 좋음

---

📌 테이블 생성 예제

CREATE TABLE transaction_log (
    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 (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

📌 중요한 포인트 2개

1. PK에 파티션 키(created_at)를 포함해야 함
   (MySQL InnoDB 파티션 테이블의 제약사항)
2. TO_DAYS()를 사용하는 이유
   → DATETIME을 정수(일수)로 변환해야 RANGE 기준을 정확하게 잡을 수 있음.

---

📌 파티션 추가 (매달 자동 확장)

ALTER TABLE transaction_log
ADD PARTITION (
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01'))
);

→ 파티션은 미리 여러 달을 추가해두는 게 운영 편함.

---

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

ALTER TABLE transaction_log DROP PARTITION p202401;

 

→ 즉시 DROP, DELETE보다 수십~수천 배 빠름
→ Undo log 거의 없음

---

✅ 2) 일자 단위 RANGE 파티션 (트래픽 큰 서비스에서 흔함)

하루 수백만 건 이상 적재되는 테이블에 주로 사용.

테이블 생성

CREATE TABLE access_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    ip VARCHAR(50),
    created_at DATETIME NOT NULL,
    PRIMARY KEY(id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-02')),
    PARTITION p20241202 VALUES LESS THAN (TO_DAYS('2024-12-03')),
    PARTITION p20241203 VALUES LESS THAN (TO_DAYS('2024-12-04')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

일별 파티션 장점

• 읽기/쓰기 경합이 줄어듦
• HOT partition 문제 해결되기 쉬움
• 일별 purge가 쉬움

---

📌 일별 파티션 추가 자동화 스크립트 예 (운영에서 많이 쓰임)

ALTER TABLE access_log
ADD PARTITION (
    PARTITION p20241204 VALUES LESS THAN (TO_DAYS('2024-12-05'))
);

보통 자동 스케줄러(CRON + SQL)로 매일 생성하는 방식 사용.

---

---

✅ 3) HASH 파티션 (ID 균등 분배가 필요할 때)

📌 조회가 날짜 기반이 아니라 USER 기준으로 쏠리는 경우 추천

예:

• 특정 user_id가 너무 많은 요청을 하는 API
• 유저 기반 작업이 대부분인 게임 DB

---

예제: user_id 기준 8개 파티션

CREATE TABLE user_log (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    action VARCHAR(100),
    created_at DATETIME,
    PRIMARY KEY(id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;

장점

• 균등하게 분산 → 특정 파티션만 HOT해지는 걸 완화
• 관리 난이도가 낮음

단점

• 기간별 삭제가 필요하면 부적합 (DROP PARTITION 불가)
• 로그성 테이블에는 RANGE가 더 좋음

---

---

✅ 파티션 pruning(가지치기) 제대로 되는 쿼리 예제

SELECT *
FROM transaction_log
WHERE created_at >= '2024-03-01'
  AND created_at < '2024-04-01';

→ MySQL은 자동으로
p202403 파티션 하나만 스캔함

반대로 Pruning이 안 되는 패턴:

WHERE DATE(created_at) = '2024-03-05' 

 

날짜 함수 씌우면 pruning 안 됨.
→ 반드시 컬럼에 함수 쓰면 안 됨

 

---

📌  실무 가이드 (진짜 중요한 숫자)

데이터 양	추천
1천만 건 이하	파티션 불필요
5천만 건 이상	월별 파티션 검토
1억 건 이상	월별 or 일별 강력추천
1억 ~ 10억	파티션 없으면 장애 수준
10억 이상	파티션 필수 + 압축 or 샤딩 고려

 

---

✅ 전체 구조 먼저 보기

PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-02')),
    PARTITION p20241202 VALUES LESS THAN (TO_DAYS('2024-12-03')),
    PARTITION p20241203 VALUES LESS THAN (TO_DAYS('2024-12-04')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

 

이 문법은 MySQL에서 **"이 테이블은 날짜(created_at) 기준으로 여러 조각(파티션)으로 나누어 저장하겠다"**라는 의미입니다.

하나씩 뜯어보죠.

---

✅ 1. PARTITION BY RANGE (TO_DAYS(created_at))

🔍 의미

• 파티션을 RANGE 방식으로 나누겠다
• 나누는 기준은: TO_DAYS(created_at) 값

🔍 왜 TO_DAYS(created_at)을 쓰는가?

created_at은 DATETIME 타입입니다.
하지만 RANGE 파티션은 정수(Integer) 형태의 값을 기준으로 나누는게 가장 정확합니다.

그래서 MySQL에서 흔하게 두 가지를 사용합니다:

함수	의미
TO_DAYS(datetime)	날짜를 일수(정수)로 변환
UNIX_TIMESTAMP(datetime)	초 기반 정수

 

예:
TO_DAYS('2024-12-02') → 752350 같은 정수로 변환됩니다.

즉, 내부적으로는 이런 느낌:

created_at >= 2024-12-01 → TO_DAYS = 752349
created_at < 2024-12-02 → TO_DAYS = 752350

 

정수값 기준으로 나누니까 파티션 범위가 깔끔하게 동작합니다.

---

✅ 2. PARTITION p20241201 VALUES LESS THAN (...)

🔍 의미

이 파티션은 특정 기준값보다 작은 데이터들을 저장한다라는 뜻입니다.

예를 들어:

PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-02'))

이 파티션에는 2024-12-01 하루치 데이터만 들어갑니다.

왜냐?

• 2024-12-01의 TO_DAYS 값 = 752349
• 2024-12-02의 TO_DAYS 값 = 752350

즉:

p20241201 파티션 조건:
TO_DAYS(created_at) < 752350
→ created_at < 2024-12-02 00:00:00

따라서 2024-12-01 00:00:00 ~ 2024-12-01 23:59:59 범위 데이터만 저장됨.

---

✅ 3. RANGE 파티션은 “구간” 개념 — 그래서 연속적으로 작성해야 함

전체 파티션 구간이 이렇게 연결되도록 구성해야 합니다.

p20241201 → created_at < 2024-12-02
p20241202 → created_at < 2024-12-03
p20241203 → created_at < 2024-12-04

RANGE 파티션은 다음 규칙이 있습니다:

✔ 각 파티션의 범위는 “LESS THAN” 기준
✔ 범위가 서로 겹쳐서는 안 됨
✔ 전체 범위가 끊어지면 안 됨

그래서 이렇게 연속해서 만들어야 합니다.

---

✅ 4. PARTITION pmax VALUES LESS THAN MAXVALUE

🔍 pmax 의 역할

• 마지막에 모든 나머지 값을 담는 catch-all 파티션
• created_at이 어떤 값이 들어오더라도 받을 수 있는 최종 파티션

왜 필요한가?

만약 새 날짜가 들어와도 해당하는 파티션이 없으면 insert 에러가 납니다.

예:

INSERT INTO logs(created_at) VALUES('2024-12-10');

그런데 2024-12-10 파티션을 미리 안 만들어놨다면 에러.

그래서 운영에서 반드시 MAXVALUE 마지막 파티션은 두는 게 안정적입니다.

---

✅ 전체 문법을 실제 행동 기준으로 요약하면:

✔ 1. created_at을 정수 날짜로 바꾼 뒤

✔ 2. 각 파티션의 LESS THAN 조건 범위에 따라 데이터 저장

✔ 3. 하루 단위 파티션이라면

• p20241201 → 12/01 데이터
• p20241202 → 12/02 데이터
• p20241203 → 12/03 데이터

✔ 4. 마지막 pmax가 모든 나머지 날짜를 받음

---

🔥 실제 동작 예제 (삽입 시 어떤 파티션에 들어가는가?)

INSERT INTO logs(created_at) VALUES('2024-12-01 12:00:00');

→ p20241201으로 들어감
(created_at < 2024-12-02에 해당)

INSERT INTO logs(created_at) VALUES('2024-12-03 07:00:00');

→ p20241203으로 들어감
(created_at < 2024-12-04에 해당)

INSERT INTO logs(created_at) VALUES('2025-01-01');

→ pmax로 들어감 (나머지 범위)

---