chocomae/doc/plan/db/02-option1-index-and-query-rewrite.md

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

> [개요 문서](01-improvement-overview.md) | 추천 단계: **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](../../../src/web/server/record/app_ranking.php), [ranking_record_hour.php](../../../src/web/server/record/ranking_record_hour.php), [ranking_record_day.php](../../../src/web/server/record/ranking_record_day.php), [ranking_record_month.php](../../../src/web/server/record/ranking_record_month.php)) — 커버링 |
| `idx_br_player_app_time` | `(PlayerID, AppID, RecordDateTime)` | 기록 저장 시 "이번 시간 기록" 확인 ([update_result_record.php](../../../src/web/server/record/update_result_record.php)), 히스토리 ([history_record.php](../../../src/web/server/record/history_record.php)), 기록 삭제 후 최고기록 재계산 ([delete_record.php](../../../src/web/server/record/delete_record.php)), 플레이어 삭제 |
| `idx_br_maestro_time` | `(MaestroID, RecordDateTime)` | 관리자 기록 목록 최신순 조회 ([request_app_player_record_list.php](../../../src/web/server/record/request_app_player_record_list.php)) |

> `idx_br_player_app_time`이 `MaestroID`가 아닌 `PlayerID`로 시작하는 이유: `PlayerID`는 한 선생님에게만 속하므로 `PlayerID`만으로도 대상이 충분히 좁혀지고, [delete_test_player_record.php](../../../src/web/server/maestro/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](../../../src/web/php/db/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](../../../src/web/server/record/request_writing_player_record_list.php)) |

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

| 인덱스 이름 | 컬럼 순서 | 이 인덱스를 쓰는 쿼리 |
|---|---|---|
| `idx_ls_maestro_player_time` | `(MaestroID, PlayerID, ScoreDateTime)` | [get_license_score.php](../../../src/web/server/license_timer/get_license_score.php) |

#### 적용 SQL

```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](01-improvement-overview.md#7-1-테이블인덱스-크기)의 쿼리로 `index_mb`를 기록하세요. 디스크 여유 공간이 테이블 크기의 2배 이상인지 먼저 확인하세요(`df -h`).

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

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

> 앱 105는 **낮을수록 좋은** 앱입니다([util_app.php](../../../src/web/server/lib/util_app.php) `is_highest_record_prefer_app()`). 중복 정리 시 앱 105는 가장 작은 값을, 나머지는 가장 큰 값을 남깁니다.

```sql
-- 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](03-option2-daily-summary-tables.md)에서 다룹니다.

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

#### 변환 규칙

| 의미 | 현재 (인덱스 사용 불가) | 변경 (인덱스 사용 가능) |
|---|---|---|
| 지금 이 시간 | `DATE(x) = DATE(NOW()) AND HOUR(x) = HOUR(NOW())` | `x >= CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DATETIME)`<br>`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)`<br>`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`<br>`AND x < DATE(?) + INTERVAL (HOUR(?) + 1) HOUR` |
| 특정 날짜 `?`가 속한 달 | `YEAR(x)=YEAR(?) AND MONTH(x)=MONTH(?)` | `x >= CAST(DATE_FORMAT(?, '%Y-%m-01') AS DATE)`<br>`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](../../../src/web/server/record/update_result_record.php) — 게임 종료마다 실행 (가장 자주 실행)

`get_best_record()`

```sql
-- 현재
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](../../../src/web/server/record/app_ranking.php) — 교실 화면 진입 시 3개 쿼리

```sql
-- 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](../../../src/web/server/record/ranking_record_hour.php) / [ranking_record_day.php](../../../src/web/server/record/ranking_record_day.php) / [ranking_record_month.php](../../../src/web/server/record/ranking_record_month.php)

```sql
-- 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](../../../src/web/server/record/history_record.php) — 최근 7일 히스토리 (+ AppID 바인딩으로 보안 수정)

```sql
-- 변경 (쿼리 전체)
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](03-option2-daily-summary-tables.md)에서 근본 해결.

#### E. [typing_exam_collection.php](../../../src/web/php/db/typing_exam_collection.php) — 긴글 시험

```sql
-- 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`):

```sql
-- 변경
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](../../../src/web/server/record/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](../../../src/web/server/player/delete_player.php) | `DELETE ... WHERE MaestroID=? AND PlayerID=?` (6개 테이블) | `idx_br_player_app_time` 등 |
| [delete_test_player_record.php](../../../src/web/server/maestro/delete_test_player_record.php) | `DELETE ... WHERE PlayerID=?` | `idx_br_player_app_time`, `idx_ter_player_writing_time` |
| [request_*_player_record_list.php](../../../src/web/server/record/request_app_player_record_list.php) | `WHERE MaestroID=? AND 'start' <= RecordDateTime ... ORDER BY RecordDateTime DESC LIMIT 50` | `idx_*_maestro_time` (날짜 조건은 원래 범위 형태) |
| [app_highest_record.php](../../../src/web/server/lib/app_highest_record.php), [menu_collection.php](../../../src/web/php/db/menu_collection.php), [writing_collection.php](../../../src/web/php/db/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](../../db/260907-daily-db-backup/backup-db.sh) 수동 실행 | 백업 파일 생성·크기 |
| 1 | 스테이징 | 최신 백업 복원 | row 수가 운영과 비슷한지 |
| 2 | 스테이징 | [개요 7-3](01-improvement-overview.md#7-3-대표-쿼리-explain-기준값) 방식으로 대표 쿼리 **개선 전** 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일 슬로우 쿼리 로그 확인, [측정 양식](01-improvement-overview.md#7-6-측정-결과-기록-양식) 채우기 | 1초 이상 기록 쿼리 감소 |

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

### 결과 동일성 비교 방법

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

```sql
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. 롤백

```sql
-- 인덱스 제거 (코드를 먼저 되돌린 뒤 실행할 필요는 없음. 새 쿼리도 인덱스 없이 동작함)
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. 일별 집계 테이블](03-option2-daily-summary-tables.md), [안3. 아카이빙](04-option3-archiving.md)으로 이어집니다.

---

## 10. 체크리스트

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