SQL 파싱과 최적화

SQL 최적화의 ‘최적화'란 무엇을 대상으로하며 어떤 과정을 거치는지 알아보자.

 

 

구조적, 집합적, 선언적 질의언어

• SQL (Structured Query Language) : SQL is a set-based, declarative query language, not an imperative language such as C or BASIC.
• 즉, 기본적으로 구조적이고 집합적이고 선언적인 질의 언어이다. 원하는 결과 집합을 구조적, 집합적으로 선언한다.
• 하지만 그 결과 집합을 만드는 과정은 절차적일 수 밖에 없다. 즉, 프로시저(절차기술)가 필요한데, 그런 프로시저를 만들어내는 DBMS 내부 엔진이 SQL 옵티마이저이다.
• DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 SQL 최적화라고 한다.

---

SQL 최적화

• SQL을 실행하기 전에 SQL 파싱과 최적화 과정을 거친다. 앞으로는 파싱과정을 포함하여 최적화라고 표현하겠다.

1. SQL 파싱

• 사용자로부터 SQL을 전달받으면 가장먼저 Parser가 파싱진행
  • 파싱 트리 생성
  • Syntax 체크 : 문법적 오류가 없는지 확인. (누락된 키워드, 사용할 수 없는 키워드 등)
  • Semantic 체크 : 의미상 오류가 없는지 확인. (테이블에 존재하지 않는 컬럼사용 / 오브젝트에 권한 등)

2. SQL 최적화

• 역할 : 옵티마이저.
• 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.

3. 로우 소스 생성

• 역할 : 로우 소스 생성기.
• 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계.

---

옵티마이저

옵티마이저의 최적화 단계

1. 쿼리를 수행하는 데 후보군이 될만한 실행계획을 찾아냄.
2. 덱이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행계획의 예상비용을 산정함.
3. 최저 비용을 나타내는 실행계획을 선택함.

(위 과정은 비용기반 옵티마이저의 SQL 최적화 과정이다)

 

옵티마이저 종류

• 1) 규칙기반 옵티마이저
  • Rule-Based Optimizer (RBO), 휴리스틱 옵티마이저라 불린다.
  • 미리 정해놓은 규칙 (액세스 경로별 우선순위) 으로 액세스 경로 평가하여 실행계획 선택한다.
  • 인덱스 구조, 연산자, 조건절 형태가 순위 결정짓는 주 요인이다.
• 2) 비용기반 옵티마이저
  • Cost-Based Optimizer (CBO), 비용을 기반으로 최적화 수행.
  • 비용(Cost) 이란 쿼리수행 시 소요되는 일량, 시간을 뜻하며, 이는 예상치 이다.
  • 미리 구해둔 테이블, 인덱스에 대한 통계정보를 기초로 오퍼레이션 단계별 예상비용 산정, 이를 합한 총 비용이 가장 낮은 실행계획을 선택.
  • 오브젝트 통계 항목 : 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이 (Height), 클러스터링 팩터, 하드웨어 특성을 반영한 시스템 정보(CPU 속도, 디스크 I/O 속도 등)

💡  스스로 학습하는 옵티마이저 (Self-Learning Optimizer)
전통적 옵티마이저는 오브젝트 통계, 시스템 통계로 부터 산정한 "예상" 비용만으로 실행계획을 수립했다.앞으로는 예상치와 런타임 수행 결과를 비교하고, 예상치가 빗나갔을 때 실행계획을 조정하는 옵티마이저로 발전할 것이다.최근에 발표된 각 DBMS 버전은 이미 이런 기능을 포함하고 있다.

---

옵티마이저 힌트

1. 힌트 안에 인자를 나열할 땐 콤마를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안된다.
2. 테이블을 지정할 때 스키마명까지 명시하면 안된다.
3. FROM 절 테이블명 옆에 alias를 지정했다면, 힌트에도 반드시 사용해야한다. (From 절에는 명시하고 힌트에는 테이블명을 사용한다면 무시된다.)

Postgresql 에서의 힌트

• PostgreSQL은 Oracle 과 같은 Hint를 제공하지 않는다.
• 사용자가 Oracle의 Hint 처럼 인덱스를 지정하거나, 조인순서 등을 지정할 수 있는 기능인 PG_HINT_PLAN 기능으로 Oracle의 hint를 대신한다.
• 차이는?
  • Oracle의 Hint는 옵티마이저에게 특정한 액세스 방법과 타입을 지정하는 Directive가 된다. 옵티마이저는 부정확한 문법을 사용하거나 의미론적으로 부정확할 때 Hint를 무시할 수 있다.
  • PostgreSQL의 PG_HINT_PLAN는 Plan Tree 자체를 변경하는 기법으로 Optimizer는 지정한PG_HINT_PLAN를 무시할 수 없다.
• PG_HINT_PLAN이 제공하는 Hint들

• 힌트를 빈틈없이 줄 것인지 옵티마이저의 판단에 따를것인지는 선택. 즉 어느정도만큼 옵티마이저에게 자율성을 줄 것이냐는 기업의 서비스에 따라 다르다.