[SQL] 쿼리 속도 향상을 위한 방법

강지영·2023년 2월 24일
3

SQL

목록 보기
18/20
post-custom-banner

💡 SELECT를 할 때는 필요한 컬럼만 뽑기

ex )

-- 전 쿼리
SELECT * FROM movie; 
-- 향상된 쿼리
SELECT id FROM movie;

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

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

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;

💡 같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING보다는 WHERE 절을 사용하기

쿼리 실행 순서에서, 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을 하지 않고, 색인화 되어있는 파일을 스캔하여 검색 속도를 향상시킨다.
인덱스는 검색 속도를 향상하거나, 조건절에 해당하지 않는 열을 미리 제외하기 위해 많이 사용한다.

💡 조건 부여 시, 가급적이면 기존 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;
  • 전 쿼리
    Full Table Scan을 하면서 모든 Cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단한다
  • 향상된 쿼리
    기존에 r.value가 가지고 있는 index를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 전 쿼리 대비 더 짧은 Running Time을 가질 수 있다.

💡 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를 활용하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법 생각하자.

💡 3개 이상의 테이블을 JOIN할 경우에 크기가 가장 큰 테이블을 FROM절에 배치하고 JOIN절에는 남은 테이블을 작은 순서대로 배치

  • BUT 항상 통용되지는 않음

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

로그 테이블은 기간이 길어지면 DB에 부하가 걸리는 경우가 많으나 필요한 컬럼들로 전처리 된 경량테이블을 만들면 효율이 WMDRK

✨ 참고
쿼리 최적화

profile
Hello World!
post-custom-banner

1개의 댓글

comment-user-thumbnail
2024년 1월 3일

"같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING보다는 WHERE 절을 사용하기"
부분에 전쿼리와 향상된 쿼리가 동일한 것 같습니다!

답글 달기