MySQL 옵티마이저

 

MySQL 옵티마이저

Real MySQL 서적에 나오는 일부분으로 조만간 알라딘으로 달려간다!!!!
데이터베이스의 두뇌와 같은 역할로 쿼리의 실행 계획을 생성하는 관계형 데이터베이스 내부 핵심 엔진이다. 쿼리의 결과는 같지만 우리가 모르게 내부적으로 결과를 만들어내는 방법은 매우 다양하다. 옵티마이저는 이러한 다양한 방법 중 어떤 방법이 최적이고 최소한의 비용으로 실행될지 결정한다. 쿼리가 최적으로 실행되기 위해 테이블별로 어떤 데이터가 어떠한 형태로 저장돼있는지를 참조하고 참조한 데이터를 기반으로 최적의 실행 계획을 수립한다.

 

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3단계로 구분된다.

1. SQL 파싱(파스 트리)
   SQL 쿼리문을 쪼개어 MySQL Server가 이해할 수 있는 수준으로 파싱 하며 파스 트리가 만들어진다. 파싱 과정은 Server 내부에 SQL 파서라는 모듈로 처리하며 쿼리문이 올바르지 않다면 이 단계에서 걸러진다.
   
   
2. 파스 트리 참조 
   최적화 및 실행 계획 수립 단계이며 1단계에서 만들어진 파스 트리를 참조해 다음 내용을 수행하며 참조 과정은 Server 내부에 옵티마이저가 처리한다.
   
   2-1. 불필요한 조건 제거와 복잡한 연산의 단순화
   2-2. 여러 테이블이 조인되어 있는 경우 어떠한 순서로 테이블을 읽을지 결정
   2-3. 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
3. 스토리지 엔진(Storage Engine)
   2단계에서 수립된 계획대로 스토리지 엔진에서 레코드를 읽어오도록 요청하고 MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
   
   1, 2단계는 주로 MySQL Server 엔진에 의해서 수행되며 3단계는 Server 엔진과 스토리지 엔진이 같이 처리한다.
   
   

종류

옵티마이저는 크게 규칙 기반 최적화와 비용 기반 최적화로 나뉜다.

 

규칙 기반 최적화

• 테이블의 레코드 수나 선택도 등을 고려하지 않으며 옵티마이저 내부에 내장된 우선 순위에 따라 실행 계획을 수립
• 통계 정보를 참조하지 않으므로 같은 쿼리에 대해서 동일한 실행 계획을 생성
• 데이터의 분포도는 매우 다양하므로 현재는 많은 관계형 데이터베이스에서 사용하지않음

 

비용 기반 최적화

• 쿼리문 처리를 위해 여러 방법을 만들며 각 작업의 비용의 정도와 대상 테이블의 통계 정보를 이용해 각 실행 계획의 비용을 산출
• 산출된 정보 중 가장 비용이 적은 실행 계획으로 쿼리문을 실행
• 대부분의 관계형 데이터베이스가 비용 기반 최적화를 채택

 

처리

인덱스를 하지 않을 경우 풀 스캔을 할 수도 있고 인덱스를 사용해 조회할 수도 있다. 

 

풀 스캔 조건

• 테이블의 레코드 수가 너무 적어 인덱스를 통해 조회하는 거보다 풀 스캔이 빠를 경우
• WHERE 절이나 OR 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 레인지 스캔을 할 수 있어도 조건에 일치하는 레코드가 너무 많은 경우

 

참고 자료

https://jaehoney.tistory.com/193

https://willseungh0.tistory.com/161

https://code-lab1.tistory.com/137

https://zangzangs.tistory.com/103