SQL튜닝

개요

인덱스 튜닝	테이블 엑세스 최소화, 인덱스 스캔 효율화, 인덱스 설계
조인 튜닝	nl조인, 해시 조인, 소트 머지 조인 ...
쿼리 실행계획 확인	Explain문
where절 최적화
캐시 사용	쿼리 자주 실행되는 경우
파티셔닝

 

인덱스 튜닝의 핵심 요소는 크게 두 가지로 나뉜다.
1. 인덱스 스캔 효율화 튜닝

인덱스 스캔 과정에서 발생하는 비효율을 줄이는것

인덱스 정렬이 이루어졌다면 

2. 테이블 엑세스 횟수를 줄이는 것

인덱스 스캔 후 테이블 레코드들 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 랜덤 엑세스 최소화 튜닝이라고 한다.

 

 

https://hongchangsub.com/sql-tuning-2/

친절한 SQL 튜닝 - 2. 인덱스 구조 및 탐색

 

 

디테일 방법들

1. where문 좌변을 연산하지 않을 것

좌변 연산이 인덱스 활용을 방해
ex) 좌변에 함수 씌우는거

SELECT * FROM sales WHERE YEAR(date) = 2021;

대신에

SELECT * FROM sales WHERE date >= '2021-01-01' AND date <= '2021-12-31';

2. or 대신 union 사용

OR연산자를 사용하면 데이터베이스는 한 번의 스캔으로 모든 조건을 확인해야 한다.

이 과정에서 불필요한 데이터까지 대량으로 검색하게 되고, 특히 인덱스를 제대로 활용하지 못하는 경우가 많다.
( 인덱스는 단일 값에 대한 빠른 검색을 위해 최적화되어 있는데, OR은 여러 값을 동시에 찾아야 함. )

UNION은 각 조건에 대한 쿼리를 별도로 실행한 뒤, 그 결과를 합쳐주는 연산자

SELECT * FROM employees WHERE department = 'Marketing' OR department = 'IT';

위 쿼리를 UNION을 사용해 다음과 같이 바꿔볼 수 있다

SELECT * FROM employees WHERE department = 'Marketing'
UNION
SELECT * FROM employees WHERE department = 'IT';

department = 'Marketing'와 department = 'IT'는 각각 인덱스를 통해 빠르게 처리될 수 있습니다. 그리고 나서 UNION이 두 결과를 합치는 거죠. 이 과정에서 중복된 결과는 자동으로 제거된다.

 

만약 중복이 없다는 게 확실하다면 UNION ALL을 사용해 중복 제거 단계를 건너뛰고 성능을 더 높일 수도 있다.

 

3. 필요한 Row와 Column만 선택하기

각 부서별로 최고 매출액을 달성한 직원의 정보를 조회

SELECT e.name, e.department, e.sales
FROM employees e
JOIN (
  SELECT department, MAX(sales) AS max_sales
  FROM employees
  GROUP BY department
) d ON e.department = d.department AND e.sales = d.max_sales;

서브쿼리에서 불필요한 Column을 제외하고 오직 department와 max_sales만 선택함으로써, 중간 결과의 크기를 최소화

 

4. ROW_NUMBER(), RANK(), LEAD(), LAG() 등의 분석 함수를 활용

5. LIKE 연산자와 와일드카드(%)를 사용할 때는 문자열 끝에 와일드카드를 두는 것이 인덱스 활용에 유리

 

6. 서브쿼리 사용 최소화하기

( 서브쿼리 : SELECT문 안에 포함된 또 다른 SELECT문 )
서브쿼리 대신 JOIN, UNION, EXISTS 등의 다른 방법을 사용하여 쿼리를 작성하는 것이 좋다. 
또한, 서브쿼리를 사용할 때는 서브쿼리의 결과를 캐시하여 재사용하는 것이 좋다.

 

성별이 여성인 고객이 주문한 모든 주문을 가져오기

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE gender = 'F'
)

이를 JOIN으로 변경할수 있음

SELECT orders.*
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.gender = 'F'