데이터베이스 옵티마이저

 

옵티마이저란?

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

데이터베이스에서 SQL 쿼리를 빠르고 효율적으로 실행하기 위해 최적의 실행 계획을 만들어준다

옵티마이저의 목적은 쿼리 실행에 필요한 비용을 최소화하여 성능을 극대화하는 것이다

이 비용에는 디스크 I/O, CPU 사용량, 네트워크 트래픽 등이 포함된다

 

 

https://coding-factory.tistory.com/743

 

옵티마이저의 종류

1. 규칙 기반 옵티마이저(Rule-Based Optimizer, RBO):
   이 옵티마이저는 사전 정의된 규칙에 따라 쿼리를 최적화한다
   예를 들어, 특정한 형태의 쿼리에서는 항상 인덱스를 사용하는 방식으로 최적화하는 식
   이 방식은 상대적으로 간단하지만 최신 데이터베이스에서는 사용하지 않는다
   
   특정 우선순위
   
   
2. 비용 기반 옵티마이저(Cost-Based Optimizer, CBO):
   대부분의 현대적인 데이터베이스 관리 시스템(DBMS)에서 사용되는 방법
   CBO는 테이블의 통계 정보와 실행 비용을 바탕으로 가장 효율적인 실행 계획을 선택한다
   테이블의 데이터 분포나 인덱스의 효율성 등을 고려하여 더 정교한 실행 계획을 수립
   통계정보는 딕셔너리라는곳에 저장,업데이트한다

 

옵티마이저 동작 방식

1. Parser : SQL문장의 문법, 구조를 분석하여 쿼리 트리 생성
   
   • 쿼리트리란
     ```
     SELECT name  
     FROM employees  
     WHERE department = 'IT';
     
     
     //위와 같은 쿼리가 들어오면 아래의 트리구조로 변경이됨
                 SELECT  
                   |  
                 FILTER (department = 'IT')  
                   |  
                TABLE SCAN (employees)  
     ```
2. Optimization
   1. Query Trasformer : 파싱된 SQL을 보고, 동일한 결과를 도출하는 더 좋은 쿼리가 있으면 그것으로 바꿈
   2. Estimator: 시스템 통계정보를 딕셔너리로부터(통계정보 저장하는 저장소)
      수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다
   3. Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다 
3. Row-Source Genarator : 옵티마이저가 생성된 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅 한다
   
   • postgresql을 예로들면 row-source genarator에 의해서 c언어로 된 함수가 생성되고, 그것을 엔진이 실행하는 형태라고 한다
4. SQL Engine : sql을 실행합니다

통계정보

비용기반 옵티마이저에서 실행계획을 짜기 위해서 사용되는 정보이다

특정 주기를 설정하여 디비가 자동으로 통계정보를 갱신하도록 할 수 도 있고

사용자가 직접 만든 통계정보를 사용하도록 할 수도 있다

 

통계정보요소

테이블 크기	테이블에 저장된 총 레코드 수로, 테이블의 규모를 파악하여 최적의 접근 방식을 결정하는 데 사용된다
데이터 분포	특정 열의 값들이 어떻게 분포되어 있는지를 나타내며, 선택도를 예측하여 최적의 인덱스 사용 여부를 결정한다
인덱스 정보	인덱스가 어떤 열에 적용되었는지, 그리고 그 인덱스의 효율성을 평가하여 실행 계획을 수립하는 데 사용됨
널 값 비율	특정 열에 널(null) 값이 얼마나 있는지를 나타내며, 옵티마이저가 조인이나 필터링 조건을 더 효율적으로 처리하도록 도움
데이터 블록 수	테이블이나 인덱스를 저장하는 데 필요한 데이터 블록의 수로, I/O 비용을 예측하는 데 중요한 요소
클러스터링 팩터	인덱스의 순서와 테이블의 실제 데이터 순서 간의 관계를 나타내며, 테이블에 대한 접근 비용을 추정하는 데 사용