[Index-7][Optimization] 실무 적용(⭐) - 복합 인덱스/집계 테이블/반정규화

1. 들어가기 앞서

지금까지 학습했던 Index를 이용하여 성능 최적화 경험을 실습을 통해 알아보자. 우선 우리는 일반적인 E-Commerce를 프로젝트로 가정하고 진행한다. 즉, 아래와 같이 전형적인 Member, Product, Order, OrderItem(중간 테이블)의 구조로 진행된다.

2. 코드 분석 (중요 엔드포인트)

OrderController에는 아래 두 개의 중요한 검색 엔드포인트가 있다.

2.1. 복합 조건 주문 검색 API (/complex-search)

호출 예시) GET /api/chapter3/orders/complex-search?startDate=2024-01-01T00:00:00&status=COMPLETED&minAmount=100000&page=0&size=20

기능: 여러 조건을 조합한 주문 검색
파라미터:
- startDate: 특정 날짜 이후 주문
- status: 주문 상태 (COMPLETED, PENDING 등)
- minAmount: 최소 주문 금액
- pageable: 페이징 정보 (page, size)
사용 사례:
- "2024년 1월 이후의 완료된 10만원 이상 주문 검색"
- "특정 기간의 취소된 고액 주문 조회"

2.2. 회원별 주문 통계 API (/stats)

호출 예시) GET /api/chapter3/orders/stats?minAmount=50000&page=0&size=20

기능: 회원별 주문 통계 조회
파라미터:
- minAmount: 최소 주문 금액 기준 (선택적)
- pageable: 페이징 정보
통계 정보:
- 총 주문 건수
- 총 주문 금액
- 평균 주문 금액
- 마지막 주문일
사용 사례:
- "5만원 이상 구매 회원 통계"
- "VIP 고객 구매 패턴 분석"

3. 프로젝트 세팅

당장 위의 OrderController의 내용만을 보고 어떤 문제점이 있는지 파악하는건 쉽지 않다. 따라서 직접 프로젝트 환경을 구축하여 실행시켜봄으로써 쿼리 플랜도 알아보고 여러 인덱스를 생성해서 확인해보자.

3.1. Docker Compose로 MySQL 실행

docker compose up db

3.2. MySQL Workbench로 DB 접속

3.3. 프로젝트 실행 (Boot Run)

3.4. Mock 데이터 삽입

3.5. API 호출 테스트

3.6. 결과 분석

아까 언급했던 두 API 의 경우, 좀 문제가 있다는 게 느껴진다.대충 실행해봐도 다른 API 에 비해서 매우 오랜 시간이 걸린다는 것을 볼 수 있다.

복합 조건 주문 검색 API (/complex-search)
- 예: GET /api/chapter3/orders/complex-search?startDate=2024-01-01T00:00:00&status=COMPLETED&minAmount=100000&page=0&size=20
- 실행 시 300ms 안팎 (혹은 더) 걸리는 경우 발생
- 특정 환경에서는 1초 이상 소요될 수도 있음
회원별 주문 통계 API (/stats)
- 예: GET /api/chapter3/orders/stats?minAmount=50000&page=0&size=20
- 2~3초 정도 걸려, 사용자 체감에 문제가 될 가능성 큼
그 외 API 들 → 49ms, 160ms

API가 2초 이상 걸리는 것은 사용자 체감에 심각한 문제

4. 문제 파악 및 해결(1): 복합 조건 주문 검색 API

그러면 이 쿼리가 뭐가 문제인지 한 번 분석해보자.

    select
        o1_0.id,
        o1_0.created_at,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.order_number,
        o1_0.status,
        o1_0.total_amount,
        o1_0.updated_at 
    from
        ch3_orders o1_0 
    where
        o1_0.order_date>=? 
        and o1_0.status=? 
        and o1_0.total_amount>=? 
    order by
        o1_0.order_date desc 
    limit
        ?,?

해당 쿼리의 쿼리 플랜을 실행해보면 다음과 같이 나온다.

4.1. 주요 문제점

type: ALL - 50만건의 전체 테이블 스캔 발생
possible_keys: NULL - 조건절에 사용할 인덱스 없음
Using filesort - ORDER BY를 위한 추가 정렬 작업 필요
filtered: 2.78 - 전체 데이터 중 2.78%만 실제 필요한 데이터

4.2. 문제 원인

order_date, status, total_amount 조건에 맞는 복합 인덱스 부재
order_date DESC 정렬을 위한 인덱스 부재하기 때문에 전체 데이터를 다 보고 메모리 안에서 정렬을 했어야했기 때문에 매우 느렸다는 것을 의미(저 데이터를 뽑아올 때마다 정렬을 해야 하기 때문에)

4.3. 문제 해결 (복합 인덱스 추가)

실행계획을 개선하기 위해 다음과 같이 복합 인덱스를 추가하고 쿼리를 최적화해야 한다. 즉, 복합 인덱스를 추가하여 검색 조건과 정렬 순서를 모두 커버하는 인덱스를 생성한다.

ALTER TABLE ch3_orders ADD INDEX idx_order_date_status_amount (order_date DESC, status, total_amount);

1) type: ALL → range
2) 전체 테이블 스캔에서 인덱스 범위 스캔으로 개선
3) order_date 범위 조건을 인덱스로 처리
4) Using filesort 제거
5) ORDER BY가 인덱스를 통해 처리됨
6) 추가적인 정렬 작업 불필요

5. 문제 파악 및 해결(2): 회원별 주문 통계 API

실시간으로 회원별 주문 통계를 조회하는 쿼리는 다음과 같다. 이 API를 호출할 경우 약 2~3초의 응답 지연이 발생한다.

   SELECT
        m.email as memberEmail,
        COUNT(*) as totalOrders,
        SUM(o.total_amount) as totalAmount,
        AVG(o.total_amount) as averageAmount 
    FROM
        ch3_orders o 
    JOIN
        ch2_members m 
            ON o.member_id = m.id 
    GROUP BY
        m.email 
    HAVING
        SUM(o.total_amount) >= 5000
    limit
        100

5.1. 테이블 m(members) 및 o(orders)의 접근 방식

m(members): type: index. 이메일 유니크 인덱스를 사용하여 인덱스 풀 스캔을 수행한다. Using index가 표시되므로 커버링 인덱스로 작동하여 데이터 페이지 접근은 피하고 있으나, 약 99만 건의 인덱스 로우를 모두 훑어야 한다.
o(orders): type: ref. 조인 시 외래키 인덱스를 사용하여 m.id 값과 매칭되는 주문을 찾는다.

5.2. Nested Loop Join의 메커니즘

MySQL에서 두 테이블을 조인할 때 사용하는 기본 방식은 중첩 루프 조인(Nested Loop Join)이다. 이는 드라이빙 테이블(Outer)에서 행을 하나씩 가져와 드리븐 테이블(Inner)의 인덱스를 조회하는 방식이다.

FOR EACH row in members (외부 테이블)    // 외부 루프
    FOR EACH matching row in orders     // 내부 루프
        IF join-condition matches THEN 
            RETURN combined row

members 테이블 (외부)
┌─────┬───────┐
│ id  │ email │
├─────┼───────┤      orders 테이블 (내부) (ref type)
│ 1   │ a@a.com   ──▶ [검색] member_id = 1
│ 2   │ b@b.com   ──▶ [검색] member_id = 2
│ 3   │ c@c.com   ──▶ [검색] member_id = 3
└─────┴───────┘

현재 쿼리에서 m(members)이 드라이빙 테이블이 된 이유는 통계적으로 데이터 양이 o(orders)보다 적고 조인 조건에 인덱스가 존재하기 때문이다. 하지만 문제는 루프의 횟수이다. 드라이빙 테이블인 회원 수가 99만 명이라면, 비록 인덱스 조회(ref)라 할지라도 주문 테이블 조회를 99만 번 반복해야 한다. 개별 조회는 빠를지 모르나 누적된 반복 비용이 2~3초라는 지연을 발생시키는 것이다.

5.3. 문제 해결: 집계 테이블(Aggregation Table) 도입

실시간으로 대규모 조인과 집계를 수행하는 방식은 데이터가 늘어날수록 한계에 부딪힌다. 이를 해결하기 위해 사전 계산(Pre-calculation) 전략을 도입한다.

5.3.1. 요리 비유를 통한 전략 이해

이는 '실시간 주문 방식'과 '재료 미리 손질 방식'의 차이와 같다. 모든 음식을 주문 즉시 조리하면 손님이 몰릴 때 대응이 불가능하다. 반면, 인기 메뉴를 미리 만들어 둔 '도시락' 형태라면 손님에게 즉시 제공할 수 있다. 통계 역시 3분 전의 데이터일지라도 이미 계산된 결과를 보여주는 것이 성능 면에서 압도적으로 유리하다.

5.3.2. 집계 테이블 구현 및 배치 작업

회원별 통계 수치를 미리 저장해둘 ch3_order_stats 테이블을 생성한다. 이후 스프링의 스케줄링 기능을 활용하여 3분마다 한 번씩 아래의 집계 쿼리를 실행하고 결과를 통계 테이블에 갱신한다.

INSERT INTO ch3_order_stats (member_id, email, order_count, total_amount, avg_amount, last_order_date, updated_at)
SELECT m.id, m.email, COUNT(o.id), SUM(o.total_amount), AVG(o.total_amount), MAX(o.order_date), NOW()
FROM ch2_members m
JOIN ch3_orders o ON m.id = o.member_id
GROUP BY m.id, m.email
ON DUPLICATE KEY UPDATE 
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    updated_at = NOW();

5.3.3. 최종 튜닝 및 성능 개선 결과

이제 API는 50만 건의 주문 테이블을 뒤지는 대신, 미리 계산된 ch3_order_stats 테이블만 단순 조회한다. 여기에 검색 조건 컬럼인 total_amount에 단일 인덱스를 추가하여 효율을 극대화한다.

ALTER TABLE ch3_order_stats ADD INDEX idx_total_amount (total_amount);

개선 전: 실시간 조인 및 집계 연산으로 인해 2.3s 소요
개선 후: 사전 계산된 집계 테이블 조회로 91ms 소요 (약 25배 향상)

6. 추가 문제 파악: 주문 아이템 개수 조회와 N+1 문제

복합 인덱스 적용으로 검색 자체는 빨라졌으나, 상세 로그를 분석하면 여전히 API 한 번 호출에 수많은 쿼리가 실행되는 것을 확인할 수 있다. 이는 각 주문에 포함된 '주문 아이템(order_items)의 개수'를 가져오는 과정에서 발생하는 문제이다.

6.1. 문제의 원인 분석

하이버네이트 로그를 통해 실행되는 쿼리를 살펴보면, 각 주문마다 아래와 같은 조회가 반복적으로 일어난다.

SELECT o1_0.order_id, o1_0.id, o1_0.price, o1_0.quantity ... 
FROM ch3_order_items o1_0 
WHERE o1_0.order_id = ?;

이는 DTO 생성 로직에서 orderItems.size()를 호출하기 때문이다. 단순히 아이템의 '개수'만 필요함에도 불구하고, JPA는 연관된 모든 아이템 레코드를 메모리로 불러온 뒤 길이를 계산한다. 고작 숫자 하나를 표시하기 위해 불필요한 대량의 데이터 로드가 발생하는 것이다.

6.2. 정규화의 한계

데이터베이스 설계의 기본인 정규화(Normalization)는 데이터의 중복을 제거하고 정합성을 보장하는 데 목적이 있다. 하지만 정규화된 구조에서는 '아이템 수', '댓글 수', '리뷰 수'와 같은 집계 데이터를 얻기 위해 매번 연관 테이블을 조인하거나 추가 쿼리를 실행해야 한다. 즉, 데이터의 정합성을 위해 조회 속도를 희생하는 구조적 단점이 존재한다.

6.3. 문제 해결: 반정규화(Denormalization) 전략

속도가 최우선인 조회 화면에서는 정합성을 일부 양보하더라도 성능을 극대화할 필요가 있다. 이를 위해 쪼개진 데이터를 다시 합치는 반정규화를 진행한다.

6.3.1. 개념 및 적용

주문 아이템의 개수를 매번 계산하는 대신, 주문(Orders) 테이블 자체에 total_items라는 컬럼을 추가하여 값을 직접 들고 있게 한다. 이렇게 하면 주문 테이블만 조회해도 아이템 개수를 즉시 알 수 있다.

반정규화의 장단점

장점: 별도의 조인이나 추가 쿼리 없이 즉시 데이터 확인 가능. 쿼리 수와 전체 응답 시간의 획기적 단축.
단점: 데이터 중복 발생. 아이템이 추가/삭제될 때마다 total_items 값을 갱신해야 하는 관리 로직(Trigger 혹은 Application 로직)이 필요함.

6.3.2. 데이터 마이그레이션(Migration) 수행

기존의 정규화된 데이터를 반정규화된 새로운 테이블(ch3_improved_orders)로 옮기는 작업이 필요하다. 아래 쿼리는 기존 주문 정보와 아이템 개수를 한꺼번에 집계하여 이관하는 과정을 보여준다.

INSERT INTO ch3_improved_orders (id, member_id, order_number, status, order_date, total_amount, total_items, created_at, updated_at)
SELECT 
    o.id, o.member_id, o.order_number, o.status, o.order_date, o.total_amount,
    (SELECT COUNT(*) FROM ch3_order_items oi WHERE oi.order_id = o.id) AS total_items,
    o.created_at, o.updated_at
FROM ch3_orders o;

이후 API에서는 total_items 컬럼만 참조하게 되며, 이 과정에서 수많은 order_items 조회 쿼리가 사라지는 효과를 거둘 수 있다.

6.4. 최종 도메인 구조 및 최적화 결과 요약 (⭐)

세 가지 핵심 최적화 기법을 통해 최종적으로 구축된 시스템의 형상은 다음과 같다.

복합 인덱스: 자주 사용되는 검색 조건과 정렬 순서를 커버하여 풀 스캔과 정렬 부하 제거.
집계 테이블(Batch): 실시간 GROUP BY 연산을 배치를 통한 사전 계산으로 대체.
반정규화: N+1 문제를 방지하기 위해 필수 집계 데이터를 컬럼으로 관리.

성능 개선 성과 비교

지표	개선 전	개선 후	비고
주문 검색 응답 속도	2.5s 이상	0.2s 내외	복합 인덱스 효과
통계 조회 응답 속도	2.0s 이상	0.1s 이하	집계 테이블 효과
N+1 발생 쿼리 수	주문당 +1회	0회	반정규화 효과
DB CPU 점유율	평균 70%	50% 미만	부하 분산 성공

[Index-8][Optimization] FullText Index와 n-gram 파서 ⭐

1. 문제 발견: LIKE 검색의 치명적 성능 한계1.1. 문제 인식: 4초짜리 검색 대기 시간100만 개 이상의 상품 데이터가 누적된 환경에서 상품명 검색 기능에 심각한 병목 현상이 발생했다. 테스트 결과

receiver40.tistory.com

'Database > MySQL' 카테고리의 다른 글

[Lock-2][Optimization] 다중 트랜잭션 환경과 정합성 문제 (0)	2025.12.24
[Lock-1][Optimization] 트랜잭션의 정의와 ACID 원칙 (0)	2025.12.24
[Index-6][Optimization] 실전 분석 (2) - 실행 계획 타입(Type) (0)	2025.12.24
[Index-5][Optimization] 실전 분석 (1) - 실행 계획의 이해 (0)	2025.12.23
[Index-4][Optimization] 심화 이론 (2) - 물리적 저장 구조 (0)	2025.12.23

1. 들어가기 앞서

2. 코드 분석 (중요 엔드포인트)

2.1. 복합 조건 주문 검색 API (/complex-search)

2.2. 회원별 주문 통계 API (/stats)

3. 프로젝트 세팅

3.1. Docker Compose로 MySQL 실행

3.2. MySQL Workbench로 DB 접속

3.3. 프로젝트 실행 (Boot Run)

3.4. Mock 데이터 삽입

3.5. API 호출 테스트

3.6. 결과 분석

4. 문제 파악 및 해결(1): 복합 조건 주문 검색 API

4.1. 주요 문제점

4.2. 문제 원인

4.3. 문제 해결 (복합 인덱스 추가)

5. 문제 파악 및 해결(2): 회원별 주문 통계 API

5.1. 테이블 m(members) 및 o(orders)의 접근 방식

5.2. Nested Loop Join의 메커니즘

5.3. 문제 해결: 집계 테이블(Aggregation Table) 도입

5.3.1. 요리 비유를 통한 전략 이해

5.3.2. 집계 테이블 구현 및 배치 작업

5.3.3. 최종 튜닝 및 성능 개선 결과

6. 추가 문제 파악: 주문 아이템 개수 조회와 N+1 문제

6.1. 문제의 원인 분석

6.2. 정규화의 한계

6.3. 문제 해결: 반정규화(Denormalization) 전략

6.3.1. 개념 및 적용

6.3.2. 데이터 마이그레이션(Migration) 수행

6.4. 최종 도메인 구조 및 최적화 결과 요약 (⭐)

성능 개선 성과 비교

'Database > MySQL' 카테고리의 다른 글

티스토리툴바