Query Optimization (1)

쿼리 최적화

DB 시스템이나 인덱스, 쿼리 등 성능에 영향을 주는 요소들이 많을 것으로 생각됨. DB 시스템 튜닝을 통해 성능을 개선하는 것은 DBA의 업무겠지만, 성능을 저하시키지 않는 좋은 쿼리를 작성하는 것은 개발자의 업무인 것 같다.

WATCHA 기술 블로그: 쿼리 최적화: 빠른 쿼리를 위한 7가지 체크리스트 ✅

1. SELECT시 필요한 최소한의 컬럼만을 가져오기
   게시글 리스트를 가져오는 요청에서 post.content 등 불필요한 컬럼을 가져오지 않는게 좋음. 리스트에서 사용할 컬럼만을 가져오기.

2. 조건 연산시 Index 값을 사용할 수 있도록 기존 컬럼 값에 별도의 연산 걸지 않기
   위치 필터링을 위해 위도, 경도를 사용해 거리 계산을 하게 되면 컬럼 값을 그대로 사용하는 것이 아니라 제곱 등의 계산이 들어가게 됨. 가능하다면 컬럼 값에 연산이 들어가지 않도록 고민해보기.

3. LIKE 사용시 문자열 앞부분에는 % 넣지 않기
   검색어 필터링시 %검색어%와 같이 검색하고 있는데, 개선할 수 있는 방법이 있을까?

4. 중복 값 제거 연산 사용 지양하기 (SELECT DISTINCT, UNION DISTINCT 등)
   중복 값 제거 연산의 Cost가 큼. 중복 제거 연산이 필요하다면 EXISTS 활용 등 대체 방법을 고민해볼 것.

5. GROUP BY시 WHERE로 해결할 수 있는 경우라면 HAVING 사용하지 않기
   WHERE를 통해 데이터 크기를 줄여놓으면 GROUP BY 연산에 도움이 됨.

6. 3개 이상의 테이블 JOIN시 가장 큰 테이블을 FROM에 배치하고 나머지는 크기가 작은 순으로 JOIN
   쿼리 플래닝 시간을 줄여줄 수 있으나, 항상 효과가 있는 것은 아님.

7. 자주 사용하는 데이터는 미리 전처리된 테이블에 저장해두고 사용할 수 있음
   RDBMS의 원칙에는 어긋날 수 있는 방법임.

게시글 리스트를 가져오는 쿼리 개선

상황

1. post, category, participant 테이블이 필요
2. 카테고리의 이름을 반환해야함 (category.name)
3. 게시글마다 참여자의 수를 반환해야함 (participant에서 postId로 검색시의 count)

기존 방법

우선 post와 category 테이블만을 조인해 리스트를 가져온다음, 리스트를 순회하며 게시글 각각 participant 테이블에서 검색하고 있음

const posts = await postService.getPosts(req.query as getPostsOption);
const result = await Promise.all(
  posts.map(async (post: any) => {
    const [participant, participantCnt] =
      await participantService.getParticipantNum(post.id);
    post.participantCnt = participantCnt;
    return post;
  })
);

LIMIT이 20인경우, API 요청이 들어올때마다 DB에서는 21번의 I/O가 발생하여 속도 저하 우려가 있음

개선한 방법

GROUP BY를 활용, 하나의 쿼리를 통해 참여자 수를 포함한 게시글 리스트를 얻을 수 있음

select post.id, title, capacity, deadline, c.name as category, count(p.userId) as joined
from post
left join category c
on post.categoryId = c.id
left join participant p
on post.id = p.postId
group by id
limit 20;

실행 결과

기존 방법	개선한 방법
method1: 445.767ms method1: 133.723ms method1: 151.245ms method1: 138.503ms method1: 152.716ms	method2: 355.008ms method2: 62.561ms method2: 69.565ms method2: 70.726ms method2: 70.667ms
평균 204.3908ms	평균 125.7054ms