MySQL 파티션 함수(윈도우 함수)와 인덱스의 관계

목차

• 소개
• 윈도우 함수(PARTITION BY)란?
• 인덱스와 쿼리 실행 순서
• 윈도우 함수는 왜 인덱스를 활용하지 못하는가?
• 실제 사례 분석
• 성능 최적화 방안
• 결론

소개

MySQL 8.0부터 본격적으로 지원되기 시작한 윈도우 함수(Window Functions)는 복잡한 분석 쿼리를 간결하게 작성할 수 있게 해주는 강력한 기능입니다. 하지만 대용량 데이터베이스에서 윈도우 함수 사용 시 성능 저하가 발생하는 경우가 많습니다. 이 글에서는 윈도우 함수와 인덱스의 관계를 탐구하고, 성능 최적화 방안을 알아보겠습니다.

윈도우 함수(PARTITION BY)란?

윈도우 함수는 행과 행 사이의 관계를 정의하여 계산을 수행하는 함수입니다. PARTITION BY 절을 사용한 윈도우 함수는 특정 컬럼을 기준으로 데이터를 논리적 파티션으로 나누어 처리합니다.

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY aptCode ORDER BY dealYear DESC, dealMonth DESC, dealDay DESC) AS recentDeal
FROM housedeal

이 예제에서 ROW_NUMBER()는 각 aptCode 별로 따로 번호를 매기며, 날짜 내림차순으로 정렬합니다.

인덱스와 쿼리 실행 순서

인덱스 기본 개념

인덱스는 데이터베이스 쿼리 성능을 향상시키는 데이터 구조입니다. 인덱스는 주로 다음 작업에서 성능 향상을 제공합니다:

• WHERE 절 조건 필터링
• JOIN 연산 최적화
• ORDER BY 정렬 작업 최적화
• GROUP BY 작업 최적화

MySQL 쿼리 실행 순서

MySQL에서 쿼리 실행 순서는 다음과 같습니다:

1. FROM + JOIN: 테이블 데이터 접근
2. WHERE: 조건에 맞는 행 필터링
3. GROUP BY: 그룹화
4. HAVING: 그룹 필터링
5. SELECT: 결과 컬럼 선택
6. 윈도우 함수 적용
7. ORDER BY: 결과 정렬
8. LIMIT/OFFSET: 결과 수 제한

중요: 윈도우 함수는 SELECT 이후, ORDER BY 이전에 적용됩니다.

윈도우 함수는 왜 인덱스를 활용하지 못하는가?

윈도우 함수가 인덱스를 활용하지 못하는 이유는 여러 가지가 있습니다:

1. 실행 순서 문제:

   • 윈도우 함수는 FROM, WHERE, GROUP BY 등의 단계 이후에 실행됩니다.
   • 이 시점에서 필요한 데이터는 이미 메모리에 로드되어 있으므로 인덱스는 더 이상 활용되지 않습니다.

2. 처리 방식 차이:

   • 인덱스는 디스크에서 데이터를 효율적으로 찾아오는 구조
   • 윈도우 함수는 이미 메모리에 로드된 데이터 집합에 대해 계산을 수행하는 작업

3. 임시 테이블 사용:

   • 윈도우 함수는 내부적으로 임시 테이블과 정렬 작업을 필요로 함
   • 이 과정에서 "Using temporary; Using filesort"가 발생

실제 사례 분석

다음 쿼리를 통해 실제 사례를 분석해 보겠습니다:

SELECT count(*)
FROM (SELECT *,
      ROW_NUMBER() OVER (PARTITION BY aptCode ORDER BY dealYear DESC, dealMonth DESC, dealDay DESC) AS recentDeal
      FROM housedeal
      NATURAL JOIN (SELECT * FROM houseinfo WHERE dongCode like '11%' ) AS houseinfo) AS deals
WHERE recentDeal = 1 ORDER BY dealYear desc, dealMonth desc, dealDay desc;

EXPLAIN 결과 분석:

1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 8 const 10 100.00
2 DERIVED houseinfo ALL PRIMARY 41820 11.11 Using where; Using temporary; Using filesort
2 DERIVED housedeal ref housedeal_aptCode_houseinfo_aptCode_fk_idx,idx_housedeal_apt_date idx_housedeal_apt_date 9 whereismyhome.houseinfo.aptCode 9310 0.00

문제점:

• houseinfo 테이블의 전체 스캔 (type=ALL)
• 임시 테이블 생성 및 외부 정렬 발생 (Using temporary; Using filesort)
• 이로 인한 성능 저하

인덱스 사용 제한:

• WHERE dongCode like '11%'에 인덱스가 사용될 수 있지만
• 윈도우 함수 ROW_NUMBER() OVER (...)와 WHERE recentDeal = 1에는 인덱스를 활용할 수 없음

성능 최적화 방안

1. JOIN과 GROUP BY를 활용한 대체 구현

SELECT COUNT(*)
FROM housedeal hd
INNER JOIN houseinfo hi ON hd.aptCode = hi.aptCode
INNER JOIN (
    SELECT h.aptCode, MAX(CONCAT(h.dealYear, LPAD(h.dealMonth, 2, '0'), LPAD(h.dealDay, 2, '0'))) as max_date
    FROM housedeal h
    INNER JOIN houseinfo i ON h.aptCode = i.aptCode
    WHERE i.dongCode LIKE '11%'
    GROUP BY h.aptCode
) latest ON hd.aptCode = latest.aptCode 
    AND CONCAT(hd.dealYear, LPAD(hd.dealMonth, 2, '0'), LPAD(hd.dealDay, 2, '0')) = latest.max_date
WHERE hi.dongCode LIKE '11%';

장점:

• GROUP BY는 인덱스를 더 효율적으로 활용 가능
• 임시 테이블 사용이 줄어들 수 있음

2. 임시 테이블 접근법

-- 1. 서울 지역 아파트 코드 추출
CREATE TEMPORARY TABLE seoul_apts AS
SELECT aptCode FROM houseinfo WHERE dongCode LIKE '11%';

-- 2. 인덱스 생성
ALTER TABLE seoul_apts ADD INDEX (aptCode);

-- 3. 각 아파트별 최신 거래 날짜 계산
CREATE TEMPORARY TABLE apt_max_dates AS
SELECT 
    hd.aptCode,
    MAX(CONCAT(hd.dealYear, LPAD(hd.dealMonth, 2, '0'), LPAD(hd.dealDay, 2, '0'))) as max_date
FROM 
    housedeal hd
    INNER JOIN seoul_apts sa ON hd.aptCode = sa.aptCode
GROUP BY 
    hd.aptCode;

-- 4. 인덱스 생성
ALTER TABLE apt_max_dates ADD INDEX (aptCode, max_date);

-- 5. 각 아파트별 최신 거래 중 하나만 선택
CREATE TEMPORARY TABLE latest_deals AS
SELECT 
    hd.aptCode,
    MIN(hd.no) AS deal_id
FROM 
    housedeal hd
    INNER JOIN apt_max_dates amd ON hd.aptCode = amd.aptCode
    AND CONCAT(hd.dealYear, LPAD(hd.dealMonth, 2, '0'), LPAD(hd.dealDay, 2, '0')) = amd.max_date
GROUP BY 
    hd.aptCode;

-- 6. 결과 카운트
SELECT COUNT(*) FROM latest_deals;

장점:

• 각 단계마다 명시적 인덱스 생성으로 성능 향상
• 복잡한 작업을 단계별로 분리하여 처리
• 대량 데이터에서 메모리 효율성 증가

3. 효율적인 인덱스 설계

-- 인덱스 최적화
CREATE INDEX idx_houseinfo_dongcode ON houseinfo(dongCode);
CREATE INDEX idx_housedeal_apt_date ON housedeal(aptCode, dealYear DESC, dealMonth DESC, dealDay DESC);

결론

1. 윈도우 함수는 인덱스를 직접 활용하지 않습니다

   • 쿼리 실행 순서상 윈도우 함수는 이미 데이터가 메모리에 로드된 후 실행됨
   • 대용량 데이터에서는 성능 저하가 발생할 수 있음

2. 대안 접근법

   • JOIN과 GROUP BY를 활용한 구현
   • 임시 테이블을 활용한 단계별 처리
   • 적절한 인덱스 설계

3. 선택 기준

   • 데이터 양: 소량 데이터에서는 윈도우 함수가 코드 가독성 측면에서 유리
   • 성능 중요도: 성능이 중요한 대용량 시스템에서는 JOIN과 GROUP BY 또는 임시 테이블 접근법이 유리
   • 유지보수성: 윈도우 함수는 코드가 간결하나, 성능 최적화가 제한적

윈도우 함수는 강력한 기능이지만 인덱스 활용에 제한이 있기 때문에, 상황에 맞는 최적화 전략을 선택하는 것이 중요합니다.