한국 시간으로 집계하고 UTC 인덱스 쓰기

얼쑤의 성장 화면은 한국 시간으로 하루의 연습 시간을 보여줍니다. DB에는 연습 구간이 시작된 시각을 UTC로 저장합니다. 한국 시간 월요일 자정은 DB에서 일요일 15시이므로, 저장된 날짜를 그대로 묶으면 월요일에 연습한 기록이 지난주에 들어갑니다. 하루를 한국 시간으로 나누면서도, 조회할 회원과 기간은 인덱스로 좁힐 수 있게 쿼리를 구성했습니다.

UTC 경계값으로 범위 조회

서버는 사용자가 요청한 첫날의 한국 시간 자정과 마지막 날 다음 날의 자정을 각각 UTC로 바꿉니다. 예를 들어 10월 26일 하루를 조회하면 10월 25일 15:00 UTC 이상, 10월 26일 15:00 UTC 미만이 범위입니다. 마지막 시각을 다음 날 시작 미만으로 두면, 마지막 초에 들어 있는 마이크로초도 포함됩니다.

DB에서는 회원 ID가 일치하고 시작 시각이 그 범위에 드는 행을 찾습니다. WHERE에서 시작 시각 열에 날짜 함수를 씌우지 않아 (user_id, started_at) 인덱스로 범위를 찾을 수 있습니다. 범위에 들어온 행을 날짜별로 묶을 때만 9시간을 더합니다. 아래는 UTC로 저장하고 DB 연결 시간대도 UTC로 맞춘 환경에서 쓴 쿼리의 형태입니다.

SELECT DATE(DATE_ADD(started_at, INTERVAL 9 HOUR)) AS day,
       SUM(counted_seconds) AS total
FROM score_views
WHERE user_id = ?
  AND started_at >= ?
  AND started_at < ?
GROUP BY day

이 통계는 연습 구간을 시작한 날에 넣습니다. 자정을 넘겨 끝난 구간도 시작일에 집계되는 계약이라, 시작 시각만으로 어느 날의 합에 넣을지 결정할 수 있습니다.

자정 양쪽의 기록을 HTTP로 검증

임시 MySQL 8.4에 월요일 자정 바로 전과 자정 정각에 시작한 구간을 넣었습니다. 시작 시각의 차이는 1마이크로초입니다. 백엔드를 띄운 뒤 실제 HTTP로 월요일부터 일요일까지의 통계를 조회했습니다.

지난주 요약에는 일요일 기록의 100초가 들어갔고, 월요일 달력에는 200초가 표시됐습니다. 다른 회원의 기록을 같이 넣어도 요청한 회원의 결과에는 섞이지 않았습니다.

연습 횟수와 평균 점수의 분모

같은 요약의 연습 횟수와 점수는 별도의 쿼리로 집계했습니다. 분석을 마친 DONE 세션에 평가를 LEFT JOIN하고, 연습 횟수에는 COUNT(*), 평균의 분모에는 평가 ID가 있는 행만 세는 COUNT(e.id)를 썼습니다. 세션당 평가가 최대 한 건이라는 유일 제약이 있어 평가를 붙여도 세션 수가 늘지 않습니다. 평가가 한 건도 없으면 평균은 null로 응답합니다.

같은 HTTP 통합 테스트에서 요청한 회원의 이번 주 DONE 세션은 3건이었고, 그중 2건에 85점과 86점의 평가를 넣었습니다. 요약은 연습 횟수 3회와 평균 86점을 반환했습니다. 평균은 평가가 있는 두 건으로 나눈 85.5를 HALF_UP으로 반올림한 값입니다.

데이터 2만 건에서 달라진 인덱스

고정 데이터가 몇 행뿐인 DB에서 EXPLAIN을 실행하자 MySQL은 중복 키를 막는 데 쓰는 다른 인덱스를 골랐습니다. 자동 테스트는 실제 선택을 고정하는 대신, 회원과 시작 시각의 복합 인덱스가 possible_keys에 들어 있는지 확인하도록 했습니다.

별도로 회원 50명, 연습 구간 2만 건을 넣은 DB에서 ANALYZE TABLE로 통계를 갱신하고 실행 계획을 확인했습니다. 이 조건에서는 MySQL이 (user_id, started_at) 인덱스를 선택해 범위를 조회했습니다. EXPLAIN의 rows는 11행이었습니다. 이 값은 실행 계획이 예상한 행 수입니다. MySQL의 EXPLAIN 출력 설명

자정 경계 테스트는 날짜별 합이 맞는지를, 2만 건 실험은 그 데이터 분포에서 어떤 인덱스가 선택됐는지를 확인했습니다. 조회 시간과 운영 데이터의 실행 계획은 이 실험의 측정 범위 밖입니다.