N+1문제
기본 구조
- 아래와 같은 구조를 가지고있으며, BOOK을 호출하여 그안에 BOOK_REVEIW와 USER_INFO를 사용하였을떄 N+1문제가 발생하였습니다.
-
연관 관계 코드
- BOOK
@Entity
@Getter
@NoArgsConstructor
public class Book {
@Id
@Column(name = "ISBN")
private String isbn;
@Column(name = "BOOK_NAME")
private String bookName;
@Column(name = "AUTHOR")
private String author;
@Column(name = "PUBLISHER")
private String publisher;
@Column(name = "KDC")
private String kdc;
@Column(name = "CATEGORYy")
private String category;
@Column(name = "KEYWORD")
private String keyword;
@Column(name = "BOOK_IMAGE")
private String img;
@OneToMany(mappedBy = "book")
private List<BookReview> bookReviewList = new ArrayList<>();
@Builder
public Book(String isbn, String bookName, String author, String publisher, String kdc, String category, String keyword, String img) {
this.isbn = isbn;
this.bookName = bookName;
this.author = author;
this.publisher = publisher;
this.kdc = kdc;
this.category = category;
this.keyword = keyword;
this.img = img;
}
}- BOOK_REVIE
@Getter
@Entity
@NoArgsConstructor
public class BookReview {
@Id
@GeneratedValue
private long reviewNo;
private int rating;
private String reviewContents;
private LocalDateTime createDate;
private int declaration;
//NORMAl, BLIND
@Enumerated(EnumType.STRING)
private ReviewStatus reviewStatus;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_no")
private UserInfo userInfo;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "isbn")
private Book book;
@Builder
public BookReview(int rating, String reviewContents, LocalDateTime createDate, int declaration, Book book, UserInfo userInfo) {
this.rating = rating;
this.reviewContents = reviewContents;
this.createDate = createDate;
this.declaration = declaration;
this.book = book;
this.userInfo = userInfo;
}
public void changeBookReview(int rating,String reviewContents) {
this.rating = rating;
this.reviewContents = reviewContents;
}
}- USER_INFO
@Entity
@NoArgsConstructor
@Getter
public class UserInfo {
@Id
@GeneratedValue
private long userNo;
private String userId;
private String userPw;
//GENERAL(일반),PAUSE(정지),SLEEP(휴먼),DELETE(삭제),ADMIN(관리자)',
@Enumerated(EnumType.STRING)
private UserStatus userStatus;
private String userName;
private String userGender;
//'성별, W : 여자 / M : 남자',
private String userBirth;
private LocalDateTime lastConnection;
@OneToMany(mappedBy = "userInfo")
private List<BookReview> bookReviews = new ArrayList<>();
private String deleteReason;
private LocalDateTime delDate;
@Builder
public UserInfo(String userId, String userPw, String userName, String userGender, String userBirth, LocalDateTime lastConnection) {
this.userId = userId;
this.userPw = userPw;
this.userName = userName;
this.userGender = userGender;
this.userBirth = userBirth;
this.userStatus = UserStatus.GENERAL;
this.lastConnection = lastConnection;
}
public void changeUserStatus(UserStatus userStatus) {
this.userStatus = userStatus;
}
public void delUser(String deleteReason, LocalDateTime delDate) {
this.userStatus = UserStatus.DELETE;
this.deleteReason = deleteReason;
this.delDate = delDate;
}
public void changeUserInfo(UserChangeUserInfoRequest userInfo) {
this.userPw = userInfo.getUserPassword();
this.userName = userInfo.getUserName();
this.userGender = userInfo.getUserGender();
this.userBirth = userInfo.getUserBirth();
}
}- Service
- 여기서 서비스의 findBook()를 호출 하였다.
@Slf4j
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class BookService {
String hMessage;
StatusEnum hCode;
Object data;
private final BookRepository bookRepository;
/**
* 도서 조회
*
* @author khh
* @since 2021/04/25
**/
public BookResponse findBook(String isbn) {
log.info(":: findBook :: book is {}", isbn);
try {
Book book = bookRepository.findByIsbn(isbn);
BookDto bookDto = new BookDto(book);
if (book == null) {
hMessage = "검색된 도서가 없습니다.";
} else {
data = bookDto;
hMessage = "도서 조회 성공";
}
} catch (Exception e) {
log.info(":: deleteBook err :: error is {}", e);
hMessage = "검색 실패";
}
return BookResponse.builder()
.data(data)
.hMessage(hMessage)
.build();
}
}- 쿼리 결과 (N + 1 쿼리)
- 하위 엔티티들을 첫 쿼리 실행시 한번에 가져오지 않고, Lazy loading으로 필요한 곳에서 사용되어 쿼리가 실행될때 발생하는 문제가발생한다.
2021-06-02 07:50:22.957 INFO 20840 --- [nio-8080-exec-8] com.semobook.book.service.BookService : :: findBook :: book is 9788937461033
2021-06-02 07:50:22.988 DEBUG 20840 --- [nio-8080-exec-8] org.hibernate.SQL :
select
book0_.isbn as isbn1_0_,
book0_.author as author2_0_,
book0_.book_name as book_nam3_0_,
book0_.categoryy as category4_0_,
book0_.book_image as book_ima5_0_,
book0_.kdc as kdc6_0_,
book0_.keyword as keyword7_0_,
book0_.publisher as publishe8_0_
from
book book0_
where
book0_.isbn=?
2021-06-02 07:50:23.022 DEBUG 20840 --- [nio-8080-exec-8] org.hibernate.SQL :
select
bookreview0_.isbn as isbn7_1_0_,
bookreview0_.review_no as review_n1_1_0_,
bookreview0_.review_no as review_n1_1_1_,
bookreview0_.isbn as isbn7_1_1_,
bookreview0_.create_date as create_d2_1_1_,
bookreview0_.declaration as declarat3_1_1_,
bookreview0_.rating as rating4_1_1_,
bookreview0_.review_contents as review_c5_1_1_,
bookreview0_.review_status as review_s6_1_1_,
bookreview0_.user_no as user_no8_1_1_
from
book_review bookreview0_
where
bookreview0_.isbn=?
2021-06-02 07:50:23.046 DEBUG 20840 --- [nio-8080-exec-8] org.hibernate.SQL :
select
userinfo0_.user_no as user_no1_3_0_,
userinfo0_.del_date as del_date2_3_0_,
userinfo0_.delete_reason as delete_r3_3_0_,
userinfo0_.last_connection as last_con4_3_0_,
userinfo0_.user_birth as user_bir5_3_0_,
userinfo0_.user_gender as user_gen6_3_0_,
userinfo0_.user_id as user_id7_3_0_,
userinfo0_.user_name as user_nam8_3_0_,
userinfo0_.user_pw as user_pw9_3_0_,
userinfo0_.user_status as user_st10_3_0_
from
user_info userinfo0_
where
userinfo0_.user_no=?
2021-06-02 07:50:23.060 DEBUG 20840 --- [nio-8080-exec-8] org.hibernate.SQL :
select
userinfo0_.user_no as user_no1_3_0_,
userinfo0_.del_date as del_date2_3_0_,
userinfo0_.delete_reason as delete_r3_3_0_,
userinfo0_.last_connection as last_con4_3_0_,
userinfo0_.user_birth as user_bir5_3_0_,
userinfo0_.user_gender as user_gen6_3_0_,
userinfo0_.user_id as user_id7_3_0_,
userinfo0_.user_name as user_nam8_3_0_,
userinfo0_.user_pw as user_pw9_3_0_,
userinfo0_.user_status as user_st10_3_0_
from
user_info userinfo0_
where
userinfo0_.user_no=?해결 방법
1. Join Fetch
- join fetch 사용
- 조회시 바로 가져오고 싶은 Entity필드를 지정
@Query("select b from Book b join fetch b.bookReviewList br join fetch br.userInfo")
Book findByIsbn(String isbn);- 쿼리 결과
- 아래처럼 1줄만 발생하는 것을 확인할 수 있다.
- Book을 조회할때 하위 Entity까지 한번에 가져와진다.
- 장점
- 조회하고자 하는 Entity의 하위 Entity까지 한번에 가져올 수 있다.
- 단점
- 불필요한 쿼리문이 추가되는 단점을 가진다.
- Eager조회, Lazy조회를 해야한다 까지 쿼리에서 표현
- 불필요한 쿼리문이 추가되는 단점을 가진다.
2021-06-02 08:24:47.576 INFO 21166 --- [nio-8080-exec-1] com.semobook.book.service.BookService : :: findBook :: book is 9788937461033
2021-06-02 08:24:47.586 DEBUG 21166 --- [nio-8080-exec-1] org.hibernate.SQL :
select
book0_.isbn as isbn1_0_0_,
bookreview1_.review_no as review_n1_1_1_,
userinfo2_.user_no as user_no1_3_2_,
book0_.author as author2_0_0_,
book0_.book_name as book_nam3_0_0_,
book0_.categoryy as category4_0_0_,
book0_.book_image as book_ima5_0_0_,
book0_.kdc as kdc6_0_0_,
book0_.keyword as keyword7_0_0_,
book0_.publisher as publishe8_0_0_,
bookreview1_.isbn as isbn7_1_1_,
bookreview1_.create_date as create_d2_1_1_,
bookreview1_.declaration as declarat3_1_1_,
bookreview1_.rating as rating4_1_1_,
bookreview1_.review_contents as review_c5_1_1_,
bookreview1_.review_status as review_s6_1_1_,
bookreview1_.user_no as user_no8_1_1_,
bookreview1_.isbn as isbn7_1_0__,
bookreview1_.review_no as review_n1_1_0__,
userinfo2_.del_date as del_date2_3_2_,
userinfo2_.delete_reason as delete_r3_3_2_,
userinfo2_.last_connection as last_con4_3_2_,
userinfo2_.user_birth as user_bir5_3_2_,
userinfo2_.user_gender as user_gen6_3_2_,
userinfo2_.user_id as user_id7_3_2_,
userinfo2_.user_name as user_nam8_3_2_,
userinfo2_.user_pw as user_pw9_3_2_,
userinfo2_.user_status as user_st10_3_2_
from
book book0_
inner join
book_review bookreview1_
on book0_.isbn=bookreview1_.isbn
inner join
user_info userinfo2_
on bookreview1_.user_no=userinfo2_.user_no2. @EntitiyGraph
@EntityGraph(attributePaths = "BOOK_REVIEW")
@Query("select b from Book b")
Book findByIsbn(String isbn);-
@EntityGraph의 attributePaths에 쿼리 수행시 바로 가져올 필드명 지정하면 Lazy가 아닌 Eager조회로 가져오게 된다
-
BOOK_REVEIW의 하위 Entitiy(USER_INFO)의 이름을 가져오는 예제는 아래와 같다.
@EntityGraph(attributePaths = "BOOK_REVIEW", "BOOK_REVIEW.USER_INFO")
@Query("select b from Book b")
Book findByIsbn(String isbn);-
사용시 주의사항
- join fetch은 Inner join
- Entity Graph는 Outer Join
- 공통적으로 카테시안 곱(Catesian Product)이 발생하여 BOOK_REVIEW의 수만큼 BOOK이 중복 발생하게 된다.
Reference
hyunho