옵티마이저(Optimizer)란?
개념 : 데이터를 기반으로 최적의 실행 계획을 수립해주는 것
옵티마이저 특징
- 옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다.
- 개발자가 SQL 작성하고 실행하면 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저(Optimizer)라는 곳에서 "이 쿼리문을 어떻게 실행시키겠다!"라는 여러 가지 실행 계획을 세운다.
- 옵티마이저가 선택한 실행 방법의 적절성 여부는 쿼리 수행 속도에 가장 큰 영향을 미친다.
옵티마이저 왜 필요한가?
: 옵티마이저는 데이터베이스 시스템의 성능과 효율성을 향상시키는 데 필요합니다. 쿼리 성능 최적화, 리소스 최적화, 자동 튜닝, 다양한 최적화 기법의 활용, 유연성 및 확장성 등의 이점을 제공하여 데이터베이스 시스템이 효율적으로 동작하고 최적의 성능을 달성할 수 있도록 도와줍니다.
옵티마이저 동작 과정
- Parser : SQL 문장의 각 요소를 파싱해서 파싱트리를 만듦(문법 검사, 구문 분석)
- Query Transformer : SQL문을 효율적으로 실행하기 위해 더 일반적이고 표준적인 형태로 옵티마이저 변환
- Estimator : 시스템 통계정보를 사용해서 SQL 실행비용을 계산
- Plan Generator : SQL을 실행할 계획들을 수립
- Row-Source Generator : 옵티마이저가 생성한 계획을 SQL Engine이 실행 가능한 코드로 포맷
- SQL Engine : SQL 실행
일반적인 데이터베이스 옵티마이저의 동작
-
파싱(Parsing): 쿼리 문장을 분석하고 구문 트리(Syntax Tree) 또는 파싱 트리(Parse Tree)를 생성합니다. -
변환(Transformation): 파싱된 구문 트리를 논리적 트리 또는 대표적인 형태로 변환합니다. 이 단계에서 쿼리의 의미를 파악하고 최적화에 필요한 정보를 수집합니다. -
최적화(Optimization): 변환된 논리적 트리를 기반으로 다양한 실행 계획 후보들을 생성하고, 각 후보의 비용을 추정하여 최적의 실행 계획을 선택합니다. 이 단계에서 최적화 알고리즘과 최적화 기법이 적용됩니다. -
실행(Execution): 선택된 실행 계획을 기반으로 쿼리를 실행하고 결과를 반환합니다.
옵티마이저 종류
1. 규칙 기반 최적화(Rule-Based Optimizer, RBO)
실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하는 옵티마이저이다.
- 과거의 옵티마이저의 비용 예측하는 성능이 좋지 않아서 규칙기반 옵티마이저를 사용했었다.
규칙 기반 최적화 장점
- 간단하고 직관적인 방법: 미리 정의된 규칙 세트를 사용하므로 상대적으로 이해하기 쉽습니다.
- 계산 비용이 낮음: 규칙은 미리 정의되어 있으므로 추가 계산이 거의 필요하지 않습니다.
규칙 기반 최적화 단점
- 제한된 최적화 능력: 규칙은 단순한 패턴에 대해 효과적이지만, 복잡한 쿼리나 데이터 상호작용을 다루기 어려울 수 있습니다.
- 제약 조건 처리의 어려움: 많은 제약 조건을 고려하기 어렵고, 유연성이 부족할 수 있습니다.
- 정확성 부족: 최적의 실행 계획을 보장하기 어려울 수 있습니다.
2. 비용 기반 최적화(Cost-Based Optimizer, CBO)
비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤(최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행한다.
비용 기반 옵티마이저는 비용을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다.
비용 기반 최적화(CBO) 장점
- 최적의 실행 계획 선택: 비용을 고려하여 실행 계획 후보를 비교하여 가장 효율적인 실행 계획을 선택할 수 있습니다.
- 자동 튜닝: 데이터베이스 통계 및 구조 변경에 따라 자동으로 실행 계획을 조정할 수 있습니다.
- 다양한 최적화 기법 활용: 인덱스, 조인 기법, 부분 집합 추출 등 다양한 최적화 기법을 활용하여 성능을 최대화할 수 있습니다.
비용 기반 최적화(CBO) 단점
- 통계 정보의 정확성: 통계 정보의 정확성에 따라 실행 계획 품질이 달라질 수 있습니다.
- 복잡한 쿼리 처리의 어려움: 매우 복잡한 쿼리에 대해서는 최적의 실행 계획을 선택하기 어려울 수 있습니다.
3. 휴리스틱 기반 옵티마이저(Heuristic-Based Optimizer)
휴리스틱 기반 옵티마이저는 경험적인 규칙을 사용하여 실행 계획을 결정합니다. 비용 기반 옵티마이저와 비슷하지만, 정확한 비용 계산 대신 휴리스틱 규칙을 적용합니다.
휴리스틱 기반 옵티마이저 장점
상대적으로 빠른 계산 속도: 휴리스틱 기반 방법은 비교적 간단한 알고리즘을 사용하므로 계산 비용이 낮을 수 있습니다.
제한된 최적화 능력: 휴리스틱 기반 방법은 몇 가지 경험적인 규칙을 사용하여 실행 계획을 결정합니다.
휴리스틱 기반 옵티마이저 단점
- 정확성 부족: 휴리스틱 기반 방법은 최적의 실행 계획을 보장하지 않을 수 있습니다.
- 제약 조건 처리의 어려움: 많은 제약 조건을 고려하기 어렵고, 유연성이 부족할 수 있습니다.
옵티마이저는 만능이 아니다.
- 칼럼의 통계 정보만 가지고는 조건절에서 사용된 조건을 만족하는 데이터의 양이 어느정도인지 알 수가 없기에 비용 계산 결과가 정확하지가 않습니다.
- 그리고 비용산정시 쿼리문 단독으로 실행된다고 가정하기에 운영 서버와 같이 쿼리가 동시 실행된다면 실제 비용은 달라질 수 있기도 하고 히스토그램 버킷이 최대 254까지만 사용할 수 있어 254개 이상의 값을 갖는 컬럼의 경우 비용 예측 결과가 정확하지 않는 등의 옵티마이저에 여러가지 빈틈들이 많습니다.
Reference & Additional Resources
