Miscellaneous
10만 건 데이터 랭킹 조회 성능 1200배 개선기 : 커버링 인덱스와 지연 조인 본문
0. 인트로
교내 학술제 프로젝트로 산책 기반 게이미피케이션 봉사 서비스를 기획 및 개발했습니다. 감사하게도 해당 학술제에서 대상을 수상했지만, 시간에 쫓겨 개발한 탓에 코드 품질과 성능에 대한 아쉬움이 남았습니다.
특히 서비스의 핵심 기능인 '포인트 기반 랭킹 시스템'은 데이터가 쌓일수록 성능 저하가 우려되는 부분이었습니다. 이 글은 가상으로 10만 건의 대용량 데이터 환경을 구축하고, 랭킹 조회 API의 병목을 진단하여 응답속도를 1200배 이상 개선한 트러블 슈팅 기록입니다.
1. 기존 환경 설명
기존의 rankings 테이블의 구조와 랭킹 조회 쿼리는 다음과 같습니다.
CREATE TABLE rankings (
rank_period int NOT NULL,
id bigint NOT NULL AUTO_INCREMENT,
point bigint NOT NULL,
updated_at datetime(6) NOT NULL,
user_id bigint DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT FKcup4ei1jmensgunlbncpb5rnv FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;SELECT
u.nickname,
u.profile_image_url AS profileImageUrl,
r.point,
RANK() OVER (ORDER BY r.point DESC) AS ranking
FROM rankings r
JOIN users u ON u.id = r.user_id
WHERE r.rank_period = :period
AND u.is_deleted = false
ORDER BY ranking ASC, r.updated_at ASC
LIMIT 10;
아래는 조회 쿼리에 대한 EXPLAIN 결과입니다.
이미지에서 보실 수 있듯이, type: ALL로 쿼리를 실행할 때마다 Full Scan이 발생하고 있습니다.
2. 테스트 환경 및 시나리오
테스트 환경은 아래와 같습니다.
| 구분 | 환경 | 상세 스펙 |
| Server | AWS EC2 (Spring Boot 3.2) | t3.micro (2vCPU, 1GB RAM) |
| DB | AWS RDS (MySQL 8.4) | db.t4g.micro (2vCPU, 1GB RAM) |
| OS | Ubuntu 24.04 LTS | - |
| Test Tool | k6 (Docker) | v1.5.0 |
테스트는 학술제 당시 실제 운영 서버와 동일한 환경에서 진행했습니다.
k6 테스트 시나리오는 아래와 같습니다.
import http from 'k6/http';
import { check, sleep } from 'k6';
export const options = {
stages: [
{ duration: '30s', target: 50 },
{ duration: '1m', target: 50 },
{ duration: '30s', target: 100 },
{ duration: '1m', target: 100 },
{ duration: '10s', target: 0 },
],
};
const TOKEN = "ACCESS_TOKEN";
export default function () {
const param = {
headers: {
Authorization: `Bearer ${TOKEN}`,
},
};
const res = http.get("랭킹 조회 엔드포인트", param);
check(res, {
'status is 200': (r) => r.status === 200,
});
sleep(Math.random()*0.4+0.8);
}단순 조회만 진행하도록 시나리오를 구성했습니다.
3. 가정 및 문제 상황
총 가입자 수를 10만명, DAU는 전체 가입자의 20%인 2만명이라고 가정했습니다.
사용자가 하루에 1회 산책을 진행한다고 했을 때, 하루에 2만 건의 쓰기 부하가 발생합니다. 즉 0.23TPS, 피크 타임에 트래픽이 10배 몰린다고 가정하면 2.3TPS로 미미한 수준입니다.
반면 읽기 조회는 사용자 당 하루 평균 5회 가정 시 일일 10만 건의 조회 요청이 발생합니다. 현재 rankings 테이블에는 인덱스가 없으므로, 매 요청마다 10만 건의 풀 스캔이 발생하고, 이로 인한 성능 저하가 발생할 수 있습니다.
해당 환경을 바탕으로 실제로 부하 테스트를 진행한 결과, 평균 응답 속도는 15.74s로 나타났으며, 824번의 요청 중 단 1번의 요청만 실패했습니다. (성공률 99.87%)
4. 인덱스 추가
가장 접근하기 쉽고 확실한 해결책으로 인덱스 도입을 시도했습니다.
rankings 테이블에 idx_ranking_period_point (rank_period, point desc)로 인덱스를 추가했습니다.
물론 인덱스로 인한 쓰기 지연이 발생하겠지만, 앞선 가정에 따라 Read : Write 비율이 5 : 1 이상의 조회 비율을 고려할 때 이는 충분히 감수할만한 트레이드 오프라고 판단했습니다.
하지만 테스트 결과는 예상과 정반대였습니다.
인덱스 적용 후 조회 성능은 오히려 크게 하락했습니다. 평균 응답 시간이 15.74s에서 23.58s로 증가했고, API 성공률 또한 92.8%로 하락했습니다. 오차를 감안하더라도, 성능이 개선되기는 커녕 오히려 악화되었습니다.
5. 성능 저하 원인 파악
정말 인덱스가 원인이었을까?
우선 성능 하락의 원인이 정말 인덱스 때문인지 검증이 필요했습니다. 이를 위해 IGNORE INDEX 힌트를 사용하여 강제로 인덱스를 타지 않도록 설정하고 비교해 보았습니다.
기존 쿼리는 의도했던 대로 인덱스를 이용하는 것을 확인했고, IGNORE INDEX를 넣은 쿼리는 인덱스 추가 전과 실행 계획이 동일하다는 것을 확인했습니다. 이를 통해 성능 저하의 원인이 인덱스임을 확인했습니다.
인덱스를 탔는데 왜 더 느려졌을까?
조회 성능을 높이려고 넣은 인덱스가 왜 독이 되었을까요? 이 현상을 이해하기 위해서는 디스크 I/O 방식의 차이를 짚고 넘어가야 합니다.
- Full Table Scan (Sequential I/O)
: 디스크 헤드가 한번 자리를 잡으면, 인접한 데이터 블록들을 연속으로 쭉 읽어들입니다. 한 번의 I/O 요청으로 여러 블록을 가져오는 Multi-Block Read가 가능하여 대량 데이터 처리에 효율적입니다.
- Index Range Scan (Random I/O)
: 인덱스는 논리적으로 정렬되어 있지만, 실제 데이터는 디스크 곳곳에 흩어져 있습니다. 디스크는 블록 단위로 데이터를 읽는데, 인덱스를 통해 레코드를 한 건씩 찾을 때마다 매번 새로운 블록을 찾아 움직여야 하는 랜덤 I/O가 발생합니다.
결국 제 상황은 한 번의 무거운 순차 I/O보다, 10만 번의 가벼운 랜덤 I/O가 물리적으로 훨씬 더 많은 시간을 소비한 케이스였습니다.
옵티마이저의 오판
일반적으로 MYSQL 옵티마이저는 읽어야 할 데이터가 전체의 20~25%(손익분기점)를 넘으면 인덱스보다 풀스캔이 효율적이라 판단합니다. 제 테스트 환경은 10만 건 중 조건에 맞는 데이터가 10만 건(100%)이었기에, 당연히 풀 스캔을 선택해야 정상입니다. 하지만 옵티마이저는 굳이 느린 랜덤I/O를 선택했습니다. 왜 그랬을까요?
그 이유는 쿼리에 포함된 ORDER BY와 LIMIT 절 때문입니다.
옵티마이저는 대량의 데이터를 메모리에서 정렬하는 작업에 큰 패널티를 부여합니다. 인덱스의 경우 이미 정렬되어있으므로, 옵티마이저는 인덱스를 타고 LIMIT 10만 찾으면 금방 끝날 것이라고 예측했습니다. 그 결과 옵티마이저는 정렬을 피하고자 인덱스를 타고 랜덤 I/O를 진행했습니다.
6. SQL 개선
옵티마이저가 정렬 비용을 피하기 위해 비효율적인 랜덤 I/O를 선택하는 문제를 해결해야 했습니다. 이를 위해 저는 지연 조인 기법을 도입하여 쿼리를 재설계했습니다.
SELECT
u.nickname,
u.profile_image_url AS profileImageUrl,
filtered_r.point,
RANK() OVER (ORDER BY filtered_r.point DESC) as ranking
FROM (
SELECT user_id, point, updated_at
FROM rankings
WHERE rank_period = :period
ORDER BY point DESC
LIMIT 50
) filtered_r
JOIN users u ON u.id = filtered_r.user_id
WHERE u.is_deleted = false
ORDER BY filtered_r.point DESC, filtered_r.updated_at ASC
LIMIT 10;
- AS-IS
: 조건에 맞는 데이터 10만 건 모두 users 테이블과 조인한 후 정렬했습니다. 이 과정에서 불필요한 데이터까지 접근하며 10만 번의 랜덤 I/O가 발생했습니다.
- TO-BE
: 서브쿼리에서 상위 50건의 ID만 먼저 필터링했습니다. 실제 조인은 50회만 수행하며 랜덤 I/O를 줄였습니다.
LIMIT 50을 설정한 이유
쿼리를 보면 최종적으로 필요한 건 10명인데, 서브쿼리에서는 50명을 조회하고 있습니다. 여기에는 서비스의 비지니스 로직을 고려한 트레이드 오프가 담겨있습니다.
저희 서비스는 회원 탈퇴 시 데이터를 물리적으로 삭제하지 않고, is_deleted 플래그를 업데이트하는 Soft Delete 방식을 사용하고 있습니다. 만약 랭킹 상위 10명을 딱 맞춰 조회했는데 그 중 탈퇴한 유저가 포함되어있다면, 최종 결과는 10명 미만이 반환될 위험이 있습니다.
이를 방지하기 위해 Over-fetching 전략을 이용했습니다. 탈퇴 유저가 존재할 확률을 고려하여 넉넉하게 50명을 우선 조회하고, 요구사항에 맞춰 유효한 10명을 반환하는 방식을 택했습니다.
해당 쿼리의 EXPLAIN 결과는 아래와 같습니다.
지연 조인 쿼리를 이용한 부하테스트 결과
지연 조인을 적용한 후, 동일한 환경에서 k6 부하 테스트를 진행한 결과, 놀라운 성능 향상을 확인할 수 있었습니다.
- 평균 응답 속도 (Avg) : 23.58s → 38.09ms (약 619배 개선)
- 성공률 : 12054번 요청 중 12048번 성공 (성공률 99.95%)
단순히 쿼리의 실행 순서만 바꿨을 뿐인데, 수 초가 걸리던 조회가 밀리초 단위로 단축되었습니다.
7. 커버링 인덱스
지연 조인을 통해 랜덤 I/O를 줄였고 응답 속도도 크게 개선되었지만, 여기서 멈추지 않고 조회 성능을 더 끌어 올리는 방법을 고민했습니다. 이를 위해 커버링 인덱스를 도입했습니다.
커버링 인덱스란?
커버링 인덱스란 쿼리를 충족시키는 데 필요한 모든 컬럼을 포함한 인덱스입니다.
- 일반 인덱스 조회
: 인덱스에서 검색 조건을 처리한 후, 나머지 컬럼을 가져오기 위해 디스크에 접근합니다.
- 커버링 인덱스 조회
: 조회하려는 모든 데이터가 이미 인덱스 트리에 저장되어 있습니다. 따라서 실제 테이블에 접근할 필요 없이 인덱스만 읽고 쿼리를 처리할 수 있습니다.
랜덤 I/O 제거
앞서 적용한 지연 조인 쿼리의 SELECT문을 살펴봅시다.
SELECT user_id, point, updated_at
FROM rankings
WHERE rank_period = :period
ORDER BY point DESC
LIMIT 50
기존 인덱스 (rank_period, point)는 user_id와 updated_at 정보를 가지고 있지 않습니다. 따라서 DB는 해당 데이터를 가져오기 위해 50번 디스크에 접근합니다. 저는 이 랜덤 I/O까지 제거하여 조회 성능을 끌어올리고자 했습니다.
커버링 인덱스의 Trade-off
이를 위해 인덱스 구성을 확장했습니다.
- AS-IS
: idx_ranking_period_point (rank_period, point desc)
- TO-BE
: idx_rankings_covering (rank_period, point desc, updated_at, user_id)
물론, 커버링 인덱스 도입에도 Trade-off가 존재합니다.
디스크 I/O 없이 메모리 상의 인덱스만으로 데이터 처리가 가능하므로 조회 성능은 향상되지만, 인덱스 키의 크기가 커져 저장 공간을 더 차지합니다. 또, 데이터 삽입/수정 시 갱신해야 할 인덱스 크기가 커져 쓰기 부하가 증가합니다.
하지만, 앞서 분석했듯 저희 서비스는 Read : Write 비율이 5 : 1 이상인 조회 중심 서비스입니다. 따라서, 쓰기 비용의 증가를 감수하더라도 다량의 트래픽을 빠르게 처리하는 것이 전체 시스템 성능에 이득이라고 판단하여 커버링 인덱스를 적용했습니다.
8. 도입 후 성능 개선
커버링 인덱스를 적용한 뒤 다시 EXPLAIN을 실행했습니다.
가장 눈에 띄는 변화는 Extra 컬럼에 Using index입니다. Using index는 필요한 컬럼이 인덱스 트리 안에 포함되어 있어, 데이터 테이블 접근이 아예 발생하지 않았음을 의미합니다.
그리고 k6 부하테스트를 진행했습니다.
- 평균 응답 속도 (Mean) : 38.09ms → 12.76ms (약 1.8배 개선)
- 성공률 : 12369번 요청 중 12369번 성공 (성공률 100.00%)
9. 최종 성과
커버링 인덱스를 통해 마지막 남은 I/O 병목까지 제거한 결과는 다음과 같습니다.
| 구분 | 평균 응답 속도 (Mean) | 개선 배수 (Mean) | API 성공률 |
| 기존 (Full Scan) | 15.74s | 1.0x (기준) | 99.87% |
| 시도 1 (인덱스 추가) | 23.58s | ▼ 약 1.5배 | 92.80% |
| 시도 2 (지연 조인) | 38.09ms | ▲ 약 413배 | 99.95% |
| 시도 3 (커버링 인덱스) | 12.76ms | ▲ 약 1234배 | 100.00% |
10. 더 시도해볼 것
1. 랭킹 상위 50명이 모두 탈퇴자라면?
현재 로직은 탈퇴 유저를 고려하여 넉넉하게 50건을 조회한 뒤 애플리케이션 단에서 필터링하고 있습니다. 하지만 상위 50명이 모두 탈퇴자라면 랭킹 리스트는 비어버리는 치명적인 버그가 발생할 수 있습니다.
따라서 해당 로직을 수정해야 합니다. rankings테이블에 is_active 컬럼을 추가하고, 인덱스를 (rank_period, is_active, point DESC ... )로 재설계해야 합니다. 이를 통해 정확히 LIMIT 10을 가져올 수 있도록 개선할 수 있습니다.
2. MYSQL의 한계 및 Redis
현재 구조는 데이터가 10만 건 수준이기에 MYSQL 튜닝만으로 유의미한 성능을 냈습니다. 하지만 트래픽이 폭증하고 데이터가 수천만 건으로 늘어난다면, 빈번한 순위 산정으로 인해 디스크 I/O 부하가 MYSQL의 임계치를 넘을 것입니다.
이에 따라 Redis Sorted Set 도입을 고려해볼 수 있습니다. 메모리 기반 정렬로 디스크 I/O를 제거하고, O(logN)의 속도를 보장할 수 있습니다.
이번 포스팅에서는 MYSQL 튜닝에 집중했으나, 다음 스텝으로는 Redis 도입을 고려해볼 수 있을 것 같습니다.
3. 10만 건이 대용량?
실무 수준에서 10만 건은 대용량이라고 부르기 부끄러운 수준일 것입니다. 10만 건은 다소 애매한 규모로 보일 수 있습니다. 데이터가 수가 애매했기 때문에 인덱스가 예상과 달리 성능을 떨어뜨린 것으로 보였을 수도 있을 것 같습니다.
1000만 건 이상의 대용량 데이터 환경에서 테스트했다면, 초기 인덱스 적용 시 성능이 저하되는 현상 없이 즉각적인 개선 효과를 확인할 수 있었을 것입니다.
11. 마치며
이번 트러블 슈팅은 단순히 쿼리 속도를 줄이는 작업이 아니라, 그동안 제가 작성해 온 코드와 DB를 대하는 태도를 되돌아보게 만든 시간이었습니다.
돌아가기만 하면 되는 코드?
돌이켜보면, 그동안 비지니스 로직을 구현하는 데에만 급급하여 데이터가 어떻게 저장되고 읽히는 지에 대한 깊은 고민 없이 쿼리를 작성해왔습니다. "데이터가 아직 적으니까", "JPA가 알아서 해주겠지"라는 안일한 생각에 대해 반성할 수 있는 시간이었습니다.
인덱스는 만능이 아니다
가장 인상깊은 점은, 인덱스를 넣었는데 성능이 떨어질 수 있다는 사실입니다. 1차적으로 쿼리를 잘못 작성한 것이 원인이긴 했지만, 막연히 "인덱스를 넣으면 성능이 빨라진다"는 안일한 생각을 이번 기회에 바로잡을 수 있었습니다. 데이터의 분포도나 선택도, 디스크의 I/O 패턴을 고려하지 않은 인덱스 설계는 오히려 독이 될 수 있음을 배웠습니다. 이제는 인덱스를 추가하기 전에 실행계획을 먼저 확인해볼 필요성을 느끼게 되었습니다.
감사합니다.