[Real MySQL 1권] 옵티마이저

 

 

1) 옵티마이저란?

 

- MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다
  이런 다양한 방법 중에서 어떤 방법이 최적이고, 최소의 비용이 소모될지 결정해야 한다

 

-MySQL에서는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며,
  그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다.
  MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당한다.

 

 

2) 실행 계획이란?

- MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있는데,
  EXPLAIN 명령의 결과에는 상당히 많은 정보가 출력된다.
  실행 계획에 표시되는 내용을 제대로 이해하려면 MySQL 서버 옵티마이저가 실행하는 최적화에 대해

  어느 정도 지식을 갖추고 있어야 한다

 

 

3) MySQL 쿼리 실행 절차

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

1. 사용자로부터 요청된 SQL 문장을 잘개 쪼개서 MySQL 서버가 이해할 수 있는 수준으로

     분리(파스 트리)한다.

 

2.  SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고, 

     어떤 인덱스를 이용해 테이블을 읽을지 선택한다.

 

3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 

    스토리지 엔진으로부터 데이터를 가져온다.

 

- 첫 번째 단계를 SQL 파싱이라고 하며, MySQL 서버의 "SQL 파서"라는 모듈로 처리한다.
   SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다.
   또한 이 단계에서 "SQL 파스 트리"가 만들어진다.
   MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행한다.

- 두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리한다.

 

1. 불필요한 조건 제거 및 복잡한 연산의 단순화
2. 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
3. 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
4. 가져온 레코드들을 임시 테이블에 넣고 다시 한 번 가공해야 하는지 결정

- 물론 이 밖에도 수많은 처리를 하지만 대표적으로 이러한 작업을 들 수 있다.
  두 번째 단계는 "최적화 및 실행 계획 수립" 단계이며, MySQL 서버의 "옵티마이저"에서 처리한다.
  또한, 두 번째 단계가 완료되면 쿼리의 "실행 계획"이 만들어진다.

 

- 세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고,
   MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
 

-  첫 번째 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하며,

   세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

4) 옵티마이저의 종류

- 옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다
   옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화 방법과
   예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법으로 크게 나눌 수 있다

 

(1) 규칙 기반 최적화

 

-  규칙 기반 최적화는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 

    옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미합니다.
   

-  이 방식에서는 통계 정보(테이블의 레코드 건수나 칼럼값의 분포도)를 조사하지 않고

   실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어낸다.
   

- 하지만 사용자의 데이터는 분포도가 매우 다양하기 때문에

  규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않는다.

(2) 비용 기반 최적화

 

- 비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고,
  각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.
  이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.

- 현재는 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지다.