[querydsl] mysql filesort관련 group by 성능 최적화

mysql 환경에서 querydsl 를 도입하면서 성능 저하 문제를 피하기 위해 고민하고 있습니다. 데이터 조회 시 사용하는 group by 쿼리에서 불필요한 filesort 로 성능저하가 발생할 수 있습니다. filesort는 MySQL 서버가 메모리 버퍼에 담을 수 없는 방대한 데이터를 정렬하기 위해 임시 파일을 사용하는 방식입니다. 이는 불필요한 디스크 I/O 작업을 증가시켜 쿼리 실행 속도를 크게 저하시킵니다.

이번 포스트에서는 querydsl group by 쿼리에서 발생하는 filesort 문제를 분석하고, order_by_null 옵션을 활용하여 Filesort를 효과적으로 방지하는 방법을 소개합니다.

MySQL 정렬 방식

MySQL은 데이터 정렬을 위해 다음 두 가지 방식을 사용합니다.

1. 인덱스 정렬
   데이터가 이미 정렬된 상태로 저장된 인덱스를 활용하여 정렬 작업을 수행합니다. 인덱스 정렬은 메모리 접근만으로 이루어지기 때문에 Filesort에 비해 훨씬 빠른 속도를 제공합니다. MySQL Optimizer는 인덱스 정렬이 가능한 실행계회에 우선순위를 두고 선택합니다.
   • 장점: 메모리 접근만으로 이루어지기 때문에 Filesort에 비해 훨씬 빠른 속도를 제공합니다. 쿼리 실행 계획에 using index라는 메시지가 표시되어 정렬 방식을 쉽게 확인할 수 있습니다.
   • 단점: 인덱스가 많아지면 InnoDB 버퍼 풀을 위한 메모리 사용량이 증가합니다.
2. Filesort
   • GROUP BY 또는 ORDER BY 절에 인덱스를 사용할 수 없는 경우
   • 정렬 대상 데이터가 메모리 버퍼 크기를 초과하는 경우
   • 복잡한 쿼리 조건으로 인해 인덱스 사용이 비효율적인 경우
   인덱스를 사용할 수 없는 경우에 MySQL은 데이터를 임시 파일에 저장하고 외부 정렬 알고리즘을 사용하여 정렬 작업을 수행합니다. Filesort는 다음과 같은 경우 발생합니다.
   • 장점: 인덱스 정렬 방식에 비해 유연하게 사용할 수 있습니다. ORDER BY 절에 여러 컬럼을 지정하거나 복잡한 정렬 조건을 사용할 수 있습니다.
   • 단점: 데이터량이 많거나 정렬 대상 데이터가 메모리 버퍼 크기를 초과하면 성능 저하가 발생합니다. 쿼리 실행 계획에 using filesort라는 메시지가 표시되어 성능 문제 진단에 도움이 됩니다.

Filesort 발생 원인

Querydsl Group By 쿼리에서 Filesort가 발생하는 주요 원인을 정리하자면 다음과 같습니다.

1. Group By 대상 컬럼에 인덱스가 없는 경우

MySQL에서 group by 쿼리 실행 시 group by 대상 컬럼에 인덱스를 사용하여 데이터를 그룹화합니다. 하지만 group by 대상 컬럼에 인덱스가 없는 경우, MySQL은 데이터를 전체 테이블에서 스캔하여 그룹화해야 하며 이 과정에서 Filesort가 발생하게 됩니다.

2. ORDER BY 절에 Group By 대상 컬럼이 없는 경우

ORDER BY 절에 group by 대상 컬럼이 없는 경우, MySQL은 먼저 group by 작업을 수행한 후 결과 데이터를 다시 정렬해야 합니다. 이때 Group By 결과 데이터가 메모리 버퍼 크기를 초과하면 filesort가 발생하게 됩니다.

Filesort 발생 시 문제점

Filesort 발생 시 다음과 같은 문제점이 발생할 수 있습니다.

1. 쿼리 실행 속도 저하

Filesort는 다음과 같은 과정에서 불필요한 디스크 I/O 작업을 증가시켜 쿼리 실행 속도를 크게 저하시킵니다.

1. 정렬 대상 데이터를 메모리 버퍼에 담을 수 없는 경우, MySQL은 데이터를 임시 파일에 저장합니다.
2. 임시 파일에 저장된 데이터를 외부 정렬 알고리즘을 사용하여 정렬합니다.
3. 정렬된 데이터를 다시 메모리에 로드하여 쿼리 결과에 반영합니다.

만약 여러 번 서비스 요청 등 많은 데이터 조회시 쿼리 실행 시간이 지연되어 사용자 경험에 악영향을 미칠 수 있습니다.

2. 서버 부하 증가

Filesort는 CPU 및 메모리 자원을 많이 소모하여 서버 부하를 증가시킵니다.

• CPU 사용량 증가: 외부 정렬 알고리즘은 CPU를 집중적으로 사용합니다.
• 메모리 사용량 증가: 정렬 대상 데이터가 메모리 버퍼에 담을 수 없는 경우, 임시 파일을 저장하기 위한 추가 메모리가 필요합니다.
• 디스크 I/O 사용량 증가: 임시 파일 읽고 쓰기 작업이 증가하여 디스크 I/O 사용량이 증가합니다.

이는 다른 쿼리 실행 속도에도 영향을 미쳐 전체 시스템 성능 저하를 초래할 수 있습니다.

Filesort 방지 방법

1. 인덱스 활용
   ORDER BY 절에 지정된 컬럼에 인덱스를 생성하면 MySQL은 인덱스를 사용하여 데이터를 정렬하므로 Filesort를 방지할 수 있습니다.
2. order_by_null 옵션 활용
   ORDER BY 절에 group by 대상 컬럼을 포함하고, 해당 컬럼 값이 null인 경우 맨 앞 또는 맨 뒤로 정렬하도록 지정하면 Filesort를 방지할 수 있습니다.
3. 쿼리 최적화
   • WHERE 절 조건 활용: 불필요한 데이터를 쿼리 결과에서 제외하여 정렬 대상 데이터의 양을 줄일 수 있습니다.
   • SELECT 절 최적화: 필요한 컬럼만 SELECT하여 정렬 대상 데이터의 양을 줄일 수 있습니다.
   • GROUP BY 절 최적화: group by 대상 컬럼을 최소화하여 group by 작업에 소요되는 시간을 줄일 수 있습니다.

QueryDsl에서 order_by_null 옵션 활용

이번 프로젝트에서 Querydsl group by 쿼리에서 filesort를 방지하기 위해 order_by_null 옵션을 사용하고 있습니다. 다음은 프로젝트에서 사용중인 코드를 예시로 만들었습니다.

fun searchAll(
        searchParam: SearchParam,
    ): List<ProductDetail> = queryFactory.selectFrom(productDetail)
        .where(
            buildConditions(searchParam)
        )
        .applyGroupBy(searchParam.groupByList)
        .orderBy(OrderByNull.DEFAULT).fetch()

searchParam에 있는 groupByList에 있는 groupBy 대상 컬럼들이 인덱스에 없다면? querydsl에서 group by 대상 기준으로 using filesort옵션이 활성화되어 정렬을 하게 됩니다. 아직 querydsl에서는 using filesort을 조작할 수 있는 유틸함수를 제공해주지 않는 것 같았습니다. 별도로OrderByNull 클래스를 만들어 group by 시 사용하고 있습니다.

/**
 * group by 시 filesort 방지
 */
class OrderByNull private constructor() : OrderSpecifier<Comparable<*>>(
    Order.ASC, Expressions.nullExpression(), NullHandling.Default
) {
    companion object {
        val DEFAULT: OrderByNull = OrderByNull()
    }
}

마무리

Querydsl group by 쿼리에서 filesort는 쿼리 실행 속도를 크게 저하시키고 서버 부하를 증가시키는 문제점을 야기합니다. 따라서 인덱스 활용, order_by_null 옵션 활용, 쿼리 최적화 등의 방법을 통해 filesort를 방지하고 쿼리 성능을 향상시키는 것이 중요합니다.

참고자료

• https://jojoldu.tistory.com/477