수천만 건 공공데이터 집계 분석 성능을 10배 끌어올리는 MySQL 8.0 윈도우 함수(Window Function)와 TempTable 최적화

전국 사업자 인허가/폐업 이력, 요양기관 진료 통계, 부동산 실거래가 등 수천만 건 규모의 공공데이터를 기반으로 분석 대시보드를 구축할 때 엔지니어가 마주하는 가장 까다로운 요구사항은 지역별 상위 N개 시설 추출, 월별 누적 집계, 직전 분기 대비 증감률(Lag/Lead) 계산입니다.

MySQL 5.7 이전 버전에서는 이러한 순위 및 누적 통계를 구하기 위해 상관 서브쿼리(Correlated Subquery)나 동일 테이블 간의 자기 조인(Self-Join, ON a.region = b.region AND a.date >= b.date)을 사용해야 했습니다. 이 방식은 $O(N^2)$의 연산 복잡도를 유발하여 수천만 건 테이블에서 디스크 임시 테이블(Disk-based Temporary Table)을 대량으로 생성하고 쿼리가 수십 초 이상 지연되는 심각한 I/O 병목을 유발합니다. 본 글에서는 단일 패스(Single-pass)로 행 집합을 스캔하는 MySQL 8.0 윈도우 함수(Window Function)의 실행 엔진 메커니즘과 메모리 오버플로우를 방어하는 TempTable 스토리지 엔진 튜닝 아키텍처를 분석합니다.

1. 자기 조인(Self-Join)의 디스크 I/O 병목과 윈도우 함수의 단일 패스 원리

기존의 GROUP BY 기반 집계는 여러 행을 그룹화하여 단 1개의 요약 행으로 축약하므로 원본 행의 세부 속성을 함께 유지할 수 없었습니다. 반면 윈도우 함수는 개별 행의 정체성을 보존하면서도 정의된 파티션 프레임(Partition Frame)에 걸친 집계값을 각 행마다 산출합니다.

• 자기 조인의 카티션 곱 붕괴: 지역별 순위를 매기기 위해 100만 건 데이터에 자기 조인을 걸면 내부적으로 수십억 번의 행 비교가 발생하여 Using temporary; Using filesort가 발생하고 디스크 I/O가 포화됩니다.
• 윈도우 함수의 스트리밍 실행 노드: MySQL 8.0 옵티마이저는 윈도우 전용 실행 노드(Windowing Execution Node)를 도입하여, 파티션 키와 정렬 키 순서로 1회 정렬된 데이터 스트림을 선형 순회($O(N)$)하면서 누적치와 순위를 실시간 계산합니다.
• B+Tree 인덱스 연계 최적화: PARTITION BY와 ORDER BY 순서에 일치하는 복합 인덱스(Composite Index)가 존재할 경우, 정렬 단계(Filesort) 자체를 완전히 생략하고 인덱스 순서대로 즉시 스트리밍 집계합니다.

2. 실무 고성능 윈도우 함수 SQL 패턴 DDL 및 실행 계획

지역별 요양기관 통계 테이블에서 시도별 병상수 순위(DENSE_RANK) 및 연도별 누적 개설수(SUM OVER)를 10ms 이내로 도출하는 프로덕션 SQL 명세입니다.

-- 1. 파티션 및 정렬 순서에 최적화된 복합 인덱스 설계
-- PARTITION BY sheng_code ORDER BY bed_count DESC 조건에 완전 일치
ALTER TABLE clinic_facility_stats
ADD INDEX idx_region_bed_opt (sheng_code, bed_count DESC, clinic_id);

-- 2. 윈도우 함수를 활용한 지역별 상위 순위 및 누적 집계 단일 패스 쿼리
EXPLAIN ANALYZE
WITH RankedFacilities AS (
    SELECT
        clinic_id,
        facility_name,
        sheng_code,
        bed_count,
        -- 지역(sheng_code) 내 병상수 기준 순위 (단일 패스 정렬)
        DENSE_RANK() OVER (
            PARTITION BY sheng_code 
            ORDER BY bed_count DESC
        ) AS region_bed_rank,
        -- 지역 내 누적 병상수 롤링 합계
        SUM(bed_count) OVER (
            PARTITION BY sheng_code 
            ORDER BY bed_count DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total_beds
    FROM clinic_facility_stats
    WHERE status = 'ACTIVE'
)
SELECT *
FROM RankedFacilities
WHERE region_bed_rank <= 10;

3. TempTable 스토리지 엔진과 인메모리 한도 튜닝

윈도우 함수 연산 시 복합 인덱스를 타지 못하면 MySQL은 내부 임시 테이블을 생성합니다. MySQL 8.0부터는 기존의 MEMORY 엔진 대신 가변 길이 문자열(VARCHAR)을 최적화하는 TempTable 엔진이 기본 적용됩니다.

-- MySQL 8.0 TempTable 인메모리 한도 점검 및 튜닝
-- 1. TempTable이 RAM에서 수용 가능한 최대 메모리 크기 (기본값 1GB)
-- 수천만 건 집계 시 2GB~4GB로 확장하여 디스크 스필(Spill to Disk) 방지
SET GLOBAL temptable_max_ram = 2 * 1024 * 1024 * 1024; -- 2GB

-- 2. RAM 한도 초과 시 임시 테이블 보관 방식 설정 (기본값: TempTable MMAP)
-- MMAP 페이징 오버헤드를 피하기 위해 메모리 초과 시 InnoDB 디스크 테이블로 직접 전환
SET GLOBAL temptable_max_mmap = 0; -- MMAP 비활성화 후 InnoDB 디스크 테이블 직접 할당

4. 결론 및 도입 성과

수천만 건의 공공데이터 집계 환경에서 레거시 자기 조인 방식을 걷어내고 MySQL 8.0 윈도우 함수와 최적화된 복합 인덱스를 적용하면, 수십 초가 소요되던 분석 쿼리를 50ms 이내로 단축할 수 있습니다. TempTable 인메모리 버퍼 튜닝과 결합하여 대규모 동시 분석 트래픽 속에서도 디스크 I/O 고갈 없는 고성능 데이터 파이프라인을 구축할 수 있습니다.

출처 및 1차 공식 기술 문서

• MySQL 8.0 Reference Manual — Window Function Concepts and Syntax
• MySQL 8.0 Reference Manual — Internal Temporary Table Storage Engine
• MySQL 8.0 Reference Manual — Window Function Optimization in Execution Engine