[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 최적화 기본 원리 #1

<목차>

• 옵티마이저

• 실행 계획

• SQL 처리 흐름도

<< 옵티마이저와 실행계획 >>

 

 

1. 옵티마이저

* 사용자가 질의한 SQL 문에 대해 최적의 실행 방법(실행 계획 ; Execution Plan)을 결정하는 역할을 수행한다.

* 최적의 실행 방법이란, 같은 일을 처리하더라도 최소의 일량으로 처리하는 방법을 말한다.

* 규칙 기반 옵티마이저(Rule Based Optimizer ; RBO), 비용 기반 옵티마이저(Cost Based Optimizer ; CBO)로 분류할 수 있다.

---> 대부분의 관계형 DB는 비용 기반 옵티마이저만을 제공.

옵티마이저의 종류

 

A. 규칙 기반 옵티마이저 (RBO)

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

* SQL문을 실행하기 위해 가용 인덱스 유무와 종류,

  SQL문에서 사용하는 연산자의 종류,

  SQL문에서 참조하는 객체의 종류 등을 기반으로 실행 계획을 생성한다.

 

 

B. 비용 기반 옵티마이저 (CBO)

* 단순히 몇 개의 규칙만으로 모든 상황을 정확히 판단할 수는 없다.

* 비용 기반 옵티마이저는 규칙 기반 옵티마이저의 단점을 극복하기 위해 등장했다.

* SQL문을 처리하는데 필요한 비용이 가장 적은 실행 계획을 선택한다.

  (비용 : SQL문을 처리하기 위해 예상되는 소요시간 / 자원)

* 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등 다양한 객체 통계 정보와 시스템 통계 정보를 이용한다.

* 정확한 통계 정보를 유지하는 것이 비용 기반 최적화에서 중요한 요소이다.

 

2. 실행 계획

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

* 동일한 SQL에 대해 다양한 실행 계획이 존재하지만 각 계획마다 성능은 서로 다를 수 있다.

실행 계획 정보의 구성요소

 : 실행 계획을 구성하는 요소에는

   A) 조인 순서,

   B) 조인 기법,

   C) 액세스 기법,

   D) 최적화 정보,

   E) 연산 등이 있다.

 

 

A. 조인 순서

* 조인 작업을 수행할 때 참조하는 테이블의 순서.

• FROM 절에 A, B 테이블이 존재할 때 조인 작업을 위해 A 테이블을 읽고 B 테이블을 읽는 작업을 한다면 조인 순서는 A -> B이다. 위의 그림에서는 EMP -> DEPT이다.

* N 개의 테이블이 존재할 때 조인 순서는 N!개.

 

 

B. 조인 기법

* 두 개의 테이블을 조인할 때 사용할 수 있는 방법. (NL JOIN, Hash JOIN, Sort Merge JOIN 등)

• 위 그림에서는 NL JOIN을 사용하고 있다.

 

C. 액세스 기법

* 하나의 테이블을 액세스할 때 사용할 수 있는 방법이다.

* 인덱스를 이용하여 테이블을 액세스하는 인덱스 스캔

* 테이블 전체를 모두 읽으면서 조건을 만족하는 행을 찾는 테이블 스캔.

• 위 그림에서는 인덱스를 이용한 인덱스 스캔을 사용하고 있다.

 

D. 최적화 정보

* 옵티마이저가 실행 계획의 각 단계마다 예상되는 비용 사항을 표시한 것.

* Cost, Card, Bytes가 있다.

• Cost : 상대적인 비용 정보.

• Card : Cardinality의 약자, 주어진 조건을 만족한 결과 집합의 건수를 의미.

• Bytes : 결과 집합이 차지하는 메모리의 양을 바이트로 표시한 것.

* 비용 정보는 실제로 SQL을 실행하고 얻은 결과가 아닌, 통계 정보를 바탕으로 옵티마이저가 계산한 예상수치이다.

* 비용 사항이 실행 계획에 표기되지 않았다면, 규칙 기반 방식으로 실행 계획을 생성한 것.

• 위 그림에서는 최적화 정보가 표기되어 있으므로, 비용 기반 방식으로 실행 계획을 생성한 것이다.

 

 

E. 연산

* 조인 기법, 액세스 기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재한다.

• SQL에서 정렬을 목적으로 ORDER BY를 수행했다면 정렬 연산이 표시된다.

 

3. SQL 처리 흐름도

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

SQL 처리 흐름도

: SQL문의 처리를 위해 어떤 테이블을 먼저 읽었는지(조인 순서),

  테이블을 읽기 위해서 인덱스 스캔을 수행했는지 또는 테이블 전체 스캔을 수행했는지(액세스 기법),

  조인 기법 등을 표현.

• 위 그림에서는 조인 순서 : TAB1(Outer Table OR Driving Table) -> TAB2(Inner Table OR Lookup Table)

• 액세스 기법 : TAB1은 테이블 전체 스캔, TAB2는 I01_TAB2 라는 인덱스를 통한 스캔 수행.

• 조인 기법은 NL JOIN 수행.

• 대량의 데이터를 Random 방식으로 액세스하면 많은 I/O가 발생하여 성능상 좋지 않다.

* SQL 처리 흐름도에 일량을 함께 표시할 수 있다.

• '건수' 라고 표시된 곳에 SQL 처리를 위해 작업한 건수 또는 처리 결과 건수 등의 일량을 함께 표시할 수 있다.

• 이를 통해 어느 부분에서 비효율적으로 수행되는지 힌트를 얻을 수 있다.

 

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148208&boardConfigUid=9&categoryUid=216&boardIdx=136&boardStep=1

데이터 전문가 지식포털 DBGuide.net