[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 3장. SQL 최적화 기본 원리 - 1. 옵티마이저와 실행 계획

옵티마이저

• 옵티마이저(Optimizer)는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.
  • 이러한 최적의 실행 방법을 실행계획(Execution Plan)이라고 한다.
• 최적의 실행 방법 결정이라는 것은 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일 을 처리할 수 있을지 결정하는 것이다.
• 옵티마이저가 최적의 실행 방 법을 결정하는 방식에 따라 규칙기반 옵티마이저(RBO, Rule Based Optimizer)와 비용기반 옵티마이저(CBO, Cost Based Optimizer)로 구분할 수 있다.

규칙기반 옵티마이저

• 규칙기반 옵티마이저는 규칙(우선 순위)을 가지고 실행계획을 생성한다.
  • 실행계획을 생성 하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측할 수 있다.
• 규칙기반 옵티마이저가 실행계획을 생성할 때 참조하는 정보
  • SQL문을 실행하기 위해서 이용 가능한 인덱스 유무와 (유일, 비유일, 단일, 복합 인덱스)종류
  • SQL문에서 사용하는 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류
  • SQL문에서 참조하는 객체(힙 테이블, 클러스터 테이 블 등)의 종류 등
• 결과적으로 규칙기반 옵티마이저는 우선 순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이라고 판단
• 규칙기반 옵티마이저의 우선순위 규칙은 다음과 같다.
  • 순위의 숫자가 낮을수록 높은 우선순위이다.
    
• 규칙기반 옵티마이저는 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선 순위가 높다.
  • 따라서 규칙기반 옵티마이저는 해당 SQL문에서 이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획을 생성한다.
• 규칙기반 옵티마이저의 조인 기법의 선택은 다음과 같다.
  • 양쪽 조인 칼럼에 모두 인덱스가 없는 경우에는 Sort Merge Join을 사용하고 둘 중하나라도 조인 칼럼에 인덱스가 존재한다 면 일반적으로 NL Join을 사용한다.

비용기반 옵티마이저

• 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.
• 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용 량을 의미한다.
• 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용한다.
  • 통계정보가 없는 경우 비용기반 옵티마이저는 정확한 비용 예측이 불가능해져서 비효율적인 실행계획을 생성할 수 있다.
  • 그렇기 때문에 정확한 통계정보를 유지하는 것은 비 용기반 최적화에서 중요한 요소이다.
• 비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성되어 있다.
  
• 비용기반 옵티마이저는 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.
• 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점이 있다.

실행 계획

• 실행계획(Execution Plan)이란 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다.
• 실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업이다.
• 동일한 SQL에 대해 결과를 낼 수 있는 다양한 처리 방법(실행계획)이 존재할 수 있지만 각 처리 방법마다 실행 시간(성능)은 서로 다를 수 있다.
• 실행계획을 구성하는 요소에는 조인 순서(Join Order), 조인 기법 (Join Method), 액세스 기법(Access Method), 최적화 정보(Optimization Information), 연 산(Operation) 등이 있다.
  

SQL 처리 흐름도

• SQL 처리 흐름도(Access Flow Diagram)란 SQL의 내부적인 처리 절차를 시각적으로 표현한 도표이다.
  • 실행계획을 시각화한 것!
• 액세스 처리 흐름도에는 SQL문의 처리를 위해 어떤 테이블을 먼저 읽었는지(조인 순서), 테이블을 읽기 위해서 인덱스 스캔을 수행했는지 또는 테이블 전체 스캔을 수행했는지(액세스 기법)과 조인 기법 등을 표현할 수 있다.