[Spring] @Formula annotation을 활용하여 count 조회 성능 개선

 

사내 애플리케이션에 장비와 사용자 수를 count 하는 쿼리가 존재했습니다. 조회 쿼리 성능을 개선하기 위함임은 알 수 있지만, 구체적으로 어떻게 동작하는지도 알아보려고 합니다.

 

더불어, 해당 어노테이션의 장점과 단점에 대해서도 상세히 설명드리겠습니다.

@Formula

• entity 내에서 실제로 db 스키마에 존재하지 않는 가상 컬럼 (jpa 상에서만 존재하고, 실제 db에 존재하지 않는 컬럼)을 정의할 수 있는 기능 
• 다른 컬럼들의 값에 기반하여 계산된 값을 표현할 수 있으며, 엔티티를 조회할때만 계산되어 사용됩니다. (Read-Only)
• Default: EAGER 전략

 

이제 @Formula 어노테이션을 적용하였을 때 조회 쿼리와 lazy loading으로 size() 메서드를 호출하였을 때의 쿼리 차이를 비교해 보겠습니다.

@Formula("(select count(*) from conf_device t2 where t2.region_id = id)")
public int deviceCount;

@Formula("(select count(*) from conf_user t3 where t3.region_id = id)")
public int userCount;

 

 

@Formula 어노테이션 적용 시 쿼리 (root entity가 1개일 때의 기준: 1방)

Hibernate: 
    select
        region0_.id as id1_12_,
        region0_.ip as ip2_12_,
        region0_.name as name3_12_,
        region0_.port as port4_12_,
        region0_.reg_date as reg_date5_12_,
        region0_.ssl_enabled as ssl_enab6_12_,
        region0_.status as status7_12_,
        region0_.upd_date as upd_date8_12_,
        region0_.uptime as uptime9_12_,
        region0_.version as version10_12_,
        (select
            count(*) 
        from
            conf_device t2 
        where
            t2.region_id = region0_.id) as formula0_,
        (select
            count(*) 
        from
            conf_user t3 
        where
            t3.region_id = region0_.id) as formula1_ 
    from
        conf_region region0_ 
    order by
        region0_.id desc limit ?

 

 

size() 메서드 호출 시 쿼리 (root entity가 1개일 때의 기준: 7방)

public int getDeviceCount() {
    return this.devices.size();   
}
    
public int getUserCount() {
    return this.users.size();      
}

 

Hibernate: 
    select
        region0_.id as id1_12_,
        region0_.ip as ip2_12_,
        region0_.name as name3_12_,
        region0_.port as port4_12_,
        region0_.reg_date as reg_date5_12_,
        region0_.ssl_enabled as ssl_enab6_12_,
        region0_.status as status7_12_,
        region0_.upd_date as upd_date8_12_,
        region0_.uptime as uptime9_12_,
        region0_.version as version10_12_ 
    from
        conf_region region0_ 
    order by
        region0_.id desc limit ?
Hibernate: 
    select
        users0_.region_id as region_i6_12_0_,
        users0_.id as id1_13_0_,
        users0_.id as id1_13_1_,
        users0_.email as email2_13_1_,
        users0_.name as name3_13_1_,
        users0_.passwd as passwd4_13_1_,
        users0_.reg_date as reg_date5_13_1_,
        users0_.region_id as region_i6_13_1_,
        users0_.type as type7_13_1_,
        users0_.upd_date as upd_date8_13_1_,
        users0_.user_id as user_id9_13_1_,
        users0_.user_identifier as user_id10_13_1_ 
    from
        conf_user users0_ 
    where
        users0_.region_id=?
Hibernate: 
    select
        userroles0_.user_id as user_id5_13_0_,
        userroles0_.id as id1_14_0_,
        userroles0_.id as id1_14_1_,
        userroles0_.refer_id as refer_id2_14_1_,
        userroles0_.refer_type as refer_ty3_14_1_,
        userroles0_.type as type4_14_1_,
        userroles0_.user_id as user_id5_14_1_ 
    from
        conf_user_role userroles0_ 
    where
        userroles0_.user_id=?
Hibernate: 
    select
        userroles0_.user_id as user_id5_13_0_,
        userroles0_.id as id1_14_0_,
        userroles0_.id as id1_14_1_,
        userroles0_.refer_id as refer_id2_14_1_,
        userroles0_.refer_type as refer_ty3_14_1_,
        userroles0_.type as type4_14_1_,
        userroles0_.user_id as user_id5_14_1_ 
    from
        conf_user_role userroles0_ 
    where
        userroles0_.user_id=?
Hibernate: 
    select
        userroles0_.user_id as user_id5_13_0_,
        userroles0_.id as id1_14_0_,
        userroles0_.id as id1_14_1_,
        userroles0_.refer_id as refer_id2_14_1_,
        userroles0_.refer_type as refer_ty3_14_1_,
        userroles0_.type as type4_14_1_,
        userroles0_.user_id as user_id5_14_1_ 
    from
        conf_user_role userroles0_ 
    where
        userroles0_.user_id=?
Hibernate: 
    select
        userroles0_.user_id as user_id5_13_0_,
        userroles0_.id as id1_14_0_,
        userroles0_.id as id1_14_1_,
        userroles0_.refer_id as refer_id2_14_1_,
        userroles0_.refer_type as refer_ty3_14_1_,
        userroles0_.type as type4_14_1_,
        userroles0_.user_id as user_id5_14_1_ 
    from
        conf_user_role userroles0_ 
    where
        userroles0_.user_id=?
Hibernate: 
    select
        devices0_.region_id as region_i9_12_0_,
        devices0_.id as id1_3_0_,
        devices0_.id as id1_3_1_,
        devices0_.device_id as device_i2_3_1_,
        devices0_.device_type as device_t3_3_1_,
        devices0_.mac as mac4_3_1_,
        devices0_.model as model5_3_1_,
        devices0_.name as name6_3_1_,
        devices0_.oper_status as oper_sta7_3_1_,
        devices0_.reg_date as reg_date8_3_1_,
        devices0_.region_id as region_i9_3_1_,
        devices0_.serial as serial10_3_1_ 
    from
        conf_device devices0_ 
    where
        devices0_.region_id=?

 

정말 불필요한 쿼리들이 많이 발생하게 됩니다.

 

count를 조회하기 위해 불필요한 필드까지 모두 조회하게 됩니다. 이를 해결하기 위해서 SQL 하위 쿼리로 entity를 조회할 때 count도 조회할 수 있는 @Formula annotation을 적용하여 count만 조회할 수 있게 됩니다. 해당 어노테이션은 native sql을 사용하기에 어노테이션 내부에 db에서 직접 실행할 수 있는 sql문을 넣어줘야 합니다.

 

하지만, 조회할 때만 값을 계산해 주기에 값이 업데이트 됐을 때 자동으로 count가 늘어나지 않습니다. refresh를 해주어야 다시 count가 증가하기에 이 부분을 염두해두고 사용하셔야 됩니다.

 

<참고 자료>

https://dkswnkk.tistory.com/734

Hibernate의 @Formula를 이용한 연관 관계 엔티티 집계

https://www.popit.kr/jpa-%EC%97%94%ED%84%B0%ED%8B%B0-%EC%B9%B4%EC%9A%B4%ED%8A%B8-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0%ED%95%98%EA%B8%B0/

JPA 엔터티 카운트 성능 개선하기 | Popit