Pagination 쿼리, 속도가 너무 느려요 !!!

 

개요

최근 프로젝트에서 대용량 테이블 조회 API의 성능 문제를 마주하게 되었습니다. 조회 대상은 약 50만 건에 달하는 데이터였고, 단순한 조회에도 6~7초 정도 걸려렸습니다. 더군다나 FE쪽에서도 Server Side Rendering 방식을 쓰고 있었어서, 이 API 때문에 페이지 자체 랜더링이 지연되는 문제가 생겨버렸습니다,,

---

문제 상황

문제의 쿼리는 소속의 랭킹을 가져오는 API의 쿼리입니다.

특정 소속에서 진행한 프로젝트의 개수가 많을수록 랭킹이 올라가기 때문에 해당 소속에서 진행한 프로젝트의 개수를 가져오는 것이 조회 API 요구 사항중 하나였습니다. (단, ranking은 따로 계산합니다.)

문제가 되는 쿼리는 다음과 같습니다.

select
    be1_0.belonging_id,
    be1_0.belonging_type,
    be1_0.name,
    be1_0.current_ranking,
    be1_0.prev_ranking,
    count(distinct pe1_0.project_id) # 프로젝트 개수 count
from
    belonging be1_0
left join                    # 소속의 속한 사람 join
    tb_member me1_0
	on me1_0.belonging_id=be1_0.belonging_id
left join
    participant pe1_0        # 소속의 속한 사람이 참여한 project join
    on pe1_0.member_id=me1_0.member_id
where
    be1_0.belonging_type=?
group by
    be1_0.belonging_id
order by
    case
        when (be1_0.current_ranking) is null
            then 1
        else 0
        end,
    be1_0.current_ranking,
    be1_0.name
limit
    0, 20

 

 

---

문제 원인 분석

인덱스 미적용

위 데이터를 쿼리하기 위해 Join도 진행하고 WHERE, ORDER BY를 사용했지만 이를 고려한 인덱스 적용은 전혀 적용되지 않았습니다.

그에 따라 테이블 풀스캔이 일어나고 있었고 JOIN 조건에 사용되는 컬럼들이 인덱스가 없어 모든 row를 스캔하고 있었습니다.

 

 

대량 데이터 조인 시 row 수 폭발 (N:N:N 관계)

우선 소속 데이터는 중학교, 고등학교, 기업들에 대한 데이터가 저장되어 있으므로 데이터가 꽤 많은 상태였습니다. (기업 데이터만 50만건)

거기에다가 해당 소속에 속한 멤버를 left join하고, 해당 멤버가 참여한 프로젝트들에 대한 데이터를 left join 하고 있습니다.

이는 row수가 기하급수적으로 늘어날 수 있는 문제를 초래할 수 있습니다.

 

---

해결 방안 적용

인덱스 추가

• 쿼리 최적화를 위해 다음 복합 인덱스와 조인키 인덱스를 생성했습니다.

CREATE index belonging_ranking_index ON belonging (current_ranking, name, belonging_type);

CREATE INDEX idx_tb_member_belonging_id ON tb_member(belonging_id);
CREATE INDEX idx_participant_member_id ON participant(member_id);

• 이 인덱스를 통해 WHERE belonging_type = ? 필터링과 ORDER BY current_ranking, name 정렬을 동시에 커버치는 것을 노렸습니다.
• 조인키 에 대한 인덱스를 적용하므로써 모든 row를 스캔하는 것을 막고자 했습니다.

 

ID 선 조회 후 JOIN 전략 적용

• 먼저 조건에 맞는 belonging_id만 빠르게 조회하였습니다. (페이징 적용을 위해)

SELECT belonging_id
FROM belonging
WHERE belonging_type = ?
ORDER BY current_ranking, name
LIMIT ?, ?

• 이후 해당 ID들을 기반으로 필요한 Join을 수행하여 데이터 조회하였습니다.

SELECT ...
FROM belonging
LEFT JOIN tb_member ON ...
LEFT JOIN participant ON ...
LEFT JOIN project ON ...
WHERE belonging_id IN (?, ?, ?, ...)
GROUP BY belonging_id

• 조인 대상 row 수를 대폭 줄여 성능 개선을 노렸습니다.

 

Page ➞ Slice로 변경

• Page 대신 Slice를 사용하여 totalElements 계산을 제거했습니다.
• 이에 따라 select count(*) 쿼리를 없애고, 단순 조회 + next 존재 여부만 판단하도록 변경했습니다.
• SliceImpl을 사용해 hasNext 플래그만 반환하고 무한 스크롤에는 영향이 없도록 하였습니다.

---

성능 체크

쿼리 개선 전 성능을 살펴보겠습니다.

 

개선 전 쿼리 성능

 

 

쿼리에 explain을 붙혀 실행해보니 위와 같은 결과가 나왔습니다.

 

be1_0, me1_0, pe1_0 에 대해서 테이블 풀스캔(type = ALL)이 일어나고 있었고, be1_0 에 대해서는 인덱스를 통한 정렬이 아닌 별도로 정렬(Extra = Using filesort)이 일어나고 있었습니다.

 

테이블 풀스캔을 피하기 위해 다음과 같은 조치를 취해보겠습니다. 

• be1_0의 where절과 order by 절을 커버할 수 있는 복합 인덱스 추가
• me1_0, pe1_0의 left join에서 사용하는 조인키를 인덱스로 추가 

 

인덱스 추가 후

추가한 인덱스는 다음과 같습니다.

CREATE index belonging_ranking_index ON belonging (current_ranking, name, belonging_type);

CREATE INDEX idx_tb_member_belonging_id ON tb_member(belonging_id);
CREATE INDEX idx_participant_member_id ON participant(member_id);

 

쿼리 결과는 다음과 같습니다.

 

 

쿼리 속도도 반토막되고 type이 range(조건으로 인덱스 일부만 스캔), ref 인덱스를 사용해서 매칭되는 row를 탐색) 로 바뀐 것 보니 인덱스도 잘 적용된 모습을 보입니다.

 

be1_0에 대한 filesort 는 여전히 일어나고 있습니다. 이건 order by의 case 을 통한 null 에 대한 처리 때문에 그런것 같습니다.

 

 

ID 선 조회 후 JOIN 전략 적용

다음은 쿼리를 page 처리를 위해 먼저 조건에 맞는 belonging_id만 빠르게 조회하는 방식을 적용한 후 성능 체크를 진행해 보았습니다.

 

 

 

 

쿼리가 두개로 쪼개졌지만 둘의 실행 시간을 합쳐도 이전 단일 쿼리보다 훨씬 빠른 모습을 보입니다.

3.530초 -> 0.766초

 

 

Page ➞ Slice로 변경

다음은 page에서 slice 형태로 응답값을 바꾸면서 page의 total element 계산 쿼리를 제거하여 쿼리 시간을 단축하고자 했습니다.

 

slice 방식은, 예를 들어, page size가 20이라면 20 + 1개를 조회해서 실제 조회된 row가 21개가 된다면 다음 페이지가 있다고 판단하고 hasNext flag 값을 true로 반환하는 방식으로 적용했습니다.

 

따라서 total element 계산 쿼리를 제거하였다고 봐도 무방합니다.

제거된 total element 계산 쿼리는 다음과 같습니다.

 

사실 위에 인덱스 적용, 쿼리 쪼개기 방식에서 언급은 안했지만 total element를 계산하기 위해 위 쿼리가 추가로 수행되고 있었습니다.

위 쿼리를 제거한다면 0.193초 정도 단축하는 효과를 볼 수 있습니다.

 

되돌아보면 이 total element 계산 때문에 시간이 많이 들것 같다고 생각했는데, 복합 인덱스가 적용되니 type = const (단일 row만 필요할 때 옵티마이저가 상수처럼 다룸)로 생각보다 짧은 시간이 잡아 먹고 있었습니다.

 

결론

 

이번 경험에서는 ID 선 조회 패턴, JOIN 최소화, 불필요한 count 제거, 인덱스 적용으로 쿼리 속도 개선을 진행하였습니다.

결론적으로 조회 속도가 5 ~ 7초 ➞ 0.5초 이내 (약 10배) 만큼 빨라졌습니다. 

이번 기회에 확실히 인덱스의 강력함을 느꼈습니다.

 

또 페이징 성능 개선을 위해 slice 방식 외에도 NO OFFSET 방식이라고 해서 아예 다음으로 조회해야할 Id를 명시하는 방법이 있더라구요.

이 방법도 무한 스크롤이 요구사항일때는 써볼만한 방식인 것 같습니다. 요거는 FE와 합의가 되면 또 써보도록 하겠습니다.

감사합니다.