한방 쿼리

배경

저희 모락 서비스에서는 ‘역할 정하기’ 라는 기능이 있습니다. 이 기능은 데일리 마스터, 서기 등 그룹 별로 등록한 역할들을 랜덤으로 정해주는 기능입니다. 역할 정하기의 페이지에서 역할 정하기를 클릭하면 각 그룹마다 저장한 역할을 팀원들과 매칭시켜줍니다. 그리고 역할 정하기 히스토리를 조회하는 API 도 있습니다. 이 API 의 비즈니스 로직은 복잡하기때문에 기본적인 JPA 메서드를 이용하는 대신 query dsl 을 이용하여 한방 쿼리로 보내고 있었습니다. 이 한방 쿼리는 서브쿼리가 2개가 생성되고 있었고 해당 쿼리로 인해 성능이 좋지 않았는데요. 이 한방 쿼리에 대해 분석을 해보고 더 나은 방향을 찾아보도록 하겠습니다.

상태

해당 기능의 erd 는 다음과 같습니다.

역할 정하기를 클릭하면 역할 히스토리 테이블에 클릭한 시간이 저장되고, 각각의 매칭 결과가 역할 정하기 결과 테이블에 저장됩니다.

오늘 변경하려는 API 는 역대 매칭 기록을 조회하는 API 입니다. 데이터 저장은 date time 타입으로 하지만, 비즈니스 정책 상 응답에서는 일별로 그룹화하여 표현합니다. 그리고 한 날짜에 여러 기록이 있다면 해당 날짜의 가장 마지막 결과를 응답합니다.

로직

해당 로직은 다음과 같습니다.

teamCode 로 role 조회

조회한 roleId 가 포함된 role history 목록을 일별로 그룹화/정렬하고  조회

조회된 role history 가 포함된 role match result 를 조회

List<RoleHistory> roleHistories = jpaQueryFactory.selectFrom(roleHistory)
                .join(roleHistory.matchResults).fetchJoin()
                .where(roleHistory.id.in(
                        JPAExpressions.select(roleHistory.id.max())
                                .from(roleHistory)
                                .groupBy(toLocalDate())
                                .orderBy(toLocalDate().desc())
                                .where(roleHistory.roleId.eq(
                                        JPAExpressions.select(role.id)
                                                .from(role)
                                                .where(role.teamCode.code.eq(teamCode))
                                ))
                ))
                .fetch();
Java
복사

2개의 서브쿼리를 이용해 한방 쿼리를 날릴 수 있었습니다.

select
        rolehistor0_.id as id1_8_,
        rolehistor0_.date_time as date_tim2_8_,
        rolehistor0_.role_id as role_id3_8_,
        matchresul1_.role_history_id as role_his1_6_0__,
        matchresul1_.member_id as member_i2_6_0__,
        matchresul1_.role_name as role_nam3_6_0__ 
    from
        role_history rolehistor0_ 
    inner join
        role_match_result matchresul1_ 
            on rolehistor0_.id=matchresul1_.role_history_id 
    where
        rolehistor0_.id in (
            select
                max(rolehistor2_.id) 
            from
                role_history rolehistor2_ 
            where
                rolehistor2_.role_id=(
                    select
                        role3_.id 
                    from
                        role role3_ 
                    where
                        role3_.team_code='edenee17'
                ) 
            group by
                date(rolehistor2_.date_time) 
            order by
                date(rolehistor2_.date_time) desc)
SQL
복사

using where 는 스토리지 엔진이 데이터를 가져온 뒤 mysql 엔진이 추가 체크를 통해 필터링한다는 정보입니다.

date 선택 이유

쿼리 제안

짧게 여러번

select
        role0_.id as id1_5_,
        role0_.created_at as created_2_5_,
        role0_.updated_at as updated_3_5_,
        role0_.team_code as team_cod4_5_ 
    from
        role role0_ 
    where
        role0_.team_code='edenee1'
SQL
복사

select
        max(rolehistor0_.id) as col_0_0_ 
    from
        role_history rolehistor0_ 
    where
        rolehistor0_.role_id=17
    group by
        dayofyear(rolehistor0_.date_time) 
    order by
        dayofyear(rolehistor0_.date_time);
SQL
복사

select
        rolehistor0_.id as id1_8_,
        rolehistor0_.date_time as date_tim2_8_,
        rolehistor0_.role_id as role_id3_8_ 
    from
        role_history rolehistor0_ 
    where
        rolehistor0_.id in (
            50000 , 9350
        )
SQL
복사

select
        matchresul0_.role_history_id as role_his1_6_0_,
        matchresul0_.member_id as member_i2_6_0_,
        matchresul0_.role_name as role_nam3_6_0_ 
    from
        role_match_result matchresul0_ 
    where
        matchresul0_.role_history_id in (
            50000 , 9350
        )
SQL
복사