14. SQL 최적화의 원리(SQLD 자격증 공부)

옵티마이저(Optimizer)

• SQL을 어떻게 실행할 것인지에 대해 계획, 즉 SQL 실행 계획을 수립후 실행
• 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라 성능이 다름
• SQL 성능에 아주 중요한 역활을 한다
• DBMS의 소프트웨어이다.

 ▷ 특징

 - 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계등 정보를 사용하여 예상되는 비용을 산정
 - 최저비용을 가지고 있는 계획을 선택하여 SQL 실행

 

 ▷ 실행 계획 확인

실행 계획을 PLAN_TABLE에 저장

DESC PLAN_TABLE​

 

 ▷ 실행 방법

• 파싱(Parsing)을 실행하여 문법검사 및 구문분석 수행
• 구문분석 후 규칙 기반 또는 비용 기반으로 실행 계획 수립
• 기본적으로는 비용 기반 옵티마이저를 사용해 실행 계획을 수립 
• 비용 기반 옵티마이저는 통계정보를 활용하여 최적의 실행 계획을 수립
• 실행이 완료되면 데이터를 인출(Fetch)

 ▷ 옵티마이저 엔진

• Query Transformer : SQL을 효율적으로 실행하기 위해 옵티마이저가 변환
• Estimator : 통계정보를 사용해서 실행비용 계산
• Plan Generator : 실행계획 수립

 ▷ 옵티마이저 우선 순위

• 규칙기반 옵티마이저(Rule base Optimizer)는 실행계획을 수립시에 15개의 우선순위를 기준으로 계획을 수립
• Oracle은 규칙기반 옵티마이저보다 비용기반 옵티마이저를 기본적으로 사용

순위	내용
1	ROWID를 사용한 단일 행
2	클러스터 조인에 의한 단일 행
3	유일 또는 기본키를 가진 해시 클러스터 키에 의한 단일 행
4	유일하거나 기본키에 의한 단일 행
5	클러스터 조인
6	해시 클러스터 조인
7	인덱스 클러스터 키
8	복합 칼럼 인덱스
9	단일 칼럼 인덱스
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색
12	정렬 - 병합 조건
13	인덱스가 구성된 칼럼에서 MAX 또는 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행
15	전체 테이블을 스캔

※ 클러스터란?
디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 데이터를 디스크 같은 위치에 저장시키는 방법

 

 ▷ 비용 기반 옵티마이저(Cost base Optimizer)

• 오브젝트 통계 및 시스템 통계를 사용해서 총비용을 계산
• 총비용은 SQL문을 실행하는데 예상되는 소요시간 또는 자원의 사용량을 의미
• 총비용이 적은쪽으로 실행 계획을 수립
• 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.

---

인덱스(Index)

• 데이터를 빠르게 검색할 수 있는 방법을 제공
• 오름차순(asc) 및 내림차순(desc) 탐색이 가능
• 하나의 테이블에 여러개의 인덱스를 생성할 수 있고, 하나의 인덱스는 여러개의 칼럼으로 구성할 수 있다.
• 기본키는 자동으로 인덱스가 생성
• 구조로는 Root Block, Branch Block, Leaf Block으로 구성
• Root Block은 인덱스 트리에서 가장 상위에 있는 노드
• Branch Block은 다음 단계의 주소를 가지고 있는 포인터
• Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어 저장
• Leaf Block은 Double Linked List 형태로 되어 있어 양방향 탐색이 가능

인덱스 구조

 ▷ 인덱스 생성

CREATE INDEX문을 사용하여 생성하며 기본적으로 오름차순으로 정렬한다.
다만 DESC를 포함하면 내림차순으로 정렬한다.

CREATE INDEX 인덱스이름 EMP ON
	EMP(ENAME ASC, SAL DESC)	-- 생성 예시​

 

 ▷ 인덱스 스캔(Index Scan)

1. 인덱스 유일 스캔(Index Unique Scan) : 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생
2. 인덱스 범위 스캔(Index Range Scan) : 특정 범위를 조회하는 WHERE문을 사용할 경우 발생하며 Like,Between이 대표적인 예시가 된다. 또한 범위 스캔은 인덱스의 Leaf Block의 특정 범위를 스캔한 것이다.
3. 인덱스 전채 스캔(Index Full Scan) : 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽는다.

 ※ Table Full Scan시에 High Watermark의 의미
High Watermark는 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미하며 데이터가 삭제되면 해당내용은 변경

---

▷ Nested Loop 조인

• 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식
• 먼저 조회되는 테이블을 외부 테이블, 그다음 조회되는 테이블을 내부 테이블
• RANDOM ACCESS가 많이 발생하면 성능 지연이 발생

 ▷ Sort Merge 조인

• 두개의 테이블을 SORT_AREA라는 메모리 공간에 모드 로딩하고 SORT를 수행
• SORT가 완료되면 두개의 테이블을 병합
• 데이터양이 많아지면 성능이 떨어진다.
• 정렬 데이터가 너무 많으면 정렬은 임시영역에서 수행하며 임시영역은 디스크에 존재하기에 성능이 떨어짐

 ▷ Hash 조인

• 두개의 테이블 조인키를 사용해서 해시 테이블을 생성
• 함수를 사용해 주소를 계산하고 해당주소를 사용해 테이블을 조인하기에 CPU연산을 많이 한다.