29초 통계 API 개선: 병렬 쿼리와 날짜 범위 조건

관리자 통계 화면을 조회하는 데 약 29초가 걸렸다.
처음에는 느린 SQL 한 문장을 찾으면 될 거라고 봤다.
쿼리를 펼친 뒤 호출 코드를 따라가면서 생각이 바뀌었다.
데이터베이스가 각 쿼리를 처리하는 시간뿐 아니라, 애플리케이션이 여러 쿼리를 기다리는 순서도 한 요청의 시간을 만든다.

화면에는 서로 다른 통계 두 개가 함께 나왔다.
두 번째 통계는 첫 번째 결과를 사용하지 않았지만, 코드는 첫 번째가 끝난 뒤에야 두 번째를 시작했다.

SQL 쿼리는 데이터베이스에 어떤 데이터를 읽거나 계산할지 요청하는 문장이다.
통계 API 하나도 전체 건수, 평균값, 기간별 합계처럼 여러 쿼리를 실행할 수 있다.

식당에서 서로 다른 두 메뉴를 주문하면서 첫 메뉴를 다 먹은 뒤에야 두 번째 메뉴를 주문하면 주방이 비어 있어도 대기시간이 더해진다.
두 집계가 서로의 결과를 쓰지 않는다면 동시에 요청하고 더 늦게 끝나는 쪽까지만 기다릴 수 있다.

다만 모든 느린 API를 병렬화해서는 안 된다.
먼저 각 쿼리가 왜 오래 걸리는지와 두 결과가 정말 독립적인지를 나눠 봐야 한다.

Promise.all()은 여러 JavaScript 비동기 작업을 먼저 시작하고 모두 끝날 때까지 함께 기다리는 기능이다.
데이터베이스가 실제로 동시에 처리하려면 각 쿼리가 사용할 연결과 서버 자원도 있어야 한다.

기다리는 순서부터 겹칠 수 있었다

두 집계 중 하나의 결과가 다른 쪽 조건으로 쓰이는지부터 확인했다.
의존 관계가 없었기 때문에 애플리케이션에서는 두 database.query() 호출을 먼저 만들고 Promise.all()로 함께 기다리도록 바꿨다.
이 변경은 직렬 await를 없애지만, 데이터베이스에서 두 쿼리가 실제로 겹쳐 실행되는지는 연결 풀과 드라이버 동작에 달려 있다.
그래서 쿼리 시작·종료 시각과 사용한 연결도 함께 확인해야 한다.

type AggregateRow = {
  value?: string | number | null;
};

const [countRows, averageRows] = await Promise.all([
  database.query<AggregateRow[]>(countSql, {
    replacements: { targetId },
  }),
  database.query<AggregateRow[]>(averageSql, {
    replacements: { targetId, startAt, endAt },
  }),
]);

return {
  count: Number(countRows[0]?.value) || 0,
  average: Number(averageRows[0]?.value) || 0,
};

당시 변경에서는 집계값을 Number(value) || 0으로 숫자화했다.
작업을 다시 정리하며 보니 잘못된 문자열이나 빈 결과까지 0으로 숨길 수 있었다.
값이 없을 때 정말 0으로 볼지는 통계의 의미에 맞춰 별도로 정해야 한다.

function parseRequiredFinite(
  value: string | number | null | undefined,
  field: string,
) {
  if (
    value === null
    || value === undefined
    || (typeof value === 'string' && value.trim() === '')
  ) {
    throw new Error(`${field} 집계값이 비어 있습니다.`);
  }

  const number = Number(value);
  if (!Number.isFinite(number)) {
    throw new Error(`${field} 집계값을 숫자로 바꿀 수 없습니다.`);
  }

  return number;
}

function parseNullableFinite(
  value: string | number | null | undefined,
  field: string,
) {
  if (value === null || value === undefined) {
    return null;
  }

  return parseRequiredFinite(value, field);
}

const count = parseRequiredFinite(countRows[0]?.value, 'count');
const average = parseNullableFinite(averageRows[0]?.value, 'average');

COUNT()는 조건에 맞는 행이 없으면 보통 0을 돌려주지만, AVG()는 평균을 낼 행이 없으면 NULL이 된다.
둘을 모두 0으로 바꾸면 “데이터가 없음”과 “평균이 0임”을 구별할 수 없다.
그래서 필수 집계는 잘못된 값을 오류로 드러내고, 빈 결과가 정상인 평균은 null을 유지했다.

Promise.all() 안에서 하나가 실패하면 전체 요청도 실패한다.
다만 먼저 실패한 뒤에도 이미 시작한 다른 쿼리를 자동으로 취소하지는 않는다.
남은 DB 작업의 중단 여부는 드라이버와 별도로 구현한 취소 처리에 달려 있다.
통계 하나만 성공해도 화면에 보여 줘야 한다면 오류 정책을 다르게 설계해야 한다.
동시에 실행하는 쿼리가 많아지면 연결 풀, 곧 애플리케이션이 재사용하려고 일정 개수만 열어 둔 DB 연결과 데이터베이스 CPU가 더 바빠질 수도 있다.
이 사례에서는 서로 독립된 두 집계만 겹쳤고, 실제 실행이 겹쳤는지는 연결 풀의 사용량과 쿼리 타임라인으로 검증 대상으로 남겼다.

같은 요청 안에서 날짜 조건도 살펴봤다

실행 순서를 확인한 뒤 각 쿼리의 조건으로 내려갔다.
날짜 컬럼에 DATE_FORMAT()을 적용해 문자열로 만든 다음 기간을 비교하고 있었다.

인덱스는 정리된 전화번호부와 비슷하다.
성으로 정렬된 책이 있어도 모든 성을 다른 형식으로 바꾼 뒤 비교하면 정렬된 순서를 바로 활용하기 어려울 수 있다.
당시에는 날짜 컬럼의 함수를 제거하고 바인딩된 범위 비교로 바꿨다.
지금 다시 구현한다면 종료 경계의 중복까지 피할 수 있도록 아래처럼 반개구간을 사용한다.

SELECT AVG(metric_value) AS value
FROM sample_metrics
WHERE target_id = :targetId
  AND created_at >= :startAt
  AND created_at < :endAt;

:startAt과 :endAt은 SQL 문자열에 직접 값을 이어 붙이지 않고 데이터만 따로 전달하는 바인딩 파라미터다.
먼저 “하루”가 서비스 기준 시각인지 UTC인지 정하고, 그 기준에서 구한 시작·종료 시각을 데이터베이스 컬럼의 타임존에 맞춰 전달해야 한다.
예시는 시작 시각은 포함하고 다음 구간의 시작은 제외하는 반개구간을 사용했다.
이렇게 하면 자정 경계의 같은 데이터를 두 번 세는 일을 피하기 쉽고, 여름·겨울에 시계가 바뀌는 지역에서도 하루를 무조건 24시간으로 계산하는 실수를 줄일 수 있다.

범위 조건은 MySQL이 인덱스를 검토하기 좋은 형태를 만들 뿐, 인덱스 사용을 보장하지 않는다.
데이터 분포와 복합 인덱스의 순서에 따라 선택은 달라진다.
어느 인덱스를 쓸 계획이고 몇 행을 읽을 것으로 예상하는지는 쿼리의 실행 계획을 보여 주는 EXPLAIN으로 확인해야 한다.

29초에서 분리해 낸 두 가지 병목

실제로 한 해결은 두 가지였다.
서로 의존하지 않는 집계 두 개를 Promise.all()로 함께 요청했고, 날짜 컬럼을 DATE_FORMAT()으로 가공하던 조건을 원본 날짜의 범위 비교로 바꿨다.
전자는 애플리케이션의 불필요한 직렬 대기를 없애고, 후자는 MySQL이 범위 접근과 인덱스를 검토하기 쉬운 조건을 만든다.
Promise.all()은 두 쿼리 중 하나라도 실패하면 전체가 실패한다는 오류 정책까지 포함한다.

두 쿼리의 시간을 A, B라고 하면 직렬 대기는 A + B, 병렬 대기는 경합이 없을 때 대략 max(A, B)다.
관측한 29초가 두 쿼리 대기시간의 합이라면 절감할 수 있는 대기는 min(A, B)이고, 두 시간이 같을 때 상한은 14.5초·50%다.
한 쿼리가 대부분을 차지하면 이득은 작아지고, 동시 실행이 연결 풀이나 DB CPU 경합을 만들면 오히려 느려질 수 있다.
그래서 이 값은 성과 수치가 아니라 병렬화만으로 기대할 수 있는 범위를 정하는 계산이다.

날짜 범위 조건은 먼저 일반 EXPLAIN으로 예상 실행 계획과 선택한 인덱스를 확인한다.
EXPLAIN ANALYZE는 이름과 달리 쿼리를 실제로 실행하므로, 운영 데이터베이스에서 무심코 누르면 안 된다.
스테이징이나 읽기 전용 복제본처럼 부하를 통제할 수 있는 환경에서 변경 전후의 실제 읽은 행 수와 실행 시간을 비교한다.
전체 요청은 직렬 버전, 병렬 버전, 병렬 실행과 범위 조건을 모두 적용한 버전으로 나눠 측정하면 두 변경의 효과가 섞이지 않는다.
p95는 요청 100개를 빠른 순서로 세웠을 때 약 95번째 요청의 시간으로, 평균에 가려진 느린 구간을 확인할 때 함께 본다.

이 방법은 결과가 서로 독립되고, 두 작업을 동시에 실행해도 연결 풀과 DB CPU가 감당할 수 있을 때 적용한다.
두 번째 쿼리가 첫 번째 결과에 의존하거나 동시 실행이 데이터베이스 경합을 키운다면 직렬 실행이 맞다.
날짜 컬럼을 범위로 비교하는 방식은 기간 조회의 기본 후보지만, 최종 판단은 운영과 같은 데이터로 실행 계획과 결과 동일성을 확인한 뒤 내려야 한다.

결론

당시 확인한 문제는 한 가지가 아니었다.
서로 독립적인 두 집계를 애플리케이션이 차례로 기다렸고, 날짜 컬럼을 함수로 가공한 조건은 데이터베이스가 범위 접근을 검토하기 어려운 모양이었다.

나는 독립 쿼리를 Promise.all()로 함께 시작하고, 날짜 조건을 created_at >= :startAt AND created_at < :endAt 형태의 반개구간으로 바꿨다.

29초가 최종적으로 몇 초가 됐는지는 남아 있는 측정값이 없어 성과 수치로 만들지 않았다.
대신 두 쿼리 시간이 같다는 가장 좋은 가정에서도 병렬화만으로 줄일 수 있는 대기는 최대 50%라는 계산 경계를 남겼다.

느린 API에서는 SQL 한 줄만 고치기 전에 요청 안의 대기 순서를 그리고, 쿼리 자체의 실행 계획과 애플리케이션의 직렬 대기를 따로 측정해야 한다.

참고한 공식 문서

• MDN — Promise.all()
• MySQL 8.4 — Range Optimization
• MySQL 8.4 — EXPLAIN Statement
• MySQL 8.4 — Optimization and Indexes