Real MySQL 9장 옵티마이저와 힌트 #1 (~9.2)

• MySQL 서버에서 옵티마이저는 각 테이블에 있는 데이터가 어떻게 분포되어있는지 등의 통계정보를 가지고 최적의 실행계획을 수립한다.

9.1 개요

9.1.1 쿼리 실행 절차

쿼리 실행 절차는 크게 세 단계로 나눌 수 있다.

1. SQL 문장을 분석하고 파스 트리를 만든다.
   • SQL 문장의 문법적인 오류를 발견한다.
2. 만들어진 파스트리를 확인해서 테이블과 인덱스를 어떤 식으로 읽을지를 선택한다. (최적화 및 실행 계획 수립)
   • 복잡한 연산을 단순화한다.
   • 조인이 있을 경우 읽는 순서를 정한다.
   • 인덱스 통계정보를 이용해 사용할 인덱스를 결정한다.
   • 가져온 레코드를 임시테이블에 넣고 다시 가공할지를 결정한다.
3. 수립된 실행계획에 따라 스토리지 엔진에서 읽어오도록 요청한다.
   • MySQL 엔진에서 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

9.1.2 옵티마이저의 종류

옵티마이저는 크게 두 가지로 나눌 수 있다.

• 규칙기반 최적화(RBO)
• 비용기반 최적화(CBO) : 통계정보 등을 사용하여 최소 비용을 산출
  • 요즘은 대부분의 RDBMS는 비용기반 최적화를 사용하고 있다.

9.2 기본 데이터 처리

• RDBMS 마다 데이터를 가공하는 방법은 천차만별이다.
• MySQL에서 데이터를 정렬, Grouping 등을 처리하는 방법을 설명한다.

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

• 풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어서 처리한다.
  • 다음의 상황에서 풀 테이블 스캔이 발생한다.
    • 테이블 레코드 건수가 너무 적어서 인덱스를 통해 읽는 것보다 테이블을 직접 읽는 것이 빠를 때
    • 인덱스를 사용할 적절한 Where, On 절 조건이 없을 때
    • 일치하는 데이터 건수가 너무 많을 때(통계 기준)
  • 풀 테이블 스캔 시 페이지 하나씩 읽는 것이 아니라 앞으로 필요할 것 같은 페이지를 미리 버퍼 풀에 읽어들인다. (리드 어헤드)
• 풀 인덱스 스캔 : 인덱스를 처음부터 끝까지 읽어서 처리한다.

select count(*) from employees;

• 위의 쿼리는 풀 테이블 스캔을 할 것처럼 보이지만 레코드 건수만 필요로 한다면 용량이 작고 빠른 인덱스를 선택한다.
• 그러나 다음과 같이 인덱스에 없는 테이블 레코드가 필요한 쿼리는 풀 테이블 스캔을 하게된다.

select * from employees;

9.2.2 병렬 처리

• MySQL 8.0 이후부터 하나의 쿼리에 대해 여러 스레드가 병렬 처리를 할 수 있게 되었다.
• innodb_parallel_read_threads 라는 변수로 하나의 쿼리를 최대 몇개의 스레드가 처리할지 설정할 수 있다.

9.2.3 ORDER BY 처리(Using filesort)

• 정렬 처리 방법의 두 가지
  1. 인덱스를 이용한 정렬
     • 장점 : 인덱스는 이미 정렬되어 있어서 조회가 매우 빠르다
     • 단점 : INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가, 삭제 작업이 필요해서 느리다.
  2. Filesort를 이용한 정렬
     • 장점 : 인덱스를 생성하지 않아도 되므로 INSERT, UPDATE,DELETE가 상대적으로 빠르다.
     • 단점 : 정렬 작업이 쿼리를 실행할 때 처리되어 느리다.
• 인덱스를 사용하지 못하는 경우
  • 정렬 기준이 너무 많아서 모두 인덱스를 생성할 수 없을 때
  • GROUP BY의 결과 또는 DISTINCT 같은 처리 결과를 정렬할 때
  • UNION의 결과 같이 임시 테이블의 결과를 다시 정렬할 때
  • 랜덤하게 결과 레코드를 가져올 때
• 실행계획을 살펴보면 Extra 컬럼에 Using filesort 메시지가 포함되면 Filesrot를 사용한지 알 수 있다.

9.2.3.1 소트버퍼

• MySQL에서 정렬을 수행하기 위해 만들어둔 메모리 공간이다.
• 최대 사용 가능한 소트 버퍼의 크기는 sort_buffer_size 변수로 설정할 수 있다.
• 만약 정렬해야 할 레코드 수가 소트버퍼 크기보다 크다면 소트버퍼에서 부분 정렬을 수행하고 디스크에 쓰고 다시 해당 버퍼만큼 읽어서 정렬을 수행하고 병합하고 하는 과정을 반복해야 한다.
• 소트 버퍼의 크기가 너무 작으면 디스크 읽기 쓰기가 빈번해 질 것이다.
• 소트 버퍼의 크기가 너무 크면 동시 접속이 잦은 환경에서 운영체제는 메모리 부족 현상을 겪을 수 있다.
  • 운영체제의 OOM-Killer는 메모리가 부족하면 메모리를 가장 많이 사용하는 프로세스를 강제로 종료할 것이다.

9.2.3.2 정렬 알고리즘

• 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담아서 정렬할지, 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스와 투 패스 정렬로 나눌 수 있다.
• 쿼리가 어떤 정렬 모드를 수행하는지는 다음과 같이 옵티마이저 트레이스 기능으로 확인할 수 있다.

select * from information_schema.optimizer_trace

...
"sort_mode": "<fixed_sort_key, packed_additional_fields>"

• 정렬 모드는 3가지로 나뉘어진다.
  • <sort_key, row_id> : 정렬 키와 레코드의 로우 아이디만 가져와서 정렬한다.
  • <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬한다. 레코드 칼럼은 고정 사이즈로 메모리에 저장한다.
  • <sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정려한다. 레코드 칼럼은 가변 사이즈로 메모리에 저장한다.
• 첫 번째 방식은 투 패스 정렬방식이고 나머지 두 방식은 싱글 패스 방식이다.

9.2.3.2.1 싱글 패스 정렬방식

• 정렬에 필요하지 않은 칼럼까지 소트 버퍼에 담아서 정렬을 수행한다.

9.2.3.2.2 투 패스 정렬방식

• 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행한다.
• 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져온다.
• 차이점
  • 투패스 정렬방식은 테이블을 두 번 읽어야 하므로 상당히 비용이 많이 든다.
  • 싱글 패스는 한 번만 읽어도 된다. 하지만 소트 버퍼의 공간을 많이 사용한다.
• 최근에는 일반적으로 싱글 패스를 많이 쓰지만 다음의 경우 투 패스 정렬방식을 사용한다.
  • 레코드의 크기가 max_length_for_sort_data 값보다 클 때
  • BLOB, TEXT 타입의 칼럼이 SELECT 대상에 포함 될 때

9.2.3.3 정렬 처리 방법

• 쿼리에 ORDER BY 문이 포함되면 다음의 세 가지 방법 중 하나로 처리된다.
  • 인덱스를 이용한 정렬 : 실행 계획의 Extra 컬럼 => 별도 표기 없음
  • 조인의 드라이빙 테이블만 정렬 => Using filesort
  • 조인 결과를 임시 테이블에 저장 후 정렬 => Using temporary; Using filesort

옵티마이저는 먼저 인덱스를 이용해서 정렬을 할 수 있을지 여부를 체크한다. 인덱스를 사용할 수 있으면 인덱스를 사용하고 그렇지 않으면 레코드를 정렬버퍼에 넣고 정렬을 수행한다. 이 때 MySQL 서버는 정렬 대상 레코드를 최소화하기 위해 다음 두 가지 방법 중 하나를 수행한다.

• 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
• 조인이 끝나고 결과 레코드 모두를 가져와서 정렬을 수행

9.2.3.3.1 인덱스를 이용한 정렬

• 조건
  • 인덱스를 이용하려면 먼저 ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인을 한다면)에 속해야 한다.
  • 또한, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
• 인덱스를 이용한 정렬은 이미 인덱스 자체가 정렬돼 있기 때문에 순서대로 읽기만 하면 된다. (추가 작업이 없음)
• 조인을 하더라도 네스티드 루프 방식으로 실행되기 때문에 조인으로 인해 순서가 흐트러지지 않는다.

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

• 조건
  • 인덱스를 이용하려면 먼저 ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인을 한다면)에 속해야 한다.
• 일반적으로 조인이 수행되고 나면 레코드 수가 꽤나 증가한다. 따라서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이다.

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;

9.2.3.3.3 임시 테이블을 이용한 정렬

• 하나의 테이블만을 정렬하거나 9.2.3.3.2와 같이 2개 이상의 테이블을 조인하더라도 드라이빙 테이블만 정렬하여 처리할 수 있는 경우는 임시 테이블이 필요하지 않다.
• 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬하는 과정을 거친다.
  • 이 방법은 가장 느린 정렬 방법이다.

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 temporary; Using filesort라는 코멘트가 남는다.
  • 이는 임시 테이블에 조인 결과를 저장하고 그 결과를 정렬했다는 의미이다.

9.2.3.3.4 정렬 처리 방법의 성능 비교

일반적으로 limit은 처리하는 양을 줄일 수 있기 때문에 서버가 처리할 작업량을 줄일 수 있다고 생각한다. 하지만 데이터를 처리하는 방식에 따라 limit으로 처리량이 줄 수도있고 줄지 않을 수도 있다. 다음 두 가지 방식을 살펴보자.

• 스트리밍 방식 : 서버 쪽에서 레코드가 검색될 때마다 바로바로 클라이언트로 결과를 전송해주는 방식이다. 이 방식에서는 limit 제한을 걸면 처리량을 줄이고 마지막 레코드를 가져오는 시간을 줄일 수 있다.
• 버퍼링 방식 : 데이터를 스캔할 때 order by나 group by를 걸면 스트리밍이 불가능하다. where 조건에 만족하는 모든 레코드를 가져와서 정렬하거나 그루핑해서 차례로 응답을 보내야한다. 즉 결과를 모아서 일괄 가공해야 하므로 limit과 같은 결과 건수를 제한하는 조건은 성능 향상에 도움이 되지 않는다.
• order by 처리방식 중에서 인덱스를 이용한 정렬 방식만 스트리밍 형태의 처리이고 나머지는 모두 버퍼링 방식이다.

9.2.4 GROUP BY 처리

• group by는 order by와 비슷하게 스트리밍 방식의 데이터 처리를 사용할 수 없게 만든다.
• group by 작업은 다음과 같이 나눌 수 있다.
• 인덱스 이용
  • 인덱스 스캔 : 인덱스를 차례로 읽는 방식
  • 루스 인덱스 스캔 : 인덱스를 건너뛰면서 읽는 방식
• 인덱스를 이용 x : 임시 테이블 사용

9.2.4.1 인덱스 스캔을 이용하는 Group By(타이트 인덱스 스캔)

• 조건
  • 조인의 드라이빙 테이블에 속한 칼럼만을 이용해 그루핑
  • group by의 순서로 인덱스가 있어야 함

9.2.4.2 루스 인덱스 스캔을 이용하는 Group by

• 인덱스 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 방식
• 루스 인덱스 스캔이 되는 지 조건을 판별하기가 어렵다.
• 다음의 예로 연습해보자. (col1, col2, col3의 순으로 인덱스)

select col1, col2 from tb_test group by col1, col2;
select distinct col1, col2 from tb_test;
select col1, min(col2) from tb_test group by col1;
select col1, col2 from tb_test where col1 < const group by col1, col2;
select max(col3), min(col3), col1, col2 from tb_test where col2 > const group by col1, col2;
select col2 from tb_test where col1 < const group by col1, col2;
select col1, col2 from tb_test where col3 = const group by col1, col2;

9.2.4.3 임시 테이블을 사용하는 Group By

• 인덱스를 전혀 사용하지 못할 때에는 임시테이블을 만든다.
• group by 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 합수 연산을 수행한다.

9.2.5 DISTINCT 처리

• distinct 처리는 크게 두 가지로 나뉠 수 있다.
• 집합함수(Min, Max, count 등)과 함께 사용되는 경우와 그렇지 않은 경우이다.

9.2.5.1 SELECT DISTINCT...

• 이 경우 group by와 동일한 방식으로 처리된다.

select distinct emp_no from salaries;
select emp_no from salaries group by emp_no;

• distinct 키워드로 가장 흔하게 실수하는 것 중 하나는 distinct는 select 하는 모든 대상 컬럼에 대해 유니크하게 조회하는 것이다. 특정 칼럼만 유니크하게 조회하는 것이 아니다.
  • 괄호를 쳐도 의미없다!!!

select distinct(first_name), last_name from employees;

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

• 하지만 집합함수와 함께 사용하는 distinct는 단일 칼럼의 유니크 값을 가져온다.
• 집합함수와 함께 사용할 때에는 항상 임시 테이블을 생성한다.

select count(distinct s.salary)
from employees e, salaries s
where e.emp_no = s.emp_no
and e.emp_no between 10001 and 100100;

• 위의 쿼리를 실행했을 때 조인한 결과에서 salary를 저장하기 위한 임시테이블을 만든다.
• 이 테이블에서는 salary 칼럼은 유니크 인덱스가 생성된다.

9.2.6 내부 임시 테이블 활용

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

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

• 메모리는 TempTable이라는 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)
  • 최대 사용 가능한 메모리 크기는 1GB이다. 1GB가 초과하는 경우 디스크로 기록하게 된다.
  • 이 때 두 가지 방식으로 저장할 수 있다.
    1. MMAP 파일로 기록 (디폴트, InnoDB 테이블로 전환하는 것 보다 오버헤드가 적음)
    2. InnoDB 테이블로 기록
• 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)

9.2.6.2 임시 테이블이 필요한 쿼리

• 다음과 같은 쿼리는 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" 메시지가 표시되는지 확인하면 알 수 있다.

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

• 내부 임시 테이블은 기본적으로 메모리에 만들어진다.
• 하지만 다음의 조건을 만족하면 디스크 기반의 임시 테이블을 사용한다.
  • Union이나 union all에서 select되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  • group by나 distinct 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블의 크기가 temp_table_max_ram 시스템 변수 값보다 큰 경우

9.2.6.4 임시 테이블 관련 상태 변수

• 일반적으로 실행 계획에서 "Using temporary"가 표시된 것으로 임시 테이블이 사용되었다는 것을 알 수 있다.
• 하지만 임시 테이블을 하나 사용했는지 여러개 사용했는지, 메모리에 생성됐는지 디스크에 생성됐는지는 다음의 상태 변수로 확인할 수 있다.
• show session status like 'Created_tmp%';
  • Created_tmp_tables : 내부 임시 테이블의 개수를 누적하는 상태 값
  • Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수를 누적하는 상태 값