이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.
- 서브쿼리, CTE, Window function은 언제 유용한가?
- 복잡한 SQL은 왜 실행 계획 확인이 더 중요한가?
- 백엔드에서 집계 쿼리를 실시간 API로 제공할 때 무엇을 조심해야 하는가?
개요
서브쿼리, CTE, Window function은 SQL을 강력하게 만든다. 하지만 복잡한 SQL은 중간 결과와 정렬, materialization, 조인 비용이 커질 수 있다. 가독성을 위해 사용하되 실행 계획을 확인해야 한다.
원리
서브쿼리, CTE, Window function의 원리는 “SQL을 짧게 만드는 기능”이 아니라 중간 결과를 어떻게 만들고 재사용하고 정렬할지 DB에 표현하는 것이다. 복잡한 SQL은 애플리케이션 왕복을 줄일 수 있지만, DB 안에서 큰 sort, hash aggregate, materialization, 임시 파일을 만들 수도 있다.
백엔드 개발자는 복잡한 SQL을 작성할 때 가독성, 실행 계획, 메모리 사용, 임시 디스크 사용, timeout, API 호출 빈도를 함께 봐야 한다. 한 번 실행하는 리포트 SQL과 초당 수백 번 호출되는 사용자 API SQL은 같은 문법이라도 전혀 다른 설계 대상이다.
서브쿼리
최근 주문이 있는 회원을 찾는 쿼리다.
SELECT id, email
FROM members m
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.member_id = m.id
AND o.created_at >= timestamp '2026-06-01'
);EXISTS는 존재 여부 확인에 적합하다. orders(member_id, created_at) 인덱스가 있으면 효과적일 수 있다.
IN, EXISTS, JOIN 중 무엇이 빠른지는 DBMS와 통계, 데이터 분포에 따라 달라질 수 있다. 중요한 것은 의도를 명확히 쓰고 대표 데이터로 실행 계획을 확인하는 것이다. 존재 여부만 필요하다면 결과 row를 불필요하게 늘리지 않는 형태를 먼저 검토한다.
CTE
CTE는 복잡한 쿼리를 단계적으로 읽기 좋게 만든다.
WITH paid_orders AS (
SELECT id, member_id, total_price
FROM orders
WHERE status = 'PAID'
)
SELECT member_id, sum(total_price)
FROM paid_orders
GROUP BY member_id;CTE는 가독성을 높이지만 DB 버전과 Optimizer 동작에 따라 최적화 방식이 달라질 수 있다. 실행 계획으로 확인해야 한다.
PostgreSQL도 버전에 따라 CTE inlining/materialization 동작이 달라졌고, MySQL도 CTE와 derived table 최적화 방식이 다를 수 있다. “CTE를 쓰면 항상 느리다”도, “CTE는 보기 좋으니 괜찮다”도 둘 다 위험하다.
Window Function
Window function은 그룹으로 행을 접지 않고 행별 순위나 누적값을 계산한다.
SELECT id, member_id, total_price,
row_number() OVER (
PARTITION BY member_id
ORDER BY created_at DESC
) AS rn
FROM orders;회원별 최신 주문을 고르는 데 사용할 수 있다.
WITH ranked AS (
SELECT id, member_id, created_at,
row_number() OVER (
PARTITION BY member_id
ORDER BY created_at DESC
) AS rn
FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;대량 데이터에서는 정렬 비용이 클 수 있다.
회원별 최신 주문 조회가 자주 호출된다면 window function보다 (member_id, created_at desc, id desc) 인덱스와 cursor 방식, 별도 최신 주문 snapshot이 더 나을 수 있다. Window function은 강력하지만 모든 요청마다 전체 파티션을 정렬하게 만들면 API용으로는 무겁다.
실시간 집계와 API
다음 API는 처음에는 편하다.
SELECT date(created_at) AS day, count(*)
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY date(created_at)
ORDER BY day;하지만 주문이 많아지면 매 요청마다 많은 행을 읽고 그룹화한다. 대시보드는 캐시, materialized view, batch 집계, 분석 DB를 검토한다.
PostgreSQL materialized view는 조회를 빠르게 만들 수 있지만 자동으로 항상 최신이 되지는 않는다. refresh 주기와 lock 영향, stale data 허용 범위를 제품과 합의해야 한다. MySQL에서는 summary table을 batch로 갱신하는 식의 설계가 더 일반적일 수 있다.
실전 팁
- 복잡한 SQL은 가독성과 실행 계획을 모두 본다.
EXISTS는 존재 확인에 자주 유용하다.- Window function은 강력하지만 정렬 비용을 조심한다.
- 실시간 집계 요구는 정확도와 지연 허용을 제품과 합의한다.
- CTE는 DB 버전별 최적화 동작을 확인한다.
- 복잡한 SQL은
EXPLAIN에서 sort, hash aggregate, temporary table, materialization, rows estimate 차이를 확인한다. - API용 SQL과 리포트/운영용 SQL은 timeout, 실행 위치, 허용 지연을 다르게 잡는다.
- 집계 결과가 자주 필요하면 원본 테이블을 매번 읽는 대신 summary table, cache, materialized view, 분석 DB를 검토한다.
위험 신호!
- 복잡한 CTE를 작성하고 실행 계획을 보지 않는다.
- Window function이 대형 테이블 전체를 정렬한다.
- 대시보드가 운영 DB에서 매 요청마다 집계한다.
IN (subquery)와EXISTS차이를 실행 계획 없이 감으로 고른다.- 집계 쿼리에 기간 제한이 없다.
- Window function으로 최신 row 1개를 고르면서 전체 테이블을 매번 정렬한다.
- materialized view나 summary table의 갱신 지연을 사용자에게 설명하지 못한다.
확인 질문
- CTE를 사용할 때 실행 계획을 봐야 하는 이유는 무엇인가?
- 가독성은 좋아져도 DB가 중간 결과를 어떻게 처리하는지에 따라 성능이 달라질 수 있기 때문이다.
- Window function이 강력하지만 위험할 수 있는 이유는 무엇인가?
- 파티션과 정렬 비용이 커져 대량 데이터에서 메모리와 디스크 부하를 만들 수 있기 때문이다.
- 실시간 집계 API를 조심해야 하는 이유는 무엇인가?
- 매 요청마다 많은 행을 읽고 그룹화해 운영 DB에 큰 부하를 줄 수 있기 때문이다.
- materialized view나 summary table을 둘 때 합의해야 할 것은 무엇인가?
- 갱신 주기, 오래된 데이터 허용 범위, refresh 중 lock/부하, 실패 시 재생성 방법, 원본과 불일치했을 때의 복구 기준이다.