[MySQL] 옵티마이저 - 개요

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적인 방법은 다양합니다. 다양한 방법 중 어떤 방법이 최적이고 최소 비용이 소모될지 결정해야 합니다. MySQL은 쿼리를 최적으로 실행하기 위해 테이블의 데이터 통계 정보를 참조하여 최적의 실행 계획을 수립합니다. 이러한 기능은 옵티마이저가 수행합니다. 

쿼리 수행 절차

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

1. SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리
2. SQL 파싱 정보를 확인하면서 어떤 테이블을 읽고 어떤 인덱스를 사용할지 선택
3. 테이블 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

첫 번째 단계를 "SQL 파싱"이라 하며, SQL 파서가 실행합니다. 이 단계에서 SQL 문장의 문법적 오류도 걸러집니다. 파싱 결과 "SQL 파스 트리"가 만들어집니다.

 

두 번째 단계는 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리합니다.

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

즉, 최적화 및 실행 계획 수립을 합니다.

 

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

 

1, 2 단계는 MySQL 엔진에서 처리하고, 3 단계는 MySQL 엔진과 스토리지 엔진이 동시에 처리합니다.

옵티마이저의 종류

규칙 기반 최적화

대상 테이블의 레코드 건수나 선택도를 고려하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립하는 방법입니다. 테이블의 정보를 참고하지 않기 때문에 동일한 쿼리에 대해 동일한 실행 계획이 수립됩니다.

비용 기반 최적화

쿼리를 처리하기 위한 여러 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출합니다. 이 중 최소 비용인 실행 계획으로 쿼리를 실행합니다. 

 

과거에는 테이블 통계정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠습니다. 하지만 현재 대분의 DBMS는 비용 기반 옵티마이저를 채택하고 있습니다.