Published on

인기글 조회를 위한 MySQL 정렬 쿼리 개선

Authors
  • avatar
    Name
    김민석
    Twitter

Introduction

상황

조회수를 기준으로 내림차순 정렬하여 상위 5개 게시글을 조회하는 쿼리를 사용했다.

// 문제가 된 Repository 메서드
@Query("SELECT a FROM Article a ORDER BY a.viewCount DESC")
List<Article> findTopByViews(Pageable pageable);

위 코드는 실제로 다음과 같은 SQL을 생성한다.

SELECT article0_.id as id1_0_,
       article0_.title as title6_0_,
       article0_.view_count as view_co7_0_

       // 생략 ...

FROM article article0_
ORDER BY article0_.view_count DESC
LIMIT 5;

서비스에서의 호출

@Transactional(readOnly = true)
public List<ArticleListResponse> getTopRankedArticles() {

    // 5개만 필요한데 ...
    List<Article> topArticles = articleRepository.findTopByViews(PageRequest.of(0, 5));

    return topArticles.stream()
        .map(ArticleListResponse::from)
        .toList();
}

부하 테스트에서 확인된 결과

게시글 데이터를 1500개로 늘리고 동시 접속자 시나리오를 테스트했더니 다음과 같은 에러가 발생했다.

# 애플리케이션 로그에서 보이기 시작한 경고들
2025-07-21 14:23:11.234 WARN  --- [http-nio-8080-exec-12]
o.h.engine.jdbc.spi.SqlExceptionHelper   :
SQL Warning Code: 1003, SQLState: 01000
Message: Query execution was interrupted

2025-07-21 14:23:15.891 ERROR --- [http-nio-8080-exec-15]
c.z.hikari.pool.HikariPool            :
HikariPool-1 - Thread starvation or clock leap detected
(housekeeper delta=4s567ms).

인기글 조회 API가 전체 시스템의 병목지점이 되어버린 것이다.

응답 시간이 늘어나면서 메인 페이지 로딩을 길어지는 상황이 발생하기도 했다.

문제는 DB 서버였다. CPU 사용률이 증가하면서 쿼리들까지 영향을 받기 시작했다. 커넥션 풀이 고갈되고, 타임아웃 에러가 발생했다. 5개의 결과를 얻기 위해 1,500개의 모든 행을 읽고 정렬하고 있었던 것이다.


문제

부하 테스트 결과 MySQL의 정렬 처리 방식에서의 문제였다는 것을 알게 되었다.

풀 테이블 스캔이 무조건 일어나는 상황

ORDER BY view_count DESC LIMIT 5 쿼리를 실행할 때 MySQL은 다음과 같은 상황에 빠진다

image.png
  • type: ALL - 인덱스 없이 전체 테이블을 순차적으로 읽어야 한다.
  • rows: 1500 - 1500개 모든 행을 검사해야 한다.
  • Extra: Using filesort - 별도의 정렬 과정이 필요하다.

MySQL은 상위 5개만 필요하다는 것을 알고 있지만, view_count 컬럼에 정렬된 인덱스가 없기 때문에 어쩔 수 없이 모든 데이터를 읽어서 정렬해야 한다.


메모리 vs 디스크 정렬의 성능 차이

MySQL의 정렬 처리는 sort_buffer_size 설정이 기본적으로 262144 = 256KB 가 설정된다.

image.png

메모리 부족 시 발생하는 문제

  1. 초기 시도: 256KB 메모리 버퍼에서 정렬 시도
  2. 용량 초과: 1,500개 행의 모든 컬럼 데이터가 256KB 초과
  3. 디스크 정렬로 전환: 임시 파일을 이용한 외부 정렬 시작
  4. 다중 패스 정렬: 데이터를 여러 번 읽고 쓰며 병합

image.png

Sort_merge_passes가 0이 아니라는 것은 메모리에서 정렬이 불가능해서 디스크를 사용했다는 의미다.

동시에 접근해서 생기는 리소스 충돌 문제

문제는 여러 사용자가 동시에 메인 페이지에 접속할 때 발생한다.


동시 접속자 100명

image.png
# 각 사용자마다 다음 과정이 동시에 실행됨
1. MySQL 커넥션 획득
2. 1,500개 행 전체 읽기 (디스크 I/O)
3. 임시 공간에서 정렬 작업
4. 상위 5개 선택 후 반환
5. 커넥션 반납

동시에 100개의 요청이 들어오자 각각이 1,500개 행을 읽으면서 디스크 I/O가 증가했다.

요청마다 정렬 작업을 수행하다 보니 CPU 사용률이 증가 했고, 각 요청이 정렬을 위해 별도의 버퍼 메모리를 잡으려 하면서 메모리 부족 현상까지 나타났다. 결국 쿼리 실행 시간이 길어지면서 커넥션이 오랫동안 점유되었고, 커넥션 풀이 고갈되면서 다른 요청들이 대기 상태에 빠지는 문제가 계속 누적되었다.


데이터 증가에 따른 성능 저하

정렬 알고리즘의 시간 복잡도는 O(n log n)이므로, 데이터가 늘어날수록 성능이 저하된다.

데이터 100개:     100 × log₂(100)       ≈ 664 연산
데이터 1,500개:   1,500 × log₂(1,500)   ≈ 15,825 연산 (24배 증가)
데이터 10,000개:  10,000 × log₂(10,000) ≈ 132,877 연산 (200배 증가)

실제 MySQL 환경에서는 정렬 연산만 일어나는 것이 아니라 여러 요소들이 동시에 작용한다.

우선 모든 행을 읽어야 하기 때문에 디스크 I/O 시간이 늘어나고, 정렬 과정에서 메모리가 부족해 sort_buffer_size를 초과하면 디스크에 임시 파일을 만들어 정렬하게 된다.

MySQL은 데이터를 16KB 크기의 페이지 단위로 읽기 때문에 필요한 데이터보다 더 많은 블록을 불러오게 되고, 버퍼 풀에 해당 페이지가 없으면 캐시 미스가 발생해 디스크 접근이 반복된다.

이런 상황들이 겹치면서 이론적으로 계산한 것보다 성능 저하가 발생한다.


인덱스 추가의 제약과 단점

view_count에 인덱스를 추가하는 것도 완벽한 해결책이 아니다.

-- 인덱스 추가 시 예상되는 문제들
CREATE INDEX idx_view_count ON article(view_count DESC);

문제점들

쓰기 성능 저하: 조회수가 증가할 때마다 인덱스 페이지 수정 필요하다.

image.png

락 충돌 증가: 인기 글의 조회수 업데이트 시 인덱스 레벨에서 락 충돌 발생

image.png

메모리 사용량 증가: 인덱스도 버퍼 풀에 캐시되어 메모리 사용량 증가

image.png

매번 요청할 때마다 전체 데이터를 정렬하려는 접근 방식이 문제이다. 조회수는 읽기만 하는 값이 아니라, 읽을 때마다 계속 증가하는 값이다.

인기 있는 글일수록 더 자주 조회되기 때문에 인덱스가 끊임없이 갱신되었고, 읽기 성능을 높이기 위해 만든 인덱스가 읽기 성능을 떨어뜨리는 상황이 발생했다.

문제의 원인은 실시간 정렬에 의존한 구조에 있었다. 요청이 들어올 때마다 최신 데이터를 정렬해서 가져오려는 방식이 문제로 나타난 것이다.


원인

MySQL에서 ORDER BY를 포함한 쿼리가 실행되는 내부 과정을 알아야 한다.

MySQL 쿼리 옵티마이저의 판단 과정

  1. 인덱스 사용 가능성 검토
SHOW INDEX FROM article;
-- view_count 컬럼에 인덱스가 없음을 확인
  1. 실행 계획 수립
EXPLAIN FORMAT=JSON SELECT * FROM article ORDER BY view_count DESC LIMIT 5;
image.png

정렬 공간의 메모리 vs 디스크 충돌 상황

MySQL의 정렬 성능은 sort_buffer_size 설정에 의존한다는 것이다.

구분메모리 정렬디스크 정렬
조건데이터 크기 < sort_buffer_size데이터 크기 > sort_buffer_size
알고리즘Quick Sort (평균 O(n log n))External Merge Sort
성능빠름 (RAM 속도)느림 (디스크 I/O 속도)
임시파일생성 안 함.tmp 파일 생성
Sort_merge_passes01 이상

메모리에서만 정렬할 때는 100개의 데이터를 정리하는 데 약 30ms 정도밖에 걸리지 않았다. 하지만 데이터가 1,500개로 늘어나면서 디스크 정렬이 개입되자 달라졌다.

디스크에서 메모리로 데이터를 읽어 오는 데만 500ms가 걸렸고, 청크 단위로 나누어 정렬한 뒤 임시 파일로 다시 쓰는 과정에서 800ms가 추가로 소요됐다.

이어서 여러 청크를 합치는 병합 정렬이 600ms를 차지했고, 최종 결과를 만들어내는 데에도 200ms가 더 필요했다. 결국 전체 과정은 2,100ms가 걸려 메모리 정렬이 느려지며 성능 저하가 발생한 것이다.


LIMIT의 성능 저하

"5개만 필요하니까 MySQL이 똑똑하게 처리할 것"이라고 생각했지만 내부 동작은 달랐다.

MySQL의 쿼리 처리 순서

-- 논리적 순서 (개발자가 생각하는 순서)
SELECT → ORDER BY → LIMIT

-- 실제 내부 처리 순서
FROM → WHERE → SELECT → ORDER BY → LIMIT

왜 LIMIT이 나중에 적용되는가?

SQL 표준에 따르면 ORDER BY는 전체 결과집합에 대해 수행되어야 한다.

  1. "article 테이블의 모든 행을 가져와라"
  2. "view_count 기준으로 내림차순 정렬해라"
  3. "정렬된 결과에서 상위 5개만 선택해라"

개선이 불가능한 이유는 인덱스 없이는 MySQL이 "어떤 5개가 상위 5개인지" 미리 알 수 없기 때문에, 전체 데이터를 정렬해야 하는 것이다.


해결

Redis 기반 캐싱과 스케줄링 개선했다.

인기글은 실시간으로 변경될 필요가 없다는 점에서 Redis 캐싱을 도입했다. 매번 요청이 들어올 때마다 전체 테이블을 스캔하고 정렬하지 않고, 미리 계산된 인기글 ID 목록을 Redis에 저장해두고 해당 ID들로 데이터베이스에서 필요한 데이터만 조회하는 방식으로 변경했다. 전체 테이블 스캔과 정렬 과정을 완전히 제거할 수 있었다.

@Transactional(readOnly = true)
public List<ArticleListResponse> getTopRankedArticles() {
    List<Long> topIds = getTopArticleIds();
    Map<Long, Article> articleMap = getArticleMapInOrder(topIds);

    return topIds.stream()
            .map(articleMap::get)
            .filter(Objects::nonNull)
            .map(ArticleListResponse::from)
            .toList();
}

private List<Long> getTopArticleIds() {
    String key = RedisKeys.TOP_ARTICLES;
    List<String> cachedIds = redisTemplate.opsForList()
            .range(key, 0, 4);

    return cachedIds.stream()
            .map(Long::parseLong)
            .toList();
}

스케줄링 갱신

정렬 작업은 백그라운드에서 주기적으로 수행하도록 분리했다. 5분마다 실행되는 스케줄러가 데이터베이스에서 최신 인기글 순위를 계산하고 Redis에 ID 목록만 업데이트하는 방식으로 구현했다. 이렇게 하면 정렬 작업이 사용자 요청과 분리되어 응답 성능에 영향을 주지 않는다.

// 스케줄러
@Scheduled(fixedRate = RedisConstants.RANKING_REFRESH_INTERVAL)
public void syncTopArticlesToRedis() {
    articleRankingService.updateRedisTopArticles();
}

@Transactional(readOnly = true)
public void updateRedisTopArticles() {
    // 백그라운드에서 실행되는 정렬 쿼리
    List<Article> topArticles = articleRepository.findTopByViews(
            PageRequest.of(0, 5));

    String key = RedisKey.getTopArticleListKey();

    // Redis에 결과 저장
    redisTemplate.delete(key);
    for (Article article : topArticles) {
        redisTemplate.opsForList().rightPush(key, article.getIdAsString());
    }
}

순서 보장을 위한 정교한 조회 로직

Redis에서 가져온 ID 순서를 유지하면서 데이터베이스에서 실제 데이터를 조회하는 부분에서는 순서 보장이 중요했다. Redis List에서 조회한 순서대로 결과를 반환해야 하기 때문에 LinkedHashMap을 사용해 삽입 순서를 보장하도록 구현했다.

private Map<Long, Article> sortedArticleMap(List<Long> topIds) {
    return articleRepository.findAllById(topIds).stream()
            .sorted(Comparator.comparingInt(article -> topIds.indexOf(article.getId())))
            .collect(Collectors.toMap(
                    Article::getId,
                    Function.identity(),
                    (existing, replacement) -> replacement,
                    LinkedHashMap::new
            ));
}

아키텍처 변경을 통해 사용자 요청 시에는 Redis에서 빠르게 ID 목록을 조회하고, 해당 ID들로 데이터베이스에서 5개의 게시글만 조회하는 방식으로 개선했다. 전체 테이블 스캔과 정렬이 제거되면서 응답 시간이 단축되었다.


결과

image.png

개선된 시스템을 동일한 부하 테스트 환경에서 검증한 결과 성능 향상을 확인할 수 있었다. 기존에 평균 2.1초가 걸리던 인기글 조회 API가 평균 15ms로 단축되어 응답 시간 개선했다.

Redis에서 5개의 ID만 조회하고, 해당 ID들로 데이터베이스에서 정확히 필요한 데이터만 가져오는 방식으로 변경한 결과였다.


image.png

CPU 사용률이 42% → 25%로 감소했으며, 메모리 측면에서는 정렬 버퍼가 필요하지 않게 되어 메모리 사용량이 개선되었다. 디스크 I/O는 전체 테이블 스캔이 제거되면서 성능이 개선 되었다.

인기글 조회 기능이 더 이상 전체 시스템의 병목지점이 되지 않았고, 메인 페이지 로딩 시간이 빨라졌다.

MySQL의 쿼리 처리 성능도 개선되었다. 인기글 순위가 실시간으로 변경될 필요가 없었고 스케줄러가 정상 실행되어 Redis에 데이터가 유지되며 빠른 응답을 제공했다.

Discuss on Twitter • View on GitHub