목록 조회 API 성능 개선기

추천 경매 목록 조회 API 조회 성능을 개선한 과정에 대해 정리해보고자 한다.

 

요구사항

경매와 상품이 1:1, 상품과 상품 이미지가 1:N으로 연결되어 있다.

경매 추천 목록 조회 API는 필터링과 정렬 수행 후 조건에 만족하는 경매 리스트를 반환한다.

• 필터링 조건: 경매 상태(진행중), 사용자 거주지
• 동적 정렬 조건: 북마크순, 입찰수순, 최신순, 경매 마감일 임박 순

 

문제

조회할 때마다 경매 테이블과 상품 테이블을 조인하여 성능이 저하되었다.

select ..
from auction a1_0
join product p1_0
     on p1_0.product_id = a1_0.product_id
where a1_0.auction_status = 'BIDDING'
  and a1_0.si = '서울시'
  and a1_0.gu = '관악구'
  and a1_0.dong = '봉천동'
order by a1_0.created_at desc
limit 10;

 

또한 각 상품의 상품 이미지를 조회하는 N+1 쿼리가 추가로 발생하였다.

select
   i1_0.product_id,
   i1_0.product_image_id,
   i1_0.created_at,
   i1_0.image_url,
   i1_0.updated_at 
from
   product_image i1_0 
where
   i1_0.product_id=?

해결 1: 검색용 테이블 추가

API 응답에 포함될 필드와 필터/정렬에 쓰이는 필드를 검색용 테이블에 저장하였다.

이제 매번 조인할 필요 없이 하나의 테이블에서 조회할 수 있다.

 

 

검색 테이블에는 필요한 최소한의 데이터만 저장하도록 설계하였다.

예를 들어, 경매 상태 대신 새 상품 여부만 저장하도록 했다.

또한 상품 이미지는 대표 이미지 URL만 저장하여 N+1문제도 함께 해결할 수 있다.

AuctionSearch.builder()
  .isNewProduct(auction.getStatus()== AuctionStatus.BIDDING)
  .imgUrl(product.getImages().get(0).toString())

 

 

경매 등록 시 실제 경매 테이블과 검색용 테이블에 동시에 데이터를 저장한다.

경매글은 수정 기능이 없으므로 삽입되는 값들은 이후에 변경되지 않는다.

 

정렬에 필요한 북마크 수, 입찰 수만 업데이트가 필요했다.

이에 3분마다 스케줄러로 집계 연산을 수행하여 경매 검색 테이블에 삽입해주었다.

UPDATE auction_search s
    JOIN (
        SELECT a.auction_id,
               COUNT(DISTINCT b.bookmark_id) AS bookmark_count,
               COUNT(DISTINCT bd.bidding_id) AS bidding_count
          FROM auction a
          LEFT JOIN bookmark b ON a.auction_id = b.auction_id
          LEFT JOIN bidding bd ON a.auction_id = bd.auction_id
         GROUP BY a.auction_id
    ) cnt ON s.auction_id = cnt.auction_id
SET s.bookmark_count = cnt.bookmark_count,
    s.bidding_count = cnt.bidding_count

 

결과

예전에는 경매와 상품 간의 nested loop join이 발생했는데, 지금은 하나의 테이블에서 바로 조회한다.

또한 상품 대표 이미지를 저장하여 상품 이미지를 매번 조회할 필요가 없기 때문에 N+1 문제도 사라졌다.

select ...
from auction_search as1_0
where as1_0.is_progress = true
  and as1_0.si = '서울시'
  and as1_0.gu = '관악구'
  and as1_0.dong = '봉천동'
order by as1_0.bidding_count desc
limit 10;

 

실행 계획을 시각화해 보면 아래와 같다.

참고로 API 조회 시 10개만 조회했다면, SQL 쿼리 실행 후 쿼리 플랜을 살펴볼 때는 100개를 조회하였다.

 

응답 시간을 비교해 보았을 때 10배 이상 개선된 걸 확인할 수 있었다.

 

해결 2: 복합 인덱스 생성

불필요한 조인은 없앴지만, full table scan이 발생했기에 인덱스를 적용하기로 했다.

 

정렬 조건별로 인덱스를 만들지, 정렬 조건을 제외하고 공통된 인덱스를 하나만 만들지 고민이 되었다.

인덱스가 많아질수록 관리하는 데 비용이 들기 때문이다.

 

경매, 상품 컬럼 값은 최초로 한 번만 삽입되고, 집계값만 3분마다 수정된다.

즉 조회용 테이블이므로, 삽입/수정으로 인한 성능 저하가 크게 발생하지 않을 것이라 생각했다.

 

각 정렬 조건에 대한 복합 인덱스를 추가했다.

si, gu, dong은 거주지에 대한 필터링 인덱스이고,

created_at, bidding_count, end_date는 정렬 인덱스이다.

create index idx_is_progress_location_created_at on 
auction_search (is_progress, si, gu, dong, created_at desc); 

create index idx_is_progress_location_bidding_cnt on 
auction_search (is_progress, si, gu, dong, bidding_count desc); 

create index idx_is_progress_location_bookmark_cnt on 
auction_search (is_progress, si, gu, dong, bookmark_count desc); 

create index idx_is_progress_location_end_date on 
auction_search (is_progress, si, gu, dong, end_date);

 

 

결과

옵티마이저가 복합 인덱스를 사용하여 full table scan에서 index scan으로 쿼리 플랜이 변경되었다.

 

인덱스 적용 전과 비교했을 때 정렬과 필터링 연산을 사용하지 않아 actual total time이 개선되었다.

 

 

인덱스 전후로 API 조회 성능이 653ms에서 249ms로 단축되었다.

 

 

마무리

조회용 테이블을 분리하고, 복합 인덱스 적용한 후 API 조회 성능이 11.7배 단축되었다. (2916ms->249ms)

쿼리를 작성에서 끝나는 게 아니라, 실행 계획으로 데이터베이스 조회 성능을 확인하는 게 중요함을 배웠다.