[쿼리 최적화] 쿼리를 최적화 할 수 있는 7가지 방법

몇 번의 면접을 진행하면서 쿼리 최적화에 대한 질문이 들어와 그 부분을 공부한 내용입니다. 본 포스팅은 왓챠의 쿼리 최적화 포스트를 참고해 작성한 내용입니다. 

 

개요

데이터가 100만개인 테이블에서 10개의 데이터 조회 시 속도가 느릴 경우 쿼리 최적화를 통해 속도 개선을 할 수 있습니다.

 

예시 데이터 셋

# movie (약 100개의 레코드)
-- id(int): primary key
-- title(varchar): 영화 제목

# rating (약 10,000,000개의 레코드)
-- id(int): primary key
-- movie_id(int): 별점의 대상이 되는 movie id
-- value(int): 별점의 값

# genre (약 100,000개의 레코드)
-- id(int): primary key
-- movie_id(int): 별점의 대상이 되는 movie id
-- genre(varchar): 장르명

 

1. Select 시에는 꼭 필요한 컬럼만 불러오기

-- Inefficient (not 최적화 쿼리)
SELECT * FROM movie; 

-- Improved (최적화한 쿼리)
SELECT id FROM movie;

많은 필드 값을 불러올 수록 DB에 더 많은 로드로 인한 부담이 발생됩니다. 컬럼 중에 불필요한 값을 가진 필드가 있다면 과감히 제외하고, 꼭 필요한 열만 불러오는 것이 좋습니다.

 

2. Where 절 사용 시, 가급적이면 기존 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를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 Inefficient 쿼리 대비 더 짧은 Running Time을 가질 수 있습니다.

 

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

value In (…), value = “…”, value LIKE “…%”는 Index를 활용하는 반면, value Like “%…”는 Full Table Scan을 활용합니다. 따라서 같은 결과를 낼 수 있다면, value Like “%…” 보다는 다른 형태의 조건을 적용하는 것이 바람직합니다.

-- 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 DISINCT와 같이 중복 값을 제거하는 연산 사용 최대한 지양하기

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

• 중복 값을 제거하는 연산은 많은 시간이 걸리기 때문에 되도록이면 사용을 지양해야 합니다.
• 만약 불가피하게 사용해야 하는 상황이라면 DISTINCT 연산을 대체하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법을 고민할 수 있습니다.
  • 가장 대표적인 대체 방법으로는 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 절보다 먼저 실행됩니다. Where 절로 미리 데이터 크기를 작게 만들면, Group by에서 다뤄야 하는 데이터 크기가 작아지기 때문에 보다 효율적인 연산이 가능합니다.

 

6. 3개 이상의 테이블을 Inner Join 할 때는, 크기가 가장 큰 테이블을 From에, 나머지 테이블을 Inner Join 절에 배치하기

• Inner Join 과정에서 최소한의 Combination을 탐색하도록 From & Inner Join의 순서를 배열하면 좋다는 이야기지만 항상 통용되는 건 아닙니다.
• 간단한 Inner Join의 경우 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 Inner Join의 순서를 바꾸기 때문입니다.
• 예를들어 아래의 Query (A)와 (B)에서 inner join 순서가 다름에도 결과적으로 실행 시간에는 차이가 없습니다. (순서대로 Inner Join 된다면, Query(B)가 (A)보다 훨씬 더 적은 시간이 소요될 것 입니다)

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

예시의 테이블만으로는 Query Planner의 성능을 넘을 수 있을 정도의 복잡한 쿼리를 만들 수 없습니다.

• 하지만 테이블 개수가 늘어난다면 탐색해야 할 Inner Join 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용의 증가로 이어집니다.
• 그러면 결국 Inner Join 순서로 Planning 비용 줄이기 > 비싼 Planning 비용으로 가장 최적의 순서 찾기 가 되는 상황이 발생할 수 있습니다.
• 복잡한 쿼리에서는 완전하게 최적화되지 않은 Inner Join 연산이 실행될 때가 있기에 이를 사전 방지해야 합니다. 그때 최적화된 Inner Join 순서를 입력 단계에서 조정해 두는 것은 분명 도움이 될 것 입니다. (Inner Join의 최적화 여부가 연산량에 미치는 영향력은 상당히 크기 때문입니다)

 

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

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

ETC

• 위에 설명한 내용 외에도 Order by는 연산 중간에 사용하지 말기, Limit 활용하기와 같은 습관을 들이는 것도 중요합니다.

 

Reference

왓챠 블로그