Chapter 09.01 옵티마이저(Optimizer)와 힌트

9.1 개요

• 실행 계획을 이해해야 실행 계획의 불합리한 부분을 찾아내고 더 최적화된 방법으로 실행 계획을 수립하도록 유도 가능하다.

• Def) 옵티마이저(Optimizer): MySQL은 쿼리를 최적으로 실행하기 위해 데이터가 어떤 분포로 저장돼있는지 통계 정보를 참조한다. 기본 데이터를 비교해서 최적의 실행 계획을 수립하는 작업을 말한다. 
• MySQL 서버를 포함한 대부분의 DBMS에서 이러한 기능을 담당한다. 
• 명령어 EXPLAIN으로 쿼리 실행 계획을 확인할 수 있다.

 

 

9.1.1 쿼리 실행 절차

 

1. SQL 파싱(parsing): 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리 (파스 트리)한다. SQL 파서라는 모듈로 처리한다. 
   • SQL 문법 검사
   • SQL 파스 트리가 만들어진다.
   • 서버는 파스 트리를 이용해서 쿼리를 실행하는 것이다. 
2. 최적화 및 실행 계획 수립: SQL의 파싱 정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해서 테이블을 읽을지 선택한다.
   • 불필요한 조건 제거, 복잡합 연산 단순화
   • 여러 테이블의 조인 있는 경우 어떤 순서로 읽는지 결정한다. 
   • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해서 사용할 인덱스를 결정한다. 
   • 가져온 래코드들을 임시 테이블에 넣고 다시 한번 가공해야하는 지 결정한다. 
3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해서 스토리지 엔진으로부터 데이터를 가져온다.
   • MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 한다. 

 

• 1, 2 단계는 거의 MySQL 엔진에서 처리하고 3단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다. 

 

 

 

9.1.2 옵티마이저의 종류

 

• 옵티마이저는 데이터베이스 서버에서 두뇌 역할을 한다. 

 

• 규칙 기반 최적화(Cost-based optimize, CBO)
  • 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선 순위에 따라서 실행 계획을 수립하는 방식을 의미한다. 
  • 통계 정보를 조사하지 않고 실행하므로 같은 쿼리는 항상 같은 실행 방법을 만들어낸다. 
  • 사용자 데이터는 분포도가 매우 다양하므로 이미 많은 DBMS에서 사용되지 않는다.
• 비용 기반 최적화(Rule based optimizer, RBO)
  • 쿼리를 처리하기 위한 여러 가지 방법을 만들고 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해서 실행 계획별 비용을 산출한다. 
  • 실행 방법별로 비용이 최소로 소용되는 방식을 선택해서 최종으로 쿼리실행한다. 
  • 현재 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다. 

 

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱 위키북스」