TIL 2024-01-09 쿼리최적화란

 

쿼리 최적화란

성능이 낮은 쿼리의 응답 속도를 개선하는 작업입니다.

1. SELECT 에는 꼭 필요한 컬럼만
2. WHERE절에 가급적이면 별도의 연산을 하지않는 것을 권장여기서 Inefficient 쿼리의 경우, Full Table Scan을 하면서 모든 Cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단해야 합니다. 반편 Improved 쿼리의 경우 기존에 r.value 가 가지고 있는 index를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 Inefficient 쿼리 대비 더 짧은 러닝 타임을 가질 수 있다.
3. -- Inefficient SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count FROM movie m INNER JOIN rating r ON m.id = r.movie_id WHERE FLOOR(r.value/2) = 2 GROUP BY m.id; -- Improved SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count FROM movie m INNER JOIN rating r ON m.id = r.movie_id WHERE r.value BETWEEN 4 AND 5 GROUP BY m.id;
4. LIKE 사용 시 %(와일드 카드)은 String 앞 부분에 배치하지 않는 것이 좋다
   1. 두 번째와 같은 원리로 Index를 활용하지 않고 Full Table Scan이 됩니다. 따라서 같은 결과를 낼 수 있다면 LIKE ‘%…’ 보다는 LIKE ‘..%..’ or LIKE ‘….%’ 을 사용하는 것이 바람직 합니다.
5. SELECT DISTINCT, UNION DISTINCT와 같이 중복 값을 제거하는 연산은 최대한 자제
   1. GROUP BY를 써서 대체하자
6. 같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING 보다는 WHERE절을 사용하자
   1. 쿼리 실행 순서에서, WHERE 절이 HAVING 절 보다 먼저 실행됩니다.
   2. 따라서 WHERE 절로 미리 GROUP BY에서 쓸 데이터 크기를 작게 만들면 효율적인 연산이 가능합니다.
7. 3개 이상의 테이블을 INNER JOIN 할 때는, 크기가 가장 큰 테이블을 FROM 절에, INNER JOIN 절에는 남은 테이블을 작은 순서대로 배치하는 것을 추천하지만 항상 통용되지는 않습니다.
   1. 간단한 INNER JOIN의 경우는 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 INNER JOIN의 순서를 바꾸기 때문입니다. 예를 들어서, 아래의 두 쿼리는 서로 다른 INNER JOIN 순서를 가짐에도, 결과적으로 실행 시간에는 차이가 없습니다. (순서대로 INNER JOIN 된다면, Query (B)가 (A)보다 훨씬 적은 시간이 걸려야 할 것입니다.)
   2. 그러나 테이블의 개수가 늘어난다면, 탐색해야 할 INNER JOIN 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용의 증가로 이어집니다. 그러다 보면 언젠가는 비싼 Planning 비용을 들이고 가장 최적의 순서를 찾는 것보다, 차선의 INNER JOIN 순서로 쿼리를 실행하더라도 Planning 비용을 줄이는 것이 결과적으로는 더 효과적인 상황이 올 것입니다.
   3. 그렇기 때문에 복잡한 쿼리에서는 완전하게 최적화되지 않은 INNER JOIN 연산이 실행될 때가 있습니다. (개인적으로는 BigQuery를 사용할 때, 임시 테이블을 많이 만들다 보면 이 같은 상황이 발생하는 것을 종종 경험하곤 합니다) 따라서 이를 사전에 방지하기 위해 최적화된 INNER JOIN 순서를 입력 단계에서 조정해 두는 것은 분명 도움이 될 것입니다. INNER JOIN의 최적화 여부가 연산량에 미치는 영향력은 상당히 크기 때문입니다.
   4. 안타깝게도, 예시에서 활용되는 3개의 테이블을 가지고는 Query Planner의 성능을 넘을 수 있을 정도로 복잡한 예시 쿼리를 만들어 볼 수는 없습니다.
8. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리 된 테이블을 따로 보관/관리하는 것도 좋다
   1. RDBMS 원칙에 어긋나는 측면도 있고, DB의 실시간성을 반영하지 못할 가능성이 높기 때문에, 운영계보다는 분석계에서 더 많이 사용되곤 합니다.
   2. 예를 들면 사용자에 의해 발생한 Log 데이터 중에서 필요한 Event만 모아서 따로 적재해두는 것, 혹은 핵시 서비스 지표를 주기적으로 계산해서 따로 모아두는 것 등이 가장 대표적으로 볼 수 있는 사례입니다.