SQL 수행 과정 중 옵티마이저에 대해 대략적으로 살펴보겠습니다.
SQL 수행 과정
1. 파싱(Parse)
- 문법 검사 및 SQL → 파스 트리 생성
변수 바인딩 위치 확인, 테이블/컬럼 이름 검증 등
2. SQL 캐시 확인
- 캐시 hit인 경우 바로 4 또는 5로 넘어갑니다.
3. 옵티마이징(Optimize)
- 여러 실행 계획(Access Path) 중 가장 효율적인 경로 선택
이때 인덱스 사용 여부, join 순서, 서브쿼리 방식 등이 결정됨
4. 바인딩(Bind)
- 변수 값 바인딩 (PreparedStatement라면 이 단계 포함)
5. 실행(Execute)
- 옵티마이저가 선택한 실행 계획에 따라 실제 데이터 접근
결과 반환
옵티마이저 방식
1. 규칙 기반 옵티마이저 (RBO)
- 사전 정의된 규칙에 따라 결정 (과거 Oracle)
2. 비용 기반 옵티마이저 (CBO)
- 통계 정보 기반으로 카디널리티, 인덱스 존재 유무, I/O 비용등을 평가하여 실행 계획 선택
현대 DB는 CBO를 채택하고 있습니다.
옵티마이저 동작 방식
1. 통계정보 가져오기
- ANALYZE, DBMS_STATS 등을 통해 미리 수집된 통계 기반
- 카디널리티 (cardinality): 예상 행 수
- 히스토그램: 컬럼 값 분포
- 테이블/인덱스 크기, 블록 수
- NULL 비율, NDV (# of distinct values)
2. 가능한 액세스 경로 탐색
- 테이블 액세스: Full Scan, Index Scan, Index Range Scan, Index Skip Scan 등
- Join 방식: Nested Loop, Hash Join, Sort-Merge Join 등
- 조인 순서 및 연결 순서 (Join Order)
3. 셀렉티비티(Selectivity) 추정
- 각 조건이 전체 행에서 몇 %를 필터링하는지 추정
- 예시:
- col = 'A' → 5%
- col LIKE 'A%' → 20%
- col BETWEEN 10 AND 20 → 10%
- 셀렉티비티 = 조건을 만족할 확률, 값이 낮을수록 좋은 조건
4. 카디널리티 계산
- 각 연산 또는 조합 후의 예상 row 수를 계산
- 예:
- 테이블 A: 10,000건
- WHERE gender = 'F' → 셀렉티비티 0.5 → 5,000건
카디널리티
한 테이블의 컬럼이 가질 수 있는 서로 다른 값의 개수
또는 두 테이블 간의 관계에서 한 쪽이 다른 쪽과 얼마나 연결되는지 (관계의 수)
5. 비용(Cost) 계산
- IO 비용, CPU 비용, 메모리 사용량 등을 총합하여 “비용” 수치로 환산
- 비용은 단위 시간이나 block 단위가 아님. 상대 비교용 지표
6. 최적 실행 계획 선택
- 모든 가능한 계획 중 총 예상 비용이 가장 낮은 것을 선택
두두두두..