윈도우함수와 파티셔닝/인덱스

1. 윈도우 함수(Window Function)란?

윈도우 함수는 행과 행 간의 관계를 정의하여 성적의 순위(Rank), 누적 합계(Running Total), 혹은 이전/다음 행의 데이터(Lag/Lead) 등을 쉽게 계산할 수 있도록 지원하는 특수한 SQL 함수입니다.

일반적인 GROUP BY 집계 함수는 데이터를 그룹핑하면서 기존 행들의 상세 정보가 손실되고 압축되지만, 윈도우 함수는 기존 행의 상세 내역을 그대로 유지한 채 연산 결과만 별도의 열(Column)로 추가해 주는 결정적인 차이점을 가집니다.

2. 윈도우 함수와 루틴(Routines)의 차이점

윈도우 함수와 루틴(프로시저/함수)은 DB 내부에서 무언가를 계산한다는 점에서 혼동하기 쉽지만, 작동하는 레이어와 내부 메커니즘이 완전히 다릅니다.

3. 윈도우 함수의 예시

• 예시: 부서별(dept_id)로 직원의 급여(salary)가 높은 순서대로 순위를 매겨 조회하는 쿼리입니다.

SELECT
    emp_id,
    emp_name,
    dept_id,
    salary,
    -- dept_id로 구역을 나누고, salary 역순으로 정렬하여 순위를 부여
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees;

• PARTITION BY: 데이터를 어떤 그룹(윈도우창)으로 쪼갤지 결정합니다.
• ORDER BY: 정렬 기준을 정의하며, 순위나 누적 합의 계산 방향을 결정합니다.

4. 윈도우 함수를 활용하기 위한 인덱스(Index) 처리

윈도우 함수는 테이블 전체 데이터를 메모리에 올려 정렬(Filesort)하는 작업이 동반되므로, 트래픽이 높은 OLTP 환경에서는 심각한 병목을 유발할 수 있습니다. 이를 최적화하기 위해서는 OVER() 절에 지정된 컬럼들을 기준으로 복합 인덱스(Composite Index)를 정교하게 구성해야 합니다.

• 핵심 규칙: 인덱스 구성 전략 윈도우 함수가 인덱스를 타고 곧바로 정렬을 생략하기 위해서는 PARTITION BY 컬럼 + ORDER BY 컬럼 순서로 구성된 복합 인덱스가 필요합니다.

• 인덱스 설계 예시 위의 예시 쿼리를 최적화하기 위한 가장 이상적인 인덱스는 다음과 같습니다.

CREATE INDEX idx_dept_salary ON employees (dept_id, salary DESC);

5. MySQL / Oracle 기준 인덱스 고려 시 주의점

두 RDBMS는 인덱스를 다루는 내부 최적화 메커니즘과 저장 아키텍처 관점에서 다음과 같은 뚜렷한 차이점과 주의점을 가집니다.

• MySQL (InnoDB 엔진 기준)

• Clustered Index 구조의 영향 InnoDB는 기본 키(PK)를 기준으로 데이터가 물리적으로 정렬되어 저장되는 클러스터형 인덱스 구조를 취합니다. 이로 인해 보조 인덱스(Secondary Index)를 생성할 때 PK 컬럼이 인덱스 레코드 뒤에 자동으로 포함되는 내부 메커니즘을 이해해야 합니다. 만약 지나치게 무겁거나 과도한 복합 인덱스를 남용하면 버퍼 풀(Buffer Pool) 메모리를 불필요하게 점유하여 쓰기 성능 저하를 초래할 수 있습니다.

• 버전별 정렬 제한 확인 MySQL 8.0 이전 버전에서는 인덱스 생성 시 DESC 정렬을 물리적으로 지원하지 않아 역순 정렬 윈도우 함수 사용 시 무조건 내부 정렬(Filesort) 병목이 발생했습니다. 8.0 버전부터는 하향식 내림차순 인덱스를 공식 지원하므로, 사용 중인 버전의 아키텍처 한계를 명확히 파악하고 윈도우 함수 최적화를 진행해야 합니다.

• Oracle

• 비용 기반 옵티마이저(CBO)의 유연성과 변수 Oracle은 정교한 통계 정보를 기반으로 인덱스를 타는 것이 유리한지, 아니면 테이블 전체를 읽어 메모리(PGA) 영역에서 해시/정렬(Hash/Sort Window)하는 것이 유리한지 자율적으로 계산합니다. 따라서 데이터의 분포도(데이터가 골고루 섞여 있는지 여부)가 깨지면 인덱스가 존재하더라도 옵티마이저에 의해 무시될 수 있으므로 주기적인 통계 정보 갱신이 수반되어야 합니다.

• Global / Local 인덱스의 명확한 구분 파티셔닝 구조와 결합할 때, 특정 파티션에 종속되어 독립적으로 관리되는 Local Index와 파티션 범위를 넘어서는 전체 테이블 대상의 Global Index를 명확히 구분해야 합니다. 윈도우 함수의 PARTITION BY 기준 컬럼과 테이블의 파티션 키가 일치한다면 Local 인덱스를 활용하는 것이 관리와 성능 면에서 압도적으로 유리합니다.

6. 추가 개념: 파티셔닝(Partitioning) 및 관리 전략

1) 파티셔닝(Partitioning)의 본질

파티셔닝은 하나의 거대한 논리적 테이블을 물리적으로 분할된 여러 개의 작은 독립 파일(파티션)로 쪼개어 저장하는 성능 최적화 기술입니다.

애플리케이션 레이어에서는 단일 테이블을 조회하는 것처럼 투명하게 동작하지만, DB 엔진 내부에서는 검색 조건에 부합하는 특정 파티션 파일만 선별적으로 접근하는 파티션 프루닝(Partition Pruning)이 작동합니다. 이를 통해 대용량 데이터 조회 시 디스크 I/O 부하를 획기적으로 줄일 수 있으며, 비즈니스 성격에 따라 범위(Range), 리스트(List), 해시(Hash) 기준으로 데이터를 분할합니다.

2) 파티셔닝 데이터 생명주기 관리 (Data Lifecycle Management)

파티셔닝은 설계 단계보다 지속적인 운영 환경에서의 관리 자동화가 핵심입니다. 실무에서 가장 많이 활용되는 날짜 기준의 Range 파티셔닝을 안정적으로 운영하기 위해서는 다음과 같은 전략이 필수적입니다.

• 슬라이딩 윈도우(Sliding Window) 전략의 자동화
• 트래픽과 데이터가 지속적으로 누적됨에 따라 미래 시점의 새로운 파티션을 미리 열어주고(Add), 보존 기간이 지나 쓰이지 않는 과거의 데이터 파티션은 분리하여 백업하거나 제거(Drop/Truncate)하는 주기적인 관리가 필요합니다.

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, order_date) -- 파티션 키는 반드시 PK에 포함되어야 함
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p_202604 VALUES LESS THAN ('2026-05-01'),
    PARTITION p_202605 VALUES LESS THAN ('2026-06-01'),
    PARTITION p_202606 VALUES LESS THAN ('2026-07-01'), -- 현재 시점 파티션
    PARTITION p_202607 VALUES LESS THAN ('2026-08-01')  -- 미래 예비 파티션
);

• 이를 수동으로 관리하면 휴먼 에러로 인해 시스템 장애가 발생할 수 있으므로, DB 내부의 스케줄러(Event Scheduler / Job)나 외부 배치 시스템(Spring Batch 등)과 연동하여 다음 달 파티션을 자동으로 생성해 두는 완전 자동화 파이프라인 구축이 권장됩니다.

DELIMITER //
 
CREATE PROCEDURE sp_manage_sliding_window()
BEGIN
    DECLARE v_next_partition_name VARCHAR(20);
    DECLARE v_next_partition_date VARCHAR(20);
    DECLARE v_old_partition_name VARCHAR(20);
    
    -- 1) 메타데이터 락(Metadata Lock) 타임아웃 최소화 설정
    -- DDL 수행 시 트래픽 락 병목이 길어지는 것을 방지하기 위해 5초 뒤 즉시 실패 처리
    SET lock_wait_timeout = 5;
 
    -- 2) 2개월 뒤의 미래 파티션 정보 계산 (예: 현재 2026-06 -> 대상 2026-08)
    -- 파티션 이름은 p_202608, 상한선 조건 값은 2026-09-01이 됨
    SET v_next_partition_name = CONCAT('p_', DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 2 MONTH), '%Y%m'));
    SET v_next_partition_date = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 3 MONTH), '%Y-%m-01');
 
    -- 3) 3개월 전의 과거 파티션 이름 계산 (예: 현재 2026-06 -> 대상 2026-03)
    SET v_old_partition_name = CONCAT('p_', DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y%m'));
 
    -- =========================================================
    -- [STEP 1] 미래 파티션 미리 열어주기 (ADD PARTITION)
    -- =========================================================
    -- 파티션이 이미 존재하지 않는지 딕셔너리 정보 조회 후 동적 실행
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.partitions 
        WHERE table_name = 'orders' AND partition_name = v_next_partition_name
    ) THEN
        SET @add_sql = CONCAT('ALTER TABLE orders ADD PARTITION (PARTITION ', 
                              v_next_partition_name, ' VALUES LESS THAN (\'', v_next_partition_date, '\'))');
        PREPARE stmt_add FROM @add_sql;
        EXECUTE stmt_add;
        DEALLOCATE PREPARE stmt_add;
    END IF;
 
    -- =========================================================
    -- [STEP 2] 보존 기간이 지난 과거 파티션 제거 (DROP PARTITION)
    -- =========================================================
    -- 지우려는 과거 파티션이 실제로 존재하는지 확인 후 동적 실행
    IF EXISTS (
        SELECT 1 FROM information_schema.partitions 
        WHERE table_name = 'orders' AND partition_name = v_old_partition_name
    ) THEN
        SET @drop_sql = CONCAT('ALTER TABLE orders DROP PARTITION ', v_old_partition_name);
        PREPARE stmt_drop FROM @drop_sql;
        EXECUTE stmt_drop;
        DEALLOCATE PREPARE stmt_drop;
    END IF;
 
END //
 
DELIMITER ;

• 메타데이터 락(Metadata Lock) 및 다운타임 제어
• 파티션을 생성, 삭제, 혹은 쪼개는(Split) 행위는 데이터베이스 입장에서 테이블 구조를 변경하는 DDL(Data Definition Language) 작업에 해당합니다.
• 서비스 운영 중에 대량의 데이터가 적재된 파티션을 조작하면 순간적으로 메타데이터 락(Metadata Lock)이 발생하여 테이블 전체의 동시성 트래픽이 마비될 수 있습니다. 따라서 실무에서는 반드시 트래픽이 가장 적은 새벽 시간대에 작업을 배치하거나, 무중단 구조를 지원하는 온라인 DDL 옵션을 정밀하게 검토한 후 실행해야 시스템의 안정성을 확보할 수 있습니다.

-- MySQL 이벤트 스케줄러 활성화 확인
SET GLOBAL event_scheduler = ON;
 
-- 매월 1일 새벽 3시에 슬라이딩 윈도우 프로시저를 실행하는 스케줄러 등록
CREATE EVENT ev_monthly_partition_sliding_window
ON SCHEDULE EVERY 1 MONTH
STARTS DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01 03:00:00')
DO
    CALL sp_manage_sliding_window();