20 KiB
20 KiB
플레이어 기록 DB 성능 개선 방안 — 개요
- 선행 문서: player-record-tables-and-queries.md (기록 테이블·쿼리 현황 조사)
- 대상 DB: 운영 MariaDB 10.11.13
- 작성일: 2026-09-14
- 이 문서는 계획 문서입니다. 소스 코드와 DB는 아직 변경하지 않았습니다.
문서 구성
| 순서 | 문서 | 내용 |
|---|---|---|
| 1 | 이 문서 | 현재 상황, 쉬운 원리 설명, 용어 사전, 5개 안 비교, 추천 로드맵, 사전 측정 방법 |
| 2 | 02-option1-index-and-query-rewrite.md | 안1. 인덱스 추가 + 쿼리 조건 개선 |
| 3 | 03-option2-daily-summary-tables.md | 안2. 일별 최고기록 집계 테이블 도입 |
| 4 | 04-option3-archiving.md | 안3. 오래된 원본 기록 아카이빙 |
| 5 | 05-option4-partitioning.md | 안4. 테이블 파티셔닝 (보류 권장) |
| 6 | 06-option5-application-layer.md | 안5. 애플리케이션(PHP/화면) 개선 |
| 7 | 07-alternative-approaches.md | 다른 방향의 해결책 연구: 학교별 테이블 분할, Redis·MongoDB·Kafka, DB 실행 환경 변경, 폴링·PHP 환경 |
처음이라면 이 문서의 2장(원리) → 4장(비교표) → 6장(로드맵) 순서로 읽고, 실제 작업할 안의 문서를 열어보는 것을 권장합니다.
1. 현재 상황 한눈에 보기
운영 DB에서 측정한 row 수 (2026-09 기준):
| 테이블 | row 수 | 성격 | 비고 |
|---|---|---|---|
best_record |
1,206,768 | 이력(계속 증가) | 가장 큰 개선 대상. COUNT(*)만 0.93초 |
app_highest_record |
228,930 | 스냅샷(플레이어×앱당 1행) | UNIQUE 키가 없어 중복 행 존재 여부 점검 필요 |
typing_exam_record |
127,955 | 이력(계속 증가) | best_record와 같은 구조·같은 문제 |
typing_exam_highest_record |
20,346 | 스냅샷 | |
license_score |
620 | 이력 | 현재 규모로는 문제 없음 (예방 차원만) |
license_time |
1 | 스냅샷 | 사실상 미사용 기능 |
SELECT COUNT(*) FROM best_record가 0.93초 걸렸다는 것은, DB가 120만 행을 처음부터 끝까지 훑었다는 뜻입니다. 현재 랭킹·히스토리·기록 저장 쿼리도 대부분 비슷한 방식으로 동작하므로, 게임이 끝날 때마다, 랭킹 화면을 열 때마다 이 비용이 반복됩니다. 기록은 매시간 쌓이므로 시간이 갈수록 더 느려집니다.
2. 왜 느려지는가 — 쉬운 설명
2-1. 인덱스 = 책 뒤의 "찾아보기(색인)"
- 두꺼운 책에서 "파티셔닝"이라는 단어를 찾을 때, 첫 페이지부터 읽으면 오래 걸립니다(= 전체 스캔, Full Table Scan).
- 책 뒤의 색인에서 "ㅍ" 항목을 찾아 페이지 번호로 바로 가면 빠릅니다(= 인덱스 탐색).
- 현재 기록 테이블에는 "MaestroID 색인", "PlayerID 색인"처럼 한 가지 기준짜리 색인만 있습니다(외래 키를 만들 때 자동 생성된 것).
2-2. 복합 인덱스 = 전화번호부 (성 → 이름 순서)
- 전화번호부는 "성"으로 먼저 정렬하고, 같은 성 안에서 "이름"으로 정렬합니다.
- "김철수"는 빨리 찾지만, "성은 모르고 이름이 철수인 사람"은 전부 뒤져야 합니다.
- 인덱스도 같습니다.
(MaestroID, AppID, RecordDateTime)인덱스는 "이 선생님의 → 이 앱의 → 이 시간대 기록"을 순서대로 좁혀서 바로 찾습니다. 컬럼 순서가 중요합니다. 보통=로 비교하는 컬럼을 앞에, 범위(>=,<)로 비교하는 컬럼을 뒤에 둡니다.
2-3. 컬럼에 함수를 씌우면 색인을 쓸 수 없다
RecordDateTime 인덱스는 2026-09-14 13:25:10 같은 전체 시각 순서로 정렬되어 있습니다.
-- 현재 방식: "시(hour)가 13인 기록"
WHERE DATE(RecordDateTime) = DATE(NOW()) AND HOUR(RecordDateTime) = 13
DB 입장에서는 각 행의 RecordDateTime에 DATE(), HOUR()를 계산해 봐야 조건에 맞는지 알 수 있으므로, 색인이 있어도 모든 행을 확인합니다.
-- 개선 방식: "13:00 이상 14:00 미만"
WHERE RecordDateTime >= '2026-09-14 13:00:00' AND RecordDateTime < '2026-09-14 14:00:00'
이 조건은 색인 안에서 연속된 한 구간이므로 그 구간만 읽습니다. 이렇게 인덱스를 활용할 수 있는 조건을 sargable하다고 부릅니다. 결과는 완전히 같고 방식만 다릅니다.
2-4. 매시간 1행씩 영원히 쌓이는 구조
best_record는 "플레이어 × 앱 × 1시간"마다 1행이 생깁니다. 삭제하는 로직이 사실상 없으므로, 인덱스를 잘 만들어도 히스토리·월간 랭킹처럼 넓은 범위를 묶어 계산(GROUP BY)하는 쿼리는 데이터가 늘수록 결국 느려집니다. 이 문제는 "미리 날짜별로 계산해 둔 작은 테이블(집계 테이블)"과 "오래된 원본 분리(아카이빙)"로 해결합니다.
3. 용어 사전
| 용어 | 뜻 | 이 프로젝트에서의 예 |
|---|---|---|
| 인덱스 (Index) | 원하는 행을 빨리 찾기 위한 정렬된 색인 | best_record의 PlayerID 색인 |
| 복합 인덱스 | 여러 컬럼을 순서대로 묶은 인덱스 | (MaestroID, AppID, RecordDateTime) |
| 커버링 인덱스 | 쿼리에 필요한 컬럼이 전부 인덱스에 들어 있어 원본 행을 읽지 않아도 되는 인덱스 | 랭킹용 인덱스에 PlayerID, BestRecord까지 포함 |
| 전체 스캔 (Full Scan) | 테이블의 모든 행을 처음부터 끝까지 읽음 | 현재 랭킹/히스토리 쿼리 |
| sargable | 인덱스를 활용할 수 있는 형태의 조건 | RecordDateTime >= ? AND RecordDateTime < ? |
| EXPLAIN | 쿼리를 실행하지 않고 "어떻게 실행할지" 계획을 보여주는 명령 | EXPLAIN SELECT ... |
| ANALYZE | 쿼리를 실제로 실행하고 계획과 실제 소요를 함께 보여줌 | ANALYZE FORMAT=JSON SELECT ... |
| 집계(요약) 테이블 | 원본을 미리 묶어 계산해 둔 작은 테이블 | 안2의 daily_best_record (플레이어×앱×날짜당 1행) |
| UPSERT | "없으면 INSERT, 있으면 UPDATE"를 쿼리 한 번으로 처리 | INSERT ... ON DUPLICATE KEY UPDATE |
| UNIQUE 키 | 같은 값 조합이 두 번 들어가지 못하게 막는 인덱스 | app_highest_record (MaestroID, PlayerID, AppID) |
| 아카이빙 | 오래된 데이터를 별도 보관 테이블로 옮겨 운영 테이블을 작게 유지 | best_record_archive |
| 파티셔닝 | 한 테이블을 내부적으로 연/월 단위 "서랍"으로 나누어 저장 | RecordDateTime 연도별 파티션 |
| 온라인 DDL | 서비스 중에도 테이블 구조(인덱스 등)를 변경하는 방식 | ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE |
| N+1 쿼리 | 목록 1번 조회 후 항목마다 쿼리를 1번씩 더 실행하는 비효율 패턴 | 메뉴 화면의 앱별 최고기록 조회 |
| 슬로우 쿼리 로그 | 기준 시간보다 오래 걸린 쿼리를 기록하는 DB 기능 | long_query_time = 1 |
4. 5개 개선안 요약 비교
| 안1. 인덱스 + 쿼리 조건 | 안2. 일별 집계 테이블 | 안3. 원본 아카이빙 | 안4. 파티셔닝 | 안5. 애플리케이션 개선 | |
|---|---|---|---|---|---|
| 핵심 아이디어 | 알맞은 색인을 만들고, 색인을 쓸 수 있게 조건을 바꾼다 | 날짜별 최고기록을 미리 계산해 두고 거기서 조회한다 | 오래된 원본을 보관 테이블로 옮긴다 | 테이블을 연/월 서랍으로 나눈다 | PHP 코드의 비효율(N+1, 이중 조회, 문자열 SQL)을 고친다 |
| 효과 | 즉시 큼 | 장기적으로 매우 큼 | 장기 용량 관리 | 장기 용량 관리 | 중간 (특정 화면) |
| 난이도 | 하 | 중 | 중 | 상 | 하~중 |
| 위험도 | 낮음 | 중간 | 중간 | 높음 | 낮음 |
| 예상 작업량 | 1~2일 | 3~5일 | 2~3일 | 3~5일 + 서비스 점검 시간 | 2~4일 |
| DB 구조 변경 | 인덱스 추가, UNIQUE 키 | 새 테이블 2개 | 보관 테이블 2개 + 배치 | PK 변경, FK 제거, 테이블 재구성 | 없음 |
| 코드 수정 범위 | 쿼리 조건 (약 7개 파일) | 기록 저장·삭제·조회 경로 | 관리자 기록 목록, 삭제 경로 | 적음 (쿼리 조건은 안1 필요) | 메뉴·기록 목록 API·화면 |
| 기존 데이터 변경 | 최고기록 중복 행 정리만 | 없음 (새 테이블에 복사) | 원본 행 이동 | 테이블 재구성 | 없음 |
| 추천 | 1단계 (필수) | 2단계 | 3단계 | 보류 | 병행 |
각 안은 서로 배타적이지 않습니다. 안1 → 안2 → 안3은 순서대로 쌓아 올리는 구조이고, 안5는 언제든 병행할 수 있으며, 안4는 안3과 목적이 겹쳐 현재는 권장하지 않습니다.
5. 문제점 × 개선안 매트릭스
선행 문서 4장의 문제 1~7이 각 안으로 얼마나 해결되는지 정리했습니다. (◎ 근본 해결 / ○ 상당 부분 개선 / △ 일부 도움 / - 무관)
| # | 문제점 | 안1 | 안2 | 안3 | 안4 | 안5 |
|---|---|---|---|---|---|---|
| 1 | 날짜 컬럼에 함수를 씌운 조건 (인덱스 무력화) | ◎ | ◎ | - | △ (안1이 선행되어야 효과) | - |
| 2 | 복합 인덱스 부재 | ◎ | ○ (새 테이블은 처음부터 인덱스 설계) | - | △ | - |
| 3 | 이력 테이블 무한 증가 | - | ○ (조회가 원본에 의존하지 않게 됨) | ◎ | ◎ | - |
| 4 | 기록 저장 시 반복되는 조회+쓰기 비용, 최고기록 delete→insert | ○ | ◎ (UPSERT) | △ | - | - |
| 5 | 메뉴 화면 N+1 쿼리 | - | - | - | - | ◎ |
| 6 | 관리자 기록 목록: COUNT+목록 이중 조회, LIKE '%…%', 문자열 SQL |
○ (날짜 인덱스) | - | ○ (조회 대상 축소) | △ | ◎ |
| 7 | 미사용 ranking 테이블 |
- | - | - | - | ○ (재활용 또는 삭제 결정) |
6. 추천 조합과 단계별 로드맵
| 단계 | 내용 | 목표 / 완료 조건 | 참고 문서 |
|---|---|---|---|
| 0단계. 측정 (반나절) | 테이블 크기, 대표 쿼리 EXPLAIN 기준값, 슬로우 쿼리 로그, 최고기록 중복 점검 | 개선 전 수치를 표로 남김 (7장) | 이 문서 7장 |
| 1단계. 안1 | 인덱스 추가 → 쿼리 조건 sargable 변환 → 월간 랭킹 버그 수정 | 랭킹·기록 저장 쿼리 EXPLAIN에서 type=ALL(전체 스캔)이 사라짐 |
안1 |
| 1단계 병행. 안5 일부 | 기록 목록 API의 SQL Injection 제거 (보안 이슈라 우선) | 모든 조건이 ? 바인딩 |
안5 |
| 2단계. 안2 | 일별 집계 테이블 생성 → 저장 로직에 반영 → 과거 데이터 채우기 → 조회 전환 | 히스토리/일간·월간 랭킹이 집계 테이블에서 조회됨, 기존 결과와 동일 | 안2 |
| 3단계. 안3 | 보관 기간 결정 → 아카이브 테이블·배치 도입 | 운영 best_record가 보관 기간 이내 데이터만 유지, "최근 7일 히스토리"는 계속 정상 |
안3 |
| 수시. 안5 나머지 | N+1 제거, 기록 목록 페이징, 랭킹 캐시, 중복 코드 정리 | 메뉴 화면 쿼리 수가 앱 개수와 무관해짐 | 안5 |
| 보류. 안4 | 재검토 조건 충족 시에만 검토 | 원본 1,000만 행 초과 등 | 안4 |
왜 이 순서인가
- 안1은 기존 데이터를 거의 건드리지 않고, 인덱스는 추가해도 기존 코드가 그대로 동작하므로 가장 안전하게 큰 효과를 얻습니다.
- 안2는 "몇 년 전 기록이라도 플레이어가 마지막으로 플레이한 7일은 보여준다"는 요구사항을 원본 테이블 없이도 만족시키는 장치입니다. 따라서 안3(원본 분리)보다 반드시 먼저 해야 합니다.
- 안3은 안2가 끝나야 안전하게 오래된 원본을 옮길 수 있습니다.
안2를 건너뛰는 경로도 있습니다
- 학생들이 하루 한 수업(한 시간)만 플레이하는 경우가 많으면, 집계 테이블 행 수가 원본과 크게 다르지 않을 수 있습니다. 이때 안2의 가치는 속도보다 "원본 없이도 히스토리가 동작하는 구조"입니다.
- 안1 후 히스토리·월간 랭킹이 충분히 빠르다면, 안2 없이 안3의 "대안 B(히스토리를 아카이브 테이블에서 보충 조회)" 로 요구사항을 만족시키는 더 단순한 경로를 택할 수 있습니다.
- 판단 기준과 측정 방법: 안2 3장, 두 경로 비교: 안3 3-2
7. 사전 측정 절차 (0단계)
운영 DB에서 무거운 쿼리를 반복 실행하면 서비스에 영향이 갈 수 있으므로, 가능하면 최신 백업을 스테이징 DB(
mariadb.jisangs.com)에 복원해서 측정하세요. 운영에서 실행할 때는 사용량이 적은 시간대에 실행하세요.
7-1. 테이블·인덱스 크기
SELECT TABLE_NAME,
TABLE_ROWS AS approx_rows,
ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'chocomae'
ORDER BY DATA_LENGTH DESC;
TABLE_ROWS는 추정치입니다(정확한 값은COUNT(*)).- 함께 확인:
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;— 데이터+인덱스 크기가 이 값보다 훨씬 크면 디스크 읽기가 많아져 느려집니다.
7-2. 월별 적재량 (증가 속도 파악)
SELECT DATE_FORMAT(RecordDateTime, '%Y-%m') AS ym, COUNT(*) AS cnt
FROM best_record
GROUP BY ym
ORDER BY ym;
이 결과로 "보관 기간을 13개월로 하면 운영 테이블에 몇 행이 남는지"(안3), "1,000만 행에 언제 도달하는지"(안4 재검토 시점)를 계산할 수 있습니다.
7-3. 대표 쿼리 EXPLAIN 기준값
- 기록이 많은 선생님·앱 조합을 찾습니다.
SELECT MaestroID, AppID, COUNT(*) AS cnt
FROM best_record
WHERE RecordDateTime >= NOW() - INTERVAL 30 DAY
GROUP BY MaestroID, AppID
ORDER BY cnt DESC
LIMIT 5;
- 그 값으로 현재 형태의 일간 랭킹 쿼리 계획을 확인합니다. (
123,5는 위 결과로 교체)
EXPLAIN
SELECT BR.PlayerID, U.Name, MAX(BR.BestRecord) AS HighScore
FROM best_record BR, player U
WHERE BR.MaestroID = 123 AND BR.PlayerID = U.PlayerID
AND YEAR(BR.RecordDateTime) = YEAR('2026-09-10')
AND MONTH(BR.RecordDateTime) = MONTH('2026-09-10')
AND DAYOFMONTH(BR.RecordDateTime) = DAYOFMONTH('2026-09-10')
AND BR.AppID = 5
GROUP BY BR.PlayerID
ORDER BY MAX(BR.BestRecord) DESC;
- EXPLAIN 결과 읽는 법
| 컬럼 | 볼 것 | 좋은 값 / 나쁜 값 |
|---|---|---|
type |
어떻게 찾는가 | ref, range, eq_ref 좋음 / ALL(전체 스캔) 나쁨 |
key |
사용한 인덱스 | 기대한 인덱스 이름이 보이면 좋음 / NULL이면 인덱스 미사용 |
rows |
읽을 것으로 예상하는 행 수 | 작을수록 좋음 (120만 근처면 전체 스캔) |
Extra |
추가 작업 | Using index(커버링) 좋음 / Using temporary; Using filesort는 대상 행이 많을 때 부담 |
- 실제 소요 시간까지 보려면
EXPLAIN대신ANALYZE FORMAT=JSON을 사용합니다. 이 명령은 쿼리를 실제로 실행하므로 SELECT에만 사용하세요. 결과의r_total_time_ms가 실제 소요 시간(ms)입니다.
7-4. 슬로우 쿼리 로그 켜기 (일시적)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- 1초 이상 걸린 쿼리 기록
SET GLOBAL log_output = 'TABLE'; -- mysql.slow_log 테이블에 기록
-- 하루 정도 서비스 후 확인
SELECT start_time, query_time, rows_examined, LEFT(sql_text, 200) AS sql_head
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 30;
-- 측정이 끝나면 끄기
SET GLOBAL slow_query_log = 0;
SET GLOBAL은 DB 컨테이너가 재시작되면 초기화됩니다. 계속 켜 두려면 Docker의 MariaDB 설정 파일(my.cnf)에 추가해야 합니다.
7-5. 최고기록 테이블 중복 점검
-- 같은 (선생님, 플레이어, 앱) 조합이 2행 이상인 경우
SELECT MaestroID, PlayerID, AppID, COUNT(*) AS cnt
FROM app_highest_record
GROUP BY MaestroID, PlayerID, AppID
HAVING cnt > 1
ORDER BY cnt DESC
LIMIT 50;
SELECT MaestroID, PlayerID, WritingID, COUNT(*) AS cnt
FROM typing_exam_highest_record
GROUP BY MaestroID, PlayerID, WritingID
HAVING cnt > 1
ORDER BY cnt DESC
LIMIT 50;
결과가 있으면 안1의 UNIQUE 키 추가 전에 정리가 필요합니다(안1 문서 3-2 참고).
7-6. 측정 결과 기록 양식
| 측정 항목 | 개선 전 | 안1 후 | 안2 후 | 안3 후 |
|---|---|---|---|---|
best_record data_mb / index_mb |
||||
일간 랭킹 쿼리 rows / 실제 ms |
||||
시간 랭킹 쿼리 rows / 실제 ms |
||||
히스토리(최근 7일) 쿼리 rows / 실제 ms |
||||
기록 저장 시 중복 확인 쿼리 rows / 실제 ms |
||||
| 관리자 기록 목록(50건) 실제 ms | ||||
| 1초 이상 슬로우 쿼리 수 (1일) | ||||
| 일일 백업 파일 크기 |
8. 조사 중 발견한 버그·위험 (개선 작업 때 함께 수정 권장)
| 우선순위 | 파일 | 내용 | 영향 | 조치 |
|---|---|---|---|---|
| 높음 (보안) | request_app_player_record_list.php, request_writing_player_record_list.php, request_license_timer_player_record_list.php | 시작일·종료일·학생 이름·AppID를 SQL 문자열에 그대로 이어붙임 | 요청 값을 조작하면 다른 선생님의 기록 조회 등 SQL Injection 가능 | 안5: 전부 ? 바인딩으로 변경 |
| 높음 (보안) | history_record.php | AppID를 쿼리 문자열에 직접 연결 |
위와 동일 | 안1 쿼리 수정 시 바인딩으로 변경 |
| 중간 (정확성) | app_ranking.php get_ranking_month() |
MONTH()만 비교하고 연도(YEAR)를 비교하지 않음 |
작년·재작년 같은 달 기록까지 이달의 랭킹에 섞임 | 안1: 월 범위 조건으로 변경하며 자연히 해결 |
| 중간 (정합성) | app_highest_record, typing_exam_highest_record |
UNIQUE 키 없이 "삭제 후 삽입"으로 갱신 | 동시에 기록이 저장되면 같은 조합이 2행 이상 생길 수 있음 | 안1: 중복 정리 + UNIQUE 키 / 안2: UPSERT |
| 낮음 (버그) | typing_exam_collection.php getHighestRecordArrayForAllWriting() |
bind_param("iii", …)에 값은 2개만 전달 |
이 함수 호출 시 오류 | 호출처 확인 후 "ii"로 수정 또는 미사용이면 삭제 |
| 낮음 (코드) | record/*.php 여러 파일 |
if($replyJSON.length === 0)는 PHP에서 "문자열 연결"로 해석되어 항상 거짓 |
빈 결과 시 에러 응답 분기가 동작하지 않음 (현재는 빈 배열로 응답되어 큰 문제는 없음) | 필요 시 count($replyJSON) === 0으로 정리 |
9. 적용 시 공통 안전 수칙
- 백업 먼저: 작업 직전에 backup-db.sh를 수동 실행하고 백업 파일 크기를 확인합니다.
- 스테이징 먼저: 최신 백업을 스테이징 DB에 복원해 같은 작업을 먼저 해보고, 소요 시간과 결과를 기록합니다.
- 사용량이 적은 시간대: 운영 DB 구조 변경은 새벽에 진행합니다.
- 한 번에 하나씩: 인덱스 추가 → 확인 → 코드 배포 → 확인 순서로, 여러 변경을 한꺼번에 하지 않습니다.
- 롤백 SQL을 미리 준비: 각 안 문서의 "롤백" 절을 작업 전에 복사해 둡니다.
- 스키마 변경 이력 남기기: 운영 DB에 적용한 SQL은
src/web/sql/migration/YYMMDD_설명.sql같은 파일로 저장소에 남기고, 신규 설치용 make_db.sql에도 반영해 운영 DB와 스키마 파일이 달라지지 않게 합니다.