기본 검색

⇒ 상품 구매 정보를 저장한 purchase table에서 구매 날짜가 어제이며, 상품 별 총 구매수량이 가장 많은 20개의 상품을 조회한다.

초기 쿼리 테스트

인기 상품 단일 트래픽 처리 (38.828sec)

⇒ 한번 조회시 처리시간이 너무 많이 소요되어 TPS 측정 부적합하다.

select pd.product_id, pd.product_name, pd.price from purchase pr
left join products pd on pd.product_id = pr.product_product_id
where DATE_FORMAT(pr.create_at, '%Y%m%d') = '20230608'
group by pr.product_product_id
order by sum(pr.amount) DESC
limit 20;

쿼리 개선 1차 (purchase_date_index 적용)

문제 상황 : 기본적인 쿼리 성능의 처리 성능이 너무 안 좋다.

해결 방안 : where 조건절의 날짜비교를 위한 index를 생성해서 조회 성능을 높히려고 한다. 하지만 create_at은 년.월.일.시.분.초 의 데이터를 담고 있기 때문에 인덱스를 바로 적용하기 어렵기에 create_at을 년.월.일 형식으로 포맷한 purchase_date필드를 생성해서 해당 컬럼에 인덱스를 적용하기로 함.

인덱스 적용 후 쿼리 테스트

CREATE INDEX date_idx ON purchase (purchase_date)

select * from purchase pr
left join products pd on pd.product_id = pr.product_product_id
where pr.purchase_date = "20230517"
group by pr.product_product_id
order by sum(pr.amount) DESC
limit 20;

처리결과 24.625/sec

앞선 쿼리의 결과 보다 확실히 빨라졌지만 서버에 적용하기엔 여전히 너무 느리다.

쿼리 개선 2차 (Drive Table 간소화)

문제 상황: left join처리를 위해 mysql에서 nested loop join을 하게 되는데 구매 이력이 많은 날짜에 대한 쿼리 요청을 해본 결과 nl join이 너무 많이 발생해서 처리 성능이 저하 되는 상황.

해결 시도 : join의 횟수를 줄이기 위해서 drive 테이블인 purchase를 간소화한다.

⇒ 조건에 맞는 구매이력에 대한 products id를 먼저 서브쿼리로 구한 후 join해서 조회하는 방식을 시도