MySQL 8(8.0.15 기준)에 window function 들어온 지 좀 됐는데 이번에 분석/집계 대시보드 쿼리 리팩터링하면서 본격적으로 썼다. 5.7까지는 Postgres 부러워만 했는데 이제 꽤 자리잡음. 케이스별 실전 사용과 EXPLAIN 주의점.
케이스 1: 그룹별 top-N
상품별 최신 3건. 5.7까지 가장 흔한 관용구는 상관서브쿼리 + LIMIT 이었는데 성능이 박살나기 쉽다.
-- 8.0
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS rn
FROM orders o
WHERE created_at >= '2019-01-01'
) t
WHERE t.rn <= 3;
실행 동작. MySQL은 윈도우 함수를 Volcano 스타일 이터레이터의 마지막 단계(WINDOW 연산자)에서 계산. PARTITION BY의 순서가 인덱스와 맞으면 sort 회피 가능. 위 쿼리에서 (product_id, created_at DESC) 조합 인덱스가 있으면 Using filesort가 사라진다. 인덱스가 없으면 tmp table + filesort가 들어가서 결과셋이 커질수록 급격히 느려짐.
대안: LATERAL JOIN이 MySQL 8.0.14에서 들어왔다. 각 product_id별로 subquery를 따로 돌려 top-3만 긁어오는 방식. 결과 세트에서 차지하는 비중이 작을 때는 LATERAL이 더 빠르다. 우리 케이스는 행당 주문 수가 수백 이상이라 window + row_number가 더 빨랐음.
케이스 2: 누적합/이동 평균
-- 일자별 매출 + 누적합 + 7일 이동평균
SELECT ymd,
amount,
SUM(amount) OVER w_cum AS cum,
AVG(amount) OVER w_7d AS ma7
FROM daily_sales
WINDOW
w_cum AS (ORDER BY ymd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
w_7d AS (ORDER BY ymd ROWS BETWEEN 6 PRECEDING AND CURRENT ROW);
frame 지정 주의. ROWS와 RANGE가 다르다. ROWS는 물리적 행 개수 기준, RANGE는 ORDER BY 값의 범위 기준. 날짜가 중간에 빠져 있는 데이터에서 "최근 7일 이동평균"을 진짜 기간 기준으로 하려면 RANGE BETWEEN INTERVAL 6 DAY PRECEDING — 인데 MySQL 8.0 초기 버전은 RANGE의 interval 프레임 지원이 제한적. 8.0.2+에서 일부 들어왔지만 운영에서는 ROWS로 쓰되 날짜 gap을 직접 메우는 쪽이 안전.
케이스 3: LAG/LEAD로 증감 계산
SELECT ymd, amount,
amount - LAG(amount, 1, 0) OVER (ORDER BY ymd) AS dod_diff,
amount - LAG(amount, 7, 0) OVER (ORDER BY ymd) AS wow_diff,
(amount / NULLIF(LAG(amount, 7) OVER (ORDER BY ymd), 0) - 1) AS wow_pct
FROM daily_sales;
5.7 시대엔 self join 또는 사용자 변수(@prev := ...) 트릭으로 짰던 쿼리. 사용자 변수 방식은 8.0에서 warning 뜨고, 평가 순서가 보장되지 않는다는 문서 경고가 강해져서 사실상 금지 권고. LAG로 대체.
케이스 4: 그룹 내 비중
SELECT product_id,
region,
revenue,
revenue / SUM(revenue) OVER (PARTITION BY product_id) AS share,
RANK() OVER (PARTITION BY product_id ORDER BY revenue DESC) AS rk
FROM sales_by_region;
제품별 지역 매출 비중과 순위 한 번에. 이걸 예전엔 GROUP BY product_id로 합계 subquery 만들어 조인해야 했다.
성능 및 EXPLAIN
- 동일 결과 기준, 상관서브쿼리 대비 1.5~2배 빠름(우리 데이터 기준, orders 800만, products 6만)
- window 연산자는
EXPLAIN FORMAT=TREE(8.0.16+)로 보면 명확. -> Window aggregate: ... 라인 확인
- 정렬 회피 여부가 결정적. PARTITION BY ~ ORDER BY 컬럼 조합이 인덱스 선두 컬럼과 맞아떨어져야
Using filesort가 사라진다
- FRAME 없이 window 쓰면 기본이
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. row_number, rank 같은 랭킹 함수에는 상관없지만 SUM, AVG는 결과 의미가 달라진다
CTE(WITH) 함께 쓰기
WITH daily AS (
SELECT DATE(created_at) AS ymd, SUM(amount) AS amount
FROM orders WHERE created_at >= '2019-01-01'
GROUP BY DATE(created_at)
)
SELECT ymd, amount,
SUM(amount) OVER (ORDER BY ymd) AS cum
FROM daily;
MySQL 8의 CTE는 Postgres와 달리 기본적으로 materialize되지 않고 subquery처럼 inline. 그래도 derived_merge 최적화가 안 먹는 복잡한 쿼리에서는 임시 테이블로 펼쳐질 수 있다. EXPLAIN으로 확인 필수. 재귀 CTE(WITH RECURSIVE)는 최대 cte_max_recursion_depth(기본 1000)에 걸리니 큰 트리 순회는 주의.
마이그레이션 주의
- 예약어 확장.
RANK, ROW, OVER가 예약어. 기존 컬럼명이 이것들이면 따옴표 처리 or 개명
- 사용자 변수로 짠 "rolling" 쿼리는 8.0에서 결과가 달라질 수 있음. 순서 보장 경고 강화. 이번 기회에 전부 window로 교체 중
- 캐릭터셋 기본값이 utf8mb4로 바뀌어서 old client 호환성 체크. 우리 서비스는 5.7 시절부터 utf8mb4라 영향 없음
한 줄 결론: 윈도우 함수 없던 시절로 돌아가긴 싫다. 특히 대시보드/분석 쿼리가 읽기 쉬워짐. 다음 스프린트는 기존 집계 배치 8개를 전부 window로 리팩터링 예정.
