오라클 Fetch 속도 - olakeul Fetch sogdo

DB에 대한 데이터 분석가의 에티켓

⭐️ 🎥 📺 📽 📹🌟

Photo by Taylor Vick on Unsplash

별점 5.5억 개, 유저당 월평균 시청 시간 24시간 이상 등, 왓챠피디아 & 왓챠의 DB에는 방대한 데이터가 있으며, 서비스의 성장과 함께 날이 갈수록 더 많은 양의 데이터가 쌓이고 있습니다. 그러다 보니 때때로 DB에 쿼리를 날릴 때에는 쿼리의 최적화 여부에 따라 성능이 눈에 띄게 변하는 것을 자주 느낍니다. 그래서 이번 글에서는 SQL을 최적화할 수 있는 7가지 방법에 대해 정리하려고 합니다.

물론, 소개할 7가지 방법 외에도 다양한 최적화 방법이 있을 것입니다. 하지만 SQL 별로 사용하고 있는 Query Planner의 로직이 조금씩 다르고, 쿼리가 참조할 테이블의 크기와 구성 또한 다양할 것이기 때문에, 본 글에서는 가장 일반적으로 적용될 수 있는 방법을 소개할 예정입니다.

일부 방법에는 예시 데이터를 활용하여 Inefficient 쿼리와 Improved 쿼리 간에 성능 차이가 실제로 얼마나 나는지 비교하는 자료를 포함하였습니다. 예시 데이터로 활용될 테이블과 필드는 아래와 같으며, 이들은 왓챠의 데이터와는 무관한, 실험을 목적으로 임의로 만들어낸 데이터셋임을 미리 밝힙니다.

# 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): 장르명

들어가기 전에

Inefficient 쿼리들은 언제까지나 리소스가 넉넉하지 않은 상황에서, Improved 쿼리들을 사용했을 때 같은 결과를 얻는 것이 보장되는 경우에만 Inefficient인 것으로 한정됩니다. 쿼리 최적화가 굳이 필요 없는 상황이거나, 원하는 데이터의 형태에 따라서는 Inefficient 쿼리도 좋은 방법일 수 있습니다.
데이터는 로컬 환경에 저장했으며, MySQL Workbench & MySQL 8.0.19를 활용했습니다. 쿼리를 연달아 실행시킨 후 소요 시간(Duration)을 기준으로 성능을 비교하였습니다. Duration 옆에 있는 Fetch Time은 쿼리 연산에 드는 시간이 아닌, 결과를 불러오는 시간이기 때문에, 쿼리의 성능과는 무관합니다.
본 자료의 2번과 3번에 소개되는 방법은 Full Table Scan과 Index Scan에 대한 사전 지식이 있는 상태를 가정하고 작성되었습니다. Full Table Scan과 Index Scan에 대해서 좀 더 자세히 알고 싶으신 분들은 링크에 정리가 잘 되어 있으니 참고하시면 좋을 것 같습니다.

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

3. LIKE사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않는 것이 좋습니다.

2번과 같은 원리입니다. Index를 활용할 수 있는value IN (...), value = "...", value LIKE "...%"와는 다르게, value LIKE "%..."는 Full Table Scan을 활용합니다. 따라서 같은 결과를 낼 수 있다면, value LIKE "%..."보다는 다른 형태의 조건을 적용하는 것이 바람직합니다.
예를 들면, 다양한 장르 중에서 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 절이 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개 이상의 테이블을 INNER JOIN 할 때는, 크기가 가장 큰 테이블을 FROM 절에 배치하고, INNER JOIN 절에는 남은 테이블을 작은 순서대로 배치하는 것이 좋습니다

INNER JOIN 과정에서 최소한의 Combination을 탐색하도록 FROM & INNER JOIN의 순서를 배열하면 좋다는 이야기인데, 항상 통용되지는 않습니다.
간단한 INNER JOIN의 경우는 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 INNER JOIN의 순서를 바꾸기 때문입니다. 예를 들어서, 아래의 두 쿼리는 서로 다른 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 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 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용의 증가로 이어집니다. 그러다 보면 언젠가는 비싼 Planning 비용을 들이고 가장 최적의 순서를 찾는 것보다, 차선의 INNER JOIN 순서로 쿼리를 실행하더라도 Planning 비용을 줄이는 것이 결과적으로는 더 효과적인 상황이 올 것입니다.
그렇기 때문에 복잡한 쿼리에서는 완전하게 최적화되지 않은 INNER JOIN 연산이 실행될 때가 있습니다. (개인적으로는 BigQuery를 사용할 때, 임시 테이블을 많이 만들다 보면 이 같은 상황이 발생하는 것을 종종 경험하곤 합니다) 따라서 이를 사전에 방지하기 위해 최적화된 INNER JOIN 순서를 입력 단계에서 조정해 두는 것은 분명 도움이 될 것입니다. INNER JOIN의 최적화 여부가 연산량에 미치는 영향력은 상당히 크기 때문입니다.
안타깝게도, 예시에서 활용되는 3개의 테이블을 가지고는 Query Planner의 성능을 넘을 수 있을 정도로 복잡한 예시 쿼리를 만들어 볼 수는 없습니다.

7. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리된 테이블을 따로 보관/관리하는 것도 좋습니다.

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

정리

지금까지 가장 보편적인 7가지의 쿼리 최적화 방법에 대해서 알아보고, Toy Data를 대상으로 쿼리를 비교하면서 실제로 높은 성능으로 이어지는지에 대해 실험해 보았습니다.
소개한 방법 외에도 ORDER BY는 연산 중간에 사용하지 말기, LIMIT을 활용하기와 같은, 팁보다는 습관에 가까운 것들부터, 어떤 SQL을 사용하느냐에 따라 추가적으로 적용할 가능한 것들까지 다양한 방법들이 존재합니다. 그렇지만 결국 사용 중인 SQL에 부합하는 몇 가지 방법만 알게 되면 대부분 최적화가 가능하므로, 숙지하는 것이 결코 어렵지 않다고 할 수 있겠습니다.