MySQL에서 쿼리를 최적으로 실행하기 위해 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업을 옵티마이저가 한다.

9.1 개요

9.1.1 쿼리 실행 절차

1. 파싱

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

   SQL 파서라는 모듈로 처리한다. SQL 파스 트리가 만들어진다.

2. 최적화 및 실행 계획 수립

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

   옵티마이저에서 처리한다.

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

3. 실행 계획대로 스토리지 엔진으로부터 데이터 가져오기

   1,2단계는 MySQL엔진이, 3단계는 MySQL엔진과 스토리지 엔진이 동시에 참여한다.

9.1.2 옵티마이저의 종류

• 비용 기반 최적화 각 단위 작업의 비용과 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다. 비용이 최소인 방식으로 쿼리를 실행한다.
  
• 규칙 기반 최적화 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식. 데이터를 조사하지 않고 실행 계획이 수립되므로 좋지 않다.
  

9.2 기본 데이터 처리

MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있다.

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

• 일반적으로 테이블 전체 크기는 인덱스보다 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다.
• InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.
• 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다. 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.
• 리드 어헤드는 풀 인덱스 스캔에도 동일하게 사용된다.

9.2.2 병렬 처리

• 병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미한다.
• MySQL 8.0 에서는 아무런 where 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다. SELECT COUNT(*) FROM employees;
  
• 병렬 처리용 스레드 개수를 CPU 코어 개수보다 크게 설정하면 성능이 떨어진다.

9.2.3 ORDER BY 처리 (Using file sort)

• 정렬을 처리하는 방법에는 인덱스를 이용하는 방법과 쿼리가 실행될때 Filesort 라는 별도의 처리를 이용하는 방법이 있다.
• MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 EXTRA 칼럼에 “using filesort” 메시지 여부로 판단할 수 있다.

9.2.3.1 소트 버퍼

• MySQL은 정렬을 수행하기 위해 소트 버퍼라는 별도의 메모리 공간을 할당받아 사용한다.
• 소트 버퍼는 정렬이 필요한 경우에만 할당되는 가변적인 공간이며, 쿼리 실행이 완료되면 즉시 반납된다.
• 정렬해야 할 레코드 건수가 소트 버퍼 공간보다 크다면, 레코드를 여러 조각으로 나눠서 처리하는데, 이때 임시저장을 위해 디스크를 사용한다. 정렬하고 병합하는 과정에서 디스크 읽기, 쓰기가 유발된다.
• 소트 버퍼를 크게 설정하면 더 빨라질 것 같지만, 소트 버퍼는 세션 메모리이므로 클라이언트마다 할당되는 공간이라 크게 설정하면 메모리 부족 현상을 겪을 수도 있다.

9.2.3.2 정렬 알고리즘

• 레코드 정렬할 때, 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 2가지 정렬 모드가 있다.
• 정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지는 옵티마이저 트레이스 기능으로 확인 가능하다. filesort_summary의 sort_algorithm, sort_mode 필드
  
• 싱글 패스 정렬 방식 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.
  
• 투 패스 정렬 방식 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식이다.
  
• 싱글 패스는 더 많은 소트 버퍼 공간이 필요하고, 투 패스는 테이블을 두 번 읽어야해서 비합리적이다. (대상 레코드가 작은 경우 싱글 패스, 대상 레코드가 클 경우 투 패스가 낫다)

9.2.3.3 정렬 처리 방법

• 쿼리에 ORDER BY가 사용되면 아래 3가지 방법 중 하나로 정렬이 처리된다.
  • 인덱스를 이용한 정렬 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. B-Tree 계열의 인덱스여야 한다. (B-Tree 인덱스는 키 값으로 정렬돼 있기 때문)
    
  • 조인에서 드라이빙 테이블만 정렬 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행한다. 이 방법을 사용하려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다.
    
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 ORDER BY절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 칼럼인 경우 이 방식이 적용된다. 쿼리 실행 계획에서 Extra 칼럼에 Using temporary가 표시된다.
    
• 정렬 처리 방법의 성능 비교 쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 느리게 작동하는 이유를 알아보기 위해, 쿼리가 처리되는 방법 2가지를 보자.

  • 스트리밍 방식

    서버 쪽에서 처리할 데이터양에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식이다.

    클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터 가공 작업을 시작할 수 있다.

    LIMIT을 사용하는 경우, 마지막 레코드를 가져오기까지의 시간을 줄일 수 있다.
    

  • 버퍼링 방식

    ORDER BY나 GROUP BY 같은 쿼리는 정렬이나 그루핑이 필요해서 스트리밍 방식을 사용할 수 없다.
    
    MySQL 서버가 레코드를 검색하고 작업하는 동안 클라이언트는 대기해야하므로 응답 속도가 느려진다.

    인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이다.

    정렬 처리 방법에 따라 성능 차이가 크다. 가능하면 인덱스 사용 정렬로 유도하고, 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하자.

9.2.3.4 정렬 관련 상태 변수

MySQL 서버는 정렬 관련 상태 변수를 저장하는데, 이 값들을 이용해 MySQL 서버가 처리한 정렬 작업의 내용을 이해할 수 있다.

9.2.4 GROUP BY 처리

• GROUP BY도 쿼리가 스트리밍 처리를 할 수 없게 하는 처리이다.
• GROUP BY에서 사용되는 HAVING절은 GROUP BY 결과에 대해 필터링 역할을 수행하므로 인덱스로 튜닝이 불가능하다.
• GROUP BY는 인덱스를 이용할 때는 인덱스 스캔, 루스 인덱스 스캔을 사용하고, 인덱스를 사용하지 못하는 쿼리에서는 임시 테이블을 사용한다.

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

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

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

• 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 Using index for group-by 코멘트가 표시된다.
• 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 프리픽스 인덱스는 이 방식을 사용할 수 없다.
• 루스 인덱스 스캔은 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. (분포도가 좋지 않은 인덱스일수록 성능이 향상된다)

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

• 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.
• 8.0버전부터는 ORDER BY 절이 없을 때, 묵시적 정렬이 실행되지 않는다.

9.2.5 DISTINCT 처리

9.2.5.1 SELECT DISTINCT …

• 단순히 유니크한 레코드만 가져오고자 한다면 GROUP BY와 동일한 방식으로 처리된다.
• DISTINCT는 SELECT하는 레코드를 유니크하게 SELECT하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아니다. 아래 쿼리에서 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져오는 것이다. SELECT DISTINCT first_name, last_name FROM employees; 또한 DISTINCT(first_name) 같이 써도, DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없다.
  

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

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

9.2.6 내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다. 이 내부적인 임시 테이블은 “CREATE TEMPORARY TABLE” 명령으로 만든 임시 테이블과는 다르다. 내부적인 임시 테이블은 쿼리 처리가 완료되면 자동으로 삭제된다.
  

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

• 메모리는 TempTable이라는 스토리지 엔진을 사용, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다.
• 메모리의 TempTable의 크기가 1GB를 넘으면 MySQL 서버는 메모리의 TempTable을 MMAP 파일로 전환하거나, InnoDB 테이블로 전환할 수 있다. MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문에 MMAP 파일로 전환하는 것이 기본값이다.
  

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

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

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

• 기본적으로 메모리상에 만들어지지만 다음의 경우 디스크에 생성된다.
  • UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블의 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 시스템 변수 값보다 큰 경우

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

• 실행 계획에 Using temporary가 표시됐다고해서, 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지 알 수 없다. 한 번 표시됐다고 하나만 사용했다는 것을 의미하지도 않는다.
• 이를 알기 위해, MySQL 서버의 상태 변수(SHOW SESSION STATUS LIKE ‘Created_tmp%’;)를 확인하면 된다.

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다.

9.3.1.1 MRR과 배치 키 액세스(mrr & batch_key_access)

• MRR (multi-range read), DS-MRR(disk sweep multi-range read)라고도 한다.
• 기존에는 네스티드 루프 조인 방식을 사용했다. 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 방식
  
• 네스티드 루프 조인 방식에서는 조인 처리를 MySQL 엔진이 담당하고, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당한다. 이 방식에서는 스토리지 엔진에서는 최적화를 수행할 수 없다.
  
• 이런 단점을 보완하기 위해 조인 버퍼에 조인 대상을 버퍼링한다. 조인 버퍼에 레코드가 가득 차면 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다. 이렇게 해서 디스크 읽기를 최소화할 수 있다. 이 방식을 MRR이라고 한다.
  
• MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 한다. 부가적인 정렬 작업이 필요해서 성능이 저하되기도 한다.
  

9.3.1.2 블록 네스티드 루프 조인

• 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이다.
• 네스티드 루프 조인과 차이점은 조인 버퍼 사용 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐다. 블록 네스티드 루프 조인에서는 조인 버퍼가 사용된다. 실행 계획에서 Extra 칼럼에 Using join buffer가 표시되면 조인 퍼버를 사용한다는 것을 의미한다.
  
• 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리된다. 그래서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 느려진다.
• 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시(조인 버퍼)한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다.
• 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다. A가 드라이빙, B가 드리븐 테이블일 때, A에서 검색된 레코드를 조인 버퍼에 담아두고, B의 레코드를 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.
  

9.3.1.3 인덱스 컨디션 푸시다운

first_name, last_name 칼럼이 있는 테이블에서 두 칼럼에 대한 인덱스 ix_lastname_firstname이 있다고 가정하자.

이때, SELECT * FROM employees WHERE last_name=’Action’ AND first_name LIKE ‘%sal’; 쿼리를 살펴보자.

last_name=’Action’은 ix_lastname_firstname을 인덱스 레인지 스캔으로 사용할 수 있지만, first_name LIKE ‘%sal’ 는 아니다.

• 인덱스 컨디션 푸시다운이 없다면, last_name=’Action’ 조건으로 인덱스 레인지 스캔을 하고 테이블 레코드를 읽은 후, first_name LIKE ‘%sal’ 조건에 부합되는지 여부를 비교하여 레코드를 반환하게 된다. 2건의 테이블 읽기가 발생한다. 인덱스를 범위 제한 조건으로 사용하지 못하는 두번째 조건은 MySQL 엔진이 스토리지 엔진으로 넘기지 않기 때문이다.
  
• 인덱스 컨디션 푸시다운을 활성화하면, 인덱스를 범위 제한 조건으로 사용하지 못하는 조건이라도 인덱스에 포함된 칼럼의 조건이라면 같이 모아서 스토리지 엔진으로 전달한다. 그러면 불필요한 2건의 테이블 읽기가 발생하지 않는다.

9.3.1.4 인덱스 확장(use_index_extensions)

• 이 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.
• InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성한다. 그래서 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다. 예를 들어, A테이블의 프라이머리 키가 (a, b)이고, ix_c라는 c 칼럼만을 포함하는 세컨더리 인덱스가 있다고 하자. 이 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키인 a, b 칼럼을 순서대로 포함한다. 그래서 ix_c 인덱스는 (c, a, b) 조합으로 인덱스를 생성한 것과 흡사하게 작동한다.
  

9.3.1.5 인덱스 머지

• 인덱스를 이용해 쿼리를 실행할 때, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하는 실행 계획을 수립한다. 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
  
• 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고, 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
• 각각의 결과를 어떤 방식으로 병합할지에 따라 아래 세가지로 구분된다.
  • index_merge_intersection
  • index_merge_sort_union
  • index_merge_union

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

• 2개 이상의 where 조건을 가지고 있고, 모두 인덱스를 사용할 수 있을 때 두 키를 모두 사용해서 쿼리를 처리하는 방식이다. 실행 계획의 Extra 칼럼에 Using intersect라고 표시되면, 이 쿼리가 여러 개의 인덱스를 각각 검색하여 그 결과의 교집합만 반환한 것을 의미한다.
  
• 옵티마이저가 각각의 조건에 일치하는 레코드 건수를 예측해본 결과, 이 조건들이 모두 상대적으로 많은 레코드를 가져와야할 때 이 방식을 사용한다.

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

• 인덱스 머지의 Using union은 where절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화다.
• 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키 기준으로 중복된 값을 정렬 없이 걸러낸다. 중복 제거를 수행할 때 사용되는 알고리즘은 우선순위 큐이다.

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

• 만약 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 sort union 알고리즘을 사용한다.

9.3.1.9 세미 조인(semijoin)

• 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라고 한다.

9.3.1.10 테이블 풀-아웃

• Table pullout 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화다.
• Table pullout 최적화가 사용됐는지 정확하게 확인하는 방법은 EXPLAIN 명령 실행 직후, SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 보는 것이다. IN (subquery) 형태가 사라지고, JOIN으로 쿼리가 재작성되어 있다.
• Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니고, 몇 가지 제한 사항이 있다.

9.3.1.11 퍼스트 매치

• First Match 최적화 전략은 IN (subquery) 형태의 세미 조인을 EXISTS (subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
• First Match 최적화가 사용되면 실행 계획의 Extra 칼럼에는 FirstMatch(table-N) 문구가 표시된다.

9.3.1.12 루스 스캔(loosescan)

• 세미 조인 서브쿼리 최적화의 루스 스캔은 루스 인덱스 스캔과 비슷한 읽기 방식을 사용한다.
• 루스 스캔 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화다.

9.3.1.13 구체화(Materialization)

• Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다. 구체화는 내부 임시 테이블을 생성한다는 것을 의미한다.
• 다른 서브쿼리 최적화와는 달리, 서브쿼리 내에 GROUP BY절이 있어도 사용할 수 있다.

9.3.1.14 중복 제거(Dubplicated Weed-out)

• Dubplicated Weed-out은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.
• 실행 계획에 Dubplicated Weed-out이 표시되지는 않지만, Extra 칼럼에 start temporary, end temporary 문구가 별도로 표기된다. start/end temporary 문구의 구간이 Dubplicated Weed-out 최적화의 처리 과정이라고 보면 된다.

9.3.1.15 컨디션 팬아웃(condition_fanout_filter)

• 조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다.
• 옵티마이저는 여러 테이블이 조인되는 경우, 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
• 옵티마이저가 조건을 만족하는 레코드 건수를 정확하게 예측할 수 있다면, 더 빠른 실행 계획을 만들 수 있다. 실행 계획에서 예측된 컬럼 비율은 filtered 컬럼에서 확인할 수 있다.
  
• condition_fanout_filter 최적화 기능을 활성화하면 옵티마이저는 더 정교한 계산을 하므로 더 많은 시간, 컴퓨팅 자원이 소모된다. 그러므로 쿼리가 간단한 경우에는 큰 도움이 되지 않을 수 있다.

9.3.1.16 파생 테이블 머지(derived_merge)

• 예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다. 이렇게 FROM 절에 사용된 서브쿼리를 파생 테이블이라고 부른다.
  
• 임시 테이블에 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리 성능이 저하된다.
• 5.7 버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐다. (derived_merge 최적화 옵션)

9.3.1.17 인비저블 인덱스(use_invisible_indexes)

• 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능이다. ALTER TABLE … ALTER INDEX … [ VISIBLE | INVISIBLE ]
  
• use_invisible_indexes 옵션을 on으로 설정하면, INVISIBLE 상태의 인덱스도 옵티마이저가 볼 수 있다. (기본값은 off)

9.3.1.18 스킵 스캔

• 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다. 예를 들어, (A, B, C) 칼럼으로 구성된 인덱스가 있을 때, WHERE 절에 B와 C 칼럼에 대한 조건을 가지고 있다면 이 쿼리는 인덱스를 활용할 수 없다. 인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이런 제약 사항을 뛰어넘을 수 있는 최적화 기법이다.
  
• 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다.
• 하지만 인덱스의 선행 칼럼이 매우 다양한 값을 가지는 경우에는 비효율적일 수 있으므로, 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용한다.

9.3.1.19 해시 조인

• 해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않으므로, 최고 스루풋(best throughput) 전략에 적합하다.
• 네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠르므로, 최고 응답 속도(best response-time) 전략에 적합하다.
• 일반적인 웹 서비스는 온라인 트랜잭션 서비스이므로 응답 속도가 더 중요하다. 분석 서비스는 전체 스루풋이 더 중요하다. MySQL은 범용 RDBMS이므로 응답 속도에 더 집중해야 한다. 즉, MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책이다.
  
• 해시 조인은 빌드 단계와 프로브 단계로 나뉘어 처리된다. 빌드 단계에서는 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성하는 작업을 수행한다. 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고 한다. 프로브 단계는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정이다. 이 나머지 테이블을 프로브 테이블이라고 한다.
  
• 해시 테이블을 메모리에 저장할 때 조인 버퍼를 사용한다. 해시 테이블이 조인 버퍼 메모리보다 큰 경우 빌드 테이블과 프로브 테이블을 청크 단위로 나눠 저장된 청크 개수만큼 반복 처리하여 완성된 조인 결과를 만들어낸다.
• 옵티마이저는 빌드 테이블 크기에 따라 클래식 해시 조인 알고리즘(메모리에서 모두 처리 가능한 경우), 그레이스 해시 조인 알고리즘(해시 테이블이 조인 버퍼 메모리보다 큰 경우)을 하이브리드하게 활용하도록 구현돼 있다.

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

• 옵티마이저도 실수할 수 있다. 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵션을 사용할 수 있다. 기본값은 on이지만, 옵티마이저가 자주 실수한다면 off로 설정할 수 있다.
  

9.3.2 조인 최적화 알고리즘

9.3.2.1 Exhaustive 검색 알고리즘

• FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다.
• 테이블이 n개라면 가능한 조인 조합은 n!개이므로, 테이블이 많아지면 시간이 오래 걸린다.

9.3.2.2 Greedy 검색 알고리즘

• Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 도입된 최적화 기법이다.
• MySQL에서는 조인 최적화를 위한 시스템 변수로 optimizer_prune_level, optimizer_search_depth가 제공된다.
  • optimizer_search_depth 시스템 변수는 Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정하는 시스템 변수다. optimizer_search_depth는 0~62 사이의 정수로 설정할 수 있고, 1~62는 지정된 개수로 한정해서 최적의 실행 계획을 산출하고, 0은 옵티마이저가 자동으로 결정한다. optimizer_search_depth 설정 값과 쿼리의 조인 테이블 개수에 따라 Exhaustive 검색만 사용되거나 Greedy 검색과 Exhaustive 검색이 동시에 사용된다.
    
  • optimizer_prune_level 시스템 변수는 Heuristic 검색이 작동하는 방식을 제어한다.

9.4 쿼리 힌트

쿼리 힌트는 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이다.

9.4.1 인덱스 힌트

인덱스 힌트는 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다. 가능하다면 옵티마이저 힌트를 사용하는 것이 낫다.

인덱스 힌트는 SELECT, UPDATE 명령에서만 사용할 수 있다.

9.4.1.1 STRAIGHT_JOIN

• STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우, 조인 순서를 고정하는 역할을 한다. 여러 개의 테이블을 조인할 때 어느 테이블이 드라이빙 테이블이 되고, 어느 테이블이 드리븐 테이블이 될지 알 수 없다. 그래서 조인 순서 고정이 필요한 경우에 STRAIGHT_JOIN를 사용한다.
  
• 인덱스 힌트는 SELECT, UPDATE 명령 바로 뒤에 사용한다. SELECT STRAIGHT JOIN e.first_name, d.dept_name FROM …
  
• STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.
• 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

  • 임시 테이블과 일반 테이블의 조인

    임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다.
    

  • 임시 테이블끼리 조인

    임시 테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 읽어도 무관하므로, 크기가 작은 테이블을 드라이빙 테이블로 선정하는 것이 좋다.
    

  • 일반 테이블끼리 조인

    양쪽 모두 조인 칼럼에 인덱스가 있거나 모두 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙 테이블로 선정하는 것이 좋다. 그외의 경우에는 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택하자.

    여기서 말하는 레코드 건수는 테이블 전체 레코드 건수가 아니라, 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 그 조건을 만족하는 레코드 건수를 의미한다.

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

• 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.
• 옵티마이저는 사용할 인덱스를 잘 선택하지만, 비슷한 인덱스가 여러 개 있는 경우 가끔 실수할 수 있다. 이럴 때 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.
• 종류

  • USE INDEX

    옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
    

  • FORCE INDEX

    USE INDEX보다 강한 힌트라고 하지만, USE도 충분히 강해서 FORCE는 굳이 안써도 된다.
    

  • IGNORE INDEX

    특정 인덱스를 사용하지 못하게 하는 용도

    이 3종류 인덱스 힌트 모두 용도를 명시할 수 있다. USE INDEX FOR JOIN, USE INDEX FOR ORDER BY 처럼 사용할 수 있다.

9.4.1.3 SQL_CALC_FOUND_ROWS

• MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다.
• 하지만 SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리는 멈추지 않고 끝까지 검색을 수행한다.
• SQL_CALC_FOUND_ROWS는 성능 향상을 위해 만들어진 힌트가 아니라 개발자의 편의를 위해 만들어진 힌트이므로 쿼리에는 이를 사용하지 않는 편이 낫다.

9.4.2 옵티마이저 힌트

9.4.2.1 옵티마이저 힌트 종류

• 옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나눌 수 있다. 이 구분으로 인해 힌트 사용 위치가 달라지는 것은 아니다.
  • 인덱스
  • 테이블
  • 쿼리 블록
  • 글로벌 (쿼리 전체)

9.4.2.2 MAX_EXECUTION_TIME

옵티마이저 힌트 중에서 유일하게 쿼리의 실행 계획에 영향을 미치지 않으며, 단순히 쿼리의 최대 실행 시간을 설정하는 힌트다. 여기에 지정한 시간을 초과하면 쿼리는 실패한다.

9.4.2.3 SET_VAR

SET_VAR는 실행 계획을 바꾸는 용도뿐 아니라 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로 사용할 수 있다.

9.4.2.4 SEMIJOIN & NO_SEMIJOIN

• SEMIJOIN 힌트는 어떤 세부 전략을 사용할지 제어하는 데 사용할 수 있다.
• 최적화 전략들은 상황에 따라 다른 최적화 전략으로 우회하는 것이 더 나은 성능을 낼 수도 있기 때문에 NO_SEMIJOIN 힌트도 제공된다.

9.4.2.5 SUBQUERY

• 서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법이다.
• 사용할 기회가 그다지 많지 않다.

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

8.0.20 버전부터는 해시 조인이 블록 네스티드 루프 조인까지 대체했다. 그래서 8.0.20 버전 이후에는 해시 조인을 유도하거나 해시조인을 사용하지 않게 하고자 할 때 이런 힌트들을 사용한다.

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• 조인의 순서를 결정하기 위해 전통적으로 사용했던 STRAIGHT_JOIN 힌트의 단점을 보완한 힌트들이다.
  • JOIN_FIXED_ORDER STRAIGHT_JOIN과 동일하게 FROM 절의 테이블 순서대로 조인을 실행하는 힌트
    
  • JOIN_ORDER FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
    
  • JOIN_PREFIX 조인에서 드라이빙 테이블만 강제하는 힌트
    
  • JOIN_SUFFIX 조인에서 드리븐 테이블만 강제하는 힌트
    

9.4.2.8 MERGE & NO_MERGE

• 예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 항상 내부 임시 테이블(파생 테이블)로 생성했다. 이는 불필요한 자원 소모를 유발한다. 그래서 5.7, 8.0 버전에서는 가능하면 임시 테이블을 사용하지 않게 FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입했다.
• 병합 여부에 대해 옵티마이저가 최적의 방법을 선택하지 못할 때, MERGE, NO_MERGE 힌트를 사용한다.

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

• MySQL는 가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려 한다. 하지만 하나만으로 부족하다면 다른 인덱스를 쓰기도 하고, 이를 인덱스 머지라고 한다.
• 인덱스 머지 실행 계획의 사용 여부를 결정할 때, 이 힌트들을 사용한다.

9.4.2.10 NO_ICP

• 인덱스 컨디션 푸시다운 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로 기본적으로 항상 사용하는 방향으로 실행 계획을 수립한다.
• 하지만 비용이 잘못 계산된다면 잘못된 계획을 수립할 수 있는데, 이때 인덱스 컨디션 푸시다운 최적화만 비활성화하는 힌트이다.

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

• 인덱스 스킵 스캔은 인덱스의 선행 칼럼에 대한 조건 없이도 옵티마이저가 해당 인덱스를 사용할 수 있게 해주는 최적화 기능이다. 하지만 조건이 누락된 선행 칼럼이 가지는 유니크한 값의 개수가 많아지면 성능은 오히려 떨어진다.
• 인덱스 스킵 스캔의 사용 여부를 결정할 때, 이 힌트들을 사용한다.

9.4.2.12 INDEX & NO_INDEX

• 이 힌트들은 예전 MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공된다.
• 인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열했다. 하지만 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.