MySQL에서는 EXPLAIN이라는 명령으로 쿼리 실행 계획을 확인할 수 있다. 실행 계획에 표시되는 내용을 제대로 이해하려면 MySQL 서버 옵티마이저가 실행하는 최적화에 대해 어느 정도 지식을 갖추고 있어야 한다.

개요

옵티마이저는 쿼리의 실행 계획을 수립하는 가장 복잡한 부분이다. 실행 계획을 이해하면 실행 계획의 불합리한 부분을 찾아내고 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.

1번 단계를 SQL파싱이라고 하며, MySQL 서버의 SQL 파서라는 모듈로 처리한다. SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다. 또한 이 단계에서 SQL 파서 트리가 만들어진다. MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행한다.

2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.

2번 단계에서 만들어진 SQL 파스 트리를 참조하면서 아래 내용을 처리한다.

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

즉, 최적화 및 실행 계획 수립 단계이며, MySQL 서버의 옵티마이저에서 처리한다. 2번 단계가 완료되면 쿼리의실행 계획이 만들어진다.

3. 2번 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

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

옵티마이저 종류

옵티마이저는 대부분의 DBMS가 채택하고 있는 비용 기반 최적화 방법(Cost-based optimizer)과 예전 초기 버전의 Oracle DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer)으로 크게 나눌 수 있다.

규칙 기반 최적화

대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다. 통계 정보를 사용하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다. 상대적으로 느린 CPU가 느려 비용 계산 과정이 부담스럽다는 이유라 사용되었지만 요즘은 CPU도 빠르고 사용자의 데이터 분포도가 매우 다양하기 때문에 거의 사용하지 않는다.

비용 기반 최적화

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

통계 정보는 히스토그램을 이용한다.

기본 데이터 처리

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

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

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

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

20~25%를 넘으면 랜덤 I/O가 많이 발생하여, 성능이 좋지 않다고 판단한다.

스토리지 별 디스크 읽기

일반적으로 테이블은 인덱스보다 매우 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다. 대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. 하지만 MySQL에는 풀 테이블 스캔을 실행할 때 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.

MyISAM 스토리지 엔진

MyISAM 스토리지 엔진은 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어 온다.

InnoDB 스토리지 엔진

일반적으로 테이블은 매우 크기 때문에 풀 테이블 스캔을 수행할 때 한 번에 여러 블록이나 페이지를 읽는다. 테이블의 연속된 데이터 페이지가 읽힐 때, 백그라운드 스레드에 의해 리드 어헤드(Read Ahead) 작업이 자동으로 시작된다. 리드 어헤드는 특정 영역의 데이터가 곧 필요하게 될 것이라 예상하여, 요청이 도착하기 전에 디스크에서 미리 읽어 InnoDB의 버퍼 풀에 저장하는 것을 말한다.

리드 어헤드

풀 테이블 스캔이 실행되면, 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 담당하지만, 특정 시점부터 읽기 작업을 백그라운드 스레드로 전환한다. 이 시점부터 백그라운드 스레드는 한 번에 4개 또는 8개씩 페이지를 읽으며, 이런 작업을 늘려가며 최대 64개의 데이터 페이지를 읽어 버퍼 풀에 저장한다. 그 결과, 포그라운드 스레드는 미리 버퍼 풀에 저장된 데이터를 사용할 수 있어 쿼리 처리가 매우 빠르게 완료된다.

MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다. 주로 웨어하우스용으로 사용할 때 이용한다.

리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용된다.

mysql > SELECT COUNT(*) FROM employees;

이 쿼리는 아무 조건 없이 SELECT 쿼리를 실행해 테이블의 레코드 건수를 조회하고 있으므로 풀 테이블 스캔을 할 것 같지만 단순히 레코드의 건수만 필요로 하는 쿼리이기 때문에 풀 인덱스 스캔하게 될 가능성이 높다. MySQL 서버는 단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문이다.

mysql > SELECT * FROM employees;

이 쿼리는 레코드에만 있는 칼럼 정보가 필요하기에 풀 테이블 스캔이 된다.

병렬 처리

MySQL 8.0 버전부터 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리할 수 있다. innodb_parallel_read_threads 시스템 변수를 이용해 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있다. 아직 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없다. 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있다.

ORDER BY 처리(Using Filesort)

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

	장점	단점
인덱스 이용	INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 된다.	INSERT, UPDATE, DELETE 작업 시, 인덱스 추가/삭제가 필요하므로 느리다. 인덱스의 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort 이용	인덱스가 필요없으므로 인덱스의 단점이 장점이 된다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 빠르다.	쿼리 실행 시 정렬 작업이 처리되므로 레코드 건수가 많아질수록 쿼리의 응답 속도가 느리다.

모든 정렬을 인덱스를 이용해서 할 수 있는 것은 아니다. 아래 경우는 인데스를 이용하도록 튜닝하기 어렵다.

• 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
• GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
• 랜덤하게 결과 레코드를 가져와야 하는 경우

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

소트 버퍼

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

문제점

레코드 정렬이 필요할 때, 정렬해야할 레코드 수가 할당된 소트 버퍼보다 크다면, MySQL은 레코드를 여러 조각으로 나누어 처리하는데 이 과정에서 디스크를 이용한다.

메모리의 소트 버퍼에서 정렬을 수행한 다음, 그 결과를 임시로 디스크에 저장한다. 이어서 다음 레코드를 가져와 정렬하고, 이를 반복하며 디스크에 임시 저장한다. 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다. 이 과정에서 디스크를 사용하여 임시 저장하면서 I/O 작업이 많이 발생하므로 성능이 좋지 않다.

소트 버퍼에 큰 메모리 공간을 할당하면 빠르게 정렬할 수 있을 것 같지만, 실제로는 리눅스 계열 운영체제에서 큰 메모리 공간 할당 때문에 성능이 더 떨어질 수 있다. 메모리에 남는 공간이 생기거나 스왑 메모리를 사용하게 되어 디스크 I/O가 발생할 수 있다.

소트 버퍼는 세션 메모리 영역이며, 여러 클라이언트 간에 공유되지 않는다. 커넥션이 많을수록 정렬 작업이 많아지면, 소트 버퍼로 소비되는 메모리 공간도 커진다. 이러한 상황에서 OOM-Killer가 여유 메모리를 확보하기 위해 프로세스를 강제로 종료할 수 있다. OOM-Killer는 메모리 사용량이 높은 프로세스를 강제 종료하는데, 일반적으로 메모리를 많이 사용하는 데이터베이스가 강제 종료 대상의 최우선이다.

소트 버퍼 크기 설정

소트 버퍼를 크게 설정해서 빠른 성능을 얻을 수는 없지만 읽기와 쓰기 사용량은 줄일 수 있다. 그래서 MySQL 서버의 데이터가 많거나 디스크의 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이 될 수 있다.

소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져 MySQL 서버가 메모리 부족을 겪을 수 있기 때문에 적절히 설정하자. 그리고 대량 데이터의 정렬이 필요한 경우 해당 세션의 소트 버퍼만이 일식적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 좋은 방법이다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 나눌 수 있다.

• <sort_key, rowid>: 정렬 키와 레코드의 로우 아이디만 가져와서 정렬하는 방식
• <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 고정 사이즈로 메모리에 저장
• <sort_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 가변 사이즈로 메모리 저장

첫 번째 방식을 "투 패스(Two-pass)", 두 번째와 세 번째 방식을 "싱글 패스(Single-pass)"로 명명하고 설명한다.

싱글 패스 정렬 방식

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

위 그림에서 알 수 있듯이, 처음 테이블을 읽을 때 정렬에 필요하지 않은 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트에 넘겨주는 과정을 볼 수 있다.

투 패스 정렬 방식

정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정력 방식으로 싱글 패스 정렬 방식 이전에 도입된 방식이다. 하지만 MySQL 8.0에서도 여전히 특정 조건에서는 투 패스 정렬 방식을 사용한다.

적용

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

성능 차이

투 패스 방식이 더 빠른 것도 같지만 항상 그런 것은 아니다. 싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라고 볼 수 있다.

꼭 필요한 칼럼을 조회하도록 쿼리를 작성하는 것이 좋은 이유는 그렇지 않을 경우 정렬 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 크기 때문이다. 특히 정렬이 필요한 SELECT는 불필요한 칼럼을 SELECT하지 않게 쿼리를 작성하는 것이 효율적이다.

정렬 처리 방법

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 처리된다.

정렬 처리 방법	실행 계획의 Extra 칼럼 사용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort" 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort" 메시지가 표시됨

드라이빙 테이블이란 조인을 할 때 먼저 액세스 되어 ACCESS PATH를 주도하는 테이블을 드라이빙 테이블이라고 한다. 즉, 조인할 때 먼저 액세스 되는 테이블을 드라이빙 테이블이라고 하며 나중에 액세스 되는 테이블을 드리븐 테이블이라고 한다. 여기서 드라이빙 테이블은 옵티마이저가 결정하고 자연스레 드라이빙 테이블이 아닌 테이블은 드리븐 테이블로 결정된다.

옵티마이저는 먼저 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토한다. 인덱스를 이용할 수 있다면 별도의 filesort 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다. 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리한다. 이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택한다.

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

일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다.

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고 ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. 그리고 B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없다. 여러 테이블이 조인되는 경우에는 네스티드-루프 방식의 조인에서만 이 방식을 사용할 수 있다.

예외적으로 R-Tree도 B-Tree 계열이지만, 정렬을 사용할 수 없다.

인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스 순서대로 읽기만 하면 된다. 실제로 MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지 않는다.

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

ORDER BY 절을 넣지 않아도 자동으로 정렬되므로 ORDER BY 절 자체를 쿼리에서 완전히 제거해서 쿼리를 작성하는 것은 좋지 않은 선택이다. MySQL 서버는 인덱스로 처리할 수 있는 경우 부가적으로 불필요한 정렬 작업을 수행하지 않는다. 또한 어떤 이유로 실행 계획이 조금 변경된다면 ORDER BY가 명시되지 않은 쿼리는 결과를 기대했던 순서대로 가져오지 못해서 버그로 연결될 수도 있다.

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

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고 레코드 하나하나의 크기도 늘어난다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다. 이 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고 그 결과와 salaries 테이블을 조인한다.

1. 인덱스를 이용해 emp_no BETWEEEN 100001 AND 100010 조건을 만족하는 9건을 검색
2. 검색결과를 last_name 칼럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고 하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않다. 하지만 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다. 앞에서 살펴본 조인의 드라이빙 테이블만 정렬은 임시 테이블을 사용하지 않지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬하는 과정을 거친다. ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 컬럼인 경우 조인이 된 데이터를 가지고 정렬할 수밖에 없다.

정렬 처리 방법의 성능 비교

주로 웹 서비스용으로 LIMIT과 ORDER BY가 필수로 사용된다. 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄인다. 그렇지만 WHERE 조건은 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY로 느려지는 경우가 발생한다.

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 처리되는 방법을 2가지 방식으로 구분할 수 있다.

스트리밍 방식

서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다. 이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.

쿼리가 스트리밍 방식으로 처리될 수 있다면 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다. 웹 서비스 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요하다. 스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해준다.

또한 스트리밍 방식으로 처리되는 쿼리에서는 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.

버퍼링 방식

ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다. 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다.

어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능하다면 인덱스를 사용한 정렬로 유도하고 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있다.

정렬 처리 방법 중 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며 나머지는 모두 버퍼링된 후에 정렬된다.

정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다. 각 상태 값은 다음과 같은 의미가 있으며, 이 값들을 이용해 MySQL 서버가 처리한 정렬 작업의 내용을 어느 정도 이해할 수 있다.

• Sort_merge_passes는 멀티 머지 처리 횟수를 의미한다.
• Sort_range는 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다.
• Sort_scan은 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다. Sort_scan과 Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값이다.
• Sort_rows는 지금까지 정렬한 전체 레코드 건수를 의미한다.

GROUP BY 처리

GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데 HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

GROUP BY 작업도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나눠 볼 수 있다. 인덱스를 이용할 때는 인덱스를 차례대로 읽는 인덱스 스캔 방법과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔이라는 방법으로 나뉜다. 그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.

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

조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹 함수 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 쿼리 실행 시점에 추가적인 정렬 작업이나 내부 임시 테이블은 필요하지 않다. 이러한 그루핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 컬럼에 별도로 GROUP BY 관련 코멘트나 임시 테이블 사용 또는 정렬 관련 코멘트가 표시되지 않는다.

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

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

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

MySQL의 처리 순서

1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값 "10001"을 찾아낸다.
2. (emp_no, from_date) 인덱스에서 emp_no가 "10001"인 것 중에서 from_date 값이 "1985-03-01"인 레코드만 가져온다.
3. (emp_no, from_date) 인덱스에서 emp_no의 그 다음 유니크한 값을 가져온다.
4. 3번 단계에서 결과가 더 없으면 처리를 종료하고 결과가 있다면 2번 과정으로 돌아가서 반복을 수행한다.

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

GROUP BY의 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 임시 테이블을 사용해서 처리된다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. DISTINCT는 MIN(), MAX() 또는 COUNT() 같은 집합 함수가 없는 경우 2가지로 구분된다. 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다. 하지만 실행 계획의 Extra 컬럼에서는 Using temporary 메시지가 출력되지 않는다.

SELECT DISTINCT ...

단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 동일한 방식으로 처리된다. 특히 MySQL 8.0 버전부터 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 다음 두 쿼리는 내부적으로 같은 작업을 수행한다.

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

DISTINCT는 SELECT하는 레코드를 유니크하게 SELECT하는 것이지 특정 컬럼만 유니크하게 조회하는 것이 아니다.

SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 컬럼에 영향을 미친다. 절대로 SELECT하는 여러 컬럼 중 일부 컬럼만 유니크하게 조회하는 것은 아니다.

집합 함수와 함께 사용된 DISTINCT

COUNT() 또는 MIN(), MAX() 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데 이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다. 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져온다.

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다. 내부적이라는 단어가 포함된 이유는 여기서 이야기하는 임시 테이블은 "CREATE TEMPORARY TABLE" 명령으로 만든 임시 테이블과는 다르기 때문이다.
일반적으로 MySQL 엔진이 사용하는 임시 테이블을 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능하다.

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

MySQL 8.0 버전부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하고 디스크에 저장되는 임시 테이블은 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바이트 이상인 크기의 컬럼이 있는 경우
• 메모리 임시 테이블의 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 시스템 변수 값보다 큰 경우

고급 최적화

MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다. 옵티마이저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있다.

옵티마이저 스위치 옵션

MRR과 배치 키 엑세스

블록 네스티드 루프 조인(block_nested_loop)

인덱스 컨디션 푸시다운(index_condition_pushdown)

인덱스 확장 (use_index_extensions)

인덱스 머지 (index_merge)

인덱스 머지 - 교집합 (index_merge_intersection)

인덱스 머지 - 합집합 (index_merge_union)

인덱스 머지 - 정렬 후 합집합 (index_merge_sort_union)

세미 조인 (semijoin)

테이블 풀-아웃 (Table Pull-out)

퍼스트 매치 (firstmatch)

루스 스캔 (loosescan)

구체화 (Materialization)

중복 제거 (Duplicated Weed-out)

컨디션 팬아웃 (condition_fanout_filter)

파생 테이블 머지(derived_merge)

인비저블 인덱스 (use_invisible_indexes)

스킵 스캔 (skip_scan)

해시 조인 (hash_join)

인덱스 정렬 선호(prefer_ordering_index)

조인 최적화 알고리즘

MySQL에는 조인 쿼리의 실행 계획 최적화를 위한 알고리즘 2개가 있다. MySQL의 조인 최적화는 나름 많이 개선됐다고 이야기한다. 하지만 사실 테이블의 개수가 많아지면 최적화된 실행 계획을 찾는 것이 상당히 어려워지고 실행 계획을 수립하는 데만 몇 분이 걸릴 수도 있다. 테이블의 개수가 특정 한계를 넘어서면 그때부터는 실행 계획 수립에 소요되는 시간만 몇 시간이나 며칠로 늘어날 수도 있다. 여기서는 왜 그런 현상이 생기고 어떻게 그런 현상을 피할 수 있는지 알아보자.

Exhaustive 검색 알고리즘

MySQL 5.0과 그 이전 버전에서 사용되던 조인 최적화 기법으로 FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다. 테이블이 20개라면 이 방법으로 처리했을 때 가능한 조인 조합은 모두 20! 개가 된다.

Greedy 검색 알고리즘

Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 조인 최적화 기법이다. Greedy는 Exhaustive 검색 알고리즘보다는 조금 복잡한 형태로 최적의 조인 순서를 결정한다.

1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성

2. 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정

3. 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획"의 첫 번째 테이블로 선정

4. 전체 N-1개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성

5. 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행 계획에 대입해 실행 비용을 계산

6. 비용 계산 결과 최적의 실행 계획에서 두 번쨰 테이블을 3번에서 생성된 "부분 실행 계획"의 두 번째 테이블로 선정

7. 남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복 실행하면서 "부분 실행 계획"에 테이블의 조인 순서를 기로

8. 최종적으로 "부분 실행 계획"이 테이블의 조인 순서로 결정됨

쿼리 힌트

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

MySQL 서버에서 사용 가능한 쿼리 힌트는 다음과 같이 2가지로 구분할 수 있다.

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

인덱스 힌트는 예전 버전의 MySQL 서버에서 사용되어 오던 "USE INDEX" 같은 힌트를 의미하며 옵티마이저 힌트는 MySQL 5.6 버전부터 새롭게 추가되기 시작한 힌트를 지칭한다.

인덱스 힌트

STRAIGHT_JOIN과 USE INDEX 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다. 이들은 모두 SQL의 문법에 맞게 사용해야 하기 때문에 사용하게 되면 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있다. MySQL 5.6 버전부터 추가되기 시작한 옵티마이저 힌트들은 모두 MySQL 서버를 제외한 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고는 볼 수 있다. 그래서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트를 사용할 것을 추천한다. 또한 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 사용할 수 있다.

조인 순서 고정 힌트

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드이기도 하다. STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

다음 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고 어느 테이블이 드리븐 테이블이 될지 알 수 없다.

mysql > SELECT *
		FROM employees e, dept_no de, departments d
        WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

일반적으로 조인을 하기 위한 컬럼들의 인덱스 여부로 조인의 순서가 결정되며 조인 컬럼의 인덱스에 아무런 문제가 없는 경우에는 레코드가 적은 테이블을 드라이빙으로 선택한다.

하지만 이 쿼리의 조인 순서를 변경하려는 경우에는 STRAIGHT_JOIN 힌트를 사용할 수 있다. 인덱스 힌트는 사용할 위치가 정해져 있으므로 주의하자.

mysql > SELECT STRAIGHT_JOIN
 	   e.first_name, e.last_name, d.dept_name
       FROM employees e, dept_emp de, departments d
       WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로(employees -> dept_emp -> departments) 조인을 수행하도록 유도한다.

주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

• 임시 테이블과 일반 테이블의 조인: 이 경우에는 거의 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다. 일반 테이블의 조인 칼럼에 인덱스가 없는 경우에는 레코드 건수가 작은 쪽을 먼저 읽도록 드라이빙으로 선택하는 것이 좋은데, 대부분 옵티마이저가 적절한 조인 순서를 선택하기 때문에 쿼리를 작성할 때부터 힌드를 사용할 필요가 없다. 옵티마이저가 실행 계획을 제대로 수립하지 못해서 심각한 성능 저하가 있는 경우에는 힌트를 사용하면 된다.
• 임시 테이블끼리 조인: 임시 테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관하므로 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋다.

위에서 언급한 레코드 건수는 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 그 조건을 만족하는 레코드 건수를 의미할 뿐, 무조건 테이블 전체의 레코드 건수를 의미하지 않는다.

STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음이 있다.

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

JOIN_FIXED_ORDER 옵티마이저 힌트는 STRAIGHT_JOIN 힌트와 동일한 효과를 낸다. STRAIGHT_JOIN 힌트는 한 번 사용되면 FROM 절의 모든 테이블에 대해 조인 순서가 결정되는 효과를 낸다. 하지만 나머지 3개의 옵티마이저 힌트는 STRAIGHT_JOIN과 달리 일부 테이블의 조인 순서에 대해서만 제한한다.

인덱스 힌트

조인의 순서를 변경하는 것으로 다음으로 자주 사용되는 것이 인덱스 힌트인데 STRAIGHT_JOIN 힌트와는 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다. 대체로 MySQL 옵티마이저는 어떤 인덱스를 사용해야 할지를 무난하게 잘 선택하는 편이다. 하지만 3~4개 이상의 컬럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우에는 가끔 옵티마이저가 실수를 하는데 이런 경우에는 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.

인덱스 힌트는 크게 다음과 같이 3종류가 있다. 3 종류의 인덱스 모두 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용하며, 괄호 안에 아무것도 없거나 존재하지 않는 인덱스 이름을 사용할 경우에는 쿼리의 문법 오류로 처리한다.

• USE INDEX: MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트이다. 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만, 항상 그 인덱스를 사용하는 것은 아니다.
• FORCE INDEX: USE INDEX보다 옵티마이저에 미치는 영향이 강한 힌트이다.
• IGNORE INDEX: USE INDEX와 FORCE INDEX와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트다. 때로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용한다.

위 3종류의 인덱스 힌트 모두 용도를 명시해 줄 수 있다.

• USE INDEX FOR JOIN: MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트이다. 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만, 항상 그 인덱스를 사용하는 것은 아니다.
• USE INDEX FOR ORDER BY: MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트이다. 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만, 항상 그 인덱스를 사용하는 것은 아니다.
• USE INDEX GROUP BY: MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트이다. 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만, 항상 그 인덱스를 사용하는 것은 아니다.

SQL_CALC_FOUND_ROWS

옵티마이저 힌트

옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나뉜다.

• 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
• 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
• 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
• 글로벌(쿼리 전체) : 전체 쿼리에 대해서 영향을 미치는 힌트

이 구분으로 인해 힌트의 사용 위치가 달라지는 것은 아니다. 그리고 힌트에 인덱스 이름이 명시 될 수 있는 경우를 인덱스 수준의 힌트로 구분하고 테이블 이름까지만 명시될 수 있는 경우를 테이블 수준의 힌트로 구분한다. 또한 특정 힌트는 테이블과 인덱스의 이름을 모두 명시할 수도 있지만 인덱스의 이름을 명시하지 않고 테이블 이름만 명시할 수도 있는데 이런 경우는 인덱스 테이블 수준의 힌트가 된다.

---

기타 사진