쿼리 최적화 테크닉

1. SELECT 시에는 꼭 필요한 컬럼만 불러온다.

-- Inefficient
SELECT * FROM MOVIE;

-- IMPROVED
SELECT id FROM MOVIE;

• 많은 필드 값을 불러올수록 DB는 더 많은 로드를 부담하게 되고 데이터 전송에 필요한 리소스가 많아진다.

 

 

2. WHERE절 컬럼에 별도의 연산을 걸지 않는 것이 좋다.

-- Inefficient
SELECT	 m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count
FROM	 MOVIE m, RATING r
WHERE	 m.id = r.movie_id
AND      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, RATING r
WHERE	 m.id = r.movie_id
AND      r.value BETWEEN 4 AND 5
GROUP BY m.id;

• 컬럼에 함수를 적용하거나 연산을 수행하면 인덱스를 사용할 수 없기 때문에 Inefficient 쿼리의 경우 Full Table Scan을 하면서 모든 값을 탐색하게 된다. 반면, Improved 쿼리의 경우 기존에 r.value가 가지고 있는 인덱스를 그대로 활용할 수 있기 때문에 Inefficient 쿼리 대비 더 짧은 Running Time을 가질 수 있다.

 

 

3. 조건 절의 타입을 일치시킨다.

-- Inefficient 1
SELECT	 *
FROM	 RATING
WHERE	 value = '4'

-- Inefficient 2
SELECT	 *
FROM	 RATING
WHERE	 TO_CHAR(value) = '4'

-- IMPROVED
SELECT	 *
FROM	 RATING
WHERE	 value = 4

• NUMBER 타입의 value와 '4'는 직접 비교를 할 수 없기 때문에 오라클은 컬럼에 TO_CHAR() 함수를 사용하여 내부적으로 형변환을 시도한다. 문제는 TO_CHAR(value) 컬럼은 인덱스가 존재하지 않기 때문에 Full Scan을 수행하게 된다.
• 형변환이 필요하다면 우측 값쪽에서 변환하는 것이 바람직하다.

 

 

4. 부정문 사용을 자제한다.

-- Inefficient
SELECT	 *
FROM	 GENRE
WHERE	 genre <> '액션'

-- IMPROVED
SELECT	 *
FROM	 RATING
WHERE	 genre IN ('SF', '로맨스', '코메디')

• 부정문은 인덱스를 타지 않기 때문에 Full Scan을 수행하게 된다.

 

 

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

• value LIKE "%..."는 Full Table Scan을 활용한다.
• 예를 들면, 다양한 장르 중에서 Comedy와 Romantic Comedy를 추출하고 싶은 경우, LIKE "%Comedy"보다는, 다른 형태의 조건절을 사용하는 것이 효과적일 것이다.

 

 

6. SELECT DISTINCT, UNION DISTINCT와 같이 중복 값을 제거하는 연산 사용을 자제한다.

• 중복 값을 제거하는 연산은 많은 시간이 걸리기 때문에 불필요한 Distinct 사용을 자제하고 중복 값을 제거하는 기능을 가진 UNION과 DISTINCT를 함께 사용하지 않는다.

 

 

7. 같은 내용의 조건이라면, 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 절보다 먼저 실행되기 때문에 WHERE 절로 미리 데이터 크기를 작게 만들면 GROUP BY에서 다뤄야 하는 데이터 크기가 작아지기 때문에 보다 효율적인 연산이 가능하다.

 

 

8. Join 조건에 'OR' 사용을 자제한다.

-- Inefficient
SELECT *
FROM SH.costs c
		INNER JOIN SH.products p ON c.unit_price = p.prod_min_price OR c.unit_price = p.prod_list_price;

-- Improved
SELECT *
FROM SH.costs c
         INNER JOIN SH.products p ON c.unit_price = p.prod_min_price
UNION ALL
SELECT *
FROM SH.costs c
         INNER JOIN SH.products p ON c.unit_price = p.prod_list_price;

• 조인 조건에 'OR'을 사용하는 경우 Index를 활용하지 못하여 Full Scan을 수행하게 된다.