26 KiB
26 KiB
안2. 일별 최고기록 집계 테이블 도입
개요 문서 | 추천 단계: 2단계 | 난이도: 중 | 위험도: 중간 | 예상 작업량: 3~5일 전제: 안1 완료 (특히 최고기록 테이블 UNIQUE 키)
한 줄 요약
"플레이어 × 앱(글) × 날짜"당 최고기록 1행만 담는 작은 집계 테이블을 만들어 기록 저장·삭제 때 함께 갱신하고, 히스토리(최근 7일)·일간·월간 랭킹은 이 테이블에서 조회한다. 원본을 옮기거나 지워도(안3) 히스토리 기능이 계속 동작하게 하는 기반이다.
1. 해결하는 문제
| 문제 (선행 문서 번호) | 해결 정도 | 설명 |
|---|---|---|
| 1. 날짜 함수로 묶어 계산하는 쿼리 | ◎ | 히스토리·일간·월간 랭킹에서 GROUP BY DATE(...) 자체가 사라짐 |
| 3. 이력 테이블 무한 증가 | ○ | 조회 기능이 원본에 의존하지 않게 되어 안3(아카이빙)이 가능해짐 |
| 4. 저장 시 조회+쓰기 비용, 최고기록 delete→insert | ◎ | 최고기록을 UPSERT 1회로 처리 |
| 요구사항: 몇 년 전 기록이라도 "마지막으로 플레이한 7일" 히스토리 유지 | ◎ | 집계 테이블은 아카이빙 대상이 아니므로 영구 보존 |
| (정합성) 기록 삭제 후 최고기록 재계산이 원본 전체를 읽음 | ◎ | 집계 테이블에서 재계산 → 원본을 아카이빙해도 최고기록이 틀어지지 않음 |
2. 쉬운 설명
- 원본
best_record는 영수증 묶음입니다. 매시간 1장씩 쌓입니다. - 히스토리 화면은 "날짜별 최고 점수 7개"만 필요한데, 지금은 볼 때마다 영수증 묶음 전체를 날짜별로 분류해서 계산합니다.
- 안2는 날짜별 요약 장부(
daily_best_record)를 따로 두고, 영수증이 들어올 때마다 장부의 그날 칸을 고쳐 적습니다. - 히스토리는 장부에서 최근 7칸만 읽으면 끝납니다. 오래된 영수증을 창고로 옮겨도(안3) 장부는 남아 있으므로 히스토리는 그대로 보입니다.
3. 안2를 꼭 해야 하는가? (먼저 판단하기)
안2는 쓰기 경로를 여러 곳 고쳐야 하므로 안1보다 손이 많이 갑니다. 아래 기준으로 판단하세요.
3-1. 측정
-- 집계 테이블이 만들어질 경우의 행 수 (원본 1,206,768행과 비교)
SELECT COUNT(*) AS daily_rows
FROM (
SELECT 1
FROM best_record
GROUP BY PlayerID, AppID, DATE(RecordDateTime)
) t;
- 학생들이 하루에 한 시간(한 수업)만 플레이하는 경우가 많다면
daily_rows가 원본과 크게 차이 나지 않을 수 있습니다(예상). 이 경우 안2의 가치는 "행 수 감소"가 아니라 "원본 없이도 히스토리·랭킹·최고기록 재계산이 가능해지는 구조" 입니다. - 안1 적용 후 히스토리·월간 랭킹 쿼리를
ANALYZE로 측정해 보세요.
3-2. 판단 기준
| 상황 | 권장 |
|---|---|
| 안3(원본 아카이빙)을 할 계획이다 | 안2 진행 (또는 안3 문서 3-2의 대안 B 검토) |
| 안1 후 히스토리·월간 랭킹이 충분히 빠르고(예: 수십 ms), 아카이빙 계획도 없다 | 안2 보류, 1년 뒤 재측정 |
| 안1 후에도 월간 랭킹·히스토리가 느리다(예: 수백 ms 이상) | 안2 진행 |
4. 변경 내용
4-1. 새 테이블
CREATE TABLE daily_best_record (
DailyBestRecordID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
MaestroID INT UNSIGNED NOT NULL,
PlayerID INT UNSIGNED NOT NULL,
AppID INT UNSIGNED NOT NULL,
RecordDate DATE NOT NULL,
BestRecord FLOAT NOT NULL, -- 그날 최고기록 (앱 105는 최저값)
BestRecordDateTime DATETIME NOT NULL, -- 그 최고기록이 저장된 시각
UpdatedDateTime DATETIME NOT NULL,
UNIQUE KEY uk_dbr_player_app_date (PlayerID, AppID, RecordDate),
KEY idx_dbr_maestro_app_date (MaestroID, AppID, RecordDate, PlayerID, BestRecord),
FOREIGN KEY (MaestroID) REFERENCES maestro(MaestroID),
FOREIGN KEY (AppID) REFERENCES app(AppID),
FOREIGN KEY (PlayerID) REFERENCES player(PlayerID)
);
CREATE TABLE daily_typing_exam_record (
DailyTypingExamRecordID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
MaestroID INT UNSIGNED NOT NULL,
PlayerID INT UNSIGNED NOT NULL,
WritingID INT UNSIGNED NOT NULL,
RecordDate DATE NOT NULL,
MaxPlusRecord FLOAT NULL, -- 그날 0 이상 기록 중 최댓값 (없으면 NULL)
MaxPlusRecordDateTime DATETIME NULL,
MaxMinusRecord FLOAT NULL, -- 그날 0 미만 기록 중 최댓값 (없으면 NULL)
UpdatedDateTime DATETIME NOT NULL,
UNIQUE KEY uk_dter_player_writing_date (PlayerID, WritingID, RecordDate),
KEY idx_dter_maestro_writing_date (MaestroID, WritingID, RecordDate, PlayerID),
FOREIGN KEY (MaestroID) REFERENCES maestro(MaestroID),
FOREIGN KEY (PlayerID) REFERENCES player(PlayerID),
FOREIGN KEY (WritingID) REFERENCES writing(WritingID)
);
설계 이유
| 결정 | 이유 |
|---|---|
UNIQUE 키가 PlayerID로 시작 |
플레이어는 한 선생님에게만 속함. 저장·삭제·히스토리가 모두 플레이어 기준이라 이 키 하나로 처리 |
idx_dbr_maestro_app_date에 PlayerID, BestRecord 포함 |
일간·월간 랭킹을 인덱스만으로 계산 (커버링) |
긴글 시험은 MaxPlusRecord, MaxMinusRecord 두 컬럼 |
현재 랭킹 쿼리가 Record >= 0(플러스 랭킹)과 Record < 0(마이너스 랭킹)을 각각 MAX() 로 계산하므로, 두 값을 따로 저장해야 결과가 똑같이 나옴. 히스토리는 MAX(Record) 전체이므로 COALESCE(MaxPlusRecord, MaxMinusRecord)와 같음 |
BestRecordDateTime, MaxPlusRecordDateTime |
기록 삭제 후 app_highest_record/typing_exam_highest_record의 RecordDateTime까지 재계산하기 위함 |
| 외래 키 포함 | 기존 테이블과 같은 규칙. 학생 삭제 시 player보다 먼저 지워야 함(4-5) |
4-2. 집계 행 갱신 방식: "그날 원본에서 다시 계산"
집계 행을 갱신하는 방법은 두 가지가 있습니다.
| 방식 | 내용 | 장점 | 단점 |
|---|---|---|---|
| A. 증분 갱신 | 새 기록이 들어올 때 GREATEST(기존값, 새값)으로 덮어씀 |
원본을 읽지 않음 | 기록 삭제 시에는 쓸 수 없음. 긴글 시험처럼 원본 행이 마이너스→플러스로 바뀌는 경우 원본과 어긋날 수 있음 |
| B. 재계산 (권장) | 저장·삭제 후 그날 그 플레이어·앱의 원본(최대 24행) 을 읽어 집계 행을 다시 씀 | 저장·삭제·과거 데이터 채우기에 같은 함수 사용, 원본과 항상 일치 | 원본을 조금 읽음 (안1 인덱스로 최대 24행이라 부담 없음) |
권장 방식 B의 공용 함수 (신규 파일 src/web/server/lib/daily_record.php):
<?php
include_once __DIR__ . "/util_app.php";
// 해당 날짜의 best_record 원본으로 daily_best_record 1행을 다시 계산한다.
function refresh_daily_best_record($db_conn, $maestro_id, $player_id, $app_id, $date) {
$order = is_highest_record_prefer_app($app_id) ? "DESC" : "ASC";
$query = "
SELECT BestRecord, RecordDateTime
FROM best_record
WHERE PlayerID = ? AND AppID = ? AND MaestroID = ?
AND RecordDateTime >= DATE(?) AND RecordDateTime < DATE(?) + INTERVAL 1 DAY
ORDER BY BestRecord " . $order . ", RecordDateTime ASC
LIMIT 1";
$stmt = $db_conn->prepare($query);
$stmt->bind_param("iiiss", $player_id, $app_id, $maestro_id, $date, $date);
$stmt->execute();
$best_record = null;
$best_date_time = null;
$stmt->bind_result($best_record, $best_date_time);
$found = $stmt->fetch();
$stmt->close();
if (!$found) {
$stmt = $db_conn->prepare("
DELETE FROM daily_best_record
WHERE PlayerID = ? AND AppID = ? AND RecordDate = DATE(?)");
$stmt->bind_param("iis", $player_id, $app_id, $date);
$stmt->execute();
$stmt->close();
return;
}
$stmt = $db_conn->prepare("
INSERT INTO daily_best_record
(MaestroID, PlayerID, AppID, RecordDate, BestRecord, BestRecordDateTime, UpdatedDateTime)
VALUES (?, ?, ?, DATE(?), ?, ?, NOW())
ON DUPLICATE KEY UPDATE
BestRecord = VALUES(BestRecord),
BestRecordDateTime = VALUES(BestRecordDateTime),
UpdatedDateTime = NOW()");
$stmt->bind_param("iiisds", $maestro_id, $player_id, $app_id, $date, $best_record, $best_date_time);
$stmt->execute();
$stmt->close();
}
?>
긴글 시험용 refresh_daily_typing_exam_record($db_conn, $maestro_id, $player_id, $writing_id, $date)도 같은 구조로 작성합니다. 원본 조회 부분만 다음과 같습니다.
SELECT
MAX(IF(Record >= 0, Record, NULL)) AS MaxPlusRecord,
MAX(IF(Record < 0, Record, NULL)) AS MaxMinusRecord,
COUNT(*) AS cnt
FROM typing_exam_record
WHERE PlayerID = ? AND WritingID = ? AND MaestroID = ?
AND RecordDateTime >= DATE(?) AND RecordDateTime < DATE(?) + INTERVAL 1 DAY;
-- MaxPlusRecordDateTime은 Record >= 0 인 행 중 ORDER BY Record DESC, RecordDateTime ASC LIMIT 1 로 별도 조회
-- cnt = 0 이면 집계 행 DELETE
ORDER BY BestRecord뒤에 붙는$order는 코드에서"DESC"/"ASC"두 값 중 하나로만 정해지므로 문자열 연결이어도 안전합니다. 사용자 입력은 모두?로 바인딩합니다.
4-3. 최고기록 테이블 UPSERT로 교체
lib/app_highest_record.php의 "조회 → 삭제 → 삽입" 3단계를 쿼리 1개로 바꿉니다. (안1의 UNIQUE 키 uk_ahr_maestro_player_app 필요)
-- 높을수록 좋은 앱 (앱 105 제외 전부)
INSERT INTO app_highest_record (MaestroID, PlayerID, AppID, HighestRecord, RecordDateTime)
VALUES (?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
RecordDateTime = IF(VALUES(HighestRecord) > HighestRecord, VALUES(RecordDateTime), RecordDateTime),
HighestRecord = GREATEST(HighestRecord, VALUES(HighestRecord));
-- 낮을수록 좋은 앱 (앱 105)
INSERT INTO app_highest_record (MaestroID, PlayerID, AppID, HighestRecord, RecordDateTime)
VALUES (?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
RecordDateTime = IF(VALUES(HighestRecord) < HighestRecord, VALUES(RecordDateTime), RecordDateTime),
HighestRecord = LEAST(HighestRecord, VALUES(HighestRecord));
⚠️
RecordDateTime을HighestRecord보다 먼저 적어야 합니다. MariaDB는UPDATE절을 왼쪽부터 차례로 적용하므로,HighestRecord를 먼저 바꾸면 뒤의 비교가 이미 바뀐 값과 비교하게 되어RecordDateTime이 갱신되지 않습니다.
typing_exam_highest_record도 같은 방식입니다(0 이상 기록일 때만, 높을수록 좋음). typing_exam_collection.php의 getHighestRecord() → addHighestRecord()/updateHighestRecord() 흐름을 UPSERT 1회로 교체합니다.
4-4. 기록 저장 흐름 변경
| 흐름 | 파일 | 변경 전 | 변경 후 |
|---|---|---|---|
| 일반 앱 게임 종료 | server/record/update_result_record.php | ① 이번 시간 best_record 조회 → INSERT/UPDATE ② app_highest_record 조회 → DELETE → INSERT |
① 그대로(안1 쿼리) ② refresh_daily_best_record(..., 오늘) ③ app_highest_record UPSERT |
| 긴글 시험 종료 | php/record/update_typing_exam_record.php | 이번 시간 기록 조회 → INSERT/UPDATE, 최고기록 조회 → INSERT/UPDATE | 기록 저장 후 refresh_daily_typing_exam_record(..., 오늘), 최고기록 UPSERT. TDD용 isPrevHourFlag 경로는 1시간 전 날짜로 갱신 |
- 오늘 날짜는 PHP가 아니라 DB 기준으로 맞춥니다. 함수에
$date대신"NOW()"를 넘길 수 없으므로, 저장 직후SELECT CURDATE()로 받은 값을 넘기거나, 함수 안에서DATE(?)대신CURDATE()를 쓰는 오늘 전용 버전을 둡니다. - 한 요청 안의 여러 쿼리를 트랜잭션으로 묶으면 중간 실패 시 원본과 집계가 어긋나지 않습니다.
$db_conn->begin_transaction();
// ... best_record 저장, refresh_daily_best_record, app_highest_record UPSERT ...
$db_conn->commit();
4-5. 삭제 흐름 변경
| 흐름 | 파일 | 추가할 처리 |
|---|---|---|
| 개별 기록 삭제 (일반 앱) | record/delete_record.php | get_best_record_info()에서 RecordDateTime도 조회 → 원본 삭제 → refresh_daily_best_record(그 날짜) → 최고기록 재계산을 원본 대신 집계 테이블에서 수행 (아래 SQL) |
| 개별 기록 삭제 (긴글 시험) | 〃 | 위와 같은 방식, daily_typing_exam_record.MaxPlusRecord 기준 |
| 학생 삭제 | player/delete_player.php | DELETE FROM daily_best_record WHERE MaestroID=? AND PlayerID=?, daily_typing_exam_record도 동일. player 삭제보다 먼저 실행(외래 키) |
| 테스트 계정 기록 초기화 | maestro/delete_test_player_record.php | DELETE FROM daily_* WHERE PlayerID=? 추가 |
-- delete_record.php: 최고기록 재계산 (현재 get_best_record_record_info()의 대체)
SELECT BestRecord, BestRecordDateTime
FROM daily_best_record
WHERE PlayerID = ? AND AppID = ? AND MaestroID = ?
ORDER BY BestRecord DESC -- 앱 105는 ASC
LIMIT 1;
-- 긴글 시험 (현재 get_typing_exam_best_record_info()의 대체)
SELECT MaxPlusRecord, MaxPlusRecordDateTime
FROM daily_typing_exam_record
WHERE PlayerID = ? AND WritingID = ? AND MaestroID = ? AND MaxPlusRecord > 0
ORDER BY MaxPlusRecord DESC
LIMIT 1;
이 변경이 중요한 이유: 안3으로 오래된 원본을 옮긴 뒤에도 원본 기준으로 재계산하면, 옮겨진 기간의 최고기록이 무시되어 최고기록이 낮아지는 오류가 생깁니다. 집계 테이블은 전체 기간을 갖고 있으므로 안전합니다.
4-6. 조회 전환
히스토리 (최근 7일) — history_record.php
SELECT D.RecordDate AS Date, D.BestRecord AS HighScore, A.AppName AS AppName
FROM daily_best_record D
INNER JOIN app A ON A.AppID = D.AppID
WHERE D.PlayerID = ? AND D.AppID = ? AND D.MaestroID = ?
AND D.RecordDate <= ?
ORDER BY D.RecordDate DESC
LIMIT 7;
-- bind_param('iiis', $player_id, $app_id, $maestro_id, $date)
GROUP BY가 없고, UNIQUE 키에서 최근 날짜부터 7행만 읽고 멈춥니다. 기록이 몇 년 전이어도 같은 속도입니다.- 앱 105의 MIN/MAX 구분은 집계할 때 이미 반영되어 있어 조회 쿼리는 하나로 충분합니다.
긴글 시험 — typing_exam_collection.php getHistoryRecord()
SELECT RecordDate, COALESCE(MaxPlusRecord, MaxMinusRecord) AS HighScore
FROM daily_typing_exam_record
WHERE PlayerID = ? AND WritingID = ? AND MaestroID = ?
AND RecordDate <= ?
ORDER BY RecordDate DESC
LIMIT 7;
일간 랭킹 — ranking_record_day.php, app_ranking.php get_ranking_day()
SELECT D.PlayerID AS PlayerID, U.Name AS Name, D.BestRecord AS HighScore
FROM daily_best_record D
INNER JOIN player U ON U.PlayerID = D.PlayerID
WHERE D.MaestroID = ? AND D.AppID = ? AND D.RecordDate = DATE(?) -- app_ranking.php는 CURDATE()
ORDER BY D.BestRecord DESC; -- 앱 105는 ASC
플레이어당 하루 1행이므로 GROUP BY가 필요 없습니다.
월간 랭킹 — ranking_record_month.php, get_ranking_month()
SELECT D.PlayerID AS PlayerID, U.Name AS Name, MAX(D.BestRecord) AS HighScore -- 앱 105는 MIN
FROM daily_best_record D
INNER JOIN player U ON U.PlayerID = D.PlayerID
WHERE D.MaestroID = ? AND D.AppID = ?
AND D.RecordDate >= CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE)
AND D.RecordDate < CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
GROUP BY D.PlayerID
ORDER BY MAX(D.BestRecord) DESC; -- 앱 105는 MIN ... ASC
긴글 시험 일간·월간 랭킹 — getRankingRecordDay/Month(), getRankingMinusRecordDay/Month()
-- 플러스 랭킹 (월간 예시)
SELECT D.PlayerID, U.Name, MAX(D.MaxPlusRecord) AS HighScore
FROM daily_typing_exam_record D
INNER JOIN player U ON U.PlayerID = D.PlayerID
WHERE D.MaestroID = ? AND D.WritingID = ?
AND D.RecordDate >= CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE)
AND D.RecordDate < CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
AND D.MaxPlusRecord IS NOT NULL
GROUP BY D.PlayerID
ORDER BY MAX(D.MaxPlusRecord) DESC;
-- 마이너스 랭킹: MaxPlusRecord → MaxMinusRecord, ORDER BY ... ASC
원본에 남는 조회
| 조회 | 이유 |
|---|---|
시간 랭킹 (ranking_record_hour.php, get_ranking_hour(), getRanking*Hour()) |
1시간 단위 정보는 집계 테이블에 없음. 안1 인덱스로 1시간 구간만 읽으므로 충분히 빠름 |
관리자 기록 목록 (request_*_player_record_list.php) |
개별 기록(시각 포함)을 보여주고 삭제하는 화면이므로 원본 필요 → 안3·안5에서 처리 |
5. 과거 데이터 채우기 (backfill)
집계 테이블은 비어 있는 상태로 만들어지므로 기존 120만 행을 한 번 옮겨 계산해야 합니다. MariaDB 10.11의 윈도우 함수(ROW_NUMBER())로 "그날 가장 좋은 기록 1행"을 고릅니다.
-- 월 단위로 나누어 실행 (한 번에 전체를 하면 원본에 오래 잠금이 걸릴 수 있음)
SET @from = '2019-01-01', @to = '2019-02-01';
INSERT INTO daily_best_record
(MaestroID, PlayerID, AppID, RecordDate, BestRecord, BestRecordDateTime, UpdatedDateTime)
SELECT MaestroID, PlayerID, AppID, RecordDate, BestRecord, RecordDateTime, NOW()
FROM (
SELECT MaestroID, PlayerID, AppID,
DATE(RecordDateTime) AS RecordDate,
BestRecord, RecordDateTime,
ROW_NUMBER() OVER (
PARTITION BY PlayerID, AppID, DATE(RecordDateTime)
ORDER BY IF(AppID = 105, BestRecord, -BestRecord), RecordDateTime
) AS rn
FROM best_record
WHERE RecordDateTime >= @from AND RecordDateTime < @to
) t
WHERE rn = 1
ON DUPLICATE KEY UPDATE
BestRecord = VALUES(BestRecord),
BestRecordDateTime = VALUES(BestRecordDateTime),
UpdatedDateTime = NOW();
ORDER BY IF(AppID = 105, BestRecord, -BestRecord): 앱 105는 작은 값이, 나머지는 큰 값이 1등(rn = 1)이 됩니다.ON DUPLICATE KEY UPDATE라서 같은 달을 여러 번 실행해도 결과가 같습니다(중단 후 재실행 안전).- 월 목록은 개요 7-2의 결과를 사용하고,
php-cli로 월별 반복 스크립트를 만들거나 수동으로 몇 달씩 실행합니다. daily_typing_exam_record는GROUP BY PlayerID, WritingID, DATE(RecordDateTime)로MAX(IF(Record>=0,Record,NULL)),MAX(IF(Record<0,Record,NULL))을 계산하고,MaxPlusRecordDateTime은 같은 윈도우 함수 방식으로 구합니다.
결과 검증
-- 원본에서 계산한 값과 집계 테이블이 다른 행 수 (0이어야 함, 월별로 확인)
SET @from = '2026-08-01', @to = '2026-09-01';
SELECT COUNT(*) AS only_in_source FROM (
SELECT PlayerID, AppID, DATE(RecordDateTime) AS d,
IF(AppID = 105, MIN(BestRecord), MAX(BestRecord)) AS r
FROM best_record
WHERE RecordDateTime >= @from AND RecordDateTime < @to
GROUP BY PlayerID, AppID, DATE(RecordDateTime)
EXCEPT
SELECT PlayerID, AppID, RecordDate, BestRecord
FROM daily_best_record
WHERE RecordDate >= @from AND RecordDate < @to
) x;
-- 두 SELECT 순서를 바꿔 only_in_daily도 확인
6. 적용 절차 (3단계 배포로 위험 줄이기)
핵심: 쓰기를 먼저 → 과거 채우기 → 조회는 마지막에 전환. 조회를 바꾸기 전까지는 화면 결과가 기존과 같으므로 문제가 생겨도 사용자 영향이 없습니다.
| 순서 | 어디서 | 작업 | 확인 |
|---|---|---|---|
| 0 | 운영 | 안1 완료 확인 (UNIQUE 키 포함), 백업 | |
| 1 | 스테이징 | 4-1 테이블 생성 | SHOW CREATE TABLE |
| 2 | 스테이징 | 쓰기 코드 배포: daily_record.php, 저장 흐름(4-4), 삭제 흐름(4-5), 최고기록 UPSERT(4-3). 조회 코드는 그대로 |
게임 종료·긴글 시험·기록 삭제·학생 삭제 후 집계 행이 생기고/바뀌고/사라지는지 |
| 3 | 스테이징 | 5장 backfill (월 단위) → 오늘 날짜만 한 번 더 실행 | 소요 시간 기록, 결과 검증 쿼리 0건 |
| 4 | 스테이징 | 조회 코드 배포 (4-6) | 게임 화면 히스토리·일간·월간 랭킹이 2단계 이전 화면과 같은지 (월간은 안1의 연도 버그 수정분만 차이) |
| 5 | 운영 | 1 → 2 (쓰기 배포) | 슬로우 쿼리·오류 로그 |
| 6 | 운영 (새벽) | 3 (backfill) | 검증 쿼리 0건 |
| 7 | 운영 | 4 (조회 배포) | 화면 테스트, 1주일간 매일 정합성 점검(아래) |
3단계에서 "오늘 날짜만 한 번 더"를 하는 이유: backfill이 오늘 데이터를 읽는 사이에 새 기록이 저장되면, backfill이 조금 전 상태로 집계 행을 덮어쓸 수 있습니다. 끝난 뒤 오늘 날짜만 다시 실행하면 최신 상태로 맞춰집니다.
정기 정합성 점검 (최근 7일)
SELECT COUNT(*) AS mismatch FROM (
SELECT PlayerID, AppID, DATE(RecordDateTime) AS d,
IF(AppID = 105, MIN(BestRecord), MAX(BestRecord)) AS r
FROM best_record
WHERE RecordDateTime >= CURDATE() - INTERVAL 7 DAY
GROUP BY PlayerID, AppID, DATE(RecordDateTime)
EXCEPT
SELECT PlayerID, AppID, RecordDate, BestRecord
FROM daily_best_record
WHERE RecordDate >= CURDATE() - INTERVAL 7 DAY
) x;
0이 아니면 어떤 쓰기 경로에서 집계 갱신이 빠졌는지 확인하고, 해당 날짜 범위로 5장 backfill을 다시 실행하면 복구됩니다.
7. 롤백
| 상황 | 방법 |
|---|---|
| 조회 결과가 이상함 | 조회 코드만 git revert 후 재배포 → 즉시 원본 기준 조회로 복귀. 집계 테이블은 두어도 무방 |
| 쓰기 경로 오류 | 쓰기 코드 git revert. UPSERT로 바꾼 최고기록 로직도 함께 되돌아감 (UNIQUE 키가 있어도 기존 delete→insert 코드는 동작) |
| 안2 전체 철회 | 코드 되돌린 뒤 DROP TABLE daily_best_record, daily_typing_exam_record; |
8. 수정 대상 파일
| 파일 | 변경 |
|---|---|
src/web/server/lib/daily_record.php (신규) |
refresh_daily_best_record(), refresh_daily_typing_exam_record() |
src/web/server/lib/app_highest_record.php |
UPSERT 함수로 교체 |
src/web/server/record/update_result_record.php |
집계 갱신, UPSERT, 트랜잭션 |
src/web/php/record/update_typing_exam_record.php, src/web/php/db/typing_exam_collection.php |
집계 갱신, 최고기록 UPSERT, 히스토리·일간/월간 랭킹 조회 전환 |
src/web/server/record/delete_record.php |
삭제 후 집계 갱신, 최고기록 재계산을 집계 테이블 기준으로 |
src/web/server/player/delete_player.php, src/web/server/maestro/delete_test_player_record.php |
집계 행 삭제 추가 (player 삭제 전) |
src/web/server/record/history_record.php, ranking_record_day.php, ranking_record_month.php, app_ranking.php |
조회 전환 |
src/web/sql/make_db.sql, src/web/sql/migration/YYMMDD_add_daily_record_tables.sql |
테이블 정의 |
php/record/*는 php/lib/connect_db.php의 클래스 방식, server/record/*는 server/setup/connect_db.php의 전역 $db_conn 방식을 씁니다. 공용 함수는 연결 객체를 인자로 받게 만들어 두 쪽에서 모두 호출할 수 있게 합니다(TypingExamCollection 안에서는 $this->mysqli를 넘김).
9. 장단점과 위험
| 장점 | 단점 / 위험 | 대응 |
|---|---|---|
| 히스토리가 기록 기간과 무관하게 빠름 | 쓰기 경로 여러 곳 수정 → 한 곳이라도 빠지면 집계가 어긋남 | 공용 함수 1개로 통일, 정기 정합성 점검, 재실행 가능한 backfill |
| 원본을 아카이빙해도 히스토리·최고기록 재계산이 정상 (안3 가능) | 테이블 2개 추가, 저장 시 쿼리 1~2개 증가 | 증가 쿼리는 인덱스로 최대 24행만 읽음 |
| 최고기록 UPSERT로 동시성 문제 해소 | ON DUPLICATE KEY UPDATE 절의 컬럼 순서 실수 위험 |
4-3 주의사항, 테스트 케이스(더 좋은 기록/나쁜 기록/같은 기록) |
조회 쿼리가 단순해짐 (GROUP BY 제거) |
원본과 집계가 둘 다 있어 "진짜 값"이 헷갈릴 수 있음 | 원본이 기준, 집계는 원본에서 다시 만들 수 있는 사본이라는 원칙을 문서·주석으로 명시 |
10. 예상 효과 (측정으로 확인 필요)
| 쿼리 | 안1 후 읽는 행 (예상) | 안2 후 읽는 행 (예상) |
|---|---|---|
| 히스토리 (최근 7일) | 해당 플레이어·앱의 전체 기간 원본 행 | 7행 |
| 일간 랭킹 | 그날 원본 행 (플레이어 × 플레이한 시간 수) | 그날 플레이어 수만큼 |
| 월간 랭킹 | 그달 원본 행 | 그달 (플레이어 × 플레이한 날 수) |
| 기록 삭제 후 최고기록 재계산 | 플레이어·앱 전체 원본 행 | 플레이어·앱의 플레이한 날 수 |
| 게임 종료 시 쓰기 | 조회 2 + 쓰기 최대 3 | 조회 2 + 쓰기 2~3 (최고기록 UPSERT 1회) |
11. 체크리스트
- 3장 기준으로 안2 진행 여부 결정 (daily_rows 측정, 안1 후 히스토리·월간 랭킹 ms)
- 안1의 최고기록 UNIQUE 키 적용 확인
- 테이블 생성 SQL 스테이징 적용
daily_record.php공용 함수 작성- 쓰기 경로 반영: 일반 앱 저장 / 긴글 시험 저장(TDD 경로 포함) / 기록 삭제(2종) / 학생 삭제 / 테스트 계정 초기화
- 최고기록 UPSERT (컬럼 순서 주의) 및 테스트
- backfill 월 단위 실행 → 오늘 날짜 재실행 → 검증 쿼리 0건
- 조회 전환: 히스토리 2종, 일간·월간 랭킹 (일반 앱 + 긴글 시험 플러스/마이너스)
- 화면 결과 비교
- 운영: 쓰기 배포 → backfill(새벽) → 조회 배포
- 1주일 정합성 점검, 이후 주 1회 (또는 배치화)
make_db.sql, migration 파일 반영