옵티마이저 Optimizer

1. 쿼리 실행 절차

① SQL Parser: 요청된 SQL문장을 MySQL 서버가 이해할 수 있는 수준으로 분리하고 문법상 문제가 없는지 확인한다. 
② Optimiser: SQL 파스 트리를 확인하면서 어떤 테이블에서 어떤 인덱스를 이용해 테이블을 읽을지 실행계획을 세운다.
③ 실행계획대로 스토리지 엔진으로부터 데이터를 가져온다.

 

2. 옵티마이저 역할

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

 

3. 비용 기반 최적화 옵티마이저

옵티마이저는 DBMS 두뇌와 같은 역할을 하며 MySQL의 옵티마이저는 비용 기반 최적화 방식을 채택하고 있다. 비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획 별 비용을 산출한다. 이렇게 산출된 실행 방법 별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 실행계획을 세우게 된다. 하지만 경우의 수가 너무 많은 경우 모든 경우의 비용 산정을 하기 어렵기 때문에 항상 최고의 실행계획을 세우지 못할 수 있다. 그래서 쿼리 튜닝이 필요하게 된다. 

 

4. 기본 데이터 처리

MySQL는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있다. 이러한 기본적인 가공을 위해 MySQL이 어떤 알고리즘을 사용하는지 간단히 살펴보자. 

 

4.1 리드 어헤드

대부분 DBMS는 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. InnoDB 스토리지 엔진은 풀 테이블 스캔이나 풀 인덱스 스캔이 시작되면 포그라운드 스레드 (Foreground Thread, 클라이언트 스레드)에 의해 innodb_read_ahead_threshold  시스템 변수값만큼 연속된 데이터 페이지를 읽고 난 후 읽기 작업을 백그라운드 스레드(Background Thread)로 넘기는데 리드 어헤드(Read Ahead) 작업이 시작된다. 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다. 백그라운드 스레드는 대량의 페이지(최대 64개)를 읽으면서 계속 그 수를 증가시키고 읽은 페이지를 InnoDB 버퍼 풀에 저장한다. 이후 포그라운드 스레드는 백그라운드 스레드가 미리 버퍼 풀에 준비한 데이터를 가져다 사용하므로 쿼리 처리 속도가 향상된다. 
일반적인 상황에서는 innodb_read_ahead_threshold  시스템 변수의 디폴트 설정값으로 충분하지만 데이터 웨어하우스용으로 MySQL을 사용한다면 이 옵션값을 낮추어 더 빨리 리드 어헤드가 시작되게 유도할 수 있다. 

 

4.2 풀 테이블 스캔 (Full Table Scan)

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미한다. MySQL 옵티마이저는 다음과 같은 조건이 될 때 주로 풀 테이블 스캔을 선택한다.

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

일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다.  

4.3  풀 인덱스 스캔 (Full Index Scan)

풀 인덱스 스캔은 인덱스를 처음부터 끝까지 스캔하는 것을 의미한다. 다음 쿼리를 보자.

-- SELECT (1)
SELECT COUNT(*) FROM employees;
  
  
-- SELECT (2)
SELECT * FROM employees;

SELECT (1) 쿼리는 아무런 조건 없이 employees 테이블의 레코드 건수를 조회하고 있으므로 풀 테이블 스캔을 할 것처럼 보인다. 하지만 풀 인덱스 스캔을 하게 될 가능성이 높다. 옵티마이저는 단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문이다. 일반적으로 인덱스는 테이블보다 용량이 작아서 훨씬 빠른 처리가 가능하다. 
SELECT (2) 쿼리는 인덱스를 이용하는 것보다 테이블에 접근해서 순차 I/O를 하는 것이 훨씬 비용이 적게 들기 때문에 풀 테이블 스캔으로 처리한다.

 

5. 병렬 처리

MySQL 8.0 버전부터는 용도가 한정돼 있긴 하지만 쿼리의 병렬 처리가 가능해졌다. 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미한다. (여러 쿼리를 각각의 스레드가 처리하는 것은 처음부터 가능했었다.)
innodb_parallel_read_threads 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있다. 아직 MySQL 서버에는 병렬로 처리하게 하는 힌트나 옵션은 없다. 오직 WHERE 조건 없이 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다. 아래 쿼리를 살펴보자.

SET SESSION innodb_parallel_read_threads=1; -- 병렬 스레드 1개 설정
SELECT COUNT(*) FROM salaries; -- 0.32 초

SET SESSION innodb_parallel_read_threads=2; -- 병렬 스레드 2개 설정
SELECT COUNT(*) FROM salaries; -- 0.20 초

SET SESSION innodb_parallel_read_threads=4; -- 병렬 스레드 4개 설정
SELECT COUNT(*) FROM salaries; -- 0.18 초

SET SESSION innodb_parallel_read_threads=8; -- 병렬 스레드 8개 설정
SELECT COUNT(*) FROM salaries; -- 0.13 초

위 쿼리 실행은 참고만 하자. 실제로 실행해보면 이미 가져왔는 데이터는 버퍼 영역에 있을 가능성이 높아 속도가 빨라지는 경향이 있다.  병렬 스레드 개수를 실행하는 컴퓨터의 CPU 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.