개요
- 이전 글에서는 서브쿼리를 변환하는 방법을 알아보았고, 성능적으로 어떠한 차이가 있는지 알아보았다.
- 이번 글에서는 실무에서 자주 등장하는 서브쿼리 형태에 대해서 알아보고, 해당 쿼리를 어떠한 방식으로 처리하는 게 효율적인지 정리해 보려고 한다.
필수 사전 지식
아래와 같은 문제는 내가 자주 접했던 유형의 쿼리 형태이다.
문제
orders 테이블과 order_details 테이블이 있습니다. 주문 정보와 각 주문에 대해 가장 마지막에 등록된 주문상세의 생성일자를 조회하라.
만약, 등록된 일자가 같은 경우, order_detail_id가 가장 큰 값의 생성일자를 조회하라.테이블 정보
orders
create table orders
(
order_id bigint primary key,
customer_name varchar(255),
order_date varchar(255)
);order_details
create table order_details
(
order_detail_id bigint primary key,
create_date varchar(255),
order_id bigint,
product_name varchar(255),
quantity integer
);풀이
- 먼저 같은 order에 대해서 가장 마지막에 생성된 order_detail를 찾아야 하기 때문에 order_id로 그룹화 한 뒤, create_date가 가장 큰 값을 찾는다.
select od.order_id, max(subod1.create_date) as latest_create_date
from order_details od
group by od.order_id- 하지만, 같은 order_id, create_date 같은 데이터는 중복해서 나타날 것이다.
- 같은 order에 대해서는 create_date로 그룹화 한 뒤, 그중에서 order_detail_id가 가장 큰 값을 선택해야 한다.
select od.order_id, od.create_date as latest_create_date, max(od.order_detail_id) as max_dtls_id
from order_details od
inner join (
select order_id, max(create_date) as latest_create_date
from order_details
group by order_id
) subod on od.order_id = subod.order_id and od.create_date = subod.latest_create_date
group by od.order_id, od.create_date;- 위 쿼리를 실행해 보면, 같은 order에 대해서 create_date 가 같으면 order_detail_id가 큰 값이 조회된다.
- 이 쿼리를 이용해서 원하는 결과를 select 하는 쿼리를 만들어보자
select o.*, od_latest.max_order_detail_id, od_latest.latest_create_date
from orders o
left join (
select subod2.order_id, max(subod2.order_detail_id) as max_order_detail_id, od_max.latest_create_date
from order_details subod2
inner join (
select subod1.order_id, max(subod1.create_date) as latest_create_date
from order_details subod1
group by subod1.order_id
) od_max on subod2.order_id = od_max.order_id and subod2.create_date = od_max.latest_create_date
group by subod2.order_id, od_max.latest_create_date
) od_latest on o.order_id = od_latest.order_id;위와 같이 쿼리를 짜면 되지만, 문제는 QueryDSL-JPA에서는 FROM절의 서브쿼리를 지원하지 않는다는 것이다. 이제 FROM절의 서브쿼리를 해결하는 방법에 대해서 정리해 보자.
FROM절 서브쿼리 해결하기
내가 생각했을 때 FROM절 서브쿼리를 해결하는 방법은 아래 3가지이다.
- WHERE절 서브쿼리로 변환하기
- QueryDSL-SQL로 FROM절 서브쿼리 구현하기
- 서브쿼리를 별도로 실행한 뒤, 애플리케이션에서 조립하기
이제 위 세 가지 방법을 각각 시도해 보고 장단점을 비교해 보자
1. WHERE절 서브쿼리로 변환하기
- 이전 글에서 FROM절의 서브쿼리를 WHERE절의 서브쿼리로 변환하는 방법은 알아보았다.
- 그 방법으로 WHERE절의 서브쿼리로 변환하면 아래와 같다.
select o.*, od.order_detail_id as max_order_detail_id, od.create_date as max_create_date
from orders o
left join order_details od on o.order_id = od.order_id
where (od.order_id, od.create_date, od.order_detail_id) in (
select subod2.order_id, subod2.create_date as latest_create_date, max(subod2.order_detail_id) as max_dtls_id
from order_details subod2
where (subod2.order_id, subod2.create_date) in (
select subod1.order_id, max(subod1.create_date) as latest_create_date
from order_details subod1
group by subod1.order_id
)
group by subod2.order_id, subod2.create_date
)
or od.order_detail_id is null;- 위 쿼리에서 주의해야 될 점은 order_details.order_detail_id is null를 마지막 where절에 추가해 줘야 한다는 점이다.
- 이 부분이 이해되지 않는 다면, 이전글을 다시 한번 참고하자.
- 이제, 이 쿼리를 QueryDSL-JPA로 구현해 보자
private final JPAQueryFactory queryFactory;
public List<OrderDto> searchOrdersWithWhereSubQuery() {
QOrder order = QOrder.order;
QOrderDetail orderDetail = QOrderDetail.orderDetail;
QOrderDetail subod1 = new QOrderDetail("subod1");
QOrderDetail subod2 = new QOrderDetail("subod2");
return queryFactory
.select(Projections.fields(OrderDto.class,
order.orderId,
order.orderDate,
order.customerName,
orderDetail.createDate.as("maxCreateDate"),
orderDetail.orderDetailId.as("maxOrderDetailId")
))
.from(order)
.leftJoin(orderDetail).on(order.orderId.eq(orderDetail.orderId))
.where(
Expressions.list(orderDetail.orderId, orderDetail.createDate, orderDetail.orderDetailId).in(
JPAExpressions.select(subod2.orderId, subod2.createDate, subod2.orderDetailId.max().as("max_dtls_id"))
.from(subod2)
.where(Expressions.list(subod2.orderId, subod2.createDate).in(
JPAExpressions.select(subod1.orderId, subod1.createDate.max().as("latest_create_date"))
.from(subod1)
.groupBy(subod1.orderId)
))
.groupBy(subod2.orderId, subod2.createDate)
).or(orderDetail.orderDetailId.isNull())
)
.fetch();
}2. QueryDSL-SQL로 FROM절 서브쿼리 구현하기
- 서브쿼리(with QueryDsl) 편에서 QueryDSL-SQL을 사용해서 FROM절의 서브쿼리를 구현하는 방법을 알아보았다.
- QueryDSL-SQL을 사용해서 구현하면 아래와 같다.
select o.*, od_latest.max_order_detail_id, od_latest.latest_create_date
from orders o
left join (
select subod2.order_id, max(subod2.order_detail_id) as max_order_detail_id, od_max.latest_create_date
from order_details subod2
inner join (
select subod1.order_id, max(subod1.create_date) as latest_create_date
from order_details subod1
group by subod1.order_id
) od_max on subod2.order_id = od_max.order_id and subod2.create_date = od_max.latest_create_date
group by subod2.order_id, od_max.latest_create_date
) od_latest on o.order_id = od_latest.order_id;private final JPASQLQueryFactory jpaSqlQueryFactory;
public List<OrderDto> searchOrdersWithFromSubQuery() {
QOrder order = QOrder.order;
QOrderDetail subod1 = new QOrderDetail("subod1");
QOrderDetail subod2 = new QOrderDetail("subod2");
StringPath odMax = Expressions.stringPath("od_max");
StringPath odLatest = Expressions.stringPath("od_latest");
return jpaSqlQueryFactory.createQuery()
.select(Projections.fields(OrderDto.class,
order.orderId,
order.orderDate,
order.customerName,
Expressions.numberPath(Long.class, odLatest, "max_order_detail_id").as("maxOrderDetailId"),
Expressions.stringPath(odLatest, "latest_create_date").as("maxCreateDate")
))
.from(order)
.leftJoin(
SQLExpressions
.select(subod2.orderId, subod2.orderDetailId.max().as("max_order_detail_id"), Expressions.stringPath(odMax, "latest_create_date"))
.from(subod2)
.innerJoin(
JPAExpressions.select(subod1.orderId, subod1.createDate.max().as("latest_create_date"))
.from(subod1)
.groupBy(subod1.orderId),
odMax
)
.on(
subod2.orderId.eq(Expressions.numberPath(Long.class, odMax, "order_id"))
.and(subod2.createDate.eq(Expressions.stringPath(odMax, "latest_create_date")))
)
.groupBy(subod2.orderId, Expressions.stringPath(odMax, "latest_create_date")),
odLatest
).on(
order.orderId.eq(Expressions.numberPath(Long.class, odLatest, "order_id"))
).fetch();
}3. 쿼리를 나눠서 실행한 뒤, 애플리케이션에서 조립하기
쿼리 2번으로 나눠서 실행 후 조립 (서브쿼리 1번 사용)
서브쿼리를 1번만 사용해서 처리하는 방법은 아래와 같다. 실제로 실행된 쿼리는 아래와 같다.
select od.order_id,
od.create_date,
max(od.order_detail_id) as max_order_detail_id
from order_details od
where (
od.order_id, od.create_date
) in (
select sub.order_id,
max(sub.create_date)
from order_details sub
group by sub.order_id
)
group by od.order_id,
od.create_date;select orders.order_id,
orders.order_date,
orders.customer_name
from orders;private final JPAQueryFactory queryFactory;
public List<OrderDto> searchOrdersByOneSubQuery() {
QOrder order = QOrder.order;
QOrderDetail orderDetail = QOrderDetail.orderDetail;
QOrderDetail subod1 = new QOrderDetail("subod1");
// orderDetail 정보 조회
Map<Long, Tuple> orderDetailMap = queryFactory
.select(orderDetail.orderId, orderDetail.createDate, orderDetail.orderDetailId.max())
.from(orderDetail)
.where(Expressions.list(orderDetail.orderId, orderDetail.createDate).in(
JPAExpressions.select(subod1.orderId, subod1.createDate.max())
.from(subod1)
.groupBy(subod1.orderId)
))
.groupBy(orderDetail.orderId, orderDetail.createDate)
.fetch().stream()
.collect(
Collectors.toMap(
tuple -> tuple.get(orderDetail.orderId),
tuple -> tuple
)
);
// orderDetail 정보를 조립한다.
return queryFactory
.select(Projections.fields(OrderDto.class,
order.orderId,
order.orderDate,
order.customerName
))
.from(order)
.fetch().stream()
.peek(orderDto -> {
Optional.ofNullable(orderDetailMap.get(orderDto.getOrderId()))
.ifPresent(od -> {
orderDto.setMaxOrderDetailId(od.get(orderDetail.orderDetailId.max()));
orderDto.setMaxCreateDate(od.get(orderDetail.createDate));
});
}).collect(Collectors.toList());
}- 위와 같이 Where절에 들어갈 서브쿼리를 별도로 실행했다.
- 그다음 결과를 Map 형태로 담고 있고 있다가, order 테이블을 조회할 때 필요한 값들을 넣어주는 형태로 구현할 수 있다.
- 하지만 위의 여전히 서브쿼리를 1번 사용하고 있다. 만약 서브쿼리를 아예 사용하지 않으려면 아래와 같이 구현할 수 있다.
쿼리 3번으로 나눠서 실행 후 조립
서브쿼리를 아예 사용하지 않고 전부 개별 쿼리로 실행하면 아래와 같다. 실제로 실행된 쿼리는 아래와 같다.
select (order_details.order_id || order_details.create_date),
max(order_details.order_detail_id) as max_order_detail_id
from order_details
group by order_details.order_id,
order_details.create_date;select order_details.order_id,
max(order_details.create_date) as max_create_date
from order_details
group by order_details.order_id;select orders.order_id,
orders.order_date,
orders.customer_name
from orders;private final JPAQueryFactory queryFactory;
public List<OrderDto> searchOrdersByTwoSubQuery() {
QOrder order = QOrder.order;
QOrderDetail orderDetail = QOrderDetail.orderDetail;
// (쿼리 분리1) orderId, createDate 로 묶어서 가장 큰 orderDetailId 조회
Map<String, Long> maxOrderDetailIdMap = queryFactory
.select(
Expressions.stringTemplate("{0} || {1}", orderDetail.orderId, orderDetail.createDate),
orderDetail.orderDetailId.max()
)
.from(orderDetail)
.groupBy(orderDetail.orderId, orderDetail.createDate)
.fetch().stream()
.collect(
Collectors.toMap(
tuple -> tuple.get(Expressions.stringTemplate("{0} || {1}", orderDetail.orderId, orderDetail.createDate)),
tuple -> tuple.get(orderDetail.orderDetailId.max())
)
);
// (쿼리 분리2) orderId 로 묶어서 가장 큰 createDate 조회
Map<Long, OrderDetailDto> orderDetailMap = queryFactory
.select(orderDetail.orderId, orderDetail.createDate.max())
.from(orderDetail)
.groupBy(orderDetail.orderId)
.fetch().stream()
.collect(
Collectors.toMap(
tuple -> tuple.get(orderDetail.orderId),
tuple -> {
Long orderId = tuple.get(orderDetail.orderId);
String maxCreateDate = tuple.get(orderDetail.createDate.max());
OrderDetailDto orderDetailDto = new OrderDetailDto();
orderDetailDto.setOrderDetailId(maxOrderDetailIdMap.get(orderId + maxCreateDate));
orderDetailDto.setCreateDate(maxCreateDate);
return orderDetailDto;
}
)
);
// orderDetail 정보를 조립한다.
return queryFactory
.select(Projections.fields(OrderDto.class,
order.orderId,
order.orderDate,
order.customerName
)).from(order)
.fetch().stream()
.peek(orderDto -> {
Optional.ofNullable(orderDetailMap.get(orderDto.getOrderId()))
.ifPresent(od -> {
orderDto.setMaxOrderDetailId(od.getOrderDetailId());
orderDto.setMaxCreateDate(od.getCreateDate());
});
}).collect(Collectors.toList());
}성능 비교
우리가 알아본 방법은 크게 4가지다.
- QueryDSL-JPA를 사용한 Where절 서브쿼리 실행
- QueryDSL-SQL을 사용한 From절 서브쿼리 실행
- QueryDSL-JPA를 사용해서 쿼리를 2개로 분리해서 실행
- QueryDSL-JPA를 사용해서 쿼리를 3개로 분리해서 실행
하지만 2번 과정은 QueryDSL-JPA가 아닌 QueryDSL-SQL이다. 실무에서는 보통 QueryDSL-JPA를 사용하기 때문에 2번은 제외하고 1, 3, 4번 과정에 대해서 성능 비교를 해보도록 하자.
데이터 삽입
테스트를 위해서 orders 테이블에 1만 건, order_details 테이블에 10만 건의 데이터를 넣었다.
insert into orders (order_id, customer_name, order_date)
select
generate_series as order_id,
'Customer_' || generate_series as customer_name,
'2023-01-01' as order_date
from generate_series(1, 10000);
insert into order_details (order_detail_id, create_date, order_id, product_name, quantity)
select
generate_series as order_detail_id,
'2023-01-01' as create_date,
(random() * 9999 + 1)::bigint as order_id,
'Product_' || generate_series as product_name,
(random() * 9 + 1)::integer as quantity
from generate_series(1, 100000);쿼리 수행 시간 비교
QueryDSL-JPA를 사용한 Where절 서브쿼리 실행
- 실행할 쿼리는 아래와 같다.
select o.*, od.order_detail_id as max_order_detail_id, od.create_date as max_create_date
from orders o
left join order_details od on o.order_id = od.order_id
where (od.order_id, od.create_date, od.order_detail_id) in (
select subod2.order_id, subod2.create_date as latest_create_date, max(subod2.order_detail_id) as max_dtls_id
from order_details subod2
where (subod2.order_id, subod2.create_date) in (
select subod1.order_id, max(subod1.create_date) as latest_create_date
from order_details subod1
group by subod1.order_id
)
group by subod2.order_id, subod2.create_date
)
or od.order_detail_id is null;- 인덱스가 없는 경우라면 실행계획은 아래와 같다.
- 인덱스가 없기 때문에 Group By를 하는 과정에서 Seq Scan으로 테이블을 풀스캔하고 있다.
- Group By를 하는 과정에서 커버링 인덱스를 사용할 수 있도록 아래와 같이 인덱스를 생성해 보자
create index idx_order_details_order_id_create_date_order_detail_id
on order_details(order_id, create_date, order_detail_id);- 이제 다시 실행계획을 살펴보자.
- Group By를 하는 과정에서 커버링 인덱스를 사용하는 것을 확인할 수 있다.
- 성능도 132ms -> 90ms로 줄었다.
QueryDSL-JPA를 사용해서 쿼리를 2개로 분리해서 실행
- 실행할 쿼리는 아래와 같다.
select od.order_id,
od.create_date,
max(od.order_detail_id) as max_order_detail_id
from order_details od
where (
od.order_id, od.create_date
) in (
select sub.order_id,
max(sub.create_date)
from order_details sub
group by sub.order_id
)
group by od.order_id,
od.create_date;select orders.order_id,
orders.order_date,
orders.customer_name
from orders;- 먼저 인덱스가 없는 경우부터 살펴보자.
- 인덱스가 없는 경우는 107ms + 2ms = 109ms 가 나왔다.
- 이번에도 인덱스를 생성하고 실행 계획을 살펴보자.
- 인덱스가 없는 경우에는 88ms + 2ms = 90ms 가 나왔다.
- 시간적인 측면에서도 Where 서브쿼리보다 좋은 성능을 보이고 있진 않다.
- 여기서 네트워크 I/O도 1번 더 수행되기 때문에 Where 서브쿼리보다는 성능적으로 좋지 않을 것 같다.
쿼리 3번으로 나눠서 실행 후 조립
- 실행할 쿼리는 아래와 같다.
select (order_details.order_id || order_details.create_date),
max(order_details.order_detail_id) as max_order_detail_id
from order_details
group by order_details.order_id,
order_details.create_date;select order_details.order_id,
max(order_details.create_date) as max_create_date
from order_details
group by order_details.order_id;select orders.order_id,
orders.order_date,
orders.customer_name
from orders;- 먼저 인덱스가 없는 경우부터 살펴보자.
- 실행시간을 보면 46ms + 45ms + 2ms = 93ms가 나왔다.
- 이번에도 인덱스를 생성하고 실행 계획을 살펴보자.
- 인덱스가 있는 경우 26ms + 29ms + 2ms = 57ms가 나왔다.
- 쿼리를 3개로 나눴을 경우, 쿼리가 간단해지기 때문에 인덱스 사용을 유도하기 좋다는 생각이 들었다.
- 시간적인 측면에서도 가능 성능이 좋다.
- 하지만 네트워크 I/O가 3번이고, 쿼리 실행 후 애플리케이션에서 결과를 조합하는 시간도 소요된다.
- 그렇기 때문에 실제로 로직을 실행해 보고 비교해 봐야 한다.
실제 실행시간 비교
각각의 로직을 1000번 반복해서 실행시간을 살펴보자. 인덱스가 있는 경우로 성능을 비교했다.
QueryDSL-JPA를 사용한 Where절 서브쿼리 실행
@Test
@DisplayName("Where절 서브쿼리 테스트")
public void whereSubQueryTest() {
for (int i = 0; i < 1000; i++) {
orderDetailRepository.searchOrdersWithWhereSubQuery();
}
}
- where 서브쿼리 실행 결과는 1분 5초가 걸렸다.
QueryDSL-JPA를 사용해서 쿼리를 2개로 분리해서 실행
@Test
@DisplayName("2개의 쿼리로 분리")
public void twoQueryTest() {
for (int i = 0; i < 1000; i++) {
orderDetailRepository.selectTwoQuery();
}
}
- 2개의 쿼리로 분리한 경우 1분 23초가 걸렸다.
- 예상대로 where 서브쿼리보다 오래 걸렸다.
- 이유는 쿼리를 분리했지만, 서브쿼리가 여전히 존재하기 때문에 쿼리 수행시간에서 이득을 보지 못했고 오히려 네트워크 I/O만 늘었기 때문이다.
QueryDSL-JPA를 사용해서 쿼리를 3개로 분리해서 실행
@Test
@DisplayName("3개의 쿼리로 분리")
public void selectThreeQueryTest() {
for (int i = 0; i < 1000; i++) {
orderDetailRepository.selectThreeQuery();
}
}
- 쿼리를 3개로 분리하여 서브쿼리를 아예 사용하지 않은 경우가 가장 빠른 결과를 보였다.
- 물론 위의 전제는 인덱스를 효율적으로 사용했다는 가정이다.
정리
- 서브쿼리는 꼭 필요한 경우가 아니면 사용하지 않는 것이 좋은 것 같다.
- 서브쿼리 사용이 불가피하다면, 꼭 인덱스를 활용해서 서브쿼리의 실행을 최적화하는 작업이 필요하다.
- 서브쿼리를 사용하지 않고, 쿼리를 쪼개서 실행하면 쿼리가 간단하기 때문에 인덱스를 효율적으로 사용하도록 유도하기 쉽다.
- 쿼리를 나눠서 실행하면 네트워크 I/O가 늘어나지만, 인덱스를 효율적으로 사용한다는 전제 하여 쿼리 성능이 훨씬 빨라지기 때문에 결론적으로는 실행 시간이 단축될 확률이 높다.
- 사실 쿼리의 형태, 데이터의 형태, 테이블의 성격에 따라서 옵티마이저가 수립하는 실행계획이 달라지기 때문에, 위의 테스트로 어떤 것이 좋다고 단정 지을 수는 없다.
- 따라서 상황에 따라 실행계획을 살펴보고, 적절한 인덱스를 설계해서 그때그때 더 효율적인 방법을 찾아야 한다.
- 하지만 내가 내린 결론은 가능하면 쿼리는 간단하게 짜고, 인덱스를 효율적으로 활용할 수 있는 쿼리로 실행하는 것이 베스트인 것 같다.
- 간단한 쿼리로 실행하는 것이 유지보수 측면에서도 다른 개발자들이 쿼리를 이해하기 쉽기 때문에 좋은 것 같다.
'Database' 카테고리의 다른 글
| PostgreSQL Logical Structure 이해하기 (0) | 2023.12.11 |
|---|---|
| PostgreSQL 아키텍처 이해하기 (0) | 2023.12.08 |
| 서브쿼리 변환하기 1편 (이론) (0) | 2023.11.26 |
| PostgreSQL 실행계획 분석하기 6편 (서브쿼리) (0) | 2023.11.25 |
| PostgreSQL 실행계획 분석하기 5편 (Aggregates) (0) | 2023.11.25 |