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

강지영·2023년 2월 24일

SQL

목록 보기

18/20

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