Cộng đồng Hỏi & Đáp của Inflearn
câu hỏi đã được viết
내장함수로 감싸진 칼럼의 order by 최적화는 불가능할까요?
Viết
·
218
0
내장 함수를 사용한 경우에도 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;실제 운영상황에서도 이런 쿼리 요구사항이 있다고 할때에는 쿼리를 분리해서 여러번 처리하고 애플리케이션 단에서 처리해야할까요?
Câu trả lời 2
1
안녕하세요. 질문 남겨주셔서 감사합니다~
함수를 적용한 칼럼은 일반적으로 인덱스가 적용되지 않아요. 지금의 문제를 봤을 때 MySQL 의 함수형 인덱스를 사용하면 문제를 해결해볼 수 있을 것 같긴 합니다.
다음 쿼리처럼 함수를 적용한 칼럼을 인덱스로 만들면 인덱스가 사용될거에요. 근데 이건 MySQL 8.0 버전부터 추가된 기능인 점을 참고하셔야합니다.
CREATE INDEX idx_month ON tb1 (MONTH(tb1.created_at));0
안녕하세요, 인프런 AI 인턴입니다.
일반적으로, SQL 쿼리 내에서 내장 함수를 사용하여 칼럼 값을 변형한 후에 그 결과를 기준으로 정렬(ORDER BY)을 수행하는 경우, 해당 칼럼에 대해 인덱스를 사용한 최적화가 어렵습니다. 이는 인덱스는 칼럼의 원래 값에 기반하여 구성되기 때문에, 값이 함수에 의해 변형될 경우 인덱스를 효율적으로 활용할 수 없게 되기 때문입니다.
특히, MONTH()와 같은 날짜 처리 함수를 사용했을 때, 이러한 현상이 두드러지는데, 이는 함수로 처리된 결과에 대한 인덱스가 없기 때문에 전체 테이블 스캔 또는 소트 작업(using temporary, using filesort)을 통해 결과를 도출하기 때문입니다.
이 문제를 해결하기 위한 몇 가지 접근 방법은 있으나, 상황에 따라 적용 가능성과 효율성이 달라질 수 있습니다:
미리 계산된 필드 사용: 가능하다면, 데이터베이스에 월 정보를 직접 저장할 수 있는 새로운 칼럼을 추가하고, 이 칼럼에 대해 인덱스를 생성하여 사용하는 방법을 고려할 수 있습니다. 이렇게 하면 직접 인덱스를 활용하여 정렬할 수 있습니다.
집계 테이블 활용: 특히 큰 데이터 세트에 대해 반복적으로 같은 집계를 수행해야 하는 경우, 집계 결과를 저장하는 별도의 테이블을 사용하고, 이 테이블에 인덱스를 적용하는 방식을 고려할 수 있습니다. 이 방법은 데이터가 자주 업데이트되지 않는 경우에 특히 유용합니다.
애플리케이션 레벨에서의 후처리: 경우에 따라서는 데이터베이스에서 가능한 가까운 결과를 빠르게 추출한 뒤, 최종 정렬이나 추가 필터링을 애플리케이션 코드에서 처리하는 방법이 더 효율적일 수 있습니다.
그러나, 특정 경우에 최적의 해결 방안을 선택하기 위해서는 쿼리가 수행되는 데이터의 크기, 사용 빈도, 데이터 업데이트 빈도, 애플리케이션의 처리 능력 등 여러 요소를 고려해야 합니다.
질문하신 경우에는, 가능하다면 상황에 맞는 최적의 접근 방법을 선택하여 적용해보시는 것이 좋습니다. 데이터베이스 설계 단계에서부터 이러한 요소를 고려하는 것이 이후 성능 최적화에 큰 도움이 될 수 있습니다.