옵티마이저

• 옵티마이저는 쿼리의 실행 계획을 수립한다.
• 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.

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

옵티마이저의 종류

옵티마이저란

• 테이터베이스 서버에서 두뇌와 같은 역할을 담당한다.
• 크게 다음 두 가지로 나눌 수 있다.
  • 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, RBO)으로 크게 나눌 수 있다.
  • 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법

규칙 기반 최적화

• 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방법
• 통계 정보(테이블의 레코드 건수나 칼럼값의 분포도)를 조사하지 않고 실행 계획이 수립된다.
  • 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다.
• 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이다.
• 사용자의 데이터는 분포도가 매우 다양하기 때문에 규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않는다.

비용 기반 최적화

• 다음과 같이 처리하는 방법
  1. 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만든다.
  2. 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.
  3. 이렇게 산출된 실행 방법 별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.
• 현재 MySQL을 포함한 대부분의 DBMS가 비용 기반의 옵티마이저를 채택하고 있다.

Reference

참고 서적

📔 Real MySQL 8.0