To your route, 투룻

개발부터 운영까지 폭넓은 기술 스택을 경험했습니다. Github Actions를 활용한 CI/CD 파이프라인 구축, 서버와 DB 다중화 구현, 모니터링 대시보드 개발, JMeter를 통한 성능 테스트 수행, 쿼리 최적화 등 다양한 기술적 도전과 트러블 슈팅을 수행했습니다.

협업 측면에서는 애자일을 실천했습니다. 데일리 스크럼으로 팀 진행 상황을 공유하고, 명확한 팀 컨벤션과 그라운드 룰을 수립하여 효율적인 협업 환경을 조성했습니다. 페어 프로그래밍과 코드 리뷰를 통해 코드 품질을 높이고, 테크 로그 작성으로 팀 내 지식 공유를 활성화했습니다. 스프린트 단위의 개발-피드백 사이클을 통해 지속적인 제품 개선을 이뤄냈습니다.

서비스 링크

https://www.touroot.kr/

깃허브 레포지토리

https://github.com/woowacourse-teams/2024-touroot

팀 구성

• BE 5명
• FE 3명

프로젝트 진행 내용

1. Query Optimization

문제 상황

• 100만 데이터 기준으로 필터링 및 좋아요 순 정렬 쿼리에서 1.445초로 성능 저하가 발생

  SELECT t.*
  FROM travelogue t JOIN travelogue_tag tt
      ON t.id = tt.travelogue_id
  WHERE tt.tag_id IN (?, ?)
  GROUP BY tt.travelogue_id
  HAVING COUNT(tt.id) = ?
  ORDER BY t.like_count DESC
  LIMIT ? OFFSET ?;
  

  • JOIN 후 GROUP BY를 수행하기 때문에 travelogue의 (like_count) 인덱스를 드라이빙 테이블로 삼아 정렬하더라도 GROUP BY로 인해 정렬이 깨짐
  • 따라서 travelogue_tag 를 드라이빙 테이블로 삼아 직접 정렬하지만, JOIN 후 GROUP BY를 수행하기 때문에 드라이빙 테이블의 크기를 충분히 줄이지 못해 성능 저하 발생

해결

1. GROUP BY와 JOIN의 순서를 바꿈

   SELECT t.*
   FROM travelogue t JOIN (
   		SELECT tt.travelogue_id
       FROM travelogue_tag tt
       WHERE tt.tag_id IN (?, ?)
       GROUP BY tt.travelogue_id
       HAVING COUNT(tt.id) = ?
   ) st ON t.id = st.travelogue_id
   ORDER BY t.like_count DESC
   LIMIT ? OFFSET ?;
   

   • travelogue의 (like_count) 인덱스를 드라이빙 테이블로 삼는 경우, JOIN 전에 GROUP BY를 수행하기 때문에 마지막까지 정렬된 상태가 유지됨. 즉 추가적인 정렬 작업이 필요 없음.
   • travelogue_tag를 드라이빙 테이블로 삼는 경우, GROUP BY와 HAVING 절을 먼저 적용해 드라이빙 테이블의 row 수를 크게 줄임.

2. 서브 쿼리에서 커버링 인덱스 적용

   SELECT tt.travelogue_id
   FROM travelogue_tag tt
   WHERE tt.tag_id IN (?, ?)
   GROUP BY tt.travelogue_id
   HAVING COUNT(tt.id) = ?)
   

   • (tag_id, travelogue_id) 로 복합 인덱스를 설정하여 커버링 인덱스를 적용함. 이를 통해 테이블 룩업이 발생하지 않음.
   • 기존 외래 키 인덱스인 (tag_id) 를 대체하기 때문에 인덱스로 인한 추가적인 쓰기 오버헤드가 적음
   • 서비스에서 여행기당 최대 3개의 태그만 가질 수 있기 때문에 JOIN도 최대 3번만 발생
   • 복합 인덱스의 두번째 컬럼인 travelogue_id로 GROUP BY를 수행하기 때문에 Using temporary 발생
     • 반대로 (travelogue_id, tag_id) 복합 인덱스를 활용하면 Using temporary는 발생하지 않지만, tag_id 에 대해 Index Skip Scan이 발생하여 성능이 더 저하됨

3. GROUP BY를 JOIN으로 변환

   SELECT tt1.travelogue_id
   FROM travelogue_tag tt1 JOIN travelogue_tag tt2
   				ON tt1.travelogue_id = tt2.travelogue_id
   WHERE tt1.tag_id = ? AND tt2.tag_id = ?;
   

   • GROUP BY를 제거하여 Using temporary가 발생하지 않음
   • (tag_id, travelogue_id) 복합 인덱스를 그대로 활용하여 Index Skip Scan이 발생하지 않음
   • 이후 travelogue와 JOIN 시 드리븐 테이블로 선정될 때 travelogue_id 로 정렬되어 있어 마치 인덱스처럼 동작함

• 최종 개선한 쿼리

SELECT t.* 
FROM travelogue t 
JOIN travelogue_tag tt1 
		ON t.id = tt1.travelogue_id AND tt1.tag_id = ?
JOIN travelogue_tag tt2 
		ON t.id = tt2.travelogue_id AND tt2.tag_id = ?
ORDER BY t.like_count DESC
LIMIT ? OFFSET ?;

• NL JOIN 에서 드라이빙 테이블의 크기에 따라 성능 차이가 발생
  • OFFSET이 작은 경우(offset 0)
    • travelogue 테이블의 like_count 인덱스를 드라이빙 테이블로 사용하여 정렬하면 매우 빠름 (약 2ms)
    • travelogue_tag 테이블을 드라이빙 테이블로 사용하면 using temporary, using filesort 로 정렬하기 때문에 느림 (약 140ms)
  • OFFSET이 큰 경우(offset 1000)
    • travelogue 테이블에서 인덱스를 활용해 탐색하는 범위가 커지므로 드라이빙 테이블이 커지고 성능이 저하됨 (약 700ms)
    • travelogue_tag 테이블을 드라이빙 테이블로 삼고 using temporary, using filesort 로 직접 정렬하는 것이 더 빠름 (약 140ms)
• 결론적으로 드라이빙 테이블 선정은 옵티마이저에게 맡기고, 쿼리만 개선하는 것으로 결정
  • 실제 환경에서는 OFFSET이 큰 조회가 드물기 때문에, 옵티마이저에게 드라이빙 테이블 선정과 성능 최적화를 맡기는 것이 적합하다고 판단
  • 만약 OFFSET이 큰 조회가 빈번하게 발생하고 성능 병목 현상이 발생한다면, STRAIGHT_JOIN을 사용하여 드라이빙 테이블을 명시적으로 지정 가능
  • using temporary, using filesort를 사용하는 쿼리도 기존 쿼리보다 드라이빙 테이블의 크기가 많이 줄어들었기 때문에 쿼리 성능이 준수함

관련 링크

• MySQL 쿼리 개선기 - 인덱스를 활용한 정렬보다 Using temporary, Using filesort가 더 빠르다고?

2. 동시성 문제 - 여행기 장소 중복 저장 문제 해결

문제 상황