@Fetch(FetchMode.SUBSELECT) 과 IN subquery 는 왜 느릴까?

그림 1 회사에서 받은 이메일 하나

들어가기

JPA 를 활용하다가 문제를 하나 맞딱뜨렸습니다. 퇴근하기 10분전, 동료분의 호출로 위 그림처럼 시간이 오래걸리는 경우가 발생했습니다. 처음 보는 코드에서 어디서 발생했는지 알 수 없는 상황에서 겉으로 보기에는 아무런 문제가 없었습니다. 

그러던 중, 코드를 살펴보던 중 처음 보는 어노테이션 @Fetch(FetchMode.SUBSELECT) 가 있었습니다.

@Fetch(FetchMode.SUBSELECT) 이란?

 JPA를 활용하다보면, N+1 문제는 늘 발생할 수 있는 문제입니다. N+1 문제를 해결하기 위해 다양한 방법 있습니다. 그리고, 그 가운데 Fetch(FetchMode.SUBSELECT) 이 있습니다.

그럼 이 애노테이션은 어떻게 N+1 문제를 해결할까요?

백기선님의 블로그를 참고하면 아래와 같은 내용이 있습니다.

굳이 배치 사이즈를 줄 필요없을 때 활용한다.

또한 Fetch(FetchMode.SUBSELECT) 은 연관된 데이터를 조회할 때 서브쿼리를 사용해서 N+1 문제를 해결합니다.

그럼 이 Fetch(FetchMode.SUBSELECT) 이 N+1 을 해결하는 것이라면, 서브 쿼리가 오래 걸린 이유는 무엇일까요?

원인

mysql에서 in subquery에 대해서 아래와 같은 내용이 있었습니다.

IN subquery 로 동작되는 코드의 경우 느릴 수 밖에 없다고 합니다.

만약, 30만명의 고객(customer)와 300만개의 주문(orders)가 존재한다고 가정합니다.

mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|  3000000 |
+----------+
1 row in set (0.46 sec)

우선 2명의 고객 정보를 조회합니다.

mysql> SELECT c_custkey FROM customer LIMIT 2;
+-----------+
| c_custkey |
+-----------+
|        29 |
|        48 |
+-----------+
2 rows in set (0.00 sec)

이들이 주문한 주문 정보를 조회하는 것은 다음과 같이 조회하면 된다. o_custkey가 INDEX로 걸려있기 때문에 수행 속도가 빠릅니다.

mysql> SELECT SQL_NO_CACHE o_orderkey FROM orders WHERE o_custkey IN (29, 48);
+------------+
| o_orderkey |
+------------+
|    1572644 |
|    2773829 |
|    3440902 |
|    5779717 |
|    6144551 |
|    9493348 |
|    9974083 |
+------------+
7 rows in set (0.00 sec)

하지만, 쿼리를 다음과 같이 변경하면 동일한 결과가 출력되지만 3.12초나 걸린다고 합니다.

mysql> SELECT SQL_NO_CACHE o_orderkey
    -> FROM orders
    -> WHERE o_custkey IN
         (SELECT c_custkey FROM customer WHERE c_custkey IN (29, 48));
+------------+
| o_orderkey |
+------------+
|    1572644 |
|    2773829 |
|    3440902 |
|    5779717 |
|    6144551 |
|    9493348 |
|    9974083 |
+------------+
7 rows in set (3.12 sec)

왜 그럴까요?

in subquery 작동 원리

우리가 알고 있는 사칙연산에서 곱하기, 나누기가 먼저 수행되고, 더하기, 빼기가 수행되는 우선순위 규칙이 명확히 있습니다. 마찬가지로 예상은 subquery가 먼저 동작되고 난 뒤, main query 가 동작될 것이라고 예상했습니다.

그러나, 실제는 그렇지 않습니다.

in query 동작 원리

IN ( ? ? ? ) EXPLAIN 결과

mysql> EXPLAIN SELECT SQL_NO_CACHE o_orderkey FROM orders WHERE o_custkey IN (29, 48);
+----+-------------++-----------+---------+------+------+--------------------------+
| id | select_type || key       | key_len | ref  | rows | Extra                    |
+----+-------------++-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      || o_custkey | 4       | NULL |    8 | Using where; Using index |
+----+-------------++-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

IN subquery의 EXPLAIN 결과

mysql> EXPLAIN SELECT SQL_NO_CACHE o_orderkey
    -> FROM orders
    -> WHERE o_custkey IN
    ->    (SELECT c_custkey FROM customer WHERE c_custkey IN (29, 48));
+----+--------------------+----------++-----------+---------+---------+--------------------------+
| id | select_type        | table    || key       | key_len | rows    | Extra                    |
+----+--------------------+----------++-----------+---------+---------+--------------------------+
|  1 | PRIMARY            | orders   || o_custkey | 4       | 3001636 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | customer || PRIMARY   | 4       |       1 | Using index; Using where |
+----+--------------------+----------++-----------+---------+---------+--------------------------+
2 rows in set (0.00 sec)

 예상한 대로라면, subquery 가 나온 결과물로 rows가 8개로 나와야 하는데, 실제로는 3001636 개

즉, Full Scan 이 실행되었습니다. 즉, 느릴수 밖에 없는거죠.

우리는 여기서 DEPENDENT SUBQUERY 를 주의 깊게 볼 필요가 있습니다.

DEPENDENT SUBQUERY 이란?

"의존적 서브쿼리" 이는 상위 테이블 결과에 의존적이라는 이야기이며, 상위 테이블의 매 레코드마다 1번씩 subquery 결과를 비교를 하게 됩니다.

그래서 이를 회피하는 방법으로

- IN()은 INNER JOIN 혹은 EXISTS 로 변환 가능합니다.

- NOT IN() 은 LEFT OUTER JOIN 혹은 NOT EXISTS로 변환

 

 

 

[참고자료]

www.baeldung.com/hibernate-fetchmode

whiteship.tistory.com/2373

jason-heo.github.io/mysql/2014/05/22/avoid-mysql-in.html