데이터 베이스 옵티마이저란?

1.옵티마이저란?

SQL을 실행을 할 때 DB는 내부적으로 SQL을 가장 빠르고 효율적이게 처리를 하려고 한다. 때문에 DBMS는 실행계획을 세우고 SQL을 실행시킨다. 실행계획을 짜기 위해 사용하는 것이 옵티마이저이다. 

---

2.실행계획(Execution Plan)

동일한 결과가 나오는 SQL을 작성하더라도 어떻게 실행되냐에 따라 성능이 달라질 수 있다. 옵티마이저는 Data Dictionary에 있는 오브젝트 통계, 시스템 통계등의 정보를 사용해서 예상되는 비용을 평가하여 여러 실행계획을 작성한다. 그 뒤 여러가지 실행계획의 비용을 계산하고 가장 낮은 비용을 가진 실행 계획을 선택하여 SQL을 실행시킨다.

---

3.쿼리의 처리 흐름

출처: https://zangzangs.tistory.com/103

1. SQL이 작성되고 실행된다.
2. 파서(Parser)는 작성된 SQL의 문법검사와 구문 분석을 실시한다.
3. 옵티마이저는 실행 계획을 생성하고 카탈로그 매니저에 있는 통계정보를 참고하여 비용을 평가한다. 
   그리고 평가한 결과를 토대로 실행계획을 결정한다.
   실행계획 수행의 순서는 다음과 같다.
    1) Query Transformer : 파싱된 SQL을 일반적, 표준적인 형태로 변환한다.
    2) Estimator : 객체, 시스템 통계정보에 기반한 선택도, 카디널리티, 비용을 계산하고 총 비용을 계산한다.
    3) Plan Generator : 하나의 쿼리를 수행하는데 후보군이 될만한 실행계획을 생성한다.
4. 옵티마이저가 생성한 실행계획을 SQL엔진이 실제로 실행 가능한 코드의 형태로 변환한다.
5.  SQL 실행 엔진이 옵티마이저가 결정한 실행계획대로 데이터를 처리한다.

---

4. 옵티마이저의 종류

옵티마이저의 종류는 두가지로 나뉜다. 규칙기반 옵티마이저와 비용기반 옵티마이저이다. 

1) 규칙기반 옵티마이저(Rule Based Optimizer)

실행계획을 세울때 특정한 규칙에 따라 세우는 방식이다. 규칙은 여러개가 있으며, 각 규칙마다 우선 순위가 매겨져 있다.

우선순위가 높은 순서대로 실행계획을 생성한다. 즉, 우선순위가 높은 규칙대로 처리할 경우 보다 효율적으로 처리가 가능하다고 인식하는 것이다. 

우선순위	규칙 내용
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우( ex) BETWEEN , LIKE 연산자를 이용시)
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우(ex) >, >=, =, < =, <  연산자 사용시)
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

규칙대로 실행한다고 해서 무조건 빨라지는 것은 아니다. 위의 규칙에서 상위 규칙이라고 절대적으로 빠른것은 아니기 때문이다. 테이블의 row가 적은 경우에는 15번 규칙의 FULL TABLE SCAN이 더 빠를 수도 있다. 하지만 규칙상 INDEX를 사용하는게 더 빠르게 설정되어 있기 때문에, 옵티마이저는 비효율적인 방법을 택할 수 있다. 또 HINT 나 HASH JOIN 은 규칙 기반 옵티마이저 이후에 나온 개념이므로 규칙기반 옵티마이저에서는 사용할 수 없다.

2) 규칙기반 옵티마이저에서 조인 테이블의 조건

규칙 기반 옵티마이저에서 조인 순서를 결정할 때, 조인 칼럼 인덱스의 존재유무가 판단기준이 된다. 하지만 조인 테이블의 칼럼에 인덱스가 없을 수도 있고 우선순위가 다를 수도 있다. 정리하면 다음과 같다.

둘다 칼럼에 인덱스가 있는 경우 + 우선순위가 A > B
우선순위	선행테이블	후행테이블
규칙상 우선순위가 높은것	A	B
A테이블 칼럼에 인덱스가 있는 경우
우선순위	선행테이블	후행테이블
인덱스가 없는 테이블의 칼럼 먼저	B	A
둘다 칼럼에 인덱스가 있는 경우 + 우선순위가 같을 경우 + FROM 절 뒤에 A가 먼저 B가 나중에 나올 때
우선순위	선행테이블	후행테이블
FROM 절로 나열된 것의 역순	B	A
둘다 칼럼에 인덱스가 없는경우 +  FROM 절 뒤 순서가 A일 경우
우선순위	선행테이블	후행테이블
FROM 절 뒤에 있는거 먼저	A	B

참고1.

일반적으로 인덱스가 걸려있는 칼럼이 하나라도 있는경우에는 NL Join 을 사용하고 인덱스가 걸려있는 칼럼이 하나라도 없다면 Sort Merge Join 을 사용한다고 한다.

3) 비용기반 옵티마이저(Cost Based Optimaizer)

앞서 말했듯 규칙기반에 의한 실행은 절대적으로 빠른것이 아니다. 현실에선 몇개의 규칙과 참조로 정확한 효율을 계산하기에는 무리가 있다. 비용기반 옵티마이저는 보다 정확한 예측을 하기 위해 출현하였다. 비용기반 옵티마이저는 SQL을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택한다. 비용은 예상되는 소요시간 혹은 자원 사용량을 의미한다. 규칙기반 옵티마이저와 차이점은 규칙기반 옵티마이저에서는 사용하지 않았던 테이블, 인덱스 ,칼럼등 다양한 객체의 통계정보와 시스템 통계정보(CPU, 디스크 I/O 속도 등) 등을 이용한다는 것이다. 때문에 통계정보의 정확도와 보관이 필요하다.

비용기반 옵티마이저의 대략적으로 3부분으로 나눌때 질의변환기 / 대안계획 생성기 / 비용 예측기로 나눌 수 있다.

• 질의 변환기

작성한 SQL을 처리하기에 용이한 형태로 변환한다. 

• 대안계획 생성기

동일한 결과르르 생겅하는 다양한 대안 계획을 생성하며, 이 계획은 연산의 적용순서 변경, 연산 방법 변경 ,조인 순서 변경등을 통해 생성된다. 원론적으로 동일한 결과를 생성하는 최대한의 대안계획을 생성해야 보다 나은 최적화를 수행 할 수 있다. 하지만 대한 계획이 너무 많이 생성되게 된다면 처리 시간이 오래 소요될 수 있다. 그래서 대부분의 상용 옵티마이저는 대안 계획의 생성 숫자를 최대 2000개 정도로 제한한다. 제한된 경우의 수 안에 최적의 대안 계획이 포함되지 않을 수도 있다는 단점이 있다. 

• 비용 예측기

대안 계획에 대한 비용을 예측한다. 비용의 정확한 측정을 위해 연산의 중간 집합의 크기 및 결과 집합의 크기 ,분포도 등의 예측이 정확하게 되어야 된다. 규칙기반 옵티마이저와 다르게 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다. DBMS 버전, DBMS 설정정보, 벤더에 따라 동일 SQL문이라 해도 예측이 다를 수 있어 서로 다른 실행 계획이 생성될 수 있다.

4) 비용기반 옵티마이저 행동에 영향을 미치는 요소 

• SQL문과 연산자의 형태
• 옵티마이징 팩터
  쿼리가 같아도 인덱스, 파티셔닝, 클러스터링 등의 구성 형태에 따라 실행계획과 성능이 달라진다.
• DBMS 제약 설정
  PK, FK, NOT NULL 과 같은 제약설정은 쿼리 성능 최적화에 중요한 정보를 제공한다. 
  예를 들어 인덱스 칼럼에  NOT NULL 이 설정되었다면 옵티마이저는 전체 개수를 구하는 COUNT 쿼리에 이 인덱스를 활용할 수 있다.
• 옵티마이저 힌트
  사용자가 지정한 힌트를 우선한다.
• 통계정보
  모든 판단 기준은 통계정보에서 도출된다.
• 옵티마이저 관련 파라미터
  SQL, 데이터, 통계정보, H/W 등의 모든 환경에 동일해도 DBMS 버전을 업데이트하면 옵티마이저 관련 파라미터가 변경되기 때문에 옵티마이저가 다르게 작동할 수 있다.

---

참고자료

조인수행 원리 https://eehoeskrap.tistory.com/84

데이터베이스]옵티마이저 (Optimizer)https://zangzangs.tistory.com/103

[DB] 데이터베이스 옵티마이저(Optimizer)란?https://code-lab1.tistory.com/137

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여https://coding-factory.tistory.com/743