[SQLD] 2과목 SQL 기본 및 활용 - 3장 SQL 최적화 기본 원리

박진우·2022년 6월 9일

2과목 SQL 최적화 SQLD

SQLD

목록 보기

18/21

💡 옵티마이저와 실행계획

옵티마이저의 개요

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

즉 다양한 실행방법들 중에서 최적의 실행방법을 결정하는 것이 옵티마이저의 역할이라고 할 수 있다.

◽ 옵티마이저 종류

◾규칙 기반 옵티마이저 (RBO, Rule Based Optimizer)

우선순위 규칙에 따라 실행계획을 생상하며 인덱스가 있으면 반드시 인덱스를 사용한다.

규칙(우선순위)를 가지고 실행계획을 생성한다.
인덱스유무(유일, 비유일, 단일, 복합 인덱스)
연산자(=, <, ...)- 객체(힙 테이블, 클러스터 테이블)의 세 종류를 이용하여 SQL문을 실행한다.
규칙기반 옵티마이저는 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법
규칙 기반 옵티마이저의 규칙

규칙1. Single row by rowid

ROWID를 통해서 테이블에서 하나의 행을 엑세스하는 방식
ROWID는 행이 포함된 데이터파일, 블록 등의 정보를 가지고 있음
하나의 행을 액세스 하는 가장 빠른 방법

규칙4. Single row by unique or primary key

유일 인덱스를 통해 하나의 행을 엑세스하는 방식
인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 엑세스

규칙8. Composite index

복합인덱스에 동등(=) 조건으로 검색하는 경우
복합인덱스 사이의 우선순위 규칙
- 1. 인덱스 구성 칼럼의 개수가 더 많은 것
- 1. 모든 구성 칼럼에 대해 '='로 값이 주어진 것

규칙9. Single column index

단일 칼럼 인덱스에 '=' 조건으로 검색하는 경우

규칙 10. Bounded range search on indexed columns

인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한저하는 형태로 검색하는 방식
BETWEEN, LIKE 등- A BETWEEN '10' AND '20' 또는 A LIKE '1%'규칙

11. Unbounded range search on indexed columns

인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 방식
,>, <, <=, >= 등규칙 15. Full table scan- 전체 테이블을 엑세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위가 높다.

조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재할 때, 우선순위가 높은 테이블을 선택한다.

조인 칼럼에만 인덱스가 존재하는 경우에는 인덱스가 없는 테이블을 먼저 선택하여 조인 수행한다.

만약 조인 테이블의 우선순위가 동일하지않다면, FROM 절에 나열된 테이블의 역순으로 수행한다.

양쪽 조인 칼럼에 모두 인덱스가 없는 경우 Sort Merge Join 사용- 조인 칼럼에 인덱스가 존재한다면 NL Join 사용한다.

◾비용 기반 옵티마이저 (CBO, Cost Based Optimizer)

SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이며, 규칙기반 옵티마이저의 단점을 극복 하기 위하여 출현했다.

SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식
테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보등을 이용한다.
처리 비용이 가장 적은 실행계획 선택한다.
데이터 딕셔너리(Data Dictionary)의 통계정보나 DBMS의 차이로 같은 쿼리도 다른 실행계획이 생성될 수 있다.
실행계획의 예측 및 제어가 어렵다.

◽ 비용기반 옵티마이저의 3가지 모듈

질의 변환기, 대안 계획 생성기, 비용 예측기

질의 변환기: 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈이다.
대안 계획 생성기: 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈이다.
비용 예측기: 대안 계획 생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈, 정확한 통계정보를 필요로 한다.
비용기반 옵티마이저: 규칙기반 옵티마이저와는 다르게 비용에 따라 인덱스 스캔, 또는 전체 테이블을 스캔할 수 있다.
통계정보, DBMS 설정정보, DBMS 버전 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.

◽ 옵티마이저 실행계획(Execution Plan)

요구한 사항을 처리하기 위한 절차와 방법을 의미한다.

실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업이다. 옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다.

즉, 옵티마이저는 최적의 실행 계획을 생성해준다.

“SQL에서 요구한 사항을 처리하기위한 절차와 방법”
실행계획을 구성하는 요소에는 조인순서, 조인기법, 액세스기법, 최적화정보, 연산 등
동일한 SQL에 대해 결과를 낼 수 있는 다양한 처리 방법(실행계획)이 존재할 수 있지만 각 처리 방법마다 실행 시간(성능)은 서로 다를 수 있다.
옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다.
조인 순서 : EMP ➡️ DEPT, NL Join 기법 사용, 인덱스 스캔 기법 사용, 각 최적화 정보 표시
논리적으로 가능한 조인 순서는 N!개 만큼 존재 (✅N: FROM절에 존재하는 테이블 수)
NL Join, Hash Join, Sort Merge Join 기법
인덱스 스캔, 전체 테이블 스캔 방법
최적화 정보 : Cost, Card, Bytes
최적화 정보는 통계 정보를 바탕으로 옵티마이저가 계산한 예상치
연산에는 조인기법, 엑세스 기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재한다.

◾ SQL 실행계획 수립 & 실행

옵티마이저는 데이터베이스 관리 시스템(DBMS)의 소프트웨어
개발자가 SQL 작성하면 → 옵티마이저가 실행계획 수립하고 SQL 실행
결과는 동일해도, 어떻게 실행하느냐에 따라서 성능 달라짐 ➡️ 실행계획은 SQL 성능에 있어서 중요하다.
옵티마이저는 PLAN_TABLE에 실행계획을 저장함 ➡️ 개발자가 조회하여 확인 가능 (DESC PLAN_TABLE;)

◽ SQL 처리 흐름도

SQL의 내부적인 처리절차를 시각적으로 표현한 도표

실행계획을 시각화 해놓은 것
Outer Table, Driving Table : 조인을 먼저 수행하는 테이블
Inner Table, Lookup Table : 조인을 나중에 수행하는 테이블
랜덤방식 엑세스
NL Join 수행
TAB1(테이블 전체 스캔), TAB2(인덱스 스캔)
일량 표시 가능 (액세스 건수, 조인 시도 건수, 테이블 액세스 건수, 성공 건수에 표시 가능)

◽ 옵티마이저의 특징

옵티마이저의 계획	옵티마이저의 필요성
오브젝트 통계, 시스템 통계 등의 정보를 사용 → 예상 비용 산정	개발자가 작성한 SQL문을 효율적으로 실행하기 위해 계획 수립
여러 실행계획 중에서, 최저비용의 실행계획을 선택함	개발자는 힌트를 사용하여 실행계획 변경을 요청할 수 있음

❓힌트(HINT): 개발자가 옵티마이저에게 실행방법을 알려주는 것

◽ 옵티마이저 실행/구동 단계

① SQL 실행	② 파싱 (Parsing)	③ 실행계획 수립	④ 실행계획 저장	⑤ SQL 실행 및 인출
개발자가 SQL문을 작성하고 실행함	SQL의 문법검사 및 구문분석 수행	옵티마이저가 SQL 실행계획 수립	실행계획 수립 및 선정 완료 후 저장함	SQL 실행 및 데이터 인출 (Fetch)

◽ 옵티마이저 엔진/ 서브엔진별 역할

Query Transformer	Estimator	Plan Generator
- SQL문을 효율적으로 실행하기 위함이다. - 좀 더 일반적이고 표준적인 형태로 변환한다. - 변환되어도 결과는 동일하다.	- 최적의 실행계획을 위해 총비용을 계산한다. - 통계정보를 사용하여 SQL 실행비용 계산한다. - 각 단계의 선택도, 카디널리티, 비용 계산한다.	- SQL을 실행할 실행계획 수립한다. - 하나의 쿼리를 수행하는 데에 후보군이 될만한 실행계획들을 생성한다.