[MYSQL] 리스트 조회 쿼리개선 - 요청속도를 4분의 1로 줄인 건에 대하여..

🤷‍♂️ 문제상황

리스트 조회시에 API 응답시간이 4초가량 소요되는것이 확인되었다.

페이지를 넘길때마다 4초가 걸린다는 점이 꽤 타격이 컸다.

개발시에 테스트 데이터 몇백개정도로 확인했을때는 알아차리기 힘들었는데, 운영데이터는 20만건 이상이 사용되다보니 이러한 현상이 발생했다.

나의 경우에 파악되는 문제상황은 다음과 같다.

1. 데이터가 너무 많음 => 이건 사실상 해결할 수 없는 문제다.

2. Join하는 테이블이 너무 많음

3. 페이지네이션 구현을 위해 total갯수를 확인하는 로직이 따로 분리되어있음 (동일쿼리를 2번 조회하게된다.)

💁‍♀️1트) JOIN 순서 변경

프로젝트 특성상 Join 테이블이 7,8개 정도로 굉장히 많았는데, 모두 조회에 필요한 데이터다보니 직접적으로 Join을 건드리기는 어려웠다.

이리저리 수정을 시도하다가 Join 순서를 바꿔주었다.

SELECT * # 필요한 필드만 뽑아서 쓴다
FROM 테이블1
Join 테이블2 ON 테이블2.두번째Idx = 테이블1.두번째Idx
Join 테이블3 ON 테이블3.세번째Idx = 테이블1.두번째Idx

↓

SELECT * # 필요한 필드만 뽑아서 쓴다
FROM 테이블1
Join 테이블3 ON 테이블3.세번째Idx = 테이블1.두번째Idx
Join 테이블2 ON 테이블2.두번째Idx = 테이블1.두번째Idx

생각치 못했는데 순수 쿼리 속도로 0.1초가량 차이가 날정도로 차이가 컸다.

각 테이블의 특성을 파악하고 결과값에 영향이 큰 테이블부터 Join시키면 크게 영향을 줄 수 있을것같다.

💁‍♀️2트) with 구문 활용

쿼리를 이것저것 확인하던중, with 구문을 사용한 예시가 눈에 띄었다.

with list AS ( 
    SELECT *
    FROM 테이블1
    # 이것저것필요한쿼리들
)
SELECT list.*,
    (SELECT COUNT(*) FROM list) AS totalCount
FROM list
LIMIT 10 OFFSET 0;

이런식으로 테이블을 먼저 조회하고 갯수만 카운트하면, 전체 테이블을 조회하는 횟수는 1번이므로 획기적으로 줄어들지 않을까 싶었다.

하지만 count, list 각각 조회시에 약 400ms, with문 사용시에 약 700ms로, 생각과는 다르게 테이블은 2회 조회되는것같았다.

🙆‍♂️해결 ) Primary Key 활용

이 방법은 Prisma 쿼리를 참고하다가 나온 부분인데,
Prisma ORM에서는 테이블 연관관계를 스키마로 엄격하게 관리한다.

JOIN 테이블을 조회할때도 한번에 조회하지 않고, 첫번째로 조회된 테이터와 연관된 데이터만 조회를 하는 형식이다.

# 쿼리를 보면 이런느낌..

tx.메인테이블.findFirst({
  select : {
    ..메인테이블의 머시기 데이터들,
    서브테이블 : {
      select : {
        ..서브테이블의 머시기 데이터들,
      }
    }
  }
})

그래서 첫조회때 검색할 데이터의 Prismary Key와 total갯수만 간단하게 가져오고,

실제 데이터를 가져올때는 잡다한 쿼리들을 제외하고 데이터만 가져오는 방식을 고안해보았다.

코드는 다음과 같다.

## 조회1. 키값과 total 갯수 조회
WITH getCount AS (
    SELECT count(*) OVER () AS totalCount,
        메인테이블.메인pmk AS primary
    FROM 메인테이블
    ## JOIN 테이블들
    ## 온갖 WHERE AND문
    ## ORDER BY도 챙겨주자.
)
SELECT getCount.*
FROM getCount
LIMIT 10 OFFSET 0

## 조회2. 키값으로 필요한 데이터 조회
SELECT 메인테이블에서필요한필드들
FROM 메인테이블
## JOIN 테이블들
WHERE 메인pmk in ({조회한 pmk값들})
## ORDER BY도 챙겨주자.

개선사항
첫번째 조회의 JOIN 테이블에서도 WHERE문에 필요한 테이블이 아닌것들을 제외시키면 성능을 좀더 높일 수 있다.

서비스로직에서 처리해주어야할일이 조금 늘었지만 20만개 데이터를 감당하려면..어쩔수없다.

Primary key의 이름을 고정하고 이 키값 처리로직을 공통유틸로 빼면 귀찮음을 조금 덜수 있지 않을까 싶다.

🧚‍♀️결과

개선사항까지 적용한 쿼리 (total 갯수가 다른건 그새 데이터가 추가돼서 그렇고, 결과는 같았다.)

POST MAN으로 테스트 해보았을때, 단일 API의 동일결과로 1/4로 빠른 속도를 확인할 수 있었다.

성능은 확실히 늘렸지만, 로직이 복잡한 코드는 유지보수할때 사이드이펙트 폭격으로 버거울수 있기때문에

ORM을 사용할 수 있는 환경이라면 ORM을 쓰는것이 더 효율적일수도 있겠다 (...)

카페인부족