QueryDsl 거리 검색 쿼리 최적화

개요

사이드 프로젝트를 진행하면서 검색 API 구현을 담당하였다.
개인 프로젝트등에서는 단순 키워드를 통한 검색을 구현 하였지만
본 프로젝트에서는 정보 제공의 목적으로 거리를 계산해서 제공을 해야하는 상황이 발생했다.
이를 해결하는 과정을 기록으로 남겨두어 나중에 비슷한 상황이 발생하면 다시 참고해보는 용도로 해당 글을 작성한다.

문제 요구사항

해당 문제 상황에서 고려되는 사항은 다음 두 가지가 있었다.

1. 유저의 위도, 경도가 주어지면 해당 건물에 대한 거리가 출력되어야한다.
2. 유저의 위도, 경도가 주어지면 거리순 정렬이 되어야한다.

문제 인식

다른 요구사항에는 적절하게 구현이 완성된 상태였다.
기본적인 QueryDsl을 사용해서 값만 매칭시켜두면 해결되는 문제였기에 큰 어려움은 없었다.

다만 해당 문제를 해결하기 전 까지 거리를 검색하는 과정을 구현하기 위해서는 추가적인 쿼리 요청을 통해서 진행을 하거나 서버의 메모리에서 계산을 진행해야 하나 생각을 했다.

하지만 추가적인 쿼리 요청과 서버 메모리 상에서의 계산은 서비스를 제공하는데 부하가 생기지 않을까 생각을 해서
한번의 쿼리에 문제를 해결하도록 하여 구현을 해봤다.

Hibernate: 
    select
        a1_0.animal_hospital_id,
        a1_0.name,
        a1_0.street_address,cast(a1_0.region as char),
        a1_0.latitude,
        a1_0.longitude,
        a1_0.tell,
        a1_0.veterinarian_numbers,
        a1_0.scale,
        (cast(? as decimal(53,2))*acos((((cos(radians(?))*cos(radians(a1_0.latitude)))*cos((radians(a1_0.longitude)-radians(?))))+(sin(radians(?))*sin(radians(a1_0.latitude)))))),
        a1_0.is_cooperation 
    from
        animal_hospital a1_0 
    left join
        animal_hospital_category a2_0 
            on a1_0.animal_hospital_id=a2_0.animal_hospital_id 
    group by
        a1_0.animal_hospital_id 
    order by
        (?*acos((((cos(radians(?))*cos(radians(a1_0.latitude)))*cos((radians(a1_0.longitude)-radians(?))))+(sin(radians(?))*sin(radians(a1_0.latitude))))))

첫 시도를 진행했을 때의 쿼리 로그이다.
다른 조건은 null로 처리되어 where절이 존재하지 않는 경우를 볼 수 있다.
하지만 거리를 계산하는 연산이 추가되어 전송이 되고있는 경우를 볼 수 있었다.

이유를 찾아보니 다음 코드가 문제가 되는 것이라 판단했다.

NumberExpression<Double> coordinateDistance = null;  
coordinateDistance = getCoordinateDistance(param.longitude(), param.latitude());

.orderBy(coordinateDistance.asc())

getCoordinateDistance 메소드에 null값이 입력되면 null로 반환하는 것으로 알고 코드를 작성하였는데
SQL 자체에서 오류가 발생하는 부분이라 orderBy절에도 정상적으로 SQL이 입력되어 수행되는 것이였다.

왜냐하면 Expressions.numberTemplate 메소드가 템플릿 문자열을 생성하는 것이기 때문이다.
더 풀면 Expressions.numberTemplate 메소드는 실제로 계산을 수행하지 않고, SQL 쿼리를 위한 문자열 템플릿을 생성하는데, 이 때문에 longitude와 latitude가 null이어도 이 메소드는 정상적으로 실행되기 때문이였다…

해결 방법

NumberExpression<Double> coordinateDistance = Expressions.asNumber(0.0);  
if (param.longitude() != null && param.latitude() != null) {  
coordinateDistance = getCoordinateDistance(param.longitude(), param.latitude());  
}

바로 계산을 수행하는 과정에서 coordinateDistance를 기본값을 Expressions로 선언해주고
검색 쿼리에 기준점의 위도 경도 값이 입력되지 않는다면 해당 연산을 수행하지 않도록 하였다.

Hibernate: 
    select
        a1_0.animal_hospital_id,
        a1_0.name,
        a1_0.street_address,
        cast(a1_0.region as char),
        a1_0.latitude,
        a1_0.longitude,
        a1_0.tell,
        a1_0.veterinarian_numbers,
        a1_0.scale,
        a1_0.is_cooperation 
    from
        animal_hospital a1_0 
    left join
        animal_hospital_category a2_0 
            on a1_0.animal_hospital_id=a2_0.animal_hospital_id 
    group by
        a1_0.animal_hospital_id 
    order by
        ?

그 결과 기존에 존재하던 불필요한 연산을 활용하지 않았고

Hibernate: 
    select
        a1_0.animal_hospital_id,
        a1_0.name,
        a1_0.street_address,
        cast(a1_0.region as char),
        a1_0.latitude,
        a1_0.longitude,
        a1_0.tell,
        a1_0.veterinarian_numbers,
        a1_0.scale,
        (cast(? as decimal(53,2))*acos((((cos(radians(?))*cos(radians(a1_0.latitude)))*cos((radians(a1_0.longitude)-radians(?))))+(sin(radians(?))*sin(radians(a1_0.latitude)))))),
        a1_0.is_cooperation 
    from
        animal_hospital a1_0 
    left join
        animal_hospital_category a2_0 
            on a1_0.animal_hospital_id=a2_0.animal_hospital_id 
    group by
        a1_0.animal_hospital_id 
    order by
        (?*acos((((cos(radians(?))*cos(radians(a1_0.latitude)))*cos((radians(a1_0.longitude)-radians(?))))+(sin(radians(?))*sin(radians(a1_0.latitude))))))

실제 필요한 연산에서는 적용되는 것을 볼 수 있었다.

부하 테스트

전

후

1000명의 유저와 3번의 반복으로 3000회의 테스트를 수행한 결과

평균 0.7초 -> 0.6초 지연시간
TPS 850/sec -> 900/sec 의 결과를 얻을 수 있었다.