본문 바로가기
Case Studies (실무)

정산 시즌 조회 p99 20s → 500ms (실행계획 + LATERAL JOIN)

정산 시즌에만 특정 조회 API가 p99 20초까지 치솟았다가, 쿼리 구조를 재설계해서 p99 500ms까지 내린 사례를 정리합니다.

개선의 핵심은 아래 3가지입니다.

  • 비정규화를 통해 서브쿼리가 아니라 원본 테이블 조인 방식으로 변경했습니다.
  • COUNT 쿼리는 JOIN을 직접 하지 않고 EXISTS로 세미조인 형태로 변경했습니다.
  • LIMIT의 ‘정답 ID 리스트’를 먼저 확정한 뒤, 무거운 조인/집계를 수행하도록 실행계획을 강제했습니다. (2-Step Query, 아래 예시 참고)

핵심 결과 (TL;DR)

  • 정산 시즌 조회 API p99: 20s → 500ms
  • Datadog APM으로 배포 전/후 p95/p99 비교로 검증했습니다.
  • 접근의 요점은 다음과 같습니다.
    • COUNT는 **EXISTS(세미조인)**로 “존재성만” 확인하여 임시 테이블 병목을 제거했습니다.
    • SELECT는 서브쿼리 머티리얼라이즈/임시테이블 폭발을 유발하는 구조를 해체했습니다.
    • 남는 무거운 부분은 **LATERAL JOIN / ID-first 2-step(Repo1/Repo2/Repo3)**로 상한을 LIMIT(100)에 고정했습니다.

문제 상황

리스트 API는 보통 다음 두 쿼리가 함께 동작합니다.

  • COUNT(*) (페이지네이션 전체 건수)
  • SELECT ... LIMIT/OFFSET (목록 조회)

기존 구현은 COUNT와 SELECT 모두에서 다음 문제가 겹쳐 있었습니다.

  • JOIN이 과도했고,
  • 서브쿼리가 driven table로 조인되며,
  • 서브쿼리 내부 GROUP BY, ORDER BY, JOIN 때문에 derived table이 temporary table로 materialize되었습니다.

데이터가 폭증하는 정산 시즌에는 이 “임시테이블 + 조인 폭발” 비용이 그대로 지표에 찍혀 p99 20초까지 상승했습니다.

관측(Observability)

가장 먼저 한 일은 “느리다”가 아니라 어디서 터지는지를 확정하는 것이었습니다.

  • Datadog APM에서 해당 엔드포인트의 DB span을 확인했습니다.
  • 실행계획에서 다음을 확인했습니다.
    • derived/temporary/materialize 발생 지점
    • SELECT 되는 row 수 폭증 구간
    • Using temporary, Using filesort 유무

해결 전략 요약

이 문제는 단순히 “쿼리를 나눈다”로 해결되지 않았습니다. 핵심은 실행계획을 driving-first(Driving Table이 먼저 필터링 적용되게 하는것)로 만들고, 쿼리 비용의 상한을 LIMIT(100) 기준으로 고정하는 것이었습니다.

  • COUNT는 **EXISTS(세미조인)**로 “존재성만” 확인하여 JOIN으로 인한 카디널리티 폭발/임시 테이블 병목을 제거했습니다.
  • SELECT는 서브쿼리 머티리얼라이즈/임시테이블 폭발이 발생하는 구조를 해체했습니다(가능하면 비정규화로 서브쿼리를 제거했습니다).
  • 그래도 남는 무거운 구간은 LATERAL JOIN으로 driving-first를 강제하고, 최종적으로는 ID-first 2-step(Repo1/Repo2/Repo3) 패턴으로 비용 상한을 LIMIT(100)에 고정했습니다.

이 문제의 본질은 보통 아래 흐름입니다.

  • 무거운 (b ⋈ c + GROUP BY) 같은 덩어리를 먼저 크게 만들고
  • 그 뒤에 필터가 적용되어 대부분 버려지며
  • LIMIT은 늦게 적용되어 비용 상한이 없습니다.

이를 뒤집는 패턴이 **Repo1/Repo2/Repo3 (ID-first pagination / deferred join)**입니다.

Repo1 = 필터+정렬+LIMIT의 ‘결정권자’ (정답 ID 100개 확정)
Repo2 = 그 100개 ID의 1:1 성격 상세 조인
Repo3 = 그 100개 ID에 대한 무거운 집계/1:N(폭발)만 따로

COUNT 최적화: EXISTS 세미조인

COUNT는 상세 row를 만들 필요가 없습니다. 목적은 “조건을 만족하는 엔티티 수”입니다.
그래서 JOIN으로 row를 불리는 방식 대신, EXISTS로 ‘있냐/없냐’만 확인합니다.

SELECT COUNT(*)
FROM driving d
WHERE d.created_at >= ?
  AND d.created_at < ?
  AND d.status IN ('PAID','SHIPPED')
  AND EXISTS (
    SELECT 1
    FROM child c
    WHERE c.d_id = d.id
      AND c.is_current = 1
  );
  • “JOIN을 물리적으로 아예 안 읽는다”는 의미가 아니라,
  • JOIN 결과 row 증식(카디널리티 폭발)을 제거해 비용 상한을 낮추는 접근입니다.

SELECT 최적화 1단계: 서브쿼리 제거

DB 연산이 많이 들어가는 서브쿼리가 매 요청마다 발생하면, 정산 시즌에는 그대로 병목이 됩니다.

이런 것들은 비정규화로 서브쿼리 자체를 제거할 수 있으면 가장 효율적입니다.

SELECT 최적화 2단계: LATERAL JOIN

서브쿼리를 완전히 못 푸는 구간은 LATERAL JOIN으로 “driving에서 필터된 row에 대해서만” 계산되게 만듭니다.

SELECT d.id, d.created_at, d.status, x.agg_value
FROM driving d
JOIN LATERAL (
  SELECT SUM(c.amount) AS agg_value
  FROM child c
  WHERE c.d_id = d.id
    AND c.state <> 'CANCELLED'
) x ON TRUE
WHERE d.created_at >= ?
  AND d.created_at <  ?
ORDER BY d.created_at DESC, d.id DESC
LIMIT 100 OFFSET 0;
  • 목표는 “무조건 빠르다”가 아니라,
  • 실행계획을 driving-first 형태로 유도/고정해 derived/temporary 폭발을 줄이는 것입니다.

ID-first 2-step: 비용 상한 고정

여전히 해결이 안 되는 케이스는 보통 아래와 같습니다.

  • “서브쿼리가 조인되면서 임시테이블이 폭발”
  • 즉, 조인이 적용되는 후보 row 자체가 너무 많음

이때는 쿼리를 “나눈다”기보다, 정답 ID 100개를 먼저 확정한 뒤 그 100개만 확장하는 구조로 바꿉니다.

Repo1/Repo2/Repo3 패턴 예시

아래 예시는 현실적인 케이스를 가정합니다.

  • 최종 화면: orders 기준 최신 100개
  • 필터
    • 기간, 상태 (orders)
    • 배송사 이름 (다른 테이블)
    • 쿠폰 코드 (다른 테이블)
    • 주문 아이템 합계 조건 (무거운 집계)

Repo1: 정답 ID 100개 확정 (필터 + 정렬 + LIMIT/OFFSET)

중요 원칙: 다른 테이블 조건은 JOIN으로 붙여서 폭발시키지 말고 EXISTS로 존재성만 확인해 필터를 정확히 표현합니다.

SELECT o.id, o.created_at
FROM orders o
WHERE o.created_at >= :from
  AND o.created_at <  :to
  AND o.status IN ('PAID','SHIPPED')

  AND EXISTS (
    SELECT 1
    FROM shipments sh
    JOIN carriers car ON car.id = sh.carrier_id
    WHERE sh.order_id = o.id
      AND sh.is_current = 1
      AND car.name = 'DHL'
  )

  AND EXISTS (
    SELECT 1
    FROM coupon_redemptions cr
    JOIN coupons cp ON cp.id = cr.coupon_id
    WHERE cr.order_id = o.id
      AND cp.code IN ('WELCOME10','VIP20')
  )

  -- 무거운 집계 조건도 "존재성"으로 표현 가능하면 Repo1에서 확정
  AND EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.order_id = o.id
      AND oi.state <> 'CANCELLED'
    GROUP BY oi.order_id
    HAVING SUM(oi.qty * oi.unit_price) >= 10000
  )

ORDER BY o.created_at DESC, o.id DESC
LIMIT 100 OFFSET 0;

✅ 여기서 페이지의 정답(100개) 이 확정됩니다. Repo2/Repo3는 절대 추가 필터로 결과를 줄이면 안 됩니다(페이지가 깨집니다).

Repo2: 100개 ID에 대한 1:1 상세 조인

Repo2는 WHERE id IN (:ids)로 범위를 제한하고 1:1 성격 조인(고객/가맹점/주소 등)만 가져옵니다.

SELECT
  o.id, o.created_at, o.status,
  cu.id AS customer_id, cu.email,
  m.id AS merchant_id, m.name AS merchant_name,
  addr.city, addr.zip_code
FROM orders o
JOIN customers cu ON cu.id = o.customer_id
LEFT JOIN merchants m ON m.id = o.merchant_id
LEFT JOIN addresses addr ON addr.id = o.shipping_address_id
WHERE o.id IN (:orderIds);

⚠️ 정렬 주의: IN은 순서를 보장하지 않습니다. 안전한 방법 중 하나를 선택합니다.

  • (권장) Repo1이 id + sort_key(created_at)를 함께 내리고, Repo2 결과를 JOIN해서 정렬을 유지합니다.
  • 또는 애플리케이션에서 orderIds 순서대로 재정렬합니다.

Repo3: 무거운 집계/1:N 폭발 구간만 따로

핵심은 “Repo3도 page 스코프(100개)에만” 수행된다는 점입니다. 그리고 Repo3 내부의 파생 집계도 반드시 page로 제한해야 합니다(여기서 자주 다시 터집니다).

WITH page AS (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.id IN (:orderIds) -- 또는 Repo1 결과를 임시테이블/CTE로 전달
),
items AS (
  SELECT oi.id, oi.order_id, oi.qty, oi.unit_price
  FROM order_items oi
  JOIN page p ON p.id = oi.order_id
  WHERE oi.state <> 'CANCELLED'
),
disc AS (
  SELECT d.order_item_id, SUM(d.amount) AS discount_amount
  FROM item_discounts d
  JOIN items i ON i.id = d.order_item_id
  WHERE d.is_applied = 1
  GROUP BY d.order_item_id
)
SELECT
  i.order_id,
  COUNT(*) AS item_count,
  SUM(i.qty) AS total_qty,
  SUM(i.qty * i.unit_price) AS gross_amount,
  SUM(COALESCE(d.discount_amount,0)) AS discount_amount
FROM items i
LEFT JOIN disc d ON d.order_item_id = i.id
GROUP BY i.order_id;

서비스 레이어에서 합치기(단순)

@Transactional(readOnly = true)
fun findOrderPage(req: Req): List<OrderRow> {
    val ids = repo1.findOrderIds(req)              // 정답 100개
    if (ids.isEmpty()) return emptyList()

    val details = repo2.findOrderDetails(ids)      // 1:1 상세
    val aggMap = repo3.findOrderAgg(ids).associateBy { it.orderId }

    val detailMap = details.associateBy { it.orderId }

    // Repo1 정렬 그대로 반환(정렬 보존 로직은 필요)
    return ids.mapNotNull { id ->
        val d = detailMap[id] ?: return@mapNotNull null
        d.toRow(aggMap[id])
    }
}

절대 규칙

✅ Repo1이 ‘정답을 결정’합니다 Repo1에 반드시 있어야 하는 것:

  1. 최종 포함/제외를 결정하는 모든 필터(다른 테이블 조건 포함)
  2. 최종 정렬(ORDER BY)
  3. LIMIT/OFFSET

Repo2/Repo3에서 필터를 추가로 걸면:

  • Repo1이 뽑은 100개가 87개로 줄어드는 등 페이지가 깨집니다

검증(Observability)

  • Datadog APM에서 엔드포인트 latency를 확인했습니다.
    • p50/p95/p99 비교
  • DB CPU 모니터링으로 연산 부하를 확인했습니다.
  • 정산 시즌 “피크 시간대”를 기준으로 비교해 유의미성을 확보했습니다.

회고

  • WHERE절에서 Driving Table에 대해 빠르게 필터링되는 요소가 있는지 먼저 파악하는 것이 우선입니다. 먼저 필터링이 된 뒤 조인이 되어야 하며, 조인이 된 뒤 필터링이 적용되면 DB 부하가 치솟습니다.
  • 어느 derived가 materialize되는지만 잡아도 방향이 나옵니다.
  • COUNT 쿼리는 LIMIT이 걸리지 않으므로 전체에 대해 스캔합니다. 그러므로 조인을 하지 않고 EXISTS를 통해 세미조인을 활용하는 것이 최선입니다.
  • 서브쿼리를 반드시 사용해야 한다면, 해당 서브쿼리가 가져오는 데이터 수를 조절할 수 있는지 확인해야 합니다.
  • FROM 절에 서브쿼리를 사용하는 경우에도, 바깥에서 LIMIT을 걸면 머티리얼라이즈로 DB 부하가 폭증합니다. (e.g SELECT * FROM (SELECT ...) LIMIT 100) LIMIT을 서브쿼리 안으로 넣을 수 있다면 그렇게 해야 합니다.
  • COUNT 쿼리에서는 LATERAL JOIN을 사용하면 부하가 치솟을 수 있지만, LIMIT이 있는 SELECT 쿼리에서는 LATERAL JOIN으로 서브쿼리에 필터링 조건이 PUSHDOWN 되어 서브쿼리 데이터 수가 먼저 조절되면서 효율적으로 동작합니다.
  • Repo1/Repo2/Repo3(ID-first, 2 Step Query) 패턴은 단순히 쿼리를 나누는 것이 아니라 비용 상한을 LIMIT(100)에 고정하는 운영 친화적 패턴입니다.

Diagram Note: 병목 분석 → 개선 적용 플로우

diagram rendering...