QueryDsl left outer join N+1 문제?

공부는 혼자하는 거·2022년 5월 10일
0

Spring Tip

목록 보기
27/52

아래와 같은 형태의 엔티티 클래스가 있다고 가정하자.

@Slf4j
@ToString(exclude = {"restaurant", "orderItems", "customer", "delivery", "payment"})
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Builder(access = AccessLevel.PROTECTED)
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@Getter
@Entity
public class Order extends BaseTimeEntity {

    @Id //Pk
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Enumerated(EnumType.STRING)
    private OrderStatus orderStatus; //주문, 접수완료, 조리중, 조리완료

    private int totalPrice;

    //TableNum이 0이면 TakeOut
    private int tableNum;

    @Enumerated(EnumType.STRING)
    private OrderType orderType;

    @ManyToOne(fetch = FetchType.LAZY) //단방향
    private Restaurant restaurant;

    //orderitem만 단독으로 삭제하려면, order.orderitem.remove
    @JsonIgnore //무한순환참조 방지.
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<OrderItem> orderItems = new ArrayList<>();

    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer; //필수 아님

    @OneToOne(fetch = FetchType.LAZY, mappedBy = "order")
    private Delivery delivery;

    @OneToOne(fetch = FetchType.LAZY)
    private Payment payment;

    private LocalDateTime orderDate;

    private int orderAmount; //주문수량

}

쿼리는 아래와 같이 작성해줬다.

  @Override
    public Page<Order> findOrdersByRestaurant(Pageable pageable, long restaurantId, OrderStatus orderStatus, Long day) {

        List<Order> content = queryFactory
                .selectFrom(order)
                .join(order.payment, payment)
                .fetchJoin()
                .join(order.restaurant, restaurant)
                .fetchJoin()
                .leftJoin(order.customer, customer)
                .leftJoin(order.delivery, delivery)
                .where(order.restaurant.id.eq(restaurantId),
                        eqOrderStatus(orderStatus),
                        getBetweenOrderDate(day))
                .limit(pageable.getPageSize())
                .offset(pageable.getOffset())
                .orderBy(order.id.desc())
                .fetch();


        List<Order> countQuery = queryFactory
                .selectFrom(order)
                .join(order.payment, payment)
                .fetchJoin()
                .join(order.restaurant, restaurant)
                .fetchJoin()
                .leftJoin(order.customer, customer)
                .leftJoin(order.delivery, delivery)
                .where(order.restaurant.id.eq(restaurantId),
                        eqOrderStatus(orderStatus),
                        getBetweenOrderDate(day))
                .fetch();

        return new PageImpl<>(content, pageable, countQuery.size());
    }


현재 내 생각에 쿼리는 총 2번 날라갈 것으로 예측이 됐다. count query 랑, select query.
하지만 테스트 결과는...

    @Transactional
    @Test
    void findOrdersByRestaurantTest() {

        PageRequest of = PageRequest.of(0, 10);
        /**
         * why? n+1
         */
        Page<Order> ordersByRestaurant = orderRepository.findOrdersByRestaurant(of, 1L, null, null);
        //ordersByRestaurant.map(order -> order.toDto());
    }
HeFormatSql(P6Spy sql,Hibernate format):
    select
        order0_.`id` as id1_5_0_,
        payment1_.`id` as id1_7_1_,
        restaurant2_.`id` as id1_8_2_,
        order0_.`createTime` as createti2_5_0_,
        order0_.`modifiyTime` as modifiyt3_5_0_,
        order0_.`customer_id` as custome10_5_0_,
        order0_.`orderAmount` as orderamo4_5_0_,
        order0_.`orderDate` as orderdat5_5_0_,
        order0_.`orderStatus` as ordersta6_5_0_,
        order0_.`orderType` as ordertyp7_5_0_,
        order0_.`payment_id` as payment11_5_0_,
        order0_.`restaurant_id` as restaur12_5_0_,
        order0_.`tableNum` as tablenum8_5_0_,
        order0_.`totalPrice` as totalpri9_5_0_,
        payment1_.`createTime` as createti2_7_1_,
        payment1_.`modifiyTime` as modifiyt3_7_1_,
        payment1_.`paymentStatus` as payments4_7_1_,
        payment1_.`paymentType` as paymentt5_7_1_,
        payment1_.`receipt` as receipt6_7_1_,
        restaurant2_.`createTime` as createti2_8_2_,
        restaurant2_.`modifiyTime` as modifiyt3_8_2_,
        restaurant2_.`city` as city4_8_2_,
        restaurant2_.`street` as street5_8_2_,
        restaurant2_.`zipcode` as zipcode6_8_2_,
        restaurant2_.`name` as name7_8_2_,
        restaurant2_.`ownerName` as ownernam8_8_2_,
        restaurant2_.`ownerTel` as ownertel9_8_2_,
        restaurant2_.`restaurantTel` as restaur10_8_2_,
        restaurant2_.`useYn` as useyn11_8_2_ 
    from
        `
    Order` order0_ inner join
        `Payment` payment1_ 
            on order0_.`payment_id`=payment1_.`id` 
    inner join
        `Restaurant` restaurant2_ 
            on order0_.`restaurant_id`=restaurant2_.`id` 
    left outer join
        `Customer` customer3_ 
            on order0_.`customer_id`=customer3_.`id` 
    left outer join
        `Delivery` delivery4_ 
            on order0_.`id`=delivery4_.`order_id` 
    where
        order0_.`restaurant_id`=1 
    order by
        order0_.`id` desc limit 10 
Hibernate: select delivery0_.`id` as id1_2_0_, delivery0_.`createTime` as createti2_2_0_, delivery0_.`modifiyTime` as modifiyt3_2_0_, delivery0_.`city` as city4_2_0_, delivery0_.`street` as street5_2_0_, delivery0_.`zipcode` as zipcode6_2_0_, delivery0_.`deliveryStatus` as delivery7_2_0_, delivery0_.`order_id` as order_id8_2_0_ from `Delivery` delivery0_ where delivery0_.`order_id`=?
[2022-05-11 08:27:59:4941][main] INFO  p6spy p6spy logSQL 
 22.05.11 08:27:59 | OperationTime : 1ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        delivery0_.`id` as id1_2_0_,
        delivery0_.`createTime` as createti2_2_0_,
        delivery0_.`modifiyTime` as modifiyt3_2_0_,
        delivery0_.`city` as city4_2_0_,
        delivery0_.`street` as street5_2_0_,
        delivery0_.`zipcode` as zipcode6_2_0_,
        delivery0_.`deliveryStatus` as delivery7_2_0_,
        delivery0_.`order_id` as order_id8_2_0_ 
    from
        `Delivery` delivery0_ 
    where
        delivery0_.`order_id`=6 
Hibernate: select delivery0_.`id` as id1_2_0_, delivery0_.`createTime` as createti2_2_0_, delivery0_.`modifiyTime` as modifiyt3_2_0_, delivery0_.`city` as city4_2_0_, delivery0_.`street` as street5_2_0_, delivery0_.`zipcode` as zipcode6_2_0_, delivery0_.`deliveryStatus` as delivery7_2_0_, delivery0_.`order_id` as order_id8_2_0_ from `Delivery` delivery0_ where delivery0_.`order_id`=?
[2022-05-11 08:27:59:4945][main] INFO  p6spy p6spy logSQL 
 22.05.11 08:27:59 | OperationTime : 1ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        delivery0_.`id` as id1_2_0_,
        delivery0_.`createTime` as createti2_2_0_,
        delivery0_.`modifiyTime` as modifiyt3_2_0_,
        delivery0_.`city` as city4_2_0_,
        delivery0_.`street` as street5_2_0_,
        delivery0_.`zipcode` as zipcode6_2_0_,
        delivery0_.`deliveryStatus` as delivery7_2_0_,
        delivery0_.`order_id` as order_id8_2_0_ 
    from
        `Delivery` delivery0_ 
    where
        delivery0_.`order_id`=5 
Hibernate: select delivery0_.`id` as id1_2_0_, delivery0_.`createTime` as createti2_2_0_, delivery0_.`modifiyTime` as modifiyt3_2_0_, delivery0_.`city` as city4_2_0_, delivery0_.`street` as street5_2_0_, delivery0_.`zipcode` as zipcode6_2_0_, delivery0_.`deliveryStatus` as delivery7_2_0_, delivery0_.`order_id` as order_id8_2_0_ from `Delivery` delivery0_ where delivery0_.`order_id`=?
[2022-05-11 08:27:59:4946][main] INFO  p6spy p6spy logSQL 
 22.05.11 08:27:59 | OperationTime : 0ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        delivery0_.`id` as id1_2_0_,
        delivery0_.`createTime` as createti2_2_0_,
        delivery0_.`modifiyTime` as modifiyt3_2_0_,
        delivery0_.`city` as city4_2_0_,
        delivery0_.`street` as street5_2_0_,
        delivery0_.`zipcode` as zipcode6_2_0_,
        delivery0_.`deliveryStatus` as delivery7_2_0_,
        delivery0_.`order_id` as order_id8_2_0_ 
    from
        `Delivery` delivery0_ 
    where
        delivery0_.`order_id`=4 
Hibernate: select delivery0_.`id` as id1_2_0_, delivery0_.`createTime` as createti2_2_0_, delivery0_.`modifiyTime` as modifiyt3_2_0_, delivery0_.`city` as city4_2_0_, delivery0_.`street` as street5_2_0_, delivery0_.`zipcode` as zipcode6_2_0_, delivery0_.`deliveryStatus` as delivery7_2_0_, delivery0_.`order_id` as order_id8_2_0_ from `Delivery` delivery0_ where delivery0_.`order_id`=?
[2022-05-11 08:27:59:4948][main] INFO  p6spy p6spy logSQL 
 22.05.11 08:27:59 | OperationTime : 0ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        delivery0_.`id` as id1_2_0_,
        delivery0_.`createTime` as createti2_2_0_,
        delivery0_.`modifiyTime` as modifiyt3_2_0_,
        delivery0_.`city` as city4_2_0_,
        delivery0_.`street` as street5_2_0_,
        delivery0_.`zipcode` as zipcode6_2_0_,
        delivery0_.`deliveryStatus` as delivery7_2_0_,
        delivery0_.`order_id` as order_id8_2_0_ 
    from
        `Delivery` delivery0_ 
    where
        delivery0_.`order_id`=3 
Hibernate: select order0_.`id` as id1_5_0_, payment1_.`id` as id1_7_1_, restaurant2_.`id` as id1_8_2_, order0_.`createTime` as createti2_5_0_, order0_.`modifiyTime` as modifiyt3_5_0_, order0_.`customer_id` as custome10_5_0_, order0_.`orderAmount` as orderamo4_5_0_, order0_.`orderDate` as orderdat5_5_0_, order0_.`orderStatus` as ordersta6_5_0_, order0_.`orderType` as ordertyp7_5_0_, order0_.`payment_id` as payment11_5_0_, order0_.`restaurant_id` as restaur12_5_0_, order0_.`tableNum` as tablenum8_5_0_, order0_.`totalPrice` as totalpri9_5_0_, payment1_.`createTime` as createti2_7_1_, payment1_.`modifiyTime` as modifiyt3_7_1_, payment1_.`paymentStatus` as payments4_7_1_, payment1_.`paymentType` as paymentt5_7_1_, payment1_.`receipt` as receipt6_7_1_, restaurant2_.`createTime` as createti2_8_2_, restaurant2_.`modifiyTime` as modifiyt3_8_2_, restaurant2_.`city` as city4_8_2_, restaurant2_.`street` as street5_8_2_, restaurant2_.`zipcode` as zipcode6_8_2_, restaurant2_.`name` as name7_8_2_, restaurant2_.`ownerName` as ownernam8_8_2_, restaurant2_.`ownerTel` as ownertel9_8_2_, restaurant2_.`restaurantTel` as restaur10_8_2_, restaurant2_.`useYn` as useyn11_8_2_ from `Order` order0_ inner join `Payment` payment1_ on order0_.`payment_id`=payment1_.`id` inner join `Restaurant` restaurant2_ on order0_.`restaurant_id`=restaurant2_.`id` left outer join `Customer` customer3_ on order0_.`customer_id`=customer3_.`id` left outer join `Delivery` delivery4_ on order0_.`id`=delivery4_.`order_id` where order0_.`restaurant_id`=?
[2022-05-11 08:27:59:4969][main] INFO  p6spy p6spy logSQL 
 22.05.11 08:27:59 | OperationTime : 1ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        order0_.`id` as id1_5_0_,
        payment1_.`id` as id1_7_1_,
        restaurant2_.`id` as id1_8_2_,
        order0_.`createTime` as createti2_5_0_,
        order0_.`modifiyTime` as modifiyt3_5_0_,
        order0_.`customer_id` as custome10_5_0_,
        order0_.`orderAmount` as orderamo4_5_0_,
        order0_.`orderDate` as orderdat5_5_0_,
        order0_.`orderStatus` as ordersta6_5_0_,
        order0_.`orderType` as ordertyp7_5_0_,
        order0_.`payment_id` as payment11_5_0_,
        order0_.`restaurant_id` as restaur12_5_0_,
        order0_.`tableNum` as tablenum8_5_0_,
        order0_.`totalPrice` as totalpri9_5_0_,
        payment1_.`createTime` as createti2_7_1_,
        payment1_.`modifiyTime` as modifiyt3_7_1_,
        payment1_.`paymentStatus` as payments4_7_1_,
        payment1_.`paymentType` as paymentt5_7_1_,
        payment1_.`receipt` as receipt6_7_1_,
        restaurant2_.`createTime` as createti2_8_2_,
        restaurant2_.`modifiyTime` as modifiyt3_8_2_,
        restaurant2_.`city` as city4_8_2_,
        restaurant2_.`street` as street5_8_2_,
        restaurant2_.`zipcode` as zipcode6_8_2_,
        restaurant2_.`name` as name7_8_2_,
        restaurant2_.`ownerName` as ownernam8_8_2_,
        restaurant2_.`ownerTel` as ownertel9_8_2_,
        restaurant2_.`restaurantTel` as restaur10_8_2_,
        restaurant2_.`useYn` as useyn11_8_2_ 
    from
        `
    Order` order0_ inner join
        `Payment` payment1_ 
            on order0_.`payment_id`=payment1_.`id` 
    inner join
        `Restaurant` restaurant2_ 
            on order0_.`restaurant_id`=restaurant2_.`id` 
    left outer join
        `Customer` customer3_ 
            on order0_.`customer_id`=customer3_.`id` 
    left outer join
        `Delivery` delivery4_ 
            on order0_.`id`=delivery4_.`order_id` 
    where
        order0_.`restaurant_id`=1 

쿼리가 현재 가지고 있는 로우수만큼(지금은 4번) Delivery table에서 select를 해오지 않나!

쿼리를 아래와 같이 바꿨다.

        List<Order> content = queryFactory
                .selectFrom(order)
                .join(order.payment, payment)
                .fetchJoin()
                .join(order.restaurant, restaurant)
                .fetchJoin()
                .leftJoin(order.customer, customer)
                .leftJoin(order.delivery, delivery)
                .fetchJoin() //추가!!
                .where(order.restaurant.id.eq(restaurantId),
                        eqOrderStatus(orderStatus),
                        getBetweenOrderDate(day))
                .limit(pageable.getPageSize())
                .offset(pageable.getOffset())
                .orderBy(order.id.desc())
                .fetch();

이제는 예상대로 쿼리가 딱 2번 나간다. 흠.. fetch join을 제일 마지막에 해줘야 되나..

profile
시간대비효율

0개의 댓글