100만 건을 엑셀로 내려받다 — 느렸던 건 쿼리가 아니라 자료구조였습니다

운영팀이 거래내역 한 달치를 엑셀로 받으려고 했습니다. 약 100만 건입니다.

화면에는 이렇게 떴습니다.

다운로드 시간이 초과되었습니다. 잠시 후 다시 시도해 주세요.

"잠시 후 다시 시도"해도 같은 결과였습니다. 당연합니다 — 데이터가 줄어들 리 없으니까요.

처음 세운 가설은 "쿼리가 느리다"였고, 맞았습니다. 실제로 조회에서 시간을 크게 잡아먹는 지점이 셋 있었고 고쳤습니다. 그런데 고치고 나서 계산해보니 쿼리를 아무리 빠르게 만들어도 100만 건은 통과하지 못한다는 결론이 나왔습니다. 병목이 조회가 아니라 그 뒤에 있었기 때문입니다.

결론부터 요약합니다.

• 증상: 60초 타임아웃. 그런데 그 60초는 서버가 실패한 시각이 아니라 화면이 포기한 시각이었습니다
• 1차 원인: 행마다 비밀 저장소를 네트워크로 호출, 날짜 조건의 컬럼 캐스팅, 같은 조건을 두 번 도는 2단계 조회 — 전부 조회 단계
• 진짜 벽: 같은 데이터를 파이프라인 네 곳에서 각각 펼치고 있었습니다. 전부 건수에 비례하는 자료구조였고, 100만 건에서는 어느 한 곳도 버티지 못합니다
• 상태: 조회 최적화와 왕복 제거는 반영했고(운영 배포·검증 전), 구조 전환은 설계까지 왔습니다

1️⃣ 60초는 서버가 실패한 시각이 아니었습니다

다운로드는 비동기 Job 구조였습니다. 작업을 만들고, 진행률을 폴링하고, 끝나면 결과를 받습니다.

폴링 훅에는 이런 코드가 있었습니다.

const TIMEOUT_MS = 60000;

timerRef.current = setInterval(async () => {
  if (Date.now() - startedAt > TIMEOUT_MS) {
    fail('다운로드 시간이 초과되었습니다. 잠시 후 다시 시도해 주세요.');
    return;
  }
  // ... 상태 조회
}, 500);

60초가 지나면 서버 상태와 무관하게 실패로 처리합니다. 그동안 서버 워커는 멀쩡히 돌고 있고, 작업이 끝나면 결과도 정상적으로 저장됩니다. 다만 화면이 이미 포기했으니 그걸 가져갈 사람이 없을 뿐입니다.

메시지도 사실과 달랐습니다. "시간이 초과되었습니다"는 작업이 끝났다는 인상을 주지만, 실제로는 진행 중이었습니다. 그리고 사용자가 취할 수 있는 행동(기간을 줄인다)을 알려주지 않았습니다.

여기서 얻은 건 수정 사항이 아니라 문제 정의였습니다. "60초 안에 못 끝낸다"가 문제이고, "60초에 끊긴다"는 그 증상입니다. 타임아웃 값을 늘리는 건 증상만 가리는 일입니다.

2️⃣ 조회가 느렸던 세 가지 — 하나는 자료구조 문제였습니다

조회 어댑터를 열어보니 시간을 먹는 지점이 셋이었습니다. 앞의 둘은 흔한 실수라 짧게만 적습니다.

행마다 비밀 저장소를 호출하고 있었습니다. 카드번호가 암호화되어 있어 복호화가 필요한데, 복호화 함수 안에서 매번 키를 조회했습니다.

private String decryptCardNo(String encrypted) throws Exception {
    String key = this.loadSecretKey();   // ← 행마다 네트워크 왕복
    SecretKey secretKey = new SecretKeySpec(hexToBytes(key), "AES");
    // ...
}

키는 조회 내내 동일한데도 그랬습니다. 100만 건이면 왕복 100만 번입니다. 키를 한 번만 확보해 넘기도록 바꿨습니다.

주의할 점이 하나 있습니다. "그럼 필드에 캐싱하면 되지 않나" 싶지만, 그러면 저장소에서 키가 교체됐을 때 낡은 키를 계속 씁니다. "조회 1회 = 키 1회" 를 기준선으로 삼고, 그 범위 밖으로는 들고 나가지 않기로 했습니다.

날짜 조건이 컬럼을 문자열로 캐스팅하고 있었습니다.

// 컬럼을 CAST(... AS CHAR) 로 감싸면 인덱스를 못 탄다
booleanBuilder.and(resultTime.stringValue().goe(startText))
              .and(resultTime.stringValue().lt(endText));

조건의 컬럼 쪽에 함수를 씌우면 인덱스가 죽습니다. 파라미터 쪽만 맞춰주면 되는 일이었습니다. 여기서 함정이 하나 있었는데, 뒤에서 따로 적겠습니다.

세 번째가 이 글의 주제와 이어집니다. 조회가 2단계였습니다.

List<Long> ids = queryFactory.select(p.id)
    .from(p) /* ... 조인 6개 ... */
    .where(condition)
    .fetch();                          // 대상 id를 전부 메모리로

return queryFactory.select(projection)
    .from(p) /* ... 같은 조인 ... */
    .where(p.id.in(ids))               // 그 id를 통째로 IN 절에
    .fetch();

같은 조건으로 같은 조인을 두 번 돕니다. 그리고 두 번째 쿼리의 IN 절에 id가 전부 들어갑니다. 100만 건이면 IN 목록 원소가 100만 개입니다.

MySQL은 IN 목록을 정렬해 이진 탐색하지만, 그 전에 파서가 100만 개의 리터럴을 읽어 트리로 만들어야 합니다. 쿼리 문자열 자체가 수 MB가 되고 max_allowed_packet에 걸릴 수도 있습니다. 조건을 두 번 평가하는 비용보다 IN 목록이라는 자료구조 자체가 더 비쌌습니다.

두 쿼리의 조건이 같고 조인이 전부 inner join이라 결과 집합이 동일하다는 걸 확인하고, 단일 쿼리로 합쳤습니다.

Before

After

여기까지가 "쿼리를 고쳤다"입니다. 그리고 여기서 멈췄으면 100만 건은 여전히 실패했을 겁니다.

3️⃣ 이미 JSON인 것을 객체로 풀었다가 다시 JSON으로

결과를 내려주는 컨트롤러입니다.

public ResponseEntity<?> result(@PathVariable String jobId) {
    String json = getUseCase.getResultJson(jobId);            // ① 이미 JSON 문자열
    Object data = objectMapper.readValue(json, Object.class); // ② 객체 그래프로 역직렬화
    return ResponseEntity.ok(BaseResponse.success(data));     // ③ 다시 JSON으로 직렬화
}

캐시에 이미 JSON 문자열로 저장되어 있는 결과를, 객체로 풀었다가, 응답을 쓰면서 다시 문자열로 만듭니다. ②에서 만든 객체는 ③이 끝나면 그대로 버려집니다. 아무 데도 쓰이지 않습니다.

단건 조회에서는 아무도 눈치채지 못할 코드입니다. 문제는 이게 버릴 목적으로 만드는 자료구조의 크기입니다.

readValue(json, Object.class)는 JSON 객체를 LinkedHashMap으로 만듭니다. 행 하나가 필드 11개라면 LinkedHashMap 하나에 엔트리가 11개 들어갑니다. 이게 얼마나 되는지 뜯어보면:

키 문자열은 다행히 공유됩니다. Jackson은 심볼 테이블로 필드명 String을 재사용하므로 "transactionNo"가 100만 개 생기지는 않습니다. 하지만 엔트리 객체는 공유되지 않습니다. 행마다 11개씩, 100만 행이면 1,100만 개의 Entry 객체입니다.

행당 1KB로 잡으면 100만 건에서 약 1GB입니다. 이 서비스의 운영 힙은 1GB, 테스트 환경은 256MB입니다. 버릴 데이터를 만들다가 힙을 다 쓰는 셈입니다.

고치는 방법은 허무할 만큼 간단했습니다. 이미 JSON이면 그대로 흘려보내면 됩니다.

@Getter
public static class RawResultResponse {
    private final String status;

    @JsonRawValue          // 이스케이프 없이 본문에 그대로 박힌다
    private final String data;

    private final String timeStamp;
}

응답 JSON의 모양이 종전과 완전히 같으므로 호출하는 쪽은 한 줄도 바뀌지 않습니다. 역직렬화를 안 하는 것만으로 1,100만 개의 객체 생성이 사라집니다.

그리고 같은 낭비가 프론트 프록시에도 있었습니다.

// 백엔드 응답을 통째로 파싱해서 JS 객체로 만들고, 다시 직렬화한다
return NextResponse.json(await response.json(), { status: response.status });

이쪽이 더 위험했습니다. 프록시는 Node 힙이 512MB로 제한되어 있고, 초과하면 프로세스 매니저가 워커를 재시작합니다. 그런데 cluster 모드로 떠 있습니다. 즉 대용량 응답 하나가 워커를 죽이면, 그 워커가 처리 중이던 다른 사용자의 요청까지 함께 끊깁니다. 다운로드 실패로 끝나지 않고 남의 화면이 깨지는 문제입니다.

본문 스트림을 그대로 통과시키도록 바꿨습니다.

return new NextResponse(response.body, {
  status: response.status,
  headers: { 'Content-Type': responseContentType ?? 'application/json' }
});

에러 처리가 깨지지 않는지도 확인했습니다. 클라이언트의 응답 파서는 response.ok가 아닐 때 본문을 파싱하지 않고 상태 코드로 에러 객체를 합성합니다. 오히려 백엔드가 JSON이 아닌 본문(HTML 에러 페이지 등)을 줄 때 프록시에서 파싱 예외로 죽지 않게 되어 더 안전해졌습니다.

4️⃣ 100만 건에서 무너지는 건 코드가 아니라 데이터의 모양입니다

여기까지 고치고 100만 건 기준으로 다시 계산했습니다. 그리고 여전히 안 된다는 결론이 나왔습니다.

파이프라인을 따라가며 같은 데이터가 몇 번 펼쳐지는지 세어봤습니다.

네 번입니다. 그리고 전부 건수에 비례합니다. 앞에서 ③의 백엔드 쪽 왕복을 없앴지만, 나머지는 그대로입니다.

행 지향 Map은 키를 100만 번 반복해서 씁니다

가장 먼저 눈에 들어온 건 payload의 모양이었습니다. 결과가 List<Map<String, Object>> 였으므로 JSON은 이렇게 나갑니다.

[
  {"merchantMid":"...","terminalMid":"...","date":"20260812","paymentType":"PAY", ...},
  {"merchantMid":"...","terminalMid":"...","date":"20260812","paymentType":"CANCEL", ...}
]

필드명이 행마다 반복됩니다. 필드 11개의 이름과 구두점을 합치면 행당 약 126바이트, 값이 약 120바이트입니다. 즉 payload의 절반이 키 이름입니다. 100만 건이면 250MB 중 126MB가 같은 문자열 11종의 반복입니다.

열 지향으로 바꾸면 키를 한 번만 씁니다.

{
  "columns": ["merchantMid", "terminalMid", "date", "paymentType", "..."],
  "rows": [
    ["...", "...", "20260812", "PAY", "..."],
    ["...", "...", "20260812", "CANCEL", "..."]
  ]
}

같은 정보인데 payload가 절반 이하가 됩니다. 사내에서 병렬로 진행되던 다른 추출 기능이 이 방식으로 3만 건에서 13MB → 5.5MB를 만들었습니다. 직렬화 방식이나 압축을 건드린 게 아니라, 데이터의 모양만 바꾼 결과입니다.

다만 100만 건에서는 이걸로도 부족합니다. 250MB가 110MB가 될 뿐, 여전히 힙과 브라우저를 통과해야 하니까요. payload를 줄이는 방향으로는 한 자릿수 배수밖에 못 벌고, 우리에게 필요한 건 자릿수 자체를 바꾸는 것이었습니다.

브라우저에서 엑셀을 만드는 이상 방법이 없습니다

마지막 단계가 결정적이었습니다. 엑셀 생성은 브라우저에서 JS 라이브러리로 하고 있었는데, 이 라이브러리는 시트를 셀 주소를 키로 하는 딕셔너리로 들고 있습니다. "A1", "B1" 같은 문자열 키에 {t: 's', v: '값'} 형태의 셀 객체가 하나씩 붙습니다.

100만 행 × 11열이면 1,100만 개의 셀 객체와 1,100만 개의 주소 문자열입니다. 여기에 순수 JS이므로 메인 스레드에서 동기 실행됩니다. 탭이 얼어붙습니다.

이건 최적화로 줄일 수 있는 성질이 아닙니다. 자료구조가 그렇게 생겼습니다.

그리고 엑셀 자체에 벽이 있습니다

계산하다 알게 된 사실인데, xlsx 시트 한 장의 최대 행 수는 1,048,576행입니다. 2의 20제곱입니다. 열은 16,384(2의 14제곱)입니다. 임의로 정한 숫자가 아니라 포맷이 행 인덱스에 할당한 비트 수입니다.

한 달치가 100만 건이면 헤더 한 줄을 더해 1,000,001행. 한계까지 4만 8천 행 남았습니다. 거래가 조금만 늘거나 기간이 하루만 길어져도 시트 분할 없이는 파일 자체가 만들어지지 않습니다.

부끄러운 이야기를 덧붙이면, 처음에 10만 건을 가정하고 쓴 설계 문서에는 이 한계가 한 줄도 없었습니다. 10만에서는 존재하지 않는 문제였고, 규모가 바뀌면 없던 제약이 생긴다는 걸 놓쳤습니다.

그래서 서버에서 만들기로 했습니다

방향은 하나로 좁혀졌습니다. payload를 줄이는 게 아니라 없애는 것 — 서버에서 파일을 만들어 스트리밍으로 내려줍니다.

Before

After

여기서 자료구조 선택이 셋 있었습니다.

첫째, SXSSF의 슬라이딩 윈도우. 일반 워크북 구현은 시트 전체를 메모리에 객체 트리로 들고 있습니다. 100만 행이면 그대로 힙에 올라갑니다. SXSSF는 최근 N행만 메모리에 두고 윈도우를 벗어난 행은 임시 파일로 밀어냅니다.

공짜는 아닙니다. 이미 밀어낸 행에는 다시 접근할 수 없습니다. 구체적으로 열 너비 자동 맞춤은 모든 행의 내용을 봐야 계산되므로, 윈도우 방식에서는 쓸 수 없습니다. 되돌아가 이전 행을 수정하는 것도 안 됩니다. 랜덤 접근을 포기하고 상수 메모리를 얻는 거래입니다. 이 제약을 알고 들어가야 나중에 "왜 자동 너비가 안 되지"에서 시간을 안 씁니다.

그리고 임시 파일은 명시적으로 정리해야 합니다. 정리를 빠뜨리면 서버 디스크에 조각이 쌓이는데, 그 안에 카드번호가 들어 있습니다. 그래서 워크북 수명 주기를 얇은 인터페이스 뒤에 가두고 각 기능이 직접 다루지 못하게 했습니다. 기능마다 정리 코드를 반복하면 언젠가 하나는 빠집니다.

둘째, 깊은 offset 대신 id 청크. 청크로 나눠 읽는다면 OFFSET n LIMIT m이 가장 먼저 떠오릅니다. 그런데 마지막 페이지는 OFFSET 995000입니다. DB는 앞의 99만 5천 행을 만들어서 버려야 합니다. 페이지가 뒤로 갈수록 느려지고, 전체로 보면 작업량이 제곱에 가깝게 늘어납니다.

그래서 대상 id를 먼저 확보하고 5,000개씩 잘라 IN으로 조회하기로 했습니다. 2️⃣에서 없앤 구조와 비슷해 보이지만 다릅니다. 없앤 건 100만 개를 한 번에 IN에 넣던 것이고, 여기서는 5,000개씩 나눠 넣습니다. 5,000개 IN은 기본 키 인덱스를 타서 빠릅니다. 목적이 "쿼리 중복 제거"에서 "메모리 상수화"로 바뀌었기 때문에 같은 재료로 다른 답이 나왔습니다.

셋째, List<Long>의 박싱. id 목록을 들고 있어야 하는데, 이게 100만 개입니다.

Long.valueOf의 캐시는 -128~127뿐이라 실제 id는 전부 새 객체입니다. 10만 건이었다면 2MB 대 0.8MB로 신경 쓸 값이 아니었지만, 100만 건에서는 20MB와 8MB의 차이입니다. 1GB 힙에서 20MB가 치명적이진 않지만, 공짜로 없앨 수 있는 2.5배 낭비입니다. 규모가 바뀌면 무시해도 되던 것이 무시할 수 없게 됩니다.

이 셋을 합치면 힙에 살아있는 건 id 배열과 청크 하나뿐입니다. 점유량이 건수와 무관해집니다. 테스트 환경 256MB에서도 도는 게 목표입니다.

함정 하나 — "정리"하려다 9시간 밀릴 뻔했습니다

날짜 조건을 고칠 때 이야기입니다.

컬럼 캐스팅을 없애는 가장 자연스러운 방법은 컬럼 타입에 맞춰 비교하는 것입니다. 이 컬럼은 엔티티에서 Instant로 매핑되어 있으니 Instant로 비교하면 깔끔합니다. 코드 리뷰에서도 그렇게 지적받기 좋은 모양입니다.

그런데 이 서비스의 기동 옵션에 -Duser.timezone=Asia/Seoul이 있고, ORM의 JDBC 타임존 설정은 없습니다. 이 조합에서 Instant가 어느 기준으로 변환되어 바인딩될지는 환경에 달려 있습니다. 어긋나면 조회 결과가 9시간 밀린 채로 나갑니다.

빌드도 통과하고 화면도 정상으로 보입니다. 건수만 조금 다릅니다. 정산 근거로 쓰이는 자료에서는 최악의 실패 모드입니다.

그래서 타임존 변환이 아예 개입하지 않는 LocalDateTime으로 비교하고, 코드에서 UTC로 맞춘 값을 그대로 넘기도록 했습니다. 컬럼을 함수로 감싸지 않으므로 인덱스는 그대로 탑니다.

// 컬럼은 그대로 두고 파라미터만 맞춘다.
// LocalDateTime 은 타임존 변환 없이 바인딩되므로 기준이 어긋날 수 없다.
DateTimeExpression<LocalDateTime> resultTime =
    Expressions.dateTimeTemplate(LocalDateTime.class, "{0}", column);

booleanBuilder.and(resultTime.goe(utcStart)).and(resultTime.lt(utcEnd));

설계 문서에 "고치지 말아야 할 것" 절을 따로 만들어 이 판단을 근거와 함께 적어뒀습니다. 주석 없이 두면 다음 사람이 반드시 "정리"할 모양이기 때문입니다.

마치며 — 어디까지 왔고 무엇이 미검증인가

정직하게 적습니다.

반영한 것 (커밋 완료, 운영 배포와 검증 전)

• 비밀 저장소 조회를 행별에서 조회 1회로
• 날짜 조건의 컬럼 캐스팅 제거
• 2단계 조회를 단일 쿼리로 통합
• 결과 응답의 JSON 왕복 제거 (백엔드 + 프록시)
• 화면 타임아웃을 작업 보존 시간 안쪽으로 늘리고, 폴링을 응답 후 예약 방식으로 변경

설계까지만 온 것

• 서버 측 파일 생성, 청크 조회, 비공개 저장소 스트리밍

측정하지 않은 것. 이 글의 메모리 수치는 객체 레이아웃에서 계산한 추정치이고 프로파일러로 측정한 값이 아닙니다. 힙 덤프를 떠서 확인하지 않았습니다. 자릿수를 판단하는 데는 충분하다고 봤지만, "1GB"를 측정값으로 읽으시면 안 됩니다.

아직 답이 없는 것

• 100만 행이 시트 한 장 한계에 4만 8천 행 남았습니다. 시트 분할 전략이 필요한데 설계에 없습니다
• 다운로드 기간·건수 상한이 없습니다. 사용자가 '전체'를 고르면 몇 년치를 긁습니다. 사실 이 상한 하나가 지금까지의 작업보다 비용 대비 효과가 클 수도 있습니다
• 작업 취소 수단이 없습니다. 사용자가 화면을 떠나도 워커는 끝까지 돌고 파일을 만듭니다

돌아보면 이 작업에서 새로 배운 알고리즘이나 라이브러리는 없습니다. 한 일은 "이 데이터가 지금 몇 벌 존재하고, 각각 어떤 모양인가"를 끝까지 세어본 것뿐입니다.

같은 데이터가 JSON 문자열, 해시맵 그래프, JS 객체, 셀 딕셔너리로 네 번 존재했고, 전부 건수에 비례했습니다. 각 단계는 저마다 합리적인 선택이었습니다 — 캐시에 넣으려니 직렬화가 필요했고, 응답으로 감싸려니 역직렬화가 필요했고, 브라우저에서 파일을 만들려니 객체가 필요했습니다. 단건이나 수천 건에서는 아무 문제가 없습니다.

문제는 각 단계가 자기 지역에서만 합리적이었다는 것입니다. 파이프라인 전체를 놓고 "이 데이터가 몇 번 펼쳐지는가"를 세어보기 전까지는 어디를 고쳐야 할지 알 수 없었습니다.

100만이라는 숫자가 한 일은 그 질문을 강제한 것뿐입니다.