♻️ 쿼리 최적화와 인덱스로 API 응답 속도 426배 개선하기

문제 

총대마켓 서비스의 메인페이지 필터링 및 검색 조회 API Latency가 100만 건 더미데이터 기준, 약 25초에 근접했습니다. 사용자가 메인페이지에서 30초간 아무런 데이터도 확인할 수 없었죠.

 

조회 성공 시 API Latency 약 23초

2024-10-10 20:35:23 [http-nio-8080-exec-4] INFO  c.z.c.l.config.LoggingInterceptor - LoggingInfoSuccessResponse[identifier=e472b64f-5b04-4ce4-ab08-0c96c43fdd45, memberIdentifier=Not Found, httpMethod=GET, uri=/read-only/offerings, requestBody=, statusCode=200, latency=23618ms]

 

조회 실패 시 Connection Error 발생

아래 에러는 다양한 상황에서 발생할 수 있으나, 클라이언트 혹은 서버의 timeout으로 인한 connection error로 추측하였습니다.

 

이전에, 쿼리 최적화 과정 없이 최소한의 리소스로 최적의 인덱스 걸기를 시도했습니다. 하지만 성능 개선은 적었습니다. 덕분에 한방쿼리는 인덱스를 거는 데 한계가 있다는 판단과, 인덱스가 잘 걸리는 쿼리가 중요하다는 판단을 했습니다. 소개드린 문제 상황과 더불어 이 깨달음을 계기로 쿼리 최적화를 추진하게 되었습니다. 인덱스를 고려하여 쿼리를 개선한 그 과정을 이 글에서 공유하고자 합니다. 저의 글을 읽으신 여러분은 쿼리를 작성하는 시점에 인덱스를 함께 고려하여 아래 과정을 겪지 않으시길 바랍니다.

 

 

상황 분석

상황을 분석해 위 문제가 발생한 원인을 파악해 봅시다.

 

1. 해당 API가 접근하는 Offering DB 테이블에 100만 건의 더미데이터가 존재합니다.

쿼리 성능 측정을 위해 개발 서버에 더미데이터를 삽입하였습니다.

 

2. 한방쿼리를 통해 테이블에 접근합니다.

한 번의 DB 접근으로 원하는 데이터를 조회하기 위해 아래와 같은 한방쿼리를 사용합니다. 옵티마이저의 실행계획 분석을 통해, 한방쿼리는 인덱스가 잘 걸리지 않음을 확인할 수 있었습니다.

SELECT o
FROM OfferingEntity o
WHERE (o.offeringStatus != 'CONFIRMED')
   AND (o.discountRate IS NOT NULL)
   AND (o.discountRate < :lastDiscountRate OR (o.discountRate = :lastDiscountRate AND o.id < :lastId))
   AND (:keyword IS NULL OR o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%)
ORDER BY o.discountRate DESC, o.id DESC

아무리 많은 인덱스를 걸어두어도 `is_deleted` 컬럼에 대해서만 인덱스가 걸림

 

3. 비효율적인 복합 인덱스가 존재합니다.

앞서 소개드렸듯, 쿼리 최적화 과정 없이 최소한의 리소스로 최적의 인덱스 걸기를 시도하여 아래와 같은 인덱스를 생성하였습니다. 인덱스를 아래와 같이 적용했던 이유는 노션 문서를 통해 확인하실 수 있습니다.

복합인덱스1 (meeting_date, is_deleted, offering_status)
복합인덱스2 (is_deleted, id, discount_rate)
복합인덱스3 (offering_status, title, meeting_address)

 

 

쿼리 최적화 주안점

쿼리를 리팩터링할 때 고려한 포인트는 아래와 같습니다. 쿼리를 어떻게 작성해야 인덱스에 잘 걸릴지를 중점으로 진행하였습니다.

• 조건절에서 OR로 묶여있는 구문 추출 및 분리
• 조건절의 NOT-EQUAL문 사용 쿼리 개선
• 커버링 인덱싱 가능 여부 고려

더 자세한 내용은 각 쿼리 예시와 함께 언급하겠습니다.

 

 

쿼리 최적화 과정

성능을 개선했던 쿼리 중, 최적화 과정이 잘 드러나는 몇가지 쿼리를 소개합니다.

JPQL 쿼리를 예시로 제시하며, 속도 측정은 API 응답 시간을 기준으로 측정하였습니다.

 

사례1

기존 쿼리

SELECT o
FROM OfferingEntity o
WHERE (o.offeringStatus = 'IMMINENT')
    AND (o.meetingDate > :lastMeetingDate OR (o.meetingDate = :lastMeetingDate AND o.id < :lastId))
    AND (:keyword IS NULL OR o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%)
ORDER BY o.meetingDate ASC, o.id DESC

개선 방향

1. OR 절을 기준으로 쿼리를 분리하였습니다.

각 조건이 OR로 묶여 있어 인덱스 타기가 어려운 형태입니다. title과 meetingAddress 컬럼은 인덱스를 활용했을 때 효과가 가장 클, 즉 카디널리티가 가장 높은 컬럼입니다. 따라서 두 컬럼이 인덱스의 득을 볼 수 있도록 하기 위해 OR 절을 기준으로 쿼리를 분리하였습니다.

개선 쿼리

OR 절을 기준으로 분리한 쿼리는 아래와 같습니다.

SELECT o
FROM OfferingEntity o
WHERE (o.meetingDate > :lastMeetingDate OR (o.meetingDate = :lastMeetingDate AND o.id < :lastId))
    AND (o.offeringStatus = 'IMMINENT')
ORDER BY o.meetingDate ASC, o.id DESC

 

 

SELECT o
FROM OfferingEntity o
WHERE (o.meetingAddress LIKE :keyword%)
    AND (o.offeringStatus = 'IMMINENT')
    AND (o.meetingDate > :lastMeetingDate OR (o.meetingDate = :lastMeetingDate AND o.id < :lastId))
ORDER BY o.meetingDate ASC, o.id DESC

 

 

SELECT o
FROM OfferingEntity o
WHERE (o.title LIKE :keyword%)
    AND (o.offeringStatus = 'IMMINENT')
    AND (o.meetingDate > :lastMeetingDate OR (o.meetingDate = :lastMeetingDate AND o.id < :lastId))
ORDER BY o.meetingDate ASC, o.id DESC

 

인덱스

복합인덱스1 (meeting_date, id desc)
복합인덱스2 (meeting_address, offering_status)
복합인덱스3 (title, offering_status)

 

1. 정렬 컬럼을 기준으로 복합인덱스1 을 적용했습니다.
2. 조건절의 첫번째와 두번째 컬럼을 기준으로 복합인덱스2 와 복합인덱스3 을 적용했습니다.

 

사례2

기존 쿼리

SELECT o
FROM OfferingEntity o
WHERE (o.offeringStatus != 'CONFIRMED')
   AND (o.discountRate IS NOT NULL)
   AND (o.discountRate < :lastDiscountRate OR (o.discountRate = :lastDiscountRate AND o.id < :lastId))
   AND (:keyword IS NULL OR o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%)
ORDER BY o.discountRate DESC, o.id DESC

개선 방향

1. OR 절을 기준으로 쿼리를 분리하였습니다.
2. NOT-EQUAL 명령어를 IN 명령어로 변경하였습니다.
3. IS NOT NULL 조건절을 제거하였습니다.

역시나 OR 절을 분리하였습니다. 추가로, NOT-EQUAL 조건의 경우 인덱스를 타지 못합니다. 따라서 여집합 개념을 활용해 != 절을 in 명령어로 변경하여 인덱스를 활용하도록 하였습니다. IS NOT NULL 조건의 경우 범위 탐색 로직에서 자동으로 필터링되기 때문에 불필요하여 제거하였습니다.

개선 쿼리

SELECT o
FROM OfferingEntity o
WHERE ((o.discountRate < :lastDiscountRate) or (o.discountRate = :lastDiscountRate AND o.id < :lastId))
    AND (o.offeringStatus IN ('AVAILABLE', 'FULL', 'IMMINENT'))
ORDER BY o.discountRate DESC, o.id DESC

 

 

SELECT o
FROM OfferingEntity o
WHERE (o.meetingAddress LIKE :keyword%)
   AND (o.offeringStatus IN ('AVAILABLE', 'FULL', 'IMMINENT'))
   AND ((o.discountRate < :lastDiscountRate) or (o.discountRate = :lastDiscountRate AND o.id < :lastId))
ORDER BY o.discountRate DESC, o.id DESC

 

 

SELECT o
FROM OfferingEntity o
WHERE (o.title LIKE :keyword%)
   AND (o.offeringStatus IN ('AVAILABLE', 'FULL', 'IMMINENT'))
   AND ((o.discountRate < :lastDiscountRate) or (o.discountRate = :lastDiscountRate AND o.id < :lastId))
ORDER BY o.discountRate DESC, o.id DESC

 

인덱스

 

복합인덱스1 (discount_rate)
복합인덱스2 (meeting_address, offering_status)
복합인덱스3 (title, offering_status)

1. 정렬 컬럼을 기준으로 복합인덱스1 을 적용했습니다.
2. 조건절의 첫번째와 두번째 컬럼을 기준으로 복합인덱스2 와 복합인덱스3 을 적용했습니다.

 

사례3

기존 쿼리

SELECT MAX(o.id)
FROM OfferingEntity o
WHERE is_deleted = false

개선 방향

1. 커버링 인덱스로 데이터를 조회합니다.

위 쿼리에 인덱스를 걸지 않을 경우 최대 id 하나만을 조회하는 쿼리임에도 풀 테이블 스캔을 진행합니다. is_deleted 단일 인덱스를 생성하는 경우 is_deleted와 해당하는 위치정보 id를 저장하는 인덱스 테이블이 생성됩니다. 따라서 커버링 인덱싱으로 성능을 개선할 수 있었습니다.

인덱스

단일인덱스 (is_deleted)

 

 

결과

최종 인덱스

create index idx_title_status on offering(title, offering_status);
create index idx_meetingAddress_status on offering(meeting_address, offering_status);
create index idx_discountRate on offering(discount_rate);
create index idx_meetingDate_idDesc on offering(meeting_date, id desc);
create index idx_isDeleted on offering(is_deleted);

 

쿼리 최적화 과정을 통해 생성한 최종적인 인덱스 리스트입니다. 인덱스 추가에 따라 발생하는 성능 저하 트레이드오프가 있기 때문에, 최소한의 인덱스로 최고의 효율을 낼 수 있도록 노력했습니다. '어떤 트레이드오프가 있을까?' 궁금하시다면 아래 글을 추천드립니다.

 

2024.11.07 - [우아한테크코스 6기] - 인덱스는 만능인가

인덱스는 만능인가

 

최종 성능 비교

개선 전

 

개선 후

 

 

 

결과적으로 100만 건의 더미데이터 기준, 약 426배 (1.8초 → 0.004초) API 응답 속도를 개선하였습니다.

 

알게 된 점

높은 성능 개선 지점

• 정렬을 위한 컬럼에 복합 인덱스 걸었을 때
• 검색을 위한 컬럼에 인덱스 걸었을 때
• 검색절에 묶여 있는 OR절을 분리했을 때

낮은 성능 개선 지점

• 페이징을 위한 OR절에 인덱스 걸었을 때

느낀 점

• 기본키가 있는 조건절은 기본키 인덱스에 걸리는 게 가장 성능이 좋다.
• 어떤 데이터가 파라미터로 들어오느냐에 따라 걸리는 인덱스가 다르다.

이후 고민 포인트

• 쿼리를 분리하면서 중복으로 작성되는 조건절이 하나둘씩 보인다. 동적 쿼리 생성 라이브러리를 도입해 보자.

 

참고

쿼리 최적화 팁: https://community.heartcount.io/ko/query-optimization-tips/