[DB]Query 최적화 및 튜닝 기술

Query Optimize Skill(Query 최적화 및 튜닝 기술)

Tip 1. Use Column Names Instead of * in SELECT Statement

단순히, 몇 개의 column만 조회하는 경우라면 * (Asterisk)를 사용할 필요가 없습니다. 비록 적기 편할 순 있겠지만, 쿼리를 완료하기 위해서 더 많은 시간을 필요로 합니다.

일부 칼럼만 선택함으로써 결과 테이블의 크기를 줄이고, 네트워크 트래픽을 감소 시킴으로써 쿼리의 평균 속도를 높일 수 있습니다.

• Original: SELECT * FROM SH.Sales;
• Improved: SELECT s.PROD_ID FROM SH.Sales s;
• 27% Time Reduction

 

Tip 2. Avoid include HAVING clause in SELECT Statement

HAVING 절은 모든 열이 선택된 이후에 필터를 위해 사용됩니다. SELECT 문에서는 HAVING 절이 불필요 합니다. 이것은 최종 테이블에서 모든 열들을 파싱하면서 HAVING 조건에 충족되지 않는 열들을 필터링합니다.

• Original: 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: 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;
• 31% Reduction

 

Tip 3. Eliminate Unnecessary DISTINCT Conditions

아래의 예제와 같이 테이블은 Primary Key를 가지고 있기 때문에 DISTINCT를 사용할 필요가 없으므로 제거합니다. DISTINCT를 사용하면 정렬하는 과정이 들어가기 때문에, Query 속도가 상당히 저하됩니다.

• Original: SELECT DISTINCT * FROM SH.Sales s JOIN SH.Customer c ON s.cust_id = c.cust_id WHERE c.cust_marital_status = ‘single’;
• Improved: SELECT * FROM SH.Sales s JOIN SH.Customer c ON s.cust_id = c.cust_id WHERE c.cust_marital_status = ‘single’;
• 85% Reduction

 

Tip 4. Un-nest subqueries

중첩된 쿼리를 조인 조건으로 재작성하는 것은 효율적인 실행과 효과적인 최적화를 불러일으킵니다. 일반적으로 서브 쿼리를 푸는 것은 ANY, ALL, EXISTS 등이 사용되는 한 개의 테이블에서 처리됩니다.

• Original: 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: 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;
• 73% Time Reduction

 

Tip 5. Consider using an IN predicate when querying an indexed column

IN-list는 index된 검색을 위해 활용될 수 있습니다. Optimizer는 Index의 정렬 순서와 일치하도록 IN-list를 정렬하여 보다 효율적인 검색을 수행할 수 있습니다. IN-list에는 상수 또는 쿼리 블록이 실행되는 동안 일정한 값만 포함해야 합니다.

• Original: SELECT s.* FROM SH.sales s WHERE s.prod_id = 14 OR s.prod_id = 17;
• Improved: SELECT s.* FROM SH.sales s WHERE s.prod_id IN (14, 17);
• 73% Time Reduction

 

Tip 6. Use EXISTS intead of DITINCT when using table joins that involves tables having one -to many relationships

DISTINCT 키워드는 테이블의 모든 열을 선택한 후에 중복되는 것을 파싱하는 형태로 작동합니다. 만약 서브쿼리와 함께 EXISTS 키워드를 사용한다면, 전체 테이블을 조회하는 것을 피할 수 있습니다.

• Original: SELECT DISTINCT c.country_id, c.country_name FROM SH.countries c, SH.customers e WHERE e.country_id = c.country_id;
• Improved: 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);
• 61% Time Reduction

 

Tip 7. Try to use UNION ALL in place of UNION

UNION 문은 중복된 열의 존재 유무에 상관없이 열을 선택할 때 중복 검사를 하지만 UNION ALL은 중복 검사를 하지 않으므로 UNION보다는 UNION ALL을 사용하는 것이 빠릅니다.

• Original: SELECT cust_id FROM SH.sales UNION SELECT cust_id FROM customers;
• Improved: SELECT cust_id FROM SH.sales UNION ALL SELECT cust_id FROM customers;
• 81% Time Reduction

 

Tip 8. Avoid using OR in join conditions

조인 조건에 OR을 사용할 때 마다 쿼리는 최소한 2배 이상 느려집니다. OR문을 사용하는 경우에는 Index를 활용한 검색을 하지 못하고, Full-Scan을 하기 때문입니다.

• Original: SELECT * FROM SH.cost c INNER JOIN SH.products p ON c.unit_price = p.prod_min_price OR c.unit_price = p.prod_list_price;
• Improved: 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;
• 70% Time Reduction

 

Tip 9. Avoid functions on the right hand side of the operator

Functions 또는 Methods를 SQL 쿼리에서 자주 함께 사용됩니다. 집계 함수 기능을 제거하여 쿼리를 재작성 하는 것은 성능을 상당히 높여줄 것 입니다.

• Original: SELECT * FROM SH.sales WHERE EXTRACT (YEAR FROM TO_DATE (time_id, ‘DD-MON-RR’)) 
• Improved: 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’));
• 70% Time Reduction

 

Tip 10. Remove any redundant methematics

SQL을 사용하다 보면 수학 연산을 사용할 때가 많은 데 이는 쿼리문의 성능을 저하시키는 요소가 됩니다. 따라서 최소한의 수학 연산으로 쿼리문을 구성해야 합니다

• Original: SELECT * FROM SH.sales s WHERE s.cust_id + 10000 < 35000;
• Improved: SELECT * FROM SH.sales s WHERE s.cust_id < 25000;
• 11% Time Reduction

---

정리

1. SELECT 문에서 * (Asterisk)보다COLUMN 명을 지정하여 조회합니다.
2. HAVING 절은 모든 열이 선택된 이후에 필터를 위해 사용됩니다. 따라서, SELECT 문에서는 HAVING 절이 불필요하기 때문에 사용하지 않습니다.
3. 테이블은 기본키를 가지고 있기 때문에 DISTINCT를 사용할 필요가 없으므로 제거합니다.
4. 중첩된 쿼리를 조인 조건으로 재작성하는 것은 효율적인 실행과 효과적인 최적화를 불러 일으킵니다. 일반적으로 서브쿼리를 푸는 것은 ANY, ALL, EXISTS 등이 사용되는 한 개의 테이블에서 처리됩니다.
5. WHERE 조건문에서 OR로 연결하는 것 보다 IN을 사용하는 것이 효율적입니다.
6. DISTINCT 키워드는 모든 열을 선택한 후에 중복되는 것들을 파싱하는 형태로 작동하는데, 이는 성능을 저하 시킵니다. 
7. UNION 보다는 UNION ALL을 사용해야 합니다.
8. JOIN 조건에서는 OR의 사용을 줄입니다.
9. 집계 함수 기능을 제거하는 것이 성능을 더 높여줄 수 있습니다.
10. 불필요한 수학 연산은 제거합니다.

---

참고

https://mangkyu.tistory.com/52