[DB] 쿼리 최적화 및 튜닝 Query Optimization

1.What? 데이터베이스 튜닝이란 

 데이터베이스를 그냥 데이터를 인출하고 저장하는 것 만으로 활용하는 것이 아니라, 실제 서비스 운영에 있어 성능을 매우 중요하게 생각하고 바꾸는 것.

 

2. WHY? 왜 데이터 베이스 튜닝하는가

 데이터베이스는 주어진 하드웨어 환경 안에서 처리량과 응답속도를 개선하기 위해 수행한다. 개발 중에 잦은 데이터변경과 환경의 변화, 데이터량의 변화 등으로 성능이 보장되지 않는다. 정기적으로 데이터베이스 튜닝을 통해 이를 개선해나가야 한다.

 

3. HOW? 어떻게 할 것 인가

 시스템 리포팅을 통해 평균 응답시간이 가장 오래걸리는 쿼리를 산출해볼 수 있다. 튜닝에 좋은 방법은 여러가지 쿼리를 써보고 속도를 비교해보는 것이다.

 

1) SELECT 문에서 * 대신 컬럼명을 사용해라

Use Column Names Instead of * in a SELECT Statement

 일부컬럼만 선택함으로써 결과 테이블 크기를 줄이고 네트워크 트래픽을 감소시킨다. 결과적으로 쿼리의 퍼포먼스를 높일 수 있다.

 

  - 비교 : 27% 시간 단축

--Original query 
SELECT * from SH.Sales;
--Improved query
SELECT s.prod_id FROM SH.sales s;

 

2)  SELECT 문에서 HAVING절을 사용하지 마라.

Avoid including a HAVING clause in SELECT statements

 HAVING 절은 모든 행을 조회하고 나서 행을 필터링 한다. SELECT문에서는 쓸모가 없다. 최종 테이블에서 행들을 파싱하며 조건을 파악한다.

 

  - 비교 : 31% 시간 단축

-- original query 
SELECT s.cust_id, count(s.cust_id)
FROM SH.sales s
GROUP BY s.cust_id
HAVING s.cust_id != '1660' AND s.cust_id != '2';
-- Improved query
SELECT s.cust_id, count(s.cust_id)
FROM SH.sales s
WHERE s.cust_id != '1660'
AND s.cust_id != '2'
GROUP BY s.cust_id;

 

3. 불필요한 DISTINCT를 사용하지 마라

Eliminate Unnecessary DISTINCT Conditions

 primary key가 있는 테이블에서는 DISTINCT가 불필요하다.

 

  - 비교 : 85% 시간 단축

--original query
SELECT DISTINCT * FROM SH.sales s
JOIN SH.customers c
ON s.cust_id = c.cust_id
WHERE c.cust_marital_status = 'single';
--Improved query
SELECT * FROM SH.sales s JOIN
SH.customers c
ON s.cust_id = c.cust_id
WHERE c.cust_marital_stauts = 'single';

 

4. 서브쿼리를 JOIN조건으로 재작성해라

Un-nest sub queries

중첩된(nested) 서브쿼리문을 join으로 바꾸면 효율적인 최적화를 할 수 있다.

 

  - 비교 : 61% 시간 단축

-- Original query
SELECT * FROM SH.products p WHERE p.prod_id = 
(SELECT s.prod_id FROM SH.sales s WHERE s.cust_id = 100996 AND s.quantity_sold = 1);
-- Improved query
SELECT p.* 
FROM SH.products p, sales s 
WHERE p.prod_id = s.prod_id AND s.cust_id = 100996 AND s.quantity_sold = 1;

5. INDEX된 컬럼 검색을 할 땐 IN 연산자를 활용해라.

Consider using an IN predicate when querying an indexed column

 IN-list는 index된 검색을 위해 활용될 수 있으며 효율적인 검색을 수행할 수 있다.

 

  - 비교 : 73% 시간 단축

-- Original query
SELECT s.* FROM SH.sales s WHERE s.prod_id = 14 OR s.prod_id = 17;
-- Improved query
SELECT s.* FROM SH.sales s WHERE s.prod_id IN (14,17);

 

6. 테이블 조인할 때는 DISTINCT보단 EXISTS를 활용해라 

Use EXISTS instead of DISTINCT when using table joins that involves tables having one-to-many relationships

 DISTINCT 키워드는 테이블의 모든 열을 선택한 후에 중복되는 것을 파싱한다. 서브쿼리와 EXISTS키워드를 사용하면

전체 테이블 조회를 피할 수 있다.

 

 - 비교 : 61% 시간 단축

-- Original query
SELECT DISTINCT c.country_id, c.country_name FROM SH.countries c, SH.customers e WHERE e.country_id = c.country_id;
-- Improved query
SELECT c.country_id, c.country_name FROM SH.countries c WHERE EXISTS
(SELECT 'X' FROM SH.customers e WHERE e.country_id = c.country_id);

 

7. UNION 보다는 UNION ALL이 낫다 

Try to use UNION ALL in place of UNION

 UNION문은 중복된 열의 존재 유무에 상관없이 열을 선택할 때 중복검사를 하지만, UNION ALL은 중복검사를 하지 않으므로 더 빠르다

 

- 비교 : 81% 시간 단축

-- Original query
SELECT cust_id FROM SH.sales UNION SELECT cust_id FROM customers;
-- Improved query
SELECT cust_id FROM SH.sales UNION ALL SELECT cust_id FROM customers;

 

8. 조인조건에 OR을 되도록 사용하지마라

Avoid using OR in join conditions

 조인 조건에 'OR'을 사용할 때마다 쿼리는 최소 2배씩 느려진다. OR문을 사용하는 경우에 index를 이용해 검색 하지 않고 full-scan을 한다.

 

 - 비교 : 70% 시간 단축

-- Original query
SELECT *
FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_min_price OR c.unit_price = p.prod_list_price;
-- Improved query
SELECT *
FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_min_price 
UNION ALL 
SELECT * FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_list_price;

 

9. 집계함수 기능을 제거하면 성능이 향상된다.

Avoid functions on the right hand side of the operator

 함수는 SQL 쿼리에서 자주 사용된다. 집계함수 기능을 제거하면 성능을 상당히 높여준다.

 

- 비교 : 70% 시간 단축

-- Original query
SELECT * 
FROM SH.sales 
WHERE EXTRACT (YEAR FROM TO_DATE (time_id, 'DD-MON-RR')) = 2001 AND EXTRACT (MONTH FROM TO_DATE (time_id, 'DD-MON-RR')) = 12;
-- Improved query
SELECT * 
FROM SH.sales 
WHERE TRUNC (time_id) BETWEEN TRUNC(TO_DATE('12/01/2001', 'mm/dd/yyyy')) AND TRUNC (TO_DATE ('12/30/2001', 'mm/dd/yyyy'));

 

10. SQL문에 불필요한 수학연산을 제거해라

Remove any redundant mathematics

 수학연산을 쓰면 쿼리는 매번 행을 찾아서 다시 계산한다. 속도를 개선하고싶다면 불필요한 수학연산을 줄이는게 좋다.

 

 - 비교 : 11% 시간 단축

-- Original query
SELECT * FROM SH.sales s WHERE s.cust_id + 10000 < 35000;
-- Improved query
SELECT * FROM SH.sales s WHERE s.cust_id < 25000;

 

 

참고

논문 : Query Optimization Techniques - Tips For Writing Efficient And Faster SQLQueries

https://issuu.com/ijstr.org/docs/query-optimization-techniques-tips- 

https://travislife.tistory.com/25?category=883280