# 02단계: 개선 전 성능 측정 (베이스라인)

> 인덱스 추가 전 EXPLAIN과 실행 시간을 측정해 개선 효과의 기준점을 만듭니다.
> 04단계에서 **같은 스크립트, 같은 쿼리, 같은 시각**으로 다시 측정해 비교합니다.

모든 명령은 `result/` 디렉터리에서 실행합니다.

```bash
cd doc/plan/db/260915-improve-production/result
```

---

## 측정 방식

| 원칙 | 이유 |
|---|---|
| 02와 04가 같은 쿼리 파일(`queries/*.sql`)을 실행 | 쿼리가 다르면 실행 시간을 비교할 수 없음 |
| `NOW()` 대신 **지난달의 시각을 고정** (`params.sql`) | 새벽 작업 시간에는 현재 시각의 기록이 거의 없음. 지난달 데이터는 02와 04 사이에 바뀌지 않아 결과 행 수까지 비교 가능 |
| 같은 조건을 **함수형**과 **범위형** 두 가지로 측정 | 인덱스만의 효과(함수형)와 인덱스 + 쿼리 수정 효과(범위형)를 나눠 확인 |
| 쿼리 묶음을 2회 실행해 **2회차** 값 사용 | 1회차에는 디스크에서 페이지를 읽는 시간이 섞임 |
| `SQL_NO_CACHE` | 쿼리 캐시 결과로 시간이 왜곡되지 않게 함 |

| 쿼리 파일 | 조건 | 형태 |
|---|---|---|
| `q1_hour_func.sql` | 특정 1시간 기록 | `DATE()`, `HOUR()` 함수 (수정 전 코드 형태) |
| `q2_hour_range.sql` | 특정 1시간 기록 | 범위 조건 (Stage에서 수정한 코드 형태) |
| `q3_day_func.sql` | 일간 랭킹 | `YEAR()`, `MONTH()`, `DAYOFMONTH()` 함수 |
| `q4_day_range.sql` | 일간 랭킹 | 범위 조건 |
| `q5_month_func.sql` | 월간 랭킹 | `YEAR()`, `MONTH()` 함수 |
| `q6_month_range.sql` | 월간 랭킹 | 범위 조건 |

---

## 2-0. 측정 기준 시각 정하기

MaestroID 181 · AppID 21의 **지난달** 기록 중 가장 많은 시간대를 찾습니다.

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
mariadb -h chocomae.jinaju.com -u jisangs -p chocomae -e "
SELECT DATE(RecordDateTime) AS day, HOUR(RecordDateTime) AS hour, COUNT(*) AS cnt
FROM best_record
WHERE MaestroID = 181 AND AppID = 21
  AND RecordDateTime >= CAST(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(NOW(), '%Y-%m-01') AS DATETIME)
GROUP BY day, hour
ORDER BY cnt DESC
LIMIT 5;" > baseline_pick_time.txt

cat baseline_pick_time.txt
```

첫 번째 행의 `day`와 `hour`로 `params.sql`을 만듭니다. **아래 값은 예시이니 반드시 바꾸세요.**

```bash
cat > params.sql << 'EOF'
SET @maestro = 181, @app = 21, @day = '2026-08-20', @hour = 14;
EOF

cat params.sql
```

> 04단계가 끝날 때까지 `params.sql`을 바꾸지 마세요.

---

## 2-1. Slow Query Log 켜기 (SUPER 권한)

> `SET GLOBAL`은 SUPER 권한이 필요합니다. 2026-09-15에 `jisangs` 계정에 임시 부여했으므로 jisangs로 실행하면 됩니다 (`mariadb -h chocomae.jinaju.com -u jisangs -p chocomae`로 접속 후 아래 SQL 실행). 운영의 원래 값은 `slow_query_log=ON`, `long_query_time=3`, `log_output=FILE`이며(01 1-4), 05단계에서 이 값으로 복원합니다.

```sql
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = 'ON';

SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('slow_query_log', 'long_query_time', 'log_output', 'log_queries_not_using_indexes');
```

- `log_output = 'FILE,TABLE'`: 기존 파일 로그(`/var/log/mysql/mariadb-slow.log`)는 유지하면서 04단계에서 `mysql.slow_log` 테이블로 조회할 수 있게 합니다.
- `long_query_time`은 **새 연결부터** 적용됩니다. PHP는 요청마다 새로 연결하므로 바로 반영됩니다.
- ⚠️ `log_queries_not_using_indexes`는 켜지 마세요. 인덱스를 쓰지 않는 모든 쿼리가 실행 시간과 상관없이 기록되어 `mysql.slow_log`가 빠르게 커집니다.

---

## 2-2. 측정 파일 만들기

### 측정 쿼리 6개

> 쿼리 파일은 반드시 `SELECT`로 시작해야 합니다. 스크립트가 앞에 `EXPLAIN FORMAT=JSON `을 붙여 실행하므로 주석을 넣지 마세요.

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
mkdir -p queries

cat > queries/q1_hour_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, BestRecord, RecordDateTime
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND DATE(RecordDateTime) = @day
  AND HOUR(RecordDateTime) = @hour
ORDER BY RecordDateTime, PlayerID;
EOF

cat > queries/q2_hour_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, BestRecord, RecordDateTime
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(@day AS DATETIME) + INTERVAL @hour HOUR
  AND RecordDateTime <  CAST(@day AS DATETIME) + INTERVAL (@hour + 1) HOUR
ORDER BY RecordDateTime, PlayerID;
EOF

cat > queries/q3_day_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND YEAR(RecordDateTime) = YEAR(@day)
  AND MONTH(RecordDateTime) = MONTH(@day)
  AND DAYOFMONTH(RecordDateTime) = DAYOFMONTH(@day)
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q4_day_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(@day AS DATETIME)
  AND RecordDateTime <  CAST(@day AS DATETIME) + INTERVAL 1 DAY
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q5_month_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND YEAR(RecordDateTime) = YEAR(@day)
  AND MONTH(RecordDateTime) = MONTH(@day)
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q6_month_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(DATE_FORMAT(@day, '%Y-%m-01') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(@day, '%Y-%m-01') AS DATETIME) + INTERVAL 1 MONTH
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

ls queries/
```

### 측정 스크립트

비밀번호를 한 번만 입력받아 EXPLAIN 6개와 실행 시간을 모두 측정합니다. 비밀번호는 파일이나 명령 인자로 남지 않습니다.

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
cat > run_measure.sh << 'EOF'
#!/bin/zsh
# 02·04단계 공통 측정 스크립트
# 사용법: zsh run_measure.sh baseline   (02단계, 인덱스 추가 전)
#         zsh run_measure.sh after      (04단계, 인덱스 추가 후)

PREFIX=$1
if [[ $PREFIX != baseline && $PREFIX != after ]]; then
  echo "사용법: zsh run_measure.sh baseline|after" >&2; exit 1
fi
cd "${0:A:h}" || exit 1

printf "Enter password: "; read -rs DBPW; echo

db() {
  mariadb --defaults-extra-file=<(printf '[client]\npassword="%s"\n' "$DBPW") \
    -h chocomae.jinaju.com -u jisangs chocomae "$@"
}

# 1) EXPLAIN: 쿼리마다 JSON 파일 1개
for q in queries/*.sql; do
  out="${PREFIX}_explain_${q:t:r}.json"
  { cat params.sql; printf 'EXPLAIN FORMAT=JSON '; cat "$q"; } | db -N -r > "$out" \
    || { echo "EXPLAIN 실패: $q" >&2; exit 1; }
  echo "저장: $out"
done

# 2) 실행 시간: 같은 쿼리 묶음을 2회 실행 (2회차 값 사용)
for round in 1 2; do
  { cat params.sql
    for q in queries/*.sql; do
      echo "SELECT '=== round ${round}: ${q:t:r} ===' AS msg;"
      cat "$q"
    done
  } | db -vvv || { echo "실행 시간 측정 실패" >&2; exit 1; }
done > "${PREFIX}_perf.txt"
echo "저장: ${PREFIX}_perf.txt"

# 3) 요약: 쿼리 이름 / 결과 행 수 / 실행 시간 (2회차)
echo
grep -E '^\| === |rows? in set|Empty set' "${PREFIX}_perf.txt" | paste - - - | grep 'round 2' \
  | sed -E 's/\| === round 2: (.*) === \|/\1/; s/\t1 row in set \([0-9.]+ sec\)//'
EOF
```

### EXPLAIN 요약 스크립트

EXPLAIN JSON에서 `access_type`, 사용 인덱스, `rows`만 뽑아 한 줄씩 보여줍니다.

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
cat > summarize_explain.py << 'EOF'
# 사용법: python3 summarize_explain.py baseline [after]
import glob
import json
import sys


def find_table(node):
    if isinstance(node, dict):
        if isinstance(node.get("table"), dict):
            return node["table"]
        children = node.values()
    elif isinstance(node, list):
        children = node
    else:
        return None
    for child in children:
        found = find_table(child)
        if found:
            return found
    return None


def keys_of(node):
    if isinstance(node, dict):
        for k, v in node.items():
            if k == "key":
                yield v
            else:
                yield from keys_of(v)
    elif isinstance(node, list):
        for v in node:
            yield from keys_of(v)


for prefix in sys.argv[1:]:
    for path in sorted(glob.glob(f"{prefix}_explain_*.json")):
        with open(path, encoding="utf-8") as f:
            table = find_table(json.load(f))
        key = table.get("key") or " ∩ ".join(keys_of(table.get("index_merge", {}))) or "-"
        print(f"{path:40} access_type={str(table.get('access_type')):12} key={key:28} rows={table.get('rows')}")
EOF
```

---

## 2-3. 베이스라인 측정 실행

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
zsh run_measure.sh baseline
```

**출력 예 (숫자는 예시):**
```
Enter password:
저장: baseline_explain_q1_hour_func.json
...
저장: baseline_explain_q6_month_range.json
저장: baseline_perf.txt

q1_hour_func	38 rows in set (0.052 sec)
q2_hour_range	38 rows in set (0.049 sec)
q3_day_func	21 rows in set (0.061 sec)
q4_day_range	21 rows in set (0.058 sec)
q5_month_func	96 rows in set (0.410 sec)
q6_month_range	96 rows in set (0.395 sec)
```

**확인할 점:**
- 함수형과 범위형 짝(q1↔q2, q3↔q4, q5↔q6)의 **결과 행 수가 같아야** 합니다. 다르면 범위 조건이 잘못된 것이니 03단계로 가지 말고 원인을 확인하세요.
- `Empty set`이면 그 시각에 기록이 없는 것이니 2-0에서 다른 시각을 고르세요.
- 비밀번호를 틀리면 `Access denied`와 함께 멈춥니다. 다시 실행하세요.

---

## 2-4. EXPLAIN 요약

**아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:**

```bash
python3 summarize_explain.py baseline | tee baseline_explain_summary.txt
```

**운영 예상:** 01단계 사전 측정처럼 `index_merge`(MaestroID ∩ AppID)나 단일 인덱스 접근이 나오고, `rows`는 수천~1만 행 수준입니다. 전체 스캔(`ALL`)이 아닐 수 있으니 나온 값을 그대로 기준값으로 기록하세요.

---

## 2-4-1. 서버 측 실행 시간 (ANALYZE)

2-3의 실행 시간은 Mac ↔ AWS 네트워크 왕복이 포함된 클라이언트 시간입니다. 서버가 쿼리를 처리한 시간(`r_total_time_ms`)을 따로 기록해 두면 네트워크 영향 없이 비교할 수 있습니다.

> 운영 측정 (2026-09-15): 클라이언트 시간 약 90ms 중 서버 처리 시간이 약 75ms였습니다. 6개 쿼리 모두 `index_merge`로 1만여 행을 읽기 때문입니다. `ANALYZE`는 쿼리를 실제로 실행하지만 조회(SELECT)만 하므로 데이터는 바뀌지 않습니다.

**요약 스크립트 만들기:**

```bash
cat > summarize_analyze.py << 'EOF'
# 사용법: python3 summarize_analyze.py baseline [after]
import json
import sys

decoder = json.JSONDecoder()
for prefix in sys.argv[1:]:
    text = open(f"{prefix}_analyze.txt", encoding="utf-8").read()
    names = [line.split("=== ")[1].split(" ===")[0] for line in text.splitlines() if line.startswith("=== ")]
    docs, pos = [], 0
    while True:
        start = text.find("{", pos)
        if start < 0:
            break
        doc, pos = decoder.raw_decode(text, start)
        docs.append(doc)
    for name, doc in zip(names, docs):
        block = doc["query_block"]
        print(f"{prefix:8} {name:16} server_time_ms={block.get('r_total_time_ms')}")
EOF
```

**측정 (비밀번호 한 번 입력):**

```bash
{ cat params.sql
  for q in queries/*.sql; do
    echo "SELECT '=== ${q:t:r} ===';"
    printf 'ANALYZE FORMAT=JSON '
    cat "$q"
  done
} | mariadb -h chocomae.jinaju.com -u jisangs -p chocomae -N -r > baseline_analyze.txt

python3 summarize_analyze.py baseline | tee baseline_analyze_summary.txt
```

---

## 2-5. 결과 기록

**파일로 저장된 결과:**
```
params.sql, queries/*.sql            ← 04단계에서 그대로 재사용 (수정 금지)
run_measure.sh, summarize_explain.py ← 04단계에서 그대로 재사용
baseline_pick_time.txt               ← 측정 기준 시각 선정 근거
baseline_explain_q*.json (6개)       ← EXPLAIN 결과
baseline_explain_summary.txt         ← EXPLAIN 요약
baseline_perf.txt                    ← 실행 시간 (-vvv 전체 출력, 네트워크 포함)
summarize_analyze.py                 ← 04단계에서 그대로 재사용
baseline_analyze.txt                 ← ANALYZE 결과 (서버 측 실행 통계)
baseline_analyze_summary.txt         ← 서버 측 실행 시간 요약
```

2-3 요약과 2-4 결과는 04단계 4-4의 `comparison.txt`에 옮겨 적습니다.

---

## 다음 단계

✅ 베이스라인 측정 완료 → **03-add-indexes.md로 이동**