ex )
-- 전 쿼리
SELECT * FROM movie;
-- 향상된 쿼리
SELECT id FROM movie;
많은 필드 값을 불러올수록 DB는 더 많은 로드를 부담한다.
칼럼 중에 불필요한 값을 가진 필드가 있다면 과감히 제외하고, 꼭 필요한 열만 불러오는 것이 좋다.
Index를 활용할 수 있는 value IN (...), value = "..."
, value LIKE "...%"
와는 다르게, value LIKE "%..."
는 Full Table Scan을 활용한다.
따라서 같은 결과를 낼 수 있다면, value LIKE "%..."
보다 다른 형태의 조건을 적용하는 것이 바람직하다.
ex )
-- 전
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;
쿼리 실행 순서에서, WHERE 절이 HAVING 절보다 먼저 실행된다.
따라서 WHERE 절로 미리 데이터 크기를 작게 만들면, GROUP 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
GROUP BY id
HAVING m.id > 1000;
인덱스를 사용하면 테이블을 full scan을 하지 않고, 색인화 되어있는 파일을 스캔하여 검색 속도를 향상시킨다.
인덱스는 검색 속도를 향상하거나, 조건절에 해당하지 않는 열을 미리 제외하기 위해 많이 사용한다.
-- 전 쿼리
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;
SELECT DISTINCT
, UNION 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);
중복 값을 제거하는 연산은 많은 시간이 걸린다.
사용해야 하는 상황이라면, DISTINCT 연산을 대체할 수 있는 EXISTS를 활용하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법 생각하자.
로그 테이블은 기간이 길어지면 DB에 부하가 걸리는 경우가 많으나 필요한 컬럼들로 전처리 된 경량테이블을 만들면 효율이 WMDRK
✨ 참고
쿼리 최적화
"같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING보다는 WHERE 절을 사용하기"
부분에 전쿼리와 향상된 쿼리가 동일한 것 같습니다!