[MySQL] 대용량 테이블 분할 실전 사례와 쿼리 튜닝 효과

MySQL로 운영되는 시스템에서 데이터가 수백만 건, 수천만 건을 넘어가면 성능 저하는 피할 수 없다.
이럴 때 테이블을 물리적 또는 논리적으로 분할하여 데이터 접근 범위를 줄이는 것이 매우 효과적이다.

✅ 실무 시나리오: 사용자 행동 로그 테이블

A 서비스는 하루 약 500만 건의 사용자 행동 로그가 쌓이는 구조였다.
기존에는 하나의 테이블에 모든 데이터가 저장되어 있어, 다음과 같은 문제가 발생했다.

• log_date BETWEEN 조건 쿼리가 점점 느려짐
• DELETE FROM logs WHERE log_date < now() - INTERVAL 3 MONTH 문장이 느려서 서비스 중단 위협
• 로그 백업 및 아카이브 시간이 증가
• 인덱스 크기 증가로 인해 SELECT 쿼리도 느려짐

반응형

🧩 도입 전략: RANGE 파티션 적용

로그의 특성상 log_date가 명확한 파티션 기준이 되었고, 아래와 같이 RANGE 파티션을 설계하였다.

CREATE TABLE user_log (
  id BIGINT NOT NULL,
  user_id INT,
  log_date DATE,
  action VARCHAR(100),
  PRIMARY KEY (id, log_date)
)
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
  PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
  PARTITION p202409 VALUES LESS THAN (TO_DAYS('2024-10-01')),
  PARTITION pmax     VALUES LESS THAN MAXVALUE
);

적용 후 기대한 성능 개선이 실제로 나타났는지 테스트를 진행했다.

🔍 쿼리 성능 전/후 비교

1. SELECT 쿼리

-- 최근 한 달 로그 조회
SELECT COUNT(*) FROM user_log
WHERE log_date BETWEEN '2024-08-01' AND '2024-08-31';

• 파티션 적용 전: 7.1초 (FULL TABLE SCAN)
• 파티션 적용 후: 0.9초 (파티션 프루닝으로 해당 월 파티션만 스캔)

2. DELETE 쿼리

DELETE FROM user_log
WHERE log_date < '2024-07-01';

• 파티션 적용 전: 13.2초
• 파티션 적용 후: 1.1초 (ALTER TABLE ... DROP PARTITION 으로 대체)

ALTER TABLE user_log DROP PARTITION p202407;

DROP PARTITION은 내부적으로 테이블을 수정하지 않고 메타데이터만 변경하여 삭제하므로 훨씬 빠르고 안전하다.

3. INSERT 성능

• 데이터가 각 월별 파티션에 나뉘어 들어가므로 디스크 I/O 분산 효과 발생
• 버퍼 풀 충돌 감소로 인해 INSERT 시 TPS 증가 확인

반응형

🛠️ 실무 팁: 분할 시 고려사항

• 날짜 파티션은 월 단위가 가장 일반적이고 관리하기 좋다
• 오래된 파티션을 DROP하거나 EXPORT하여 백업 자동화 가능
• DDL을 자동화하려면 이벤트 스케줄러 또는 외부 배치 스크립트 사용
• 파티션 이름은 명확하게 pYYYYMM 형식으로 지어야 유지보수에 유리
• 파티션 테이블도 인덱스 설계가 중요하다 (WHERE에 사용되는 컬럼 포함)

⚠️ 주의사항

• WHERE 조건에 파티션 키가 포함되지 않으면 모든 파티션을 조회하므로 성능 개선이 없음
• DROP PARTITION으로 데이터를 삭제할 경우, 트랜잭션 롤백이 불가능하므로 신중하게 처리
• UPDATE log_date와 같이 파티션 키를 변경하는 UPDATE는 비효율적이며, 가능하면 피해야 한다
• 너무 자주 파티션을 나누면 메타데이터 관리 부담이 증가하므로 균형이 필요하다

📊 파티셔닝 도입 전후 요약

항목	도입 전 (기존 단일 테이블)	도입 후 (파티셔닝 테이블)
SELECT 속도	느림 (FULL SCAN)	빠름 (PARTITION PRUNING)
DELETE 처리	오래 걸림	매우 빠름 (DROP PARTITION)
INSERT 처리	인덱스 병목 존재	병목 완화
유지보수 편의성	불편함	월 단위 관리 가능
시스템 장애 위험	높음	관리성 향상

✅ 정리

• 대용량 로그 테이블에 파티셔닝을 적용하면 쿼리 성능, 유지보수, 시스템 안정성이 크게 향상된다.
• 특히 RANGE 파티션은 날짜 기반 로그에 매우 적합하고, DROP PARTITION을 통한 삭제가 매우 강력하다.
• SELECT/DELETE/INSERT 성능 모두 개선되며, 트래픽 급증 상황에서도 대응이 쉬워진다.
• 중요한 건 파티션 키 선정과 파티션 수 조정, 인덱스 설계를 함께 고려해야 한다는 점이다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - Partitioning Use Cases