동일한 테이블을 여러 번 조인하는 방법과 서브쿼리를 사용하는 방법의 차이

동일한 테이블을 여러 번 조인하는 방법과 서브쿼리를 사용하는 방법의 차이(서브 쿼리가 느린 이유)

 

해당 포스팅은 동일한 테이블을 여러 번 조인하는 쿼리를 보면서 다른 방법은 없는지? 성능적 이슈는 없는지? 하는 의문에서 시작하였으며, 조인을 사용하는 방식과 서브쿼리를 사용하는 방식 각각의 이론적* 장단점을 정리해 본 내용입니다.

 

/*

실무에서의 쿼리는 대부분 아래 예시의 쿼리보다 복잡할 뿐만 아니라 다양한 케이스가 존재할 수 있으며, 데이터 양이나 인덱스 등의 변수로 인해서도 쿼리의 성능이 다르게 나올 수 있기 때문에 어떤 것이 정답이다 라고는 할 수 없습니다.

때문에 아래 내용은 이론에 기초한 장단점이며, 이론을 참고하되 성능적 부분은 실제 사용되는 쿼리의 실행 계획 등을 통해 확인할 필요가 있습니다.

*/

---

조인 방식과 서브쿼리를 사용하는 방식

SELECT p.product_id
     , p.product_name
     , p.main_category_id
     , mc.category_name AS main_category_name
     , p.sub_category_id
     , sc.category_name AS sub_category_name
  FROM product p
  LEFT JOIN category mc
    ON mc.category_id = p.main_category_id
  LEFT JOIN category sc
    ON sc.category_id = p.sub_category_id;

 

위 예시 쿼리와 같은 경우처럼 동일한 테이블을 여러 번 JOIN 하는 경우가 있을 수 있는데요.

 

 

SELECT p.product_id
     , p.product_name
     , p.main_category_id
     , (
        SELECT mc.category_name
          FROM category mc
         WHERE p.main_category_id = mc.category_id
       ) AS main_category_name
     , p.sub_category_id
     , (
        SELECT sc.category_name
          FROM category sc
         WHERE p.sub_category_id = sc.category_id
       ) AS sub_category_name
  FROM product p;

 

JOIN을 사용하지 않고 같은 결과를 가져오기 위해서는 다음과 같이 서브쿼리(스칼라 서브쿼리)를 사용하는 방법도 있습니다.

 

이처럼 JOIN을 사용하는 방식과 서브쿼리를 사용하는 방식, 두 방식 모두 위 예시와 같이 여러 개의 테이블로부터 데이터를 추출하기 위한 복잡한 쿼리문에 사용될 수 있는데요.

대부분의 경우 JOIN을 사용하는 방식이 훨씬 더 효율적이며, 때문에 여러 쿼리 튜닝 글에서도 조인으로 바꿀 수 있는 서브쿼리는 조인으로 변경하여 사용하는 것을 권장하는데 그 이유는 무엇일까요?

 

---

 서브쿼리를 사용하는 방식이 느린 이유

위와 같은 스칼라 서브쿼리(상호 연관 서브쿼리) 방식이 느린 핵심적인 이유는 바로 '메인 쿼리 결과의 건수만큼 서브쿼리가 반복'되기 때문인데요.

쿼리가 실행되는 순서는 다음과 같습니다.

 

1. 메인 쿼리(outer query)를 먼저 수행

2. 첫 번째 행을 후보행으로 잡고 스칼라 서브쿼리에 필요한 값을 전달

3. 후보행에서 전달된 값을 통해 서브쿼리를 수행 후, 서브쿼리의 입력 값(메인 쿼리에서 전달된 값)에 대한 출력 값을 메모리(query execution cache)에 저장

4. 다음 후보행을 잡고 필요한 값을 전달하여 서브쿼리를 실행하는데, 이때 해시함수를 통해 서브쿼리에 전달되는 입력 값이 메모리에 존재하는지 확인하여 있으면 해당 결과를 즉시 반환, 없으면 서브쿼리를 수행 후 메모리에 저장

5. 메인 쿼리 결과의 모든 행이 끝날 때까지 3-4 과정 반복

 

 

이처럼 메인 쿼리의 결과 건수만큼 서브쿼리가 반복되기 때문에 메인 쿼리를 통해 조회되는 데이터가 적은 경우에는 큰 차이가 없을 수 있지만, 조회되는 데이터가 많아지면 많아질수록 쿼리의 성능이 떨어지게 되는 것입니다.

(캐싱은 쿼리 단위로 이루어지며, 쿼리를 시작할 때 메모리에 공간을 할당하고 쿼리를 수행하는 동안 공간을 사용, 쿼리를 마치는 순간 메모리 공간을 반환하게 됩니다.)

 

 

서브쿼리를 사용하는 것이 빠른 경우도 있다?

위 쿼리 실행 순서에서 주목해 볼 수 있는 부분은 바로 '스칼라 서브쿼리의 캐싱'입니다.

 

만약 서브쿼리로 전달되는 입력 값의 종류가 적다면, 한번 요청된 입력 값에 대해서는 서브쿼리가 실행되지 않고 캐싱된 값을 가져올 수 있기 때문에 이를 통해 성능을 높일 수 있게 됩니다.

(예를 들어 위 쿼리에서 product 데이터가 10만 건이고 category 데이터가 10건이라면 실행될 수 있는 서브쿼리의 결과는 10건 밖에 안되기 때문에 캐싱 효과를 통한 성능 향상을 기대할 수 있습니다.)

 

하지만 서브쿼리로 전달되는 입력 값의 종류가 많은 경우, 계속 캐시를 확인해야 하는 비용 때문에 성능이 나빠지며, 메모리 사용률과 CPU 사용률도 높아지게 됩니다.

 

 

+ 추가적으로 서브쿼리를 사용하는 것은 복잡한 조인을 사용하는 것보다 쿼리를 짜는 측면이나 이해하는 측면에서도 더 쉬울 수 있으며, 복잡한 쿼리를 명확하게 구성할 수 있다는 가독성 측면의 장점도 있습니다.

 

 

---

정리하자면 물론 상황에 따라 성능적 부분을 고려하지 않아도 되는 쿼리도 있을 것이고, 옵티마이저가 서브쿼리를 조인으로 풀어서 실행해 주는 경우도 있을 것인데요.

결론적으로 왜 대부분의 경우 서브쿼리를 사용하는 것보다 조인을 사용하는 것이 더 효율적인지, 어떤 경우에 서브쿼리를 사용하는 것이 더 효율적일 수 있을지를 생각해 보며, 실제 쿼리에서 실행계획을 통해 결과를 확인해 보는 과정이 필요할 것 같습니다.

 

 

 

< 참고 자료 >

https://learnsql.com/blog/subquery-vs-join/
https://developer-anna.tistory.com/23
https://kimdubi.github.io/mysql/subquery_tuning/