MSSQL - Optimizer(옵티마이저)

MSSQL에서 가장 중요한 옵티마이저에 대해서 이야기하려고합니다. 

MSSQL 옵티마이저란?

MSSQL 옵티마이저(Query Optimizer)는 Microsoft SQL Server의 핵심 구성 요소 중 하나로, 사용자가 작성한 SQL 쿼리를 최적의 실행 계획(Execution Plan) 으로 변환하여 데이터베이스에 대한 효율적인 접근을 가능하게 합니다. 간단히 말해, “어떤 방법으로 쿼리를 실행할지 결정하는 뇌” 역할을 수행합니다.

---

MSSQL 옵티마이저의 동작 원리

SQL Server에서 쿼리가 실행될 때, 옵티마이저는 다음과 같은 과정을 거칩니다:

1. 쿼리 파싱 (Parsing):
   SQL 문이 구문 분석되어 문법적 오류가 없는지 확인한 후, 내부 표현(파스 트리; Parse Tree)로 변환됩니다.
2. 쿼리 재작성 (Query Rewriting):
   쿼리의 논리적 표현을 단순화하고, 불필요한 중복이나 복잡한 서브쿼리 등을 최적화할 수 있도록 변환합니다.
3. 실행 계획 생성 (Plan Generation):
   여러 후보 실행 계획(Candidate Plans)을 생성합니다. 이때 옵티마이저는 다양한 조인 방법(Nested Loops, Merge Join, Hash Join 등)과 인덱스 활용 여부 등을 고려합니다.
4. 비용 추정 (Cost Estimation):
   각 후보 실행 계획에 대해 I/O, CPU 사용량, 메모리 소비 등 다양한 요소를 기반으로 “비용”을 추정합니다. 여기서 사용되는 통계 정보(Statistics)는 테이블의 데이터 분포, 인덱스 상태 등이 포함됩니다.
5. 최적 계획 선택 (Plan Selection):
   추정 비용이 가장 낮은 실행 계획을 선택하여 실행하게 됩니다. 이 실행 계획은 쿼리의 성능에 큰 영향을 미치며, 쿼리 실행 전 캐시에도 저장되어 후속 실행 시 재사용됩니다.

---

MSSQL 옵티마이저의 주요 기능

• 비용 기반 최적화 (Cost-Based Optimization):
  옵티마이저는 각 실행 계획의 예상 비용을 계산하여 최적의 경로를 선택합니다.
• 다양한 조인 전략:
  데이터의 특성에 따라 최적의 조인 알고리즘(예: Nested Loops, Hash Join, Merge Join)을 자동으로 선택합니다.
• 인덱스 활용:
  테이블의 인덱스와 통계 정보를 분석하여, 인덱스를 효과적으로 사용하거나 인덱스 스캔/시크 방식 중 적합한 방법을 결정합니다.
• 동적 프로그래밍:
  복잡한 쿼리의 경우, 여러 부분 쿼리의 최적 실행 계획을 조합하는 방식으로 최종 실행 계획을 도출합니다.
• 실행 계획 캐싱:
  동일한 쿼리에 대해 이미 최적화된 실행 계획을 메모리에 저장해, 재실행 시 최적화 과정을 생략하고 빠른 응답을 제공합니다.

---

옵티마이저의 종류

1. RBO(Rule Based Optimizer) - 초창기 버전부터 제공함

• 기본적으로 15개의 순위가 매겨진 규칙이 있음
  -> 이를 기초로 해서 실행계획을 수립함
• SQL에 대한 실행계획이 하나 이상일 경우엔, 순위가 높은 규칙을 이용
• 수립될 실행계획이 에측 가능하기 때문에 개발자가 원하는 처리 경로로
  유도하기가 쉬움

• 1위 : Rowid에 의한 1row(eqaul 검색)
• 2위 : 클러스터 조인에 의한 1row (클러스터화된 테이블간의 조인을 통해서 1건의 데이터를 찾는 것을 말한다)
• 3위 : Unique나 Primary key를 사용한 해시 클러스터 키에 의한 1row
• 4위 : 클러스터 키에 대해서 1건을 찾는것을 의미한다.
• 5위~7위 클러스터와 연관되어 있다.
• 5위 : 클러스터화된 테이블끼리의 조인
• 6위 : 해시 클러스터
• 7위 : 클러스터 키

• 8위 : 1개의 인덱스가 2개의 컬럼으로 이루어져있을 때
• 9위 : 단 1개의 컬럼으로 된 인덱스 사용
• 10위 : 영역이 정해져있을 때(equal, between, like)
• 11위 : 10번째보다 범위가 넓을때를 뜻한다.

• 12위 : 소트머지를 했을 때
• 13위 : 인덱스로 구성된 칼럼의 최대 또는 최소
• 14위 : 인덱스로 구성된 칼럼으로 order by
• 15위 : 인덱스 없이 전체 테이블 스캔 (FULL Table Scan)

 

궁금하신 점은 댓글이나 메일로 부탁드립니다. 감사합니다.