JPA N+1 문제 해결하며 JMeter로 쿼리 성능 비교 후 group_concat 집계 함수 방식 채택

진행중인 프로젝트에서 N+1 문제를 접하게 되었고, 이를 해결하기 위한 방법을 고민해 보았습니다. 찾은 해결 방법은 2가지 였는데, 이 2가지 방법을 Apache JMeter로 테스트를 해보아서 어느 방법이 더 적합할지 선택할 수 있었습니다.

또한 이 문제를 접하게 되면서 JPA의 지연 로딩과 즉시 로딩, N+1 문제가 발생하는 이유와 해결 방법, Fetch Join 과 Inner Join의 주의 사항, Mysql group_concat 함수도 공부해 볼 수 있었습니다. 다음 링크들은 공부하게 되면서 정리한 글입니다.
지연로딩-즉시로딩-리마인드,

N+1 문제와 해결 방법,

Spring-Data-JPA에서-FetchJoin-주의-사항

• 개발 환경 : JAVA 17, Spring Boot 3.1.1, Spring Data JPA, Mysql 8.3.0, [깃허브 주소]

문제 상황

사용자의 추억이 담긴 ‘capsule’ 엔티티의 세부 정보를 조회해야 합니다. 다음은 응답을 해줘야할 데이터들입니다.

@Schema(description = "비밀 캡슐 상세 정보")
@Builder
public record SecretCapsuleDetailResponse(
@Schema(description = "캡슐 스킨 url")
String capsuleSkinUrl,

@Schema(description = "개봉일")
ZonedDateTime dueDate,

@Schema(description = "생성자 닉네임")
String nickname,

@Schema(description = "생성자 프로필 url")
String profileUrl,

@Schema(description = "생성일")
ZonedDateTime createdDate,

@Schema(description = "캡슐 위도 좌표")
Double latitude,

@Schema(description = "캡슐 경도 좌표")
Double longitude,

@Schema(description = "캡슐 생성 주소")
String address,

@Schema(description = "캡슐 생성 도로 이름")
String roadName,

@Schema(description = "제목")
String title,

@Schema(description = "내용")
String content,

@Schema(description = "이미지 url들")
List<String> imageUrls,

@Schema(description = "비디오 url들")
List<String> videoUrls,

@Schema(description = "개봉 여부")
Boolean isOpened,

@Schema(description = "캡슐 타입")
CapsuleType capsuleType
) {
}

쉽게 말해서 한 게시물의 정보들을 조회할 때 필요한 응답 값들 입니다. 여기서 이미지 URL들과 비디오 URL을 반환하기 위해서는 ‘capsule’ 엔티티와 1대N 관계를 가지고 있는 ‘image’ 엔티티와 ‘video’ 엔티티의 데이터가 필요했습니다. capsule 객체 하나당 image URL은 최대 5개, video URL은 최대 1개로 지정했습니다. List<String> 형식으로 비디오 URL을 저장한 이유는 나중에 더 많은 비디오를 추가할 수 있는 유연성을 확보하기 위해서입니다. 이러한 상황에서 의도치 않은 데이터베이스 쿼리 발생 및 N+1 문제를 방지하기 위해 주의해야 했습니다.

문제 고민

첫 번째 해결 방법에 대한 시도 - Fetch Join

1대N 관계에 있어서 JPA에서는 흔히 해결할 수 있는 방법으로 fetch Join을 사용하여 즉시 로딩을 할 수 있기에, fetch join을 image 엔티티와 video 엔티티에 각각 2번 사용하면 될 것이라고 생각을 하였습니다.

그러나 ~toMany에서 fetch join을 2번 사용하게 되면 MultipleBagFetchException 이 발생하게 됩니다. 따라서 fetch Join을 2번 사용하는 방법은 문제를 해결하지 못합니다. (toOne에서는 fetch join을 여러번 사용해도 문제가 없습니다.)

2번째 해결 방법에 대한 시도 - EntityGraph

N+1문제를 해결하는 방법으로는 EntityGraph를 사용할 수도 있었습니다.

@EntityGraph(attributePaths = {"images", "videos"})를 Spring Data JPA에서 Repository 메서드 위에 달면 되지만, 이 역시 MultipleBagFetchException 이 발생하고 맙니다. 따라서 EntityGraph는 문제를 해결하지 못합니다.

3번째 해결 방법에 대한 시도 - FetchMode.SUBSELECT, BatchSize

FetchMode.SUBSELECT 로 해당 엔티티를 조회하는 쿼리는 그대로 발생하고 연관관계의 데이터를 조회할 때 서브 쿼리로 함께 조회하는 방법이 있었습니다. 이때 쿼리는 하나의 capsule 객체를 조회하는 쿼리가 발생하고

Hibernate: 
    select
        c1_0.`capsule_id`,
        c2_0.`image_url`,
        c1_0.`due_date`,
        m1_0.`nickname`,
        m1_0.`profile_url`,
        c1_0.`created_at`,
        c1_0.`full_road_address_name`,
        c1_0.`title`,
        c1_0.`content`,
        c1_0.`is_opened`,
        c1_0.`type` 
    from
        `capsule` c1_0 
    join
        `member` m1_0 
            on c1_0.`member_id`=m1_0.`member_id` 
    join
        `capsule_skin` c2_0 
            on c1_0.`capsule_skin_id`=c2_0.`capsule_skin_id` 
    where
        c1_0.`capsule_id`=? 
        and c1_0.`member_id`=? 
        and c1_0.`type`=? limit ?
Hibernate: 
    select
        i1_0.`image_url` 
    from
        `image` i1_0 
    where
        i1_0.`capsule_id`=?
Hibernate: 
    select
        v1_0.`video_url` 
    from
        `video` v1_0 
    where
        v1_0.`capsule_id`=?

이렇게 capsuleId로 image와 video의 데이터를 가져오는 방법으로 해결할 수 있었습니다.

@BatchSize(지정된 size 만큼 SQL의 IN절을 사용해서 조회하는 방식) 역시, FetchMode.SUBSELECT와 같은 쿼리가 발생하였습니다.

즉, FetchMode.SUBSELECT와 BatchSize를 사용하는 방식은 capsule 테이블를 조회, capsule id를 이용하여 image 테이블 조회, video 테이블 조회로 총 3회의 쿼리가 발생하여 해결 할 수 있습니다.

4 번째 해결 방법에 대한 시도 - Left Join

capsule 테이블과 image 테이블, video 테이블을 각각 left join 하여 총 2번 left join을 하면 문제가 해결 될 것이라고 생각을 하였습니다.

select
        c1_0.`capsule_id`,
        c2_0.`image_url`,
        c1_0.`due_date`,
        m1_0.`nickname`,
        m1_0.`profile_url`,
        c1_0.`created_at`,
        c1_0.`full_road_address_name`,
        c1_0.`title`,
        c1_0.`content`,
        i1_0.`image_url`,
        v1_0.`video_url`,
        c1_0.`is_opened`,
        c1_0.`type` 
    from
        `capsule` c1_0 
    join
        `member` m1_0 
            on c1_0.`member_id`=m1_0.`member_id` 
    join
        `capsule_skin` c2_0 
            on c1_0.`capsule_skin_id`=c2_0.`capsule_skin_id` 
    left join
        `image` i1_0 
            on c1_0.`capsule_id`=i1_0.`capsule_id` 
    left join
        `video` v1_0 
            on c1_0.`capsule_id`=v1_0.`capsule_id` 
    where
        c1_0.`capsule_id`=? 
        and c1_0.`member_id`=? 
        and c1_0.`type`=? limit ?

다음과 같은 쿼리가 완성되었습니다. 이때, capsule 엔티티는 member, capsuleSkin과는 N대1 관계를 가지고 있는데, join을 통해 nickname, profileUrl, imageUrl들을 가져올 수 있습니다.

다시 핵심으로 들어와서, left join을 2번 사용했을 때의 문제점은 image URL들과 video URL들이 카테시안 곱을 하여 데이터가 조회가 되는 것입니다.

image1,2,3,4,5와 video1을 생성했는데 조회된 데이터에서는 (image1, video1)… (image5, video1)으로 데이터가 조회되는 것이 문제였습니다.

Left join을 2번만 사용해서는 문제를 해결 할 수가 없습니다. left join을 2번 사용할 경우 카테시안 곱을 주의해야겠습니다.

5 번째 해결 방법에 대한 시도 - group_concat 집계함수

Left join을 2번 사용해서의 문제점은 연관된 데이터는 조회가 잘 되지만 카테시안 곱 형태로 가져온다는 것이었습니다. 즉, 카테시안 곱만 해결하면 문제를 해결 할 수 있다. Mysql에서는 특정 조건에서 조회된 데이터들을 하나로 문자열로 묶는 집계 함수인 group_concat이 있습니다. group_concat 함수는 기본적으로 , 쉼표로 데이터들을 묶으며, 1024 길이 까지 됩니다. 이미지, 비디오 URL은 예를 들어 directoryName/memberId/image_name.png로 저장되기에 하나의 URL은 약 50자 미만이고, 이미지 URL은 최대 5개로 한정지었기에, 총 250자이므로 사용하기에 적합해 보였습니다. 자세한 group_concat 함수에 대한 내용은 공식문서 에서 확인할 수 있습니다.

select
        c1_0.`capsule_id`,
        c2_0.`image_url`,
        c1_0.`due_date`,
        m1_0.`nickname`,
        m1_0.`profile_url`,
        c1_0.`created_at`,
        c1_0.`full_road_address_name`,
        c1_0.`title`,
        c1_0.`content`,
        group_concat(distinct i1_0.`image_url`),
        group_concat(distinct v1_0.`video_url`),
        c1_0.`is_opened`,
        c1_0.`type` 
    from
        `capsule` c1_0 
    join
        `member` m1_0 
            on c1_0.`member_id`=m1_0.`member_id` 
    join
        `capsule_skin` c2_0 
            on c1_0.`capsule_skin_id`=c2_0.`capsule_skin_id` 
    left join
        `image` i1_0 
            on c1_0.`capsule_id`=i1_0.`capsule_id` 
    left join
        `video` v1_0 
            on c1_0.`capsule_id`=v1_0.`capsule_id` 
    where
        c1_0.`capsule_id`=? 
        and c1_0.`member_id`=? 
        and c1_0.`type`=? limit ?

실행되는 쿼리는 다음과 같습니다. group_concat이 추가가 된 것입니다.

실행 결과를 보았을 때 연관된 image URL데이터가 쉼표(,)로 하나의 문자열로 데이터를 가져오는 것을 확인할 수 있습니다. 클라이언트에 데이터를 보내줄 때에는 쉼표(,)를 기준으로 문자열을 파싱하여 전달해 주었습니다.

그렇다면 이렇게 성공적입니다.

총 5번 시도에 대한 정리

시도 방법	상황	해결 가능성
Fetch Join 2번	MultipleBagFetchException 발생	X
EntityGraph	MultipleBagFetchException 발생	X
FetchMode.SUBSELECT	총 3회 쿼리 발생	O
BatchSize	총 3회 쿼리 발생	O
InnerJoin 2번	카테시안 곱 발생	X
group_concat	총 1회 쿼리 발생 (집계 함수 사용)	O

최종적으로는 capsule 테이블을 조회한 후, 해당 capsule의 id를 사용하여 image 테이블을 조회하고, 이후에는 video 테이블을 조회하여 총 3번의 쿼리를 사용하는 방법이 효율적인지, 혹은 group_concat 집계 함수를 사용하여 1번의 쿼리로 처리하되 추가적인 파싱 메서드가 필요한 방법으로 해결하는 것 중 어느 것이 더 효율적인지 궁금하였습니다.

이를 확인하기 위하여 Apache JMeter를 이용하여 테스팅하기로 결정했습니다.

JMeter를 이용하여 테스팅을 결정한 이유는

1. 설치, 테스트 하기 쉬우며 GUI를 가지고 있습니다.
2. 커뮤니티, 레퍼런스 방대하고 최근까지도 유지보수가 진행중입니다.
3. JMeter는 부하 테스트를 기반으로 HTML 보고서를 생성할 수 있습니다.

저는 K6를 이용한 부하 테스트를 경험한 적이 있었는데, 이때는 테스트를 위해 script를 작성해야 했기에 번거로움이 있었지만, 몇 번만의 클릭 만으로 테스트를 할 수 있는 JMeter의 장점이 맘에 들었습니다.

다음은 K6와 JMeter를 비교한 글입니다. -> Comparing k6 and JMeter for load testing | Grafana Labs

테스팅 시나리오

Member 테이블 100만, capsule 테이블 50만, causule_skin 테이블 50만, image 테이블 100만, video테이블 50만이 저장 되어 있습니다.

Jmeter에서 Thread Group을 설정할 수 있는데, 이 설정은

1) 테스트 실행 중 Error 가 발생했을 경우에도 테스트를 진행하고

2) 가상 사용자의 숫자는 1000 명이며

3) 첫 번째 Thread 가 수행 되고, 다음 thread 가 수행 될 때 1초의 대기 시간이 있으며

4) 각각의 Thread 가 2번씩 실행되는 것입니다.

HTTP Request 에서는 capsule_id를 1 ~ 50만 숫자를 랜덤으로 생성하여 요청을 보냅니다.

즉, 1000명의 사용자가 자신의 캡슐 데이터를 2번씩 조회를 하는 것 만큼의 부하가 생기는 상황으로 가정해 볼 수 있습니다.

그럼 group_concat을 사용하지 않고 총 3회의 쿼리로 데이터를 가져오는 조회 방법과 group_concat 집계 함수를 사용하여 1회의 쿼리로 데이터를 가져와 파싱하는 조회방법을 비교해보겠습니다.

group_concat X, 총 3회 쿼리 발생

group_concat O, 총 1회 쿼리 발생 후 문자열 파싱

평균 Latency를 확인한 결과, group_concat을 사용하지 않은 경우, 1250s에서 사용한 경우 171s로 Latency가 감소한 것으로 나타났습니다. 이는 대략 86.32%의 감소를 보였습니다.

사용자 수 3천명으로 증가 비교

3천명으로 증가했을 때에도 약 2배 정도 차이가 발생했습니다. 이는 유저 수가 증가함에 따라 WAS에서 처리할 수 있는 양의 한계로 차이가 감소한 것으로 보입니다. 실제 사용자가 서비스를 이용하면서 캡슐 상세 조회 트래픽이 3천 명 이상 증가한다면, 쿼리를 개선하는 방법 외에도 다른 대안을 고려해야 할 것입니다.

쿼리 실행 순서

group_concat으로 작성한 쿼리의 실행 계획을 확인하여 더 개선할 수 있는지를 확인해보고자 했습니다. 먼저 쿼리 실행 계획을 분석하기 전에 먼저 SQL 쿼리 실행 순서를 알고 갈려 합니다.

• 쿼리는 FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT 순으로 진행이 됩니다.

비밀 캡슐 상세 조회 쿼리

• 최종 쿼리에 explain을 붙여서 쿼리 실행 계획를 확인해보겠니다.

모두 PK와 FK 인덱스를 이용하여 데이터를 조회하는 것을 알 수 있었습니다.

• 이번엔 explain analyze를 붙여 쿼리 실행 계획 트리 형태를 확인해보겠습니다.

-> Limit: 1 row(s)  (cost=5.4 rows=1) (actual time=0.332..0.332 rows=1 loops=1)
    -> Aggregate: group_concat(distinct i1_0.image_url separator ','), group_concat(distinct v1_0.video_url separator ',')  (cost=5.4 rows=1) (actual time=0.331..0.331 rows=1 loops=1)
        -> Nested loop left join  (cost=4.9 rows=5) (actual time=0.0488..0.309 rows=5 loops=1)
            -> Nested loop left join  (cost=3.15 rows=5) (actual time=0.0342..0.0374 rows=5 loops=1)
                -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=84e-6..126e-6 rows=1 loops=1)
                -> Index lookup on i1_0 using fk_image_capsule_id (capsule_id=2)  (cost=3.15 rows=5) (actual time=0.033..0.0358 rows=5 loops=1)
            -> Index lookup on v1_0 using fk_video_capsule_id (capsule_id=2)  (cost=0.27 rows=1) (actual time=0.0527..0.0538 rows=1 loops=5)

• 설명

1. Limit 절
   • 결과 집합이 한 행만 반환되어야 한다는 것을 나타내는데, QueryDSL에서 fetchFirst()를 사용하였기에 limit 이 발생한 것이다.
2. Aggregate 절
   • 이 단계는 결과를 집계하는 단계이다. group_concat 함수를 사용하여 이미지 URL과 비디오 URL을 각각 그룹화하고, 중복된 값들을 제외하고 쉼표(,)로 구분하여 합치는 작업을 수행한다.
3. Nested loop left join (중첩된 루프 왼쪽 조인) 절
   • 이 단계는 왼쪽 조인을 수행하는데, 중첩된 루프 방식으로 동작한다. 외부 루프에는 내부 루프가 중첩되어 있으므로 외부 루프에서 가져온 각 행마다 내부 루프가 실행된다. 또한 outer table에 먼저 접근하고 inner table에 대해 중첩 loop 로직이 들어가는데, outer table과 inner table의 위치 설정은 상관이 없다. 왜냐하면, optimizer가 최적의 outer table과 inner table을 찾아서 선택하기 때문이다.
4. Index lookup (인덱스 조회) 절:
   • 이 단계는 특정 인덱스를 사용하여 행을 가져오는 단계이다.
   • 첫 번째 인덱스 조회는 이미지 테이블에서 실행된다. fk_image_capsule_id 인덱스를 사용하여 capsule_id가 2인 image URL 5개(row)와 capsule 데이터를 가져온다. 이때 반복은 1번 돈다.
     • Rows fetched before execution (실행 전 행 가져오기) 절 에서는 실행 전에 이미 행을 가져온 것을 나타내는데 1행만 가져왔다.
   • 두 번째 인덱스 조회는 비디오 테이블에서 실행된다. fk_video_capsule_id 인덱스를 사용하여 capsule_id가 2인 video URL과 capsule 데이터를 가져온다. 1행만 처리되지만 image URL이 5개이므로 loop를 5번 돈다.

실행 순서

1. 처음 left join을 통해 capsule_id가 2인 image URL 5개를 포함한 capsule을 가져옵니다.
2. 두 번째 left join을 통해 capsule_id가 2인 video URL 1개을 포함한 capsule을 가져옵니다.
3. 집계 함수를 통해 image URL과 video URL을 그룹화 시킵니다.
4. 결과 집합에서 한 행만 반환을 합니다.

정리

1대N 연관관계에 있어서 ‘1’인 엔티티의 ‘N’인 엔티티의 데이터까지 가지고 올 때 N+1문제를 조심해야 하는데, fetch join 2번 사용, EntityGraph, FetchMode.SUBSELECT, BatchSize, left join 2번 사용, group_concat 집계 함수 사용을 시도하면서 총 3번의 쿼리가 발생해서 조회를 하는 방법과, 집계 함수를 사용하여 1회 쿼리를 사용하고 파싱하는 방법으로 문제를 해결 할 수 있음을 알 수 있었습니다. 이를 Jmeter로 테스트를 해보았을 때 1000명의 유저가 2번씩 캡슐 상세보기 기능을 사용하면 group_concat 집계 함수를 사용했을 시에 평균 Latencty는 사용 안했을 때보다 86.32% 감소됨을 학인 할 수 있었습니다.

현재 우리 프로젝트는 배포를 할 생각인데, 초기 유저 수는 1000명 이하일 것으로 예상합니다. 유저 수가 증가하더라도 이 두 방법 중 어느 것이 더 적합할지 생각을 해보았을 때, 평균적으로 더 빠른 Latency를 반환하는 group_concat 집계 함수 이용 후 파싱 하는 방법이 더 효율적이라고 판단하여 문제를 해결 할 수 있었습니다.