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

December 13, 2025

1. 서론

제품의 대시보드 화면에 필요한 최근 감사 기록 조회 API를 개발하는 과정에서 마주친 쿼리 성능 문제와, 이를 개선하며 분석한 내용을 정리해본다.

단순히 쿼리를 바꾼 경험이 아니라, 왜 느렸는지, DB 옵티마이저가 어떤 선택을 했는지를 이해하는 과정에 초점을 맞췄다.

2. 상황

2.1 개발 환경

Java 8 & Spring Framework
MariaDB 10.2

2.2 초도 개발

제품에 로그인하면 가장 먼저 노출되는 대시보드 화면에 최근 감사 기록을 보여주는 컴포넌트를 신규 개발이 필요했다. 이를 위해 최근 감사 로그를 조회하는 API가 필요했고, 해당 API 개발은 내가 담당했다.

최근 감사 기록 조회 API는 복잡한 비즈니스 로직보다는 DB 조회 자체가 핵심인 기능이었다.

JdbcTemplate를 사용해 쿼리를 실행하고 결과를 그대로 프론트에 전달하는 단순한 흐름이었고, 기존에 제품의 코드에서 페이지네이션에 쓰는 쿼리를 재활용해 API 구현은 빠르게 완료했다.

기존 쿼리를 재사용해 작성한 초기 버전은 대략 다음과 같은 형태였다. 회사 제품의 실제 코드와 쿼리는 외부 공개가 어려워, 이해를 돕기 위해 단순화한 DDL과 쿼리를 사용하여 글을 진행하는점 너른 이해 부탁드린다.

아래는 audit_log 테이블의 DDL과, 이를 조회하는 쿼리이다

create table audit_log
(
    id          varchar(20)  not null
        primary key,
    title       varchar(255) null,
    event       varchar(50)  null,
    type        char         null,
    log         longtext     null,
    create_date datetime     null
);
 
create index audit_log_create_date_idx
    on audit_log (create_date);

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

3. 문제 파악

3.1 테스트 필요성과 결과

감사 기록 데이터는 사용 기간이 길어질수록 기하급수적으로 증가하는 특성을 가진다. 또한 신규 개발된 API는 제품의 첫 화면을 담당하므로, 빠른 응답 속도는 UX 측면에서 필수라고 판단했다.

이에 실제 운영 환경을 가정해 대규모 더미 데이터 기반 성능 테스트를 진행했다.

테스트 준비는 단순했다. 1,000만 건의 데이터를 삽입하는 프로시저를 작성하고 실행하는 것으로 충분했으며, 테스트 결과는 심각했다.

1,000만 건 기준 조회 시간: 3초 이상

대시보드에서 단 5건의 데이터를 조회하는데 3초 이상이 소요되고, 화면에 로딩이 노출된다면 이는 사용자 경험 측면에서 치명적이다.

4. 문제 원인 분석

4.1 실행계획을 기반한 문제 분석

실행 계획을 확인해보면 핵심적인 문제가 바로 드러난다.

{
  "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
              }
            }
          }
        }
      }
    }
  }
}

바로 위 JSON은 EXPLAIN format=JSON 의 결과이다. 이를 분석하면 다음과 같이 해석이 가능하다.

임시 테이블 생성을 위한 TABLE FULL SCAN 발생(인덱스 미활용)
filesort 발생
임시 테이블 FULL SCAN(인덱스 미활용)으로 between 1 and 5 탐색

2번의 전체 레코드 수에 대한 TABLE FULL SCAN 발생과 정렬이 발생했다. 이런 상황이면 데이터가 증가하면 그에 비례해서 조회속도는 점점더 느려질것 또한 자명하게 예상된다.

그렇다면 왜 인덱스가 있음에도 풀 스캔이 발생했을까?

4.1.1 임시 테이블 생성에 기존 create_date 인덱스를 안쓰는 이유

ROW_NUMBER()를 사용하면서 MariaDB는 내부적으로 임시 테이블을 생성한다. 이 과정에서 기존의 create_date 인덱스가 사용되지 않았다.

가장 큰 원인은 a.* (모든 컬럼 조회)였다.

create_date 인덱스에는 날짜 컬럼만 존재
a.*를 조회하려면 인덱스 탐색 후, 다시 실제 데이터 페이지로 접근 필요

Secondary Index 탐색 후, PK 값을 통해 InnoDB의 Clustered Index(Primary Key 기반 레코드) 로 다시 접근해야 하므로 랜덤 I/O가 증가한다.

옵티마이저 입장에서는 다음과 같이 판단했을 가능성이 크다.

"결국 대부분의 레코드에 접근해야 한다면 인덱스를 타고 랜덤 I/O를 반복하는 것보다 차라리 테이블을 한 번에 읽어서 메모리에서 정렬하자"

4.1.2 ROW_NUMBER()로 생성된 임시 테이블을 또 풀스캔하는 이유

문제는 여기서 끝이 아니다.

ROW_NUMBER()로 생성된 임시 테이블에는 어떠한 인덱스도 존재하지 않는다. 따라서 row_num BETWEEN 1 AND 5 조건을 처리하기 위해서도, DB는 임시 테이블 전체를 스캔해야 한다.

결과적으로, 이 쿼리는

원본 테이블 풀 스캔 + 정렬
임시 테이블 풀 스캔

이라는 비효율적인 실행 흐름을 가지게 되었다.

5. 해결 과정

5.1 회사가 ROW_NUMBER() 쓰는 이유

우리 제품은 단일 DB가 아니라 다중 RDBMS를 지원한다.

MariaDB
Oracle
MSSQL

세 DB에서 공통으로 사용할 수 있는 SQL을 유지하기 위해 페이징 조회에 ROW_NUMBER()를 사용해왔다.

5.2 팀에 문제 상황과 해결 방안 공유

분석 결과를 간단히 문서화하여 팀에 공유했고, 해결책 다음과 같았다.

"이 케이스에서는 LIMIT를 쓰자"

최근 N건 조회라는 명확한 목적에는 ROW_NUMBER()가 아니라, 인덱스를 그대로 활용할 수 있는 쿼리가 훨씬 적합했다.

SELECT *
FROM audit_log
ORDER BY create_date DESC
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
    }
  }
}

create_date 인덱스 활용
불필요한 정렬 및 임시 테이블 제거

인덱스를 타면서 딱 5건만 읽고, 이미 정렬이 되어있는 인덱스를 활용하니 filesort도 발생 하지않은 실행계획이 산출된것을 볼 수 있다.

수치적으로도 응답 속도는 1,000만건 기준 83% 개선(3.2초 → 0.54초)되었다. 또한 데이터가 쌓여도 조회 속도가 저하되지 않고 안정적인 조회 속도를 보장할 수 있게되었다.

6. 배운 점

단순 조회 기능이라도 뜻밖의 병목이 발생할 수 있으니 미래를 고려한 테스트는 꼭 하자
ROW_NUMBER()는 여러 DB에서 지원하기에 범용적이지만, 항상 최적의 선택은 아니다
인덱스가 존재하더라도, 조회 컬럼과 쿼리 구조에 따라 옵티마이저는 이를 사용하지 않을 수 있다
실행 계획을 직접 확인하고, DB가 왜 그런 선택을 했는지 이해하는 과정이 성능 개선의 핵심이다
"범용성 있는 쿼리"와 "성능 최적화된 쿼리"는 종종 트레이드오프 관계에 있다