chocomae/doc/plan/db/05-option4-partitioning.md

안4. 테이블 파티셔닝 (현재는 보류 권장)

개요 문서 | 추천 단계: 보류 (재검토 기준 충족 시 검토) | 난이도: 상 | 위험도: 높음 | 예상 작업량: 3~5일 + 서비스 점검 시간

한 줄 요약

best_record, typing_exam_record를 RecordDateTime 기준 연도(또는 월)별 파티션으로 나누어, 날짜 범위 조회 시 해당 파티션만 읽고 오래된 파티션은 통째로 떼어낼 수 있게 한다. 효과는 크지만 외래 키 제거·기본 키 변경·테이블 재구성이 필요해 현재 규모와 운영 여건에서는 권장하지 않는다.

---

1. 파티셔닝이란 — 쉬운 설명

• 지금 best_record는 서랍 하나에 몇 년 치 기록이 모두 들어 있습니다.
• 파티셔닝은 겉보기엔 테이블 하나지만 내부적으로 "2019년 서랍, 2020년 서랍, …, 2026년 서랍" 으로 나누어 저장합니다.
• "2026년 9월 기록"을 찾으면 DB가 2026년 서랍만 엽니다(= 파티션 프루닝, partition pruning).
• 2019년 기록을 정리할 때는 행을 하나씩 지우지 않고 2019년 서랍을 통째로 빼냅니다. 수백만 행이라도 거의 즉시 끝납니다.

---

2. 해결하는 문제

문제 (선행 문서 번호)	해결 정도	설명
3. 이력 테이블 무한 증가	◎	오래된 파티션을 즉시 분리·삭제
1. 함수로 감싼 날짜 조건	△	안1의 범위 조건이 먼저 적용되어야 프루닝이 동작. 파티셔닝만으로는 해결되지 않음
2. 복합 인덱스 부재	△	파티션마다 인덱스가 작아짐. 복합 인덱스 자체는 여전히 필요
6. 관리자 기록 목록	△	날짜 범위가 좁으면 일부 파티션만 읽음

---

3. 적용 모습 (예시)

아래 SQL은 이해를 돕기 위한 예시입니다. 실제 적용 시에는 스테이징에서 충분히 검증해야 합니다.

3-1. 사전 확인: 외래 키 이름

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'chocomae'
  AND TABLE_NAME IN ('best_record', 'typing_exam_record');

-- 반대로, 이 두 테이블을 참조하는 외래 키가 없는지도 확인 (현재 스키마 기준 없음)
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'chocomae'
  AND REFERENCED_TABLE_NAME IN ('best_record', 'typing_exam_record');

현재 make_db.sql 기준 best_record는 maestro, app, player를 참조하는 외래 키 3개, typing_exam_record는 maestro, player, writing을 참조하는 외래 키 3개가 있습니다.

3-2. 방법 A — 기존 테이블을 직접 변경 (간단하지만 쓰기 잠금 발생)

-- 1) 외래 키 제거 (이름은 3-1 결과로 교체)
ALTER TABLE best_record
  DROP FOREIGN KEY best_record_ibfk_1,
  DROP FOREIGN KEY best_record_ibfk_2,
  DROP FOREIGN KEY best_record_ibfk_3;

-- 2) 기본 키에 파티션 기준 컬럼 포함
ALTER TABLE best_record
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (BestRecordID, RecordDateTime);

-- 3) 연도별 파티션 생성 (테이블 전체 재작성)
ALTER TABLE best_record
PARTITION BY RANGE COLUMNS (RecordDateTime) (
  PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
  PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
  PARTITION p2021 VALUES LESS THAN ('2022-01-01'),
  PARTITION p2022 VALUES LESS THAN ('2023-01-01'),
  PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
  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 pmax  VALUES LESS THAN (MAXVALUE)
);

• 2), 3)은 테이블 전체를 다시 쓰는 작업이라 진행 중 기록 저장이 막힙니다. 120만 행 기준 수 분(예상) 의 서비스 점검 시간이 필요합니다.
• 첫 파티션 연도는 개요 7-2 결과의 가장 오래된 연도로 맞춥니다.

3-3. 방법 B — 새 테이블로 복사 후 교체 (점검 시간 최소화, 작업은 복잡)

1. 파티션이 적용된 best_record_new를 생성 (외래 키 없음, 기본 키 (BestRecordID, RecordDateTime), 안1의 인덱스 포함)
2. 오래된 데이터부터 월 단위로 INSERT INTO best_record_new SELECT * FROM best_record WHERE RecordDateTime >= ? AND RecordDateTime < ? 반복 복사
3. 짧은 점검 시간에 마지막 구간 복사 후 RENAME TABLE best_record TO best_record_old, best_record_new TO best_record;
4. 복사 도중 발생한 기록 수정·삭제(같은 시간대 UPDATE, 관리자 삭제)를 놓치지 않도록 마지막 수 시간 구간은 반드시 점검 시간에 다시 복사
5. 문제 없으면 best_record_old 삭제

3-4. 운영 중 해야 하는 일

-- 매년 말: 새 연도 파티션 추가 (pmax를 쪼갬)
ALTER TABLE best_record REORGANIZE PARTITION pmax INTO (
  PARTITION p2027 VALUES LESS THAN ('2028-01-01'),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

-- 오래된 파티션을 별도 테이블로 분리 (MariaDB 10.7 이상 지원)
ALTER TABLE best_record CONVERT PARTITION p2019 TO TABLE best_record_2019;

-- 또는 영구 삭제
ALTER TABLE best_record DROP PARTITION p2019;

-- 쿼리가 필요한 파티션만 읽는지 확인 (partitions 컬럼)
EXPLAIN PARTITIONS
SELECT PlayerID, MAX(BestRecord) FROM best_record
WHERE MaestroID = 123 AND AppID = 5
  AND RecordDateTime >= '2026-09-01' AND RecordDateTime < '2026-10-01'
GROUP BY PlayerID;

pmax에 데이터가 쌓인 뒤 REORGANIZE하면 그만큼 재작성 비용이 커지므로, 연말 전에 자동으로 다음 해 파티션을 만드는 배치가 필요합니다.

---

4. MariaDB 파티셔닝 제약 (중요)

제약	이 프로젝트에 미치는 영향
파티션된 InnoDB 테이블은 외래 키(FOREIGN KEY)를 가질 수 없음	기존 외래 키 6개 제거 필요. 이후 "없는 플레이어의 기록"이 생겨도 DB가 막아주지 않음
모든 PRIMARY KEY / UNIQUE 키에 파티션 기준 컬럼이 포함되어야 함	기본 키를 (BestRecordID, RecordDateTime)으로 변경. 향후 안2처럼 원본에 UNIQUE 키를 만들 때도 RecordDateTime 포함 필요
프루닝은 파티션 기준 컬럼에 대한 범위/등호 조건이 있을 때만 동작	안1의 범위 조건 변환이 선행되어야 함. DATE(RecordDateTime)=... 형태는 모든 파티션을 읽음
날짜 조건이 없는 쿼리는 모든 파티션을 각각 탐색	기록 저장 후 최고기록 재계산(WHERE MaestroID=? AND PlayerID=? AND AppID=? ORDER BY BestRecord), 학생 삭제(WHERE MaestroID=? AND PlayerID=?)는 파티션 수만큼 인덱스를 탐색 → 파티션이 많으면 오히려 느려질 수 있음
파티션 구조 변경(ALTER ... PARTITION BY, PK 변경)은 테이블 재작성	서비스 점검 시간 필요
파티션 수가 많으면 열린 파일 수·메모리 사용 증가	월 단위보다 연 단위가 이 프로젝트 규모에 적합

---

5. 장단점

장점	단점
오래된 데이터 분리·삭제가 즉시 끝남 (행 단위 DELETE 불필요)	외래 키 제거로 데이터 무결성 보호 약화
날짜 범위 조회 시 해당 파티션만 읽음	기본 키 변경, 테이블 재작성, 서비스 점검 시간 필요
파티션별 인덱스가 작아 캐시 효율이 좋아짐	날짜 조건 없는 쿼리는 오히려 느려질 수 있음
코드 수정은 적음 (안1 적용 전제)	매년 파티션 추가 배치 등 운영 작업이 늘어남
	문제가 생겼을 때 원인 파악과 복구가 어려움 (DB 경험이 필요)

---

6. 현재 보류를 권장하는 이유

1. 규모가 아직 크지 않음: 120만 행은 안1의 복합 인덱스로 "필요한 구간만 읽는" 상태가 되면 파티셔닝의 추가 이득이 작습니다.
2. 안3과 목적이 겹침: 오래된 데이터를 운영 테이블에서 빼는 목적은 안3(아카이빙)으로도 달성할 수 있고, 안3은 외래 키·기본 키를 건드리지 않습니다.
3. 외래 키 제거의 부작용: 학생 삭제 순서가 어긋나거나 코드 버그가 생기면 고아 기록이 쌓여도 DB가 막아주지 않습니다.
4. 저장·삭제 경로가 날짜 조건 없이 동작: 이 서비스의 가장 잦은 쓰기 흐름(기록 저장 → 최고기록 재계산, 학생 삭제)은 PlayerID 기준이라 프루닝 혜택을 받지 못합니다.
5. 운영 부담 대비 경험 수준: 파티션 추가 자동화, 재작성 작업, 장애 시 복구는 DB 운영 경험이 필요한 영역입니다.

---

7. 재검토 기준

아래 중 하나라도 해당하면 파티셔닝을 다시 검토합니다.

기준	확인 방법
안3 적용 후에도 운영 best_record가 1,000만 행 초과 (또는 곧 초과 예상)	개요 7-2의 월별 적재량 × 보관 개월 수
안3 아카이빙 배치의 삭제 작업이 수십 분 이상 걸리거나 서비스 지연을 일으킴	배치 로그, 슬로우 쿼리 로그
기록 테이블 데이터+인덱스 크기가 innodb_buffer_pool_size를 크게 초과해 디스크 읽기가 잦음	개요 7-1
서버 이전·DB 재구성 등으로 어차피 테이블을 재작성해야 하는 시점이 옴	인프라 계획

-- 최근 12개월 월별 증가량으로 1,000만 행 도달 시점 추정
SELECT DATE_FORMAT(RecordDateTime, '%Y-%m') AS ym, COUNT(*) AS cnt
FROM best_record
WHERE RecordDateTime >= CURDATE() - INTERVAL 12 MONTH
GROUP BY ym
ORDER BY ym;

---

8. 만약 적용한다면 순서 (요약)

1. 안1(범위 조건), 안2(집계 테이블) 선행 완료
2. 날짜 조건 없는 쿼리 목록 재점검 → 필요 시 날짜 조건 추가 또는 집계 테이블로 전환
3. 스테이징에서 방법 B로 리허설, 점검 시간 측정
4. 파티션 자동 추가 배치 작성 및 테스트
5. 운영 점검 공지 → 적용 → EXPLAIN PARTITIONS로 프루닝 확인
6. 외래 키 대신 정합성 점검 쿼리를 정기 실행 (예: player에 없는 PlayerID를 가진 기록 수)

-- 고아 기록 점검 (외래 키 제거 후 정기 실행)
SELECT COUNT(*) FROM best_record BR
LEFT JOIN player P ON BR.PlayerID = P.PlayerID
WHERE P.PlayerID IS NULL;

---

9. 체크리스트 (재검토 시)

• 재검토 기준 중 무엇에 해당하는지 수치로 기록
• 안1·안2·안3 적용 상태 확인
• 외래 키 제거에 대한 대체 점검 방안 합의
• 날짜 조건 없는 쿼리 목록과 성능 영향 측정
• 스테이징 리허설 (방법 B), 점검 시간 산정
• 파티션 자동 추가 배치
• 롤백 계획 (best_record_old 보존 기간)