JPQL 서브쿼리 성능을 개선해보자. COUNT에서 HAVING, 그리고 NOT EXISTS까지

개발을 하다 보면 특정 조건을 만족하는 “유일한” 레코드만 조회해야 할 때가 있습니다. 최근 JPQL로 특정 VIN(vin_no) 차대번호에 대해 상태코드가 ‘60’인 계약이 딱 한 건만 존재하는 경우만 조회하는 쿼리를 작성하면서 이러한 상황을 겪었습니다. 초기 구현은 동작했지만 성능 병목이 있었고, 이를 해결하기 위해 점진적으로 쿼리를 개선해 나갔습니다. 이 글에서는 제가 COUNT 상관 서브쿼리 -> GROUP BY + HAVING -> NOT EXISTS 순으로 쿼리를 개선해 간 과정을 공유하고, 각각의 방식의 장단점을 살펴보겠습니다.

1단계: 상관 서브쿼리와 COUNT(*) = 1를 사용한 초기 접근

초기에는 상관 서브쿼리를 사용하여 해당 VIN에 상태코드 ‘60’인 계약 건수가 정확히 1개인지 검사했습니다. JPQL로는 아래와 같이 작성할 수 있습니다:

SELECT c
FROM Contract c
WHERE c.stateCode = '60'
  AND (SELECT COUNT(c2) 
       FROM Contract c2 
       WHERE c2.vinNo = c.vinNo AND c2.stateCode = '60'
      ) = 1

위 JPQL은 외부 쿼리의 각 계약 c에 대해 내부 서브쿼리로 같은 vin_no를 가진 상태코드 ‘60’ 계약 건수를 세어, 그것이 1인 경우만 결과에 포함합니다. 즉, “해당 VIN에 상태코드 60인 계약이 한 건뿐인 경우”를 만족하는 c만 조회하는 것입니다.

 

이 쿼리는 비교적 직관적입니다. 서브쿼리에서 COUNT를 사용해 조건을 직접 검사하므로 별도의 그룹핑이나 조인을 생각하지 않아도 되는 단순한 방법입니다. 아무 생각없이 이런 코드를 작성하고 DBA 분께 쿼리 검수를 받을 생각을 했다니 지금 돌이켜보면 부끄럽습니다.

 

 상관 서브쿼리 방식은 성능 상의 불리함이 있습니다. 위 쿼리에서 내부 COUNT 서브쿼리는 외부 쿼리의 각 행마다 실행될 수 있기 때문입니다 . 예를 들어 상태코드 ‘60’인 후보 레코드가 1,000건이라면, 서브쿼리가 최대 1,000번 반복 수행될 수 있습니다. 특히 COUNT()= 1 비교를 위해  서브쿼리가 해당 VIN의 모든 관련 레코드를 세어봐야 합니다. 만약 동일 VIN에 상태코드 ‘60’인 계약이 여러 건이라면, COUNT는 그 모두를 셀 때까지 멈추지 않습니다. 이는 데이터량이 많을수록 급격히 비효율적입니다.

 

참고로 EXISTS와 비교하면, EXISTS는 조건을 만족하는 첫 번째 행을 찾으면 즉시 종료할 수 있지만, COUNT는 모든 행을 끝까지 확인해야 하므로 더 느립니다 . 이러한 이유로 대용량 데이터에서는 COUNT 기반 검사보다 EXISTS 기반 검사가 유리합니다 .

(이걸 팀 동료가 알려줬는데, 모르고 있다는 사실이 매우 부끄러웠습니다..)

 

추가적으로 상관 서브쿼리는 DB 옵티마이저의 제약도 있습니다. 일부 DBMS에서는 상관 서브쿼리를 효과적으로 JOIN으로 변환하여 최적화하지만, 그렇지 못한 경우 인덱스가 있어도 매번 반복 조회하면서 성능이 떨어질 수 있습니다 . 요약하면, 상관 관계로 묶인 COUNT 서브쿼리는 작성은 쉬워도 큰 테이블에서는 응답 속도가 느려지는 함정이 있었습니다. 이러한 이유로 다음과 같은 개선을 시도했습니다.

2단계: GROUP BY + HAVING으로 유일한 레코드 필터링

성능 문제를 해결하기 위해 두 번째로 시도한 방법은 GROUP BY와 HAVING 절을 활용하는 것이었습니다. 하나의 쿼리 내에서 VIN 별로 그룹화하여 개수를 세고, 그중 COUNT가 1인 그룹만 선택하면 상관 서브쿼리 없이 원하는 결과를 얻을 수 있습니다. JPQL에서는 서브쿼리를 IN 절에 사용하여 다음처럼 리팩토링할 수 있었습니다:

SELECT c
FROM Contract c
WHERE c.stateCode = '60'
  AND c.vinNo IN (
       SELECT c2.vinNo 
       FROM Contract c2 
       WHERE c2.stateCode = '60'
       GROUP BY c2.vinNo
       HAVING COUNT(c2) = 1
  )

위 쿼리는 내부 서브쿼리로 상태코드 ‘60’인 계약들을 VIN별로 묶어 그 개수가 1인 VIN만 추출합니다.

그리고 외부 쿼리에서 그 VIN에 속하는 계약만 조회함으로써, 결과적으로 해당 VIN 내에 상태코드 ‘60’ 계약이 한 건뿐인 경우만 얻어집니다.

 

이 방법은 파생 테이블이나 Native SQL 없이 JPQL만으로 중복 필터링을 수행한다는 장점이 있습니다.

 

상관 서브쿼리와 달리, 내부 서브쿼리가 외부와 독립적으로 수행됩니다. 즉, 내부 쿼리는 한 번 실행되어 VIN별 건수를 계산하며, 그 결과를 외부 쿼리가 활용하므로 반복 실행 오버헤드가 줄어듭니다.

 

또한 GROUP BY + HAVING을 통해 집합 단위로 조건을 걸기 때문에 의도도 명확합니다. 주어진 조건에 부합하는 VIN을 먼저 선별한 뒤, 해당 VIN의 계약을 가져오니 논리적으로도 깔끔합니다.

 

 

하지만 이 방식에서는 내부 서브쿼리가 테이블을 한 번 풀스캔하여 그룹핑을 수행하므로, 데이터가 매우 큰 경우 비용이 있을 수 있습니다. 다만 이는 1단계의 상관 서브쿼리가 여러 번 테이블을 스캔할 수 있는 것에 비하면 훨씬 나은 상황입니다.

 

또한 JPQL의 한계로 인해, 내부 서브쿼리 결과를 외부에서 IN으로 사용하는 형태가 되었는데, DBMS가 이를 어떻게 최적화하느냐에 따라 성능이 좌우될 수 있다고 합니다. 대부분의 경우 인덱스가 있다면 VIN으로 조인하거나 세미 조인(semi-join) 방식으로 실행되어 효율적입니다.

 

이 방식은 성능을 어느 정도 개선해주었지만, Approve를 받기 위해서 더 개선할 수 없을까 하는 고민을 해보았습니다.

 

“정확히 1건”이라는 조건을 조금 다른 각도로 생각해 보면, “2건 이상인 경우를 제외”하는 것으로도 표현할 수 있습니다.(무릎을 탁!)

3단계: NOT EXISTS를 활용한 Anti-Join 최적화

최종으로 적용한 방법은 NOT EXISTS 서브쿼리를 사용하여 동일 VIN에 두 번째 상태코드 ‘60’ 계약이 존재하지 않음을 확인하는 쿼리입니다. 사실 이 쿼리는 동료가 같이 고민해주다가 제안해준 쿼리입니다.

SELECT c
FROM Contract c
WHERE c.stateCode = '60'
  AND NOT EXISTS (
       SELECT 1 
       FROM Contract c2
       WHERE c2.vinNo = c.vinNo 
         AND c2.stateCode = '60' 
         AND c2.id <> c.id
  )

위 JPQL을 해석하면, 외부의 계약 c에 대해 동일한 vin_no를 가지면서(c2.vinNo = c.vinNo) 상태코드 ‘60’인 다른 계약(c2.id <> c.id)이 존재하지 않을 경우에만 결과로 선택합니다.

즉, c와 같은 VIN의 두 번째 계약이 없다면(NOT EXISTS) c를 결과에 포함하는 것입니다. 이 로직은 곧 해당 VIN에 c 혼자만 상태코드 60인 계약임을 의미하므로, 우리가 원한 “한 VIN당 하나의 상태코드 60 계약” 조건을 만족합니다.

 

NOT EXISTS 방식은 존재 여부만 확인하므로 매우 효율적입니다. 내부 서브쿼리는 조건에 맞는 레코드를 찾으면 즉시 true/false를 반환하기 때문에, 굳이 모든 레코드를 세지 않고도 조건을 판단할 수 있습니다.

 

데이터베이스 옵티마이저도 종종 NOT EXISTS를 Anti-Join으로 변환하여 처리하는데, 이는 내부적으로 일종의 LEFT JOIN … IS NULL 방식으로 동작해 효율적인 필터링을 제공합니다.

 

결국 이 방식에서는 “두 번째 레코드가 있는지”만 확인하므로, 첫 번째 추가 레코드를 찾는 즉시 검사를 멈출 수 있습니다 . 이는 1단계의 COUNT 방식처럼 끝까지 세는 작업을 하지 않아 불필요한 연산을 줄여줍니다.

 

첫번째 쿼리는 매번 서브쿼리를 호출하지만, NOT EXISTS 쿼리는 그 단점을 상쇄할 수 있습니다.

상관 서브쿼리이긴 하지만, EXISTS 조건은 내부적으로 매우 빠르게 종료되므로 과도한 부하를 주지 않습니다. 특히 적절한 인덱스가 존재한다면 서브쿼리도 필요한 최소 범위만 조회하므로 효율적이라고 합니다.

(역시 인덱스를 잘 타도록 쿼리를 짜는게 백엔드 개발자의 실력이라는 것을 여실히 깨달았습니다.)

요약

최종적으로 3단계 쿼리가 가장 나은 성능을 보여주었습니다. 1단계의 상관 COUNT 서브쿼리는 가장 비효율적이었고, 2단계 그룹화 쿼리는 단일 서브쿼리로 크게 개선되었지만 모든 결과를 한 번 훑는 비용이 들었습니다. 3단계의 NOT EXISTS는 조인을 활용하는 것과 유사한 실행 계획으로 동작하여 최소한의 탐색으로 조건을 만족했습니다. 실제 테스트에서도 COUNT 서브쿼리보다 EXISTS 쿼리가 거의 두 배 가까이 빠른 성능을 보인 사례가 있으며 , EXISTS의 조기 종료 특성이 성능 향상의 핵심임을 확인할 수 있었습니다.

JPQL에서 LIMIT (페이징) 처리에 대한 팁

JPQL에서 LIMIT (페이징) 처리에 대한 팁

한 가지 추가로 언급하고 싶은 점은 JPQL 문법에서는 SQL처럼 LIMIT 절을 직접 사용할 수 없다는 점입니다. SQL에서는 LIMIT 0, 1000처럼 결과 개수를 제한할 수 있지만, JPQL 쿼리 문자열 안에 LIMIT을 넣으면 문법 오류가 발생합니다.

그렇다면 JPQL로 작성한 쿼리에 대해 페이징 처리는 어떻게 해야 할까요? 저는 최근까지도 모르고 있다가 사이드 프로젝트를 하면서 알게 되었습니다.

주로 Spring Data JPA의 Pageable 파라미터를 사용하는 방식으로 페이징을 처리합니다. Pageable은 페이지 번호, 페이지 크기, 정렬 방식 등을 캡슐화한 객체로, 리포지토리 메서드에 전달하면 JPA가 자동으로 쿼리에 LIMIT/OFFSET을 적용해 줍니다.

예시:

@Query("""
    SELECT new com.example.dto.MyDto(c.id, c.vinNo)
    FROM Contract c
    WHERE c.stateCode = :stateCode
""")
List<MyDto> findByStateCode(@Param("stateCode") String stateCode, Pageable pageable);

앗. 그리고 @Param("stateCode”)도 없으면 컴파일 오류는 나지 않지만 런타임에 오류가 난다는 사실도 주의해야 합니다 😂

마치며

이번 경험을 통해 쿼리 하나를 튜닝하는 데에도 여러 접근 방식이 있음을 배웠습니다. 단순한 상관 서브쿼리는 이해하기 쉽지만 큰 데이터셋에서는 함정이 될 수 있고, GROUP BY를 활용하면 한번에 집합 연산으로 처리하여 성능을 끌어올릴 수 있었습니다. 최종적으로는 NOT EXISTS를 통한 Anti-Join 패턴이 가장 효율적이었는데,

풀스캔은 죄악이다
라는 깨달음을 얻을 수 있었습니다.