페이징 쿼리 개선-커버링 인덱스

인턴 간 맡은 도메인의 페이징 쿼리를 개선해 본 내용입니다.

---

1. 상황

아래는 도메인 이해를 위해 간략히 배송 도메인의 ER 다이어그램을 나타낸 것이다.

• 주문 상품을 단위로 배송이 등록
• 여러 주문 상품이 하나의 배송에 매핑 가능(묶음 배송)

그럼 배송 조회 페이징에 사용되는 쿼리를 살펴보자.

SELECT
dp.id,
d.created_at, d.last_transport_status, d.delivery_company, d.invoice_no,
op.order_product_status, op.product_id, op.name, op.order_product_bundle_id, op.delivery_type,
ore.name, ore.street_address, ore.detail_street_address, ore.cell_phone_number    
FROM new_delivery_product AS dp
JOIN new_delivery AS d ON dp.delivery_id = d.id
JOIN new_order_product AS op ON dp.delivery_order_product_id = op.id
JOIN new_order_receiver AS ore ON d.delivery_order_id = ore.order_id
WHERE op.order_product_status IN ('배송중', '배송완료')
    AND op.delivery_type IN ('DELIVERY')
    AND op.delivery_started_at BETWEEN '2023-12-01' AND '2024-01-10'
ORDER BY dp.id DESC
LIMIT 150000, 30;

 

조회 쿼리에서는 배송 테이블 정보 뿐만 아니라, 주문/주문 상품/주문자 테이블 정보도 필요하다.

필터링 조건은 주문 상품 상태, 배송 방법, 기간이 존재한다.

 

아래와 같은 상황에서 쿼리를 실행 해보면, 약 10초가 걸린다.

• MySQL innodb 8.0.33 / 주문 상품 3백만개 / 주문 상품(3)-배송(1)

페이징 조회가 기본적으로 느린 이유는 이전에 읽었던 행을 반복적으로 읽기 때문이다.

만약 10,000번째 부터 10개를 읽기 위해선 10,010개의 행을 읽어야하고, 10,000 행은 버려지게 된다.

뒤로 갈수록 버려지지만 읽어야하는 행은 많아지는 것이다.

 

또, 페이징 쿼리를 비롯해 일반적인 조회 쿼리가 느린 이유는 select 절 때문이다.

order by, limit 등을 처리할 때, 인덱스(커버링 인덱스가 아닌)가 타더라도 레코드 별로 I/O 작업이 발생한다.

 

페이징 쿼리를 개선시키는 방법으로 커버링 인덱스, NoOffset 방식이 존재한다.

기본적으로 두가지 방식 중 NoOffset 방식이 성능면에서 뛰어나다.

하지만, 현재 NoOffset을 적용시킬 수는 없는 상황이었기에 커버링 인덱스를 사용해 쿼리를 개선해보고자 한다.

---

2. 커버링 인덱스 

커버링 인덱스란, 쿼리를 충족시키는 데 필요한 모든 데이터를 가지고 있는 인덱스

쉽게 말해, WHERE/GROUP BY/ORDER BY 등에 사용되는 모든 컬럼이 인덱스에 포함된 것을 말한다.

커버링 인덱스를 사용하게 되면, 조회에 필요한 컬럼이 모두 인덱스에 있기 때문에 데이터 블록에 접근하지 않아도 된다.

 

앞서 언급한거와 같이 조회 쿼리가 느린 이유는 select 절로 인해 레코드 별로 I/O 작업이 발생하기 때문이라 했다.

일반 페이징 쿼리에서 limit 10000, 10 처리 시, 버려지는 10,000개의 레코드 읽기에 더불어 데이터 블록에도 접근하므로 성능 저하가 발생한다.

이에 커버링 인덱스를 적용하게 되면 필요한 10개의 레코드를 빠르게 Id로 조회한 후, 데이터 블록에 접근하므로 성능을 향상시킬 수 있다.

 

커버링 인덱스를 사용했을 때의 동작 예시이다.

 

위의 쿼리에서 new_order_product 테이블의 PK를 커버링 인덱스로 탐색하게 된다.

커버링 인덱스를 적용했을 때, 예시 쿼리이다.

SELECT *
FROM new_order_product AS op
    JOIN (
       SELECT id
        FROM new_order_product
        WHERE order_product_status IN('배송중', '배송완료')
        AND delivery_type IN('DELIVERY')
        AND delivery_started_at BETWEEN '2023-12-01' AND '2024-01-10'
        ORDER BY id DESC
        LIMIT 100000, 30
    ) AS sub_op ON op.id = sub_op.id
ORDER BY op.id;

 

2.1 커버링 인덱스 적용

현재 QueryDSL를 사용하고 있다.

기본적으로 JPQL은 인라인 뷰를 지원하지 않기 때문에, QueryDSL도 인라인 뷰(from절 서브 쿼리)를 사용할 수 없다.

인라인 뷰를 사용하기 위해 다른 우회 방법(JdbcTemplate, JPASQLQuery 등)도 존재하지만, 사용 과정의 어려움/type safe 등의 단점이 있어 현재는 2개의 쿼리로 분리해 사용했다.

 

커버링 인덱스를 사용하기 위해선 where, orderby, group by 절의 컬럼을 모두 포함해야된다.

카디널리티를 고려하여 status, delivery_type, delivery_startDate 컬럼 순으로 복합 인덱스를 생성했다.

또, 복합 인덱스에서 범위 조건이 선행 컬럼에 존재한다면, 후행 컬럼이 인덱스를 타지 않기 때문에 BETWEEN 조건 컬럼을 마지막에 배치했다.

(인덱스와 복합 인덱스를 추가로 알고 싶다면 해당 링크을 참고하면 된다 - 개인 노션 정리)

CREATE INDEX idx_delivery_status_type_date ON new_order_product (order_product_status, delivery_type, delivery_started_at);

 

그럼 구현 코드를 살펴보자.

총 2개 쿼리가 발생하고, 동작방식은 아래와 같다.(현재 count 쿼리는 제외 했다.)

1.  order_product 테이블을 커버링 인덱스로 빠르게 PK만 조회
2. 조회된 PK가 없을 경우 바로 종료
   • 불필요한 쿼리 방지 및 in절에 null이 들어가는 것을 방지
3. 조회된 PK를 대상으로 join 및 select 실행 
   • in절은 정렬순서를 보장할 수 없어, order by를 추가로 사용

단일 쿼리보다 깔끔하지는 않는 것 같다.

또한, 2개의 쿼리로 인해 DB 네트워크 통신도 2번 발생하기 때문에, 어쩌면 성능이 더 떨어질 수 도 있을거 같다.

실제 성능 차이를 비교해보자.

2.3 성능 분석

@Test
void 페이징_성능_측정() {
	//given
	final PageRequest pageRequest = PageRequest.of(1500, 30);
	final List<OrderProductStatus> status = List.of(OrderProductStatus.DELIVERY_PREPARING,
														OrderProductStatus.DELIVERING);
	final List<DeliveryType> types = List.of(DeliveryType.DELIVERY);
	final LocalDate fromDate = LocalDate.of(2023, 12,1);
	final LocalDate toDate = LocalDate.of(2024, 1,10);
		
	DeliveryDetailCond cond = new DeliveryDetailCond(pageRequest, status, types, fromDate, toDate);
		
	//when
	Page<AdminDeliveryDetailQuery> result = deliveryQueryDSLQueryDao.findDeliveryDetail(cond);
		
	//then
	assertThat(result.getContent().size()).isEqualTo(30);
}

 

2.3.1 기존 QueryDSL 페이징 성능

기존 쿼리는 성능 시간은 약 9.5초 정도가 걸린다.

 

2.3.2 커버링 인덱스 QueryDSL 페이징 성능

커버링 인덱스를 사용한 성능 시간은 약 0.37초 정도로 기존보다 크게 개선이 됬다.

2번의 쿼리가 발생하지만 성능이 더 뛰어난 것을 알 수 있다.

 

2.4 IN절 인덱스

QueryDSL 동적 쿼리에서 조금 더 고민해 볼 부분이 있다.

현재 where문에 조건이 없는 경우 null을 반환하여, 조건절에서 아예 제외하는 방식으로 동작한다.

 

만약 인덱스 선행컬럼이 제외된다면, 인덱스가 타지 않기 때문에 성능저하가 발생할 것으로 생각했다.

테스트 코드는 위와 동일하고, 두번째 인덱스 컬럼의 조건이 컬럼값을 모두 포함한 List.of(DeliveryType.DELIVERY, DeliveryType.QUICK)때와 null 일때, 실제 수행 시간을 살펴보자.

@Test
void IN절_인덱스() {
    ...
    // 경우1.컬럼값이 모두 포함된 경우
    final List<DeliveryType> types = List.of(DeliveryType.DELIVERY, DeliveryType.QUICK);
    
    // 경우2.조건에 제외된 경우
    final List<DeliveryType> types = null;
    ...
}

 

2.4.1 컬럼을 모두 포함한 IN절

인덱스를 모두 타기 때문에 약 0.7초로 성능이 좋다.

 

2.4.2 두번째 인덱스 컬럼이 조건절에 제외(Querydsl null 처리)

인덱스가 타지 않으므로 약 4초로 성능 저하가 발생한 것을 볼 수 있다.

 

따라서, IN절 조건이 없는 경우 제외하는 것보다는 컬럼 전체 값을 IN절에 포함시키는 것이 더 좋다.

상태값을 Enum으로 관리한다면 더 편리하게 모든 값을 조회할 수 있을 것이다.

---

3. 마치며

NoOffset 더불어 커버링 인덱스로 페이징 쿼리를 크게 개선할 수 있었다.

개선하면서 장점도 많이 존재했지만 단점도 조금 존재하는 거 같다.

• 커버링 인덱스를 위해 많은 컬럼과 많은 인덱스가 필요하다.
• 결국 데이터가 쌓이고 페이지 번호가 뒤로 갈수록 인덱스를 타더라도 성능이 저하된다.