쿼리 최적화와 인덱스로 API Latency 30배 개선하기

문제 

총대마켓 서비스의 메인페이지 필터링 및 검색 조회 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만 건의 더미데이터가 존재합니다.

쿼리 성능 측정을 위해 개발 서버에 더미데이터를 삽입해 둔 상황입니다. 전체 테이블 기준으로는 약 1000만 건의 데이터가 존재합니다.

 

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 쿼리를 예시로 제시하며, 속도 측정은 postman을 활용하였습니다.

 

쿼리1. 검색어 유무에 따른 최근 공모 조회

기존 쿼리

검색어가 존재하는 경우 검색 진행, 존재하지 않는 경우 검색을 진행하지 않고 cursor 방식으로 페이징하는 쿼리입니다.

SELECT o
FROM OfferingEntity o
WHERE o.id < :lastId
    AND (:keyword IS NULL OR o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%)
ORDER BY o.id DESC

개선

1. 검색어가 존재할 때 사용하는 쿼리와 존재하지 않을 때 사용하는 쿼리를 분리하였습니다.

검색어 유무를 판단하는 로직 ":keyword IS NULL"은 데이터 접근 로직이 아닌 비즈니스 로직의 책임이라고 생각했습니다. 또한, 다른 쿼리에서 해당 로직이 원인이 되어 인덱스가 걸리지 않는 현상이 발생했습니다. 따라서 해당 로직은 쿼리 내부가 아닌 쿼리를 호출하는 외부에서 처리하도록 하고, 검색어 존재 여부에 따라 쿼리를 분리하였습니다.

개선 쿼리 및 인덱스

- 검색어 부재 시

SELECT o
FROM OfferingEntity o
WHERE o.id < :lastId
ORDER BY o.id DESC

 

- 검색어 존재 시

SELECT o
FROM OfferingEntity o
WHERE o.id < :lastId
    AND (o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%)
ORDER BY o.id DESC

 

- 인덱스

해당 쿼리는 기본키인 id를 범위 탐색하는 로직이 존재하며, id를 기준으로 정렬합니다. 따라서 추가 인덱스 없이도 기본키 인덱스를 통해 충분한 성능 개선이 존재했습니다.

 

성능 측정

기본키 인덱스로 이미 충분한 성능을 가지므로, 쿼리 개선을 통한 성능 향상은 미미했습니다.

• 검색어 부재 시 약 0.02초

URI: /offerings?filter=RECENT&last-id=90000&page-size=30
LoggingInfoSuccessResponse[identifier=e5004707-d497-4762-a838-fc955ca7c847, memberIdentifier={"sub":"12","exp":1728714372}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=25ms]

• 검색어 존재 시 약 0.1초

URI: /offerings?filter=RECENT&last-id=90000&page-size=30&search=대전
LoggingInfoSuccessResponse[identifier=4df848b5-6c5e-4ad8-9a61-603bdfd18f42, memberIdentifier={"sub":"12","exp":1728714372}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=120ms]

 

쿼리2. 검색어 유무에 따른 `마감임박` 상태의 공모 조회

기존 쿼리

검색어가 존재하는 경우 검색 진행, 존재하지 않는 경우 검색을 진행하지 않고 `마감임박` 상태의 공모를 조회하여 cursor 방식으로 페이징하는 쿼리입니다.

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. 검색어가 존재할 때 사용하는 쿼리와 존재하지 않을 때 사용하는 쿼리를 분리하였습니다.
2. 검색어 범주에 따라 쿼리를 분리하였습니다.

검색절 조건 ":keyword IS NULL OR o.title LIKE :keyword% OR o.meetingAddress LIKE :keyword%" 이 OR로 묶여 있어 인덱스 타기가 어려운 형태입니다. title과 meetingAddress 컬럼은 인덱스를 활용했을 때 효과가 가장 클, 즉 카디널리티가 가장 높은 컬럼입니다. 따라서 두 컬럼이 인덱스의 득을 볼 수 있도록 하기 위해 검색어 유무를 비교하는 로직과 범주별 검색하는 로직을 분리했습니다.

개선 쿼리 및 인덱스

- 검색어 부재 시

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)

 

성능 측정

API Latency 기준 약 10배의 성능 개선이 존재했습니다.

 

개선 전

• 검색어 부재 시 약 1.1초

URI: /offerings?filter=IMMINENT&last-id=90000&page-size=30
LoggingInfoSuccessResponse[identifier=8af81b32-07d5-4c33-864c-e90ef0cb3087, memberIdentifier={"sub":"1001","exp":1728716240}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=1136ms]

• 검색어 존재 시 약 1.1초 ~ 5.2초

URI: /offerings?filter=IMMINENT&last-id=90000&page-size=30&search=대전
LoggingInfoSuccessResponse[identifier=3a8963bc-1519-4fc3-b13f-01b3be42e995, memberIdentifier={"sub":"1001","exp":1728716240}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=1130ms]

 

개선 후

• 검색어 부재 시 약 0.1초

URI: /offerings?filter=IMMINENT&last-id=90000&page-size=30
LoggingInfoSuccessResponse[identifier=32b8594b-0919-449a-a462-e5b886a235dd, memberIdentifier={"sub":"1001","exp":1728716240}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=141ms]

• 검색어 존재 시 약 0.01초

URI: /offerings?filter=IMMINENT&last-id=90000&page-size=30&search=대전
LoggingInfoSuccessResponse[identifier=b6aefd7e-85ff-4560-b23f-9c68843da10e, memberIdentifier={"sub":"1001","exp":1728728970}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=14ms]

 

쿼리3. 검색어 유무에 따른 `높은할인율순` 공모 조회

기존 쿼리

검색어가 존재하는 경우 검색 진행, 존재하지 않는 경우 검색을 진행하지 않고 `높은할인율순` 으로 공모를 조회하여 cursor 방식으로 페이징하는 쿼리입니다.

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. 검색어가 존재할 때 사용하는 쿼리와 존재하지 않을 때 사용하는 쿼리를 분리하였습니다.
2. 검색어 범주에 따라 쿼리를 분리하였습니다.
3. NOT-EQUAL 조건인 != 절을 in 명령어로 변경하였습니다.
4. IS NOT NULL 조건절을 제거하였습니다.

두 번째 개선했던 쿼리와 마찬가지로 검색 로직을 분리하였습니다. 추가로, 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, id desc)
복합인덱스2 (meeting_address, offering_status)
복합인덱스3 (title, offering_status)

 

성능 측정

API Latency 기준 약 100배의 성능 개선이 존재했습니다.

 

개선 전

• 검색어 부재 시 약 6초

LoggingInfoSuccessResponse[identifier=8d1b3878-044d-4522-8646-25f659d81700, memberIdentifier={"sub":"1001","exp":1728728970}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=6526ms]

 

개선 후

• 검색어 부재 시 약 0.05초

LoggingInfoSuccessResponse[identifier=eaf05add-84e9-41a1-a5d2-698dc0555826, memberIdentifier={"sub":"1001","exp":1728730815}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=57ms]

• 검색어 존재 시 조회된 데이터 없는 경우 약 0.01초

LoggingInfoSuccessResponse[identifier=23444891-df54-4c28-adc0-82953514ce5d, memberIdentifier={"sub":"1001","exp":1728730815}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=13ms]

• 검색어 존재 시 조회된 데이터 있는 경우 약 0.03초

LoggingInfoSuccessResponse[identifier=c0a193c1-1472-4bda-8a20-14750f0085fe, memberIdentifier={"sub":"1001","exp":1728730815}, httpMethod=GET, uri=/offerings, requestBody=, statusCode=200, latency=36ms]

 

쿼리4. 기본키 최댓값 조회

기존 쿼리

모든 삭제되지 않은 공모 중 기본키 id의 최댓값을 조회하는 쿼리입니다.

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

개선

1. is_deleted 단일 인덱스를 걸어 커버링 인덱스로 데이터를 조회합니다.

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

개선 쿼리 및 인덱스

- 인덱스

단일인덱스 (is_deleted)

 

성능 측정

API Latency 기준 약 130배의 성능 개선이 존재했습니다.

 

개선 전

약 2초

 

개선 후

약 0.01초

 

 

결과

최종 인덱스

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_id on offering(discount_rate, id);
create index idx_meetingDate_id_desc on offering(meeting_date, id desc);
create index idx_isDeleted on offering(is_deleted);

 

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

 

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

인덱스는 만능인가

 

최종 성능 비교

 

결과적으로 100만 건의 더미데이터 기준, 평균 25초에서 평균 0.9초로 약 30배의 API latency를 개선하였습니다.

 

 

알게 된 점

높은 성능 개선 지점

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

낮은 성능 개선 지점

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

느낀 점

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

이후 고민 포인트

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

 

참고

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