ROW_NUMBER()로 인한 DB 풀스캔, 이렇게 해결했습니다

img.png 안녕하세요. 이번 글에서는 회사 업무 중 경험한 DBMS의 병목 요소를 파악하고 해결한 과정을 공유해보려 합니다.

상황

회사 솔루션 제품의 대시보드 화면에 최근 감사 로그를 표시하는 API를 구현하는 업무를 맡았습니다.

저희 회사에서는 각 테이블별로 Dao 클래스가 구현되어 있었고, 조회용 동적 쿼리를 생성하는 메서드가 이미 준비되어 있었습니다.

파라미터가 null이 아니면 if 문으로 조건을 붙이는 형태였는데요. 전사적으로 이런 스타일을 사용하고 있었기 때문에 저도 이를 따라 감사 로그 조회 API를 쉽게 구현할 수 있었습니다. 기능적으로는 정상적으로 동작했고, 응답도 문제없이 반환되었습니다.

하지만 저는 여기서 한 단계 더 나아가 성능까지 검증해보기로 했습니다. 대시보드는 솔루션의 얼굴 즉 첫 화면이기 때문에, 빠른 렌더링을 위해서는 API 빠른 응답속도가 필수불가결하다고 판단했습니다.

그래서 약 1,000만 건의 더미 데이터를 삽입한 뒤 성능 테스트를 진행했는데, 응답 속도가 약 3초나 걸렸습니다. 게다가 데이터가 증가할수록 응답 시간도 선형적으로 늘어나는, 최악의 상황이었습니다.

이 문제를 파악하기 위해 곧바로 해당 쿼리의 실행 계획을 확인했습니다. img.png 시간 복잡도를 추정해보니, O(NlogN) 수준으로 비효율적인 연산이 발생하고 있었습니다.

원인

이 현상의 원인은 바로 ROW_NUMBER() 로 인한 테이블 풀스캔이었습니다. 쿼리는 다음과 같았어요.

SELECT *
FROM (
    SELECT 
        al.*, 
        ROW_NUMBER() OVER (ORDER BY created_date DESC) as row_num
    FROM 
        audit_log al
) numbered_log
WHERE 
    row_num BETWEEN 1 AND 5;

ROW_NUMBER()는 각 레코드에 순서를 매기기 위해 전체 테이블을 스캔해야되고 ORDER BY created_date DESC 의한 정렬도 해야합니다. 또한 이 결과는 임시 테이블로 생성되기 때문에, row_num에 대한 인덱스도 존재하지 않습니다.

결국 1~5행만 조회하더라도 내부적으로는 전체 스캔이 두 번 일어나게 되는 것이죠.

제가 생각하는 전체적인 작업을 순번을 매겨 한번 말씀드려보겠습니다.

  1. audit_log 테이블의 1000만 건 데이터를 모두 스캔 후 임시 테이블에 저장
    • O(N)
  2. 임시 테이블의 1000만 건 데이터를 created_date 기준으로 정렬 + 해당 데이터에 순번 부여
    • O(NlogN)
  3. 순번이 부여된 1000만 건의 임시 테이블을 다시 풀스캔하면서 row_num가 1~5인 데이터 검색
    • O(N)

그림으로 표현하면 다음과 같습니다. img_2.png

해결

그럼 어떤식으로 저는 해결을 하였을까요?

모든 경우의 수를 따져서 인덱스를 도입해봤지만, ROW_NUMBER() 기반 구조에서는 별 효과가 없었습니다. 결국 새로운 Dao 메서드를 별도로 작성하는 방향으로 접근했습니다.

다음과 같이 LIMIT을 사용하는 단순 쿼리로 변경한 결과, 성능이 즉시 개선되었습니다.

SELECT *
FROM audit_log
ORDER BY created_date DESC
LIMIT 5;

또한 실행 계획을 확인해보면 index를 잘 쓰고있는것을 확인할 수 있습니다. img_3.png

이 방식으로 변경한 뒤 응답 시간은 약 3000ms → 300ms 이하로 단축되었고, 데이터가 늘어나도 일정한 응답속도를 유지했습니다. 시간복잡도 또한 O(logN + k)로 개선이 되었습니다. img.png

보고와 의사결정

사실 제가 발견한 이 문제는 조금 조심스러웠습니다. 회사의 거의 모든 조회 쿼리가 ROW_NUMBER()를 사용하는, 일종의 '표준'이었기 때문입니다.

관리자급 소수 인원이 사용하는 B2B 솔루션 특성상 대부분의 화면에서는 큰 문제가 되지 않았던 것도 있습니다.

하지만 제가 발견한 감사 로그 데이터는 시간이 지날수록 증가하는 데이터였습니다. 서비스가 운영되는 이상 계속 데이터가 쌓이는 형태인것입니다.

테스트 환경에서 이미 1,000만 건에 3초라는 응답 속도를 확인했고, O(NlogN)라는 안좋은 시간복잡도 마주쳤는데 개선을 안하면 개발자라 말 할 수 없을것입니다.

저는 문제를 분석해 나름 정리한 문서로 팀장님께 보고드리고 개선 방향을 제안했습니다.

그 결과, 대시보드 전용으로 LIMIT 기반 Dao 메서드를 사용하는 것으로 결정되었고

응답 속도는 1,000만건 기준 70% 개선(3.2초→0.96초)되었습니다.

후기

처음 문제를 봤을때는 "사내 컨벤션을 따라야 한다"는 생각에 갇혀 기존에 사용하던 ROW_NUMBER() 쿼리 방식에만 몰두했습니다.

하지만, 문제를 해결하겠다는 의지로 실행 계획 분석을 시도했고, 이를 통해 성능 저하의 근본적인 원인을 파악할 수 있었습니다. 이후 대안으로 LIMIT를 적용해보니, 실제로 성능이 극적으로 개선되는 것을 확인했습니다.

단순히 "바꿨다"가 아니라, 분석 결과와 성능 개선 수치라는 근거를 바탕으로 문서를 작성했습니다. 이를 팀장님께 보고드렸고, 칭찬과 함께 흔쾌히 컨펌이 떨어졌고 돈 받고 파는 솔루션에 인생 첫 기여를 했습니다. 짧은 직장 생활에서 첫 성능개선이라 뿌듯한 마음이 더욱 큰거 같습니다.

이 글을 읽으시는 분들 중 비슷한 고민을 하는 분이 있다면, 제 경험이 작은 도움이나마 되면 좋겠네요.

혹시 틀린 부분은 메일로 연락주시면 정말 감사하겠습니다. 부족한 글 읽어 주셔서서 감사합니다.

참고 자료

전체 글 보기