개요
옵티마이저란?
- 옵티마이저는 데이터베이스에서 SQL 쿼리를 실행할 때, 쿼리의 최적 실행 계획을 자동으로 결정하는 역할을 합니다. 옵티마이저는 여러 가지 가능한 실행 계획 중에서 가장 비용이 적고 효율적인 방법을 선택한다.
- 여행 계획을 짤 때, 어떻게 이동할 때 교통비가 덜 들고 어떻게 할 때 가성비가 좋은지를 생각하는 것처럼, MySQL에서도 쿼리를 최적으로 실행하는 옵티마이저가 존재.
개요에서도 “쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분”이라고…
- 개발자가 모든 쿼리를 작성할 때, 일일이 모든 경우의 수(인덱스… 레코드의 건수.. 조인의 횟수…)를 고려해 작성할 수 없음 → 엔진에 내장된 옵티마이저가 알아서 판단해서 최적의 경우를 나타내는 것.
힌트란?
- 힌트는 SQL 쿼리 작성자가 옵티마이저에 특정 실행 계획을 강제로 사용하도록 지시하는 방법.
옵티마이저가 자동으로 선택하는 계획이 항상 최선이 아닐 수 있기 때문에, 힌트를 사용하여 실행 계획에 영향을 줄 수 있습니다. 힌트는 쿼리 안에 주석 형식으로 삽입되며, 옵티마이저가 힌트를 존중하여 지정된 방법을 우선적으로 고려한다.
- 내장된 옵티마이저가 항상 정답을 뱉어내는 것은 아님. 그렇기에 실행 계획을 분석하고, 더 좋은 성능에 대해 개발자가 떠오른 게 있다면 힌트를 이용하는 것.
쿼리 실행 절차
- MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계.
- 사용자가 요청한 SQL을 잘게 쪼개서 MySQL서버가 이해할 수 있는 수준으로 분리(파스트리 라고 함.)
- SQL의 파싱 정보(파스트리)를 확인하며 어떤 테이블부터 읽고 어떤 인덱스를 이용해 데이터를 조회할지 선택
- 두 번째 단게에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴
- SQL문장이 잘못됐다면 첫 번째 단계(SQL 파싱)에서 걸러짐, 걸러지지 않았다면 파스 트리 생성
- MySQL 서버는 SQL 파스 트리를 이용해 쿼리를 실행
- 두 번째 단계는 파스트리를 참조하며 아래의 내용을 처리
- 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블 조인의 경우 어떤 순서로 테이블을 읽을지 결정(작은 테이블이나 조건문과 관련)
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
- 두 번째 단계는 “최적화 및 실행 계획 수립” 단계이며, 옵티마이저에서 처리. → 실행 계획이 만들어짐
- 세 번째 단계는 만들어진 실행 계획을 통해 스토리지 엔진에 레코드를 읽어오도록 요청
옵티마이저 종류
- 비용 기반 최적화와 규칙 기반 최적화 방법으로 나뉨
- 규칙 기반 최적화는 내장된 우선순위만을 고집함(레코드 건수나 선택도를 고려하지 않음)
- 비용 기반 최적화는 각 작업의 비용(부하) 정보를 예측된 통계정보를 이용해 실행 계획별 비용을 산출