JPA 환경에서 QueryDSL을 사용하다보면 Join 쿼리 작성시 무심코 발생하는 Cross Join을 놓칠 때가 많다.
Cross Join 이란 묵시적 조인으로 조인을 명시하지 않고 엔터티에서 다른 엔터티를 조회해서 비교하는 경우 JPA가 알아서 해주는 조인이다.
Cross Join의 단점 : Cross Join을 하게 되면 나올 수 있는 데이터가 그냥 조인들보다 많아지기 때문에 성능상의 단점이있다.
따라서, 크로스 조인을 피하기 위해서 쿼리를 보고 명시적 조인을 이용해서 해결하는 것이 좋다.
Cross Join 발생 코드
public List<ProductReadOneDto> findProdInfo(Long prodNum) {
List<ProductReadOneDto> result = jpaQueryFactory
.selectFrom(p)
.leftJoin(q).on(p.prodNum.eq(q.prodNum.prodNum))
.leftJoin(r).on(p.prodNum.eq(r.orderDetail.id.prodNum))
.leftJoin(br).on(r.reviewNum.eq(br.reviewNum))
.where(p.prodNum.eq(prodNum))
.distinct()
.transform(groupBy(p.prodNum).list(Projections.constructor(ProductReadOneDto.class,
p.prodNum, p.seller.companyName, p.seller.sellerId.id, p.category, p.name,
p.originPrice, p.percentage, p.week, p.detail, p.productImgUrl, p.status,
list(Projections.constructor(QnaReadOneResponseDto.class,
q.qnaNum, q.userId.id, q.queTitle, q.queContent,q.queCreatedDate,
q.ansContent, q.ansCreatedDate, q.qnaImgUrl).as("qnas")),
list(Projections.constructor(ReviewReadListResponseDto.class,
r.user.id, r.reviewNum, r.title, r.content, r.sort, r.createdDate, r.stars).as("reviews")),
list(Projections.constructor(BestReviewReadResponseDto.class,
br.reviewNum, br.createdDate)))));
return result;
}select
distinct product0_.num as col_0_0_,
product0_.num as col_1_0_,
seller4_.company_name as col_2_0_,
seller4_.seller_id as col_3_0_,
product0_.category as col_4_0_,
product0_.name as col_5_0_,
product0_.origin_price as col_6_0_,
product0_.percentage as col_7_0_,
product0_.week as col_8_0_,
product0_.detail as col_9_0_,
product0_.product_img_url as col_10_0_,
product0_.status as col_11_0_,
qna1_.num as col_12_0_,
qna1_.user_id as col_13_0_,
qna1_.que_title as col_14_0_,
qna1_.que_content as col_15_0_,
qna1_.que_created_date as col_16_0_,
qna1_.ans_content as col_17_0_,
qna1_.ans_created_date as col_18_0_,
qna1_.qna_img_url as col_19_0_,
review2_.user_id as col_20_0_,
review2_.num as col_21_0_,
review2_.title as col_22_0_,
review2_.content as col_23_0_,
review2_.sort as col_24_0_,
review2_.created_date as col_25_0_,
review2_.stars as col_26_0_,
bestreview3_.review_num as col_27_0_,
bestreview3_.created_date as col_28_0_
from
product product0_
left outer join
qna qna1_
on (
product0_.num=qna1_.prod_num
)
left outer join
review review2_
on (
product0_.num=review2_.prod_num
)
left outer join
best_review bestreview3_
on (
review2_.num=bestreview3_.review_num
) cross //크로스 조인 발생 !!!!
join
seller_info seller4_
where
product0_.seller_id=seller4_.seller_id
and product0_.num=?👇
Cross Join을 명시적 조인(Inner Join)으로 변경
public List<ProductReadOneDto> findProdInfo(Long prodNum) {
List<ProductReadOneDto> result = jpaQueryFactory
.selectFrom(p)
.join(p.seller, s) //명시적 조인(inner join)을 추가해줬다.
.leftJoin(q).on(p.prodNum.eq(q.prodNum.prodNum))
.leftJoin(r).on(p.prodNum.eq(r.orderDetail.id.prodNum))
.leftJoin(br).on(r.reviewNum.eq(br.reviewNum))
.where(p.prodNum.eq(prodNum))
.distinct()
.transform(groupBy(p.prodNum).list(Projections.constructor(ProductReadOneDto.class,
p.prodNum, p.seller.companyName, p.seller.sellerId.id, p.category, p.name,
p.originPrice, p.percentage, p.week, p.detail, p.productImgUrl, p.status,
list(Projections.constructor(QnaReadOneResponseDto.class,
q.qnaNum, q.userId.id, q.queTitle, q.queContent,q.queCreatedDate,
q.ansContent, q.ansCreatedDate, q.qnaImgUrl).as("qnas")),
list(Projections.constructor(ReviewReadListResponseDto.class,
r.user.id, r.reviewNum, r.title, r.content, r.sort, r.createdDate, r.stars).as("reviews")),
list(Projections.constructor(BestReviewReadResponseDto.class,
br.reviewNum, br.createdDate)))));
return result;select
distinct product0_.num as col_0_0_,
product0_.num as col_1_0_,
seller1_.company_name as col_2_0_,
seller1_.seller_id as col_3_0_,
product0_.category as col_4_0_,
product0_.name as col_5_0_,
product0_.origin_price as col_6_0_,
product0_.percentage as col_7_0_,
product0_.week as col_8_0_,
product0_.detail as col_9_0_,
product0_.product_img_url as col_10_0_,
product0_.status as col_11_0_,
qna2_.num as col_12_0_,
qna2_.user_id as col_13_0_,
qna2_.que_title as col_14_0_,
qna2_.que_content as col_15_0_,
qna2_.que_created_date as col_16_0_,
qna2_.ans_content as col_17_0_,
qna2_.ans_created_date as col_18_0_,
qna2_.qna_img_url as col_19_0_,
review3_.user_id as col_20_0_,
review3_.num as col_21_0_,
review3_.title as col_22_0_,
review3_.content as col_23_0_,
review3_.sort as col_24_0_,
review3_.created_date as col_25_0_,
review3_.stars as col_26_0_,
bestreview4_.review_num as col_27_0_,
bestreview4_.created_date as col_28_0_
from
product product0_
inner join
seller_info seller1_
on product0_.seller_id=seller1_.seller_id
left outer join
qna qna2_
on (
product0_.num=qna2_.prod_num
)
left outer join
review review3_
on (
product0_.num=review3_.prod_num
)
left outer join
best_review bestreview4_
on (
review3_.num=bestreview4_.review_num
)
where
product0_.num=?그럼 아래처럼 명시적 inner join을 통해 seller 가 join되는 것을 볼 수 있다.
