EXPLAIN ANALYZE

로 슬로우 쿼리(Slow Query)를 20배 빠르게 만든 이야기 — 빨라진 것은 「1회당 처리」가 아니었다

AI와 ## 서론

Web 애플리케이션에서는 계산 비용이 높은 처리를 요청마다 실행하면 응답이 느려집니다. 그래서 저희 회사는 배치(Batch)로 사전 계산한 결과를 캐시(Cache)해 두고, 참조 시에는 그것을 불러오기만 하는 구성을 채택하고 있습니다.

이 배치의 중심에 있는 SQL 1개가 데이터 증가와 함께 슬로우 쿼리화되어, 실행에 196초가 걸리게 되었습니다. 이번에는 이 느려짐의 진인을 AI와 함께 EXPLAIN ANALYZE로 찾아내어, 9.8초까지 단축한 과정을 소개합니다. 이 개선은 이미 운영 환경에 배포되었으며, 배치 전체 실행 시간은 52분에서 약 3분이 되었습니다.

그리고 미리 살짝 스포일러를 하자면, 이 수정으로 「1회당 처리」는 1밀리초도 빨라지지 않았습니다. 그럼에도 20배 빨라졌다는, 조금 신기한 이야기이기도 합니다.

흐름: AI와 인간이 돌리는 슬로우 쿼리 조사의 『형식』

이번 개선은 즉흥적인 것이 아니라 정해진 『형식』에 따라 진행했습니다. AI에게는 탐지·분석·입안·구현을 맡기고, 인간은 실행과 「무엇을 배포할 것인가」의 판단을 담당한다. 그리고 EXPLAIN ANALYZE로 전후를 수치로 비교한다. 이 형식은 다른 슬로우 쿼리에도 그대로 적용할 수 있습니다.

범례: 🤖 AI가 담당 / 🧑 인간이 담당 / 🤝 협업 (실행=인간 / 분석=AI)

잠정 대응: 우선 요건을 확인하고, 청크(Chunk)를 절반으로 줄여 시간을 벌다

근본 대응 조사를 시작하기 전에 잠정 대응을 먼저 취했습니다.

이 배치는 대상 데이터를 청크(Chunk)로 분할하여 처리하고 있으며, 청크 사이즈는 환경 변수로 제어하고 있습니다. 원래 청크 사이즈는 1,000이었고, 배치 전체는 약 30분 만에 완료되었습니다. 하지만 데이터 증가와 함께 1개 쿼리가 196초가 걸릴 정도로 비대해졌고, 1회당 DB 부하를 무시할 수 없게 되었습니다.

여기서 먼저 확인한 것은 속도 요건입니다. 관계자에게 확인한 결과, 이 배치는 **「1일 이내에 끝나면 문제없다」**는 것을 알게 되었습니다. 그렇다면 배치 전체 소요 시간은 다소 늘어나더라도, 1회당 부하를 낮추는 것이 우선입니다. 청크 사이즈를 1,000 → 500으로 반감시키자, 1개 쿼리의 무게는 가벼워지는 반면 쿼리 횟수는 두 배가 되어, 배치 전체 시간은 30분 → 52분으로 늘어났습니다. 그래도 요건인 「1일 이내」에는 충분히 들어옵니다.

이렇게 시간적 여유를 확보한 뒤, 근본 원인 조사에 들어갔습니다.

조사: 진범은 『풀 스캔(Full Scan)』이 아니었다

슬로우 쿼리라고 하면 가장 먼저 의심한 것은 대량의 키를 IN (...)으로 일괄 취득하는 부분입니다. 실제로 EXPLAIN ANALYZE를 실행하면 바깥쪽은 풀 스캔(Full Scan)이었습니다. 다만, 이 부분은 수십 밀리초 수준으로 범인치고는 소소했습니다.

정말로 영향을 주고 있었던 것은 그 다음이었습니다. EXPLAIN ANALYZE의 loops (각 노드의 실행 횟수)를 보면, 안쪽의 서브쿼리(Subquery)가 20,870회 호출되고 있었습니다. 1회마다 수백 행을 스캔하고, 계산하고, 정렬하여 상위 몇 건을 가져옵니다. 이 동일한 처리가 약 2만 번 반복되어 합계 196초에 달했던 것입니다.

여기서 효과적이었던 것이 AI와 『측정』의 조합입니다. EXPLAIN ANALYZE의 출력을 AI에게 읽히자, 「풀 스캔보다 loops=20870이 지배적이다」라는 해석을 즉시 언어화해 주었습니다. 추정치만 있는 EXPLAIN으로는 보이지 않는, 실행 횟수라는 관점에 처음부터 조준을 맞출 수 있었던 것이 컸습니다.

대응: IN을 JOIN으로 바꾸어 『20배 팽창』을 제거하기

조사 결과를 AI에게 전달하여 효과, 구현 비용, 영향 범위와 함께 개선안을 제안받았습니다. 그중에서 『구현 비용이 작고, 효과가 높으며, 영향이 적은』 것으로 선택한 것이 복합 키(Composite Key)의 IN을 JOIN으로 교체하는 것입니다.

약 1,000개의 튜플(Tuple)을 IN으로 전달하는 것을 그만두고, VALUES ROW(...)를 사용하여 드라이빙 테이블(Driving Table)화하여 JOIN합니다 (MySQL 8.0.19+).

-- 약 1,000건을 IN으로 전달하는 대신, VALUES ROW로 JOIN 함
JOIN (VALUES ROW(1, 101), ROW(1, 102), …) AS t(col_a, col_b)
ON s.col_a = t.col_a AND s.col_b = t.col_b

이렇게 하면 바깥쪽은 복합 인덱스(Composite Index)로 핀포인트(Pinpoint)하게 조회할 수 있게 되며, 덤으로 설정 필터링(Filtering)도 앞 단계에서 효과를 보게 됩니다. 결과적으로 팬아웃(Fan-out)되었던 약 2만 행이 본래의 1,000건으로 돌아왔고, 동일한 처리의 호출 횟수가 20,870회 → 1,000회가 되었습니다.

이 수정안 또한 AI에게 구현하게 하여 다시 EXPLAIN ANALYZE를 실행해 검증한 결과, 196초 → 9.8초로 약 20배 단축되었습니다. 출력 건수는 전후가 완전히 일치하며, 결과의 정확성은 유지되었습니다.

고찰: 빨라진 것은 『1회당 처리』가 아니다

이 부분이 이번 수정의 가장 흥미로운 지점입니다. 반복되었던 내부 처리의 『1회당 작업량』은 전후가 거의 변하지 않았습니다. 스캔(Scan)하는 행수도, 계산도, 정렬(Sort)도 동일하며, 1회당 소요 시간은 약 9밀리초(ms) 그대로입니다. 오히려 아주 미세하게 늘어난 정도입니다.

그럼에도 20배 빨라진 이유는 순수하게 『불필요한 호출을 20배만큼 깎아냈기』 때문이었습니다. 속도 비 196 / 9.8 ≒ 20배는 호출 횟수의 비 20,870 / 1,000 ≒ 20.9배와 거의 일치합니다.

릴리스 결과: 배치 전체는 52분 → 약 3분

이 수정을 운영 환경에 릴리스하여 실제 배치 실행 시간으로 효과를 확인했습니다.

잠정 대응 후의 52분과 비교하면 약 17배, 당초의 30분과 비교해도 10배 단축되었습니다. 쿼리 단독으로 보았을 때의 약 20배 개선이 배치 전체의 실행 시간에도 그대로 정직하게 반영된 형태가 되었습니다. 사전 검증에서 EXPLAIN ANALYZE 수치를 확보해 두었기에, 릴리스 후의 결과에도 놀라움은 없었으며, 그저 「예측대로」임을 확인하는 것으로 충분했습니다.

「1일 이내에 끝나면 된다」라는 요구사항에 대해 약 3분. 요구사항을 여유 있게 충족할 수 있게 되었으며, 잠정 대응에서 받아들였던 「배치가 느려진다」라는 트레이드오프(Trade-off)도 결과적으로 해소되었습니다.

다음 단계: 그래도 1 쿼리 9.8초는 무겁다

배치 전체로서는 요구사항을 크게 상회하는 수준이 되었지만, 1 쿼리에 9.8초라는 것은 단독으로 보면 여전히 무겁다는 것도 사실입니다. 데이터는 앞으로도 계속 늘어날 것이기에, 여기서 만족하지 않고 더 줄일 수 있는 여지를 파악해 둡니다.

9.8초의 거의 대부분은 이제 『순수한 「1회 × 1,000회」』에 집중되어 있습니다. 남은 부분을 줄이는 방향은 이 글에서 살펴본 「횟수 × 1회의 비용」이라는 관점을 유지한 채 두 가지가 있습니다.

• 1회당 처리를 가볍게 하기 — 필터링에 효과적인 복합 인덱스를 추가하여 1회당 스캔 행수를 줄이거나, 계산 로직을 애플리케이션 측으로 옮겨 반복되는 처리 자체를 없애는 등
• 애초의 호출 횟수를 줄이기 — 「정말로 1,000번 루프를 돌 필요가 있는가」 자체를 재검토. 청크를 나누는 방식이나 처리를 묶는 방식을 바꾸면 횟수 자체를 더 줄일 여지가 있습니다.

어느 쪽을 택할지(그리고 지금 해야 할지)는 역시 ⑤의 원칙 —— 『효과가 높고, 비용과 영향이 작은 것』 —— 과 요구사항을 대조하여 결정합니다. 현재는 요구사항을 충분히 만족하고 있으므로 서두를 필요는 없지만, 데이터 증가로 다시 요구사항에 근접해 왔을 때 다음 수를 즉시 던질 수 있는 상태로 만들어 둡니다.

요약

• 튜닝하기 전에 먼저 요구사항을 확인한다. "1일 이내에 끝나기만 하면 된다"는 것을 확인했기에, 배치를 느리게 만드는 잠정적인 대응(청크(Chunk) 절반 감소)을 안심하고 적용할 수 있었고, 근본적인 대응을 위한 조사 시간을 확보할 수 있었습니다.
• 견적(Estimation)만으로는 "같은 처리가 몇 번 호출되는지" 알 수 없습니다. 실행 횟수라는 관점이 생기고 나서야 비로소 풀 스캔(Full Scan)보다 호출 횟수가 지배적이라는 것을 특정할 수 있었습니다. 부하(Weight)는 『1회당 비용 × 횟수』로 생각합니다. EXPLAIN이 아니라 EXPLAIN ANALYZE의 loops를 확인합니다.
• JOIN의 팬아웃(Fan-out)이 처리 횟수를 몇 배로 불린다. 1,000건이어야 할 것이 약 2만 번 실행되었던 정체는 다른 테이블과의 JOIN에 의한 팽창이었습니다. '필터링(絞り込み)을 적용하는 위치' 하나만으로 실행 횟수는 차원이 다르게 변합니다.
• "횟수를 줄이는 것"과 "1회를 빠르게 하는 것"은 별개의 문제다. 이번에는 전자의 작업만으로 20배 빨라졌으며, 배치 전체로도 52분 → 약 3분으로 단축되었습니다. 이 둘을 분리해서 바라보면, 어디까지 할지, 다음에 무엇을 할지에 대한 판단이 명확해집니다.

이번 개선에서 특별한 도구를 도입한 것은 아닙니다. EXPLAIN ANALYZE라는 표준 기능과, AI와의 역할 분담이라는 '형식(Pattern)'에 올라탔을 뿐입니다. AI에게 탐지·분석·구현을 맡기면서, 측정(Measurement)으로 전후를 비교하고, 인간이 채택 여부를 판단합니다. 이러한 방식으로 운영한다면, 슬로우 쿼리(Slow Query) 조사는 결코 특별한 작업이 아니라, 일상적으로·안전하게 수행할 수 있는 작업이라고 생각합니다.

Discussion