[Real MySQL] 9. 옵티마이저와 힌트

옵티마이저

• 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.
• MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다. MySQL의 옵티마이저는 쿼리를 최적으로 실행하기 위한 실행 계획을 수립하는 작업을 진행한다.
• EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다. 그리고 실행 계획을 제대로 이해하려면 MySQL 서버 옵티마이저가 실행하는 최적화에 대해 어느 정도 지식을 갖추고 있어야 한다. 

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 다음과 같다.

1. SQL 파싱: 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있도록 파싱하여 트리 형태(파스 트리)로 만든다.
   • SQL 파서가 담당한다.
   • SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다.
   • 이 단계를 통해 SQL 파스 트리가 만들어진다.
2. 최적화 및 실행 계획 수립: SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고, 어떤 인덱스를 사용할지 등을 선택한다.
   • 옵티마이저가 담당한다.
   • 이 단계를 통해 실행 계획이 만들어진다.
3. 2번 단계에서 결정된 테이블 읽기 순서나 선택된 인덱스를 통해 스토리지 엔진으로부터 데이터를 읽는다.
   • 실행 계획을 토대로 스토리지 엔진을 통해 레코드를 읽어온다.
   • MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

1 ~ 2번 단계는 거의 MySQL 엔진에서 처리되며, 3번 단계는 MySQL 엔진과 스토리지 엔진에서 처리된다.

옵티마이저의 종류 (규칙 기반 / 비용 기반 최적화)

옵티마이저는 다음과 같이 2가지 종류로 나뉜다. 현재 대부분의 DBMS는 비용 기반 최적화 방법을 사용한다.

• 규칙 기반 최적화
  
  • 통계 정보(테이블의 레코드 수나 칼럼값의 분포도 등)를 조사하지 않고 실행 계획을 수립한다.
  • 따라서 같은 쿼리에 대해 거의 항상 같은 실행 방법이 만들어진다.
• 비용 기반 최적화
  • 쿼리를 처리하기 위한 여러 가지 방법을 만들고, 통계 정보 등을 이용해 실행 계획별 비용을 산출한다. 그리고 산출된 비용이 최소인 처리 방법을 선택해 쿼리를 실행한다.

풀 테이블 스캔

풀 테이블 스캔을 하는 경우

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청 작업을 처리하는 것을 의미한다. 그러면 옵티마이저는 어느 경우에 풀 테이블 스캔을 할까?

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 것이 더 빠른 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우이다.)
• WHERE 절이나 ON 절에 인덱스를 활용할 수 있는 조건이 없는 경우
• 인덱스 레인지 스캔을 할 수 있는 쿼리라고 하더라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (이때 인덱스의 B-Tree를 샘플링해서 조사한 통계 정보를 활용한다.)

풀 테이블 스캔 시 디스크 I/O 작업을 줄이기 위한 방법

풀 테이블 스캔은 상당히 많은 디스크 읽기 작업이 필요하다. 따라서 InnoDB는 풀 테이블 스캔을 실행할 때 한 번에 여러 개의 페이지를 읽어오는 기능을 내장하고 있다.

• InnoDB에서는 특정 테이블의 연속된 데이터 페이지가 일정 횟수 이상 읽히면, 백그라운드 스레드에 의해 리드 어헤드 작업이 시작된다. 이때 리드 어헤드란, 어떤 영역의 데이터가 앞으로 필요해질 것이라 예측해서 요청이 오기 전에 미리 디스크에서 읽어서 InnoDB 버퍼 풀에 가져다 두는 것을 의미한다.
• 즉, 처음에 풀 테이블 스캔이 시작되면 처음 몇 개의 데이터는 포그라운드 스레드에 의해 페이지 읽기가 실행된다. 그러나 특정 시점부터는 읽기 작업이 백그라운드 스레드로 넘어간다. 그리고 백그라운드 스레드는 리드 어헤드를 통해 한 번에 여러 개의 페이지를 디스크로부터 읽어와서 버퍼 풀에 저장해 둔다. 그러면 포그라운드 스레드는 버퍼 풀에 있는 데이터를 가져다 사용하기만 하면 되므로 쿼리가 빠르게 처리된다. 

힌트

힌트는 옵티마이저의 실행 계획을 조작하는 구문을 의미한다.

 

MySQL의 버전이 업그레이드 되면서 쿼리의 실행 계획 최적화가 많이 성숙해지고 있다. 하지만 여전히 MySQL 서버가 비즈니스를 100% 이해하지 못하고 부족한 실행 계획을 수립할 때가 있을 수 있다. 이런 경우에는 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하다. 일반적인 RDBMS에서는 이런 목적으로 힌트가 제공되며, MySQL에서도 다양한 옵티마이저 힌트를 제공한다.

 

MySQL 서버에서 사용 가능한 쿼리 힌트는 다음 2가지가 있다.

• 인덱스 힌트
• 옵티마이저 힌트

---

Reference

• 위키북스, Real MySQL 8.0