튜닝전 / 후 쿼리

아래 좋은 article 을 발견해서 두고 두고 보려고 내 velog 에 정리하고자 한다.
글을 읽으면서 그동안 리소스 관점에서 놓치고 짠 쿼리가 스쳐 지나갔다.. 🙉

특히 LIKE 구문에 대한 내용을 다룬 3 번은 내가 자주 했던 실수인데, 이번 기회에 좀 더 좋은 쿼리를 짤 수 있을 것 같다.

🌟 Reference

1. SELECT 시 필요한 컬럼만 호출

-- Inefficient
SELECT * FROM movie;

-- Improved
SELECT id FROM movie;

• 많은 필드 값을 불러올 수록 DB는 더 많은 로드를 부담한다.
• 컬럼 중에 불필요한 값을 가진 필드가 있다면 과감히 제외하고, 꼭 필요한 컬럼만 불러오자.

2. 조건 내에 기존 DB 값에 별도의 연산을 걸지 않음

-- 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;

• Inefficient 쿼리의 경우, Full Table Scan 을 하면서 모든 Cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단.
• Improved 쿼리의 경우 기존 r.value 가 가지고 있는 index 를 그대로 활요할 수 있기 때무넹 모든 필드 값을 탐색할 필요가 없어 더 짧은 Running Time 을 가짐.

3. LIKE 사용 시 와일드카드 문자열(%) dmf String 앞 부분에 배치 x

• 2번 과 같은 원리로 value IN (...), value="...", value LIKE "...%" 와는 달리, value LIKE "%..." 는 Full Table Scan 을 활용한다. 따라서 같은 결과를 낸다면 와일드카드를 앞부분에 배치하는 것은 피하는 것이 좋다.

아래 Comedy 와 Romantic Comedy 를 추출하려는 예시를 살펴보자.

-- Inefficient
SELECT       g.value genre, COUNT(r.movie_id) r_cnt
FROM         rating r
INNER JOIN   genre g
ON           r.movie_id = g.movie_id
WHERE        g.value LIKE "%Comedy"
GROUP BY     g.value;

-- Improved(1): value IN (...)
SELECT      g.value genre, COUNT(r.movie_id) r_cnt
FROM        rating r
INNER JOIN  genre g
ON          r.movie_id = g.movie_id
WHERE       g.value IN ("Romantic Comedy", "Comedy")
GROUP  BY   g.value;

-- Improved(2): value = "..."
SELECT      g.value genre, COUNT(r.movie_id) r_cnt
FROM        rating r
INNER JOIN  genre g
ON          r.movie_id = g.movie_id
WHERE       g.value = "Romantic Comedy" OR g.value = "Comedy"
GROUP BY    g.value;

-- Improved(3): value LIKE "...%"
-- 🌟 모든 문자열을 탐색할 필요가 없어, 가장 좋은 성능을 냄
SELECT      g.value genre, COUNT(r.movie_id) r_cnt
FROM        rating  r
INNER JOIN  genre  g
ON          r.movie_id = g.movie_id
WHERE       g.value LIKE "Romantic%" OR g.value LIKE "Comed%"
GROUP BY    g.value

4. SELECT DISTINCT, UNION DISTINCT 등 중복 값 제거 연산은 최대한 사용 x

• 중복 값을 제거하는 연산은 많은 시간이 걸림.
• 사용이 불가피한 상황이라면, DISTINCT 연산 대체 또는 연산 대상 테이블의 크기를 최소화해야 한다.
  • EXISTS 활용

-- Inefficient
SELECT      DISTINCT m.id, title
FROM        movie m
INNER JOIN  genre g
ON          m.id = g.movie_id;

-- Improved
SELECT m.id, title
FROM   movie m
WHERE  EXISTS (SELECT 'X' FROM rating r WHERE m.id = r.movie_id

5. GROUP BY 연산 시 가급적 HAVING 보다 WHERE 절 사용

• 쿼리 실행 순서에서, WHERE 절이 HAVING 절 보다 먼저 실행된다. 따라서 WHERE 절로 먼저 데이터 크기를 작게 만들면, GROUP BY 에서 다뤄야 하는 데이터 크기가 작아지기 때문에 보다 효율적인 연산이 가능하다.

-- Inefficient
SELECT      m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM        movie m
INNER JOIN  rating r
ON          m.id = r.movie_id
GROUP BY    id
HAVING      m.id > 1000;

-- Improved
SELECT       m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM        movie m
INNER JOIN  rating r
ON          m.id = r.movie_id
WHERE       m.id > 1000
GROUP BY    id;

6. 3개 이상의 테이블을 INERJOIN 할 때는, 크기가 가장 큰 테이블을 FROM 절에 배치하고, INNER JOIN 절에 나머지를 작은 순서대로 배치

• INNER JOIN 과정에서 최소한의 Combination 을 탐색하도록 FROM & INNER JOIN 의 순서대로 배열하는 게 대부분 좋지만, 항상 통용되는 것은 아님
• 간단한 INNER JOIN 의 경우는 대부분의 Query Planner 에서 가장 효과적인 순서를 탐색해 INNER JOIN 의 순서를 바꾸기 때문이다.
  • 아래 두 쿼리는 JOIN 순서가 다르지만, 실행 시간에 큰 차이가 없다. (순서대로 실해된다면 Query B 가 훨씬 실행시간이 적다.)

-- Query (A)
SELECT m.title, r.value_rating, g.value genre
FROM   rating r
INNER JOIN genre g
ON.   g.movei_id = r.movie_id
INNER JOIN movie m
ON m.id = r.movie_id:

-- Query (B) 🌟
SELECT m.title, r.value rating, g.value genre
FROM  rating r           -- 10,000,000 records
INNER JOIN movie m       -- 100 records
ON r.movie_id = m.id 
INNER JOIN genre g       -- 100,000 records
ON r.movie_id = g.movie_id;

• 하지만 테이블의 개수가 늘어난다면, 탐색해야 할 INNER JOIN 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용 증가로 이어진다.
  • 이 경우 언젠가는 비싼 Planning 비용을 들이고 가장 최적의 순서를 찾는 것 보다, 차선의 INNER JOIN 순서로 쿼리를 실행하더라도 Planning 비용을 줄이는 것이 더 효과적일 것이다.
  • 따라서 복잡한 쿼리에서는 완전하게 최적화되지 않은 INNER JOIN 연산이 실행될 떄가 많다.
  • 이를 사전에 방지하기 위해 최적화된 INNER JOIN 순서를 입력 단계에서 조정해두는 것이 좋다.
  • INNER JOIN 의 최적화 여부가 연산량에 미치는 영향력은 상다하기 때문이다.

7. 자주 사용하는 형식은 미리 전처리된 테이를로 보관/관리

• RDBMS 원칙에 어긋나고, DB의 실시간성을 반영하지 못할 가능성이 높기 때문에, 대부분 운영계보다는 분석계에서 더 많이 사용된다.
• 사용자에 의해 발생한 Log 데이터 중에서 필요한 Event 만 모아서 따로 적재해두는 것, 혹은 핵심 서비스 지표를 주기적으로 계산해 따로 모아두는 것 등이 대표적인 예다.