내장함수로 감싸진 칼럼의 order by 최적화는 불가능할까요?

Question

내장 함수를 사용한 경우에도 Order By 최적화가 가능할까요?   예를 들어 아래와 같이 쿼리를 작성했는데, MONTH로 감싸진 칼럼에는 인덱스 적용이 안되서, using temporary와 using filsort가 나왔는데요. 내장함수를 사용해서 그루핑 및 정렬하는 경우에는 어쩔수 없는지 궁금합니다. select Month(tb1.id) as MONTH, count(tb1.id), SUM(tb1.price), count(tb2.id) from tb1 tb1 left join tb2 tb2 on tb1.id = tb2.tb1_id where tb1.created_at BETWEEN '2024-01-01' and '2024-05-01' group by MONTH order by MONTH; 실제 운영상황에서도 이런 쿼리 요구사항이 있다고 할때에는 쿼리를 분리해서 여러번 처리하고 애플리케이션 단에서 처리해야할까요?

yjm9505168574 · Answer

안녕하세요. 질문 남겨주셔서 감사합니다~ 함수를 적용한 칼럼은 일반적으로 인덱스가 적용되지 않아요. 지금의 문제를 봤을 때 MySQL 의 함수형 인덱스를 사용하면 문제를 해결해볼 수 있을 것 같긴 합니다.   다음 쿼리처럼 함수를 적용한 칼럼을 인덱스로 만들면 인덱스가 사용될거에요. 근데 이건 MySQL 8.0 버전부터 추가된 기능인 점을 참고하셔야합니다.   CREATE INDEX idx_month ON tb1 (MONTH(tb1.created_at));

communityai8509 · Answer

안녕하세요, 인프런 AI 인턴입니다. 일반적으로, SQL 쿼리 내에서 내장 함수를 사용하여 칼럼 값을 변형한 후에 그 결과를 기준으로 정렬( ORDER BY )을 수행하는 경우, 해당 칼럼에 대해 인덱스를 사용한 최적화가 어렵습니다. 이는 인덱스는 칼럼의 원래 값에 기반하여 구성되기 때문에, 값이 함수에 의해 변형될 경우 인덱스를 효율적으로 활용할 수 없게 되기 때문입니다. 특히, MONTH() 와 같은 날짜 처리 함수를 사용했을 때, 이러한 현상이 두드러지는데, 이는 함수로 처리된 결과에 대한 인덱스가 없기 때문에 전체 테이블 스캔 또는 소트 작업( using temporary , using filesort )을 통해 결과를 도출하기 때문입니다. 이 문제를 해결하기 위한 몇 가지 접근 방법은 있으나, 상황에 따라 적용 가능성과 효율성이 달라질 수 있습니다: 미리 계산된 필드 사용 : 가능하다면, 데이터베이스에 월 정보를 직접 저장할 수 있는 새로운 칼럼을 추가하고, 이 칼럼에 대해 인덱스를 생성하여 사용하는 방법을 고려할 수 있습니다. 이렇게 하면 직접 인덱스를 활용하여 정렬할 수 있습니다. 집계 테이블 활용 : 특히 큰 데이터 세트에 대해 반복적으로 같은 집계를 수행해야 하는 경우, 집계 결과를 저장하는 별도의 테이블을 사용하고, 이 테이블에 인덱스를 적용하는 방식을 고려할 수 있습니다. 이 방법은 데이터가 자주 업데이트되지 않는 경우에 특히 유용합니다. 애플리케이션 레벨에서의 후처리 : 경우에 따라서는 데이터베이스에서 가능한 가까운 결과를 빠르게 추출한 뒤, 최종 정렬이나 추가 필터링을 애플리케이션 코드에서 처리하는 방법이 더 효율적일 수 있습니다. 그러나, 특정 경우에 최적의 해결 방안을 선택하기 위해서는 쿼리가 수행되는 데이터의 크기, 사용 빈도, 데이터 업데이트 빈도, 애플리케이션의 처리 능력 등 여러 요소를 고려해야 합니다. 질문하신 경우에는, 가능하다면 상황에 맞는 최적의 접근 방법을 선택하여 적용해보시는 것이 좋습니다. 데이터베이스 설계 단계에서부터 이러한 요소를 고려하는 것이 이후 성능 최적화에 큰 도움이 될 수 있습니다.