### 1. 페이징 쿼리 (2개)
select
count(s1_0.id)
from
spaces s1_0;
select
s1_0.id,s1_0.closing_time, ...
from
spaces s1_0
limit
0,10;
### 2. space, realEstate
select
re1_0.id,re1_0.dong,re1_0.jibun_address,re1_0.road_address,re1_0.sido,re1_0.sigungu,re1_0.deleted_at,re1_0.floor,re1_0.has_elevator,re1_0.has_parking,re1_0.user_id
from
real_estates re1_0
where re1_0.id=1;
// 10개의 쿼리 반복
### 3. space와 spaceImage (값 컬렉션)
select
ip1_0.space_id,ip1_0.image_paths
from
space_images ip1_0
where
ip1_0.space_id=1;
// 10개의 쿼리 반복
### 4. space, SpaceOption, Option (OneToMany, ManyToOne)
select
so1_0.space_id,so1_0.option_id,o1_0.id,o1_0.name
from
space_options so1_0
left join
options o1_0 on o1_0.id=so1_0.option_id
where so1_0.space_id=1;
// 10개의 쿼리 반복
### 5. space, subCategory, MainCategory
select
sc1_0.space_id,sc1_1.id,sc1_1.main_category_id,mc1_0.id,mc1_0.name,sc1_1.name
from
space_sub_categories sc1_0
join
sub_categories sc1_1 on sc1_1.id=sc1_0.sub_category_id
left join
main_categories mc1_0 on mc1_0.id=sc1_1.main_category_id
where
sc1_0.space_id=1;
// 10개의 쿼리 반복
### 6. space, hashtag
select
h1_0.space_id,h1_1.id,h1_1.name
from
space_hashtags h1_0
join
hashtags h1_1 on h1_1.id=h1_0.hashtag_id
where h1_0.space_id=1;
// 10개의 쿼리 반복
firstResult/maxResults specified with collection fetch; applying in memory
select
s1_0.id,s1_0.closing_time, ...
from
spaces s1_0
left join
space_options so1_0 on s1_0.id=so1_0.space_id
left join
options o1_0 on o1_0.id=so1_0.option_id
// limit ?
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.juny.spacestory.space.domain.Space.imagePaths, com.juny.spacestory.space.domain.Space.questions
@OneToMany(mappedBy = "space", cascade = CascadeType.ALL, orphanRemoval = true)
@BatchSize(size = 10)
private List<SpaceOption> spaceOptions = new ArrayList<>();
select
so1_0.space_id,so1_0.option_id,o1_0.id,o1_0.name
from
space_options so1_0
left join
options o1_0 on o1_0.id=so1_0.option_id
where
so1_0.space_id in (1,2,3,4,5,6,7,8,9,10);
Property 'realEstate' may not be annotated '@BatchSize'
List<Space> spaces = queryFactory.selectFrom(space)
.leftJoin(space.realEstate, realEstate).fetchJoin()
.offset(page * size)
.limit(size)
.fetch();
@NamedEntityGraph(name = "Space.withRelations", attributeNodes = {
@NamedAttributeNode("realEstate"),
})
public class Space {
...
}
public interface SpaceRepository extends JpaRepository<Space, Long>, CustomSpaceRepository {
@EntityGraph(value = "Space.withRelations", type = EntityGraph.EntityGraphType.LOAD)
Page<Space> findAll(Pageable pageable);
...
}
## 1. 공간 페이징 쿼리(다대일 엔티티는 fetch join)
select
s1_0.id,s1_0.closing_time, ...
from
spaces s1_0
left join
real_estates re1_0 on re1_0.id=s1_0.real_estate_id limit 0,10;
## 2. 전체 개수
select count(s1_0.id) from spaces s1_0;
## 3. 공간 - 이미지
select
ip1_0.space_id,ip1_0.image_paths
from
space_images ip1_0
where
ip1_0.space_id in (1,2,3,4,5,6,7,8,9,10);
## 4. 공간 - 옵션
select
so1_0.space_id,so1_0.option_id,o1_0.id,o1_0.name
from
space_options so1_0
left join
options o1_0 on o1_0.id=so1_0.option_id
where
so1_0.space_id in (1,2,3,4,5,6,7,8,9,10);
## 5. 공간 - 카테고리
select
sc1_0.space_id,sc1_1.id,sc1_1.main_category_id,mc1_0.id,mc1_0.name,sc1_1.name
from
space_sub_categories sc1_0
join
sub_categories sc1_1 on sc1_1.id=sc1_0.sub_category_id
left join
main_categories mc1_0 on mc1_0.id=sc1_1.main_category_id
where sc1_0.space_id in (1,2,3,4,5,6,7,8,9,10);
## 6. 공간 - 해시태그
select
h1_0.space_id,h1_1.id,h1_1.name
from
space_hashtags h1_0
join
hashtags h1_1 on h1_1.id=h1_0.hashtag_id
where
h1_0.space_id in (1,2,3,4,5,6,7,8,9,10);
cf. BatchSize vs 서브쿼리
- 전자는 단순하고, batchSize가 작을수록 빠르게 동작한다 (물론 쿼리는 많이 실행된다). 서브쿼리는 space와 real_estate 테이블이 클수록 더 느리게 동작한다. 전체 요소를 가져오는 데 있어 훨씬 유연하게 동작하지만, 조건이 추가될수록 쿼리가 복잡하기에 유지보수 하기 어려워진다.
// BatchSize
SELECT ip1_0.space_id, ip1_0.image_paths
FROM space_images ip1_0
WHERE ip1_0.space_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
// 서브쿼리
SELECT ip1_0.space_id, ip1_0.image_paths
FROM space_images ip1_0
WHERE ip1_0.space_id IN (
SELECT s1_0.id
FROM spaces s1_0
LEFT JOIN real_estates re1_0 ON re1_0.id = s1_0.real_estate_id
);
// 전체 사이즈 가져오는 쿼리
SELECT COUNT(*) FROM spaces;
// 조회할 공간 아이디를 가져오는 쿼리
SELECT id FROM spaces ORDER BY id LIMIT 10 OFFSET 0;
// 공간 정보와 연관된 엔티티를 한 번에 가져오는 쿼리
SELECT
s.id AS space_id,
...,
r.id AS real_estate_id,
...,
si.image_paths AS image_paths,
o.id AS option_id,
o.name AS option_name,
so.space_id AS space_option_space_id,
sc.id AS sub_category_id,
sc.name AS sub_category_name,
mc.id AS main_category_id,
mc.name AS main_category_name,
h.id AS hashtag_id,
h.name AS hashtag_name
FROM
spaces s
LEFT JOIN
real_estates r ON s.real_estate_id = r.id
LEFT JOIN
space_images si ON s.id = si.space_id
LEFT JOIN
space_options so ON s.id = so.space_id
LEFT JOIN
options o ON so.option_id = o.id
LEFT JOIN
space_sub_categories ssc ON s.id = ssc.space_id
LEFT JOIN
sub_categories sc ON ssc.sub_category_id = sc.id
LEFT JOIN
main_categories mc ON sc.main_category_id = mc.id
LEFT JOIN
space_hashtags sh ON s.id = sh.space_id
LEFT JOIN
hashtags h ON sh.hashtag_id = h.id
WHERE
s.id IN ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 );
쿼리 수가 적다고 해서 무조건 빠른 건 아니라는 것을 명심하자! 쿼리 수가 적어짐에 따라 DB 연산에 부하가 크다면 더 느려질 수도 있다. 하지만, 네트워크 통신인 DB 쿼리는 애플리케이션 연산 속도보다 느리기 때문에 가급적 쿼리 수를 줄이는 것이 좋다.