본문 바로가기
SQL

SQL 기초 튜닝(1)

by Sunyoung95 2024. 5. 16.
현장에서 데이터 검증 시 sql을 통해 진행했는데 데이터가 많을 경우 응답시간이 길어져 검증에 어려움을 겪었다.
DBA분의 도움을 받아 쿼리를 튜닝할 수 있었고, 응답시간이 30% 이상 줄어든 것을 보고 튜닝의 중요성을 알게되었다.
이번 글을 작성하며 최소한의 sql 작성 시 피해야 할 구조를 파악하는것을 목표로 한다.
이번 글에서는 MYSQL을 기준으로 최적화 방법을 기재해 두었다.

 

1. 인덱스 사용 

  • 인덱스를 생성하여 특정 열의 데이터를 쉽게 찾을 수 있다.
  • 따라서 조건절에 자주 사용되는 컬럼을 인덱스로 생성하여 쿼리의 실행속도를 높일 수 있다. 
  • 조건절에 사용 시 (where / group by / order by) 조건절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
  • 만약 인덱스 칼럼 중 (idx1,idx2,idx3) 중간 컬럼을 건너뛰고 명시하면(idx1,idx3) 인덱스를 사용할 수 없다.
  • where + (group by || order by)
    • 동시에 같은 인덱스를 사용할 경우 : 아래의 두 예시보다 훨씬 빠르다.
    • where절만 인덱스 사용 : where절의 인덱스를 통해 검색된 결과를 별도의 정렬처리과정을 거쳐 정렬을 수행. 검색결과의 수가 많지 않을 때 효과적
    • order by(group by)절만 인덱스 사용 : order by(group by)절의 순서대로 인덱스를 읽으며 레코드 한 건씩 where 절의 조건을 확인 후 불일치 시 버린다. 대량의 레코드를 조회해서 정렬해야할 때는 이런형태의 튜닝이 사용되기도 한다. 
  • group by + order by : 둘 중 하나의 인덱스를 이용할 수 없을 때에는 둘 다 인덱스를 사용하지 못한다. 

예시)

SELECT * 
FROM orders 
WHERE order_date > '2021-01-01' 
ORDER BY order_id

위의 쿼리의 경우에는 order_date 와 order_id에 대한 인덱스를 생성하여 쿼리의 실행속도를 향상 시킬 수 있다.

CREATE INDEX idx_order_date ON orders (order_date);
CREATE INDEX idx_order_id ON orders (order_id);

2. 적절한 JOIN 사용

  • 3개 이상의 테이블을 Inner Join 할 때는 가장 큰 테이블을 FROM절에 배치하고, Inner Join절에는 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
  • Inner Join과정에서 최소한의 Combination을 탐색하도록 테이블의 순서를 배열하기 위함
    • 하지만 항상 통용되지는 않는다. 간단한 Inner Join의 경우는 대부분의 Query Planner에서 가장 효과적인 순서를 탐색하여 Inner Join의 순서를 바꾸기 때문.

3. 조건절에 연산 최소화

  • 조건 부여 시, 가급적 연산을 걸지 않는 것이 좋다.
  • 이유
    • 조건절에 수식사용 : 모든 row를 탐색하며 수식을 계산한 후 조건 충족여부를 판단
    • 조건절에 수식X : 해당 인덱스(r.value)를 그대로 활용할 수 있기 때문에 running time이 더 짧아진다.
-- bad example
SELECT r.id, r.value 
FROM RATING r 
WHERE FLOOR(r.value/2) = 2;

-- better example
SELECT r.id, r.value 
FROM RATING r 
WHERE r.value BETWEEN 4 AND 5;
  • 같은 이유로 LIKE 사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않는 것이 좋다.
-- bad example
SELECT g.value, 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;

-- better example1
SELECT g.value, 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;

-- better example2
SELECT g.value, 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;

-- better example3 (best)
SELECT g.value, 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 or UNION DISTINCT 같은 중복값을 제거하는 연산은 많은 시간이 걸린다
  • 만약 사용해야하는 상황이라면 distinct연산을 대체하거나 연산의 대상이 되는 테이블의 크기를 최소화한다.
    • 대표적인 대체방법 : EXIST
-- bad example
SELECT DISTINCT m.id, title
FROM movie m
INNER JOIN genre g
ON m.id = g.movie_id;

-- better example
SELECT m.id, title
FROM movie m
WHERE EXIST (SELECT 'X' FROM rating r WHERE m.id = r.movie_id);

5. Where >> Having

  • group by  연산 사용 시 having보다는 where절을 사용하는 것이 좋다.
  • 쿼리 실행 순서 상, where 절이 having절보다 먼저 실행되므로 where절로 데이터를 축소 하면 group by에서 다뤄야 할 데이터 크기가 작아지기 때문에 효율성이 오른다.
-- bad example
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;

-- better example
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. 쿼리 실행 계획 확인

  • 쿼리 실행 계획은 데이터베이스가 쿼리를 실행할 때 어떤 방식으로 데이터를 검색하는지 알려준다.
  • 쿼리 실행 계획을 분석함으로써 성능저하의 원인을 파악하고 인덱스나 Join 조건 등을 수정하여 성능을 개선할 수 있다.
EXPLAIN SELECT *
FROM orders
WHERE order_date > '2021-01-01'
ORDER BY order_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders| NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    11.11 | Using where; Using filesort|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

7. 데이터 마트 활용

  • 자주 사용하는 데이터 형식은 미리 테이블로 생성하여 보관/관리해두면 빠르게 가져다 사용할 수 있다.
  • 예시
    • 사용자의 log 데이터 중 필요한 Event만 모아서 따로 적재
    • 핵심 서비스 지표를 주기적으로 계산해서 따로 적재

참고

 

 

댓글

티스토리툴바