[최적화] Spring Data Jpa DTO 반환, 부하테스트, 페치조인 리스트 DTO 쿼리 반환 오류! org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list
2022. 11. 14. 18:37ㆍ카테고리 없음
https://www.youtube.com/watch?v=zMAX7g6rO_Y
해당 발표를 정말 감명깊게 봤습니다.
올라온지 꽤 된 영상이지만 그래도 제게는 인사이트가 막강했습니다.. S급 개발자는 이런 것인가 생각이 들었습니다.
발표 내용 중, Spring Data Jpa를 활용한 쿼리에서 무분별하게 Entity를 조회하면 성능상 손해가 크다는 말이 있었습니다.
아이러니하게도 발표자분이 CTO로 계시는 인프런에서도 미래에 해당 이슈 때문에 장애가 일어나기도 했던 글을 봤습니다.
요약하자면, 강의 목록을 조회해오는데 당장 쓰이지 않을 컬럼까지 모두 조회하는 바람에 대용량 컬럼까지 모두 끌어오게 되어서 강의 한 페이지를 조회하는데 DB에 많은 부하가 발생하는 상황으로 장애가 발생했다고 합니다.
여튼 서론이 길었습니다.
저도 개인 게시판 프로젝트를 진행하고 있습니다. 부끄러운 점은, 모든 조회쿼리의 반환타입이 Entity로 되어있다는 점입니다.
service 레이어에서 DTO로 변환해서 response를 내려주긴 하지만 여전히 DB 관점에서의 오버헤드가 많이 큰 설계입니다.
반환타입이 Entity라면 Jpa의 영속성 컨텍스트에서 지원하는 지연로딩, 1차캐시를 적극적으로 활용해 성능상 이점을 끌어올릴 수도 있지만, 사실 쓸 일이 그렇게 많지 않습니다.
프로젝트의 페이징 쿼리입니다.
@Query("select p from Post p join fetch p.user where p.postId in :postIds")
List<Post> paginationByPostIds(@Param("postIds") List<Long> postIds);이 쿼리를 날리기 전에 limit 쿼리 최적화를 위해 PK 커버링 인덱스로 postIds 를 미리 조회하는 쿼리가 있습니다.
여튼, select 절을 보면 Post 엔티티 전체를 조회하는 로직입니다.
그 뿐만 아니라 fetch join으로 User 엔티티의의 모든 컬럼까지 끌어오고 있는 상황입니다.
각 엔티티의 필드가 몇 개인지 보겠습니다.
public class Post {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long postId;
@Column(nullable = false, length = 1000)
private String postSubject;
@Column(nullable = false, columnDefinition = "TEXT")
private String postContent;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private Users user;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "reference_category_id")
private PostCategory postCategory;
@Column(nullable = true, columnDefinition = "BIGINT default 0")
private Long hitCount;
@Embedded
private BaseTime baseTime;
public class Users {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "user_id")
private Long userId;
@Column(nullable = false, length = 200, columnDefinition = "VARCHAR(200)")
private String userEmail;
@Column(nullable = false, length = 10, columnDefinition = "VARCHAR(10)")
private String userName;
@Column(nullable = false, length = 100, columnDefinition = "VARCHAR(100)")
private String userNickname;
@Enumerated(EnumType.STRING)
@Column(nullable = false, length = 45, columnDefinition = "VARCHAR(45)")
private UserRole userRole;
@Embedded
private BaseTime baseTime;프로젝트의 규모가 작아서 생각보다 많지는 않습니다. (추상화까지 15개)
가장 공간을 크게 요하는 큰 필드는 Post의 PostContent 정도가 되겠네요.
그렇다면, 이 페이징 쿼리를 통해 반환되는 컬럼 중, 실제 사용하는 컬럼을 보겠습니다.
public class PostSimpleQueryDTO {
private final Long postId;
private final String postSubject;
private final LocalDateTime createdAt;
private final Long hitCount;
private final Long userId;
private final String userNickname;
}실제 반환받는 15개 컬럼 중 겨우 6개만 사용하고 있습니다.
무조건 최적화 해야겠다 싶은 생각이 듭니다. 가장 많이 사용될 기능이니까요.
최적화 한다면 컬럼이 단순히 9개 줄어든 것으로 그치는 것이 아니라, 사용하지 않은 대용량 컬럼인 postContent를 지웠다는 것이 가장 큰 최적화 포인트가 아닐까 생각해봅니다. 자료형이 TEXT라서 유저가 게시글을 길게 작성했을수록 더 효과가 크리라 예상됩니다.
본격적으로 작업해보겠습니다.
엔티티를 반환하는 쿼리를
@Query("select p from Post p join fetch p.user where p.postId in :postIds")
List<Post> paginationByPostIds(@Param("postIds") List<Long> postIds);아래와 같이 수정합니다.
@Query("select new com.zzangmin.gesipan.layer.basiccrud.dto.post.PostSimpleQueryDTO(p.postId, p.postSubject, p.baseTime.createdAt, p.hitCount, p.user.userId, p.user.userNickname) from Post p join fetch p.user where p.postId in :postIds")
List<PostSimpleQueryDTO> paginationByPostIds(@Param("postIds") List<Long> postIds);
이제 테스트를 실행해봅니다.
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'commentController' defined in file [/Users/leezzangmin/Documents/GitHub/SpringCafeProject/gesipan/build/classes/java/main/com/zzangmin/gesipan/layer/basiccrud/controller/CommentController.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'commentService' defined in file [/Users/leezzangmin/Documents/GitHub/SpringCafeProject/gesipan/build/classes/java/main/com/zzangmin/gesipan/layer/basiccrud/service/CommentService.class]: Unsatisfied dependency expressed through constructor parameter 3; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'postRepository' defined in com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1372) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1222) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:582) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:953) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918) ~[spring-context-5.3.20.jar:5.3.20]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.20.jar:5.3.20]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295) ~[spring-boot-2.7.0.jar:2.7.0]
at com.zzangmin.gesipan.GesipanApplication.main(GesipanApplication.java:14) ~[main/:na]
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'commentService' defined in file [/Users/leezzangmin/Documents/GitHub/SpringCafeProject/gesipan/build/classes/java/main/com/zzangmin/gesipan/layer/basiccrud/service/CommentService.class]: Unsatisfied dependency expressed through constructor parameter 3; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'postRepository' defined in com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1372) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1222) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:582) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1389) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1309) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791) ~[spring-beans-5.3.20.jar:5.3.20]
... 19 common frames omitted
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'postRepository' defined in com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1389) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1309) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791) ~[spring-beans-5.3.20.jar:5.3.20]
... 33 common frames omitted
Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!
at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:101) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:106) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(QueryExecutorMethodInterceptor.java:94) ~[spring-data-commons-2.7.0.jar:2.7.0]
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[na:na]
at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]
at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1054) ~[na:na]
at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[na:na]
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[na:na]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:96) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:86) ~[spring-data-commons-2.7.0.jar:2.7.0]
at java.base/java.util.Optional.map(Optional.java:265) ~[na:na]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:86) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:364) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:322) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.util.Lazy.getNullable(Lazy.java:230) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.util.Lazy.get(Lazy.java:114) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:328) ~[spring-data-commons-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:144) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.20.jar:5.3.20]
... 44 common frames omitted
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.zzangmin.gesipan.layer.basiccrud.repository.PostRepository.paginationByPostIds(java.util.List)!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:96) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:51) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:163) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:87) ~[spring-data-jpa-2.7.0.jar:2.7.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:102) ~[spring-data-commons-2.7.0.jar:2.7.0]
... 66 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.zzangmin.gesipan.layer.basiccrud.entity.Post.user,tableName=users,tableAlias=users1_,origin=post post0_,columns={post0_.user_id,className=com.zzangmin.gesipan.layer.login.entity.Users}}] [select new com.zzangmin.gesipan.layer.basiccrud.dto.post.PostSimpleQueryDTO(p.postId, p.postSubject, p.baseTime.createdAt, p.hitCount, p.user.userId, p.user.userNickname) from com.zzangmin.gesipan.layer.basiccrud.entity.Post p join fetch p.user where p.postId in :postIds]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:757) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.20.jar:5.3.20]
at com.sun.proxy.$Proxy113.createQuery(Unknown Source) ~[na:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90) ~[spring-data-jpa-2.7.0.jar:2.7.0]
... 72 common frames omitted
Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.zzangmin.gesipan.layer.basiccrud.entity.Post.user,tableName=users,tableAlias=users1_,origin=post post0_,columns={post0_.user_id,className=com.zzangmin.gesipan.layer.login.entity.Users}}] [select new com.zzangmin.gesipan.layer.basiccrud.dto.post.PostSimpleQueryDTO(p.postId, p.postSubject, p.baseTime.createdAt, p.hitCount, p.user.userId, p.user.userNickname) from com.zzangmin.gesipan.layer.basiccrud.entity.Post p join fetch p.user where p.postId in :postIds]
at org.hibernate.QueryException.generateQueryException(QueryException.java:120) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
... 80 common frames omitted
Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.zzangmin.gesipan.layer.basiccrud.entity.Post.user,tableName=users,tableAlias=users1_,origin=post post0_,columns={post0_.user_id,className=com.zzangmin.gesipan.layer.login.entity.Users}}]
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:230) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1039) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:807) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:703) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:339) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:287) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
... 86 common frames omitted
Process finished with exit code 1스프링 컨테이너가 뜨지도 않습니다.
로그를 살펴보니 방금 작성한 쿼리에 문제가 생긴 것 같습니다.
Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list
검색해봅니다.
query specified join fetching, but the owner of the fetched association was not present in the select list
I'm selecting two id columns but get error specified: org.hibernate.QueryException: **query specified join fetching, but the owner of the fetched association was not present in the select list** [
stackoverflow.com
요약하면,
As error message tells you, join fetch doesn't make sense here, because it's a performance hint that forces eager loading of collection.
입니다.
생각해보니 네이티브 쿼리같은 느낌인데 페치조인은 조금 말이 안되네요
쿼리를 수정합니다
@Query("select new com.zzangmin.gesipan.layer.basiccrud.dto.post.PostSimpleQueryDTO(p.postId, p.postSubject, p.baseTime.createdAt, p.hitCount, p.user.userId, p.user.userNickname) " +
"from Post p inner join p.user u " +
"where p.postId in :postIds and u.userId=p.user.userId")
List<PostSimpleQueryDTO> paginationByPostIds(@Param("postIds") List<Long> postIds);쿼리가 잘 짜였는지에 대한 검증은 컴파일러와 미리 작성해둔 테스트코드가 합니다.
기타 서비스코드를 시그니쳐에 맞게 수정하고 메인 브랜치에 PR후 머지하면 끝입니다.
최적화 후 가장 궁금한 포인트인 성능은 어떨까요.
CI / CD + 부하테스트 + 결과전송을 미리 자동화해둔 보람이 있습니다.
귀찮은 과정은 다 날려보내고 풀리퀘스트를 날리면 main에 머지될 때 알아서 부하테스트 결과를 슬랙으로 알려줍니다.
대망의 결과는....!
11월 12일에 수행된 테스트와 오늘자(11월 14일) 수행된 테스트 결과가 슬랙 메세지로 저장되어있습니다.
가독성이 너무 나쁘지만, 중요한 부분만 뽑아 살펴보자면
tps(초당 테스트 횟수)가 평균 51.06에서 -> 53.68로 상승했습니다.
peakTps(최대 tps)가 59에서 -> 61.5로 상승했습니다.
잠깐 배워서 적용한 것 치곤 꽤나 만족스러운 성능 향상 결과네요.
해당 부하 테스트는 서로 다른 api가 여러개 섞인 시나리오 테스트라서 성능 향상이 더 미비해 보일 수 있습니다.
작은 개선이지만 기분이 좋습니다. 화이팅!
코드는