쿼리 최적화에 대한 설명과 방법은 개발자 면접에서 자주 나오는 질문 중의 하나!

1. 필요성

• 최적화되지 않은 쿼리는 CPU, 메모리를 불필요하게 소모

• 서비스를 사용하는 사용자에게 불편함을 제공

2. 방법

1) SELECT * 사용 금지

-- 비추천
SELECT * FROM SH.Sales;

-- 추천
SELECT s.PROD_ID FROM SH.Sales s;

• 테이블에서 몇 개의 컬럼만을 조회하는 경우라면, SELECT * 을 사용할 필요가 없다.

  • 아스타리스크( * )를 사용하여 필요하지 않은 칼럼을 조회해야 하는 경우, 불필요하게 리소스를 소모

• 필요한 칼럼만 조회

  • 테이블의 크기를 줄이고 네트워크 트래픽을 감소시킴으로써 쿼리의 평균 속도를 높일 수 있다.
  • 쿼리를 최적화하는 가장 쉬운 방법

2) SELECT문에 HAVING절을 포함하지 말자

-- 비추천
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

-- 추천
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;

• Having 절은 모든 열이 선택된 이후에 필터를 위해 사용된다.

• SELECT 문에서는 HAVING절이 불필요

  • HAVING절은 최종 테이블에서 모든 열들을 파싱하면서, HAVING 조건에 충족되지 않는 열들을 필터링한다.

참고: SQL - MIN, MAX, AVG, SUM, HAVING

3) LIKE 검색시 와일드카드(%)는 끝에 작성

-- 비추천
SELECT * 
FROM TABLE
WHERE COL LIKE '%ABC%'

-- 추천
SELECT * 
FROM TABLE
WHERE COL LIKE 'ABC%'

• 가능하면 와일드카드를 끝에 작성하는 것이 좋다.

• LIKE 검색 시, 와일드카드(%)가 시작 부분에 있는 경우, 인덱스를 활용하지 않는다.

  • 따라서, DB 는 모든 레코드를 검색하므로 검색 속도가 느릴 수 밖에 없다.

4) 중복 제거

(1)

-- 비추천
SELECT DISTINCT * FROM SH.Sales s 
JOIN SH.Customer c ON s.cust_id = c.cust_id 
WHERE c.cust_marital_status = 'single';

-- 추천
SELECT * FROM SH.Sales s 
JOIN SH.Customer c ON s.cust_id = c.cust_id 
WHERE c.cust_marital_status = 'single';

• DISTINCT 가 불필요할 경우에는 사용하지 말자.

  • 테이블은 Primary Key 를 가지고 있기 때문에, DISTINCT 를 사용할 필요가 없다.
  • DISTINCT 를 사용하면, 정렬하는 과정이 들어가서 쿼리의 속도가 상당히 저하된다.

• UNION 과 DISTINCT 를 같이 사용하지 말자.

  • UNION 에는 중복 값을 제거하는 기능이 존재하므로, 중복을 제거해주는 DISTINCT 와 함께 사용할 필요가 없다.

• Distinct 와 Group By 를 함께 사용하지 말자.

  • Group By절에 입력된 칼럼을 그룹화하므로, 중복된 값 중 대표값만 가져오는 DISTINCT 와 사용할 필요가 없다.

  참고: SQL - Join, Union
  참고: SQL - TOP, LIMIT, ROWNUM, DISTINCT, COUNT

(2)

-- 비추천
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);

-- 추천
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;

• 중첩된 쿼리를 조인조건으로 재작성하는 것은 효율적인 실행과 효과적인 최적화를 불러일으킨다.

(3)

-- 비추천
SELECT cust_id FROM SH.sales UNION SELECT cust_id FROM customers;

-- 추천
SELECT cust_id FROM SH.sales UNION ALL SELECT cust_id FROM customers;

• UNION 보다는 UNION ALL 을 사용하는 것이 빠르다.
  • UNION 문 : 중복된 열의 존재 유무에 상관없이 열을 선택할 때 중복 검사
  • UNION ALL : 중복검사를 하지 않는다.

5) IN-list 에는 일정한 값만 포함

-- 비추천
SELECT s.* FROM SH.sales s 
WHERE s.prod_id = 14 OR s.prod_id = 17;

-- 추천
SELECT s.* FROM SH.sales s 
WHERE s.prod_id IN (14, 17);

• IN-list에는 상수 또는 쿼리 블록이 실행되는 동안 일정한 값만 포함해야 한다.

• IN-list

  • index된 검색을 위해 활용될 수 있다.
  • Index의 정렬 순서와 일치하도록 IN-list를 정렬하여 보다 효율적인 검색을 수행할 수 있다.

6) ORDER BY 금지

• 서브 쿼리(Sub Query)에서 Order By 를 사용하는 경우, 많은 비용이 발생

참고: SQL - Subquery (서브쿼리)

7) IN 대신 EXISTS 사용

-- 비추천
SELECT DISTINCT c.country_id, c.country_name 
FROM SH.countries c, SH.customers e 
WHERE e.country_id = c.country_id;

-- 추천
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);

• WHERE문에서 서브 쿼리의 값을 확인하려는 경우, IN 또는 EXISTS 를 사용할 수 있다.

  • EXISTS

    • 일치하는 항목이 발견되는 즉시 검색 프로세스를 종료
    • 서브 쿼리의 결과가 많을 경우, EXISTS 가 나은 성능을 제공
    • 서브쿼리와 함께 EXISTS키워드를 사용한다면, 전체 테이블을 조회하는 것을 피할 수 있다.

  • IN

    • 모든 항목을 비교
    • 서브 쿼리의 결과가 적을 경우, IN 사용을 권장

참고: SQL 쿼리문 - DDL, DML, DCL

8) WHERE문에 함수 사용 제한

SELECT *
FROM TABLE
WHERE CONCAT(COL1, ' ') = 'ABC'

• 인덱스로 잡혀있는 칼럼은 WHERE문 또는 JOIN에서 함수와 사용하는 것을 피해야 한다.
  • COL1이라는 컬럼이 인덱스로 잡혀있다.
    • 이때, WHERE문에서 함수와 함께 사용되면 인덱스가 깨지므로 검색 속도가 느려진다.

9) 암시적 변환 금지

• 암시적 변환

  • DB 에서 값을 비교할 때, 데이터 타입이 다른 경우 DB 에서 자동으로 타입을 변환 후 값을 비교하는 방식

• 동일한 타입으로 값을 비교하는 것이 좋다.

  • 암시적 변환을 수행하는데 불필요한 리소스가 소모되기 때문

10) 조인 조건에 OR 을 사용하지 말자

-- 비추천
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;

-- 추천
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;

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

11) 집계 함수 기능을 제거하여 쿼리를 재작성

-- 비추천
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;

-- 추천
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'));

12) 불필요한 수학 연산을 제거

-- 비추천
SELECT * FROM SH.sales s WHERE s.cust_id + 10000 < 35000;

-- 추천
SELECT * FROM SH.sales s WHERE s.cust_id < 25000;

---

참고: [SQL]간단하면서 쉬운 쿼리 최적화 방법
참고: [Database] 쿼리 Query 최적화 및 튜닝 기술