[MySQL] 옵티마이저의 데이터 처리 방식

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

쿼리 실행 절차

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

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

이 중 두 번째 단계가 "최적화 및 실행 계획 수립" 단계로 옵티마이저가 처리합니다. 이 단계에서는 SQL 파스 트리를 참조해서 다음과 같은 내용을 처리합니다.

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

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

현재 대부분의 DBMS는 비용 기반 최적화를 사용합니다. 비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 통계 정보를 이용해 실행 계획별 비용을 산출합니다. 이 중 비용이 최소인 처리 방식을 선택하여 최종적으로 쿼리를 실행합니다.

기본 데이터 처리

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

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 것을 의미합니다. 풀 테이블 스캔은 다음과 같은 조건에서 선택됩니다.

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

풀 테이블 스캔

• 테이블 전체를 읽어들이면서 상당히 많은 디스크 읽기가 필요
• InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 시작
• 리드 어헤드란 앞으로 필요한 데이터를 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것을 의미
• 처음 몇 개의 데이터 페이지는 포그라운드 스레드, 특정 시점부터는 백그라운드 스레드가 읽기 작업을 수행
• 백그라운드 스레드는 4개 또는 8개씩 페이지를 읽으며 계속 수를 증가시켜 최대 64개의 페이지까지 읽어 버퍼 풀에 저장
• 포그라운드 스레드는 버퍼 풀에서 준비된 데이터를 가져다 사용하여 빠른 처리가 가능
  

풀 인덱스 스캔

• 풀 테이블 스캔과 동일하게 리드 어헤드를 사용
• 풀 인덱스 스캔은 주로 인덱스 테이블에 존재하는 컬럼만으로 처리가 가능한 경우 이루짐
• 용량이 작은 인덱스를 선택하여 디스크 읽기 횟수를 줄일 수 있음
  

병렬 처리

MySQL 8.0부터는 하나의 쿼리를 여러 스레드가 동시에 처리하는 병렬 처리가 가능해졌습니다.

innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있습니다. MySQL 8.0 버전에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있습니다.

ORDER BY 처리(Using filesort)

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

모든 정렬이 "Filesort"라는 정렬 작업을 거칠 필요는 없지만, 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하는 것은 거의 불가능합니다.

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

MySQL 서버에서 "Filesort"를 이용하여 정렬 처리가 이루어졌는지 확인하려면 실행 계획의 Extra 컬럼에 "Using Filesort" 메시지가 표시되는지 확인하면 됩니다.

소트 버퍼

소트 버퍼(Sort Buffer)는 MySQL이 정렬을 수행하기 위해 할당받은 별도의 메모리 공간입니다. 정렬이 필요한 경우 할당되며, 정렬해야 할 레코드의 크기에 따라 가변적으로 크기가 변경됩니다. 소트 버퍼의 최대 크기는 sort_buffer_size라는 시스템 변수를 따릅니다. 쿼리의 실행이 완료되면 소트 버퍼를 위한 메모리 공간은 즉시 시스템으로 반납됩니다.

정렬해야 할 레코드가 소량이어서 메모리에 할당된 소트 버퍼만으로 충분한 경우 아주 빠르게 정렬이 처리됩니다. 하지만 레코드의 건수가 소트 버퍼로 할당된 공간보다 큰 경우 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리합니다. 이 과정에서 임시 저장을 위해 디스크를 사용하게 됩니다.

소트 버퍼에서 정렬을 수행하고, 결과를 임시로 디스크에 기록해 둡니다. 그리고 다음 레코드를 가져와서 다시 정렬을 수행하고 디스크에 임시 저장하는 과정을 반복합니다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하며 이 작업을 멀티 머지(Multi-merge)라고 표현합니다.

이 작업들이 디스크 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 반복 작업 횟수가 많아집니다. sort_buffer_size의 값을 크게 설정하면 메모리에서 모두 처리하여 빨라질 것으로 예상할 수 있지만, 성능이 무조건 빨라지는 것은 아니라고 합니다.

또한, 소트 버퍼는 세션 메모리 영역에 해당하여 여러 클라이언트가 공유할 수 없는 영역입니다. 따라서 커넥션이 많아질수록 소트 버퍼로 소비되는 메모리 공간이 커지며 운영체제가 메모리 부족 현상을 겪을 수 있습니다. 운영체제의 메모리 공간이 부족한 경우 OOM-Killer가 프로세스를 강제 종료할 것이며, 이 경우 가장 많은 메모리를 사용하는 MySQL 서버가 강제 종료 1순위가 될 수 있습니다.

정렬 알고리즘

레코드 정렬 시 레코드 전체를 소트 버퍼에 담을지, 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 MySQL 서버는 3가지 정렬 방식을 가집니다.

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

Real MySQL 저자는 이 중 첫 번째 방식을 투 패스, 다른 두 방식을 싱글 패스 방식으로 명명하고 있습니다.

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼 전부를 담아 정렬을 수행하는 정렬 방식입니다. 모든 컬럼을 소트 버퍼에 담아 정렬을 수행하고, 정렬이 완료되면 버퍼의 내용을 그대로 클라이언트로 넘겨줍니다.

투 패스 정렬 방식

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

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

최신 버전의 MySQL에서는 싱글 패스 정렬 방식을 주로 사용하며, 다음의 경우 투 패스 정렬 방식을 사용합니다.

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

정렬 처리 방법

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

옵티마이저가 정렬 처리를 위해 인덱스를 이용할 수 있다면 인덱스를 읽어서 결과를 반환합니다. 그렇지 않다면 WHERE 조건에 일치하는 레코드를 검색해 소트 버퍼에 저장하면서 정렬을 처리합니다.

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 다음의 조건을 만족해야 합니다.

• 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 합니다.
• WHERE절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 합니다.
• B-Tree 계열의 인덱스에서만 사용가능합니다.
• 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 방식의 조인에서만 사용할 수 있습니다.

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

일반적으로 조인이 수행되면 결과 레코드 건수와 레코드 크기가 늘어납니다. 따라서 조인 실행 전에 첫 번째 테이블(드라이빙 테이블)의 레코드를 먼저 정렬한 다음 조인을 실행하면 조금 더 빨리 정렬을 처리할 수 있습니다. 이 방식의 경우 첫 번째 테이블의 컬럼만으로 ORDER BY 절을 작성해야 합니다.

select * from emplyees e, salaries s
where s.emp_no = s.emp_no
and e.emp_no between 1 and 10
order by e.last_name;

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하고, "조인의 드라이빙 테이블만 정렬"하는 것 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 정렬합니다. 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 방법입니다.

select * from emplyees e, salaries s
where s.emp_no = s.emp_no
and e.emp_no between 1 and 10
order by s.salary;

ORDER BY 절의 정렬 기준 컬럼이 드리븐 테이블에 있는 컬럼이기 때문에 조인된 데이터를 가지고 정렬할 수밖에 없습니다.

쿼리 처리 방식

스트리밍 방식

• 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송해주는 방식입니다. 가장 마지막 레코드를 언제 받을 수 있을지는 알 수 없지만 첫 번째 레코드는 요청하고 곧바로 받을 수 있습니다.
• 스트리밍 방식에서는 LIMIT처럼 결과 건수를 제한하는 조건들이 쿼리의 전체 실행 시간을 상당히 줄여 줄 수 있습니다.
  

버퍼링 방식

• ORDER BY나 GROUP BY 같은 처리는 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해야하기 때문에 스트리밍되는 것이 불가능합니다.
• MySQL 서버에서 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무 것도 하지 못하고 기다려야 합니다. 이 방식을 버퍼링(Buffering)이라고 합니다.
  
  

GROUP BY 처리

GROUP BY 또한 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나입니다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없기 때문에 HAVING 절 튜닝을 고민할 필요는 없습니다.

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

조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑하면 인덱스를 읽어 작업을 수행하고 그 결과로 조인을 처리합니다. 이 방식을 사용하는 경우 Extra 컬럼에 별도의 코멘트가 표시되지 않습니다.

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

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

루스 인덱스 스캔은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있으며, 프리픽스 인덱스는 사용할 수 없습니다. 인덱스 스캔과는 달리 유니크한 값의 수가 적을수록 성능이 향상됩니다. 즉, 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어냅니다.

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

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

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

DIDSTINCT 처리

DISTINCT는 집합 함수와 함께 사용되는 경우와 그렇지 않은 경우의 영향을 미치는 범위가 서로 다릅니다. 또한, DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요합니다.

SELECT DISTINCT

SELECT DISTINCT는 GROUP BY와 동일한 방식으로 처리되며, SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 컬럼에 영향을 미칩니다. 이 때의 DISTINCT는 SELECT 절에 사용된 모든 컬럼의 조합이 유니크한 레코드를 조회합니다.

집합 함수와 함께 사용된 DISTINCT

COUNT(), MIN(), MAX() 같은 집합 함수 내에서 DISTINCT가 사용되는 경우 SELECT DISTINCT와는 다른 형태로 해석됩니다. 집합 함수의 DISTINCT는 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들을 가져옵니다.

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 1 and 10;

위의 쿼리는 내부적으로 임시 테이블을 사용하지만 실행 계획에서 "Using temporary"를 표시하지 않습니다. employees와 salaries 테이블을 조인한 결과에서 salary와 last_name을 저장하기 위해 각각 2개의 임시테이블이 만들어집니다. 이때 각 임시 테이블의 salary, last_name 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아지면 상당히 느려질 수 있습니다.

반면 인덱스된 컬럼에 대해 DISTINCT 처리를 수행하면 임시 테이블 없이 최적화된 처리를 수행할 수 있습니다.

select counnt(distinct emp_no) from employees;

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블(Internal temporary table)을 사용합니다. 임시 테이블은 처음에는 메모리에 생성됐다가 크기가 커지면 디스크로 옮겨지게 됩니다. 이후 쿼리의 처리가 완료되면 자동으로 삭제됩니다.

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

MySQL 8.0 버전부터 메모리는 TempTable이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용합니다.

8.0 이전 버전에서 메모리 임시 테이블을 위해 사용한 MEMORY 스토리지 엔진과 디스크 임시 테이블을 위해 사용한 MyISAM 스토리지 엔진은 다음과 같은 문제점을 가지고 있었습니다.

• MEMORY 스토리지 엔진은 가변 길이 타입을 지원하지 않아 메모리 낭비가 심함
• MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못함
  
  

TempTable이 최대 사용 가능한 메모리 공간의 크기는 temptable_max_ram 시스템 변수로 제어가능하며, 기본값은 1GB입니다. 이를 초과하는 경우 MySQL 서버는 다음 2가지 디스크 저장 방식 중 하나를 선택합니다.

• MMAP 파일로 디스크에 기록
• InnoDB 테이블로 기록
  
  

어디에 기록할지는 temptable_use_mmap 시스템 변수로 제어할 수 있으며, 기본값은 on 즉, TempTable 크기가 1GB를 넘으면 MMAP 파일로 전환됩니다. MMAP 파일이 기본값인 이유는 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문이라고 합니다.

하지만 처음부터 디스크 테이블로 생성되는 경우 internal_tmp_disk_storage_engine 시스템 변수에 의해 제어되며 기본값은 InnoDB입니다.

임시 테이블이 필요한 쿼리

임시 테이블을 생성하는 대표적인 패턴의 쿼리는 다음과 같습니다.

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

쿼리의 실행 계획에서 임시 테이블을 사용하는지 여부는 Extra 컬럼에 "Using temporary" 코멘트를 확인하면 됩니다. "Using temporary"가 표시되지 않는 경우에도 임시 테이블을 사용하는 경우가 있으며 위의 예에서 마지막 3개 패턴이 그러한 예입니다.

마지막 패턴의 경우 유니크 인덱스가 없는 내부 임시 테이블이 만들어집니다. 나머지 네 개의 쿼리 패턴은 유니크 인덱스를 가진 내부 임시 테이블이 생성되며 그렇지 않은 쿼리보다 처리 성능이 상당히 느립니다.

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

내부 임시 테이블은 기본적으로 메모리에 생성되지만 다음의 조건에서 메모리 임시 테이블을 사용하지 못합니다.

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

참고

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