MySql 옵티마이져 실행 개요

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

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

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

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

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

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

옵티마이저의종류

1. 비용 기반 최적화(Cost-based optimizer, CBO)
2. 규칙 기반 최적화 방법(Rule-based optimizer, RBO)

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

규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지이다.

통계정보

MySQL에서 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부이다. 오라클과 같은 DBMS에서는 통계 정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다. 하지만 MySQL에서 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다. 하지만 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 "ANALYZE" 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다. 특히 이런 현상은 레코드 건수가 얼마 되지 않는 개발용 MySQL 서버에서 자주 발생한다.

MEMORY 테이블은 별도로 통계 정보가 없으며, MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 다음과 같이 확인할 수 있다. ANALYZE 명령은 인덱스 키값의 분포도(선택도)만 업데이트하며, 전체 테이블의 건수는 테이블의 전체 페이지 수를 이용해 예측한다.

MyISAM 테이블의 ANALYZE는 정확한 키값 분포도를 위해 인덱스 전체를 스캔하므로 많은 시간이 소요된다. 이와는 달리 InnoDB 테이블은 인덱스 페이지 중에서 8개 정도만 랜덤하게 선택해서 분석하고 그 결과를 인덱스의 통계 정보로 갱신한다.

https://12bme.tistory.com/160 [mysql 옵티마지이져원리 부터 실행계획까지]