대시보드는 사용자가 선택한 기간과 겹치는 웹 사용 기록을 조회해 스크린타임과 카테고리 통계를 만듭니다.
그중 카테고리 분석은 조회된 기록을 카테고리와 웹사이트 단위로 다시 묶기 때문에, 기간 조회가 느려지면 API 전체 응답 시간도 함께 늘어납니다.
서비스 초기에는 빠르게 동작했지만, 사용 기간이 길어진 계정일수록 오늘 하루를 조회하는 API도 점점 느려지는 현상이 나타났습니다.

현재 구현의 기간 조회 조건은 다음과 같습니다.

return dsl
    .select(...)
    .from(HISTORY)
    .join(WEBSITE)
    .on(WEBSITE.ID.equal(HISTORY.WEBSITE_ID))
    .where(
        HISTORY.USER_ID.equal(userId)
            .and(HISTORY.VISITED_AT.lessThan(endedAt))
            .and(HISTORY.CLOSED_AT.greaterThan(startedAt))
    )
    .orderBy(HISTORY.VISITED_AT)
    .fetchInto()

기록이 조회 기간과 조금이라도 겹치면 집계해야 합니다.
예를 들어 전날 23시 50분에 시작해 오늘 00시 10분에 끝난 기록도 오늘의 스크린타임 10분에 포함되어야 하므로, 단순히 visited_at BETWEEN ...으로 조회할 수 없습니다.

데이터가 쌓일수록 느려진 이유

기존 스키마에는 (user_id, visited_at) 복합 인덱스가 있었습니다.

CREATE INDEX idx_history_user_id_visited_at ON history (user_id, visited_at);

쿼리 조건에도 user_id와 visited_at이 있으므로 처음에는 적합한 인덱스처럼 보였습니다.
하지만 오늘 기준으로 쿼리를 해석하면 조건은 다음과 같습니다.

• user_id가 현재 사용자와 같음
• visited_at이 내일 00:00보다 작음
• closed_at이 오늘 00:00보다 큼

visited_at이 내일 00:00보다 작은 조건은 해당 사용자가 서비스를 시작한 날부터 오늘까지의 거의 모든 기록을 포함합니다.
따라서 MySQL은 (user_id, visited_at) 인덱스로 현재 사용자의 과거 기록을 넓게 읽은 뒤, 테이블 행의 closed_at을 확인해 오늘 이전에 끝난 기록을 버려야 했습니다.

사용 기간을 D, 하루 평균 기록 수를 H라고 하면 하루 조회임에도 인덱스 후보 범위는 대략 D × H까지 커집니다.
조회하려는 기간은 하루로 고정되어 있는데 비용은 계정의 전체 사용 기간과 함께 증가한 것입니다.

EXPLAIN으로 확인하기

먼저 EXPLAIN ANALYZE로 옵티마이저가 고른 인덱스와 실제 실행을 확인했습니다.

EXPLAIN ANALYZE
SELECT history.*
FROM history
WHERE user_id = UNHEX(REPLACE(:userId, '-', ''))
  AND visited_at < :endedAt
  AND closed_at > :startedAt;

핵심은 범위 조회를 사용한다는 사실 자체가 아니었습니다.
범위 스캔을 사용하더라도 그 범위가 넓으면 많은 인덱스 엔트리와 테이블 행을 읽습니다.

• key: idx_history_user_id_visited_at
• access: user_id = ? AND visited_at < ?
• filter: closed_at > ?

실행 계획에서 closed_at은 인덱스 접근 조건이 아니라 사후 필터 조건이었습니다.
예상 행 수와 실제 행 수도 사용자의 누적 기록에 가까운 수로 증가했습니다.

종료 시각으로 범위를 좁히기

같은 쿼리를 closed_at 관점에서 보면 조건의 선택도가 달라집니다.

즉, closed_at이 오늘 00:00보다 큰 조건은 오늘 이후에 끝난 기록만 후보로 남깁니다.

과거 3년의 기록은 이미 closed_at이 오늘 00:00 이하이므로 인덱스 탐색 단계에서 제외할 수 있습니다.

이에 (user_id, closed_at) 복합 인덱스를 추가했습니다.

CREATE INDEX idx_history_user_id_closed_at
    ON history (user_id, closed_at);

변경 후 실행 계획에서는 다음 접근 경로를 사용할 수 있었습니다.

• key: idx_history_user_id_closed_at
• access: user_id = ? AND closed_at > ?
• filter: visited_at < ?

visited_at < endedAt은 여전히 필터로 확인해야 합니다.
하지만 서비스 데이터에서는 미래에 시작한 기록이 거의 없기 때문에, closed_at > startedAt로 좁힌 후보 대부분이 실제 결과가 됩니다.

핵심은 조건에 등장하는 컬럼을 무조건 인덱스에 넣는 것이 아니라, 조회 시점에 어느 부등식이 더 작은 범위를 만드는지를 확인한 것입니다.

왜 단일 컬럼 인덱스가 아닌가

closed_at만 인덱싱하면 모든 사용자의 최근 기록이 같은 범위에 들어옵니다.
대시보드 조회는 항상 한 사용자를 대상으로 하므로 동등 조건인 user_id를 선두에 두었습니다.

즉, (user_id = ?) AND (closed_at > ?) 형태로 먼저 특정 사용자의 인덱스 구간을 찾고, 그 안에서 종료 시각 범위를 탐색할 수 있습니다.

(closed_at, user_id) 순서라면 먼저 전체 사용자의 시간 범위를 읽은 뒤 사용자를 걸러야 하므로 이 조회 패턴에는 맞지 않습니다.

성능 측정

로컬 MySQL에 사용자 한 명의 기록을 일 평균 20건씩 3년치 저장했습니다.
쿼리와 커넥션을 워밍업한 뒤, 오늘과 겹치는 기록을 조회하는 쿼리를 100회 순차 실행해 평균을 비교했습니다.

3년 전체를 조회할 때는 두 인덱스 모두 넓은 범위를 읽어야 하므로 개선 폭이 작았습니다.
반면 오늘처럼 전체 데이터 중 극히 일부만 필요한 요청에서는 종료 시각 인덱스가 후보 범위를 크게 줄였고, 평균 실행 시간이 134.0ms에서 0.5ms로 99.6% 감소했습니다.

이 결과는 “closed_at 인덱스가 항상 더 빠르다”는 뜻은 아닙니다.
조회 범위와 데이터 분포에 따라 옵티마이저의 선택은 달라질 수 있습니다.
그래서 운영 적용 전에는 다음을 함께 확인했습니다.

1. 실제 대시보드 쿼리의 시간 조건
2. 사용자별 기록 누적 분포
3. EXPLAIN ANALYZE의 선택 인덱스와 실제 읽은 행 수
4. 인덱스 추가 이후 기록 생성 성능의 회귀 여부

인덱스 추가 비용

인덱스는 읽기 성능을 무료로 얻는 장치가 아닙니다.
기록을 생성할 때마다 (user_id, closed_at) B-Tree도 함께 갱신되며 디스크 공간도 사용합니다.

처음에는 기존 (user_id, visited_at) 인덱스를 바로 제거하는 방안도 생각했습니다.
하지만 이 인덱스는 사용자 기록의 시작 시각 순 조회와 중복 기록 검사 등 다른 쿼리에서도 사용될 수 있습니다.
특정 쿼리 하나의 실행 계획만 보고 기존 인덱스를 제거하지 않고, 각 인덱스가 담당하는 조회 패턴을 분리했습니다.

CREATE INDEX idx_history_user_id_visited_at
    ON history (user_id, visited_at);
 
CREATE INDEX idx_history_user_id_closed_at
    ON history (user_id, closed_at);

쓰기 비용과 저장 공간이 실제 병목으로 확인되면 중복 인덱스 여부를 다시 평가할 수 있습니다.
현재 트래픽에서는 기간 조회의 99.6% 개선이 추가 인덱스 유지 비용보다 컸습니다.

정리

이번 문제는 인덱스가 없어서가 아니라 인덱스가 있어도 읽는 범위가 데이터와 함께 계속 커지는 문제였습니다.

(user_id, visited_at)은 문법상 쿼리 조건을 지원했지만, 오늘 조회에서 visited_at < endedAt은 3년치 기록을 포함하는 넓은 범위였습니다.
(user_id, closed_at)으로 접근 경로를 바꾸자 이미 종료된 과거 기록을 인덱스 단계에서 제외할 수 있었습니다.

인덱스 최적화에서는 Using index나 type=range 같은 표시만 보는 것보다, 실제 데이터 분포에서 그 range가 얼마나 넓은지를 확인하는 것이 더 중요하다는 점을 배웠습니다.