[성능개선]3000ms를 300ms로: MariaDB의 ROW_NUMBER() 알아보기

대시보드 감사 기록 조회 API의 성능 테스트 중, 1000만 건 데이터 조회 시 응답 시간이 3000ms를 훌쩍 넘는 현상을 발견했다. 당연히 인덱스를 탈 것이라 예상했던 쿼리가 FULL SCAN을 하고 있었다.

이 글은 문제의 원인을 파악하고 응답 시간을 10분의 1로 단축하기까지의 과정과 그 과정에서 배운 점을 담은 기록이다.

1. 문제 상황: 느린 쿼리 그리고 기대와 다른 실행계획

대시보드 화면에서 가장 최근에 기록된 감사기록 5건을 조회하는 API 개발을 맡게 됐다. 기존의 소스코드를 분석해보니 이미 관련 기능을 제공하는 DAO가 있었고, POSTMAN으로 테스트해본 결과 적은 데이터에서는 별 문제없이 잘 동작했다.

하지만 1000만 건의 데이터로 성능 테스트를 해보니 응답시간이 3000ms를 훌쩍 넘어가는 슬로우 쿼리가 되었다. 겨우 최근 데이터 5건을 조회하는데 3000ms가 넘는 시간이 걸리는건 도저히 납득할 수 없는 병목이라는 판단이 들었다.

1.1 문제의 쿼리

우리 솔루션은 MariaDB와 MSSQL, 두 DBMS를 지원해야 해서 개발 공수를 최소화하기 위해 ANSI 표준을 지키는 것을 사내 컨벤션으로 사용하고 있었다. 페이지네이션 같은 기능에는 공통으로 지원하는ROW_NUMBER() 윈도우 함수를 적용하고 있었다.

[기존 쿼리(예시)]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;

[실행 계획(NULL 인 속성은 제외)]

id	select_type	table	type	rows	Extra
1	PRIMART	<derived2>	ALL	10003872	Using where
2	DERIVED	al	ALL	10003872	Using Temporary

이 실행계획을 참고하면 MariaDB 옵티마이저는 ROW_NUMBER()를 사용한 쿼리를 다음과 같은 순서로 처리한다.

FROM audit_log: 테이블의 모든 데이터(3000만 건)를 메모리에 올림
ORDER BY created_date DESC: 메모리에 올라온 3000만 건의 데이터를 정렬(첫번째 FULL SCAN 발생)
ROW_NUMBER() OVER (…): 정렬된 3000만 건의 데이터에 대해 순번을 1번부터 3000만 번까지 모두 매김
WHERE row_num BETWEEN 1 AND 5: 순번이 매겨진 3000만 건의 결과 중에서 5개를 필터링하여 반환(순번에 대한건 인덱스가 없기때문에 두번째 FULL SCAN 발생)

즉, 결과적으로 필요한 데이터는 5건뿐이지만, 그 5건을 찾기 위해 3000만 건을 FULL SCAN하고, 정렬하고, 순번을 매기고 마지막으로 WHERE 절 조건 필터를 위한 FULL SCAN 했던 것이다.

심각한것은 임시테이블과 본테이블 2개를 모두 FULL SCAN을 하고있는것이다.

created_date 컬럼에는 당연히 인덱스가 걸려 있었다. 나는 옵티마이저가ORDER BY created_date DESC를 보고 인덱스를 활용하여 정렬한 뒤, WHERE row_num BETWEEN 1 AND 5 조건을 만족하는 5개의 행만 찾으면 즉시 연산을 중단하고 결과를 반환할 것이라 생각했다.

하지만 옵티마이저는 이 기대를 저버렸다.실행 계획은 언제나 FULL TABLE SCAN이었다.

1.2 원인 추적: 삽질 기록

“왜 인덱스를 안타는거지?” 라는 의문에서 시작해서 다음과 같은 의심과 실험을 해봤다.

의심: 인덱스가 잘 못 되었는가?
- 조치: created_date에 대한 인덱스를 삭제하고 재생성, 복합 인덱스 등 다양한 경우의 수를 시도
- 결과: 실패, 여전히 FULL SCAN 발생
의심: 구버전이라서 그런것인가?
- 조치: 가장 최신 MariaDB 설치 후 똑같이 시도
- 결과: 실패, 여전히 FULL SCAN 발생
의심: 통계 정보가 최신화 안되었나?
- 조치: ANALYZE 명령어 수행하여 통계 정보 최신화
- 결과: 실패, 여전히 FULL SCAN 발생
의심: DDL이 세팅될때 문제가 발생했을까?
- 조치: DB 모두 삭제후 DDL부터 더미데이터까지 모두 다시 세팅
- 결과: 실패, 여전히 FULL SCAN 발생

수많은 시도에도 ROW_NUMBER()를 사용한 쿼리는 결코 인덱스를 사용하지 않았다. 이대로 둔다면 데이터가 적을때는 별문제가 없지만 천만, 일억, 백억이 쌓일 수 있는 audit_log 테이블이었기에 미래의 성능저하가 뻔한 이 실행걔획을 개발자로서 도저히 용납할 수 없었다.

1.3 (참고)MSSQL과의 흥미로운 차이점

혹시나 하는 마음에 동일한 쿼리를 MSSQL에서 실행해 보았다. 결과는 놀라웠다. 비교가 안 될 정도로 빨랐다. 실행 계획을 분석해 보니, MSSQL 옵티마이저가 SQL 구문을 내부적으로 TOP절로 자동 변환하여 최적화하고 있었다.

항목	MariaDB 10.2	MSSQL Server
실행 계획	FULL SCAN TABLE	TOP으로 자동 최적화
성능	3000ms+ (엄청 느림)	엄청 빠름
내부 처리	ROW_NUMBER() 그대로 처리	내부적으로 TOP으로 변환

MSSQL이 더 똑똑했다. MSSQL 옵티마이저는 ROW_NUMBER와 BETWEEN 조합을 TOP으로 똑똑하게 치환해 최적화를 실행한 것이다.

2. 원인: 왜 FULL SCAN이 발생했나

2.1 MariaDB 최적화의 한계

MSSQL과 다르게 MariaDB의 ROW_NUMBER()는 LIMIT 최적화가 없다. https://use-the-index-luke.com/sql/partial-results/window-functions 이 사이트를 참고하면 다음과 같은 그림이 있다. 위 사이트에 따르면 PostgreSQL, Oracle, SQL Server와 달리 MariaDB, MySQL은 윈도우함수의 최적화가 부족하다고 한다.

즉, WHERE row_num BETWEEN 1 AND 5 조건을 미리 반영하지 않고, 서브쿼리 안의 ROW_NUMBER() 함수를 테이블의 모든 행에 대해 먼저 실행한다. 이 과정에서 정렬같이 매우 큰 비용이 드는 작업을 수행한다. 전체 데이터에 순번을 매긴 후에야 비로소 바깥 쿼리에서 WHERE row_num BETWEEN 1 AND 5 조건을 필터링한다.

3. 해결 및 결과

3.1 LIMIT 적용

원인을 명확히 파악한 후, 팀에 상황을 공유했다. “하나의 표준 쿼리"라는 컨벤션도 중요하지만, 서비스의 성능을 포기할 수는 없었고, 이를 책임 개발자께 말씀드렸다.

결론적으로, DBMS에 따라 쿼리를 분기하기로 의사결정이 내려졌다.

[수정된 MariaDB 쿼리 예시]

1
2
3
4
SELECT *
FROM audit_log
ORDER BY created_date DESC
LIMIT 5;

[실행 계획(NULL 인 속성은 제외)]

id	select_type	table	type	key	key_len	rows
1	SIMPLE	audit_log	index	audit_log_create_date_index	5	5

3.2 10배의 성능 향상

수정된 쿼리는created_date인덱스를 정상적으로 사용했다. 그 결과, 3000ms를 넘던 API 응답 시간은평균 300ms 이하로 단축되었다. 10배 가까운 성능 개선을 이룬 것이다.

4. 배운점

4.1 옵티마이저는 만능이 아니다

“당연히 이렇게 동작하겠지"라는 예측은 금물이다. 특히 윈도우 함수처럼 복잡한 연산은 DBMS나 버전에 따라 동작 방식이 천차만별일 수 있다. 항상 EXPLAIN으로 실행 계획을 확인하는 습관이 중요한것 같다.

4.2 DBMS별 특성을 이해해야 한다

ANSI SQL 표준은 훌륭한 가이드라인이지만, 성능이 중요한 쿼리에서는 각 DBMS가 제공하는 네이티브 기능이 훨씬 효율적일 수 있다.

4.3 컨벤션 준수 vs 성능의 딜레마

유지보수를 위한 표준화와 실제 서비스 성능 사이에서 현명한 트레이드오프가 필요하다. 성능이 중요한 부분에서는 과감히 예외를 두는 유연성이 필요하다.

“사내 컨벤션을 지켜야 한다"는 생각에 갇혀 며칠을 고생했지만, 결국 기본으로 돌아가EXPLAIN을 찍어보고 DBMS의 동작 원리를 파고든 끝에 문제를 해결할 수 있었다. 이 경험을 통해 “왜?“라는 질문을 끊임없이 던지고, 당연하게 여겼던 것들도 직접 확인하는 개발자가 되어야겠다고 다시 한번 다짐했다.

앞으로는 “당연히 옵티마이저가 알아서 해주겠지"라는 생각보다, 실제로 실행 계획을 확인하고 성능 테스트를 철저히 해봐야겠다.

1. 문제 상황: 느린 쿼리 그리고 기대와 다른 실행계획#

1.1 문제의 쿼리#

1.2 원인 추적: 삽질 기록#

1.3 (참고)MSSQL과의 흥미로운 차이점#

2. 원인: 왜 FULL SCAN이 발생했나#

2.1 MariaDB 최적화의 한계#

3. 해결 및 결과#

3.1 LIMIT 적용#

3.2 10배의 성능 향상#

4. 배운점#

4.1 옵티마이저는 만능이 아니다#

4.2 DBMS별 특성을 이해해야 한다#

4.3 컨벤션 준수 vs 성능의 딜레마#

참고한 자료#