쿼리 최적화

1. 쿼리 최적화란?

쿼리 최적화(Query Optimization)는 데이터베이스에서 실행되는 쿼리를 최적의 방식으로 처리하도록 설계하여 응답 속도와 리소스 사용량을 개선하는 작업이다.

---

2. 쿼리 최적화가 왜 필요한가?

문제점

1. 느린 응답 속도
   • 데이터가 많아질수록 쿼리 실행 시간이 길어져 사용자 경험이 저하된다.
2. 비효율적인 리소스 사용
   • CPU, 메모리, 디스크 I/O 자원이 과도하게 사용된다.
3. 확장성 제한
   • 비효율적인 쿼리는 시스템 확장 비용을 증가시킨다.

---

3. 쿼리 최적화 팁

논문 SQL 쿼리 최적화 기법 - 효율적이고 빠른 SQL 쿼리를 작성하기 위한 팁

저자: Jean HABIMANA

1. SELECT 문에서 * 대신 열 이름 사용
2. SELECT 문에서 HAVING 절을 피하고 WHERE 절 사용
3. 불필요한 DISTINCT 조건 제거
4. 서브쿼리의 평면화
5. IN 조건 사용
6. EXISTS 사용으로 DISTINCT 대체
7. UNION 대신 UNION ALL 사용
8. 조인 조건에서 OR 사용 피하기
9. 연산자 오른쪽에서 함수 사용 피하기
10. 불필요한 수학 연산 제거

---

3.1 SELECT 문에서 * 대신 열 이름 사용

• 테이블에서 특정 열만 필요할 경우, SELECT *를 사용하는 대신 필요한 열만 명시적으로 선택해야 한다.
• SELECT *는 작성하기는 간단하지만, 데이터베이스가 불필요한 데이터를 처리해야 하므로 성능이 저하된다.

예시

• 원본 쿼리:

SELECT * FROM SH.Sales;

• 개선된 쿼리:

SELECT s.prod_id FROM SH.sales s;

• 27%의 시간 단축

3.2 SELECT 문에서 HAVING 절을 피하고 WHERE 절 사용

• HAVING 절은 결과 테이블에서 필터를 적용하므로, 불필요한 계산을 발생시킬 수 있다.
• 가능한 경우 WHERE 절을 사용하여 데이터를 필터링해야 한다.

예시

• 원본 쿼리:

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;

• 31%의 시간 단축

3.3 불필요한 DISTINCT 조건 제거

• 결과 집합에 기본 키나 고유 제약 조건이 포함되어 있다면 DISTINCT는 필요하지 않다.
• DISTINCT를 사용하면 데이터베이스가 중복된 행을 찾고 제거하는 추가 작업을 수행해야 한다.
• 불필요한 DISTINCT 조건을 제거하여 성능을 향상시킬 수 있다.

예시

• 원본 쿼리:

SELECT DISTINCT * 
FROM SH.sales s
JOIN SH.customers c ON s.cust_id = c.cust_id
WHERE c.cust_marital_status = 'single';

• 개선된 쿼리:

SELECT * 
FROM SH.sales s
JOIN SH.customers c ON s.cust_id = c.cust_id
WHERE c.cust_marital_status = 'single';

• 85%의 시간 단축

3.4 서브쿼리의 평면화

• 중첩된 서브쿼리는 JOIN으로 변환하는 것이 더 효율적이다.
• 서브쿼리 방식은 서브쿼리가 먼저 실행된 후에 그 결과 값을 상위 쿼리가 전달받아 실행한다.
• JOIN 방식은 조인과 조건 필터링이 병렬 처리될 수 있어 성능이 향상될 수 있다.

예시

• 원본 쿼리:

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, SH.sales s
WHERE p.prod_id = s.prod_id
AND s.cust_id = 100996
AND s.quantity_sold = 1;

• 61%의 시간 단축

3.5 IN 조건 사용

• 인덱스가 있는 열을 조회할 때 IN 조건을 사용하는 것이 더 효율적이다.
• IN 조건은 쿼리 옵티마이저가 인덱스를 더 효과적으로 활용할 수 있게 한다.
• 옵티마이저는 IN 리스트를 정렬하여 인덱스의 정렬 순서와 일치시킬 수 있어, 더 효율적인 검색이 가능해진다.
• OR 조건을 사용하면 각 조건에 대해 별도로 인덱스를 검색해야 하므로 성능이 저하될 수 있다.

예시

• 원본 쿼리:

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

• 73%의 시간 단축

3.6 EXISTS 사용으로 DISTINCT 대체

• 다대일(one-to-many) 관계에서 조인을 수행할 때 DISTINCT 대신 서브쿼리와 함께 EXISTS를 사용하는 것이 더 효율적이다.
• EXISTS는 일반적으로 조건을 만족하는 첫 번째 행을 찾으면 즉시 결과를 반환하므로, 전체 결과 집합을 생성할 필요가 없다.
• 반면, DISTINCT는 모든 결과를 생성한 후 중복을 제거해야 하므로, 데이터 양이 많을 경우 성능 저하가 발생할 수 있다.

예시

• 원본 쿼리:

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

• 61%의 시간 단축

3.7 UNION 대신 UNION ALL 사용

• UNION은 중복을 제거하지만, 이로 인해 성능이 저하된다.
• 중복 제거가 필요하지 않다면 UNION ALL을 사용하는 것이 좋다.

예시

• 원본 쿼리:

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;

• 81%의 시간 단축

3.8 조인 조건에서 OR 사용 피하기

• 조인 조건에 'OR'을 사용하면 SQL 엔진이 두 개 이상의 조건을 모두 평가해야 하므로 쿼리 성능이 크게 저하될 수 있다.
• 데이터베이스가 조인할 때 더 많은 행을 검사해야 하고, 결과적으로 쿼리의 실행 시간이 두 배 이상 느려질 수 있다.

예시

• 원본 쿼리:

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;

• 70%의 시간 단축

3.9 연산자 오른쪽에서 함수 사용 피하기

• 연산자의 오른쪽에 위치한 함수는 인덱스 사용을 방해하거나 쿼리 최적화를 어렵게 만들 수 있으므로, 가능한 한 이러한 함수를 피하고 직접적인 값이나 상수를 사용하는 것이 좋다

예시

• 원본 쿼리:

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/31/2001', 'MM/DD/YYYY'));

• 70%의 시간 단축

3.10 불필요한 수학 연산 제거

• SQL 쿼리에서 수학 연산이 포함될 경우, 데이터베이스는 각 행을 찾을 때마다 해당 수학 연산을 다시 계산해야 하므로 성능에 부정적인 영향을 미칠 수 있다.

예시

• 원본 쿼리:

SELECT * 
FROM SH.sales s 
WHERE s.cust_id + 10000 < 35000;

• 개선된 쿼리:

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

• 11%의 시간 단축

4. 쿼리 최적화 이후에 생각해볼 방법

4.1 인덱스 활용

• 인덱스란?
  테이블의 특정 열(Column)에 대한 데이터를 정렬해 저장하는 자료구조로, 검색 속도를 획기적으로 개선할 수 있다.

풀 스캔 (Full Table Scan)

• 방식:
  • 모든 행을 하나씩 읽어 조건에 맞는지 확인한다.
  • 디스크에서 많은 양의 데이터를 읽어야 하므로 느리다.
• 시간 복잡도:
  • O(N), 여기서 N은 테이블의 총 행 수이다.

인덱스 스캔 (Index Scan)

• 방식:
  • 검색 키로 인덱스에서 위치를 찾고, 해당 행만 빠르게 조회한다.
  • 필요한 데이터만 읽으므로 빠르다.
• 시간 복잡도:
  • O(log N), 여기서 N은 키 수이다.

인덱스 스캔이 빠른 이유

1. 정렬된 데이터 구조:
   • 인덱스는 데이터를 미리 정렬하여 저장한다.
   • 원하는 데이터가 어디에 있는지 빠르게 알 수 있다.
2. 범위 탐색 최적화:
   • WHERE 조건에서 범위를 지정하면, 인덱스의 정렬된 구조를 따라 필요한 범위만 스캔할 수 있다.
   • 예: WHERE customer_id BETWEEN 100 AND 200

인덱스 추가 전후 성능 비교

-- 인덱스 없는 경우
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- 인덱스 추가
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 인덱스 사용 후
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

성능 차이

• 데이터 크기: 100만 건
• 결과:
  • 인덱스 없음 → Full Table Scan, 1.2초
  • 인덱스 있음 → Index Scan, 0.03초

---

4.2 N+1 문제 해결

• N+1 문제란?
  부모 엔티티 조회 후, 관련된 자식 엔티티를 개별 쿼리로 조회하는 비효율적인 패턴이다.

N+1 문제의 발생 원인

1. Lazy Loading:
   • 기본적으로 ORM(Object-Relational Mapping) 프레임워크에서 자주 발생하는 문제로, 부모 엔티티를 조회할 때 자식 엔티티를 즉시 로드하지 않고 필요할 때마다 개별적으로 로드하는 방식이다.
   • 이로 인해 부모 엔티티가 N개일 경우, N개의 추가 쿼리가 발생하게 된다.
2. 관계 설정의 부적절함:
   • 엔티티 간의 관계를 잘못 설정하거나, 적절한 Fetch 전략을 사용하지 않으면 N+1 문제가 발생할 수 있다.
   • 예를 들어, 부모와 자식 간의 관계를 One-to-Many로 설정했지만, 자식 엔티티를 즉시 로드하지 않으면 문제가 발생한다.
3. 비효율적인 쿼리 작성:
   • 개발자가 쿼리를 작성할 때, 자식 엔티티를 조회하기 위해 반복적으로 쿼리를 실행하는 경우에도 N+1 문제가 발생할 수 있다.
   • 이 경우, 쿼리 최적화가 필요하다.

N+1 문제 해결

// 비효율적인 방식
List<Customer> customers = customerRepository.findAll();
for (Customer customer : customers) {
    List<Order> orders = orderRepository.findByCustomerId(customer.getId());
}

// 패치 조인 사용 - 흔한 해결법
@Query("SELECT c FROM Customer c JOIN FETCH c.orders")
List<Customer> findAllWithOrders();

성능 차이

• 데이터 크기: 고객 100명, 고객당 주문 10건
• 결과:
  • N+1 문제 → 101번 쿼리, 2.5초
  • 패치 조인 → 1번 쿼리, 0.2초

---

4.3 쿼리 리팩토링

• 불필요한 서브쿼리 제거, 조건 재정렬, 중복 데이터 제거.

리팩토링 전후

-- 리팩토링 전
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_details WHERE product_id = 5);

-- 리팩토링 후 (JOIN으로 대체)
SELECT o.* FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE od.product_id = 5;

---

4.4 캐싱

• 자주 조회되는 데이터를 메모리에 저장하여 데이터베이스 조회를 줄일 수 있다.

캐싱 사용 전후

// Spring Cache 적용
@Cacheable("customerOrders")
public List<Order> getOrdersByCustomer(Long customerId) {
    return orderRepository.findByCustomerId(customerId);
}

성능 차이

• 데이터 크기: 10만 건
• 결과:
  • 캐싱 없음 → 0.8초
  • 캐싱 있음 → 0.02초

---

5. 최적화 팁

1. 모니터링 도구 활용
   • EXPLAIN명령어로 실행 계획을 분석한다.
     • EXPLAIN : 쿼리가 어떻게 실행될 것인지, 어떤 인덱스가 사용될 것인지, 테이블이 어떻게 조인될 것인지 등의 정보를 확인할 수 있다.
   • EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
   • APM (Application Performance Monitoring) 도구로 병목 지점을 확인한다.
2. 최적화 대상 선정
   • 실행 빈도가 높은 쿼리를 우선 최적화한다.
   • 느린 쿼리는 로그로 기록해 지속적으로 관리한다

---

6. 참고 문헌

• https://chung-develop.tistory.com/145
• https://jhlee-developer.tistory.com/entry/MYSQL-SQL-%EC%BF%BC%EB%A6%AC%EB%AC%B8-%EC%B5%9C%EC%A0%81%ED%99%94-%ED%9A%A8%EC%9C%A8%EC%A0%81%EC%9D%B8-%EC%BF%BC%EB%A6%AC%EB%A5%BC-%EC%9C%84%ED%95%9C-%ED%8C%81
• https://community.heartcount.io/ko/query-optimization-tips/
• Query Optimization Techniques - Tips For Writing Efficient And Faster SQL Queries ( Jean HABIMANA )