공부목적으로 다른 블로그의 글을 그대로 따라치면서 작성되었습니다. 저작권 문제 시, 비공개 처리하겠습니다
1. Select 시 에는 꼭 필요한 컬럼만 불러와야 한다
SELECT * FROM member;
SELECT id FROM member;- 많은 필드 값을 불러올수록 DB는 더 많은 로드를 부담하게 되기 때문
- 컬럼 중에 불필요한 값을 가진 필드가 있다면 과감히 제외하고, 꼭 필요한 열만 불러오는 것이 좋다
2. 조건 부여 시, 가급적이면 기존 DB값에 별도의 연산을 걸지 않는 것이 좋다
-- 나쁜
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;
-- 개선
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;floor(4/2) = 2 (O) - 내림 연산
floor(5/2) = 2 (O) - 내림 연산
between 4 and 5 = 4~5 로 명시 (상수로 주어 별도의 연산이 없게 하는 것)
나쁜 쿼리의 경우, Full Table Scan(순차접근=순차탐색)을 하면서 모든 Cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단 = O(n)
개선 쿼리의 경우 기존에 r.value가 가지고 있는 index를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 나쁜쿼리 대비 더 짧은 Running Time을 가질 수 있다 = O(log n)
3. LIKE사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않는 것이 좋다.
2번과 같은 원리입니다. Index를 활용할 수 있는 value IN (...), value = "...", value LIKE "...%" 와는 다르게,
value LIKE "%..."는 Full Table Scan (순차 탐색)을 활용합니다.
따라서 같은 결과를 낼 수 있다면, value LIKE "%..."보다는 다른 형태의 조건을 적용하는 것이 바람직합니다.
예를 들면, 다양한 장르 중에서 Comedy와 Romantic Comedy를 추출하고 싶은 경우, LIKE "%Comedy"보다는, 다른 형태의 조건절을 사용하는 것이 효과적일 것입니다.
-- 나쁜
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;
-- 개선(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;
-- 개선(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;
-- 개선(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;
개선(3) 의 경우 Romantic Comedy 경우 %Comedy 라고 하는 경우 해당 컬럼이 Comedy 로 끝나는지 문자열을
거의 모두 돌게 될 수 있습니다.
때문에 Romantic% 일 때를 문자열을 모두 순환하지않으면, 바로 OR 로 넘어가 Comed% 인지 확인하는 식으로 성능을 개선시킬 수 있다
4. SELECT DISTINCT, UNION DISTINCT와 같이 중복 값을 제거하는 연산은 최대한 사용하지 않아야 한다
중복값을 제거하는 연산은 많은 시간이 걸린다. 만약 불가피하게 사용해야 하는 상황이라면 distinct 연산을 대체하거나,
연산의 대상이 되는 테이블의 크기를 최소화 하는 방법을 고민할 필요가 있다.
가장 대표적인 대체방법으로는 exists 를 활용하는 방법이 있다
-- 나쁜
SELECT DISTINCT m.id, title
FROM movie m
INNER JOIN genre g
ON m.id = g.movie_id;
-- 개선
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 절로 미리 데이터 크기를 작게 만들면, grouby by 에서 다뤄야 하는 데이터 크기가 작아지기 때문에
보다 효율적인 연산이 가능하다
-- 나쁜
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;
-- 개선
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;
그러나 테이블의 개수가 늘어난다면(JOIN 개수), 탐색해야 할 inner join 순서의 경우의 수가 늘어나고, 이는 결국
planning 비용의 증가로 이어집니다 그러다 보면 언제가는 비싼 planing 비용을 들이고 가장 최적의 순서를
찾는 것보다 차선의 inner join 순서로 쿼리를 실행하더라도 planning 비용을 줄이는 것이 결과적으로는
더 효과적인 상황이 온다
그렇기 때문에 복잡한 쿼리에서는 완전하게 최적화되지 않은 inner join 연산이 실행 될 때 가 있다
(BigQuery 를 사용할 때 , 임시 테이블을 많이 만들다 보면 이 같은 상황이 발생하는 것을 종종 경험한다고 함)
따라서 이를 사전에 방지하기 위해 최적화된 inner join 순서를 입력 단계에서 조정해 두는 것이 분명 도움이 된다
inner join 의 최적화 여부가 연산량에 미치는 영향력이 상당히 크기 때문
요약 : 조인 할 테이블 개수가 적으면 대충써도 planning 이라는 게 최적의 순서로 배치해주는 것 같다 하지만 이것도 정렬 판단을 하는 데 시간이 있기 때문에 무수히 많은 테이블을 가장 최적의 순서로 정렬한다면 그 만큼에 시간이 소모되어 순서를 직접 주는 것보다 느려질 수 밖에 없음 - 직접 줄 때는 이 테이블의 개수가 적다는 것을 눈으로 확인했기 때문에 순서를 가장 최적의 방버으로 적용하기 때문에 거의 O(1) 성능임
7. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리된 테이블을 따로 보관/관리하는 것도 좋습니다.
RDMBS 의 원칙에 어긋나는 측면이 있고, DB의 실시간성을 반영하지 못 할 가능성이 높기 때문에,
대부분 운영계보다는 분석계에서 더 많이 사용되곤 한다
예를들어 사용자에 의해 발생한 Log 데이터 중에서 필요한 Event 만 모아서 따로 적재해두는 것, 혹은 핵심 서비스 지표를 주기적으로 계산해서 따로 모아두는 것 등이 가장 대표적으로 볼 수 있는 사례이다
참고
✅ 쿼리 최적화 첫걸음 — 보다 빠른 쿼리를 위한 7가지 체크 리스트
DB에 대한 데이터 분석가의 에티켓
medium.com
'∞. 기술 면접 > 4. 데이터베이스' 카테고리의 다른 글
| 11. 기술 면접 - 데이터베이스 - 리플리케이션 (Replication) (0) | 2021.10.18 |
|---|---|
| 10. 기술면접 - 데이터 베이스 - 옵티마이저 (Optimizer) (0) | 2021.10.18 |
| 07. 기술면접 - 데이터베이스 - Statement와 PrepareStatement (0) | 2021.10.17 |
| 06. 기술면접 - 데이터베이스 - 인덱스 (Index) (0) | 2021.10.17 |
| Spring Mybatis SQL Injection (0) | 2021.10.16 |