Database

데이터베이스 Optimizer(옵티마이저)

지금 느낌 그대로 2023. 9. 19. 19:48

옵티마이저(Optimizer)의 개념

옵티마이저는 SQL을 빠르고 효율적으로 수행하도록 '실행계획'을 수립하는 DBMS의 엔진입니다.

사용자가 쿼리를 입력하면,  옵티마이저는 쿼리를 분석해서 어떤 순서로 테이블을 조회할 지, 어떤 인덱스를 사용할 지등 여러가지 기준에 따라 여러가지 실행계획을 세웁니다. 

세워진 실행계획들 중 비용을 계산하여, 가장 효율적인 실행계획에 따라 쿼리를 수행합니다.

 

쿼리 실행 과정

 

옵티마이저(Optimizer)의 종류

옵티마이저는 실행 계획을 세우는 기준에 따라 크게 규칙 기반 옵티마이저(RBO, Rule Based Optimizer)와  비용 기반 옵티마이저(CBO, Cost Based Optimizer)로 나뉩니다.

 

규칙 기반 옵티마이저(RBO)는 여러가지 규칙을 만들어 두고, 규칙에 따라 실행계획을 수립합니다.

과거에는 옵티마이저의 비용 예측 능력이 뛰어나지 않아 규칙 기반 옵티마이저가 많이 사용되었지만, 최근에는 옵티마이저의 비용 예측 능력이 많이 향상되어 비용기반 옵티마이저가 많이 사용됩니다.

다음은 Oracle의 규칙 기반 옵티마이저에서 만든 규칙입니다.

순위가 낮을 수록 높은 우선순위를 가집니다.

 

순위     설명
1    ROWID를 사용한 단일 행인 경우
2    클러스터 조인에 의한 단일 행인 경우
3    유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우
4    유일하거나 기본키에 의한 단일 행인 경우
5    클러스터 조인인 경우
6    해시 클러스터 조인인 경우
7    인덱스 클러스터 키인 경우
8    복합 칼럼 인덱스인 경우
9    단일 칼럼 인덱스인 경우
10    인덱스가 구성된 컬럼에서 제한된 범위를 검색하는 경우
11    인덱스가 구성된 컬럼에서 무제한 범위를 검색하는 경우
12    정렬-병합(Sort - Merge) 조인인 경우
13    인덱스가 구성된 컬럼에서 MAX 혹은 MIN을 구하는 경우
14    인덱스가 구성된 컬럼에서 ORDER BY를 실행하는 경우
15    전체 테이블을 스캔(Full Table Scan)하는 경우
  • 해시 클러스터 : Oracle에서 데이터 베이스 열 중 하나를 선택하고, 해당 열의 해시 함수를 사용해 데이터를 물리적으로 그룹화한 것. 각 그룹에는 동일한 해시 값을 가진 데이터가 저장되어 동일한 해시값을 가진 데이터들은 디스크 내부에 물리적으로 가까운 곳에 저장됨. 디스크 헤드가 움직이는 범위가 줄어들어 효율적인 검색 가능
  • 해시 클러스터 키 : 해시 클러스터를 구성할 때 사용되는 열의 조합
  • 해시 클러스터 조인 : 해시 클러스터 키를 사용하여 여러 테이블 간의 조인 작업을 수행하는 방법. 특정 조인 키를 해시 함수를 사용해 해싱한 후, 해당 값을 기반으로 데이터에 액세스하여 가져오는 방식

 

 

비용 기반 옵티마이저(CBO)는 실행 계획의 비용을 계산해 가장 비용이 적은 실행계획을 가진 쿼리 수행 경로를 결정합니다. 

여기서 실행 계획의 비용은 통계정보를 저장하는 Dictionary에 저장된 통계를 기반으로 결정합니다.

비용은 예상되는 소요 시간과 자원 사용량(I/O 비용.CPU 연산 비용, 메모리 비용등)을 고려해 계산합니다.

하지만 이 비용은 통계를 기반으로 논리적으로 계산됩니다.

논리적으로 계산된다는 말의 의미는 통계 정보가 없거나 잘못된 경우, 정확한 비용을 계산하기 어려워 비효율적인 실행계획을 수립할 수 있습니다. 

그렇기 때문에, 비용 기반 옵티마이저를 최적화 하기 위해서는 정확하고 최신의 통계정보를 유지해야 합니다.

혹은 사용자가 SQL 힌트를 사용해서 직접 실행 경로를 작성해 줄 수 있습니다. 하지만 힌트, 인덱스, 조인의 개념을 정확하게 알지 못한 채로 무분별하게 힌트를 사용한다면 성능의 저하를 초래하기 때문에 적절하게 사용하는 것이 중요합니다.

 

다음은 대표적인 RDBMS 종류별로 CBO를 사용하기 시작한 버전입니다.

 

  • MySQL : MySQL 5.6 버전부터 CBO가 도입
  • MariaDB : MariaDB 5.3 버전부터 CBO가 사용
  • Oracle : Oracle 7i부터 CBO 지원. 11g 버전부터는 RBO 기술지원 종료 
  • PostgreSQL : 초기부터 CBO 사용

 

비용 기반 옵티마이저의 모드

비용 기반  옵티마이저는 여러 가지 모드를 가지고 있고, 모드에 따라 비용 계산 방식이 조금 다릅니다.

  • CHOOSE : SQL 실행 환경에서 통계정보를 얻어올 수 있다면 비용 기반 옵티마이저, 없다면 규칙 기반 옵티마이저로 동작
  • FIRST_ROWS : 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화하는 실행 계획을 수립하는 모드. 
  • FIRST_ROWS_n : SQL 실행 결과 중 처음 N개의 행을 빠르게 반환하기 위해 최적화된 모드. 웹 어플리케이션에서 특정 페이지의 처음 몇개 항목을 표시할 때 유리함 -> MySQL, MariaDB에서 default mode
  • ALL_ROWS : SQL 실행 결과 전체를 처리하는데 걸리는 비용을 최적화하는 실행 계획을 수립하는 모드. 최소한의 자원을 사용하여 최대한 빨리 가져오는 계획 수립. 대용량 데이터 조회에 유리함 

 

비용 기반 옵티마이저에서 사용되는 통계 정보

비용 기반 옵티마이저는 DBMS 내부의 Dictionary에 저장된 통계정보를 사용합니다.

사용자는 통계 정보의 생성 주기나 수행 시간을 스케줄러에 일괄적으로 등록하여 일정 주기마다 통계 정보를 수집하고 종료하도록 자동으로 통계 정보를 생성할 수도 있고, 수동으로 직접 생성할 수도 있습니다.

DBMS_STATS 패키지를 사용하면 데이터베이스,  스카미 및 계정, 테이블 인덱스 단위로 구분하여 통계정보를 수집할 수 있습니다.

 

주요 통계정보

구분 세부 통계 정보
테이블     테이블의 전체 행의 개수
    테이블이 차지하고 있는 전체 블록 개수
    테이블의 행들이 가지고 있는 평균 길이
컬럼     컬럼 값의 종류
    컬럼 내부 NULL 값의 분포도
    컬럼 값의 평균 길이
    컬럼 내부 데이터 분포의 추정치
인덱스     LEAF BLOCK 수 : 데이터를 보관하는 블록 수
    LEVELS : 인덱스 트리의 깊이 정보
    CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여있는 밀집도
시스템     I/O 성능 및 사용률
    CPU 성능 및 사용률

 

SQL 실행 과정 및 비용기반 Optimizer 구조

  • Parser : SQL 문장을 분석하여 문법 검사 및 구성요소를 파악하고 파싱 트리를 생성
  • Query Transformer : 파싱된 SQL을 보고 같은 결과를 도출하는 실행계획 중 비용이 낮은 SQL로 변환하는 역할 수행
  • Estimator : 시스템 통계정보를 Dictionary에서 수집하여 비용계산
  • Plan Generator : Estimator에서 계산한 비용을 기반으로 실행계획 생성
  • Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅
  • SQL Engine : SQL 실행

 

정리

옵티마이저의 성능은 계속해서 향상되고 있지만, 항상 최적의 실행 계획을 세우는 것은 아닙니다.

통계 정보만을 가지고는 조건절에서 사용된 조건을 만족하는 데이터의 양이 얼마나 되는지도 알 수 없고, 옵티마이저는 비용산정시 하나의 단독 쿼리문이 실행된다고 가정하고 계획을 수립하기 때문에 여러개의 쿼리가 동시에 실행되기도 하는 어플리케이션 환경에서는 부정확한 비용 계산이 이루어 질 수도 있습니다. 또한 컬럼의 데이터 분포와 데이터 값의 빈도를 파악하는데 도움을 주는 통계정보인 히스토그램 버킷의 크기가 한정되어 있어 그 이상의 값을 가지는 컬럼의 경우 비용 예측 결과가 부정확 할 수 있습니다.

사용자가 옵티마이저만 믿고 쿼리 튜닝을 하지 않는다면 어플리케이션을 최적화 하기 힘들 것 입니다.

SQL 힌트 같은 다양한 방법을 통해 최적화된 실행 계획으로 쿼리가 실행될 수 있도록, 수행 시간이 오래걸리는 쿼리 같은 경우에는 실행 계획을 보고 튜닝을 하는 것이 좋습니다.

 

 

저작자표시