Post

9초짜리 쿼리 성능개선 (드디어 나도..)

Posted Updated
By younggyo-lee 11 min read

문제의 시작

여느때처럼 개발중에 스카우터의 xlog에 몇개씩 튀는 점들이 보였다. 해당 API는 할인권 이용내역 조회 API 였는데 특정 쿼리가 무려 9.6초 이상 걸린다는 것을 발견했다.

조건 자체는 아주 단순했다. 특정 주차장, 특정 스토어, 일주일치 날짜 범위, 할인 상태 필터 그리고 결과는 최신 순으로 딱 20건만 가져오는 요청이었다.

데이터 20개 뽑는 데 10초가 걸린다니, 뭔가 문제가 있어 보였다.

아래는 hibernate가 생성해준 쿼리 이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select
    discountus0_.*
from discount_usage_history discountus0_
left outer join discount_usage_history_filter discountus1_
    on discountus0_.id = discountus1_.history_id
   and discountus0_.usage_day = discountus1_.usage_day
   and discountus0_.store_key = discountus1_.store_key
where discountus0_.parking_lot_id = ?
  and discountus0_.apply_request_date_time >= ?
  and discountus0_.apply_request_date_time <= ?
  and discountus0_.discount_status <> ?
  and discountus0_.store_key = ?
  and discountus1_.id is null
order by discountus0_.id desc
limit ?

구조를 정리하면 이렇다. discount_usage_history(이용내역)에서 데이터를 가져오되, discount_usage_history_filter(필터 이력)에 등록된 건은 제외(is null)하는 anti-join 쿼리다. 조건에 맞는 최신 20건을 내림차순으로 가져온다. 파라미터는 대략 이런 값이 들어왔다.

1
2
3
4

apply_request_date_time = '2026-06-04 00:00:00.0' ~ '2026-06-10 23:59:59.0'
discount_status <> 'APPLY_READY'
store_key = '0PEF9D1RDB76N'
limit = 20

딱 일주일 범위에 20건이다. 왜 이게 9초나 걸리는 걸까 ㅜㅜ?

범인을 찾아보자(실행계획 분석)

EXPLAIN으로 실행계획을 확인했다.

메인 테이블 (discount_usage_history)

1
2
3
4
5
6
7

table: discountus0_
partitions: p202503,p202504,...,p202611
type: index
key: PRIMARY
rows: 2640
filtered: 0.08
Extra: Using where; Backward index scan

필터 테이블 (discount_usage_history_filter)

1
2
3
4
5

table: discountus1_
type: eq_ref
key: UIDX_HISTORY_FILTER
rows: 1
Extra: Using where; Not exists; Using index

필터 테이블 쪽은 문제가 없었다. UIDX_HISTORY_FILTER 인덱스를 잘 타고 있었고, 1건씩 빠르게 조회하고 있었다.

문제는 메인 테이블이었다.

눈에 띄는 숫자 두 개가 있다. rows: 2640과 filtered: 0.08. 이게 무슨 뜻이냐면, MySQL이 약 2,640개 row를 읽었는데 그중 최종 조건을 통과한 건 고작 0.08% 뿐이라는 이야기다. 1,000개를 읽어서 1개도 건지지 못하는 상황인 것이다.

그리고 Extra: Backward index scan은 MySQL이 PK를 뒤에서부터 거꾸로 읽고 있다는 뜻이다. ORDER BY id DESC를 처리하기 위해 최신 순서로 PK 전체를 훑으면서, 조건에 맞는 row를 하나하나 필터링하고 있었다.

LIMIT 20이 있어도 소용없다. 조건에 맞는 20건을 찾기 전까지 얼마든지 많은 row를 버리면서 읽어야 하기 때문이다.

문제 1: 파티션이 전혀 좁혀지지 않았다

discount_usage_history는 할인권 이용 내역을 관리하는 테이블로 usage_day 컬럼을 기준으로 월별로 파티셔닝된 테이블이다.

그런데 기존 쿼리에는 날짜 조건이 이렇게 걸려 있었다.

1
2

and discountus0_.apply_request_date_time >= '2026-06-04 00:00:00'
and discountus0_.apply_request_date_time <= '2026-06-10 23:59:59'

여기서 핵심은 파티션 기준 컬럼은 usage_day인데, 실제 쿼리에서 조건 검색으로 사용하는 컬럼은 apply_request_date_time이다.

MySQL은 파티션 프루닝을 할 때 파티션 키 컬럼(usage_day)에 걸린 조건을 본다. apply_request_date_time은 파티션 키가 아니기 때문에, 아무리 날짜 범위를 좁혀도 파티션 프루닝효과가 없다.

실행계획에서도 그 결과가 고스란히 드러났다. 조회 기간은 2026년 6월인데 p202503부터 p202611까지, 무려 수개월치 파티션을 전부 열어보고 있었다.

개선 : 파티션 프루닝을 살리자( usage_day 조건 추가)

가장 먼저, 그리고 가장 확실하게 효과를 낼 수 있는 개선은 usage_day 조건을 함께 걸어주는 것이다.

apply_request_date_time이 2026-06-04부터 2026-06-10이라면, 아래 조건을 함께 추가한다.

1
2

and discountus0_.usage_day >= '20260604'
and discountus0_.usage_day <= '20260610'

이렇게 하면 MySQL은 p202606 파티션만 들여다보면 되고, 나머지 파티션은 아예 건드리지 않는다.

한 가지 중요한 전제가 있다. usage_day가 apply_request_date_time의 날짜와 동일한 값으로 저장되어야 한다는 것이다. 만약 두 컬럼이 서로 다른 기준으로 채워진다면 데이터 생성 로직까지 함께 확인해야 한다. usage_day 조건을 추가했는데 데이터가 다르면 오히려 정상 데이터를 걸러낼 수 있기 때문이다.

코드로는 이렇게 추가한다.

1
2
3
4
5
6
7
8
9
10
11

private BooleanExpression hasUsageDateRange(SearchDiscountHistoryDTOV1 param) {
    LocalDateTime start = DateParse.searchStartDate(param.getApplyDateFrom());
    LocalDateTime end = DateParse.searchEndDate(param.getApplyDateTo());
    
    if (start == null || end == null) { return null; }

    String startUsageDay = start.toLocalDate().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
    String endUsageDay = end.toLocalDate().format(DateTimeFormatter.ofPattern("yyyyMMdd"));

    return history.usageDay.goe(startUsageDay).and(history.usageDay.loe(endUsageDay));
}

그리고 기본 검색 조건에 이 메서드를 연결한다.

1
2
3

builder.and(history.parkingLotId.eq(parkingLotId));
builder.and(hasUsageDateRange(param));
builder.and(hasApplyDateRange(param));

apply_request_date_time 조건을 대체하는 게 아니라는 점도 짚어두자. 정확한 시간 범위 필터는 기존 그대로 유지하고, usage_day는 파티션 프루닝을 위해 추가로 걸어주는 것이다.

개선 2: 드라마틱한 개선은 없지만 <> 보다 IN이 낫다

기존 할인 상태 조건은 이렇다.

1

and discountus0_.discount_status <> 'APPLY_READY'

상태값이 APPLY_READY, APPLY_COMPLETE, APPLY_CANCEL 세 가지라면, <> 'APPLY_READY'는 사실 APPLY_COMPLETE 또는 APPLY_CANCEL을 의미한다. 이걸 명시적으로 풀어쓰면 이렇다.

1

and discountus0_.discount_status in ('APPLY_COMPLETE', 'APPLY_CANCEL')

옵티마이저가 선택지를 명확히 알 수 있어서 인덱스를 더 잘 활용할 가능성이 높고, 코드를 읽는 사람도 “이 쿼리가 어떤 상태를 대상으로 하는구나”를 바로 파악할 수 있다.

개선이 효과 있는지 어떻게 확인하나

변경 전후로 EXPLAIN ANALYZE를 비교한다.

1
2

EXPLAIN ANALYZE
select ...

체크포인트는 다음과 같다.

  1. partitions: 조회 기간에 맞게 줄었는가? 2026-06-04 ~ 2026-06-10 조회라면 p202606 중심으로 좁혀져야 한다.
  2. filtered: 0.08에서 의미 있게 올라갔는가?
  3. 실제 응답 시간: 애플리케이션 로그와 DB 실행 시간 모두 확인한다.

얼마나 빨라질 수 있을까

가장 중요한 건 두 가지다.

  • usage_day 조건을 추가해서 불필요한 파티션 접근을 차단한다.
  • 조회 패턴에 맞는 복합 인덱스로 파티션 내부에서도 빠르게 row를 좁힌다.

두 가지가 함께 맞아들어가면, 10초짜리 응답이 500ms 안으로 들어오는 것도 충분히 가능하다.

1

10,000ms / 500ms = 20

처리 시간이 20분의 1로 줄고, 같은 시간에 처리할 수 있는 요청은 이론상 20배 가까이 늘어난다.

마무리

이번 개선의 핵심은 사실 단순했다. 파티션 키로 조건을 걸어야 파티션 프루닝이 된다. 당연한 말이지만, 실제로 쿼리를 짤 때는 놓치기 쉬운 부분이다.

apply_request_date_time으로 날짜 범위를 열심히 좁혀도, 파티션 키인 usage_day에 조건이 없으면 MySQL은 모든 파티션을 다 뒤진다. 파티션을 나눠놓은 의미가 없어지는 것이다.

쿼리 성능 문제가 생겼을 때 바로 인덱스부터 의심하게 되는데, 파티션 테이블이라면 파티션 프루닝이 제대로 동작하고 있는지를 먼저 확인하는 습관을 들이는 게 좋을 것 같다. EXPLAIN의 partitions 항목 하나만 봐도 금방 알 수 있다.

덕분에 파티션 프루닝에 대해 제대로 공부한 계기가 됐다.

database
java jpa Hibernate spring spring data jpa database mysql
This post is licensed under CC BY 4.0 by the author.