[Spring] queryDsl oneToMany조회시 N+1 문제.

현재 deal은 image를 oneToMany로 가지고있음.

public abstract class Deal {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @CreatedDate
    private LocalDateTime createTime;
    @LastModifiedDate
    private LocalDateTime updateTime;
    private String title;
    private String content;
    @ManyToOne(fetch = FetchType.LAZY)
    private Member writer;
    @Enumerated(EnumType.STRING)
    private Category category;
    @ElementCollection(fetch = FetchType.LAZY)
    private List<String> area = new ArrayList<>();
    @OneToMany(fetch = FetchType.LAZY)
    private List<Image> images = new ArrayList<>();
    private LocalDateTime startTime;
}


public class Image {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String imagePath;
    private String imageOriginName;
    @CreatedDate
    private LocalDateTime createTime;
}

목록 조회를 querydsl을 통해 구현

    @Override
    public SaleListPage getSaleListPageBySearchParam(SearchParam searchParam) {
        BooleanBuilder booleanBuilder = createBooleanBuilder(searchParam);
        List<OrderSpecifier<?>> orderSpecifiers = createOrderSpecifiers(searchParam);
        Pageable pageable = PageRequest.of(searchParam.getPage(), searchParam.getSize());
        List<SaleSimpleRes> saleSimpleResList = jpaQueryFactory.select(Projections.fields(
                SaleSimpleRes.class,
                new QDealSimpleRes(sale).as("dealSimpleRes"),
                sale.immediatePrice,
                sale.startPrice,
                sale.endTime,
                sale.highestBid.bidPrice.as("bid"),
                sale.status
            ))
            .from(sale)
            .where(booleanBuilder)
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize() + 1)
            .orderBy(orderSpecifiers.toArray(new OrderSpecifier[orderSpecifiers.size()]))
            .fetch();
        boolean hasNext = false;
        if (saleSimpleResList.size() > pageable.getPageSize()) {
            saleSimpleResList.remove(pageable.getPageSize());
            hasNext = true;
        }
        return new SaleListPage(saleSimpleResList, pageable.getPageNumber(), pageable.getPageSize(),
            !hasNext);
    }

public DealSimpleRes(Deal deal) {
    this.id = deal.getId();
    this.title = deal.getTitle();
    this.content = deal.getContent();
    this.category = deal.getCategory();
    this.createTime = deal.getCreateTime();
    this.startTime = deal.getStartTime();
    if (deal.getImages() != null && !deal.getImages().isEmpty()) {
        this.image = deal.getImages().get(0).getImagePath();
    }
}

N+1문제가 발생한다.

 

 

Image Entity에도

@Entity
public class Image{
..
  @ManyToOne(fetch = FetchType.LAZY)
  private Deal deal;
  ..
  }

 

연관관계를 추가한 뒤에, 서브쿼리를 통해 이미지 함께 가져오도록 만들었다. 

List<SaleSimpleRes> saleSimpleResList = jpaQueryFactory.select(Projections.fields(
        SaleSimpleRes.class,
        new QDealSimpleRes(sale,
            Projections.constructor(ImageSimpleDto.class, Expressions.as(
                JPAExpressions
                    .select(image.imagePath)
                    .from(image)
                    .where(image.deal.id.eq(sale.id))
                    .orderBy(image.createTime.asc())
                    .limit(1),
                "imagePath"
            ))).as("dealSimpleRes"),
        sale.immediatePrice,
        sale.startPrice,
        sale.endTime,
        sale.highestBid.bidPrice.as("bid"),
        sale.status
    ))
    .from(sale)
    .where(booleanBuilder)
    .offset(pageable.getOffset())
    .limit(pageable.getPageSize() + 1)
    .orderBy(orderSpecifiers.toArray(new OrderSpecifier[orderSpecifiers.size()]))
    .fetch();

성공적으로 N+1문제 해결하고 요청에 걸리는 시간도 약 30ms -> 15~20 으로 줄었다!!

 

+) 성공인줄 알았는데 이미지를 못가져오고있었다.

몰랐는데 서브쿼리에서는 limit 키워드를 사용할수 없다.

시간 or id가 가장작은것을 가져오도록 삼중 조인을 해줘야한다..

List<SaleSimpleRes> saleSimpleResList = jpaQueryFactory.select(Projections.fields(
        SaleSimpleRes.class,
        new QDealSimpleRes(sale,
            Projections.constructor(ImageSimpleDto.class, Expressions.as(
                JPAExpressions
                    .select(image.imagePath)
                    .from(image)
                    .where(image.id.eq(
                        JPAExpressions.select(image.id.min())
                            .from(image)
                            .where(image.deal.id.eq(sale.id))))
                    .orderBy(image.createTime.asc()),
                "imagePath"
            ))).as("dealSimpleRes"),
        sale.immediatePrice,
        sale.startPrice,
        sale.endTime,
        sale.highestBid.bidPrice.as("bid"),
        sale.status
    ))
    .from(sale)
    .where(booleanBuilder)
    .offset(pageable.getOffset())
    .limit(pageable.getPageSize() + 1)
    .orderBy(orderSpecifiers.toArray(new OrderSpecifier[orderSpecifiers.size()]))
    .fetch();