[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (1)

유혁·2026년 5월 18일

CS Database Real MySQL 8.0

Real MySQL 8.0

목록 보기

16/22

9.1 개요

9.1.1 쿼리 실행 절차

MySQL 서버 쿼리 실행 과정

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

9.1.2 옵티마이저의 종류

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당하며, 비용 기반 최적화 방법(Cost-based optimizer, CBO)과 규칙 기반 최적화 방법(Rule-based optimizer, RBO) 두 가지로 크게 나눌 수 있다.

규칙 기반 최적화는 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식으로, 같은 쿼리에 대해서 항상 거의 같은 실행 방법을 만들어 낸다. 하지만 사용자의 데이터 분포도가 매우 다양하기 때문에 오래전부터 거의 사용되지 않는다.

비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한 뒤, 비용이 최소로 소요되는 처리 방식을 선택해 쿼리를 실행한다.

9.2 데이터 처리

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

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

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

MySQL은 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없으나, InnoDB의 경우 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작한다.

리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해 요청이 오기 전 미리 디스크에서 읽어 InnoB 버퍼 풀에 가져다 두는 것을 의미한다. 따라서 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.

풀 인덱스 스캔은 인덱스를 처음부터 끝까지 스캔하는 것을 의미하며, 리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아닌 풀 인덱스 스캔에서도 동일하게 사용된다.

MySQL 서버는 단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있어 풀 인덱스 스캔을 선택할 가능성이 높지만, 레코드에만 있는 칼럼이 필요한 쿼리의 경우에는 풀 테이블 스캔을 한다.

9.2.2 병렬 처리

MySQL 8.0에서는 innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다. 아직 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없으며, 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

mysql> SET SESSION innodb_parallel_read_threads=1;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.32 sec)

mysql> SET SESSION innodb_parallel_read_threads=4;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.18 sec)

9.2.3 `ORDER BY` 처리(Using filesort)

대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용되며, 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

Filesort의 장점은 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점이 되며, 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. 그러나 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 다소 느리다는 단점이 있다.

MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 "Using filesort" 메시지가 표시되는지 여부로 판단할 수 있다.

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort buffer)라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정할 수 있다. 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 큰 경우, MySQL은 레코드를 여러 조각으로 나눠서 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둔다. 그리고 다음 레코드를 가져와 다시 정렬해 반복적으로 디스크에 임시 저장한다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는 멀티 머지(Multi-merge) 단계를 거친다.

이 작업들은 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 횟수가 많아진다. 소트 버퍼를 크게 설정하면 디스크를 사용하지 않아 더 빨라질 것으로 생각할 수 있지만, 실제로는 큰 차이를 보이지 않는다. 오히려 리눅스 계열 운영체제에서는 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수도 있다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 저자는 싱글 패스(Single-pass)와 투 패스(Two-pass) 2가지 정렬 모드로 분류한다.

싱글 패스 정렬 방식

싱글 패스 정렬 방식은 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.

mysql> SELECT emp_no, first_name, last_name
		FROM employees 
        ORDER BY first_name;

위 쿼리를 first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT하는 쿼리를 싱글 패스 정렬 방식으로 처리하는 절차를 이미지로 나타낸 것으로, 처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 칼럼까지 전부 읽어 소트 버퍼에 담고 정렬을 수행하고, 완료되면 정렬 버퍼의 내용을 그대로 클라이언트에게 넘겨준다.

투 패스 정렬 방식

투 패스 정렬 방식은 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 방식으로, 싱글 패스 정렬 방식 이전부터 사용하던 방식이다.

위 이미지는 동일한 쿼리를 투 패스 정렬 방식으로 정렬하는 과정으로 표현한 것으로, last_name 칼럼을 조회하기 위해 테이블을 다시 읽어야 한다는 점에서 큰 차이가 존재한다.

투 패스 정렬 방식의 경우 테이블을 두 번 읽어야 하는 불합리함이 있지만, 싱글 패스 정렬 방식에 비해 적은 소트 버퍼 공간을 사용하여 레코드를 정렬한다. 따라서 싱글 패스 정렬 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우, 투 패스 정렬 방식은 크기나 건수가 상당히 많은 경우 효율적이다.

최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용하나, 아래의 경우 투 패스 정렬 방식을 사용한다.

레코드의 크기가 max_lengtg_for_sort_data 시스템 변수에 설정된 값보다 클 때
BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때

정렬 처리 방법

인덱스를 이용한 정렬

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

B-Tree 계열의 인덱스가 아닌 경우 사용할 수 없으며, 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 방식의 조인에서만 사용할 수 있다.

인덱스를 이용해 정렬이 처리되는 경우에는 B-Tree 인덱스가 키 값으로 정렬돼 있기 때문에 순서대로 읽기만 하면 된다. 또한 조인이 네스티드-루프 방식으로 실행되기 때문에 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다. 하지만 조인이 사용된 커리의 실행 계획에 조인 버퍼가 사용되면 순서가 흐트러질 수 있기 때문에 조심해야 한다.

다음 예제의 경우 ORDER BY의 여부와 상관없이 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력되는데, 이는 employees 테이블의 프라이머리 키를 읽은 뒤 salaries 테이블을 조인했기 때문이다.

mysql> SELECT *
		FROM employees, salaries s
        WHERE s.emp_no=e.emp_no
        	AND e.emp_no BETWEEN 100002 AND 10020
        ORDER BY e.emp_no;
        

mysql> SELECT *
		FROM employees, salaries s
        WHERE s.emp_no=e.emp_no
        	AND e.emp_no BETWEEN 100002 AND 10020;

ORDER BY 절을 넣지 않아도 자동 정렬되므로 이를 제거해서 쿼리를 작성하기도 하지만, ORDER BY 절이 쿼리에 있어도 작업량은 늘어나지 않으며 오히려 쿼리 계획이 변경되는 경우 쿼리 결과를 기대했던 순서로 가져오지 못하는 상황이 발생할 수 있다.

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

일반적인 조인은 결과 레코드 건수가 몇 배로 불어나며 레코드의 크기도 늘어나므로, 조인 실행 전 드라이빙 테이블의 레코드를 먼저 정렬한 후 조인을 실행하는 것이 정렬의 차선책이 된다. 이 방법으로 정렬이 처리되려면 조인에서 드라이빙 테이블의 칼럼만으로 ORDER BY 절을 작성해야 한다.

mysql> SELECT *
		FROM employees, salaries s
        WHERE s.emp_no=e.emp_no
        	AND e.emp_no BETWEEN 100002 AND 10010;
       	ORDER BY e.last_name;

WHERE 절의 조건을 통해 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택한다. ORDER BY의 칼럼은 프라이머리 키와 연관이 없어 인덱스를 이용한 정렬이 불가능하지만, 드라이빙 테이블에 포함된 칼럼이다. 따라서 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한다.

임시 테이블을 이용한 정렬

이 방법은 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거치므로 가운데 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.

mysql> SELECT *
		FROM employees, salaries s
        WHERE s.emp_no=e.emp_no
        	AND e.emp_no BETWEEN 100002 AND 10010;
       	ORDER BY s.salary;

이번 쿼리의 ORDER BY절의 정렬 기준 칼럼이 드리븐 테이블(salaries)에 있는 칼럼이므로, 정렬이 수행되기 전 salaries 테이블을 읽어야만 해 조인된 데이터를 가지고 정렬해야 한다.

정렬 처리 방법의 성능 비교

두 가지 쿼리 처리 방법

스트리밍 처리 방식
서버 쪽에서 처리할 데이터가 얼마인지에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 클라이언트로 전송해주는 방식이다. 이는 클라이언트가 첫 번째 레코드를 전달받는 응답 시간이 굉장히 짧아지며, LIMIT 조건을 추가하면 마지막 레코드를 가져오는 시간도 상당히 줄일 수 있다. ORDER BY나 GROUP BY 같은 처리는 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야하기 때문에 스트리밍 방식을 사용할 수 없다.
버퍼링 방식
처리되는 쿼리의 결과를 모아서 가공한 뒤 모든 결과를 가져오기까지 기다리는 방식이다. 기다리는 동안 클라이언트는 아무것도 하지 않고 기다려야 하므로 응답 속도가 느려지며, LIMIT 조건이 성능 향상에 별로 도움을 주지 않는다.

인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 버퍼링된 후에 정렬된다. 즉, LIMIT 조건의 성능 향상은 인덱스를 사용한 정렬 방식에서만 성능 향상을 보이므로 가능하다면 인덱스를 사용한 정렬로 유도하고, 최소한 드라이빙 테이블만 정렬해도 되는 수준은 유지하는 것이 좋다.

9.2.4 `GROUP BY` 처리

인덱스 스캔을 이용하는 `GROUP BY`(타이트 인덱스 스캔)

조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면 서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

루스 인덱스 스캔을 이용하는 `GROUP BY`

MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있으며, 프리픽스 인덱스는 이를 사용할 수 없다.

임시 테이블을 사용하는 `GROUP BY`

GROUP BY의 기준 칼럼이 인덱스를 전혀 사용하지 못할 때 임시 테이블을 사용한다.

9.2.5 `DISTINCT` 처리

`SELECT DISTINCT ...`

SELECT DISTINCT 형태의 쿼리 문장은 GROUP BY와 동일한 방식으로 처리되며, 특히 ORDER BY절이 없는 경우 정렬을 사용하지 않아 내부적으로 같은 작업을 수행한다.

DISTINCT는 SELECT하는 레코드를 유니크하게 SELECT하는 것일 뿐, 특정 칼럼만 유니크하게 조회하는 것이 아니다. 즉, SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다.

집합 함수와 함께 사용된 `DISTINCT`

COUNT(), MIN(), MAX() 같은 집합 함수 내에서의 DISTINCT는 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.

DISTINCT 처리를 위해 인덱스를 이용할 수 없는 경우에는 임시 테이블을 사용하게 되며, 인덱스된 칼럼에 대해 DISTINCT 처리를 수행하는 경우 인덱스 풀 스캔 혹은 레인지 스캔을 통해 처리한다.

임시 테이블을 사용하는 경우에도 실행 계획에서는 "Using temporary"를 표시하지 않고 있으니 주의하자.

9.2.6 내부 임시 테이블 활용

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

MySQL 8.0 버전부터 메모리는 가변 길이 타입을 지원하는 TempTable, 디스크에 저장되는 임시 테이블은 트랜잭션 지원이 가능한 InnoDB 스토리지 엔진 또는 TempTable 스토리지 엔진의 MMAP 파일 버전을 사용한다.

임시 테이블이 필요한 쿼리

다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하는 대표적인 내부 임시 테이블을 생성하는 케이스들이다.

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

위 5개의 패턴 중 아래 3개의 패턴은 실행 계획의 Extra 칼럼에서 임시 테이블 사용 여부를 확인하는 "Using temporary" 메시지가 표시되지 않는다.

첫 번째부터 네 번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어지며, 마지막 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다.

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

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

UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상의 크기의 칼럼이 있는 경우
GROUP BY나 DISTINCT 칼럼에서 512 바이트 이상인 크기의 칼럼이 있는 경우
메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진) temptable_max_ram 시스템 변수 값보다 큰 경우

유혁

백엔드 개발자

이전 포스트

[Real MySQL 8.0 1권] 08. 인덱스 (4)

다음 포스트

[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (1)

Real MySQL 8.0

9.1 개요

9.1.1 쿼리 실행 절차

9.1.2 옵티마이저의 종류

9.2 데이터 처리

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

9.2.2 병렬 처리

9.2.3 `ORDER BY` 처리(Using filesort)

소트 버퍼

정렬 알고리즘

싱글 패스 정렬 방식

투 패스 정렬 방식

정렬 처리 방법

인덱스를 이용한 정렬

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

임시 테이블을 이용한 정렬

정렬 처리 방법의 성능 비교

9.2.4 `GROUP BY` 처리

인덱스 스캔을 이용하는 `GROUP BY`(타이트 인덱스 스캔)

루스 인덱스 스캔을 이용하는 `GROUP BY`

임시 테이블을 사용하는 `GROUP BY`

9.2.5 `DISTINCT` 처리

`SELECT DISTINCT ...`

집합 함수와 함께 사용된 `DISTINCT`

9.2.6 내부 임시 테이블 활용

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

임시 테이블이 필요한 쿼리

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

[Real MySQL 8.0 1권] 08. 인덱스 (4)

[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (2)

0개의 댓글

[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (1)

Real MySQL 8.0

9.1 개요

9.1.1 쿼리 실행 절차

9.1.2 옵티마이저의 종류

9.2 데이터 처리

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

9.2.2 병렬 처리

9.2.3 ORDER BY 처리(Using filesort)

소트 버퍼

정렬 알고리즘

싱글 패스 정렬 방식

투 패스 정렬 방식

정렬 처리 방법

인덱스를 이용한 정렬

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

임시 테이블을 이용한 정렬

정렬 처리 방법의 성능 비교

9.2.4 GROUP BY 처리

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

루스 인덱스 스캔을 이용하는 GROUP BY

임시 테이블을 사용하는 GROUP BY

9.2.5 DISTINCT 처리

SELECT DISTINCT ...

집합 함수와 함께 사용된 DISTINCT

9.2.6 내부 임시 테이블 활용

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

임시 테이블이 필요한 쿼리

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

[Real MySQL 8.0 1권] 08. 인덱스 (4)

[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (2)

0개의 댓글

9.2.3 `ORDER BY` 처리(Using filesort)

9.2.4 `GROUP BY` 처리

인덱스 스캔을 이용하는 `GROUP BY`(타이트 인덱스 스캔)

루스 인덱스 스캔을 이용하는 `GROUP BY`

임시 테이블을 사용하는 `GROUP BY`

9.2.5 `DISTINCT` 처리

`SELECT DISTINCT ...`

집합 함수와 함께 사용된 `DISTINCT`