[DB/Cloudflare D1] 관리자 30일 통계 쿼리 개선: 반복 스캔을 날짜별 집계로 줄이기 | 개발일지

문제와 원인

관리자 30일 통계 쿼리는 날짜마다 DAU·MAU·SQL·SW 풀이의 상관 서브쿼리로 원시 테이블을 다시 읽었다. SQL 호출은 1회지만 날짜 변환과 세션 중복 제거가 30개 날짜에서 반복돼 데이터가 늘수록 처리 시간이 커졌다.

수정 전 코드

admin-read-use-cases.ts

SELECT
  day,
  (
    SELECT COUNT(DISTINCT e.anonymous_session_id)
    FROM analytics_events e
    WHERE e.event_type = 'page_view'
      AND date(datetime(e.occurred_at, '+9 hours')) = day
  ) AS dau,
  (
    SELECT COUNT(DISTINCT e.anonymous_session_id)
    FROM analytics_events e
    WHERE e.event_type = 'page_view'
      AND date(datetime(e.occurred_at, '+9 hours'))
        BETWEEN date(day, '-29 days') AND day
  ) AS mau
FROM days
ORDER BY day;

수정 후 코드

adminActivityTrendQuery

출력 30일과 첫 출력일의 MAU에 필요한 선행 29일을 합친 59일의 방문을 KST 날짜·세션별로 축약했다. visitor_days를 MATERIALIZED로 두어 DAU·MAU가 재사용하고, SQL·SW 풀이도 일별 집계 후 UNION ALL과 합계로 결합했다.

visitor_days AS MATERIALIZED (
  SELECT date(datetime(e.occurred_at, '+9 hours')) AS day,
    e.anonymous_session_id AS session_id
  FROM analytics_events e, bounds
  WHERE e.event_type = 'page_view'
    AND date(datetime(e.occurred_at, '+9 hours'))
      BETWEEN visitor_start AND end_day
  GROUP BY day, e.anonymous_session_id
),
daily_visitors AS MATERIALIZED (
  SELECT day, COUNT(DISTINCT session_id) AS dau
  FROM visitor_days GROUP BY day
)

MAU는 각 날짜의 30일 구간에서 DISTINCT 세션을 세어 여러 날 방문한 세션의 중복을 제거한다.

SELECT COUNT(DISTINCT visitor_days.session_id)
FROM visitor_days
WHERE visitor_days.day BETWEEN date(days.day, '-29 days') AND days.day

SQL 본문의 원시 테이블 참조는 4곳→3곳, 통계 조회는 SQL 1개를 유지했다. 날짜별 MAU 계산의 대상은 원시 이벤트에서 축약된 visitor_days로 바뀌었다.

검증 결과

로컬 SQL 실행 시간

Node.js·SQLite의 같은 합성 DB에서 원본 전후 SQL을 각각 15회 실행했다. 관리자 제외 조건에서 전체 결과 30행의 일치를 확인하고 중앙값을 비교했다.

규모	데이터 구성	수정 전 중앙값	수정 후 중앙값	감소량	감소율
1배 · 총 4,200행	페이지 조회 2,400행 + SQL 풀이 1,200행 + SW 풀이 600행	29.836ms	14.912ms	14.924ms	50.0%
5배 · 총 21,000행	페이지 조회 12,000행 + SQL 풀이 6,000행 + SW 풀이 3,000행	151.980ms	82.120ms	69.860ms	46.0%

같은 입력의 SQL 재실행

Swagger UI에서 같은 원본 SQL 측정 명령을 다시 실행했다. 로컬 SQLite의 합성 DB에서 관리자 제외 조건으로 각 SQL을 15회 실행했고, 두 규모의 집계 결과는 각각 30행 모두 일치했다.

규모	합성 데이터 행 수	수정 전 중앙값	수정 후 중앙값	감소율
1배	4,200행	40.732ms	19.277ms	52.7%
5배	21,000행	314.694ms	206.042ms	34.5%

같은 SQL 재실행: 각 15회 중앙값과 30행 결과 일치원본 보기 (새 창)

집계 결과 비교

조건	결과
1배 합성 데이터	전체 30행 동일 · 응답 JSON 1,890B 동일
5배 합성 데이터	전체 30행 동일 · 응답 JSON 1,980B 동일
KST 자정·월 경계	해당 날짜의 DAU·MAU·풀이 수 유지
관리자 포함·제외	두 조건 모두 이전 결과와 동일
같은 세션의 같은 날 반복 방문	세션 중복 제거 유지
서술형 SQL 풀이	객관식 풀이 수에서 제외
늦게 도착한 새로운 세션 이벤트	해당 날짜 DAU 1 증가
페이지 조회 실행계획	기존 covering index 사용 가능