Query 속도를 올리는 체크리스트 7

예전에 당근마켓 형님들이 올렸던 글을 참고해서 회사에 적용하려고 따라썼던 글이다.
이거 사실상 레플리카인데 ㅋㅋ 그래도 나중에 내가 참고할 내용으로 블로그에 적어놓으려 한다.

1. SELECT 시에는 꼭 필요한 칼럼만 불러와야 함

-- Inefficient
SELECT * FROM movie; 

-- Improved
SELECT id FROM movie;

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;

• Full table scan -> 모든 cell 값 탐색 -> 수식걸고 조건 충족 판단

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

• value가 가진 index를 그대로 활용할 수 있다. 모든 필드 값을 탐색 할 필요가 없다.

3. LIKE사용 시 와일드카드 문자열(%)을 String 앞부분에 배치하지 말기 (2번과 비슷)

• value IN (...), value = "...", value LIKE "...%"는 index 활용 가능
• value LIKE "%..."는 Full table scan임.
  

→ Genre에서 Comedy와 Romantic Comedy를 추출하기 위해 LIKE "%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)

• 시간이 많이 걸리므로 불가피하게 사용해야되면 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절로 데이터크기를 미리 줄이면 GROUP BY에서 다룰 데이터 크기가 작아지기에 효율적임.

SQL 구문 순서

1. `FROM`
2. `WHERE`
3. `GROUP BY`
4. `HAVING`
5. `SELECT`
6. `ORDER 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개 이상의 table을 INNER JOIN할 때, 크기가 큰 테이블을 FROM에 배치하고 INNER JOIN절에는 나머지를 작은 순서대로 배치

7. 그 외

• ORDER BY는 중간 연산에 사용 하지 않기
• LIMIT을 사용하기
  

결론

작년에 위 내용이 큰 도움이 되었던 것으로 기억합니다. 감사합니다.