어떤 DBMS 든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있으며, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어려운 부분이다. 하지만 그 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더욱 최적화된 방법으로 실행 계획을 수립할 수 있도록 유도할 수 있다.
MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.
쿼리문 세분화(SQL Parsing) : 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
SQL parser 라는 모듈로 처리하며 SQL 파스 트리가 만들어진다.최적화 및 실행 계획 수립 : SQL 의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
실행 : 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
첫번째 단계와 두번째 단계는 거의 MySQL 엔진에서 처리하며, 세번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.
옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당하고 있다. 옵티마이저는 현재 대부분의 DBMS 가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 오라클에서 많이 사용됐던 규칙 기반 최적화(Rule-based optimizer, RBO) 방법으로 크게 나눠볼 수 있다.
규칙 기반 최적화 는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다. 이 방식에서는 통계 정보(테이블의 레코드 건수나 칼럼 값의 분포도) 를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다. 하지만 규칙 기반 최적화는 이미 오래 전부터 많은 DBMS 에서 거의 지원되지 않거나 업데이트되지 않은 상태 그대로 남아있는 것이 현실이다.비용 기반 최적화 는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 산출된 각 실행 방법 별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행한다.규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS 가 비용 기반 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지다.
비용 기반 최적화에서 가장 중요한 것은 통계 정보다. 통계 정보가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행해 버릴 수 있기 때문이다.
MySQL 또한 다른 DBMS 와 같이 비용 기반의 최적화를 사용하지만 다른 DBMS 보다 통계 정보는 그리 다양하지 않다. 기본적으로 MySQL 에서 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부다. 오라클과 같은 DBMS 에서는 통계 정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다. 하지만 MySQL 에서 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다. 하지만 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다.