Real MySQL 8.0 9장 옵티마이저와 힌트

DBMS의 옵티마이저는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 등의 정보를 참고하여 최적의 실행 계획을 수립한다.

MySQL에서는 EXPLAIN 이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다.

1. 개요

쿼리 실행 절차

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

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 분리한다 (Parse Tree)
   • SQL 문장의 문법 오류를 검사
2. SQL의 Parse Tree를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
   • 불필요한 조건 제거 등 복잡한 연산의 단순화
   • 여러 테이블을 조인하는 경우 어떤 순서로 테이블을 읽을지 결정
   • 각 테이블에 사용된 조건과 인덱스 통계 정보를 바탕으로 사용할 인덱스 결정
3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

옵티마이저의 종류

크게 규칙 기반 최적화와 비용 기반 최적화 두 가지로 나눌 수 있다.

Rule-based optimizer는 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다.

Cost-based optimizer는 쿼리를 처리하기 위한 여러 방법을 만들고 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.

현재 대부분의 DBMS들은 Cost-based optimizer를 사용중이다.

기본 데이터 처리

풀 테이블 스캔과 풀 인덱스 스캔

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

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 테이블을 직접 읽는 것이 빠를 때
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있어도 조건 일치 레코드 건수가 너무 많은 경우 (인덱스 샘플링 조사 결과)

풀 테이블 스캔시 페이지 하나씩 읽지 않고 백그라운드 스레드가 한번에 여러 페이지를 읽어 버퍼 풀에 저장해준다. 이를 Read Ahead 라고 한다.

풀 인덱스 스캔도에서도 동일하게 작동한다.

SELECT COUNT(*) FROM employees;

해당 쿼리는 용량이 더 작은 인덱스를 풀 스캔한다.

병렬 처리

MySQL 8.0 이후부터 innodb_parallel_read_threads 시스템 변수로 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 설정할 수 있다. 물론 스레드 개수를 무한정 늘려 봤자 코어 개수가 정해져 있기 때문에 성능이 무한정 늘어나지는 않는다.

ORDER BY 처리

인덱스를 이용한 정렬, Filesort를 이용한 정렬 두 가지 방식이 존재한다.

• 인덱스
  • 장점 : 인덱스는 이미 정렬되어 있어서 조회가 매우 빠르다. 순서대로 읽기만 하면 된다.
  • 단점 : INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가, 삭제 작업이 필요해서 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다.
• Filesort
  • 장점 : 인덱스를 생성하지 않아도 되므로 INSERT, UPDATE, DELETE가 더 빠르다.
  • 단점 : 정렬 작업이 쿼리를 실행할 때 처리되므로 레코드 대상 건수가 많아질수록 응답 속도가 느려진다.

MySQL 서버에서 인덱스를 사용하지 않고 별도의 정렬 처리를 수행했는지 여부를 알 수 있다. 실행 계획의 Extra 컬럼에 “Using Filesort” 메시지가 표시된다.

소트 버퍼

정렬을 위한 별도의 메모리 공간을 Sort Buffer라고 한다. 정렬이 필요한 경우에만 할당된다.

정렬해야 할 레코드의 건수가 매우 많아서 소트 버퍼로만 해결할 수 없다면 어떻게 될까? 정렬해야 하는 레코드를 여러 조각으로 나눠서 정렬한다. 이 때 임시 저장 공간으로 디스크를 사용하게 된다. 이후 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬한다.

이러한 과정은 디스크 읽기, 쓰기를 유발한다.

Sort Buffer 크기를 너무 크게 만들면 메모리를 할당하는 과정에서 오버헤드가 발생할 수 있고, OOM이 발생하여 프로세스가 종료될 수도 있다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담아서 정렬할지, 정렬 기준이 되는 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스와 투 패스 2가지 정렬 모드로 나눌 수 있다.

투 패스 방식은 테이블을 두 번 읽어야 한다. 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간을 사용한다. 최신 버전은 일반적으로 싱글 패스 정렬 방식을 사용한다.

만약 레코드의 크기가 max_length_for_sort_data 변수에 설정된 값보다 크거나, BLOB, TEXT 타입의 컬럼이 대상이 되는 경우 투 패스 정렬 방식을 사용한다.

정렬 처리 방법

다음 세 가지 방법 중 하나로 정렬이 처리된다. 아래로 갈수록 처리 속도가 떨어진다.

• 인덱스를 이용한 정렬
• 조인에서 드라이빙 테이블만 정렬 => Using filesort
• 조인 결과를 임시 테이블에 저장 후 정렬 => Using temporary; Using filesort

먼저 옵티마이저는 정렬 처리를 위해 인덱스르 이용할 수 있는지 검토한다. 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 소트 버퍼에 저장하면서 정렬한다.

MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 2가지 방법 중 하나를 선택한다

• 조인의 드라이빙 테이블만 정렬한 다음 조인 수행
• 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

조인이 수행되면서 레코드 크기가 늘어나기 때문에 전자가 더 효율적이다.

인덱스를 이용한 정렬

반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블) 에 속하고 ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. WHERE 절에 제일 먼저 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용해야 한다.

당연하지만 해시 인덱스나 전문 검색 인덱스에서는 인덱스를 이용한 정렬을 수행할 수 없다.

조인의 드라이빙 테이블만 정렬

드라이빙 테이블은 조인 쿼리에서 가장 먼저 스캔되는 테이블로, 다른 테이블과의 조인 과정에서 기준이 되는 테이블입니다. 일반적으로 처리해야 할 데이터의 양이 적은 테이블을 드라이빙 테이블로 선택하여 쿼리 성능을 향상시킵니다. 강력한 필터링 조건(즉, 결과를 크게 줄일 수 있는 조건)을 가진 테이블이 유리합니다. 인덱스를 효과적으로 사용할 수 있는 테이블을 드라이빙 테이블로 설정하여 접근 속도를 빠르게 합니다.

조인이 수행되면 레코드 건수도 늘어나고 레코드 하나하나의 크기도 늘어난다. 그래서 드라이빙 테이블을 먼저 정렬한 다음 조인을 실행하면 효율적이다. 이 방법으로 정렬이 수행되려면 드라이빙 테이블의 컬럼만으로 ORDER BY 절을 작성해야 한다.

SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 102 AND 110
ORDER BY e.last_name;

WHERE 절의 조건은 employees 테이블의 PK를 사용한다. 또 salaries의 조인 컬럼인 emp_no에 인덱스가 존재한다. 따라서 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택한다.

임시 테이블을 이용한 정렬

SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 102 AND 110
ORDER BY s.salary;

이 쿼리는 정렬 기준이 드라이빙 테이블이 아닌 드리븐 테이블에 존재한 컬럼이다. 이 경우 조인 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬해야 한다. 가장 느린 정렬 방법이다.

정렬 처리 방법의 성능 비교

일반적으로 LIMIT 을 사용하면 처리하는 양을 줄일 수 있을 것이라고 생각한다. 그러나 ORDER BY 혹은 GROUP BY 같은 작업은 WHERE 조건을 만족하는 결과를 LIMIT만큼만 가져와서 처리할 수 없고 모두 가져온 후 정렬을 수행하거나 그루핑을 한 후 LIMIT을 처리하게 된다.

• 스트리밍 방식: 서버 쪽에서 레코드가 검색될 때마다 바로바로 클라이언트로 결과를 전송해주는 방식이다. LIMIT을 사용하면 처리량을 줄일 수 있다.
• 버퍼링 방식: 조건을 만족하는 모든 레코드를 가져와서 정렬한 후 차례대로 보내야 한다. LIMIT을 사용해도 처리량이 개선되지 않는다.

ORDER BY 처리 방식에서 인덱스를 이용한 정렬 방식만 스트리밍 형태의 처리이고 나머지는 모두 버퍼링 방식이다.

GROUP BY 처리

GROUP BY도 쿼리가 스트리밍 될 수 없게 만든다. 똑같이 인덱스를 이용하는 경우와 이용하지 못하는 경우로 나눌 수 있다.

인덱스 스캔

ORDER BY와 마찬가지로 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 인덱스 스캔을 하게 된다. 이미 정렬된 인덱스를 차례대로 읽으면서 그루핑을 한다.

루스 인덱스 스캔

루스 인덱스 스캔은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 방식이다. 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 컬럼에 “Using index for group-by” 라는 코멘트가 표시된다.

EXPLAIN
	SELECT emp_no
	FROM salaries
	WHERE from_date='1985-03-01'
	GROUP BY emp_no;

(emp_no, from_date)로 인덱스가 생성되어 있다고 하자. 이 경우 WHERE 절에 있는 조건은 인덱스 레인지 스캔을 사용할 수 없다.

하지만 이 쿼리의 실행 계획은 인덱스 레인지 스캔을 사용했으며, GROUP BY 처리까지 인덱스를 사용했다.

1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(10001)을 찾아 낸다.
2. (emp_no, from_date) 인덱스에서 emp_no=10001인 것 중에서 from_date=‘1985-03-01’인 레코드만 가져온다. AND 조건으로 (emp_no, from_date) 인덱스를 검색하는 것과 비슷하다.
3. (emp_no, from_date) 인덱스에서 그 다음 emp_no를 가져온다.
4. 반복한다

임시 테이블 이용

인덱스를 사용하지 못하는 경우 임시 테이블을 만든다. 이 때 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

EXPLAIN
	SELECT e.last_name, AVG(s.salary)
	FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no
	GROUP BY e.last_name;

이 쿼리는 다음과 같은 임시 테이블을 생성한다.

CREATE TEMPORARY TABLE ... (
	last_name VARCHAR(16),
	salary INT,
	UNIQUE INDEX ux_lastname (last_name)
);

그리고 조인 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 혹은 UPDATE를 실행한다. 별도의 정렬 작업 없이 GROUP BY가 처리된다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하려면 DISTINCT를 사용한다. 크게 MIN(), MAX(), COUNT() 같은 집합 함수와 사용되는 경우, 집합 함수가 없는 경우 2가지로 나눌 수 있다.

SELECT DISTINCT

단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하는 경우 GROUP BY와 똑같이 처리된다.

SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

두 쿼리는 내부적으로 같은 작업을 수행한다.

집합 함수와 함께 사용된 DISTINCT

집합 함수가 없는 쿼리에서는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다. 하지만 집합 함수 내에서 사용되는 경우는 그 인자로 전달된 칼럽값이 유니크한 것들을 가져온다.

집합 함수와 함께 사용할 때는 항상 임시 테이블을 생성하게 된다. 하지만 실행 계획에는 따로 표시되지 않는다.

EXPLAIN
	SELECT COUNT(DISTINCT s.salary)
	from employees e, salaries s
	WHERE e.emp_no=s.emp_no
	and e.emp_no BETWEEN 101 AND 110;

이 쿼리는 조인 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다. 만약 COUNT(DISTINCT )가 더 추가된다면 임시 테이블도 추가된다.

하지만 인덱스된 컬럼에 대해 COUNT(DISTINCT )를 처리하는 경우는 인덱스를 타면서 임시 테이블 없이 최적화된 처리를 할 수 있다.

내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 **내부적인 임시 테이블(Internal temporary table)**을 사용한다.
• 내부적(internal)이라 표현하는 이유는 CREATE TEMPORARY TABLE 명령어로 만든 임시 테이블과는 다르기 때문이다.
• 내부적인 임시 테이블은 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 또한 다른 세션이나 쿼리에서는 볼 수 없고 쿼리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

MySQL 8.0부터 TempTable이라는 스토리지 엔진이 도입되었다. temptable_max_ram 기본값 1G까지 메모리 사용 가능하고 더 커지면 디스크에 기록하게 된다. 이 때 두 가지 방식으로 저장할 수 있다.

• MMAP 파일
• InnoDB 테이블

MMAP는 디스크 파일을 메모리에 매핑하여 마치 메모리처럼 접근할 수 있다. 대신 InnoDB 테이블에서 사용할 수 있는 크래시 복구 등의 기능을 사용할 수 없다.

임시 테이블이 필요한 쿼리

• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY와 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• distinct와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT 가 인덱스로 처리되지 못하는 쿼리
• UNION이나 UNION DISTINCT 가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 하는 경우가 많다.

임시 테이블이 디스크에 생성되는 경우

내부 임시 테이블은 기본적으로 메모리에 만들어 지지만 다음과 같은 조건을 만족하는 경우 메모리 기반 임시 테이블을 사용할 수 없게 된다. 디스크 기반 임시 테이블을 사용하게 된다.

• UNION 이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 temp_table_max_ram 시스템 변수 값보다 큰 경우