최근 5건 조회에 3초가 걸린 이유: 풀 스캔은 왜 발생했을까?

December 13, 2025

요약

신규 개발된 대시보드 API의 안정성을 검증하기 위해 1,000만 건의 대규모 데이터를 투입하여 테스트를 진행하던 중, 3.2초 이상의 성능 병목을 발견했다. 다중 RDBMS 지원을 위해 관성적으로 사용되던 ROW_NUMBER() 기반 쿼리의 문제점을 실행 계획 분석으로 증명해봤고, LIMIT 절을 활용한 최적화를 통해 성능을 약 83% 개선하는 성과를 달성했다.

MariaDB를 대상으로 한 쿼리 튜닝

상황

배경

제품 대시보드 화면에서 '최근 감사 기록 5건'을 노출하는 신규 API의 개발이 완료됨에 따라 해당 기능의 전반적인 테스트와 검증 업무를 맡음
단순히 기능이 기획대로 작동하는지 확인하는 수준에 머무르지 않고, 시간이 지남에 따라 데이터가 누적되는 로그 데이터의 특성에 주목
실제 운영 환경에서도 API가 안정적으로 작동될 수 있는지 선제적으로 파악하기 위해, 대규모 데이터셋을 구축하여 성능 테스트를 주도적으로 수행

문제 발견

프로시저를 통해 1,000만 건의 더미 데이터를 삽입
조회 속도를 측정한 결과, 단 5건을 가져오는 데 3초 이상이 소요되는 심각한 지연을 확인

기존 구현

동료 개발자가 작성한 초기 API는 다중 DB(MariaDB, Oracle, MSSQL) 호환성을 위해 제품 내 공통 페이징 방식인 ROW_NUMBER() OVER 쿼리를 사용

해결 과정

1. 문제 원인 분석(실행 계획 기반)

EXPLAIN FORMAT=JSON으로 분석한 결과, 기존 ROW_NUMBER() 방식은 대용량 데이터 환경에서 치명적인 결함이 있었다. 다음은 실행 계획과 분석 내용이다.

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "rows": 10003872,
      "filtered": 100,
      "attached_condition": "row_num_added_audit.row_num between 1 and 5",
      "materialized": {
        "query_block": {
          "select_id": 2,
          "window_functions_computation": {
            "sorts": {
              "filesort": {
                "sort_key": "a.create_date desc"
              }
            },
            "temporary_table": {
              "table": {
                "table_name": "a",
                "access_type": "ALL",
                "rows": 10003872,
                "filtered": 100
              }
            }
          }
        }
      }
    }
  }
}

분석 1) 인덱스 미활용 및 Full Scan

create_date 인덱스가 있음에도 불구하고, 모든 컬럼(SELECT *)을 가져오기 위한 랜덤 I/O 비용 탓에 옵티마이저가 테이블 전체를 읽어 메모리에서 정렬(filesort)하는 방식을 선택

Secondary Index인 create_date를 통해 탐색을 시도하더라도, 실제 레코드의 모든 정보를 가져오기 위해서는 찾아낸 PK 값을 이용해 InnoDB의 Clustered Index에 다시 접근하는 과정이 필수적이다. 이 과정에서 레코드마다 개별적으로 데이터 페이지를 찾는 랜덤 I/O가 발생하게 되는데, 조회 대상 데이터가 1,000만 건에 달하는 상황에서는 이 비용이 증가하게 되는 것.

결국 옵티마이저 입장에서는 "수많은 레코드에 대해 인덱스 기반의 랜덤 I/O를 반복하며 Clustered Index를 넘나드는 것보다, 차라리 테이블 전체를 순차적으로 읽어 들인 뒤 메모리에서 filesort를 수행하는 것이 전체적인 실행 비용 면에서 더 유리하다"라고 판단한 것이라 볼 수 있다.

분석 2) 임시 테이블 생성 및 Materialization 오버헤드

ROW_NUMBER()는 윈도우 함수 특성상 전체 결과 집합에 대해 순위를 매겨야 하므로, 1,000만 건의 데이터를 담은 임시 테이블(Derived Table)을 생성(Materialization)
이 과정에서 발생하는 디스크 I/O와 메모리 사용 또한 성능 저하의 원인

2. 최적화 제안 및 적용

분석 결과를 바탕으로, 범용적인 윈도우 함수 방식 대신 해당 DB 엔진의 인덱스를 직접 활용하여 Early Exit를 유도하는 LIMIT 기반의 쿼리로 개선안을 제시했다.

-- 개선된 쿼리: 정렬된 인덱스를 직접 참조하여 5건만 추출
SELECT * 
FROM audit_log 
ORDER BY create_date DESC 
LIMIT 5;

3. 결과 확인

실행 계획 개선

불필요한 임시 테이블 생성과 filesort 제거
옵티마이저가 LIMIT 절을 확인하고 인덱스를 스캔하다가 5건이 채워지는 즉시 스캔을 중단

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "audit",
      "access_type": "index",
      "key": "idx_audit",
      "key_length": "6",
      "used_key_parts": ["create_date"],
      "rows": 5,
      "filtered": 100
    }
  }
}

성능 개선 수치

응답 속도
- 1,000만 건 기준 응답 속도가 3.2초에서 0.54초로 단축 (약 83% 개선)
상수 시간(O(1)) 응답 보장
- 기존 방식은 전체 데이터 수에 비례하여 응답 시간이 늘어나는 구조였으나, 개선 후에는 데이터 규모와 상관없이 인덱스 리프 노드 스캔만으로 결과를 반환

결론

주도적 검증의 중요성

개발자가 작성한 코드를 단순히 수용하는 것이 아니라, 실제 운영 환경을 고려한 테스트를 통해 잠재적인 장애 요소를 선제적으로 차단할 수 있었다.

기술적 분석 능력

단순히 "느리다"는 현상 파악에 그치지 않고, 실행 계획 분석을 통해 옵티마이저의 선택 이유를 논리적으로 규명하는 과정이 팀원을 설득하고 최적의 대안을 찾는 데 유용하다는 것을 경험했다.

효율과 범용성의 균형

모든 DB에서 통용되는 쿼리 구조도 좋지만, 빠른 조회가 중요한 첫 화면 API에서는 특정 DB에 최적화된 문법을 사용하는 것이 UX 측면에서 훨씬 유리한 선택일 수 있음을 이해했다.