대용량 데이터 환경에서 실행계획과 조회 구조를 분석하고, 기존 Index 활용·집계 제거·존재 여부 기반 필터링으로 개선한 튜닝 사례.
Performance Summary
| Case | Data Scale | Before | After |
|---|---|---|---|
| 복합 Index 활용 개선 | 약 3,000만 건 | 최대 약 50초 | 약 2초 |
| 알림 조회 JOIN·집계·정렬 최적화 | 약 1,500만 + 2,000만 건 | 10초 Timeout | 평균 약 1초 |
| 이벤트 제외·포함 조건 개선 | Event 약 450만 + Server 약 24만 건 | 10초 이상 Timeout | 약 1초 |
각 실행 시간은 해당 Query에서 확인한 값. SQL과 Table·Column 이름 및 일부 조건은 공개를 위해 일반화.
Overview
문제 발견
- 모니터링에서 Batch 수행시간 증가 확인
- 웹 히스토리 조회 및 이벤트 검색 중 Timeout 발생
주요 분석 대상
- MySQL 실행계획과 Index Access 방식
- 복합 Index의 Column 구성 및 검색 조건
GROUP BY집계와ORDER BY정렬, 대용량 Table Access 비용- 이벤트 조회의 제외 서비스 필터와 모니터링 그룹 서버 포함 조건
개선 방향
- 신규 Index 추가 전 기존 Index 활용도 검토
- 조회 시점의 반복적인 JOIN / 집계 제거
- 필수 검색조건에 사용한 Column과 정렬 Column을 맞춰 Index 순서 활용
- 제외 서비스와 모니터링 그룹 서버의 존재 여부를
NOT EXISTS·EXISTS로 판별
Tuning Cases
탭을 선택하면 각 Query의 문제, 분석 과정, 변경 내용과 결과를 볼 수 있다.
Case 1. 복합 Index 활용 개선
Data Scale
- IP 정보 약 3,000만 건
- Batch에서 사용하는 IP 정보 수정 Query
Problem
- 모니터링에서 Batch 수행시간 증가 확인, Query 실행 시간 최대 약 50초
- 기존 복합 Index:
INDEX (a, b, c) - 검색 조건에서 중간 Column
b누락 - 당시 실행계획에서 Index Skip Scan 사용으로 불필요한 Index 탐색 발생
WHERE a = ?
AND c = ?
Analysis
- 기존 Index
(a, b, c)의 Column 순서와 Query 조건 비교 b의 Cardinality가 2로, 두 값을IN (?, ?)조건에 포함해도 결과의 의미가 달라지지 않음 확인- 신규 Index보다 기존 Index의 연속된 선두 Column 활용 우선 검토
Solution
- 검색 조건에
b IN (?, ?)추가해 복합 Index의 연속된 선두 Column 활용 - 실행계획의 Index Access 방식 Index Skip Scan → Index Range Scan으로 변경
-- Before
WHERE a = ?
AND c = ?;
-- After
WHERE a = ?
AND b IN (?, ?)
AND c = ?;
Result
- 신규 Index 없이 기존 복합 Index 활용 개선
- 실행 시간 최대 약 50초 → 약 2초
Case 2. 알림 조회의 JOIN·집계·정렬 최적화
Data Scale
- 알림 히스토리 약 1,500만 건
- 알림 재시도 히스토리 약 2,000만 건
Problem
- 히스토리 조회 시 10초로 설정된 웹 요청 Timeout 발생
- 최종 전송 성공 여부 판단을 위해
notification_history와notification_retry_history를 JOIN한 뒤GROUP BY수행 - 재시도 데이터 중 한 건이라도 성공하면 최종 결과를 성공으로 판단
- 필수 문자열 검색조건에 적절한 Index 부재
- JOIN·집계를 제거한 뒤에도 검색 결과가 없거나 10건 이하인 조건에서 Timeout 발생
Before
테이블 이름과 핵심 조건만 남긴 예시다. 연결 키와 상태 Column 이름은 공개용으로 단순화했다.
SELECT nh.notification_no,
MAX(CASE WHEN nrh.status = 'SUCCESS' THEN 1 ELSE 0 END) AS is_success
FROM notification_history nh
LEFT JOIN notification_retry_history nrh
ON nrh.notification_no = nh.notification_no
WHERE nh.event_time >= :from_time
AND nh.event_time < :to_time
-- 그 외 필수 검색조건 생략
GROUP BY nh.notification_no
ORDER BY nh.notification_no DESC
LIMIT :page_size;
Step 1. JOIN과 GROUP BY 제거
- 조회할 때마다 재시도 이력을 JOIN·집계하지 않도록, 최종 상태를
notification_history.status에SUCCESS·FAIL·RETRY로 저장 - 조회 Query에서
notification_retry_historyJOIN과GROUP BY를 제거하고 저장된 상태를 직접 읽음 - 필수·빈번한 검색조건과 Cardinality를 고려해 Index를 재구성하여 Table Access 감소
SELECT nh.notification_no,
(nh.status = 'SUCCESS') AS is_success
FROM notification_history nh
WHERE nh.event_time >= :from_time
AND nh.event_time < :to_time
-- 그 외 필수 검색조건 생략
ORDER BY nh.notification_no DESC
LIMIT :page_size;
Step 2. Index 순서로 정렬
조회에 사용한 기존 Index는 event_time이 선두 Column. 아래는 선두 Column만 표시한 개념 예시.
기존 Index: (event_time, ...)
기존 정렬: ORDER BY notification_no DESC
변경 정렬: ORDER BY event_time DESC
- 1단계 이후에도
ORDER BY notification_no DESC는event_time선두 Index의 순서와 맞지 않아 Index 순서로 결과를 읽으며 정렬을 생략하거나,LIMIT건수를 채운 시점에 조회를 조기 종료(early stop)할 수 없었음 - 검색 결과가 없거나
LIMIT건수 미만인 조건에서는 필요한 행 수를 채우지 못해 조회 범위 전체를 확인해야 했고 Timeout이 계속 발생 event_time은 필수WHERE조건에 사용하며, 이 Query에서event_time DESC도 필요한 최신순을 표현함. 정렬의 선두 Column을notification_no에서event_time으로 바꿔 Index 순서로 결과를 읽도록 수정
SELECT nh.notification_no,
(nh.status = 'SUCCESS') AS is_success
FROM notification_history nh
WHERE nh.event_time >= :from_time
AND nh.event_time < :to_time
-- 그 외 필수 검색조건 생략
ORDER BY nh.event_time DESC
LIMIT :page_size;
Result
- 반복 계산을 Write 시점으로 이동해 조회 시점의 대용량 JOIN·집계를 제거하고, Index 순서를 활용해 별도 정렬 작업 생략
- 10초 Timeout → 평균 약 1초. 1단계 후에도 남았던 소수·무결과 조회의 Timeout을 2단계 정렬 조건 변경으로 개선
Case 3. 이벤트 조회의 제외·포함 조건 최적화
Data Scale
- Event 약 450만 건
- Server 약 24만 건
Problem
- 제외 대상으로 등록된 서비스의 이벤트는 검색 결과에서 빼고, 모니터링 그룹에 속한 서버에서 발생한 이벤트는 포함해야 함
- 이벤트 조회 Query 실행 시간이 10초 이상 걸려 웹 요청 Timeout 발생
- Event 한 건은 서버 한 대를 가리키지만, 서버 한 대는 1~50개 서비스에 속할 수 있음.
event.target_id = server.server_id로 JOIN한 뒤 서비스 정보를 결합하면 이벤트 한 건에서 여러 행이 만들어질 수 있음 - 제외 서비스 판별은
server와exclusive_service를LEFT JOIN한 뒤IS NULL로 걸렀고, 모니터링 그룹 서버 판별은IN서브쿼리로 처리
Before
event.target_id와 server.server_id의 연결은 실제 조회 구조를 반영했다. 공개할 수 없는 서비스 매칭 조건과 모니터링 그룹 소속 조건은 생략한 의사 SQL이다.
FROM event e
LEFT JOIN server s
ON s.server_id = e.target_id
LEFT JOIN exclusive_service xs
ON <server와 exclusive_service의 매칭 조건>
WHERE xs.<식별자> IS NULL
AND (
<다른 이벤트 포함 조건>
OR e.target_id IN (
SELECT ms.server_id
FROM server ms
WHERE <모니터링 그룹 소속 조건>
)
)
Analysis
- 한 이벤트 → 한 서버 → 서비스 1~50개로 이어지는 관계에서 서비스 정보를 JOIN하면, 이벤트 한 건에 대한 중간 행이 최대 50개까지 늘어날 수 있음. 필요한 것은 서비스 행이 아니라 해당 서버에 제외 서비스가 존재하는지 여부
LEFT JOIN ... IS NULL로 표현한 제외 조건을NOT EXISTS로 바꿔,event.target_id에 연결된 서버가 제외 서비스에 속하는지 이벤트 한 건 단위로 판별- 모니터링 그룹 조건도 서버 행을 결과에 붙이려는 것이 아니라, 이벤트가 발생한 서버가 그룹에 속하는지 확인하는 조건이다.
IN을EXISTS로 바꿔server.server_id = event.target_id로 연결된 서버에서 매칭되는 행의 존재 여부만 확인
After
FROM event e
WHERE NOT EXISTS (
SELECT 1
FROM server s
JOIN exclusive_service xs
ON <server와 exclusive_service의 매칭 조건>
WHERE s.server_id = e.target_id
)
AND (
<다른 이벤트 포함 조건>
OR EXISTS (
SELECT 1
FROM server ms
WHERE ms.server_id = e.target_id
AND <모니터링 그룹 소속 조건>
)
)
Result
- 서버의 서비스 소속 수와 관계없이, 제외 서비스가 연결된 서버의 이벤트는
NOT EXISTS로 걸러 내고 모니터링 그룹 서버의 이벤트는EXISTS로 포함 - 한 서버에 서비스가 1~50개 연결되어 있어도 존재 여부를 확인하는 과정에서 이벤트 행이 서비스 수만큼 늘어나지 않도록 변경
- 이 쿼리의 실행 시간은 10초 이상 Timeout → 약 1초로 개선
- 실행 시간은 전체 쿼리의 전후 결과이며, 두 조건 변경 각각의 기여도는 별도로 측정하지 않음
Technical Decisions
- 기존 Index 우선: 복합 Index의 Column 순서와 검색조건을 비교하고 업무조건 변경 가능성 확인
- Read 비용 제거: 반복되는 JOIN /
GROUP BY를 저장 시점의 결과 계산으로 대체 - 검색과 정렬의 Index 활용: 필수 조건인
event_time으로 최신순 정렬해 별도 정렬 작업 제거 - 제외·포함 조건을 존재 여부로 표현: 제외 서비스는
NOT EXISTS, 모니터링 그룹 서버는 이벤트와 연결된EXISTS로 확인
Lessons Learned
- 실행계획의 Index Access 방식과 JOIN·집계·정렬 비용을 함께 분석
- SQL 문법뿐 아니라 데이터 모델과 Read / Write 계산 시점을 포함해 최적화 범위 판단
- 제외할 대상과 포함할 대상을 구분하고, 필요한 존재 여부를 Query에 직접 표현