[최종프로젝트]쿼리 개선

기존의 코드에서 2N의 쿼리가 발생하는 것을 확인했다.
아래 코드는 상품관리자가 상품 조회를 하는 api를 요청했을 때의 코드이다.

private List<ProductAdminResponse> getAdminPageResponse(Page<Product> productPage) {
        return productPage.getContent().stream()
            .map(product -> {
                List<OrderDetail> orderDetails = feignOrderClient.findOrderDetailsByProductId(
                    product.getId());
                List<OrderDetailAdminResponse> orderDetailResponseDtos = new ArrayList<>();
                for (OrderDetail orderDetail : orderDetails) {
                    Order order = feignOrderClient.getById(orderDetail.getOrderId());
                    orderDetailResponseDtos.add(new OrderDetailAdminResponse(orderDetail, order));
                }
                return new ProductAdminResponse(product, orderDetailResponseDtos);
            })
            .collect(Collectors.toList());
    }

10개의 상품 각각 주문 1개씩 존재할 때
product쪽 쿼리 : 2개,
order쪽 쿼리 : 20개
실행시간 : 62~106ms (대부분 7,80ms)

실제 발생한 쿼리

Product 발생 쿼리
Hibernate: 
    /* <criteria> */ select
        p1_0.id,
        p1_0.created_at,
        p1_0.description,
        p1_0.image_url,
        p1_0.modified_at,
        p1_0.name,
        p1_0.price,
        p1_0.state,
        p1_0.stock,
        p1_0.user_id 
    from
        product p1_0 
    where
        p1_0.user_id=? 
        and p1_0.state 
    limit
        ?, ?
Hibernate: 
    /* <criteria> */ select
        count(p1_0.id) 
    from
        product p1_0 
    where
        p1_0.user_id=? 
        and p1_0.state

Order 발생 쿼리

Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?
Hibernate: 
    /* <criteria> */ select
        od1_0.id,
        od1_0.order_id,
        od1_0.price,
        od1_0.product_id,
        od1_0.product_name,
        od1_0.quantity,
        od1_0.reviewed 
    from
        order_detail od1_0 
    where
        od1_0.product_id=?
Hibernate: 
    select
        o1_0.id,
        o1_0.kakao_tid,
        o1_0.address_id,
        o1_0.created_at,
        o1_0.modified_at,
        o1_0.state,
        o1_0.user_id 
    from
        orders o1_0 
    where
        o1_0.id=?

• 문제점 : stream으로 돌며 productPage에서 꺼낸 product를 하나씩 가져오는데 그 안에서 또 for문을 돌며 쿼리를 발생시킴!

• 해결책 :

  • for문 안에서 도는 쿼리를 해결
  • stream으로 도는 부분을 List<Product>로 받아 해결

아래는 개선 된 코드이다.

private List<ProductAdminResponse> getAdminPageResponse(Page<Product> productPage) {
        // product를 1페이지에 나타낼 수 있는 만큼 가져오기
        List<Product> productList = productPage.getContent();

        // 해당 페이지의 product들의 모든 ID를 productIdList에 담기
        List<Long> productIdList = new ArrayList<>();
        for (Product product : productList) {
            productIdList.add(product.getId());
        }

        // productIdList 안에 담긴 상품들 중 주문된 orderDetail을 orderDetailList에 담기
        List<OrderDetail> orderDetailList = feignOrderClient.findOrderDetailsByProductId(
            productIdList);

        // orderDetail의 orderId를 orderIdList에 담기
        Map<Long, Long> orderIdMap = new HashMap<>();
        for (OrderDetail orderDetail : orderDetailList) {
            orderIdMap.put(orderDetail.getOrderId(), orderDetail.getOrderId());
        }
        List<Long> orderIdList = new ArrayList<>();
        for(Map.Entry<Long, Long> entry : orderIdMap.entrySet()){
            orderIdList.add(entry.getKey());
        }

        // orderIdList 안에 담긴 orderId로 orderList에 담기
        Map<Long, Order> orderList = feignOrderClient.getAllById(orderIdList);

        // orderDetailList와 orderList에서 각각 한개씩 꺼내서 OrderDetailAdminResponse에 담기
        List<OrderDetailAdminResponse> orderDetailResponseDtos = new ArrayList<>();

        for (OrderDetail orderDetail : orderDetailList) {
            orderDetailResponseDtos.add(
                new OrderDetailAdminResponse(orderDetail, orderList.get(orderDetail.getOrderId())));
        }

        Map<Long, List<OrderDetailAdminResponse>> orderDetailResponseMap = new HashMap<>();

        for(OrderDetailAdminResponse orderDetail : orderDetailResponseDtos){
            Long productId = orderDetail.getProductId();
            if(!orderDetailResponseMap.containsKey(productId)){
                orderDetailResponseMap.put(productId, new ArrayList<>());
            }
            orderDetailResponseMap.get(productId).add(orderDetail);
        }

        List<ProductAdminResponse> productAdminResponseList = new ArrayList<>();

        for (Product product : productList){
            List<OrderDetailAdminResponse> orderDetailsForProduct = orderDetailResponseMap.get(product.getId());
            productAdminResponseList.add(
                new ProductAdminResponse(product, orderDetailsForProduct));
        }

        return productAdminResponseList;
    }

같은 조건으로
product 쪽 쿼리 : 2개,
order 쪽 쿼리 : 2개
실행시간 : 22~47ms (대부분 30ms 초중반)

실제 발생한 쿼리

Product 발생 쿼리
Hibernate: 
    /* <criteria> */ select
        p1_0.id,
        p1_0.created_at,
        p1_0.description,
        p1_0.image_url,
        p1_0.modified_at,
        p1_0.name,
        p1_0.price,
        p1_0.state,
        p1_0.stock,
        p1_0.user_id 
    from
        product p1_0 
    where
        p1_0.user_id=? 
        and p1_0.state 
    limit
        ?, ?
Hibernate: 
    /* <criteria> */ select
        count(p1_0.id) 
    from
        product p1_0 
    where
        p1_0.user_id=? 
        and p1_0.state

Order 발생 쿼리
Hibernate: 
    /* SELECT
        od 
    FROM
        OrderDetail od 
    WHERE
        od.productId IN (:productIdList) */ select
            od1_0.id,
            od1_0.order_id,
            od1_0.price,
            od1_0.product_id,
            od1_0.product_name,
            od1_0.quantity,
            od1_0.reviewed 
        from
            order_detail od1_0 
        where
            od1_0.product_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    /* SELECT
        o 
    FROM
        
    Order o WHERE
        o.id IN (:orderIdList) */ select
            o1_0.id,
            o1_0.kakao_tid,
            o1_0.address_id,
            o1_0.created_at,
            o1_0.modified_at,
            o1_0.state,
            o1_0.user_id 
        from
            orders o1_0 
        where
            o1_0.id in (?)

10개의 상품 조회에서도 약 60%의 성능개선을 확인했다. 상품 판매자는 보다 훨씬 많은 상품과 주문을 조회하므로 보다 큰 성능 향상을 얻을 수 있다.