jisangs/chocomae
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
chocomae/doc/plan/db/02-option1-index-and-query-rewrite.md
T
jisangs 72663ac6e9 DB 개선 계획 작성
2026-09-15 10:38:05 +09:00

26 KiB
Raw Permalink Blame History

안1. 인덱스 추가 + 쿼리 조건 개선

개요 문서 | 추천 단계: 1단계 (필수) | 난이도: 하 | 위험도: 낮음 | 예상 작업량: 1~2일

한 줄 요약

실제 쿼리 패턴에 맞는 복합 인덱스를 추가하고, 날짜 컬럼에 함수를 씌운 조건을 "시작 시각 이상 ~ 끝 시각 미만" 범위 조건으로 바꿔 인덱스를 쓸 수 있게 한다.

1. 해결하는 문제

문제 (선행 문서 번호) 해결 정도
1. 날짜 컬럼에 DATE()/HOUR()/YEAR()/MONTH()/DAYOFMONTH()를 씌운 조건 ◎
2. 복합 인덱스 부재 ◎
4. 게임 종료마다 반복되는 조회 비용 ○ (조회가 1시간 범위만 읽게 됨)
6. 관리자 기록 목록 조회 ○ (날짜 인덱스로 최근 50건을 빠르게 찾음)
(버그) 이달의 랭킹에 작년 기록이 섞임 ◎
(정합성) 최고기록 테이블 중복 행 ◎ (UNIQUE 키)

2. 쉬운 설명

  • 지금은 "이 선생님의 이 앱에서 오늘 기록"을 찾을 때, 색인이 없어서 120만 행을 모두 넘겨보며 날짜를 계산합니다.
  • 인덱스를 (선생님 → 앱 → 기록 시각) 순서로 만들면, 색인에서 "선생님 123 → 앱 5 → 2026-09-14 00:00~24:00" 구간으로 바로 이동해 그날 기록만 읽습니다.
  • 단, 조건을 DATE(RecordDateTime) = '2026-09-14'처럼 쓰면 색인을 못 쓰므로, RecordDateTime >= '2026-09-14' AND RecordDateTime < '2026-09-15'로 바꿔야 합니다. 결과는 같습니다.
  • 인덱스를 추가해도 기존 코드는 그대로 동작합니다. 그래서 "인덱스 먼저 추가 → 코드 나중 배포" 순서로 안전하게 진행할 수 있습니다.

3. 변경 내용

3-1. 인덱스 추가

설계 원칙

  1. =로 비교하는 컬럼을 앞에, 범위(>=, <)로 비교하는 컬럼(RecordDateTime)을 뒤에 둔다.
  2. 자주 실행되는 랭킹 쿼리용 인덱스에는 PlayerID, 기록 값까지 넣어 원본 행을 읽지 않고 인덱스만으로 계산하게 한다(커버링 인덱스).
  3. 인덱스는 쓰기(INSERT/UPDATE) 때마다 함께 갱신되므로 필요한 것만 만든다. 기록 저장은 초당 수 건 수준이라 인덱스 3개 정도는 부담이 작다(예상).

best_record

인덱스 이름 컬럼 순서 이 인덱스를 쓰는 쿼리
idx_br_maestro_app_time (MaestroID, AppID, RecordDateTime, PlayerID, BestRecord) 시간/일간/월간 랭킹 (app_ranking.php, ranking_record_hour.php, ranking_record_day.php, ranking_record_month.php) — 커버링
idx_br_player_app_time (PlayerID, AppID, RecordDateTime) 기록 저장 시 "이번 시간 기록" 확인 (update_result_record.php), 히스토리 (history_record.php), 기록 삭제 후 최고기록 재계산 (delete_record.php), 플레이어 삭제
idx_br_maestro_time (MaestroID, RecordDateTime) 관리자 기록 목록 최신순 조회 (request_app_player_record_list.php)

idx_br_player_app_time이 MaestroID가 아닌 PlayerID로 시작하는 이유: PlayerID는 한 선생님에게만 속하므로 PlayerID만으로도 대상이 충분히 좁혀지고, delete_test_player_record.php처럼 PlayerID만으로 조회하는 쿼리까지 함께 쓸 수 있습니다. MaestroID = ? 조건은 좁혀진 행에서 추가로 확인합니다.

typing_exam_record

인덱스 이름 컬럼 순서 이 인덱스를 쓰는 쿼리
idx_ter_maestro_writing_time (MaestroID, WritingID, RecordDateTime, PlayerID, Record) 긴글 시험 랭킹 6종 (typing_exam_collection.php getRankingRecord*, getRankingMinusRecord*) — 커버링
idx_ter_player_writing_time (PlayerID, WritingID, RecordDateTime) getThisHourRecord(), getHistoryRecord(), 기록 삭제 후 재계산
idx_ter_maestro_time (MaestroID, RecordDateTime) 관리자 기록 목록 (request_writing_player_record_list.php)

license_score (선택 — 현재 620행이라 효과는 미미, 비용도 미미)

인덱스 이름 컬럼 순서 이 인덱스를 쓰는 쿼리
idx_ls_maestro_player_time (MaestroID, PlayerID, ScoreDateTime) get_license_score.php

적용 SQL

-- 0) 현재 인덱스 확인 (적용 전후 비교용)
SHOW INDEX FROM best_record;
SHOW INDEX FROM typing_exam_record;

-- 1) best_record
ALTER TABLE best_record
  ADD INDEX idx_br_maestro_app_time (MaestroID, AppID, RecordDateTime, PlayerID, BestRecord),
  ADD INDEX idx_br_player_app_time  (PlayerID, AppID, RecordDateTime),
  ADD INDEX idx_br_maestro_time     (MaestroID, RecordDateTime),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 2) typing_exam_record
ALTER TABLE typing_exam_record
  ADD INDEX idx_ter_maestro_writing_time (MaestroID, WritingID, RecordDateTime, PlayerID, Record),
  ADD INDEX idx_ter_player_writing_time  (PlayerID, WritingID, RecordDateTime),
  ADD INDEX idx_ter_maestro_time         (MaestroID, RecordDateTime),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 3) license_score (선택)
ALTER TABLE license_score
  ADD INDEX idx_ls_maestro_player_time (MaestroID, PlayerID, ScoreDateTime),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 4) 통계 갱신 (옵티마이저가 새 인덱스를 잘 고르도록)
ANALYZE TABLE best_record, typing_exam_record, license_score;
  • ALGORITHM=INPLACE, LOCK=NONE: 인덱스를 만드는 동안에도 읽기·쓰기가 계속 가능합니다. 이 방식이 불가능한 상황이면 MariaDB가 실행하지 않고 오류를 내므로, 모르는 사이 테이블이 잠기는 일은 없습니다.
  • 소요 시간: best_record 120만 행 기준 수십 초~수 분(예상). 스테이징에서 먼저 측정하세요.
  • 인덱스 크기: 인덱스 1개당 수십 MB 수준(예상). 적용 후 개요 7-1의 쿼리로 index_mb를 기록하세요. 디스크 여유 공간이 테이블 크기의 2배 이상인지 먼저 확인하세요(df -h).

3-2. 최고기록 테이블 UNIQUE 키 추가 (중복 정리 후)

app_highest_record는 "(선생님, 플레이어, 앱)당 1행"이어야 하지만 이를 보장하는 장치가 없습니다. UNIQUE 키를 추가하면 DB가 중복을 원천 차단하고, 조회도 해당 인덱스로 빨라집니다.

앱 105는 낮을수록 좋은 앱입니다(util_app.php is_highest_record_prefer_app()). 중복 정리 시 앱 105는 가장 작은 값을, 나머지는 가장 큰 값을 남깁니다.

-- 1) 안전을 위한 사본
CREATE TABLE app_highest_record_bak_260914        AS SELECT * FROM app_highest_record;
CREATE TABLE typing_exam_highest_record_bak_260914 AS SELECT * FROM typing_exam_highest_record;

-- 2) 중복 확인 (개요 문서 7-5와 동일). 0건이면 3) 생략
SELECT COUNT(*) FROM (
  SELECT 1 FROM app_highest_record
  GROUP BY MaestroID, PlayerID, AppID HAVING COUNT(*) > 1
) d;

-- 3) 중복 정리: 같은 조합에서 "더 좋은 기록(b)"이 있는 행(a)을 삭제
--    기록이 같으면 ID가 큰(최근) 행을 남김
DELETE a
FROM app_highest_record a
JOIN app_highest_record b
  ON  a.MaestroID = b.MaestroID
  AND a.PlayerID  = b.PlayerID
  AND a.AppID     = b.AppID
  AND a.AppHighestRecordID <> b.AppHighestRecordID
WHERE
  (a.AppID <> 105 AND (a.HighestRecord < b.HighestRecord
                       OR (a.HighestRecord = b.HighestRecord AND a.AppHighestRecordID < b.AppHighestRecordID)))
  OR
  (a.AppID =  105 AND (a.HighestRecord > b.HighestRecord
                       OR (a.HighestRecord = b.HighestRecord AND a.AppHighestRecordID < b.AppHighestRecordID)));

-- 긴글 시험 최고기록은 모두 "높을수록 좋음"
DELETE a
FROM typing_exam_highest_record a
JOIN typing_exam_highest_record b
  ON  a.MaestroID = b.MaestroID
  AND a.PlayerID  = b.PlayerID
  AND a.WritingID = b.WritingID
  AND a.TypingExamHighestRecordID <> b.TypingExamHighestRecordID
WHERE a.HighestRecord < b.HighestRecord
   OR (a.HighestRecord = b.HighestRecord AND a.TypingExamHighestRecordID < b.TypingExamHighestRecordID);

-- 4) 중복이 0건인지 다시 확인한 뒤 UNIQUE 키 추가
ALTER TABLE app_highest_record
  ADD UNIQUE INDEX uk_ahr_maestro_player_app (MaestroID, PlayerID, AppID),
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE typing_exam_highest_record
  ADD UNIQUE INDEX uk_tehr_maestro_player_writing (MaestroID, PlayerID, WritingID),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 5) 1~2주 문제 없으면 사본 삭제
-- DROP TABLE app_highest_record_bak_260914, typing_exam_highest_record_bak_260914;

UNIQUE 키 추가 후 기존 코드 동작: 현재 코드는 "삭제 후 삽입"이므로 평소에는 그대로 동작합니다. 드물게 두 요청이 동시에 들어오면 한쪽 INSERT가 중복 오류로 실패하는데, 현재 코드는 오류를 무시하므로 화면 오류는 없습니다. 이 경우를 완전히 없애는 UPSERT 방식은 안2 3-3에서 다룹니다.

3-3. 쿼리 조건 수정 (전/후 비교)

변환 규칙

의미 현재 (인덱스 사용 불가) 변경 (인덱스 사용 가능)
지금 이 시간 DATE(x) = DATE(NOW()) AND HOUR(x) = HOUR(NOW()) x >= CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME)
AND x < CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME) + INTERVAL 1 HOUR
오늘 DATE(x) = DATE(NOW()) x >= CURDATE() AND x < CURDATE() + INTERVAL 1 DAY
이번 달 MONTH(x) = MONTH(NOW()) ← 연도 누락 버그 x >= CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE)
AND x < CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
특정 날짜 ? YEAR(x)=YEAR(?) AND MONTH(x)=MONTH(?) AND DAYOFMONTH(x)=DAYOFMONTH(?) x >= DATE(?) AND x < DATE(?) + INTERVAL 1 DAY
특정 날짜 ?의 특정 시 ? 위 + HOUR(x) = HOUR(?) x >= DATE(?) + INTERVAL HOUR(?) HOUR
AND x < DATE(?) + INTERVAL (HOUR(?) + 1) HOUR
특정 날짜 ?가 속한 달 YEAR(x)=YEAR(?) AND MONTH(x)=MONTH(?) x >= CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE)
AND x < CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
특정 날짜 ? 이전 전체 DATE(x) <= ? x < DATE(?) + INTERVAL 1 DAY
  • 파라미터(?)나 NOW()에 함수를 쓰는 것은 괜찮습니다. 컬럼(x)에만 함수를 쓰지 않으면 됩니다.
  • 시각 계산은 지금처럼 DB의 NOW()를 기준으로 합니다. PHP의 date()로 계산하면 PHP와 DB의 타임존이 다를 때 결과가 어긋날 수 있습니다.
  • WHERE 안에서 조건의 순서는 성능과 무관합니다. 아래 예시는 읽기 쉽게 인덱스 컬럼 순서로 정렬했을 뿐입니다.

A. update_result_record.php — 게임 종료마다 실행 (가장 자주 실행)

get_best_record()

-- 현재
SELECT BestRecordID, BestRecord
FROM best_record
WHERE MaestroID = ? AND AppID = ? AND PlayerID = ? AND DATE(RecordDateTime) = DATE(NOW())
AND HOUR(RecordDateTime) = HOUR(NOW())
-- bind_param("iii", $maestro_id, $app_id, $player_id)

-- 변경
SELECT BestRecordID, BestRecord
FROM best_record
WHERE PlayerID = ? AND AppID = ? AND MaestroID = ?
  AND RecordDateTime >= CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME) + INTERVAL 1 HOUR
-- bind_param("iii", $player_id, $app_id, $maestro_id)   ← 바인딩 순서 주의

update_best_record()는 WHERE BestRecordID = ?(기본 키)로 이미 1행을 찾으므로 성능 문제는 없습니다. 뒤에 붙은 DATE()/HOUR() 조건은 "시간이 바뀌는 순간 방금 조회한 행을 갱신하지 않기 위한" 안전장치이므로 그대로 두거나 위의 범위 조건으로 바꿔도 됩니다.

B. app_ranking.php — 교실 화면 진입 시 3개 쿼리

-- get_ranking_hour() WHERE 절
-- 현재
WHERE BR.PlayerID = P.PlayerID AND DATE(BR.RecordDateTime) = DATE(NOW()) AND HOUR(BR.RecordDateTime) = HOUR(NOW()) AND BR.MaestroID = ? AND BR.AppID = ?
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME)
  AND BR.RecordDateTime <  CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME) + INTERVAL 1 HOUR
  AND BR.PlayerID = P.PlayerID

-- get_ranking_day() WHERE 절
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= CURDATE()
  AND BR.RecordDateTime <  CURDATE() + INTERVAL 1 DAY
  AND BR.PlayerID = P.PlayerID

-- get_ranking_month() WHERE 절  ※ 연도 누락 버그 수정 포함
-- 현재
WHERE BR.PlayerID = P.PlayerID AND MONTH(BR.RecordDateTime) = MONTH(NOW()) AND BR.MaestroID = ? AND BR.AppID = ?
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE)
  AND BR.RecordDateTime <  CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
  AND BR.PlayerID = P.PlayerID

세 함수 모두 bind_param('ii', $maestroID, $appID)는 변경 없음. SELECT, GROUP BY, ORDER BY 부분도 그대로 둡니다.

C. ranking_record_hour.php / ranking_record_day.php / ranking_record_month.php

-- ranking_record_day.php
-- 현재
WHERE BR.MaestroID = ? AND BR.playerID = U.playerID AND YEAR(BR.RecordDateTime) = YEAR(?) AND MONTH(BR.RecordDateTime) = MONTH(?) AND DAYOFMONTH(BR.RecordDateTime) = DAYOFMONTH(?) AND AppID = ?
-- bind_param('isssi', $maestro_id, $date, $date, $date, $app_id)
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= DATE(?)
  AND BR.RecordDateTime <  DATE(?) + INTERVAL 1 DAY
  AND BR.PlayerID = U.PlayerID
-- bind_param('iiss', $maestro_id, $app_id, $date, $date)

-- ranking_record_hour.php
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= DATE(?) + INTERVAL HOUR(?) HOUR
  AND BR.RecordDateTime <  DATE(?) + INTERVAL (HOUR(?) + 1) HOUR
  AND BR.PlayerID = U.PlayerID
-- bind_param('iissss', $maestro_id, $app_id, $date, $time, $date, $time)

-- ranking_record_month.php
-- 변경
WHERE BR.MaestroID = ? AND BR.AppID = ?
  AND BR.RecordDateTime >= CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE)
  AND BR.RecordDateTime <  CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE) + INTERVAL 1 MONTH
  AND BR.PlayerID = U.PlayerID
-- bind_param('iiss', $maestro_id, $app_id, $date, $date)

D. history_record.php — 최근 7일 히스토리 (+ AppID 바인딩으로 보안 수정)

-- 변경 (쿼리 전체)
SELECT DATE(BR.RecordDateTime) AS Date, MAX(BR.BestRecord) AS HighScore, AA.AppName AS AppName   -- 앱 105는 MIN
FROM best_record BR
INNER JOIN app AS AA ON BR.AppID = AA.AppID
WHERE BR.PlayerID = ? AND BR.AppID = ? AND BR.MaestroID = ?
  AND BR.RecordDateTime < DATE(?) + INTERVAL 1 DAY
GROUP BY DATE(BR.RecordDateTime)
ORDER BY DATE(BR.RecordDateTime) DESC
LIMIT 7
-- bind_param('iiis', $player_id, $app_id, $maestro_id, $date)

SELECT와 GROUP BY의 DATE()는 조건(WHERE)이 아니므로 인덱스 사용과 무관합니다. 다만 이 쿼리는 여전히 해당 플레이어·앱의 전체 기간 기록을 날짜별로 묶어야 하므로, 한 플레이어의 기록이 수천 행을 넘으면 점점 느려집니다 → 안2에서 근본 해결.

E. typing_exam_collection.php — 긴글 시험

-- getThisHourRecord()
-- 변경
SELECT TypingExamRecordID, Record
FROM typing_exam_record
WHERE PlayerID = ? AND WritingID = ? AND MaestroID = ?
  AND RecordDateTime >= CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME) + INTERVAL 1 HOUR
-- bind_param("iii", $playerID, $writingID, $maestroID)

-- getHistoryRecord()
-- 변경
SELECT DATE(RecordDateTime), MAX(Record)
FROM typing_exam_record
WHERE PlayerID = ? AND WritingID = ? AND MaestroID = ?
  AND RecordDateTime < DATE(?) + INTERVAL 1 DAY
GROUP BY DATE(RecordDateTime)
ORDER BY DATE(RecordDateTime) DESC
LIMIT 7
-- bind_param('iiis', $playerID, $writingID, $maestroID, $date)

랭킹 6개 함수(getRankingRecordHour/Day/Month, getRankingMinusRecordHour/Day/Month)는 C와 같은 규칙으로 바꿉니다. 예시(getRankingRecordDay):

-- 변경
SELECT TER.PlayerID AS PlayerID, U.Name AS Name, MAX(TER.Record) AS HighScore
FROM typing_exam_record TER, player U
WHERE TER.MaestroID = ? AND TER.WritingID = ?
  AND TER.RecordDateTime >= DATE(?)
  AND TER.RecordDateTime <  DATE(?) + INTERVAL 1 DAY
  AND TER.Record >= 0                -- Minus 버전은 TER.Record < 0
  AND TER.PlayerID = U.PlayerID
GROUP BY TER.PlayerID
ORDER BY MAX(TER.Record) DESC;       -- Minus 버전은 ASC
-- bind_param('iiss', $maestroID, $writingID, $date, $date)

F. 수정은 필요 없지만 인덱스 효과를 받는 쿼리

파일 쿼리 사용 인덱스
delete_record.php get_best_record_record_info() WHERE MaestroID=? AND PlayerID=? AND AppID=? ORDER BY BestRecord LIMIT 1 idx_br_player_app_time
delete_player.php DELETE ... WHERE MaestroID=? AND PlayerID=? (6개 테이블) idx_br_player_app_time 등
delete_test_player_record.php DELETE ... WHERE PlayerID=? idx_br_player_app_time, idx_ter_player_writing_time
request_*_player_record_list.php WHERE MaestroID=? AND 'start' <= RecordDateTime ... ORDER BY RecordDateTime DESC LIMIT 50 idx_*_maestro_time (날짜 조건은 원래 범위 형태)
app_highest_record.php, menu_collection.php, writing_collection.php 최고기록 단건 조회 uk_ahr_*, uk_tehr_*

4. 수정 대상 파일 목록

파일 변경 내용
src/web/server/record/update_result_record.php get_best_record() 조건
src/web/server/record/app_ranking.php 시/일/월 랭킹 조건 (+월간 연도 버그)
src/web/server/record/ranking_record_hour.php 조건, 바인딩
src/web/server/record/ranking_record_day.php 조건, 바인딩
src/web/server/record/ranking_record_month.php 조건, 바인딩
src/web/server/record/history_record.php 조건, AppID 바인딩
src/web/php/db/typing_exam_collection.php getThisHourRecord, getHistoryRecord, 랭킹 6종
src/web/sql/make_db.sql, make_db_license_timer.sql 신규 설치용 스키마에 인덱스·UNIQUE 반영
src/web/sql/migration/YYMMDD_add_record_indexes.sql (신규) 운영에 적용한 SQL 기록

5. 적용 절차

순서 어디서 작업 확인
0 운영 backup-db.sh 수동 실행 백업 파일 생성·크기
1 스테이징 최신 백업 복원 row 수가 운영과 비슷한지
2 스테이징 개요 7-3 방식으로 대표 쿼리 개선 전 EXPLAIN/ANALYZE 기록 type=ALL, rows≈120만 예상
3 스테이징 3-1 인덱스 추가, 3-2 중복 정리 + UNIQUE 키 소요 시간 기록, SHOW INDEX
4 스테이징 기존 쿼리 그대로 EXPLAIN (인덱스만으로 좋아지는지) 저장/삭제 쿼리는 ref로 개선
5 스테이징 3-3 쿼리 수정 코드 배포, 변경 쿼리 EXPLAIN + 결과 동일성 비교(아래) 랭킹 쿼리 type=range, key=idx_br_maestro_app_time
6 스테이징 화면 테스트: 게임 종료 후 기록 저장, 교실 랭킹(시/일/월), 히스토리, 긴글 시험 저장·랭킹, 기록 삭제, 학생 삭제 기존과 같은 결과 (월간은 버그 수정분 차이만)
7 운영 (새벽) 3-1, 3-2 SQL 적용 오류 없음, 서비스 정상
8 운영 코드 배포 (release 브랜치) 화면 테스트 6 반복
9 운영 1~2일 슬로우 쿼리 로그 확인, 측정 양식 채우기 1초 이상 기록 쿼리 감소

순서가 중요한 이유: 인덱스는 기존 코드에 영향이 없으므로 먼저 추가합니다. 코드를 먼저 배포하면 인덱스가 없는 동안 새 쿼리도 여전히 느립니다(결과는 같음).

결과 동일성 비교 방법

기존 쿼리와 변경 쿼리의 결과가 같은지 EXCEPT(한쪽에만 있는 행 찾기)로 확인합니다. 양방향 모두 0건이면 같습니다.

SET @m = 123, @a = 5, @d = '2026-09-10';   -- 7-3에서 찾은 값

SELECT COUNT(*) AS only_in_old FROM (
  SELECT BR.PlayerID, MAX(BR.BestRecord) AS rec
  FROM best_record BR
  WHERE BR.MaestroID = @m AND BR.AppID = @a
    AND YEAR(BR.RecordDateTime) = YEAR(@d) AND MONTH(BR.RecordDateTime) = MONTH(@d)
    AND DAYOFMONTH(BR.RecordDateTime) = DAYOFMONTH(@d)
  GROUP BY BR.PlayerID
  EXCEPT
  SELECT BR.PlayerID, MAX(BR.BestRecord) AS rec
  FROM best_record BR
  WHERE BR.MaestroID = @m AND BR.AppID = @a
    AND BR.RecordDateTime >= DATE(@d) AND BR.RecordDateTime < DATE(@d) + INTERVAL 1 DAY
  GROUP BY BR.PlayerID
) diff;
-- 두 SELECT의 순서를 바꿔 only_in_new도 확인

6. 롤백

-- 인덱스 제거 (코드를 먼저 되돌린 뒤 실행할 필요는 없음. 새 쿼리도 인덱스 없이 동작함)
ALTER TABLE best_record
  DROP INDEX idx_br_maestro_app_time,
  DROP INDEX idx_br_player_app_time,
  DROP INDEX idx_br_maestro_time;

ALTER TABLE typing_exam_record
  DROP INDEX idx_ter_maestro_writing_time,
  DROP INDEX idx_ter_player_writing_time,
  DROP INDEX idx_ter_maestro_time;

ALTER TABLE license_score DROP INDEX idx_ls_maestro_player_time;

ALTER TABLE app_highest_record         DROP INDEX uk_ahr_maestro_player_app;
ALTER TABLE typing_exam_highest_record DROP INDEX uk_tehr_maestro_player_writing;

-- 중복 정리를 되돌려야 하는 경우 (정리 이후 새로 저장된 최고기록은 사라지므로 신중히)
-- RENAME TABLE app_highest_record TO app_highest_record_broken,
--              app_highest_record_bak_260914 TO app_highest_record;

코드는 git revert 후 release 브랜치에 다시 배포합니다.

7. 장단점과 위험

장점 단점 / 위험 대응
데이터를 거의 건드리지 않음 인덱스만큼 디스크 사용량 증가 적용 전 여유 공간 확인
인덱스 추가만으로도 저장·삭제 쿼리 즉시 개선 기록 INSERT/UPDATE 시 인덱스 갱신 비용 소폭 증가 기록 저장 빈도가 낮아 체감 어려움(예상), 슬로우 로그로 확인
롤백이 간단 (DROP INDEX) 바인딩 순서를 잘못 바꾸면 오류 없이 엉뚱한 결과가 나옴 결과 동일성 비교 쿼리, 화면 테스트
월간 랭킹 버그, 보안 이슈(history) 함께 해결 옵티마이저가 기대와 다른 인덱스를 고를 수 있음 ANALYZE TABLE 후 EXPLAIN 확인, 필요 시 FORCE INDEX
히스토리·월간 랭킹은 여전히 넓은 범위를 GROUP BY 안2

8. 예상 효과 (측정으로 확인 필요)

쿼리 현재 읽는 행 (예상) 변경 후 읽는 행 (예상)
기록 저장 시 이번 시간 기록 확인 해당 선생님 또는 플레이어의 모든 기록 ~ 전체 0~1행
시간 랭킹 해당 선생님·앱의 전체 기간 기록 그 1시간의 기록 (수~수십 행)
일간 랭킹 〃 그날 기록 (수십~수백 행)
월간 랭킹 〃 (+ 다른 해 같은 달) 그달 기록 (수백~수천 행)
관리자 기록 목록 최신 50건 선생님 기록 전체 후 정렬 최근 구간부터 50건 (필터 조건에 따라 달라짐)

9. 한계 → 다음 단계

  • 히스토리(최근 7일)와 월간 랭킹은 원본 행을 매번 묶어서 계산하므로, 데이터가 계속 쌓이면 점점 느려집니다.
  • 원본 테이블 크기 자체는 줄지 않습니다.
  • → 안2. 일별 집계 테이블, 안3. 아카이빙으로 이어집니다.

10. 체크리스트

  • 운영 백업 완료, 디스크 여유 공간 확인
  • 스테이징에 최신 백업 복원
  • 개선 전 EXPLAIN/ANALYZE 수치 기록
  • 최고기록 테이블 중복 점검 → 사본 생성 → 정리 → UNIQUE 키 추가
  • 기록 테이블 인덱스 추가, ANALYZE TABLE
  • 쿼리 수정 (7개 파일), 바인딩 순서 재확인
  • 결과 동일성 비교 (시/일/월 랭킹, 히스토리)
  • 화면 테스트 (저장, 랭킹, 히스토리, 긴글 시험, 기록 삭제, 학생 삭제)
  • 운영 SQL 적용 (새벽) → 코드 배포
  • 슬로우 쿼리 로그로 1~2일 모니터링, 측정 양식 기록
  • make_db.sql 및 migration 파일 반영
Reference in New Issue View Git Blame Copy Permalink