[SQLD] 2-3. SQL 최적화 기본 원리

 

1. 옵티마이저와 실행계획

---

 

1-1. 옵티마이저 란?

---

• 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.
• 최적의 실행 방법을 실행계획(Execution Plan)이라 한다.
• 어떤 방법으로 처리하는 것이 동일한 일을 최소의 일량으로 처리할 수 있을지 결정한다.

 

 

1-2. 옵티마이저 종류

---

• 규칙 기반 옵티마이저
  • 우선순위 규칙에 따라 실행계획을 생상하며 인덱스가 있으면 반드시 인덱스를 사용한다.
• 비용 기반 옵티마이저
  • 처리 비용이 가장 적은 실행계획 선택한다. 
  • 데이터 딕셔너리(Data Dictionary)의 통계정보나 DBMS의 차이로 같은 쿼리도 다른 실행계획이 생성될 수 있다.
  • 실행계획의 예측 및 제어가 어렵다.

 

 

1-3. SQL문 실행 순서

---

1. 파싱(Parsing) : SQL 문법 검사 및 구문 분석 작업
2. 실행(Execution) : 옵티마이저의 실행 계획에 따라 실행
3. 인출(Fetch) : 데이터를 읽어 전송

 

 

1-4. 옵티마이저 엔진

---

• 질의 변환기
  • 사용자가 작성한 SQL문을 처리하기 용이한 형태로 변환하는 모듈
• 대안 계획 생성기
  • 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
  • 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성
  • 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다.
• 비용 예측기
  • 생성된 대안 계획의 비용을 예측하는 모듈

 

 

1-5. 실행계획 이란?

---

• SQL에서 요구한 사항을 처리하기 위한 절차와 방법
• 다양한 실행계획(처리 방법)마다 성능(실행시간)은 서로 다를 수 있다.
• 옵티마이저는 최적의 실행계획 생성하는 것이다.

 

 

1-6. 실행계획 구성 요소

---

• 조인 순서 (Join Order)
• 조인 기법 (Join Method)
• 액세스 기법 (Access Method)
• 최적화 정보 (Optimization Information) : 실행계획의 각 단계마다 예상 비용 표시
  • Cost : 상대적인 비용 정보
  • Card : Cardinality의 약자. (결과 집합의 건수)
  • Bytes : 결과 집합이 차지하는 메모리의 양 (바이트) 
• 연산 (Operation) : 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업

 

 

1-7. SQL 처리 흐름도 란?

---

• SQL의 내부적인 처리 절차를 시각적으로 표현한 도표로 실행계획을 시각화한 것이다.
• 조인 순서, 액세스 기법과 조인 기법 등을 표현 가능하다.
• 액세스 건수, 조인 시도 건수, 테이블 액세스 건수, 성공 건수, 스캔 방식 등 표현

 

 

2. 인덱스 기본

---

 

2-1. 인덱스 란?

---

검색 조건에 부합하는 데이터를 효과적으로 검색할 수 있도록 돕는 기능이다.

 

 

2-2. 인덱스 종류

---

• 트리 기반 인덱스
• 클러스터형 인덱스

 

 

2-3. 전체 테이블 스캔 이란?

---

테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로 추출하고 아니면 버리는 방식으로 검색한다.

 

 

2-4. 인덱스 스캔 이란?

---

• 인덱스를 구성하는 칼럼의 값을 기반으로 데이터 추출
• 인덱스를 읽어 ROWID를 찾고 해당 데이터를 찾기 위해 테이블을 읽음
• 일반적으로 인덱스 칼럼 순서로 정렬되어 출력
• 적은 데이터를 조회할 때 유리

 

 

2-5. 인덱스 스캔 종류

---

1. 인덱스 유일 스캔
2. 인덱스 범위 스캔
3. 인덱스 역순 범위 스캔
4. 기타 : 인덱스 전체 스캔, 인덱스 고속 전체 스캔, 인덱스 스킵 스캔

 

 

3. 조인 수행 원리

---

 

3-1. 조인 이란?

---

두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다.

 

 

3-2. 조인의 종류

---

1. NL Join
2. Sort Merge Join
3. Hash Join

 

 

3-3. NL Join

---

• 두 개의 테이블을 중첩된 반복문처럼 조인을 수행한다.
• 반복문 외부(처음 테이블)에 있는 테이블을 선행 테이블 또는 외부 테이블
• 반복문 내부(두번째 테이블)에 있는 테이블을 후행 테이블 또는 내부 테이블이라고 부름
• 결과 행의 수가 적은 테이블을 선행 테이블로 선택한다

  
  for 선행테이블 읽음 ( 외부 테이블 ) 
  	for 후행테이블 읽음 ( 내부 테이블 ) 
    	(선행테이블과 후행테이블 조인)
          

> NL Join 절차

1. 선행 테이블에서 조건에 맞는 값을 찾는다
2. 선행 테이블의 조인 키를 가지고 후행 테이블 조인 키 확인
3. 후행 테이블의 인덱스에 선행 테이블의 조인 키 존재 확인
4. 인덱스에서 추출한 레코드 식별자(ROWID)를 이용하여 후행 테이블 액세스하여 버퍼에 저장
5. 앞의 작업을 선행 테이블에서 만족하는 키 값이 없을 때까지 반복하여 수행

 

 

3-4. Sort Merge Join

---

• 조인 칼럼을 기준으로 데이터를 정렬하여 조인
• 넓은 범위의 데이터를 처리할 때 주로 이용
• 인덱스를 사용하지 않아 인덱스가 존재하지 않을 경우에 사용할 수 있음

> Sort Merge Join 절차

1. 선행 테이블에서 조건에 맞는 행을 찾는다
2. 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행
3. 1~2번 작업을 반복 수행하여 모든 행을 찾아 정렬
4. 후행 테이블에서도 같은 작업을 진행
5. 정렬된 결과를 이용하여 조인을 수행하고 결과 값을 추출 버퍼에 저장

 

 

3-5. Hash Join 

---

• 해싱 기법을 이용하여 조인을 수행
• NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 정렬 작업의 부담을 해결하기 위한 대안
• 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있음
• “=”로 수행하는 동등 조인만 가능
• 결과 행의 수가 적은 테이블을 선행 테이블로 사용

> Hash Join 절차

1. 선행 테이블에서 조건에 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성
3. 1~2번 작업을 반복하여 선행 테이블의 모든 조건에 맞는 행을 찾아 해쉬 테이블 완성
4. 후행 테이블에서 조건에 만족하는 행을 찾음
5. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음
6. 같은 버킷에 해당되면 조인에 성공하여 추출 버퍼에 저장
7. 후행 테이블의 조건만큼 반복 수행하여 완료