09. 옵티마이저와 힌트-1

쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장됐는지 통계 정보를 참조하고 기본 데이터를 비교하여 최적의 실행 계획을 수립하는 작업이 필요하다.

MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당한다.

 

EXPLAIN: MySQL 서버에서 쿼리의 실행 계획을 확인하는 명령어

9.1 개요

쿼리의 실행 절차

1. SQL 파싱(Parsing) - SQL 파서(Parser)

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

  - SQL 문장에 문법적 오류가 있다면 이 단계에서 걸러진다.

  - 최종적으로 SQL 파스 트리(Parse Tree)가 생성된다.

  - 이후부터 SQL 문장 자체가 아닌 파스 트리를 이용해 쿼리를 실행한다.

 

2. 최적화 및 실행 계획 수립 - 옵티마이저

  - SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 최종적으로 쿼리의 실행 계획이 만들어진다.

  - 불필요한 조건 제거 및 복잡한 연산의 단순화

  - 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정

  - 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정

  - 가져온 레코드를 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

 

3. 두 번째 단계에서 수립된 실행 계획대로 스토리지 엔진에서 데이터를 가져와 조인하거나 정렬하는 작업을 수행한다.

 

1,2번은 거의 MySQL 엔진에서 처리하며 3번은 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

옵티마이저의 종류

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다.

 

비용 기반 최적화(Cost-based optimazer, CBO)

현재 대부분의 DBMS가 선택하고 있는 방법

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

 

규칙 기반 최적화 방법(Rule-based optimizer, RBO)

예전 초기 버전의 오라클 DBMS에서 많이 사용했던 방법

기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다. 이 방식에서는 통계 정보(테이블의 레코드 건수, 컬럼값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 도출한다. 하지만 사용자의 데이터 분포도가 매우 다양하기 때문에 오래전부터 거의 사용하지 않는 방법이 되었다.

 

규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이다. 현재는 대부분의 DBMS가 비용 기반의 옵티마이저를 채택하고 있다.

9.2 기본 데이터 처리

MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등 기본 데이터 가공 기능을 가지고 있다. 결과물은 동일하더라도 RDBMS별로 그 결과를 만들어내는 과정은 천차만별이다.

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

인덱스 처리 -> 8장 '인덱스'

 

풀 테이블 스캔

인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미한다.

 

MySQL 옵티마이저가 풀 테이블 스캔을 선택하는 조건

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

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

MyISAM 스토리지 엔진에는 맞는 이야기지만 InnoDB 스토리지 엔진은 다르다.

 

InnoDB 스토리지 엔진은 특정 테이블의 연속된 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다.

• 리드 어헤드(Read ahead): 어떤 영역의 데이터가 앞으로 필요해질지 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것
• innodb_read_ahead_threshold: InnoDB 스토리지 엔진이 언제 리드 어페드를 시작할지 임계값 설정

포그라운드 스레드가 innodb_read_ahead_threshold 개수만큼 연속된 데이터 페이지를 읽으면 InnoDB 스토리지 엔진이 백그라운드 스레드를 이용해 대량으로 그 다음 페이지들을 읽어서 버퍼 풀에 적재한다. 백그라운드 스레드가 읽기를 넘겨받는 시점부터 한 번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시킨다. 한 번에 최대 64개 데이터 페이지까지 읽어서 버퍼 풀에 저장해둔다. 포그라운드 스레드는 미리 버퍼 풀에 준비해둔 데이터를 가져다 사용하기만 하면 되므로 쿼리를 빠르게 처리할 수 있다.

일반적으로 디폴트 설정으로도 충분하지만 데이터 웨어하우스용으로 MySQL을 사용한다면 innodb_read_ahead_threshold 옵션을 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되도록 하는 것도 좋은 방법이다.

 

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

 

mysql> SELECT COUNT(*) FROM employees;

 

이 쿼리는 아무런 조건 없이 employees 테이블의 레코드 건수를 조회하고 있으므로 당연히 풀 테이블 스캔을 실행할 것처럼 보인다. 하지만 실제 실행 계획은 풀 인덱스 스캔을 하게 될 가능성이 높다. 단순히 레코드 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문이다. 일반적으로 인덱스는 테이블의 2~3개 컬럼만으로 구성되기 때문에 테이블 자체보다 용량이 작아서 훨씬 빠른 처리가 가능하다. 

 

하지만 다음과 같이 레코드만 있는 컬럼이 필요한 쿼리의 경우, 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔을 한다.

 

mysql> SELECT * FROM employees;

병렬 처리

MySQL 8.0 버전부터 용도가 한정되긴 하지만 처음으로 MySQL 서버에서도 쿼리의 병렬처리가 가능해졌다.

• 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것
• innodb_parallel_read_threads: 하나의 쿼리를 최대 몇 개의 스레드를 통해 처리할지 설정
• 아직 MySQL 서버는 쿼리를 여러 개의 스레드로 병렬 처리하게 하는 힌트나 옵션은 없다.
• 8.0 버전에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬 처리할 수 있다.

 

병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 줄어드는 것을 확인할 수 있다.

하지만 병렬 처리용 스레드 개수를 아무리 늘려도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.

ORDER BY 처리 (Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다. 데이터 웨어하우스처럼 대량의 데이터를 조회해서 일괄 처리하는 기능이 아니라면 아마도 레코드 정렬 요건은 대부분의 조회 쿼리에 포함돼 있을 것이다.

 

정렬을 처리하는 방법

1. 인덱스 이용
   • SELECT 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.
   • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.
   • 인덱스 때문에 디스크 공간이 더 많이 필요하다.
   • 인덱스 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.
2. filesort 이용
   • 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다.
   • 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort 가 처리되므로 충분히 빠르다.
   • 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

레코드를 정렬하기 위해 항상 "Filesort"라는 정렬 작업을 거쳐야 하는 것은 아니다. 하지만 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능하다.

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

실행 계획의 Extra 컬럼에 "Using filesort" 메세지가 표시되면 인덱스를 이용하지 않고 별도의 정렬 처리를 수행한 것이다.

앞으로는 MySQL의 정렬이 어떻게 처리되는지 알아보자.

소트 버퍼

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

정렬해야 할 레코드가 아주 소량이어서 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 정렬이 처리될 것이다. 하지만 정렬해야 할 레코드 건수가 소트 버퍼로 할당된 공간보다 크다면 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.

1. 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해둔다.
2. 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다.
3. 각 버퍼 크기만큼 정렬된 레코드 조각들을 다시 병합하면서 정렬을 수행한다. => 멀티 머지(Multi-merge)

수행된 멀티 머지 횟수는 Sort_merge_passes 라는 상태 변수(SHOW STATUS VARIABLES 명령 참조)에 누적해서 집계된다. 위 작업들은 모두 디스크 쓰기/읽기를 유발하며 레코드 건수가 많을수록 반복 작업의 횟수가 많아진다. 

 

소트 버퍼 크기에 따른 쿼리 성능 변화

소트 버퍼(sort_buffer_size)를 크게 설정하면 메모리에서 모두 처리되니 빨라질 것으로 예상하지만 실제 벤치마크 결과로는 큰 차이를 보이진 않았다. 리눅스 계열 운영체제에서는 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수도 있다.

 

일반적인 트랜잭션 처리용 MySQL 서버의 소트 버퍼 크기는 56KB에서 1MB 미만이 적절하다. MySQL은 글로벌 메모리 영역과 세션(로컬) 메모리 영역을 가지는데, 정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역에 해당한다. 즉, 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아니다. 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커진다. 소트 버퍼의 크기를 10MB 이상으로 설정하면 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시에 실행되면서 운영체제는 메모리 부족 현상을 겪을 수도 있다. 더는 메모리 여유 공간이 없는 경우에는 운영체제의 OOM-Killer가 여유 메모리를 확보하기 위해 프로세스를 강제로 종료할 것이다. OOM-Killer는 메모리를 가장 많이 사용하는 프로세스를 강제 종료하기 때문에 일반적으로 메모리를 가장 많이 사용하는 MySQL 서버가 강제 종료 1순위가 된다.

 

참고: 소트 버퍼를 크게 설정하면 디스크의 읽기/쓰기 사용량은 줄일 수 있다. 그래서 MySQL 서버의 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼 크기를 크게 설정하는 것이 도움이 될 수도 있다. 하지만 너무 크게 설정하면 위의 언급대로 MySQL 서버가 메모리 부족을 겪을 수 있기 때문에 적절히 설정하는 것이 좋다. 대량 데이터의 정렬이 필요한 경우 해당 세션의 소트 버퍼만을 일시적으로 늘려줘서 쿼리를 실행하고 다시 줄이는 것도 좋은 방법이다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스(Single-pass)와 투 패스(Two-pass) 2가지 정렬 모드로 나눌 수 있다. (정식 명칭은 아님)

정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지는 옵티마이저 트레이스 기능으로 확인할 수 있다.

 

정렬 모드 확인

옵티마이저 트레이스

 

"filesort_summary" : { ...

"sort_algorithm": 정렬 알고리즘

"sort_mode": <sort_key, rowid> | <sort_key, additional_fields> | <sort_key, packed_additional_fields>

}

 

MySQL 서버의 정렬 방식(sort mode)

• <sort_key, rowid>: 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식 
• <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식. 레코드의 컬럼들은 고정 사이즈로 메모리 저장
• <sort_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식. 레코드의 컬럼들은 가변 사이즈로 메모리 저장 (5.7버전부터 메모리 공간의 효율적 사용을 위해 추가로 도입)

첫 번째 방식을 투 패스 정렬 방식, 두 번째와 세 번째 방식을 싱글 패스 정렬 방식이라고 명명했다. 

 

참고: "sort_algorithm" 필드의 "std::stable_sort"는 MySQL 서버에서 실제 정렬을 수행할 때 사용한 라이브러리의 함수 이름을 보여준다. "std::stable_sort"는 C++의 STL(Standard Template Library)의 stable_sort() 함수를 사용했음을 의미한다.

 

싱글 패스 정렬 방식

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

 

-- // first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT
mysql> SELECT emp_no, first_name, last_name FROM employees ORDER BY first_name;

 

싱글 패스(Single-pass) 정렬 방식

 

1. 처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 컬럼 last_name 까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다.
2. 정렬이 완료되면 소트 버퍼의 내용을 그대로 클라이언트로 넘겨준다.

 

투 패스 정렬 방식

정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 컬럼을 가져오는 방식

싱글 패스 정렬 방식이 도입되기 전부터 사용되었으며, 특정 조건에서는 여전히 투 패스 정렬 방식을 사용한다.

 

투 패스(Two-pass) 정렬 방식

 

1. 처음 employees 테이블을 읽을 때는 정렬에 필요한 first_name 컬럼과 프라이머리 키인 emp_no 키만 읽어서 정렬을 수행한다.
2. 정렬이 완료되면 그 결과 순서대로 employees 테이블을 한 번 더 읽어서 last_name 을 가져오고,
3. 최종적으로 결과를 클라이언트 쪽으로 넘겨준다.

 

MySQL의 예전 정렬 방식인 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리하지만, 새로운 정렬 방식인 싱글 패스는 이런 불합리가 없다. 하지만 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요하다. 

 

최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용한다. 다음의 경우에는 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용한다. (정렬에 필요한 컬럼 먼저 읽기 -> 나머지 SELECT 컬럼 읽기)

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

싱글 패스 방식은 정렬 대상 레코드 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.

 

참고: SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고, 모든 컬럼(*)을 가져오도록 개발할 때가 많다. 하지만 이는 정렬 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 크다. 특히 정렬이 필요한 SELECT는 불필요한 컬럼을 SELECT 하지 않게 쿼리를 작성하는 것이 효율적이다. 이는 소트 버퍼에만 영향을 미치는 것이 아니라 임시 테이블이 필요한 쿼리에서도 영향을 미친다. -> 9.2.6절 '내부 임시 테이블 활용'

정렬 처리 방법

쿼리에 ORDER BY 가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리된다. 일반적으로 아래쪽으로 갈수록 처리 속도가 떨어진다.

 

 

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

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

일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 1번이 효율적이다.

 

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는

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

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

다음 예제처럼 ORDER BY가 있든 없든 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력된다. ORDER BY 절이 없어도 정렬이 되는 이유는 employees 테이블의 프라이머리 키를 읽고, 그 다음에 salaries 테이블을 조인했기 때문이다.

 

 

 

1. WHERE 절 조건 e.emp_no BETWEEN 100002 AND 100020 에 해당하는 employess 테이블의 인덱스를 순서대로 읽어서 반환
2. 1번의 결과를 순서대로 읽으면서 salaries 테이블과 조인하여 최종 결과 반환

 

참고: 인덱스를 이용한 정렬과 ORDER BY

ORDER BY 절을 넣지 않아도 자동으로 정렬되므로 ORDER BY 절 자체를 쿼리에서 완전히 제거해서 쿼리를 작성하기도 한다. MySQL 서버는 정렬을 인덱스로 처리할 수 있는 경우 부가적으로 불필요한 정렬 작업을 수행하지 않는다. 그래서 인덱스로 정렬이 처리될 때는 ORDER BY 가 쿼리에 명시된다고 해서 작업량이 더 늘지 않는다.

또한, 어떤 이유로 쿼리의 실행 계획이 조금 변경된다면 ORDER BY 가 명시되지 않아서 기대하지 않은 순서로 실행되어 애플리케이션의 버그로 연결될 수 있다. 따라서 일부러 ORDER BY를 제거하는 선택은 권장하지 않는다.

 

조인이 네스티드-루프 방식으로 실행되기 때문에 조인 때문에 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다. 하지만 조인이 사용된 쿼리의 실행 계획에 조인 버퍼(Join Buffer)가 사용되면 순서가 흐트러질 수 있기 때문에 주의해야 한다.

 

조인의 드라이빙 테이블만 정렬 ( 소트 버퍼 )

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어난다.

그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다. 이 방법으로 정렬이 처리되려면 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 절을 작성해야 한다.

 

mysql> SELECT * 
	FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no				/* 조인 */
	 AND e.emp_no BETWEEN 100002 AND 100010		/* 검색 */
	ORDER BY e.last_name;				/* 정렬 */

 

WHERE 절이 다음 2가지 조건을 가지고 있어 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다.

• WHERE 절의 검색 조건("e.emp_no BETWEEN 100002 AND 100010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
• 드리븐 테이블(salaries)의 조인 컬럼인 emp_no 컬럼에 인덱스가 있다.

검색(AND 이하)은 인덱스 레인지 스캔으로 처리할 수 있지만 ORDER BY 절에 명시된 컬럼(e.last_name)이 employees 테이블의 프라이머리 키가 아니므로 인덱스를 이용한 정렬은 불가능하다. 

그래서 옵티마이저는 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블(employees)의 컬럼이므로 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한 것이다.

1. 인덱스를 이용해 "e.emp_no BETWEEN 100002 AND 100010" 조건을 만족하는 9건 검색
2. 검색 결과를 last_name 컬럼으로 정렬을 수행 (Filesort - 소트 버퍼)
3. 소트 버퍼의 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴 (그림의 오른쪽 번호는 레코드가 조인되어 출력되는 순서를 의미)

조인의 첫 번째(드라이빙) 테이블만 정렬

 

임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다. "조인의 드라이빙 테이블만 정렬"은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않는다.

그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다. 이 방법은 정렬의 3가지 방법 중 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.

 

다음 쿼리는 "드라이빙 테이블만 정렬"의 예제와 ORDER BY 컬럼을 제외하고 동일하다.

정렬 기준 컬럼이 드리븐(두 번째로 읽는) 테이블의 컬럼으로 바뀌었다. 즉, salaries 읽어야 정렬을 수행할 수 있기 때문에 조인된 데이터를 가지고 정렬할 수 밖에 없다.

 

mysql> SELECT * 
	FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no				/* 조인 */
	 AND e.emp_no BETWEEN 100002 AND 100010		/* 검색 */
	ORDER BY s.salary;				/* 정렬 */

 

 

Extra: "Using where; Using temporary; Using filesort"

=> 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미

 

 

1. 검색

2. 조인 -> 결과를 임시 테이블에 저장

3. 정렬 (in 임시 테이블)

 

정렬 처리 방법의 성능 비교

웹 서비스용 쿼리에서는 ORDER BY 와 함께 LIMIT이 거의 필수로 사용되는 경향이 있다. LIMT은 테이블이나 처리 결과의 일부만 가져오기 때문에 작업량이 줄이는 역할을 한다. 하지만 ORDER BY 나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 일단 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야 LIMIT으로 건수를 제한할 수 있다. WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY 나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.

 

쿼리 처리 방식

• 스트리밍(Streaming) 처리
• 버퍼링(Buffering) 처리

 

스트리밍 방식

• 서버에서 처리할 데이터가 얼마인지 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송해주는 방식
• 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트에게 스트리밍 된다.
• 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다. 그때도 요청한 쿼리를 실행 중이기 때문에 가장 마지막 레코드는 언제 받을지 알 수 없다. 
• LIMIT 처럼 결과 건수를 제한하는 조건으로 반환하는 레코드 건수가 줄어들면 쿼리 전체 실행 시간을 줄일 수 있다.
• ex) 인덱스를 이용한 정렬

스트리밍 처리

 

버퍼링 방식

• ORDER BY 나 GROUP BY 조건은 스트리밍 처리가 불가능하다. 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.
• 스토리지 엔진으로부터 모든 레코드를 가져와서(버퍼링) MySQL 서버에서 일괄 가공
• LIMIT 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다. (네트워크로 전송되는 레코드 건수는 줄일 수 있음)
• ex) 조인에서 드라이빙 테이블만 정렬, 조인에서 조인 결과를 임시 테이블로 저장 후 정렬

버퍼링 처리

 

 

예) 조인과 ORDER BY절, LIMIT이 사용된 경우

 

mysql> SELECT *
	FROM tb_test1 t1, tb_test2 t2
	WHERE t1.col1=t2.col1
	ORDER BY t1.col2
	LIMIT 10;

 

• tb_test1 테이블 레코드 100건, tb_test2 테이블 레코드 1,000건
• tb_test1 레코드 1건당 tb_test2 레코드 10건씩 존재한다고 가정 (1:10)
• 두 테이블의 조인 결과 1,000건
• t1의 col2 기준으로 정렬
• 결과는 10건만 반환

 

1. tb_test1가 드라이빙되는 경우

 

• ORDER BY 절의 t1.col2 컬럼에 대해 인덱스 사용이 가능한 경우: tb_test1 에서 가장 상위의 레코드 1건을 읽어서 tb_test2 의 레코드 10건과 조인 (1:10)
• 인덱스를 사용할 수 없고 ORDER BY 절의 t1.col2 이 드라이빙 테이블의 컬럼일 경우: tb_test1 테이블의 모든 레코드를 col2 컬럼에 대하여 정렬 -> 가장 상위의 레코드 1건과 tb_test2 의 레코드 10건 조인
• tb_test1 의 레코드를 1건씩 읽으며 tb_test2 과 조인 -> 조인 결과 1000건을 임시 테이블에 저장 -> col2 컬럼에 대하여 정렬 -> 정렬 결과에서 상위 레코드 10건 반환

 

2. tb_test2가 드라이빙 되는 경우

 

• ORDER BY 절의 t1.col2 컬럼에 대해 인덱스 사용이 가능한 경우: tb_test2 에서 가장 상위의 레코드 1건을 읽어서 tb_test2 의 레코드 10건과 조인 (1:10)
• tb_test2 테이블의 모든 레코드를 col1 컬럼에 대하여 정렬 -> 가장 상위의 레코드 1건과 tb_test2 의 레코드 1건 조인
• tb_test2 의 레코드를 1건씩 읽으며 tb_test1 과 조인 -> 조인 결과 1000건을 임시 테이블에 저장 -> col2 컬럼에 대하여 정렬 -> 정렬 결과에서 상위 레코드 10건 반환

 

어느 테이블이 먼저 드라이빙 되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다.

가능하다면 인덱스를 사용한 정렬로 유도하고, 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있다.

 

참고: 인덱스를 사용하지 못하고 별도로 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT 조건이 아무런 도움이 되지 않는 것은 아니다. 정렬할 대상 레코드가 1000건인 쿼리에 "LIMIT 10"이라는 조건이 있다면 MySQL 서버는 1000건의 레코드를 모두 정렬하는 것이 아니라 필요한 순서(ASC 또는 DESC)대로 정렬해서 상위 10건만 정렬이 채워지면 정렬을 멈추고 결과를 반환한다. 하지만 MySQL 서버는 정렬을 위해 퀵 소트와 힙 소트 알고리즘을 사용한다. 이는 상위 10건을 정렬하기 위해 더 많은 작업이 필요할 수도 있음을 의미한다.

결론적으로 인덱스를 사용하지 못하는 쿼리를 페이징 처리에 이용하는 경우 LIMIT으로 5~10건만 조회한다고 해서 쿼리가 기대만큼 아주 빨라지지는 않는다.

정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서 해당 작업의 실행 횟수를 상태 변수로 저장한다. 

정렬 관련 상태 변수는 'Sort'로 시작한다.

 

 

• Sort_merge_passes: 멀티 머지 처리 횟수
• Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
• Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
• Sort_rows: 지금까지 정렬한 전체 레코드 건수

풀 테이블 스캔의 결과를 1번(Sort_scan) 정렬

단위 정렬 작업의 결과를 13번(Sort_merge_passes) 병합 처리

전체 정렬된 레코드 건수는 300,024건(Sort_rows)

GROUP BY 처리

GROUP BY 또한 ORDER BY 처럼 쿼리를 스트리밍 처리 할 수 없다. GROUP BY 에 대한 조건절로 HAVING 절을 사용할 수 있다.

 

HAVING

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

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

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

조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 이미 인덱스가 있다면

1. 인덱스를 차례대로 읽으며 그루핑 작업 수행
2. 그 결과와 드리븐 테이블 조인

GROUP BY 가 인덱스를 사용해서 처리된다 하더라도 그룹 함수(Aggregation function) 등 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. GROUP BY 가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것으므로 쿼리 실행 시점에 추가적인 정렬 작업이나 내부 임시 테이블은 필요하지 않다.

이러한 그루핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 컬럼에 별도로 GROUP BY 관련 코멘트("Using index for group-by")나 임시 테이블 사용 또는 정렬 관련 코멘트("Using temporary. Using filesort")가 표시되지 않는다.

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

루스(Loose) 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다.

실행 계획 Extra: "Using index for group-by"

 

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

 

• salaries 테이블의 인덱스: (emp_no, from_date)

WHERE 조건은 인덱스의 첫 번째 컬럼 emp_no 에 대한 조건이 없기 때문에 인덱스 레인지 스캔을 이용할 수 없다. 

그러나 쿼리의 실행 계획을 보면 인덱스 레인지 스캔(range)을 이용했으며, Extra 컬럼의 메세지를 보면 GROUP BY 처리까지 인덱스를 사용했음을 알 수 있다.

 

 

1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) "10001"을 찾아낸다.
2. (emp_no, from_date) 인덱스에서 emp_no가 '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에서 사용된 "from_date='1985-03-01'" 조건을 합쳐서 "emp_no='10001' AND from_date='1985-03-01'" 조건으로 (emp_no, from_date) 인덱스를 검색하는 것과 거의 흡사하다.
3. (emp_no, from_date) 인덱스에서 emp_no 의 그 다음 유니크한(그룹 키) 값을 가져온다.
4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가 반복 수행한다.

MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 또한 프리픽스 인덱스(Prefix index, 컬럼값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없다.

인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면, 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. 즉, 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다. 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.

 

루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 WHERE 절의 조건이나 ORDER BY 절이 인덱스를 사용할 수 있을지 없을지 판단하는 것보다 어렵다.

 

루스 인덱스 스캔을 사용할 수 있는 쿼리 패턴

 

tb_test (col1, col2, col3) 에 대한 쿼리

 

루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴

 

tb_test (col1, col2, col3) 에 대한 쿼리

 

참고: 인덱스 스킵 스캔(Index Skip Scan)

MySQL 8.0 버전부터 도입된 루스 인덱스 스캔과 동일한 방식으로 작동하는 최적화 방식이다. 8.0 이전 버전까지는 GROUP BY 절의 처리를 위해서만 루스 인덱스 스캔이 사용됐지만, 인덱스 스킵 스캔을 통해 옵티마이저가 쿼리에서 필요로 하는 레코드를 검색하는 부분까지 인덱스 스캔 방식으로 최적화가 가능해졌다. 인덱스 스킵 스캔 또한 루스 인덱스 스캔처럼 조건이 누락된 인덱스의 선행 컬럼이 유니크한 값을 많이 가질수록 쿼리 처리 성능이 떨어진다. 그래서 선행 컬럼의 유니크한 값의 개수가 많으면 인덱스 스킵 스캔 최적화를 사용하지 않는다.

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

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

 

실행 계획 Extra: "Using temporary" 

 

mysql> 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;

 

이 실행 계획에서 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL)하기 때문이 아니라 인덱스를 전혀 사용할 수 없는 GROUP BY 이기 때문이다.

 

 

Extra 컬럼에 Using filesort 는 표시되지 않고 "Using temporary" 만 표시되었다.

8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑되는 컬럼을 기준으로 묵시적인 정렬까지 함께 수행했다. 그래서 예제 쿼리처럼 GROUP BY 는 있지만 ORDER BY 절이 없는 쿼리에 대해서는 기본적으로 그루핑 컬럼인 last_name 컬럼으로 정렬된 결과를 반환했다. 하지만 8.0 버전부터는 이같은 묵시적인 정렬은 더 이상 실행되지 않게 바뀌었다.

 

임시 테이블 생성

 

8.0 에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

 

TEMPORARY TABLE ( last_name, salary )

• last_name: GROUP BY(중복 제거) -> 유니크 인덱스
• salary: 집합 함수 연산

조인의 결과(WHERE)를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE 를 실행한다.

별도의 정렬 작업 없이 GROUP BY 가 처리된다.

 

참고: MySQL 5.7 버전까지는 GROUP BY가 사용되면 자동으로 그루핑 컬럼을 기준으로 정렬이 수행됐는데 정렬이 필요치 않은 경우라면 다음 쿼리와 같이 "ORDER BY NULL"을 추가로 사용할 것을 권장했다. "ORDER BY NULL"이 사용되면 MySQL 서버는 불필요한 추가 정렬 작업을 수행하지 않으므로 크진 않지만 성능 향상을 볼 수 있었다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. 집합 함수가 함께 사용되는 경우와 집합 함수가 없는 경우에 DISTINCT 키워드가 영향을 미치는 범위가 달라진다. 그리고 집합 함수와 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 하는 것이지, 특정 컬럼만 유니크하게 조회하는 것이 아니다. 다음 쿼리의 경우 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져온다.

 

mysql> SELECT DISTINCT first_name, last_name FROM employees;

 

DISTINCT 키워드는 SELECT 하는 모든 컬럼에 영향을 미친다.

집합 함수와 함께 사용된 DISTINCT

COUNT(DISTINCT ...), MIN(DISTINCT ...), MAX(DISTINCT ...) 등의 형태

이 경우 일반적인 SELECT DISTINCT 와 다른 형태로 해석된다.

• 집합함수가 없는 SELECT DISTINCT: 조회하는 모든 컬럼의 조합이 유니크한 것들만 가져옴
• 집합 함수 내에서 사용된 DISTINCT: 그 집합 함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져옴

 

mysql> EXPLAIN SELECT COUNT(DISTINCT s.salary)
	FROM employees e, salaries s
	WHERE e.emp_no=s.emp_no
	 AND e.emp_no BETWEEN 100001 AND 100100;

 

이 쿼리는 내부적으로 "COUNT(DISTINCT s.salary)" 를 처리하기 위해 임시 테이블을 사용한다. s.salary 는 인덱스 컬럼이 아니기 때문이다.

1. e.emp_no 에 대하여 인덱스 레인지 스캔으로 조회 범위를 줄임
2. salary 컬럼의 값만 저장할 임시 테이블 생성 ( UNIQUE INDEX ( salary ) )
3. employees 테이블과 salaries 테이블 조인
4. 조인 결과를 임시 테이블에 INSERT, UPDATE
5. COUNT() 함수 처리

실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않는다. 이 경우 MySQL 서버는 모든 버전에서 "Using temporary"를 표시하지 않고 있다. 

 

COUNT(DISTINCT e.last_name) 추가 

mysql> SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
	FROM employees e, salaries s
	WHERE e.emp_no=s.emp_no
	 AND e.emp_no BETWEEN 100001 AND 100100;

 

집합 함수 COUNT() 가 두 번 사용되지만 실행 계획은 위의 쿼리와 똑같이 표시된다.

하지만 내부적으로는 s.salary 컬럼 값을 저장하는 임시 테이블과 e.last_name 컬럼 값을 저장하는 또 다른 임시 테이블이 생성된다. (임시 테이블 2개 사용)

 

인덱스된 컬럼에 대한 DISTINCT 처리

 

 

인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있다.

 

참고: DISTINCT 가 집합 함수 없이 사용된 경우, 집합 함수 내에서 사용된 경우 쿼리의 결과가 조금씩 달라지기 때문에 그 차이를 명확하게 이해해야 한다.

 

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑 할 때 내부적인 임시 테이블(Internal temporary table)을 사용한다. 이것은 "CREATE TEMPORARY TABLE" 명령으로 만든 일반적인 임시 테이블과는 다르다. 

 

내부 임시 테이블

• 처음에는 메모리에 생성됐다가 테이블 크기가 커지면 디스크로 옮겨진다.
• 특정 예외 케이스의 경우 메모리를 거치지 않고 바로 디스크에 만들어지기도 한다.
• 다른 세션이나 다른 쿼리에서는 접근할 수 없고 쿼리 처리가 완료되면 자동으로 삭제된다.

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

원본 테이블의 스토리지 엔진과 관계없이 

 

메모리 임시 테이블

• MySQL 8.0 이전: MEMORY 스토리지 엔진
  • 가변 길이 타입 지원X
  • 가변 길이 타입의 경우 최대 길이만큼 메모리를 할당해서 사용 -> 메모리 낭비
  • tmp_table_size, max_heap_table_size: 메모리에 생성되는 내부 임시 테이블의 최대 크기 설정
• MySQL 8.0: TempTable 스토리지 엔진
  • 가변 길이 타입을 지원
  • internal_tmp_mem_storage_engine: 메모리용 임시 테이블을 MEMORY/TempTable 중에 선택
  • temptable_max_ram: TempTable 이 최대한 사용 가능한 메모리 공간 크기 (기본값: 1GB) -> 임시 테이블의 크기가 1GB보다 커지는 경우 메모리의 임시 테이블을 디스크로 기록한다.
  • temptable_use_mmap: 임시 테이블이 디스크로 기록될 때 ON(기본값)이면 MMAP 파일로 기록, OFF이면 InnoDB 테이블로 기록. 메모리의 TempTable은 MMAP 파일로 전환하는 편이 오버페드가 적다.

디스크 임시 테이블

• MySQL 8.0 이전: MyISAM 스토리지 엔진 사용. 트랜잭션 지원X
• MySQL 8.0: InnoDB 스토리지 엔진(또는 TempTable 스토리지 엔진의 MMAP 파일 버전) 사용. 트랜잭션 지원O

 

참고: MySQL 서버는 디스크의 임시 테이블을 생성할 때, 파일 오픈 직후 파일 삭제를 실행한다.(아직 삭제X) 그리고 데이터를 저장하기 위해 해당 임시 테이블을 사용한다. 이렇게 함으로써 MySQL 서버가 종료되거나 해당 쿼리가 종료되면 임시 테이블은 즉시 사라지게 보장된다. 그리고 MySQL 서버 내부의 다른 스레드 또는 외부의 사용자가 해당 임시 테이블 파일에 접근할 수 없게 된다.

이 같은 이유로 디스크에 저장된 임시 테이블 파일은 운영체제의 "dir" 또는 "ls -al" 같은 명령으로는 확인할 수 없다. 리눅스나 유닉스 계열의 운영체제에서는 "lsof -p `pidof mysqld`" 명령으로 임시 테이블이 몇 개가 사용되는지 정도는 확인할 수 있으나 파일의 상태는 "deleted"로 표시될 것이다. 그렇다고 임시 테이블의 사용이 완료된 것은 아니고 사용중이라는 뜻이다.

 

내부 임시 테이블이 처음부터 디스크 테이블로 생성되는 경우

: internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용된다. (기본값: InnoDB)

임시 테이블이 필요한 쿼리

다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 내부 테이블을 생성한다.

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

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

 

실행 계획에서 임시 테이블 사용 여부

어떤 쿼리가 실행 계획에서 임시 테이블을 사용하는 지는 Extra 컬럼에 "Using temporary" 메시지가 표시되는지 확인하면 된다. 하지만 해당 메세지가 표시되지 않을 때도 임시 테이블을 사용할 수 있는데, 위의 예에서 마지막 3개 패턴이 그러한 예다.

 

임시 테이블 유니크 인덱스 여부

1~4번째 쿼리 패턴: 유니크 인덱스를 가지는 내부 임시 테이블 생성

5번째 쿼리 패턴: 유니크 인덱스가 없는 내부 임시 테이블 생성. 처리 성능이 느리다.

 

참고: MySQL 8.0 이전 버전까지 UNION ALL이 사용된 쿼리(select_type 컬럼이 UNION RESULT 인 경우)도 항상 내부 임시 테이블을 사용해서 결과를 모은 후 결과를 반환했다. 하지만 8.0 버전부터 UNION ALL을 사용하는 쿼리는 더이상 임시 테이블을 사용하지 않게 개선되었다.

MySQL 서버에서 UNION은 DISTINCT를 생략한 것으로 판단하기 때문에 결국 UNION DISTINCT와 동일한 기능이다. 여러 결과 집합에서 중복을 제거하는 작업이 필요하기 때문에 임시 테이블을 이용한 중복 제거 작업이 필수적이다.

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

내부 임시 테이블은 기본적으로 메모리 상에 만들어지지만, 메모리 임시 테이블을 사용하지 못하고 디스크 기반의 임시 테이블을 사용해야 하는 경우가 있다.

• UNION 이나 UNION ALL 에서 SELECT 되는 컬럼 중에서 길이가 512바이트 이상인 컬럼이 있는 경우
• GROUP BY 나 DISTINCT 컬럼에서 512바이트 이상인 크기의 컬럼이 있는 경우
• 메모리 임시 테이블의 크기가 다음 설정 값보다 커지는 경우: tmp_table_size 또는 max_heap_table_size (MEMORY), template_max_ram (TempTable)

참고: MySQL 8.0.13 이전 버전까지는 BLOB나 TEXT 컬럼을 가진 경우, 임시 테이블을 메모리에 생성하지 못하고 디스크에 생성했다. 하지만 8.0.13 버전부터는 BLOB나 TEXT 컬럼을 가진 임시 테이블도 메모리에 임시 테이블을 생성할 수 있게 되었다. 하지만 메모리 임시 테이블이 TempTable 스토리지 엔진이 아닌 MEMORY 스토리지 엔진을 사용하는 경우에는 여전히 디스크 임시 테이블을 사용한다.

임시 테이블 관련 상태 변수

-- // 임시 테이블 관련 상태 변수 확인
SHOW SESSION STATUS LIKE 'Created_tmp%';

 

 

• Created_tmp_tables: 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값. 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지 구분하지 않고 모두 누적한다.
• Created_tmp_disk_tables: 디스크에 내부 임시 테이블이 만들어진 개수만 누적하는 상태 값