Post

할인권 이용 내역 조회 쿼리 성능 개선 정리

Posted
By younggyo-lee 15 min read

할인권 이용내역 조회 쿼리 성능 개선 정리

배경

할인권 이용내역에서 필터 등록된 이력을 제외하고 조회하는 API에서 특정 쿼리가 약 9.6초 이상 소요되는 문제가 있었다. 조회 조건은 특정 주차장, 스토어, 적용요청일시 범위, 할인 상태, 필터 제외 여부였고, 결과는 최신 이력순으로 20건만 가져오는 형태였다.

문제가 된 쿼리의 핵심 구조는 아래와 같다.

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 ?

실제 파라미터는 다음과 같은 형태였다.

1
2
3
4
5

parking_lot_id = 'KOR00056829'
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

실행계획

실행계획에서 가장 중요한 부분은 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

즉, 필터 테이블과의 anti-join 자체가 병목이라기보다는 discount_usage_history에서 조건에 맞는 후보 row를 효율적으로 좁히지 못하는 것이 핵심 문제였다.

원인

1. 파티션 키인 usage_day 조건이 없었다

discount_usage_history는 usage_day 기준으로 월별 파티셔닝되는 테이블이다.

1
2
3
4
5

partition-table:
  partitionTables[0]:
    table: discount_usage_history
    partitionTarget: true
    column: usage_day

하지만 기존 쿼리는 날짜 조건을 apply_request_date_time에만 걸고 있었다.

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'

이 조건은 비즈니스상 날짜 범위를 제한하지만, MySQL 파티션 프루닝에는 직접적인 도움을 주지 못한다. 파티션 기준 컬럼이 usage_day이므로 usage_day 조건이 없으면 조회 기간이 7일뿐이어도 여러 월 파티션을 훑을 수 있다.

실행계획에서도 실제 조회 기간은 2026년 6월인데 p202503부터 p202611까지 많은 파티션이 대상이 되었다.

2. ORDER BY id DESC LIMIT 20 때문에 PK 역방향 스캔이 발생했다

실행계획의 key: PRIMARY, Extra: Backward index scan은 MySQL이 최신 id 순서로 20건을 찾기 위해 PK를 뒤에서부터 읽고 있다는 의미다.

문제는 PK 순서로 읽은 뒤 아래 조건들을 후처리해야 한다는 점이다.

1
2
3
4

parking_lot_id = ?
store_key = ?
apply_request_date_time between ? and ?
discount_status <> ?

실행계획의 filtered: 0.08은 읽은 row 중 극히 일부만 최종 조건을 통과한다는 신호다. 즉, limit 20이 있어도 조건에 맞는 20건을 찾기 전까지 많은 row를 버리면서 읽을 수 있다.

3. 현재 인덱스가 이 조회 패턴과 맞지 않았다

DiscountUsageHistory 엔티티에는 아래 인덱스들이 정의되어 있다.

1
2
3
4
5

@Index(name = "IDX_PARKING_LOT_ID", columnList = "parking_lot_id"),
@Index(name = "IDX_DISCOUNT_APPLY_CHECK", columnList = "parking_lot_id, parking_history_id, discount_id"),
@Index(name = "IDX_CAR_NUMBER", columnList = "parking_lot_id, car_number"),
@Index(name = "IDX_FOUR_DIGIT_CAR_NUMBER", columnList = "parking_lot_id, four_digit_car_number"),
@Index(name = "IDX_DISCOUNT_NAME", columnList = "parking_lot_id, discount_name")

하지만 느린 쿼리의 주요 조건은 parking_lot_id, store_key, apply_request_date_time, discount_status, id desc다. 기존 인덱스는 store_key와 날짜 범위, 최신순 정렬을 함께 처리하기 어렵다.

개선 1: usage_day 범위 조건 추가

가장 먼저 적용할 개선은 applyDateFrom, applyDateTo를 yyyyMMdd 형태로 변환해서 usage_day 조건을 추가하는 것이다.

예를 들어 2026-06-04부터 2026-06-10까지 조회한다면 아래 조건이 추가되어야 한다.

1
2

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

QueryDSL 구현은 아래처럼 추가할 수 있다.

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

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 조건을 대체하는 것이 아니다. apply_request_date_time은 정확한 시간 범위 필터로 유지하고, usage_day는 파티션 프루닝을 위한 조건으로 함께 사용한다.

단, 이 전제는 usage_day가 apply_request_date_time의 날짜와 동일하게 저장된다는 것이다. 만약 두 값이 서로 다른 기준으로 저장될 수 있다면 데이터 생성 로직까지 함께 확인해야 한다.

개선 2: 조회 패턴에 맞는 복합 인덱스 추가

usage_day 조건으로 파티션 수를 줄인 뒤에도, 파티션 내부에서 조건에 맞는 row를 빠르게 찾아야 한다. 현재 쿼리는 특정 주차장과 특정 스토어의 최신 이력 20건을 찾는 패턴이므로 다음 인덱스를 우선 검토할 수 있다.

1
2
3

ALTER TABLE discount_usage_history
  ADD INDEX IDX_DUH_LOT_STORE_ID
  (parking_lot_id, store_key, id);

이 인덱스는 parking_lot_id, store_key로 범위를 좁힌 뒤 id 순서로 최신 데이터를 찾는 데 유리하다. 기본 정렬이 order by id desc limit 20인 현재 쿼리와 잘 맞는다.

날짜 범위가 넓거나 과거 임의 기간 조회가 많다면 아래 인덱스도 후보가 된다.

1
2
3

ALTER TABLE discount_usage_history
  ADD INDEX IDX_DUH_LOT_STORE_APPLY_DT_ID
  (parking_lot_id, store_key, apply_request_date_time, id);

다만 이 인덱스는 날짜 범위 필터에는 유리하지만 order by id desc와 완전히 일치하지 않을 수 있다. 날짜 기준 정렬이 가능하다면 아래처럼 정렬과 인덱스를 맞추는 방향도 검토할 수 있다.

1
2
3

ALTER TABLE discount_usage_history
  ADD INDEX IDX_DUH_LOT_STORE_APPLY_DT_ID_DESC
  (parking_lot_id, store_key, apply_request_date_time DESC, id DESC);

1

order by discountus0_.apply_request_date_time desc, discountus0_.id desc

개선 3: <> 조건을 명시적인 IN 조건으로 변경 검토

기존 조건은 아래와 같다.

1

and discountus0_.discount_status <> 'APPLY_READY'

<>는 같지 않다는 뜻이다. 현재 상태값이 APPLY_READY, APPLY_COMPLETE, APPLY_CANCEL 세 가지라면 아래처럼 명시적으로 허용할 상태를 적는 편이 옵티마이저와 읽는 사람 모두에게 더 명확할 수 있다.

1

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

상태 조건까지 복합 인덱스에 태우고 싶다면 다음 인덱스도 후보가 된다.

1
2
3

ALTER TABLE discount_usage_history
  ADD INDEX IDX_DUH_LOT_STORE_STATUS_APPLY_DT_ID
  (parking_lot_id, store_key, discount_status, apply_request_date_time, id);

다만 인덱스는 많을수록 쓰기 비용과 저장 공간이 증가한다. 운영에서는 실제 카디널리티, 조회 빈도, 실행계획을 보고 하나씩 추가하는 것이 안전하다.

개선 4: count 쿼리 비용 줄이기 검토

현재 findUnfilteredHistories()는 페이지 응답을 만들기 위해 count 쿼리를 먼저 실행하고, 이후 목록 쿼리를 한 번 더 실행한다.

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

Long total = queryFactory.select(history.count())
    .from(history)
    .leftJoin(historyFilter).on(joinCondition())
    .where(predicate)
    .fetchOne();

List<DiscountUsageHistory> content = queryFactory.selectFrom(history)
    .leftJoin(historyFilter).on(joinCondition())
    .where(predicate)
    .orderBy(sort(pageable))
    .offset(pageable.getOffset())
    .limit(pageable.getPageSize())
    .fetch();

정확한 전체 건수가 꼭 필요하지 않은 화면이라면 Page 대신 Slice 방식으로 바꾸는 것도 좋은 선택이다. pageSize + 1건만 조회해서 다음 페이지 존재 여부를 판단하면 count 쿼리를 제거할 수 있다.

운영 반영 시 주의점

운영 설정은 ddl-auto: validate이므로 엔티티에 @Index를 추가해도 운영 DB에 인덱스가 자동 생성되지 않는다.

1
2
3
4

spring:
  jpa:
    hibernate:
      ddl-auto: validate

따라서 운영 반영에는 반드시 별도 DDL이 필요하다.

1
2

SHOW INDEX FROM discount_usage_history;
SHOW INDEX FROM discount_usage_history_filter;

discount_usage_history_filter는 실행계획에서 이미 UIDX_HISTORY_FILTER를 잘 사용하고 있었다.

1
2
3

type: eq_ref
key: UIDX_HISTORY_FILTER
Extra: Using where; Not exists; Using index

따라서 우선순위는 필터 테이블 인덱스보다 discount_usage_history의 파티션 프루닝과 조회 인덱스 개선에 있다.

검증 방법

개선 전후로 EXPLAIN 또는 EXPLAIN ANALYZE를 비교한다.

1
2

EXPLAIN ANALYZE
select ...

확인할 포인트는 다음과 같다.

  1. partitions가 조회 기간에 맞게 줄어드는지 확인한다. 예를 들어 2026-06-04 ~ 2026-06-10 조회라면 p202606 중심으로 줄어야 한다.
  2. discount_usage_history의 key가 PRIMARY가 아니라 새 복합 인덱스로 변경되는지 확인한다.
  3. type이 index 전체 스캔 성격에서 range 또는 더 좁은 접근 방식으로 바뀌는지 확인한다.
  4. filtered 값이 개선되는지 확인한다.
  5. Extra에서 불필요한 Using filesort나 넓은 Backward index scan이 남는지 확인한다.
  6. 실제 응답 시간이 9초대에서 목표 시간대로 내려가는지 애플리케이션 로그와 DB 실행 시간으로 함께 확인한다.

기대 효과

이번 개선의 핵심은 두 단계다.

첫째, usage_day 조건을 추가해서 불필요한 월 파티션 접근을 줄인다.

둘째, 조회 패턴에 맞는 복합 인덱스를 추가해서 파티션 내부에서도 필요한 최신 row를 빠르게 찾도록 한다.

만약 응답 시간이 10초에서 500ms로 개선된다면 성능은 약 20배 좋아진 것이다.

1

10,000ms / 500ms = 20

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

정리

이번 쿼리는 left join ... is null 구조 자체가 느린 것이 아니라, 메인 테이블인 discount_usage_history에서 후보 row를 효율적으로 줄이지 못한 것이 핵심 병목이었다.

실행계획의 PRIMARY, Backward index scan, filtered: 0.08, 과도한 파티션 목록은 모두 같은 방향을 가리킨다. DB가 최신순으로 PK를 훑으며 많은 row를 버리고 있었고, 파티션 키 조건도 없어 조회 기간보다 훨씬 넓은 범위를 보고 있었다.

따라서 우선 적용할 개선은 다음 순서가 적절하다.

  1. usage_day 범위 조건 추가
  2. parking_lot_id, store_key, id 또는 날짜 조건을 포함한 복합 인덱스 추가
  3. discount_status <>를 IN 조건으로 변경 검토
  4. count 쿼리가 꼭 필요한지 검토
  5. EXPLAIN ANALYZE로 파티션, 인덱스, 실제 실행 시간을 재확인

작은 조건 하나가 파티션 프루닝을 살리고, 실행계획 전체를 바꿀 수 있다. 특히 파티션 테이블에서는 “비즈니스 날짜 조건”과 “파티션 키 조건”이 같은 의미처럼 보여도 DB 입장에서는 완전히 다르게 동작할 수 있다는 점을 꼭 확인해야 한다.

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