성능 최적화를 위한 반정규화 적용

문제 상황

썸네일을 이미지 테이블의 첫번째 값으로 설정하는 방식으로 인한 상품 검색 API 성능 문제

현재 상황

• Item 테이블과 ImageFile 테이블은 1 : N 관계
• Item 테이블과 Brand 테이블은 1 : 1 관계
• Item 테이블에 (deleted_date, item_name), (deleted_date, brand_id) 인덱스 사용중
• 데이터 갯수 (Item 데이터 총 25만개, Brand 데이터 총 3만개, ImageFile 데이터 총 총 43만개)

SELECT 
	i.item_uuid,
    i.item_name,
    i.price,
    MIN(img.ImageUrl),
    b.name
FROM Item AS i
JOIN 
    Brand b ON i.brand_id = b.brand_id
LEFT JOIN 
    ImageFile img ON img.item_id = i.item_id
WHERE
	i.deleted_date IS NULL
	AND b.name LIKE '%유한%'
GROUP BY
	i.item_uuid
LIMIT 0, 20
;

원인 분석

1. ImageFile 조인 시 다수 이미지 때문에 결과 행이 크게 늘어나고, GROUP BY로 인한 집계 비용이 커짐

2. Brand 테이블 name 컬럼 인덱스 설정되어있긴하나 %(와일드카드)가 좌측에 있는 경우는 인덱스가 적용되지 않음.

쿼리 시간 : 6.5초

실행 계획

---

첫번째 시도 : 서브 쿼리 사용

SELECT 
    i.item_uuid,
    i.item_name,
    i.price,
    img.min_url,
    b.name
FROM Item AS i
JOIN Brand b ON i.brand_id = b.brand_id
LEFT JOIN (
    SELECT item_id, MIN(ImageUrl) AS min_url
    FROM ImageFile
    GROUP BY item_id
) img ON img.item_id = i.item_id
WHERE
    i.deleted_date IS NULL
    AND b.name LIKE '%유한%'
LIMIT 0, 20;

쿼리 시간 : 2.1초

실행 계획

• Brand 테이블에서 name LIKE '%유한%' 조건이 큰 비용 발생 원인

  • 와일드카드 %가 앞에 붙어 인덱스가 완벽하게 활용되지 않아 26,553건 스캔 후 2,950건만 필터링됨

• Item 테이블은 brand_id와 deleted_date 조건을 인덱스로 잘 활용하고 있음

• ImageFile 테이블은 서브쿼리에서 임시 테이블 사용하며 item_id 인덱스를 통해 428,412건을 스캔함

  • 이로 인해 이미지 관련 조인 비용이 매우 큼

• 최종적으로 img 조인에서 114,989건 결과가 나오면서 비용 상승

• 임시 테이블(using_temporary_table: true)이 사용되고 있어 그룹핑 관련 작업에 비용이 발생함

---

두번째 시도 : 반정규화

• Item 테이블에 thumbnail 컬럼 추가
• 이미지의 첫 번째 URL을 별도로 저장하여 조인 및 그룹핑 비용 제거

SELECT 
    i.item_uuid,
    i.item_name,
    i.price,
    i.thumbnail,
    b.name
FROM Item AS i
JOIN Brand b ON i.brand_id = b.brand_id
WHERE
    i.deleted_date IS NULL
    AND b.name LIKE '%유한%'
LIMIT 0, 20;

쿼리 시간 : 0초

실행 계획

---

느낀점

• 다대다 혹은 일대다 관계에서 다수의 데이터를 집계하는 쿼리는 반드시 비용이 크기 때문에 반정규화 개선하는 방법에 대해 학습

• 임시 테이블과 그룹핑은 쿼리 비용 상승의 주요 원인임을 항상 염두에 두어야 하며, 가능한 미리 집계된 데이터를 활용하는 설계가 중요함