[리팩토링] 레거시 조회 API 성능 최적화 15000ms -> 180ms

조건

PACK 테이블 레코드 : 총 10246개

 

개선 전 성능 : 약 15초

개선 전 쿼리의 문제 : 아래에서 확인할 수 있듯이 쿼리에서 N+1 이슈가 지독하게 발생

	...
    ...
    ...
    
   
Hibernate: 
    select
        pocalist0_.pack_id as pack_id22_1_0_,
        pocalist0_.id as id1_1_0_,
        pocalist0_.id as id1_1_1_,
        pocalist0_.album_id as album_i20_1_1_,
        pocalist0_.poca_category_id as poca_cat2_1_1_,
        pocalist0_.comment_count as comment_3_1_1_,
        pocalist0_.poca_content as poca_con4_1_1_,
        pocalist0_.get_count as get_coun5_1_1_,
        pocalist0_.cur_qty as cur_qty6_1_1_,
        pocalist0_.poca_img as poca_img7_1_1_,
        pocalist0_.poca_level as poca_lev8_1_1_,
        pocalist0_.location_id as locatio21_1_1_,
        pocalist0_.max_qty as max_qty9_1_1_,
        pocalist0_.poca_name as poca_na10_1_1_,
        pocalist0_.poca_number as poca_nu11_1_1_,
        pocalist0_.pack_id as pack_id22_1_1_,
        pocalist0_.poca_category_common_id as poca_ca12_1_1_,
        pocalist0_.poca_category_extra_id as poca_ca13_1_1_,
        pocalist0_.poca_category_group_id as poca_ca14_1_1_,
        pocalist0_.register_time as registe15_1_1_,
        pocalist0_.poca_representstatus as poca_re16_1_1_,
        pocalist0_.update_time as update_17_1_1_,
        pocalist0_.use_type as use_typ18_1_1_,
        pocalist0_.xto_del as xto_del19_1_1_ 
    from
        APIServer_arpoca pocalist0_ 
    where
        pocalist0_.pack_id=?
Hibernate: 
    select
        userpackli0_.pacK_id as pack_id3_5_0_,
        userpackli0_.id as id1_5_0_,
        userpackli0_.id as id1_5_1_,
        userpackli0_.pacK_id as pack_id3_5_1_,
        userpackli0_.timestamp as timestam2_5_1_,
        userpackli0_.user_id as user_id4_5_1_ 
    from
        APIServer_arpoca_user_pack userpackli0_ 
    where
        userpackli0_.pacK_id=?
        
    ...
    ...
    ...

 

개선 과정

1. collection 조회 batch size 적용

-> 성능 약 2초로 개선

LEFT : 최초 실행(DNS 등에 대한 캐싱 전) / RIGHT : 여러번 실행

in 절로 한번에 많은 데이터 호출하여 쿼리 성능 개선되었지만, 여전히 n+1 문제는 해결x 

Hibernate: 
    select
        distinct pack0_.id as id1_4_,
        pack0_.album_id as album_i29_4_,
        pack0_.pack_card_img as pack_car2_4_,
        pack0_.pack_cardmotion_img as pack_car3_4_,
        pack0_.pack_codekey as pack_cod4_4_,
        pack0_.pack_content as pack_con5_4_,
        pack0_.end_time as end_time6_4_,
        pack0_.pack_getmotion_img as pack_get7_4_,
        pack0_.pack_img as pack_img8_4_,
        pack0_.level1msg as level9_4_,
        pack0_.level2msg as level10_4_,
        pack0_.level3msg as level11_4_,
        pack0_.level4msg as level12_4_,
        pack0_.level5msg as level13_4_,
        pack0_.location_id as locatio30_4_,
        pack0_.maker_userid as maker_u31_4_,
        pack0_.pack_music as pack_mu14_4_,
        pack0_.pack_name as pack_na15_4_,
        pack0_.pack_chat_cnt as pack_ch16_4_,
        pack0_.pack_cur_user as pack_cu17_4_,
        pack0_.pack_max_user as pack_ma18_4_,
        pack0_.pack_status as pack_st19_4_,
        pack0_.pack_type as pack_ty20_4_,
        pack0_.pack_pin_done_img as pack_pi21_4_,
        pack0_.pack_pin_img as pack_pi22_4_,
        pack0_.register_time as registe23_4_,
        pack0_.start_time as start_t24_4_,
        pack0_.pack_target_img as pack_ta25_4_,
        pack0_.timestamp as timesta26_4_,
        pack0_.tot_type as tot_typ27_4_,
        pack0_.update_time as update_28_4_ 
    from
        APIServer_arpoca_pack pack0_ 
    left outer join
        APIServer_user user1_ 
            on pack0_.maker_userid=user1_.id 
    left outer join
        APIServer_arpoca pocalist2_ 
            on pack0_.id=pocalist2_.pack_id 
    left outer join
        APIServer_arpoca_user_pack userpackli3_ 
            on pack0_.id=userpackli3_.pacK_id 
    where
        pack0_.pack_status=? 
    order by
        pack0_.update_time desc
Hibernate: 
    select
        pocalist0_.pack_id as pack_id22_1_1_,
        pocalist0_.id as id1_1_1_,
        pocalist0_.id as id1_1_0_,
        pocalist0_.album_id as album_i20_1_0_,
        pocalist0_.poca_category_id as poca_cat2_1_0_,
        pocalist0_.comment_count as comment_3_1_0_,
        pocalist0_.poca_content as poca_con4_1_0_,
        pocalist0_.get_count as get_coun5_1_0_,
        pocalist0_.cur_qty as cur_qty6_1_0_,
        pocalist0_.poca_img as poca_img7_1_0_,
        pocalist0_.poca_level as poca_lev8_1_0_,
        pocalist0_.location_id as locatio21_1_0_,
        pocalist0_.max_qty as max_qty9_1_0_,
        pocalist0_.poca_name as poca_na10_1_0_,
        pocalist0_.poca_number as poca_nu11_1_0_,
        pocalist0_.pack_id as pack_id22_1_0_,
        pocalist0_.poca_category_common_id as poca_ca12_1_0_,
        pocalist0_.poca_category_extra_id as poca_ca13_1_0_,
        pocalist0_.poca_category_group_id as poca_ca14_1_0_,
        pocalist0_.register_time as registe15_1_0_,
        pocalist0_.poca_representstatus as poca_re16_1_0_,
        pocalist0_.update_time as update_17_1_0_,
        pocalist0_.use_type as use_typ18_1_0_,
        pocalist0_.xto_del as xto_del19_1_0_ 
    from
        APIServer_arpoca pocalist0_ 
    where
        pocalist0_.pack_id in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
Hibernate: 
    select
        user0_.id as id1_6_0_,
        user0_.access_token as access_t2_6_0_,
        user0_.last_login_time as last_log3_6_0_,
        user0_.login_id as login_id4_6_0_,
        user0_.login_pwd as login_pw5_6_0_,
        user0_.login_type as login_ty6_6_0_,
        user0_.reg_time as reg_time7_6_0_,
        user0_.unreg_time as unreg_ti8_6_0_,
        user0_.user_email as user_ema9_6_0_,
        user0_.user_img as user_im10_6_0_,
        user0_.user_memo as user_me11_6_0_,
        user0_.user_name as user_na12_6_0_,
        user0_.user_nick as user_ni13_6_0_,
        user0_.user_phone as user_ph14_6_0_,
        user0_.user_status as user_st15_6_0_,
        user0_.wallet_id as wallet_16_6_0_,
        user0_.zdel_tmp_auth_user_id as zdel_tm17_6_0_ 
    from
        APIServer_user user0_ 
    where
        user0_.id in (
            ?, ?, ?, ?, ?, ?, ?
        )
Hibernate: 
    select
        userpackli0_.pacK_id as pack_id3_5_1_,
        userpackli0_.id as id1_5_1_,
        userpackli0_.id as id1_5_0_,
        userpackli0_.pacK_id as pack_id3_5_0_,
        userpackli0_.timestamp as timestam2_5_0_,
        userpackli0_.user_id as user_id4_5_0_ 
    from
        APIServer_arpoca_user_pack userpackli0_ 
    where
        userpackli0_.pacK_id in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )

...
...
...

 

XToOne 연관관계 fetch join을 적용하면 성능이 어느정도 나오는지 확인 : 1.7초

N+1 원인 추정 : user(7개)별로 packlist나 userpack을 양방향으로 선언해 두었는데, 연관관계 주인이 아닌 쪽에 데이터를 저장하고 호출하는 과정에서 문제가 발생하는 것으로 추정 -> 단방향으로 우선 개선

 

위와 더불어서, 우선 불러오려고 하는 데이터 필드가 너무 필요 이상으로 많다는 것을 알 수 있었다. 즉, dto 설계가 처음부터 잘못되어 있었다. 기존 작업자가 도메인에 대한 이해도가 떨어져서 필요 이상의 필드까지 전부 dto로 전달하도록 구성해둔 것이 가장 큰 문제로 보였다.

 

따라서 조회하는 필드 개수를 필요한 만큼만 줄여서 dto로 바로 반환하는 로직을 구사한다면 훨씬 가볍게 쿼리를 돌릴 수 있을 것으로 판단하였다.

 

(Dto 명세 main page에 맞게 수정 후 조회 쿼리 성능을 계산해본 뒤에는, 놀랄 수 밖에 없었다.)

 

아래는 수정된 내용 중 일부이다.

PackResponseMainDto.java

@Builder
@Data
@NoArgsConstructor
@Schema(description = "Arpoca pack for main page")
public class PackResponseMainDto {

    @Schema(description = "pk")
    private Integer packId;

    @Schema(description = "pack_max_user")
    private Integer packMaxUser;

    @Schema(description = "pack_cur_user")
    private Integer packCurUser;

    @Schema(description = "pack_chat_cnt")
    private Integer packChatCnt;

    @Schema(description = "maker_user_nickName")
    private String makerUserNickName;

    @Schema(description = "name")
    private String name;

    @Schema(description = "팩 설명")
    private String content;

    @Schema(description = "img")
    private String img;

    @Schema(description = "포카 수")
    private Integer totalPocaSize;


    @QueryProjection
    public PackResponseMainDto(Integer packId, Integer packMaxUser, Integer packCurUser, Integer packChatCnt, String makerUserNickName, String name, String content, String img, Integer totalPocaSize) {
        this.packId = packId;
        this.packMaxUser = packMaxUser;
        this.packCurUser = packCurUser;
        this.packChatCnt = packChatCnt;
        this.makerUserNickName = makerUserNickName;
        this.name = name;
        this.content = content;
        this.img = img;
        this.totalPocaSize = totalPocaSize;
    }
}

PackRepositoryCustomImpl.java

	@Override
	public List<PackResponseMainDto> findPackListOrderByUpdateDate_optDto() {
		return queryFactory
				.select(new QPackResponseMainDto(pack.id, pack.packMaxUser, pack.packCurUser, pack.packChatCnt, pack.makerUser.userNickname, pack.name, pack.content, pack.img, pack.pocaList.size()))
				.from(pack)
				.join(pack.makerUser, user)
				.leftJoin(pack.location, location)
				.leftJoin(pack.pocaList, poca)
				.orderBy(pack.updateTime.desc())
				.where(pack.packStatus.eq(PackStatus.ON))
				.fetch();
	}

 

 

10,000개의 데이터를 정렬하여 조회하는 쿼리가 처음에 아무런 개선도 하기 전의 레거시 코드로는 15초가 걸렸던 API인데,

1. dto를 이용하여 필요한 필드만 정의해주고,
2. 이 명세에 맞게 바로 repository에서 가공해줘서 별도의 가공 시간이 들지 않게 하니,

api 응답 속도가 15000ms -> 180ms 로 약 80배 정도 개선된 것을 알 수 있다.

쿼리도 훨씬 깔끔하고 효율적이게 변화되었다.

Hibernate: 
    select
        pack0_.id as col_0_0_,
        pack0_.pack_max_user as col_1_0_,
        pack0_.pack_cur_user as col_2_0_,
        pack0_.pack_chat_cnt as col_3_0_,
        user1_.user_nick as col_4_0_,
        pack0_.pack_name as col_5_0_,
        pack0_.pack_content as col_6_0_,
        pack0_.pack_img as col_7_0_,
        (select
            count(pocalist5_.pack_id) 
        from
            APIServer_arpoca pocalist5_ 
        where
            pack0_.id = pocalist5_.pack_id) as col_8_0_ 
    from
        APIServer_arpoca_pack pack0_ 
    inner join
        APIServer_user user1_ 
            on pack0_.maker_userid=user1_.id 
    left outer join
        APIServer_arpoca_location location2_ 
            on pack0_.location_id=location2_.id 
    left outer join
        APIServer_arpoca pocalist3_ 
            on pack0_.id=pocalist3_.pack_id 
    where
        pack0_.pack_status=? 
    order by
        pack0_.update_time desc

 

 

레슨런

쿼리 튜닝의 영역에서는 작업 당시 내가 할 수 있는 최선을 전부 적용했었다. 그래도 성과가 두드러지게 보였고, API 별로 필요한 데이터 스펙에 맞게 response dto를 맞춰주는 것이 생각보다 상당히 중요한 영역임을 느낄 수 있었다. (필요없는 데이터까지 조회할 때의 리소스 낭비에 대한 관점)

 

도메인에 대한 명확한 이해가 중요하다는 것도 느끼게 되었다. 도메인을 충분히 이해하지 못하면 어차피 두번 작업하게 되는 일이 발생할 확률이 높으니, 처음에 조금 시간이 드는 것 처럼 느껴지더라도 확실히 짚고 넘어가야겠다고 생각했다.