😀저번에 개발 취업 첫 면접을 보러갔을 때 CS질문 중에서 옵티마이저(Optimizer)가 무엇인지 설명해 달라는 질문을 받았다. 이때 나는 옵티티마이저에 대해서 들어본 적도, 알아본 적도 없어서 전혀 모르겠다는 솔찍한 답변을 했었다.
비록 그 회사에서 콜이 오진 않았지만, 그 면접을 내 지식을 늘려가는 기회로 삼아서 더 나은 내가 되도록 옵티마이저에 대한 포스팅을 정리해 보자.
옵티마이저(optimizer)란?
옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.
개발자가 작성한 SQL을 실행하면 DBMS에서는 옵티마이저를 기반으로 실행계획을 세워서 쿼리를 실행한다.
쿼리 실행 절차
😀그렇다면 우리는 사용자가 작성한 SQL쿼리가 어떻게 실행되고 어디서 어떻게 옵티마이저가 사용되는지어 우리에게 결과가 돌아오는지 알아볼 필요성이 있다.
쿼리의 실행절차를 크게 나누게 되면 아래의 4단계로 볼 수 있다.
Parsing > Optimization > Generation > Execution
- SQL Parsing
- 실질적으로 개발자가 작성한 SQL이 실행계획이라 생각되겠지만, DBMS는 우리가 세운 실행계획을 쪼개서 SQL파서를 통해 옵티마이저로 파싱한다.
- 우리가 전송한 SQL을 쪼개서 구성요소를 파악하고 이를 파싱해서 파싱트리를 만든다.
- 대부분의 SQL문장의 문법적인 검사는 파싱단계에서 이루어진다. SQL문법이 잘못되었다면 이 단계에서 걸러지며, 정상적이라면 SQL파싱 트리가 만들어진다.
- Optimization
- 파싱단계에서 만들어진 SQL파싱트리를 기반으로 옵티마이저가 가진 여러가지 동작을 수행한다.
- Query Transformer: 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행한다. 이 때 불필요한 조건을 제거하거나 복잡한 연산을 단순화 시키는 등의 작업을 수행한다.
- Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다.
- Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다.
- Generation
- 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅한다.
- Execution
- 포맷팅된 SQL을 실행하고 결과를 사용자에게 전달한다.
옵티마이저의 종류
옵티마이저는 최적화 기반을 기준으로 나누는 규칙기반 옵티마이저와, 비용기반 옵티마이저가 있다.
규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이지만, 현재는 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다.
규칙기반 옵티마이저(RBO : Rule-Based Optimizer)
RBO는 실행 계획을 결정할 때 미리 정해둔 규칙에 따라 쿼리를 최적화하는 방식이다.
RBO는 쿼리 실행 계획을 만들 때 일반적으로 사용되는 JOIN, WHERE 등의 SQL 구문에 대한 고정 규칙을 적용한다.
일반적으로 규칙을 세워둘 때 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하여 실행계획을 세운다.
규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산, 조건절 형태가 순위를 결정짓는 주요인이 된다.
DBMS에서 제공하는 RBO의 경우, 대부분의 경우 규칙의 우선순위를 사용자가 변경할 수 없다. 그러나 일부 DBMS(오라클의 ORSERED hint)에서는 사용자가 규칙의 우선순위를 변경할 수 있는 설정을 제공하기도 한다.
일반적으로 고정된 RBO의 우선순위는 다음과 같다.
| 우선순위 | 항목 |
|---|---|
| 1 | ROWID를 사용한 단일 행인 경우 |
| 2 | 클러스터 조인에 의한 단일 행인 경우 |
| 3 | 유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우 |
| 4 | 유일하거나 기본키(Primary Key)에 의한 단일 행인 경우 |
| 5 | 클러스터 조인인 경우 |
| 6 | 해시 클러스터 조인인 경우 |
| 7 | 인덱스 클러스터 키인 경우 |
| 8 | 복합 칼럼 인덱스인 경우 |
| 9 | 단일 칼럼 인덱스인 경우 |
| 10 | 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우 |
| 11 | 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우 |
| 12 | 정렬-병합(Sort-Merge) 조인인 경우 |
| 13 | 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우 |
| 14 | 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우 |
| 15 | 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우 |
비용기반 옵티마이저(CBO : Cost-Based Optimizer)
CBO는 실행 계획을 선택하기 위해 쿼리를 수행하는데 소요되는 일 량 또는 시간을 기반으로 최적화하는 방식이다.
CBO는 실행 계획을 최대 2천 개까지 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행하게 된다.
이때 실행계획을 수립할 때 판단 기준이 되는 비용은 예상치다. 따라서 CBO는 비용을 예측하기 위해 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보 및 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)를 이용한다.
통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다.
규칙기반 vs 비용기반
| 항목 | 규칙 기반 옵티마이저 | 비용 기반 옵티마이저 |
|---|---|---|
| 개념 | 사전에 정의된 규칙 기반 | 최소비용 계산 실행계획 수립 |
| 기준 | 실행우선 순위(Ranking) | 액세스 비용(Cost) |
| 인덱스 | 인덱스 존재 시 가장 우선시 사용 | Cost에 의한 결정 |
| 성능 | 사용자 SQL작성 숙련도 | 옵티마이저 예측 성능 |
| 장점 | 판단이 매우 규칙적 실행 예상 가능 | 통계 정보를 통한 현실 요소 적용 |
| 단점 | 예측 통계정보 요소 무시 | 최소 성능 보장 계획의 예측 제어 어려움 |
| 사례 | AND 중심 양쪽 ‘=’ 시 Index Merge 사용 | AND 중심 양쪽 ‘=’ 시 분포도별 Index 선택 |
옵티마이저에 영향을 미치는 요소
- SQL과 연산자 형태
- 결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있다.
- 옵티마이징 팩터
- 쿼리를 똑같이 작성하더라도, 인덱스, IOT, 클러스터링, 파티셔닝 등을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라진다.
- DBMS 제약 설정
- 개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.
- 예를 들어, 인덱스 칼럼에 Not Null 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있다.
- 옵티마이저 힌트
- 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선한다.
- 통계 정보
-
통계정보가 옵티마이저에게 미치는 영향력은 절대적이다. CBO의 모든 판단 기준은 통계정보에서 나온다.
-
주요 통계 정보들에는 다음과 같은 것들이 있다.
-
구분 세부 통계 정보 테이블 테이블의 전체 행의 갯수
테이블이 차지하고 있는 전체 블록 갯수
테이블의 행들이 가지고 있는 평균 길이컬럼 컬럼 값의 종류
컬럼 내부 NULL 값의 분포도
컬럼 값의 평균 길이
컬럼 내부 데이터 분포의 추정치인덱스 LEAF BLOCK 수 : 데이터를 보관하는 블록 수
LEVELS : 인덱스 트리의 LEVEL 정보
CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도시스템 통계 정보 I/O 성능 및 사용률
CPU 성능 및 사용률
-
- 옵티마이저 관련 파라미터
- SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 작동할 수 있다. 이는 옵티마이저 관련 파라미터가 추가 또는 변경되면서 나타나는 현상이다.
- DBMS 버전과 종류
- 옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다. 또한, 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.
참고자료(출처)
티스토리 HouseDust 블로그 포스팅 [DB] DB 성능 개선 방법
티스토리 코딩팩토리 블로그 포스팅 [DB] 데이터베이스 옵티마이저(Optimizer)에 대하여
티스토리 코드연구소 블로그 포스팅 [DB] 데이터베이스 옵티마이저(Optimizer)란?
티스토리 hyuuny 블로그 포스팅 [MySQL] 옵티마이저와 힌트 1
