[MySQL] 옵티마이저

이 글을 쓰기 이전에 . .

잘못된 쿼리를 작성하면서 데이터 조회 속도가 매우 느리다는 것을 알게되었다.

(데이터를 가져오기 위해 다중 조인이 필요했고 복잡하다고 느꼈던 쿼리문에 대해서 동작에 이슈가 없는지만 체크했던게.. 😥)

성능 개선을 위한 여러가지 방법을 확인해봤지만 가장 쉽게 접근할 수 있었던 건 쿼리를 다시 작성하는 것이었다.

이 과정속에서 또 알아가는 건 내가 작성하는 쿼리의 실행계획을 확인할 수 있다는 것과 옵티마이저의 하는 일 이었다.

.

.

.

옵티마이저는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하고, 기본 데이터를 비교하여 최적의 실행 계획을 수립하는 작업을 담당한다.

종류

비용 기반 최적화(Cost-based optimizer, CBO)

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

이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택하여 쿼리를 실행한다.

 

규칙 기반 최적화(Rule-based optimizer, RBO)

대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립

그렇기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다.

하지만 사용자의 데이터는 분포도가 매우 다양하기 때문에 오래전부터 거의 사용되지 않는다.

 

 

그럼 옵티마이저는 언제 실행 계획을 작성할까?

MySQL 서버에서 쿼리가 실행되는 과정을 통해 옵티마이저가 실행되는 시점을 확인해보자.

 

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
이 과정은 SQL 파싱(Parsing)이라고 하며 MySQL 서버의 SQL 파서 모듈로 처리한다.
SQL 문장이 문법적으로 잘못 되었으면 이 단계에서 걸러진다!

2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
이 단계는 최적화 및 실행 계획 수립 단계로, 옵티마이저에서 처리하며 위에서 만들어진 SQL 파스트리를 참조하면서 다음과 같은 내용을 처리한다. (대표 작업만 작성)

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

이 단계가 완료되면 쿼리의 실행 계획이 만들어진다!

 

3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

 

 

실행 계획을 어떻게 확인할 수 있을까?

EXPLAIN 키워드는 쿼리문을 실행하는 방법에 대해 정보를 제공해준다.

• SELECT, DELETE, INSERT, UPDATE, REPLACE 상태를 모두 확인 가능하다.
  MySQL 8.0.19 이상부터는 테이블의 상태도 확인할 수 있다.

EXPLAIN my_query

-- JSON 포맷으로 확인하고 싶은 경우
EXPLAIN FORMAT=JSON my_query

간단하게 쿼리문 예시로 실행 계획을 확인해보자.

결과 값을 보면 실행 계획에 대한 정보를 제공하는데, 여기를 통해 더 자세하게 알아보는 게 좋을 것 같다.

 

참고

Real MySQL 8.0 - 백은빈, 이성욱