API개발 고급(컬렉션 조회 최적화)

• 주문조회 예시
  (1) ver1: 엔티티를 직접 노출

    @GetMapping("/api/v1/orders")
    public List<Order> orderV1() {
        List<Order> all = orderRepository.findAll(new OrderSearch());
        for (Order order : all) {
            order.getMember().getName(); //LAZY 강제 초기화
            order.getDelivery().getAddress(); //LAZY 강제 초기화

            List<OrderItem> orderItems = order.getOrderItems();
            orderItems.stream().forEach(o -> o.getItem().getName()); //LAZY 강제 초기화
        }
        return all;
    }

📌 엔티티의 외부 노출은 지양

(2) ver2: 엔티티를 DTO로 변환

 @GetMapping("/api/v2/orders")
    public List<OrderDto> orderV2() {
        List<Order> orders = orderRepository.findAll(new OrderSearch());
        List<OrderDto> collect = orders.stream()
                .map(o -> new OrderDto(o))
                .collect(Collectors.toList());

        return collect;
    }
    
     //==DTO==//
    @Data
    static class OrderDto {

        private Long orderId;
        private String name;
        private LocalDateTime orderDate;
        private OrderStatus orderStatus;
        //값타입은 그대로 노출해도 OK
        private List<OrderItemDto> orderItems;
        //OrderItem은 엔티티 => DTO 안에 엔티티가 있어도 안됨(엔티티를 감싸는 형태조차도 X)
        private Address address;

        public OrderDto(Order order) {
            orderId = order.getId();
            name = order.getMember().getName();
            orderDate = order.getOrderDate();
            orderStatus = order.getStatus();
            address = order.getDelivery().getAddress();
            //orderItem도 별도의 DTO로 변환처리
            orderItems = order.getOrderItems().stream()
                    .map(orderItem -> new OrderItemDto(orderItem))
                    .collect(Collectors.toList());
        }

    }

    @Data
    static class OrderItemDto {

        private String itemName;
        private int orderPrice;
        private int count;

        public OrderItemDto(OrderItem orderItem) {
            itemName = orderItem.getItem().getName();
            orderPrice = orderItem.getOrderPrice();
            count = orderItem.getCount();
        }
    }

📌 연관관계의 경우 마찬가지로 별도의 DTO로 변환 필요
- 엔티티를 껍데기 DTO로 감싸는 형태도 X
📌 N+1 문제가 발생(지연로딩으로 인해 너무 많은 sQL 실행)
- order(1번) + member(order 수만큼) + delivery(order 수만큼) + orderItem(order 수만큼) + item(orderItem 수만큼)

(3) ver3: fetch join 사용

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

        return collect;
    }

    public List<Order> findAllWithItem() {
        return 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();
    }

    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

📌 페치조인으로 SQL이 한 번만 실행
- distinct 사용 이유: 일대다 조인이 있어서 데이터베이스 row 증가
- JPA의 distinct: SQL에 distinct를 추가하고, 덧붙여 같은 엔티티가 조회될 경우 애플리케이션에서 중복을 걸러줌

📌 단점: 페이징이 불가능!!!

• 컬렉션을 페치조인할 경우 일대다 조인이 발생하여 데이터가 예측할 수 없이 증가함
• 일대다 조인으로 인해 row가 '다'에 맞춰서 많아짐(우리가 원하는건 '일'을 기준으로 페이징) => limit, offset 설정 기준을 '일'에 맞춰서 만들기 어려움 => hibernate는 경고 로그를 남기면서 모든 데이터를 DB에서 읽어보고 메모리에서 페이징 처리(very risky) => 이런 쿼리가 날아가면 메모리 부하 발생(outOfMemory 장애)
  - 결론적으로 일대다에서는 페이징 불가

📌 (참고)컬렉션에서는 페치조인은 1개만 사용 가능

(4) ver3.1: fetch join 사용 & 페이징 한계 돌파

• ToOne관계(OneToOne, ManyToOne)는 모두 페치조인 처리(row 수를 증가시키지 않으므로)
• 컬렉션은 지연로딩으로 조회
  • 단, 성능최적화를 위해 application.yml에 batchSize 설정(글로벌 설정)
  • 이 옵션을 추가할 경우 설정된 수만큼 프록시 객체를 한꺼번에 IN 쿼리로 조회
• 이러한 방법을 통해 어느정도 N+1 문제에서 해방

(참고) 개별로 설정하고 싶다면?

• @BatchSize 어노테이션 적용(컬렉션은 컬렉션 필드에, 엔티티는 엔티티 클래스에 적용)

//application.yml
    spring.jpa.properties.hibernate.default_batch_fetch_size: 100

//fetch join 사용 & 페이징 처리
    @GetMapping("/api/v3.1/orders")
    public List<OrderDto> orderV3_page(@RequestParam(value = "offset", defaultValue = "0") int offset,
                                       @RequestParam(value = "limit", defaultValue = "100") int limit) {
        //member,delivery만 페치조인으로 가져옴(toOne관계인것들만)
        List<Order> orders = orderRepository.findAllWithMemberDelivery(offset, limit);

        List<OrderDto> collect = orders.stream()
                .map(o -> new OrderDto(o))
                .collect(Collectors.toList());

        return collect;
    }

//fetch join + paging
    public List<Order> findAllWithMemberDelivery(int offset, int limit) {
        return em.createQuery("select o from Order o" +
                " join fetch o.member m" +
                " join fetch o.delivery d", Order.class)
                .setFirstResult(offset)
                .setMaxResults(limit)
                .getResultList();
    }

• 먼저 order,member,delivery 한꺼번에 조회(페치조인이므로)

    select
        order0_.order_id as order_id1_6_0_,
        member1_.member_id as member_i1_4_1_,
        delivery2_.delivery_id as delivery1_2_2_,
        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_ 
    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 limit ? offset ?

• 추가적으로 order를 돌면서 orderItem 조회

 select
        orderitems0_.order_id as order_id5_5_1_,
        orderitems0_.order_item_id as order_it1_5_1_,
        orderitems0_.order_item_id as order_it1_5_0_,
        orderitems0_.count as count2_5_0_,
        orderitems0_.item_id as item_id4_5_0_,
        orderitems0_.order_id as order_id5_5_0_,
        orderitems0_.order_price as order_pr3_5_0_ 
    from
        order_item orderitems0_ 
    where
        orderitems0_.order_id=?

• orderItem 속 item_id를 찾아서 IN쿼리 실행
  • batchSize 설정이 없었다면?
  • orderItem 수만큼 item을 찾는 쿼리 실행

    select
        item0_.item_id as item_id2_3_0_,
        item0_.name as name3_3_0_,
        item0_.price as price4_3_0_,
        item0_.stock_quantity as stock_qu5_3_0_,
        item0_.artist as artist6_3_0_,
        item0_.etc as etc7_3_0_,
        item0_.author as author8_3_0_,
        item0_.isbn as isbn9_3_0_,
        item0_.actor as actor10_3_0_,
        item0_.director as directo11_3_0_,
        item0_.dtype as dtype1_3_0_ 
    from
        item item0_ 
    where
        item0_.item_id in (
            ?, ?
        )

📌 쿼리 호출 수가 1 + N => 1 + 1로 최적화!!!

• IN쿼리로 바뀌기 때문에
• 페치조인에 비해서는 쿼리 호출 수가 조금 더 많아지지만 DB 데이터 전송량이 최적화됨
• 무엇보다도 컬렉션 페치조인이 불가능하던 "페이징 처리가 가능"!!!

📌 결론

• ToOne 관계는 페치조인을 해도 페이징에 영향을 주지 않으므로 페치조인을 통해 쿼리 수를 줄여서 해결!
• 나머지는 "spring.jpa.properties.hibernate.default_batch_fetch_size"로 최적화!

📌 (참고) 그렇다면 default_batch_fetch_size 설정값은?

• 100~1000 사이를 선택하는 것을 권장
• 단, 데이터베이스에 따라 IN절 파라미터를 제한하는 경우가 있으므로 확인 필요
• 또한 1000으로 잡을 경우 DB에서 애플리케이션으로 한번에 불러오면서 부하가 증가할 수 있음

(5) ver5: DTO 직접 조회 + 컬렉션 조회 최적화

• 루트 1번, 컬렉션 1번의 쿼리 실행
• ToOne 관계들을 먼저 조회하고 여기서 얻은 식별자 orderId로 ToMany관계인 OrderItem을 한꺼번에 조회
• 맵을 사용함으로써 매칭 성능 향상

@GetMapping("/api/v5/orders")
public List<OrderQueryDto> ordersV5() {
return orderQueryRepository.findAllByDto_optimization();
}

	//v4와의 차이점: 쿼리를 한 번 날린 뒤 메모리에서 맵으로 돌려서 세팅
    public List<OrderQueryDto> findAllByDto_optimization() {
        List<OrderQueryDto> result = findOrders(); //1번째 쿼리

        //아이디만 추출
        List<Long> orderIds = toOrderIds(result);

        Map<Long, List<OrderItemQueryDto>> orderItemMap = findOrderItemMap(orderIds); //2번째 쿼리(IN쿼리)

        result.forEach(o -> o.setOrderItems(orderItemMap.get(o.getOrderId())));

        return result;

    }
    
 	private List<OrderQueryDto> findOrders() {
        return em.createQuery(
                        "select new jpabook.jpashop.repository.order.query.OrderQueryDto(o.id, m.name, o.orderDate, o.status, d.address)" +
                                " from Order o" +
                                " join o.member m" +
                                " join o.delivery d", OrderQueryDto.class)
                .getResultList();
    }
    
     private static List<Long> toOrderIds(List<OrderQueryDto> result) {
        return result.stream().map(o -> o.getOrderId()).collect(Collectors.toList());
    }
    
    
    private Map<Long, List<OrderItemQueryDto>> findOrderItemMap(List<Long> orderIds) {
        List<OrderItemQueryDto> orderItems = em.createQuery("select new jpabook.jpashop.repository.order.query.OrderItemQueryDto(oi.order.id, i.name, oi.orderPrice, oi.count)" +
                        " from OrderItem oi" +
                        " join oi.item i" +
                        " where oi.order.id in :orderIds", OrderItemQueryDto.class)
                .setParameter("orderIds", orderIds)
                .getResultList();

        //맵으로 변환
        Map<Long, List<OrderItemQueryDto>> orderItemMap = orderItems.stream()
                .collect(Collectors.groupingBy(orderItemQueryDto -> orderItemQueryDto.getOrderId()));
        return orderItemMap;
    }
    

(6) ver6: DTO 직접 조회 + 플랫 데이터 최적화

• 쿼리 1번 실행을 통해 필요한 모든 데이터 조회
• 단점
  • 조인이 많이 발생하여 상황에 따라 ver5보다 조회 성능이 느릴 수 있음
  • 페이징 처리가 불가능(일대다 조인이 있으므로)

@GetMapping("/api/v6/orders")
    public List<OrderQueryDto> orderV6() {
        List<OrderFlatDto> flats = orderQueryRepository.findAllByDto_flat();
        return flats.stream()
                .collect(groupingBy(o -> new OrderQueryDto(o.getOrderId(),
                                o.getName(), o.getOrderDate(), o.getOrderStatus(), o.getAddress()),
                        mapping(o -> new OrderItemQueryDto(o.getOrderId(),
                                o.getItemName(), o.getOrderPrice(), o.getCount()), toList())
                )).entrySet().stream()
                .map(e -> new OrderQueryDto(e.getKey().getOrderId(),
                        e.getKey().getName(), e.getKey().getOrderDate(), e.getKey().getOrderStatus(),
                        e.getKey().getAddress(), e.getValue()))
                .collect(toList());
    }

@Data
@EqualsAndHashCode(of = "orderId") //groupby 할 때 묶어주는 기능 명시
public class OrderQueryDto {
    private Long orderId;
    private String name;
    private LocalDateTime orderDate;
    private OrderStatus orderStatus;
    private Address address;
    private List<OrderItemQueryDto> orderItems;

    public OrderQueryDto() {
    }

    public OrderQueryDto(Long orderId, String name, LocalDateTime orderDate, OrderStatus orderStatus, Address address) {
        this.orderId = orderId;
        this.name = name;
        this.orderDate = orderDate;
        this.orderStatus = orderStatus;
        this.address = address;
    }


//생성자 추가
    public OrderQueryDto(Long orderId, String name, LocalDateTime orderDate, OrderStatus orderStatus, Address address, List<OrderItemQueryDto> orderItems) {
        this.orderId = orderId;
        this.name = name;
        this.orderDate = orderDate;
        this.orderStatus = orderStatus;
        this.address = address;
        this.orderItems = orderItems;
    }
}

 public List<OrderFlatDto> findAllByDto_flat() {
        return em.createQuery("select new" +
                " jpabook.jpashop.repository.order.query.OrderFlatDto(o.id, m.name, o.orderDate, o.status, d.address, i.name, oi.orderPrice, oi.count)" +
                " from Order o" +
                " join o.member m" +
                " join o.delivery d" +
                " join o.orderItems oi" +
                " join oi.item i", OrderFlatDto.class)
                .getResultList();
        //쿼리 1회에 모든 필요 데이터 조회(장점)

    }

@Data
public class OrderFlatDto {

    private Long orderId;
    private String name;
    private LocalDateTime orderDate;
    private OrderStatus orderStatus;
    private Address address;

    private String itemName;
    private int orderPrice;
    private int count;

    public OrderFlatDto(Long orderId, String name, LocalDateTime orderDate, OrderStatus orderStatus, Address address, String itemName, int orderPrice, int count) {
        this.orderId = orderId;
        this.name = name;
        this.orderDate = orderDate;
        this.orderStatus = orderStatus;
        this.address = address;
        this.itemName = itemName;
        this.orderPrice = orderPrice;
        this.count = count;
    }
}