Real MySQL - 옵티마이저란 무엇인가 ? (+ 기본 데이터 처리)

옵티마이저(Optimizer)

MySQL에서 쿼리의 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다. 그런 방법 중에서 어떤 방법이 최적이고 최소의 비용이 소모될 지 결정해야 한다.

 

쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지를 참조하고, 데이터를 기반으로 최적의 실행 계획을 수립해주는 것이 옵티마이저(Optimizer)이다.

 

어떤 DBMS든 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져있다. 하지만 실행 계획을 이해해야 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

 

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.

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

첫 번째 단계는 SQL 파싱(Parsing)이라고 한다. MySQL Server의 SQL 파서라는 모듈로 처리한다. SQL 문법이 잘못되었다면 이 단계에서 걸러진다. 또한 해당 단계에서 SQL 파스 트리가 만들어진다. 

 

MySQL Server가 실제로 쿼리를 실행할 때는 SQL 문장이 아니라 SQL 파스 트리를 사용해서 쿼리를 실행한다.

 

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조해서 다음의 내용을 처리한다.

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

물론 이 밖에도 수많은 처리를 한다. 해당 단계는 최적화 및 실행 계획 수립 단계이며, MySQL Server의 Optimizer에서 처리한다.

 

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

 

첫 번째, 두 번째 단계는 주로 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 함께 처리한다.

 

옵티마이저의 종류

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다. 옵티마이저는 크게 두 가지로 나눌 수 있다.

 

규칙 기반 최적화

• 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 Optimizer에 내장된 우선순위에 따라 실행 계획을 수립
• 통계 정보를 참조하지 않기 때문에 같은 쿼리에 대해서 같은 실행 계획을 만든다.
• 초기 버전의 오라클 DBMS에서 많이 사용했다.
• 사용자의 데이터는 분포도가 매우 다양하기 때문에 현재로써는 많은 DBMS에서 사용하지 않는다.

비용 기반 최적화

• 쿼리를 처리하기 위한 여러 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 통계 정보를 이용해서 각 실행 계획의 비용을 산출한다.
• 산출된 정보를 이용해서 가장 적은 비용이 드는 실행 계획을 선택해서 쿼리를 실행한다.
• 현재는 대부분의 RDBMS가 채택하고 있으며, MySQL 역시 마찬가지다.

 

기본 데이터 처리

옵티마이저는 인덱스를 사용하지 않는 풀 테이블 스캔을 할 수도 있고, 인덱스를 사용해서 데이터를 조회할 수도 있다.

 

MySQL 옵티마이저는 다음의 조건에 해당할 때 주로 풀 테이블 스캔을 선택한다.

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
• WHERE 절이나 ON 절에 인덱스를 이요할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔(Range Scan)을 할 수 있어도 조건에 일치하는 레코드가 너무 많은 경우

대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.

 

많은 사람들이 MySQL은 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어 오는 것으로 생각한다. 하지만 이는 MyISAM 스토리지 엔진에는 맞는 이야기지만 InnoDB에서는 틀린 말이다.

 

InnoDB 스토리지 엔진에서는 특정 테이블의 연속된 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead)가 자동으로 실행된다. 리드 어헤드는 앞으로 어떤 영역의 데이터가 필요할 지 예측해서 요청이 오기 전에 미리 디스크에 실어서 InnoDB의 버퍼 풀에 가져다 두는 것을 말한다.

 

즉, InnoDB에서 데이터를 조회할 때 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread)가 페이지를 읽어오지만 특정 시점부터는 읽기 작업을 백그라운드 스레드가 실행한다. 백그라운드 스레드는 한 번에 4개 또는 8개씩 페이지를 읽다가 단위를 증가시키고 최대 64개의 페이지를 읽어온다. 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.

 

MySQL 서버에서는 innodb_read_ahead_threshold시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할 지 임계값을 설정할 수 있다.  Default로 사용해도 상관없지만 MySQL를 데이터 웨어하우스로 사용하고 있다면 해당 옵션을 더 낮게 설정하는 것이 좋다.

 

리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용된다. 풀 인덱스 스캔은 실제 데이터가 아니라 인덱스를 처음부터 끝까지 스캔하는 것을 말한다. 아래의 쿼리를 예로 보자.

SELECT Count(*) FROM employees;​

쿼리는 employyes의 레코드 건수를 조회하므로 풀 테이블 스캔을 할 것처럼 보인다. 하지만 옵티마이저는 풀 인덱스 스캔을 선택할 가능성이 높다. 왜냐하면 테이블 데이터보다 인덱스가 용량이 작기 때문에 인덱스만 조회하면 훨씬 빠른 처리가 가능하기 때문이다.

 

---

Reference

• https://willseungh0.tistory.com/161
• https://zangzangs.tistory.com/103