MySQL 8.0 윈도우 함수로 순위·누적합·이동평균 최적화
MySQL 8.0이 윈도우 함수(Window Function)를 공식 지원하기 시작하면서, 복잡한 분석 쿼리를 작성하는 방식이 크게 달라졌습니다.
목차
- 개요
- 윈도우 함수의 동작 원리
- 순위 함수 — RANK, DENSE_RANK, ROW_NUMBER
- 누적합과 분포 분석
- 이동평균과 프레임 제어
- 성능 최적화와 인덱스 전략
- 운영 환경 적용 시 고려사항
- 맺음말
개요
문제 배경
MySQL 8.0이 윈도우 함수(Window Function)를 공식 지원하기 시작하면서, 복잡한 분석 쿼리를 작성하는 방식이 크게 달라졌습니다. 윈도우 함수는 SQL:2003 표준에 포함된 기능으로, PostgreSQL이나 Oracle에서는 오래전부터 사용해 왔지만 MySQL은 8.0(2018년 GA 출시)에 이르러서야 도입했습니다. 순위 계산, 누적합, 이동평균처럼 "행 간(row-to-row) 관계"를 다루는 연산은 분석 데이터베이스에서 일상적으로 등장합니다. 실시간 대시보드, 매출 리포트, 사용자 행동 분석 등 데이터를 기반으로 의사결정을 지원하는 대부분의 쿼리가 이런 패턴을 포함합니다. MySQL 8.0 윈도우 함수를 제대로 이해하면 기존에 수십 줄이 필요했던 쿼리를 절반 이하로 줄이고, 성능도 함께 끌어올릴 수 있습니다.
윈도우 함수의 핵심 가치는 애플리케이션 레이어가 아닌 데이터베이스 레이어에서 분석 연산을 처리한다는 점에 있습니다. 데이터를 모두 가져와 애플리케이션에서 정렬·집계하는 방식은 네트워크 전송 비용이 크고, 대용량 데이터에서는 메모리 압박으로 이어집니다. 반면 DB 레이어에서 윈도우 함수를 처리하면 옵티마이저가 효율적인 실행 계획을 선택할 수 있고, 전송하는 결과 행 수도 최소화됩니다.
기존 방식의 한계
MySQL 5.7 이하에서 순위를 구하려면 사용자 변수(user-defined variable)를 활용한 @rank := @rank + 1 패턴이 일반적이었습니다. 이 방식은 실행 순서가 보장되지 않아 ORDER BY와 결합할 때 예기치 않은 결과를 낳을 수 있었고, MySQL 8.0에서는 사용자 변수의 평가 순서가 변경되어 아예 동작하지 않는 경우도 생겼습니다. 공식 문서에서도 사용자 변수 기반 순위 패턴의 동작은 보장되지 않는다고 명시합니다.
누적합의 경우에는 각 행에 대해 그 이전 행을 모두 집계하는 상관 서브쿼리(correlated subquery)를 써야 했는데, 이는 O(n²) 복잡도를 가집니다. 100만 행 테이블에서 실행하면 쿼리 시간이 분 단위를 넘기는 일도 드물지 않았습니다. 이동평균도 마찬가지로, 슬라이딩 윈도우를 표현하려면 자기 조인(self-join)에 범위 조건을 조합해야 했고, 실행 계획이 복잡해져 옵티마이저가 최적 인덱스를 선택하기 어려웠습니다.
윈도우 함수의 동작 원리
OVER 절과 파티션
윈도우 함수의 핵심은 OVER 절에 있습니다. OVER 절은 해당 함수가 적용될 "윈도우(논리적 행 집합)"를 정의합니다. 일반 집계 함수(SUM, COUNT 등)가 GROUP BY와 결합해 여러 행을 하나의 결과 행으로 축약하는 것과 달리, 윈도우 함수는 원래 행의 수를 유지하면서 각 행에 집계 결과를 덧붙입니다. 이 차이가 윈도우 함수를 분석 쿼리에서 강력하게 만드는 근본 이유입니다.
PARTITION BY는 행을 논리적 그룹으로 나누는 역할을 합니다. 예를 들어 PARTITION BY department_id라고 지정하면, 같은 부서 내에서만 순위가 계산됩니다. PARTITION BY가 없으면 전체 결과 집합이 하나의 파티션으로 처리됩니다. ORDER BY는 파티션 내에서 행의 순서를 결정하며, 이 순서는 순위 함수나 프레임 기반 집계 함수의 결과에 직접 영향을 줍니다.
중요한 점은, 윈도우 함수는 WHERE, GROUP BY, HAVING 처리가 모두 끝난 뒤에 적용된다는 것입니다. 따라서 WHERE 절에서 윈도우 함수의 결과를 필터링하는 것은 불가능하며, SELECT 결과를 FROM으로 감싼 서브쿼리 또는 CTE에서만 필터링할 수 있습니다. 이 특성을 모르고 작성하면 Unknown column 오류를 만나게 됩니다.
윈도우 함수는 쿼리 실행 파이프라인의 후반부에 위치하므로, WHERE나 HAVING이 아닌 외부 서브쿼리나 CTE에서만 그 결과를 필터링할 수 있습니다.
프레임 정의
파티션이 행의 "그룹"을 정의한다면, 프레임(Frame)은 각 행 기준으로 집계에 포함할 "행의 범위"를 정의합니다. 프레임은 ROWS 또는 RANGE 키워드로 시작하며, BETWEEN ... AND ... 구문으로 경계를 지정합니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 파티션의 처음부터 현재 행까지를 포함하는 가장 일반적인 누적 프레임입니다. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 현재 행 포함 직전 2개 행, 총 3개 행의 슬라이딩 윈도우를 의미합니다. 이동평균이 대표적인 사용 사례입니다.
ROWS와 RANGE의 차이는 동률(tie) 처리에 있습니다. ROWS는 물리적 행 번호 기준으로 경계를 정하며, RANGE는 ORDER BY 값이 같은 행들을 하나의 단위로 묶어 처리합니다. 날짜 기반 이동평균을 구할 때 같은 날짜의 여러 거래를 하나의 날짜 단위로 묶으려면 RANGE가 적합합니다. 이 차이를 혼동하면 집계 범위가 의도와 달라질 수 있으므로 주의가 필요합니다.
동률 행이 없고 물리적 행 수를 고정하려면 ROWS, ORDER BY 값 단위로 묶어 처리해야 한다면 RANGE를 선택합니다.
실행 계획과 처리 순서
MySQL 8.0의 옵티마이저는 윈도우 함수를 처리할 때 내부적으로 임시 테이블(internal temporary table)을 생성합니다. EXPLAIN FORMAT=JSON으로 실행 계획을 분석해 보면 "windowing" 노드가 등장하며, 이 단계에서 파티션 키와 ORDER BY 컬럼에 따른 정렬이 수행됩니다.
여러 윈도우 함수가 동일한 PARTITION BY와 ORDER BY를 공유한다면, MySQL은 이를 하나의 정렬 패스로 통합해 처리합니다. 반면 서로 다른 파티션 키를 가진 윈도우 함수가 같은 쿼리에 있으면 각각 별도의 정렬 패스가 필요하게 되어 성능이 급격히 저하될 수 있습니다. 이 때문에 한 쿼리에서 여러 윈도우 함수를 쓸 때는 가능한 동일한 OVER 절 정의를 공유하도록 설계하는 것이 중요합니다. MySQL 8.0은 이를 위해 WINDOW 이름 지정 문법을 지원합니다.
순위 함수 — RANK, DENSE_RANK, ROW_NUMBER
세 함수의 차이와 선택 기준
MySQL 8.0은 RANK(), DENSE_RANK(), ROW_NUMBER() 세 가지 순위 함수를 제공합니다. 이름이 비슷하지만 동률(tie) 처리 방식에서 큰 차이가 있으며, 잘못 선택하면 비즈니스 요구사항을 충족하지 못하는 결과를 낳습니다.
ROW_NUMBER()는 동률에 관계없이 각 행에 고유한 번호를 순서대로 부여합니다. 같은 점수를 가진 두 행이 있어도 1, 2로 구분됩니다. 페이지네이션처럼 각 행이 고유한 번호를 가져야 하는 경우에 적합합니다. 단, ORDER BY에 유일성이 없을 때 동일 조건의 두 행 중 어느 쪽이 1번을 받을지는 비결정론적(non-deterministic)입니다.
RANK()는 동률인 행에 같은 순위를 부여하고, 다음 순위는 건너뜁니다. 예를 들어 1위가 두 명이면 두 행 모두 1위이고 다음은 3위가 됩니다. 스포츠 순위표처럼 동률을 동일 순위로 처리해야 하는 경우에 씁니다.
DENSE_RANK()도 동률에 같은 순위를 부여하지만, 다음 순위를 건너뛰지 않습니다. 1위 두 명이 있으면 다음은 2위입니다. 순위 번호에 공백 없이 연속적인 값이 필요한 경우, 특히 상위 N개 순위의 행만 필터링할 때 DENSE_RANK() <= N 조건을 쓰면 의도한 결과를 보장합니다.
| 함수 | 동률 처리 | 순위 공백 | 고유성 보장 | 주요 사용 사례 |
|---|---|---|---|---|
ROW_NUMBER() |
임의 구분 | 없음 | 항상 | 페이지네이션, 고유 식별 |
RANK() |
동일 순위 | 있음 (1,1,3) | 없음 | 스포츠 리더보드 |
DENSE_RANK() |
동일 순위 | 없음 (1,1,2) | 없음 | 상위 N개 필터링 |
NTILE(n) |
n등분 분류 | 없음 | 없음 | 백분위 그룹 분류 |
파티션 순위와 복합 정렬
PARTITION BY를 활용한 파티션 순위는 부서별·카테고리별 상위 N개 항목을 구할 때 매우 유용합니다. 전통적인 방법으로는 각 파티션을 서브쿼리로 처리한 뒤 UNION ALL로 합쳐야 했는데, 윈도우 함수를 쓰면 단일 쿼리로 처리할 수 있습니다. 코드량이 줄어들 뿐 아니라 옵티마이저가 전체 데이터를 한 번에 스캔하는 계획을 수립할 수 있어 성능도 개선됩니다.
복합 정렬 기준, 예를 들어 주요 정렬은 매출 금액 내림차순이고 같은 금액이면 등록일 오름차순으로 순위를 정하고 싶다면, ORDER BY sales_amount DESC, created_at ASC처럼 OVER 절의 ORDER BY에 여러 컬럼을 지정하면 됩니다. 이때 ROW_NUMBER()를 쓰면 항상 유일한 순위가 부여되므로 결정론적 결과를 보장합니다.
파티션 순위는 윈도우 함수로 순위를 계산한 뒤, 반드시 CTE나 서브쿼리 밖에서 순위 조건을 필터링해야 합니다.
실전 적용: 부서별 상위 N명
부서별 상위 급여 2명을 추출하는 것은 인사 분석에서 자주 쓰이는 패턴입니다. DENSE_RANK()를 선택한 이유는 동일 급여자가 2위 안에 있을 때 모두 포함하기 위함입니다. ROW_NUMBER()를 썼다면 동률 중 임의로 한 명만 포함되어 비즈니스 요건을 충족하지 못합니다.
아래 쿼리는 CTE(Common Table Expression)를 활용해 윈도우 함수 결과를 중간 결과로 명명하고, 외부 쿼리에서 필터링하는 구조를 명확하게 표현합니다.
-- 부서별 상위 급여 2위까지 추출 (동률 포함)
WITH ranked AS (
SELECT
emp_id,
dept_id,
salary,
-- 급여 기준 부서 내 순위 (동률 = 같은 순위, 공백 없음)
DENSE_RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE status = 'ACTIVE'
)
SELECT emp_id, dept_id, salary, salary_rank
FROM ranked
WHERE salary_rank <= 2
ORDER BY dept_id, salary_rank;
-- salary_rank 1, 2인 행만 반환
-- 동일 급여자가 있으면 같은 순위로 모두 포함
핵심 포인트는 CTE 내부에서 WHERE salary_rank <= 2를 쓸 수 없다는 점입니다. 윈도우 함수가 WHERE 처리 이후에 평가되기 때문에, 같은 SELECT 내 WHERE에서는 윈도우 함수의 결과 컬럼이 아직 존재하지 않습니다. CTE로 감싸 바깥 쿼리에서 필터링하는 패턴은 윈도우 함수를 활용하는 모든 쿼리에서 반복적으로 등장합니다.
누적합과 분포 분석
SUM OVER와 집계 윈도우
집계 윈도우 함수는 SUM, COUNT, AVG, MIN, MAX 같은 표준 집계 함수를 윈도우 함수로 사용하는 형태입니다. GROUP BY와 달리 행 수를 줄이지 않으면서 각 행에 집계값을 추가합니다. 이를 활용해 누적합(running total), 파티션 내 비율, 각 행의 전체 대비 점유율 등을 단일 쿼리로 계산할 수 있습니다.
누적합의 프레임은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 표준입니다. UNBOUNDED PRECEDING은 파티션의 첫 행, CURRENT ROW는 현재 행을 의미합니다. 이 프레임은 ORDER BY가 지정되면 MySQL이 기본값으로 적용하므로 생략도 가능하지만, 명시적으로 작성하면 쿼리 의도가 명확해집니다.
각 행의 전체 합 대비 비율(contribution ratio)은 SUM(amount) OVER (PARTITION BY dept_id) 처럼 프레임 없이 파티션 전체를 합산하고, 행 값을 나누어 계산합니다. 이 패턴은 파이 차트 데이터를 쿼리 레벨에서 직접 준비할 때 유용합니다. 애플리케이션 루프로 처리하던 작업을 DB 레이어로 내려 네트워크 전송량과 메모리 사용을 줄일 수 있습니다.
집계 윈도우는 원본 행 수를 그대로 유지하면서 파티션 내 누적합과 전체 합을 동시에 각 행에 추가합니다.
PERCENT_RANK와 CUME_DIST
분포 분석에는 PERCENT_RANK()와 CUME_DIST()가 쓰입니다. PERCENT_RANK()는 (rank - 1) / (total_rows - 1) 공식으로 계산되며 0~1 범위의 값을 반환합니다. 가장 낮은 값은 0.0, 가장 높은 값은 1.0입니다. CUME_DIST()는 현재 행의 값 이하인 행의 비율로, 누적 분포 함수(CDF)에 해당하며 (0, 1] 범위를 가집니다.
이 두 함수는 성능 분위(percentile) 기반 분류에 유용합니다. 상위 10% 고객을 찾으려면 PERCENT_RANK() >= 0.9 또는 CUME_DIST() >= 0.9 조건을 사용합니다. 차이는 동률 처리 방식에 있으며, CUME_DIST()는 동률인 행을 같은 누적 비율로 처리합니다. 분위 분류가 목적이라면 NTILE(n)이 더 직관적인 경우가 많으며, 행을 n개 버킷으로 균등 분할해 각 행에 1~n 정수를 부여합니다.
| 함수 | 반환 범위 | 동률 처리 | 주요 사용 사례 |
|---|---|---|---|
PERCENT_RANK() |
0.0 ~ 1.0 | 같은 값 = 같은 백분위 | 상대 순위 점수 |
CUME_DIST() |
(0, 1] | 같은 값 = 같은 CDF | 누적 분포 분석 |
NTILE(n) |
1 ~ n 정수 | 행 수 기준 균등 분할 | 분위 그룹 분류 |
누적합 쿼리 최적화
누적합 쿼리를 작성할 때 흔히 하는 실수는 CTE를 중첩하거나 서브쿼리를 겹쳐 쓰는 것입니다. MySQL 8.0의 윈도우 함수는 내부적으로 이미 효율적인 알고리즘으로 구현되어 있으므로, 단순하게 쓸수록 옵티마이저가 최적 계획을 선택할 여지가 커집니다. 특히 ORDER BY에 사용하는 컬럼에 인덱스가 있으면 윈도우 함수 처리를 위한 정렬 비용이 크게 줄어듭니다.
(partition_key, order_key)로 구성된 복합 인덱스가 있으면 MySQL은 정렬 없이 인덱스 스캔 순서로 바로 윈도우 계산을 진행할 수 있습니다. 이 경우 EXPLAIN의 Extra 컬럼에 Using filesort가 나타나지 않으며, 특히 대용량 테이블에서 쿼리 시간이 크게 단축됩니다.
누적합 쿼리의 성능 병목은 대부분
ORDER BY에 인덱스가 없어 발생하는filesort입니다.(partition_key, order_key)복합 인덱스 하나로 해결되는 경우가 많습니다.
이동평균과 프레임 제어
ROWS vs RANGE 프레임
이동평균(Moving Average)은 시계열 데이터의 노이즈를 제거하고 추세를 파악하는 데 널리 쓰입니다. MySQL 8.0에서 이동평균은 AVG 집계 함수와 슬라이딩 프레임 정의로 구현합니다. 프레임 경계를 어떻게 정의하느냐에 따라 결과가 달라지므로, ROWS와 RANGE의 차이를 명확히 이해해야 합니다.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW는 현재 행 포함 이전 6개 행, 총 7개 행의 평균을 구합니다. 이 방식은 날짜 간격과 무관하게 물리적 행 수를 기준으로 합니다. 데이터에 결측일(missing day)이 있으면 실제 7일이 아닌 임의 기간의 평균이 될 수 있습니다. 반면 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW(MySQL 8.0.2 이상)는 ORDER BY 컬럼이 날짜 타입일 때 실제 6일 이전부터 현재까지를 프레임으로 잡습니다. 결측일이 있어도 정확히 7일 범위의 평균을 계산합니다.
RANGE INTERVAL은 날짜 타입의 ORDER BY를 요구하므로, 정수 기반 인덱스가 있다면 성능 트레이드오프를 고려해야 합니다. 날짜 컬럼에 인덱스를 두어야 하는 경우에는 DATE 또는 DATETIME 타입을 명시적으로 사용하는 것이 권장됩니다.
결측일이 있는 시계열에서 ROWS는 물리적 행 수 기준, RANGE INTERVAL은 실제 날짜 범위 기준으로 이동평균을 계산합니다.
이동평균 구현
7일 이동평균 쿼리를 작성할 때, 파티션 초기 행(프레임 시작 전)의 처리에 주의가 필요합니다. 데이터의 처음 6개 행은 7개 미만의 행으로 평균을 계산하게 되는데, 이를 "불완전 윈도우(partial window)"라고 합니다. 비즈니스 요구에 따라 불완전 윈도우를 제외하거나 그대로 사용할 수 있습니다. window_size 컬럼을 함께 계산해 두면 불완전 윈도우 행을 식별하는 실용적인 방법이 됩니다.
-- 상품별 7일 이동평균 매출 (불완전 윈도우 확인 포함)
SELECT
product_id,
sale_date,
daily_revenue,
-- 7일 이동평균: 현재 포함 직전 6일 (총 7개 행)
ROUND(
AVG(daily_revenue) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2
) AS moving_avg_7d,
-- 현재 윈도우에 포함된 실제 행 수 (불완전 윈도우 감지)
COUNT(*) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS window_size
FROM daily_sales
WHERE sale_date >= '2026-01-01'
ORDER BY product_id, sale_date;
-- window_size = 7: 완전한 7일 이동평균
-- window_size < 7: 초기 구간의 불완전 윈도우
window_size 컬럼은 이동평균의 신뢰도를 확인하는 실용적인 수단입니다. 데이터 파이프라인에서는 이 값을 기준으로 불완전 윈도우 행을 시각화에서 제외하거나, 별도로 표시하는 경우가 많습니다. 두 개의 윈도우 함수가 동일한 OVER 정의를 공유하므로 정렬 패스가 한 번만 발생합니다.
LAG/LEAD로 전후 비교
LAG()와 LEAD()는 현재 행 기준으로 이전 또는 이후 행의 값을 참조하는 함수입니다. 서로 다른 행 간의 변화량(delta), 전일 대비 성장률, 연속 이벤트 간 시간 간격 등을 자기 조인 없이 처리할 수 있습니다. LAG(column, n, default) 형태로 사용하며, n번째 이전 행의 값을 가져옵니다. 이전 행이 없을 때 반환할 기본값을 세 번째 인자로 지정하지 않으면 NULL을 반환합니다.
성장률을 계산할 때는 이전 값이 0 또는 NULL인 경우를 NULLIF나 CASE로 처리해야 0 나누기 오류를 방지할 수 있습니다. LEAD()는 LAG()와 반대로 다음 행의 값을 참조하며, 앞으로 발생할 이벤트까지의 시간 차이 또는 예상 값과의 비교에 활용합니다.
LAG와 LEAD는 자기 조인 없이 행 간 차이와 변화율을 단일 SELECT에서 계산하므로, 반드시 이전값이 NULL인 경우를 처리해야 합니다.
성능 최적화와 인덱스 전략
윈도우 함수와 쿼리 실행 계획
윈도우 함수가 포함된 쿼리의 성능을 분석할 때는 EXPLAIN FORMAT=TREE 또는 EXPLAIN ANALYZE를 사용하는 것이 효과적입니다. MySQL 8.0.18 이상에서 사용 가능한 EXPLAIN ANALYZE는 실제 실행 시간과 처리 행 수를 함께 출력해, 이론적 계획과 실제 동작의 차이를 파악하는 데 유용합니다. 특히 옵티마이저가 예상한 행 수와 실제 행 수가 크게 다를 때는 통계 정보 갱신(ANALYZE TABLE)이 필요한 경우도 있습니다.
실행 계획에서 주목해야 할 지점은 크게 세 곳입니다. 첫 번째는 Windowing 노드 이전의 정렬 비용입니다. 이 정렬이 filesort로 처리되면 메모리 또는 디스크 기반 정렬이 발생합니다. 두 번째는 접근 방식으로, 인덱스 스캔인지 풀 테이블 스캔인지에 따라 비용 차이가 큽니다. 세 번째는 Using temporary 표시입니다. 이는 임시 테이블이 생성됨을 의미하며, 메모리 제약 시 디스크 스필(spill)이 발생할 수 있어 tmp_table_size와 max_heap_table_size 설정을 확인해야 합니다.
EXPLAIN ANALYZE → filesort 여부 → 복합 인덱스 추가 순서로 윈도우 함수 쿼리 성능을 진단하고 개선합니다.
인덱스 설계
윈도우 함수 성능을 극대화하는 인덱스 전략의 핵심은 (PARTITION BY 컬럼, ORDER BY 컬럼) 순서로 구성된 복합 인덱스입니다. 이 인덱스가 있으면 MySQL이 별도 정렬 없이 인덱스 순서대로 행을 읽으면서 윈도우 계산을 수행할 수 있습니다. 예를 들어 OVER (PARTITION BY dept_id ORDER BY salary DESC) 패턴을 자주 사용한다면 (dept_id, salary DESC) 복합 인덱스를 생성하는 것이 최적입니다. MySQL 8.0부터 내림차순 인덱스를 지원하므로 ORDER BY salary DESC를 인덱스 순서와 정확히 일치시킬 수 있습니다.
그러나 인덱스가 항상 정답은 아닙니다. 대량 삽입·업데이트가 빈번한 OLTP 테이블에 분석용 인덱스를 다수 추가하면 쓰기 성능이 저하됩니다. 이 경우 읽기 전용 복제본(read replica)이나 분리된 분석 스키마에서 윈도우 함수 쿼리를 실행하는 아키텍처가 더 적합합니다.
| 상황 | 인덱스 전략 | 기대 효과 |
|---|---|---|
| 단일 PARTITION BY만 있음 | (partition_key) |
파티션 분리 비용 감소 |
| PARTITION + ORDER BY | (partition_key, order_key) |
filesort 제거 |
| ORDER BY DESC 포함 | (pk, order_key DESC) |
역순 정렬 비용 제거 |
| 빈번한 쓰기 테이블 | 읽기 복제본 분리 | 쓰기 성능 보호 |
| 수천만 행 이상 분석 | OLAP 시스템 분리 | MySQL 부하 차단 |
대안 기술과 비교
윈도우 함수가 MySQL 8.0에서 좋은 선택이긴 하지만, 모든 상황에서 최선은 아닙니다. 비교 대상이 되는 접근법은 크게 세 가지입니다.
첫 번째는 애플리케이션 레이어 계산입니다. 데이터를 DB에서 모두 가져와 Java나 Python에서 순위·누적합을 계산하는 방식으로, 쿼리 설계가 단순해지지만 네트워크 전송 비용과 메모리 사용량이 증가합니다. 데이터 볼륨이 수백 행 이하인 경우에는 유효한 선택일 수 있습니다.
두 번째는 전용 OLAP 시스템 활용입니다. ClickHouse, BigQuery, Redshift 같은 컬럼 지향(columnar) 데이터베이스는 윈도우 함수를 포함한 분석 쿼리에서 MySQL보다 훨씬 뛰어난 성능을 냅니다. 수억 행 이상의 분석 워크로드라면 MySQL 윈도우 함수보다 OLAP 시스템이 더 적합합니다.
세 번째는 **사전 집계(pre-aggregation)**입니다. 이동평균이나 누적합을 주기적 배치로 미리 계산해 별도 테이블에 저장하는 방식으로, 읽기 성능은 극대화되지만 실시간성이 없고 배치 파이프라인 관리 비용이 발생합니다. 수 분 이내 지연이 허용되는 대시보드에서는 현실적인 선택입니다.
운영 환경 적용 시 고려사항
흔한 실수와 함정
윈도우 함수를 처음 적용할 때 가장 자주 만나는 실수는 WHERE 절에서 윈도우 함수 결과를 직접 필터링하려는 시도입니다. WHERE rank <= 3처럼 쓰면 Unknown column 'rank' in 'where clause' 오류가 발생합니다. 반드시 CTE나 서브쿼리로 감싸야 합니다. 이 오류는 쿼리 실행 순서를 이해하면 자연스럽게 납득됩니다.
두 번째 함정은 PARTITION BY 없이 큰 테이블에서 ORDER BY만 지정하는 경우입니다. MySQL은 테이블 전체를 하나의 파티션으로 처리하고 전체 정렬을 수행합니다. 1000만 행 테이블에서 이런 쿼리를 실행하면 임시 테이블이 디스크에 스필되어 수십 초가 걸릴 수 있습니다. PARTITION BY를 통해 데이터를 작은 단위로 분할하거나, WHERE 조건으로 처리 대상 행을 미리 줄이는 것이 중요합니다.
세 번째는 여러 윈도우 함수에서 다른 OVER 절을 사용하는 경우입니다. 각각 별도 정렬 패스가 발생해 성능이 저하됩니다. MySQL 8.0은 WINDOW 이름 지정 문법으로 공통 윈도우 정의를 재사용할 수 있습니다. WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC) 처럼 선언하면 여러 함수에서 OVER w로 참조할 수 있으며, 옵티마이저는 이를 하나의 정렬 패스로 처리합니다.
동일한 OVER 절은 WINDOW 이름으로 재사용하면 정렬 패스를 하나로 줄여 성능을 높일 수 있습니다.
모니터링과 디버깅
운영 환경에서 윈도우 함수 쿼리의 성능 문제를 추적하려면 MySQL의 performance_schema와 sys 스키마를 활용합니다. sys.statements_with_full_table_scans 뷰는 풀 테이블 스캔이 발생하는 쿼리를 집계해 보여주며, 윈도우 함수 쿼리에 인덱스가 활용되지 않는 경우를 찾아낼 수 있습니다. 슬로우 쿼리 로그를 long_query_time = 1 수준으로 설정해 두면 윈도우 함수 쿼리가 병목이 될 때 즉시 감지됩니다.
sort_buffer_size와 tmp_table_size 시스템 변수는 윈도우 함수 정렬 및 임시 테이블 처리에 직접 영향을 줍니다. 기본값인 256KB는 소규모 쿼리에는 충분하지만, 대용량 파티션을 처리할 때는 부족할 수 있습니다. 세션 수준에서 SET SESSION sort_buffer_size = 4194304처럼 일시적으로 늘려 효과를 테스트해 볼 수 있습니다. 단, 이 값을 전역적으로 과도하게 높이면 동시 쿼리가 많을 때 전체 메모리 사용량이 급증할 수 있습니다.
| 모니터링 수단 | 확인 내용 | 참고 임계값 |
|---|---|---|
EXPLAIN ANALYZE |
실제 행 수 및 실행 시간 | — |
| 슬로우 쿼리 로그 | 임계 초과 쿼리 목록 | long_query_time = 1 |
sys.statements_with_full_table_scans |
풀 스캔 쿼리 | — |
performance_schema |
I/O 대기, 잠금 이벤트 | — |
sort_buffer_size |
정렬 버퍼 용량 | 기본 256KB |
tmp_table_size |
메모리 내 임시 테이블 상한 | 기본 16MB |
확장과 마이그레이션
MySQL 5.7에서 8.0으로 마이그레이션할 때 기존 사용자 변수 기반 순위 쿼리는 윈도우 함수로 교체하는 것을 강력히 권장합니다. MySQL 8.0에서 사용자 변수의 동작이 변경되어 기존 쿼리가 잘못된 결과를 낼 수 있으며, 공식 문서(MySQL 8.0 Release Notes)에서도 이 변경을 명시합니다.
데이터 볼륨이 수천만 행 이상으로 증가하면 MySQL 단독으로는 실시간 분석에 한계가 옵니다. 이 시점에서는 CDC(Change Data Capture)를 활용해 MySQL 변경 데이터를 스트리밍으로 분석 시스템에 동기화하는 아키텍처가 현실적인 선택입니다. Debezium과 Kafka를 활용한 CDC 파이프라인은 MySQL의 OLTP 성능을 유지하면서 분석 쿼리를 별도 시스템으로 분리하는 일반적인 패턴입니다.
파티션 테이블을 사용하는 경우, 윈도우 함수의 PARTITION BY와 MySQL 테이블 파티션은 전혀 다른 개념임을 주의해야 합니다. 테이블 파티션은 물리적 스토리지를 나누는 것이고, 윈도우 함수의 파티션은 논리적 그룹입니다. 테이블 파티션 프루닝이 올바르게 동작하려면 WHERE 절에 파티션 키 조건을 포함해야 하며, 이 조건은 윈도우 함수가 처리할 행 수를 줄이는 효과도 함께 가져옵니다.
맺음말
핵심 요약
MySQL 8.0 윈도우 함수는 순위 계산부터 누적합, 이동평균, 전후 행 비교까지 분석 쿼리의 핵심 패턴을 SQL 레벨에서 간결하게 표현할 수 있게 합니다. OVER 절의 PARTITION BY와 ORDER BY, 그리고 ROWS와 RANGE 프레임 정의를 정확히 이해하는 것이 올바른 결과를 얻는 첫 번째 조건입니다. 윈도우 함수는 WHERE와 HAVING 이후에 실행되므로, 결과를 필터링하려면 반드시 외부 서브쿼리나 CTE를 활용해야 합니다. 순위 함수 세 가지(ROW_NUMBER, RANK, DENSE_RANK)는 동률 처리 방식에서 다르므로, 비즈니스 요건에 맞는 함수를 선택하는 것이 중요합니다.
성능 관점에서는 (PARTITION BY 컬럼, ORDER BY 컬럼) 복합 인덱스가 filesort를 제거하는 핵심 수단입니다. EXPLAIN ANALYZE로 실제 실행 계획을 확인하는 습관이 성능 문제를 조기에 발견하는 데 도움이 됩니다. 동일한 OVER 정의를 WINDOW 절로 통합하면 정렬 패스를 줄여 성능을 추가로 개선할 수 있습니다.
적용 판단 기준
MySQL 8.0 윈도우 함수를 적용할 가치가 있는 상황은 명확합니다. 기존에 사용자 변수, 자기 조인, 상관 서브쿼리로 구현된 순위·집계 쿼리가 있다면 윈도우 함수로 교체하는 것이 코드 품질과 성능 모두를 향상시킵니다. 처리 행 수가 수백만 행 이내이고 적절한 복합 인덱스가 있다면, 실시간 분석 쿼리에도 충분히 활용할 수 있습니다. 또한 MySQL 5.7 → 8.0 마이그레이션 시점에는 사용자 변수 기반 패턴의 전면 교체가 안전성과 유지보수 면에서 필수적입니다.
반면 수천만 행 이상의 대용량 분석 워크로드에서 실시간 응답이 필요하다면, MySQL 윈도우 함수보다 ClickHouse나 BigQuery 같은 OLAP 시스템이나 사전 집계 테이블 접근이 더 적합합니다. MySQL은 OLTP와 중간 규모 분석을 함께 처리할 때 윈도우 함수가 강점을 발휘하며, 그 경계를 넘어서는 순간에는 전용 분석 시스템으로의 전환을 진지하게 고려해야 합니다.