SQL 최적화 기본원리 (옵티마이저)

옵티마이저

---

옵티마이저(Optimizer)는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행

이러한 최적의 실행방법을 실행계획(Execution Plan)이라고 한다.

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

옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라 규칙기반 옵티마이저(RBO, Rule Based Optimizer)와 비용기반 옵티마이저(CBO, Cost Based Optimizer)로 구분된다.

(현재 대부분의 데이터베이스는 비용기반 옵티마이저만을 제공)

규칙기반 옵티마이저

=> 규칙(우선순위)을 가지고 실행계획을 생성한다.

규칙기반 옵티마이저가 조인 순서를 결정할 때는 조인 컬럼 인덱스의 존재 유무가 중요한 판단의 기준이된다.

Single row by rowid

=> ROWID를 통해서 테이블에서 하나의 행을 액세스하는 방식 (ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있기 때문에 다른 정보를 참조하지 않고도 바로 원하는 행을 액세스할 수 있다.)

Single row by unique or primery key

=> 유일 인덱스를 통해서 하나의 행을 액세스하는 방식 (인덱스를 먼저 액세스하고 이넫ㄱ스에 존재하는 ROWID를 추출하여 테이블의 행을 액세스)

Composite index 

=> 복합 인덱스에 동등("=") 조건으로 검색하는 경우이다.

Single Column Index

=> 단일 컬럼 인덱스에 "=" 조건으로 검색하는 경우. 만약 A 컬럼에 단일 컬럼 인덱스가 생성되어 있고, 조건절에 A=20 형태로 검색하는 방식

Bounded range search on indexed columns 

=> 인덱스가 생성되어 있는 컬럼에 양쪽 범위를 한정하는 형태로 검색하는 방식. BETWEEN, LIKE 등이 존재

A BETWEEN '10' AND '20' 또는 A LIKE '1%' 형태로 검색하는 방식

Unbounded range search on indexed columns 

=> 인덱스가 생성되어 있는 컬럼에 양쪽 범위를 한정하는 형태로 검색하는 방식. >, >=, <, <= 등이 존재

A > '10' 형태로 검색하는 방식

Full table scan

전체 테이블을 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

SELECT ENAME FROM EMP WHERE JOB='SALESMAN' AND SAL BETWEEN 3000 AND 6000

일 때의 최적화 과정

JOB, SAL컬럼에 인덱스가 있을 경우

우선 순위 규칙 (규칙9) JOB='SALESMAN'  (규칙10) BETWEEN 

따라서 JOB 인덱스를 이용해서 조건을 만족하는 행에 먼저 액세스 하게 된다.

비용기반 옵티마이저

규칙기반옵팀아ㅣ저에서 조건절에 "=" 연산자와 "BETWEEN" 연산자가 사용되면 규칙에 따라 "=" 컬럼의 인덱스를 사용하는 것이 보다 적은 처리 범위로 작업을 할 것이라 판단하지만 실제로는 "BETWEEN"컬럼을 사용한 인덱스가 보다 일량이 적을 수 있다.

비용기반 옵티마이저는 이러한 규칙기반 옵티마이저의 단점을 극복하기 위해 출현했다.

대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈

( 비용기반 옵티마이저는 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다.)

(사진 및 내용 출처 : http://www.dbguide.net)