컬렉션 조회 최적화 : fetch join 적용

1. fetch join

---

fetch join을 적용해서 일대다 조회를 해보자.

   public List<Order> findAllWithItem(){

       List<Order> resultList = em.createQuery("select o from Order o " +
               "join fetch o.member m " +
               "join fetch o.delivery d " +
               "join fetch o.orderItems oi " +
               "join fetch oi.item i", Order.class).getResultList();

       return resultList;
   }

현재 db에 order는 2개, orderItem은 4개 가 있다. 일대다 관계이기 때문에

DB입장에서 ORDER,와 ORDER_ITEM을 JOIN하면 ORDER의 DATA가 불어난다.

select * from orders o
join order_item oi on o.order_id = oi.order_id;

즉 data가 중복된다.

 

 

실행해보자.

@GetMapping("/api/v3/orders")
    public Result ordersV3(){
       List<Order> orders = orderRepository.findAllWithItem();
        List<OrderDto> result = orders.stream().map(o -> new OrderDto(o)).collect(Collectors.toList());
        return new Result(result);
    }

?????????????orderId가 중복되네??? order의 data가 중복된다!!!

{
    "data": [
        {
            "orderId": 4,
            "name": "userA",
            "orderDate": "2021-08-24T18:58:11.607554",
            "orderStatus": "ORDER",
            "address": {
                "city": "서울",
                "street": "송파",
                "zipcode": "232"
            },
            "orderItems": [
                {
                    "itemName": "JPA1 BOOK",
                    "orderPrice": 10000,
                    "count": 1
                },
                {
                    "itemName": "JPA2 BOOK",
                    "orderPrice": 20000,
                    "count": 2
                }
            ]
        },
        {
            "orderId": 4,
            "name": "userA",
            "orderDate": "2021-08-24T18:58:11.607554",
            "orderStatus": "ORDER",
            "address": {
                "city": "서울",
                "street": "송파",
                "zipcode": "232"
            },
            "orderItems": [
                {
                    "itemName": "JPA1 BOOK",
                    "orderPrice": 10000,
                    "count": 1
                },
                {
                    "itemName": "JPA2 BOOK",
                    "orderPrice": 20000,
                    "count": 2
                }
            ]
        },
        {
            "orderId": 11,
            "name": "userB",
            "orderDate": "2021-08-24T18:58:11.687302",
            "orderStatus": "ORDER",
            "address": {
                "city": "부산",
                "street": "강남남",
                "zipcode": "222"
            },
            "orderItems": [
                {
                    "itemName": "spring1 BOOK",
                    "orderPrice": 10000,
                    "count": 3
                },
                {
                    "itemName": "spring2 BOOK",
                    "orderPrice": 20000,
                    "count": 4
                }
            ]
        },
        {
            "orderId": 11,
            "name": "userB",
            "orderDate": "2021-08-24T18:58:11.687302",
            "orderStatus": "ORDER",
            "address": {
                "city": "부산",
                "street": "강남남",
                "zipcode": "222"
            },
            "orderItems": [
                {
                    "itemName": "spring1 BOOK",
                    "orderPrice": 10000,
                    "count": 3
                },
                {
                    "itemName": "spring2 BOOK",
                    "orderPrice": 20000,
                    "count": 4
                }
            ]
        }
    ]
}

order가 뻥튀기 되지 않았으면 좋겠는데.....그대로였음 좋겠는데...

 

 

 

2. distinct

---

• distinct를 사용하면 중복된 일대다 join으로 인한 data의 중복을 제거할 수 있다.

  public List<Order> findAllWithItem(){

       List<Order> resultList = em.createQuery("select distinct o from Order o " +
               "join fetch o.member m " +
               "join fetch o.delivery d " +
               "join fetch o.orderItems oi " +
               "join fetch oi.item i", Order.class).getResultList();

       return resultList;
   }

• 오!!! order 두개만 나왔다. 이게 원하는 대로 나온것임.

{
    "data": [
        {
            "orderId": 4,
            "name": "userA",
            "orderDate": "2021-08-24T19:06:52.220779",
            "orderStatus": "ORDER",
            "address": {
                "city": "서울",
                "street": "송파",
                "zipcode": "232"
            },
            "orderItems": [
                {
                    "itemName": "JPA1 BOOK",
                    "orderPrice": 10000,
                    "count": 1
                },
                {
                    "itemName": "JPA2 BOOK",
                    "orderPrice": 20000,
                    "count": 2
                }
            ]
        },
        {
            "orderId": 11,
            "name": "userB",
            "orderDate": "2021-08-24T19:06:52.304241",
            "orderStatus": "ORDER",
            "address": {
                "city": "부산",
                "street": "강남남",
                "zipcode": "222"
            },
            "orderItems": [
                {
                    "itemName": "spring1 BOOK",
                    "orderPrice": 10000,
                    "count": 3
                },
                {
                    "itemName": "spring2 BOOK",
                    "orderPrice": 20000,
                    "count": 4
                }
            ]
        }
    ]
}

• 쿼리를 보면 select distinct가 추가된다.
• jpa에서 만든 쿼리를 db에서 바로 실행해보자. 어떤 차이가 있을까?

select
        distinct order0_.order_id as order_id1_6_0_,
        member1_.member_id as member_i1_4_1_,
        delivery2_.delivery_id as delivery1_2_2_,
        orderitems3_.order_item_id as order_it1_5_3_,
        item4_.item_id as item_id2_3_4_,
        order0_.delivery_id as delivery4_6_0_,
        order0_.member_id as member_i5_6_0_,
        order0_.order_date as order_da2_6_0_,
        order0_.status as status3_6_0_,
        member1_.city as city2_4_1_,
        member1_.street as street3_4_1_,
        member1_.zipcode as zipcode4_4_1_,
        member1_.name as name5_4_1_,
        delivery2_.city as city2_2_2_,
        delivery2_.street as street3_2_2_,
        delivery2_.zipcode as zipcode4_2_2_,
        delivery2_.status as status5_2_2_,
        orderitems3_.count as count2_5_3_,
        orderitems3_.item_id as item_id4_5_3_,
        orderitems3_.order_id as order_id5_5_3_,
        orderitems3_.order_price as order_pr3_5_3_,
        orderitems3_.order_id as order_id5_5_0__,
        orderitems3_.order_item_id as order_it1_5_0__,
        item4_.name as name3_3_4_,
        item4_.price as price4_3_4_,
        item4_.stock_quantity as stock_qu5_3_4_,
        item4_.artist as artist6_3_4_,
        item4_.etc as etc7_3_4_,
        item4_.author as author8_3_4_,
        item4_.isbn as isbn9_3_4_,
        item4_.actor as actor10_3_4_,
        item4_.director as directo11_3_4_,
        item4_.dtype as dtype1_3_4_ 
    from
        orders order0_ 
    inner join
        member member1_ 
            on order0_.member_id=member1_.member_id 
    inner join
        delivery delivery2_ 
            on order0_.delivery_id=delivery2_.delivery_id 
    inner join
        order_item orderitems3_ 
            on order0_.order_id=orderitems3_.order_id 
    inner join
        item item4_ 
            on orderitems3_.item_id=item4_.item_id

• 뭐여????DB에 직접 실행했더니 중복이 제거가 안되다???

• 그러면 JPA에서 뭔가를 추가로 해주는건가?
• JPA의 distinct와 DB의 distinct는 다르다!!
  • DB의 distinct : 데이터가 모두 같아야 중복을 제거해줌. 즉 한 줄이 모두 같아야 실행됨
  • JPA의 distinct : distinct가 있으면 해당 data의 id값이 같으면 중복으로 보고 중복된 데이터를 제거함 !!!!!
  • 즉 jpa아 한 번 더 걸러줌/ 크..
  • 쿼리도 한번으로 끝났다.. 캬... 
  • fetch join 적용으로 쿼리가 열 몇개 에서 한 개로 줄었다....크...
• 정리) distinct 사용한 이유
  
  • 1대다 조인이 있어서 join시 데이터베이스 row가 증가한다.
  •  따라서 order 엔티티의 조회 수도 증가.
  •  JPA의 distinct는 SQL에 distinct를 추가하고, 더해서 같은 엔티티(같은 id)가조회되면,
    애플리케이션에서 중복을 걸러준다. 
• 하지만 어마어마한 단점이 있음
  • 페이징(몇 번 째부터 몇 개 가져와)이 불가능!!!
  • 일대다를 fetch join하는 순간 페이징 쿼리가 안나감 어찌보면 당연

  public List<Order> findAllWithItem(){

       List<Order> resultList = em.createQuery("select distinct o from Order o " +
               "join fetch o.member m " +
               "join fetch o.delivery d " +
               "join fetch o.orderItems oi " +
               "join fetch oi.item i", Order.class)
               .setFirstResult(1)
               .setMaxResults(100)
               .getResultList();

       return resultList;
   }

offset 어디감"????????????????????????? 못찾겠는데??

Hibernate: 
    select
        distinct order0_.order_id as order_id1_6_0_,
        member1_.member_id as member_i1_4_1_,
        delivery2_.delivery_id as delivery1_2_2_,
        orderitems3_.order_item_id as order_it1_5_3_,
        item4_.item_id as item_id2_3_4_,
        order0_.delivery_id as delivery4_6_0_,
        order0_.member_id as member_i5_6_0_,
        order0_.order_date as order_da2_6_0_,
        order0_.status as status3_6_0_,
        member1_.city as city2_4_1_,
        member1_.street as street3_4_1_,
        member1_.zipcode as zipcode4_4_1_,
        member1_.name as name5_4_1_,
        delivery2_.city as city2_2_2_,
        delivery2_.street as street3_2_2_,
        delivery2_.zipcode as zipcode4_2_2_,
        delivery2_.status as status5_2_2_,
        orderitems3_.count as count2_5_3_,
        orderitems3_.item_id as item_id4_5_3_,
        orderitems3_.order_id as order_id5_5_3_,
        orderitems3_.order_price as order_pr3_5_3_,
        orderitems3_.order_id as order_id5_5_0__,
        orderitems3_.order_item_id as order_it1_5_0__,
        item4_.name as name3_3_4_,
        item4_.price as price4_3_4_,
        item4_.stock_quantity as stock_qu5_3_4_,
        item4_.artist as artist6_3_4_,
        item4_.etc as etc7_3_4_,
        item4_.author as author8_3_4_,
        item4_.isbn as isbn9_3_4_,
        item4_.actor as actor10_3_4_,
        item4_.director as directo11_3_4_,
        item4_.dtype as dtype1_3_4_ 
    from
        orders order0_ 
    inner join
        member member1_ 
            on order0_.member_id=member1_.member_id 
    inner join
        delivery delivery2_ 
            on order0_.delivery_id=delivery2_.delivery_id 
    inner join
        order_item orderitems3_ 
            on order0_.order_id=orderitems3_.order_id 
    inner join
        item item4_ 
            on orderitems3_.item_id=item4_.item_id

• hibernate가 경고메시지를 날렸다.

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

• 메모리에서 페이징처리했다고 한다.
• ????????????????
• 그렇다는 말은data가 10000000개라면 모두다 메모리로 올린다음에 메모리에서 페이징처리한다는 의미??
• .....................................멈춰!!! 왜이래
• 왜 이런 전략으로 설계가 되었을까. --> data의 왜곡을 방지하기 위해서.
• 위의 사례로 예를들면 order와 orderItem을 일대다 join하는 순간 order의 기준이 틀어진다.
• order가 2개있고 각각 orderItem이  2개가 있었다면, db입장에서 둘을 join하면 order가 4개가 된다.
• 이 상태에서 페이징 처리를 하면 특정 order의 orderItem이 조회시 누락이 된다.
• setFirsetResult(1).serMaxResults(100)을하면 orderId가 4인 order의 orderItem data가 하나 없는 것으로 보인다..
• 즉 한마디로 order는 2개인데 db상 join하게되면 order가 4개가되니 order기준으로 페이징 처리가 불가능하다..
• 그러니 싹다 메모리로 올려서 페이징처리하는것..
• 일대다 fetch join사용 시 페이징 하지 말자. 난리난다.

• 추가 주의사항 
  • 컬렉션 fetch join을 1개만 사용이 가능한다. 왜냐? 
  • 일대다 도 지금 데이터 뻥튀기되서 난리인데..
  • 일 - 다 - 다 해버리면..생각도하기 싫음.

Fetch Join2

 

3. GitHub : 210824 collection, fetch join

---