아래와 같은 형태의 엔티티 클래스가 있다고 가정하자.
@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을 제일 마지막에 해줘야 되나..
시간대비효율