쿼리 최적화에 대하여...

저는 최근 프로젝트에서 데이터가 많아질 수록 쿼리 최적화로 성능을 높이는 것이 중요한 일 중 하나라고 느껴서 쿼리 최적화에 대해 정리하고자 합니다.

 

쿼리 최적화 방법

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;

 

 

r.value에 인덱싱이 되어 있을 때, Inefficient 쿼리에서 r.value 값을 변환하고 있어서 r.value 인덱스를 활용할 수 없어서 Full Table Scan을 합니다.

또한, 함수를 적용하고 결과를 비교하는 비용이 생깁니다.

반면에 Improved 쿼리는 r.value 범위를 지정했기 때문에 r.value 인덱스를 활용하여 효율적으로 필요한 레코드를 찾을 수 있습니다.

 

3. LIKE 사용 시 와일드카드 문자열(%)을 String 앞부분에 배치하지 않기

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

 

와일드 카드가 앞에 있다면 Full Table Scan을 하게 됩니다.

 

4. DISTINCT와 같은 중복 값을 제거하는 연산은 최대한 사용하지 않기

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

 

중복 값을 제거하는 연산은 많은 시간이 걸립니다.

만약 불가피하게 사용해야 하는 상황이라면, DINTINCT 연산을 EXISTS를 활용하는 방법으로 대체하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법을 사용하는 것이 좋습니다.

 

5. 같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING보다는 WHERE 절을 사용하기

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

 

쿼리 실행 순서에서 WHERE 절이 HAVING 절보다 먼저 실행되기 때문에, GROUP BY에서 다뤄야하는 데이터 크기를 미리 줄인다면 그만큼 빨라집니다.

같은 이야기로 ORDER BY도 다뤄야하는 데이터 크기를 미리 줄인 다음 사용하는 것이 좋습니다.

 

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

-- Query (A)
SELECT m.title, r.value rating, g.value genre 
FROM rating r 
INNER JOIN genre g 
ON g.movie_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 
INNER JOIN movie m
ON r.movie_id = m.id 
INNER JOIN genre g 
ON r.movie_id = g.movie_id;

 

 

INNER JOIN 과정에서 최소한의 Combination을 탐색하도록 FROM & INNER JOIN의 순서를 배열하면 좋다는 이야기인데, 항상 통용되지는 않습니다.

간단한 INNER JOIN의 경우는 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 INNER JOIN의 순서를 바꾸기 때문에 결과적으로 Query A와 Query B 실행시간에 큰 차이는 없습니다.

 

7. 모든 값을 반환할 필요가 없을 때 LIMIT 사용하기

-- Inefficient
SELECT title
FROM `my-project.mydataset.mytable`
ORDER BY title;

-- Improved
SELECT title
FROM `my-project.mydataset.mytable`
ORDER BY title DESC
LIMIT 1000;

 

일부만 반환해도 될 때, LIMIT를 사용하면 반환 비용을 줄일 수 있습니다.
예를 들어 20만개의 데이터가 있는데, 이름 순 1000개를 보기위해 20만개를 다 반환 받아서 보는 것보다 1000개만 반환 받는 것이 반환 비용이 훨씬 적습니다.

 

8. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리된 테이블을 따로 보관/관리하기

RDBMS의 원칙에 어긋나는 측면이 있고, DB의 실시간성을 반영하지 못할 가능성이 높기 때문에, 대부분 운영계보다는 분석계에서 더 많이 사용되곤 합니다.