728x90
컬렉션 조회 최적화
컬렉션같은 x to many의 경우 데이터가뻥튀기 되어서 돌아온다.
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();
} @GetMapping("/api/v3/orders")
public Result ordersV3()
{
List<Order> orders = orderRepository.findAllWithItem();
List<OrderDto> result = orders.stream()
.map(OrderDto::new)
.collect(Collectors.toList());
return new Result(result,result.size());
}
@Data
@AllArgsConstructor
static class Result<T>
{
private T data;
private int count;
}
@Getter
static class OrderDto
{
private Long orderId;
private String name;
private LocalDateTime orderDate;
private OrderStatus orderStatus;
private Address address;
private List<OrderItemDto> orderItems;
public OrderDto(Order order)
{
orderId = order.getId();
name = order.getMember().getName();
orderDate = order.getOrderDate();
orderStatus = order.getOrderStatus();
address = order.getDelivery().getAddress();
orderItems = order.getOrderItems().stream()
.map(OrderItemDto::new)
.collect(Collectors.toList());
}
}
@Getter
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();
}
}fetch join 할때 distinct 를 넣어주면 sql 과 다르게 jpql은 application level에서 중복을 제거해준다.
{
"data": [
{
"orderId": 4,
"name": "userA",
"orderDate": "2021-04-05T14:39:48.266819",
"orderStatus": "ORDER",
"address": {
"city": "서울",
"street": "1",
"zipcode": "1111"
},
"orderItems": [
{
"itemName": "JPA1 Book",
"orderPrice": 10000,
"count": 1
},
{
"itemName": "JPA2 Book",
"orderPrice": 20000,
"count": 2
}
]
},
{
"orderId": 11,
"name": "userB",
"orderDate": "2021-04-05T14:39:48.44082",
"orderStatus": "ORDER",
"address": {
"city": "진주",
"street": "2",
"zipcode": "2222"
},
"orderItems": [
{
"itemName": "Spring1 Book",
"orderPrice": 10000,
"count": 1
},
{
"itemName": "Spring2 Book",
"orderPrice": 20000,
"count": 2
}
]
}
],
"count": 2
}알맞게 중복이제거되어 2개가 제대로 나온것을 확인할수 있다. 하지만 이제 distinct 를 썼으니 페이징을 할 수 가없다. 하더라도 뜻대로 동작하지 않을 수 있다.
컬렉션 패치조인은 1개만 사용할 수 있다. 컬렉셔 ㄴ둘이상에 패치 조인을 사용하면 안된다. 데이터가 이상하게 조합될수 있다. 데이터가 완전히 엄청 뻥튀기 된다. 1 * M * N 이런식으로 커진다. 나중에는 뭘 기준으로 객체 그래프를 끌고와야하는지 모르게 될 수 있다.
@BatchSize
@GetMapping("/api/v3.1/orders")
public Result ordersV3_page(
@RequestParam(value = "offset", defaultValue = "0") int offset,
@RequestParam(value = "limit",defaultValue = "100") int limit
)
{
List<Order> orders = orderRepository.findAllWithMemberDelivery(offset, limit);
List<OrderDto> result = orders.stream()
.map(OrderDto::new)
.collect(Collectors.toList());
return new Result(result,result.size());
} public List<Order> findAllWithMemberDelivery(int offset, int limit)
{
return em.createQuery(
"select o from Order o " +
"join fetch o.member " +
"join fetch o.delivery d", Order.class)
.setFirstResult(offset)
.setMaxResults(limit)
.getResultList();
}현재 이것은 컬렉셔 fetch join이 아니고 (many to one , one to one) 이기 때문에 페이징이 오류없이 된다 하지만
Hibernate:
select
order0_.id as 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_.order_status as order_st3_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_.delivery_status as delivery5_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 ?
2021-04-05 18:26:04.286 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764286 | took 4ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select order0_.id as 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_.order_status as order_st3_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_.delivery_status as delivery5_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 ?
select order0_.id as 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_.order_status as order_st3_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_.delivery_status as delivery5_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 100;
Hibernate:
select
orderitems0_.order_id as order_id5_5_0_,
orderitems0_.order_item_id as order_it1_5_0_,
orderitems0_.order_item_id as order_it1_5_1_,
orderitems0_.count as count2_5_1_,
orderitems0_.item_id as item_id4_5_1_,
orderitems0_.order_id as order_id5_5_1_,
orderitems0_.order_price as order_pr3_5_1_
from
order_item orderitems0_
where
orderitems0_.order_id=?
2021-04-05 18:26:04.311 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764311 | took 1ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=?
select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=4;
Hibernate:
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=?
2021-04-05 18:26:04.336 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764336 | took 2ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
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=?
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=2;
Hibernate:
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=?
2021-04-05 18:26:04.339 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764339 | took 0ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
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=?
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=3;
Hibernate:
select
orderitems0_.order_id as order_id5_5_0_,
orderitems0_.order_item_id as order_it1_5_0_,
orderitems0_.order_item_id as order_it1_5_1_,
orderitems0_.count as count2_5_1_,
orderitems0_.item_id as item_id4_5_1_,
orderitems0_.order_id as order_id5_5_1_,
orderitems0_.order_price as order_pr3_5_1_
from
order_item orderitems0_
where
orderitems0_.order_id=?
2021-04-05 18:26:04.341 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764341 | took 0ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=?
select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=11;
Hibernate:
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=?
2021-04-05 18:26:04.344 INFO 18440 --- [nio-8084-exec-2] p6spy : #1617614764344 | took 0ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
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=?
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=9;
Hibernate:
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=?첫번쨰 fetch join쿼리가 나가고 유저당 아이템이 2개씩 존재함으로
첫번쨰 유저 Orderitem 프록시 초기화 (1) + 아이템 프록시 초기화 (2)
두번쨰 유저도 마찬가지 총 7번의쿼리가 나가게 되는 N+1 의 문제가 발생해버린다.
@Batchsize 나 application.yml 에서 jpa.hibernate.default_batch_fetch_size 글로벌 하게 설정을 해주면
in query를 지정해준 숫자만큼 날려준다. XtoOne관계들은 fetch join으로 잡아주고 나머지는 in query로 잡아오는게 best다.
결론 = toOne관계는 fetch join 해도 페이징에 영향을 주지 않는다. 나머지는 batch 로 최적화 하자.
100~1000 사이가 적당할 것이다. 너무 적으면 쿼리가 많이 나가게 됨으로. 100 이든 1000이든 어차피 전체 데이터를 로딩할떄까지 loop 가 blocking 되고 있기때문에 메모리 사용량은 같다.
JPA에서 DTO 직접 조회
public class OrderQueryRepository
{
private final EntityManager em;
public List<OrderQueryDto> findOrderQueryDtos()
{
List<OrderQueryDto> result = findOrders();
result.forEach(
o ->
{
List<OrderItemQueryDto> orderItems = findOrderItems(o.getOrderId());
o.setOrderItems(orderItems);
}
);
return result;
}
private List<OrderItemQueryDto> findOrderItems(Long orderId)
{
return 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 = :orderId",OrderItemQueryDto.class)
.setParameter("orderId",orderId)
.getResultList();
}
private List<OrderQueryDto> findOrders()
{
return em.createQuery(
"select new jpabook.jpashop.repository.order.query.OrderQueryDto(o.id, m.name, o.orderDate , o.orderStatus, d.address)" +
" from Order o" +
" join o.member m" +
" join o.delivery d",OrderQueryDto.class)
.getResultList();
}toOne 관계는 조인해도 데이터수가 증가하지않는다. toMany경우 증가하게됨으로 loop를 돌면서 일일이 채워 넣었다.
Hibernate:
select
order0_.id as col_0_0_,
member1_.name as col_1_0_,
order0_.order_date as col_2_0_,
order0_.order_status as col_3_0_,
delivery2_.city as col_4_0_,
delivery2_.street as col_4_1_,
delivery2_.zipcode as col_4_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
2021-04-06 14:00:59.265 INFO 10992 --- [nio-8084-exec-1] p6spy : #1617685259265 | took 3ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select order0_.id as col_0_0_, member1_.name as col_1_0_, order0_.order_date as col_2_0_, order0_.order_status as col_3_0_, delivery2_.city as col_4_0_, delivery2_.street as col_4_1_, delivery2_.zipcode as col_4_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
select order0_.id as col_0_0_, member1_.name as col_1_0_, order0_.order_date as col_2_0_, order0_.order_status as col_3_0_, delivery2_.city as col_4_0_, delivery2_.street as col_4_1_, delivery2_.zipcode as col_4_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;
Hibernate:
select
orderitem0_.order_id as col_0_0_,
item1_.name as col_1_0_,
orderitem0_.order_price as col_2_0_,
orderitem0_.count as col_3_0_
from
order_item orderitem0_
inner join
item item1_
on orderitem0_.item_id=item1_.item_id
where
orderitem0_.order_id=?
2021-04-06 14:00:59.299 INFO 10992 --- [nio-8084-exec-1] p6spy : #1617685259299 | took 2ms | statement | connection 19| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select orderitem0_.order_id as col_0_0_, item1_.name as col_1_0_, orderitem0_.order_price as col_2_0_, orderitem0_.count as col_3_0_ from order_item orderitem0_ inner join item item1_ on orderitem0_.item_id=item1_.item_id where orderitem0_.order_id=?
select orderitem0_.order_id as col_0_0_, item1_.name as col_1_0_, orderitem0_.order_price as col_2_0_, orderitem0_.count as col_3_0_ from order_item orderitem0_ inner join item item1_ on orderitem0_.item_id=item1_.item_id where orderitem0_.order_id=4;
Hibernate:
select
orderitem0_.order_id as col_0_0_,
item1_.name as col_1_0_,
orderitem0_.order_price as col_2_0_,
orderitem0_.count as col_3_0_
from
order_item orderitem0_
inner join
item item1_
on orderitem0_.item_id=item1_.item_id
where
orderitem0_.order_id=?쿼리는 findOrders 에서 한번 그이후에는 findOrderItems 에서 총 오더가 2개있음으로 2번의 OrderItems를 조회하는 쿼리가 나간다.
JPA에서 DTO 직접 조회(컬렉션 조회 최적화)
public List<OrderQueryDto> findAllByDto_optimization()
{
List<OrderQueryDto> result = findOrders();
List<Long> orderIds = result.stream()
.map(o -> o.getOrderId())
.collect(Collectors.toList());
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::getOrderId));
result.forEach(o -> o.setOrderItems(orderItemMap.get(o.getOrderId())));
return result;
}order들을 findOrders로 가져온후에 orderItem들을 in 쿼리를 통해 한번에 가져온다. 가져온 orderItems 들을 id를 키로해서 Map 형태로 메모리 에 담은후에 가져온 OrderQueryDto 의 orderItems에 채워 넣어준다.
Hibernate:
select
order0_.id as col_0_0_,
member1_.name as col_1_0_,
order0_.order_date as col_2_0_,
order0_.order_status as col_3_0_,
delivery2_.city as col_4_0_,
delivery2_.street as col_4_1_,
delivery2_.zipcode as col_4_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
2021-04-06 14:03:18.120 INFO 10992 --- [nio-8084-exec-5] p6spy : #1617685398120 | took 1ms | statement | connection 20| url jdbc:mysql://localhost:3306/tony_jpashop?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
select order0_.id as col_0_0_, member1_.name as col_1_0_, order0_.order_date as col_2_0_, order0_.order_status as col_3_0_, delivery2_.city as col_4_0_, delivery2_.street as col_4_1_, delivery2_.zipcode as col_4_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
select order0_.id as col_0_0_, member1_.name as col_1_0_, order0_.order_date as col_2_0_, order0_.order_status as col_3_0_, delivery2_.city as col_4_0_, delivery2_.street as col_4_1_, delivery2_.zipcode as col_4_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;
Hibernate:
select
orderitem0_.order_id as col_0_0_,
item1_.name as col_1_0_,
orderitem0_.order_price as col_2_0_,
orderitem0_.count as col_3_0_
from
order_item orderitem0_
inner join
item item1_
on orderitem0_.item_id=item1_.item_id
where
orderitem0_.order_id in (
? , ?
)쿼리는 findOrder에서 한번 그 이후에 findOrderitems 에서 in 쿼리 한번 총 2번이 나가게 된다.
@GetMapping("/api/v6/orders")
public List<OrderQueryDto> ordersV6()
{
List<OrderFlatDto> flats = orderQueryRepository.findAllByDto_flat();
Set<Map.Entry<OrderQueryDto, List<OrderItemQueryDto>>> entries = 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();
List<OrderQueryDto> collect = entries.stream().map(e -> new OrderQueryDto(
e.getKey().getOrderId(),
e.getKey().getName(),
e.getKey().getOrderDate(),
e.getKey().getOrderStatus(),
e.getKey().getAddress(),
e.getValue()))
.collect(toList());
return collect;
}이렇게 하면 쿼리가 한번 나가지만 매우 번거롭게 노가다를 해서 api 스펙에 맞춰줘야 한다. 또한 OrderQueryDto 의 orderId로 grouping하고싶다고 명확히 아래와 같이 @EqualsAndHashCode(of = "orderId")를 써주어야한다.
@Data
@EqualsAndHashCode(of = "orderId")
public class OrderQueryDto
{
@JsonIgnore
private Long orderId;
private String name;
private LocalDateTime orderDate;
private OrderStatus orderStatus;
private Address address;
private List<OrderItemQueryDto> orderItems;
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;
}
}
권장순서
1. 엔티티 조회 방식으로 우선 접근
패치조인으로 쿼리수를 최적화
컬렉션 최적화
- 페이징 필요 = batch 최적화
- 페이징 필요 X = 패치 조인사용
2. 엔티티 조회 방식으로 해결이 안되면 DTO 조회 방식 사용
3. 다안되면 nativeSQL or 스프링 JdbcTemplate
728x90
'WEB > JPA' 카테고리의 다른 글
| 실전! Spring Data JPA 1,2(공통 인터페이스 기능, 쿼리 메소드 기능) (0) | 2021.04.12 |
|---|---|
| 실전! 스프링부트와 JPA와 활용2 (OSIV와 성능 최적화) (0) | 2021.04.06 |
| 실전! 스프링 부트와 JPA 활용2 (API 개발기본 , 지연로딩과 조회 성능 최적화) (0) | 2021.04.03 |
| 김영한 (ORM 표준 JPA 프로그래밍 11) 객체지향 쿼리 언어 소개2 - 중급문법 (0) | 2021.03.20 |
| 김영한 (ORM 표준 JPA 프로그래밍 10) 객체지향 쿼리 언어 소개 (0) | 2021.03.18 |