옵티마이저와 힌트

김민우·2023년 2월 16일

DBMS mysql sql 옵티마이저

MySQL

목록 보기

7/7

MySQL 서버로 요청된 쿼리의 결과를 내부에서 만드는 방법은 다양한데, 여러 가지 중 최적의 방법으로 결과를 만들어내야 한다. 이렇게 최적의 실행 계획을 수립하는 기능은 옵티마이저가 담당한다.

즉, 실험 계획에 표시되는 내용을 이해하기 위해선 옵티마이저의 지식이 필요하다.

1. 개요

DBMS에서 가장 복잡한 부분이라 하면 쿼리의 실행 계획을 수립하는 옵티마이저다. 그러나, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것은 이해를 해야 최적화된 방법으로 실행 계획을 수립할 수 있다.

이를 이해하기 위해 먼저 알고 있어야 할 사항들을 보자.

1.1 쿼리 실행 절차

SQL 파싱

사용자로부터 요청된 SQL 문장을 잘개 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(Parse Tree) 한다.
- 이 과정을 SQL 파싱이라하며, SQL 파서라는 모듈로 처리한다.
- 문법적 오류는 이 단계에서 걸러진다.
- 이 과정이 완료되면 SQL 파스 트리가 만들어진다.
- 즉, MySQL은 SQL 문장이 아닌 SQL 파스 트리를 이용해 쿼리를 실행한다.

최적화 및 실행 계획 수립

SQL의 파싱 정보(Parse Tree)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- MySQL 서버의 옵티마이저에서 처리한다.
- 다음과 같은 내용들을 처리한다. (이 외에도 수 많은 처리를 한다.)
  - 불필요한 조건 제거 및 복잡한 연산의 단순화
  - 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
  - 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
  - 가져온 레코드들을 임시 테이블에 넣고 다시 한 번 가공해야 하는지 결정

두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
- MySQL 엔진과 스토리지 엔진이 동시에 참여하여 처리
- 수립된 실행 계획대로 스토리지 엔진에서 레코드를 읽어오도록 요청
- 스토리지 엔진으로 부터 받은 레코드를 조인하거나 정렬하는 작업을 수행

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

1.2 옵티마이저의 종류

비용 기반 최적화(Cost-based optimizer, CBO)
- 쿼리를 처리하기 위한 여러 가지 방법을 수립 후 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 통해 실행 계획별 비용을 산출 후 최소 비용 작업으로 실행 계획을 수립하는 방법
- 현재 대부분의 DBMS가 사용
규칙 기반 최적화 방법(Rule-based optimizer, RBO)
- 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위 만으로 실행 계획을 수립하는 방법
- 예전 초기 버전 오라클 DBMS에서 주로 사용
- 사용자의 데이터 분포도가 매우 다양하여 거의 사용하지 않는다.

2. 기본 데이터 처리

데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능은 RDBMS 별로 과정이 다르다. MySQL 서버는 어떤 알고리즘을 사용하는지 알아보자.

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

MySQL 옵티마이저는 다음 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.

테이블 레코드 건수가 너무 작은 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 조건이 없는 경우
인덱스 레인지 스캔을 사용했을 때 조건 일치 레코드가 너무 많다고 판단되는 경우

InnoDB 스토리지 엔진에서는 풀 테이블 스캔을 할 때, 디스크로 부터 페이지를 하나씩 읽어오지 않는다. 리드 어헤드 작업을 통해 필요한 데이터를 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 둔다.

참고
리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해질 것이라는 것을 예측해 요청 전 미리 해당 데이터를 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것

InnoDB에서 풀 테이블 스캔 실행 과정

시작하면 처음 몇 개의 페이지는 포그라운드 스레드(클라이언트 스레드)가 페이지 읽기를 시작한다.
특정 시점부터는 읽기 작업을 백그라운드 스레드가 대신한다.
- 이 시점부터 한 번에 4개 또는 8개씩 페이지를 읽으면서 그 수를 증가시킨다.
- 이 때, 한 번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해준다.

즉, 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.

리드 어헤드 시작 시점 결정

innodb_read_ahead_threshold 시스템 변수를 통해 설정
- 이 시스템 변수에 설정된 개수 만큼 연속된 데이터 페이지가 읽히면 백그라운드 스레드를 이용해 대량으로 그 다음 페이지들을 읽어서 버퍼 풀로 저장
대부분 디폴트 설정 사용
데이터 웨어하우스용도로 MySQL 사용시 더 낮은 값으로 사용해도 무방

이 리드 어헤드는 풀 인덱스 스캔에도 동일하기 적용된다.

풀 테이블 스캔, 풀 인덱스 스캔 적용 예시

SELECT COUNT(*) FROM employees;

이 쿼리는 단순히 레코드 건수만 조회하므로 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있다.

SELECT * FROM employees;

이 쿼리는 레코드에만 있는 컬럼이 필요하므로 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔을 활용한다.

2.2 병렬 처리

병렬 처리
하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미한다.

MySQL 서버의 쿼리도 병렬 처리가 가능하다. 그러나 쿼리를 여러 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없다.

쿼리를 최대 몇 개의 스레드를 사용할지는 innodb_parallel_read_threads 값으로 설정하면 된다.

병렬 처리용 스레드 개수가 늘어날 수록 쿼리 처리에 걸리는 시간이 줄어드나, 서버에 장착된 CPU의 개수를 넘어서는 경우에는 오히려 성능이 저하되므로 주의하자.

2.3 ORDER BY 처리(Using filesort)

레코드 1~2 건 이상을 가져오는 SELECT 쿼리에서 정렬을 필수적으로 사용된다. 정렬을 처리하는 방법은 다음 두가지가 있다.

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

레코드를 정렬하기 위해서 항상 Filesort 정렬 작업을 거쳐야 하는 것은 아니다. 그러나, 인덱스를 사용한 정렬은 다음 같은 이유로 모든 정렬을 사용할 순 없다.

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

소트 버퍼

정렬을 수행하기 위한 별도 메모리 공간
sort_buffer_size 를 통해 최대 버퍼 공간 설정 가능
쿼리의 실행이 완료되면 즉시 메모리 공간을 시스템에 반납한다.

만약, 정렬을 할 레코드 건수가 소트 버퍼 크기보다 크면 어떻게 될까? 이 때MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.

즉, 메모리의 소트 버퍼에서 정렬을 수행하고 그 결과를 임시로 디스크에 기록하는 작업을 반복한다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행햐야 한다. 이 병합 작업을 멀티 머지(Multi-merge)라 한다.

이 작업은 모두 디스크의 쓰기와 읽기를 유발하며 레코드 건수가 많을 수록 성능이 저하된다.

그렇다고 디스크 접근 횟수를 줄이기 위해 소트 버퍼의 크기를 늘린다 해도 빨라지지 않는다. 오히려 큰 메모리 공간 할당때문에 성능이 훨씬 떨어질 수 있다.

따라서, 소트 버퍼의 크기는 적절히 설정하는 것이 좋은데 대량 데이터의 정렬이 필요한 경엔 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리 실행 후 다시 줄이거나 하는 방법이 좋다.

정렬 알고리즘

레코드를 정렬할 때 정렬 모드는 크게 두 가지가 있다. (공식적인 명칭 X)

싱글 패스(Single-pass)
- 레코드 전체를 소트 버퍼에 담는 경우
투 패스(Two-pass)
- 정렬 기준 컬럼만 소트 버퍼에 담는 경우

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

아래 쿼리를 예시로 들어 두 정렬 모드를 알아보자.

mysql> SELECT emp_no, first_name, last_name
	   FROM employees
       ORDER BY first_name;

first_name 으로 정렬해서 emp_no, first_name, last_name 을 SELECT 하는 쿼리

싱글 패스

소트 버퍼에서 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼 전불를 담아서 정렬을 수행하는 방식이다. 위 쿼리의 실행과정은 다음과 같다.

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

소트 버퍼에 모든 레코드를 담기에 소트 버퍼 크기와 레코드 크기에 의존적이다. 정렬 대상 레코드의 크기나 건수가 작은 경우 성능이 매우 좋다.

최신 버전에선 일반적으로 이 방법을 사용한다.

투 패스

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

즉, 싱글 패스는 정렬된 값 자체를 그대로 가져왔으면 됬으나 투 패스는 정렬이 끝나고 다시 PK 값을 통해 테이블을 읽어 SELECT 절 레코드를 모두 가져온다.

정렬 대상 레코드(SELECT 대상) 의 크기나 건수가 상당히 많은 경우 효율적이므로 다음 같은 경우에 투 패스를 사용한다.

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

정렬 처리 방법

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

인덱스를 사용한 정렬
조인에서 드라이빙 테이블만 정렬
조인에서 조인 결과를 임시 테이블로 저장 후 정렬

아래로 갈수록 처리 속도는 떨어진다. 따라서, 옵티마이저는 위 방법부터 순차적으로 가능한지 여부를 아래와 같이 검토한다.

인덱스를 이용할 수 있다면 Filesort 과정 없이 인덱스를 순서대로 읽어서 반환한다.
인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코들를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)한다.
- 정렬 대상 레코드를 최소화 하기 위해 다음 2가지 방법 중 하나를 선택
  - 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
  - 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
- 일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 매우 커지기에 가능하면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 것이 효율적

인덱스를 이용한 정렬

다음 조건을 만족해야 인덱스를 이용한 정렬이 가능하다.

ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 존재
ORDER BY 순서대로 생성된 인덱스가 존재
WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY 는 같은 인덱스를 사용할 수 있어야 한다.
B-Tree 방식 인덱스가 아닌 경우 사용할 수 없다.

인덱스를 이용해 정렬이 처리되는 경우는 단순히 인덱스를 순서대로 읽기만 하면 된다. (인덱스는 이미 정렬되있으므로)

다음 예제는 ORDER BY가 있든 없든 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력되는 것을 확인할 수 있다.

mysql> SELECT *
	   FROM employees e, salaries s
       WHERE s.emp_no=e.emp_no
         AND e.emp_no BETWEEN 100002 AND 100020
       ORDER BY e.emp_no;

// emp_no 컬럼으로 정렬이 필요한데, 인덱스를 사용하면 자동으로 정렬이 된다고
// 일부러 ORDER BY emp_no를 제거하는 것은 좋지 않은 선택이다.

mysql> SELECT *
	   FROM employees e, salaries s
       WHERE s.emp_no=e.emp_no
         AND e.emp_no BETWEEN 100002 AND 100020

하지만, 조인이 사용된 쿼리의 실행 계획에 조인 버퍼(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;

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

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고 하나의 테이블로부터 SELECT 해서 정렬하는 경우라면 임시 테이블이 불필요하지만, 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 하는 경우엔 임시 테이블이 필요할 수 있다.

앞에서 살펴본 "조인의 드라이빙 테이블만 정렬"은 2개 이상의 테이블이 조인 되면서 정렬이 수행되지만 임시 테이블을 사용하지 않는다.

하지만, 그 외 패턴 쿼리는 항상 조인의 결과를 임시 테이블에 저장 후 그것을 다시 정렬하는 과정을 거친다. 이 방법은 위 2가지 방법과 비교했을 때 정렬해야 할 레코드 건수가 가장 많으므로 가장 느리다.

다음 쿼리는 위 방법들에서 살펴본 예제에서 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 salaries;

정렬이 시작되기 전 드리븐 테이블인 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수 밖에 없다.

따라서, 조인된 결과를 임시 테이블에 저장 후 그것을 정렬한다.

정렬 처리 방법의 성능 비교

주로 웹 서비스용 쿼리에서는 ORDER BY 와 함께 LIMIT가 거의 필수로 사용되는 경향이 있다. LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야할 작업량을 줄이는 역햘을 한다.

그러나, ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 만큼 가져와서 처리하는 건 불가능하다. 조건을 만족하는 레코드를 모두 가져와 정렬 또는 그루핑 작업을 실행해야 비로소 LIMIT로 건수를 제한할 수 있다.

즉, WHERE 조건이 아무리 튜닝이 잘 되도 ORDER BY 나 GROUP BY 때문에 쿼리가 느려는 경우가 자주 발생한다.

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑이 왜 느리게 작동하는 지는 다음 쿼리를 처리하는 두 가지 방법을 보면 알 수 있다.

스트리밍 처리
- 서버쪽에서 처리할 데이터가 얼마인지 관계없이 조건에 일치하는 레코드가 검색될 때 마다 즉시 클라이언트로 전송해주는 방식
- 마지막 쿼리를 언제 받을지 알 수 없다. (이는 그다지 중요하지 않다.)
  - 덕분에 LIMIT 를 사용하면 전체 실행 시간을 많이 줄일 수 있다.
- 조건에 맞는 레코드를 검색하면서 데이터 가공 작업 수행 가능
버퍼링 처리
- 조건에 맞는 모든 레코드를 가져와서 한 번에 클라이언트에게 전송해주는 방식
- ORDER BY, GROUP BY 는 스트리밍이 불가능하므로 이 방식 사용.
- MySQL은 일괄적으로 데이터 가공을 해야하기에 LIMIT 사용이 성능 향상에 별로 도움이 되지 않는다.

위 ORDER BY 쿼리의 3가지 정렬 처리 방법 중 인덱스 사용 방식이 스트리밍 형태 처리이며, 나머지는 모두 버퍼링 후 정렬된다

2.4 GROUP BY 처리

GROUP BY 또한 스트리밍 처리가 될 수 없으므로, 이 명령어에 필터링 역할을 하는 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

GROUP BY 작업을 수행 시 인덱스의 사용 유무에 따른 작동 방식은 다음과 같다.

인덱스 사용
- 인덱스 스캔
- 루스 인덱스 스캔
인덱스 사용 X
- 임시 테이블 사용

2.4.1 인덱스 스캔 사용 GROUP BY(타이트 인덱스 스캔)

ORDER BY 와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 인덱스가 있다면 그 인덱스를 순차적으로 읽어 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

그러나, 인덱스를 사용해 처리해도 그룹 함수(Aggregation function) 등의 그룹값을 처리해야 하는 임시 테이블이 필요할 수 있다.

GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 쿼리 실행 시점에 추가적인 정렬 작업이나 내부 임시 테이블은 불필요하다.

2.4.2 루스 인덱스 스캔 사용 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 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리같지만 실제로 인덱스 레인지 스캔을 사용했고, 심지어 GROUP BY 까지 인덱스를 사용했다.

MySQL 서버가 이 쿼리를 처리하는 과정은 다음과 같다.

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

MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 루스 인덱스 스캔 방식으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.

또한, 프리픽스 인덱스의 경우는 루스 인덱스 방식을 사용할 수 없다.

프리픽스 인덱스 (Prefix index)
컬럼값의 앞쪽 일부분만 생성된 인덱스

루스 인덱스 스캔 사용 가능여부는 판단하기 어렵기에 여러 패턴의 쿼리를 보고 사용 여부를 판별하는 연습을 해야한다. 다음 쿼리들은 루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴이다.

// MIN()과 MAX() 이외의 집합 함수가 사용되었기에 루스 인덱스 스캔 사용 불가
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1;

// GROUP BY에 사용된 컬럼이 인덱스 구성 컬럼의 왼쪽부터 일치하지 않기 때문에 사용 불가
SELECT col1, col2 FROM tb_test GROUP BY col2, col3;

// SELECT 절의 컬럼이 GROUP BY와 일치하지 않기 때문에 사용 불가
SELECT col1, col3 FROM tb_test GROUP BY col1, col2;

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

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

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이기 때문이다.

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

즉, 위 쿼리를 처리하기 위해 MySQL은 다음 같이 임시 테이블을 생성한다.

CREATE TEMPORARY TABLE ... (
	last_name VARCHAR(16),
    salary INT,
    UNIQUE INDEX ux_lastname (last_name)
);

그리고, 조인 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE를 실행한다. 즉, 별도의 정렬 작업 없이 GROUP BY가 실행된다.

2.5 DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.

MIN(), MAX(), COUNT() 같은 집합 함수와 함께 사용되는지 여부에 따라 DISTINCT 가 영향을 미치는 범위가 달라지기에 2가지로 구구분에서 알아보자.

2.5.1 SELECT DISTINCT

단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오려면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY 와 동일하게 처리된다.

특히, GROUP BY 를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 다음 두 쿼리는 내부적으로 같은 작업을 수행한다.

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

DISTINCT 를 사용할 때 자주 실수하는 건 DISTINCT를 특정 컬럼만 유니크하게 조회할 때 사용하는 것이다.

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

즉, 다음 쿼리에서 first_name 만 유니크하게 가져오는 것이 아니라 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져오는 것이다.

mysql> SELECT DISTINCT first_name, last_name FROM employees;

가끔 DISTINCT를 다음과 같이 함수처럼 사용하는 사람도 있다.

mysql> SELECT DISTINCT(first_name), last_name FROM employees;

이렇게 하면 MySQL 서버는 DISTINCT 뒤 괄호를 의미없다 해석 후 제거해 버린다. DISTINCT는 함수가 아니므로 그 뒤에 괄호는 의미가 없는 것이다. 따라서, 위 쿼리는 바로 위(괄호를 사용하지 않은) 쿼리와 동일하다.

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

그러나, 집합 함수와 함께 사용된 DISTINCT 의 경우는 조금 다르다.

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

COUNT() 또는 MIN(), MAX() 같은 집합 함수와 사용되는 DISTINCT 는 SELECT DISTINCT 와는 다른 형태로 해석된다.

집합 함수가 없는 SELECT 쿼리는 조회하는 모든 컬럼의 조합이 유니크한 것을 가져오지만, 집합 함수내에서 사용되는 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) 를 처리하기 위해 임시 테이블을 사용한다. 하지만 이 쿼리 실행 계획에는 임시 테이블을 사용한다는 메시지는 표기되지 않는다.

위 쿼리의 경우 employees 테이블과 salaries 테이블을 조인한 결과에서 salary 컬럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다. 이 때, 임시 테이블의 salary 컬럼에는 유니크 인덱스가 생성되기에 레코드 건수가 많아진다면 상당히 느려질 수 있다.

위 쿼리에서 SELECT 절에 COUNT(DISTINCT e.last_name) 을 추가해보자.

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

실행 계획은 이전 쿼리와 동일하게 표시된다. 하지만, 이 쿼리를 처리하려면 s.salary 컬럼의 값을 저장하는 임시 테이블과 e.last_name 컬럼의 값을 저장하는 또 다른 임시 테이블이 필요하므로 전체적으로 2개의 임시 테이블을 사용한다.

이 쿼리들은 DISTINCT 처리를 위해 인덱스를 사용할 수 없어 임시 테이블이 필요했다. 하지만, 다음 쿼리 같이 인덱스된 컬럼에 대해서만 DISTINCT 처리를 수행할 때는 인덱스 풀/레인지 스캔을 하면서 임시 테이블을 사용안할 수 있다.

mysql> SELECT COUNT(DISTICT emp_no) FROM employees;
mysql> SELECT COUNT(DISTICT emp_no) FROM dept_emp GROUP BY dept_no;

주의
DISTINCT 가 집합 함수 없이 사용된 경우와 집합 함수 내에서 사용된 경우에 따라 쿼리의 결과가 조금씩 달라지기 때문에 그 차이를 정확히 알아야 한다. 다음 3개의 쿼리의 차이를 잘 알아두자.
mysql> SELECT DISTINCT first_name, last name
	   FROM employees
	   WHERE emp_no BETWEEN 10001 AND 10200;
mysql> SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last name)
       FROM employees
	   WHERE emp_no BETWEEN 10001 AND 10200;
mysql> SELECT COUNT(DISTINCT first_name, last name)
	   FROM employees
	   WHERE emp_no BETWEEN 10001 AND 10200;

2.6 내부 임시 테이블 활용

MySQL 서버가 스토리지 엔진으로 부터 받아온 레코드를 정렬(ORDER BY)하거나 그루핑(GROUP BY) 할 때 사용하는 내부 임시 테이블은 CREATE TEMPORARY TABLE 명령으로 만드는 임시 테이블과는 다르다.

일반적으로 사용하는 임시 테이블은 처음에는 메모리에 생성되었다 테이블의 크기가 커지면 디스크로 이동한다. 반면 내부 임시 테이블은 다른 세션이나 다른 쿼리에서 볼 수 없으며 사용하는 것또한 불가능하다. 또한, 내부 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

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

메모리 임시 테이블과 디스크 임시 테이블이 사용하는 스토리지 엔진은 다음과 같다.

메모리 임시 테이블 : TempTable 스토리지 엔진
- 가변 길이 타입을 지원
디스크 임시 테이블 : InnoDB 스토리지 엔진
- 트랜잭션 지원 가능

internal_tmp_mem_storage_engine 시스템 변수를 통해 메모리용 임시 테이블을 MEMORY 또는 TempTable(디폴트) 중 선택할 수 있다.

TempTable은 최대 사용 가능 메모리 공간 크기를 temptable_max_ram 시스템 변수로 제어할 수 있다.

임시 테이블의 크기가 1GB 보다 커지는 경우 MySQL 서버는 메모리의 임시 테이블을 디스크로 기록하는데 방식은 2가지가 있다.

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

temtable_use_mmap 시스템 변수를 통해 이 두 가지 방식중 하나를 선택할 수 있다. 또한, TempTable을 MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문에 기본값은 ON(1GB 넘을시 TempTable -> MMAP) 이다.

이 때, 디스크에 생성되는 임시 테이블은 tmpdir 시스템 변수에 정의된 디렉터리에 저장된다.

하지만, 내부 임시 테이블이 메모리에 생성되지 않고 처음부터 디스크 테이블로 생성되는 경우도 있다. 이 경우에는 internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용된다. 이 시스템 변수의 기본값은 InnoDB다.

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

MySQL 엔진에서 별도의 데이터 가공이 필요한 케이스 외에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 한다.

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

어떤 쿼리 실행 계획에서 임시 테이블을 사용하는지 여부는 Extra 컬럼에 "Using temporary" 라는 메시지가 표시되는지 확인하며 된다.
하지만, 이것이 표시되지 않고도 임시 테이블이 사용될 수 있는데 위 예에서 마지막 3개 패턴이 그러한 예다.

1 ~ 4번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다. 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 상대적으로 처리 성능이 상당히 느리다.

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

내부 임시 테이블은 다음 조건들을 만족하면 디스크 기반의 임시 테이블을 사용한다.

UNION 이나 UNION ALL에서 SELECT 되는 컬럼 중에서 길이가 512 byte 이상인 크기의 컬럼이 있는 경우
GROUP BY 나 DISTINCT 컬럼에서 512 byte 이상인 크기의 컬럼이 있는 경우
MEMORY 스토리지 엔진에서 메모리 임시 테이블의 크기가tmp_table_size 또는 max_heap_table_size 시스템 변수보다 큰 경우
TempTable 스토리지 엔진에서 메모리 임시 테이블의 크기가 temptable_max_ram 시스템 변수 값보다 큰 경우

이들의 공통점은 용량이 너무 커서 메모리에 다 담을 수 없는 경우이다.

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

임시 테이블이 디스크에서 생성되었는지 메모리에 생성되었는지 그리고 각각의 개수는 몇개인지를 확인하려면 MySQL 서버의 상태 변수(SHOW SESSION STATUS LIKE 'Created_tmp%';) 를 확인해보면 된다.

3. 고급 최적화

옵티마이저 옵션 : 실행 계획을 수립할 때 통계 정보와 함께 사용되어 최적의 실행 계획을 유도

조인 관련 옵션
- 일반적으로 신경쓰지 않는다.
옵티마이저 스위치

이들은 MySQL 서버의 고급 최적화 기능들을 활성화할지 여부를 결정

3.1 옵티마이저 스위치 옵션

optimizer_switch 시스템 변수로 제어 가능하며 이 시스템 변수에 설정할 수 있는 최적화 옵션은 다음과 같다.

각각은 default, on, off 중 하나를 설정할 수 있다.

3.1.1 MRR과 배치 키 엑세스(mrr & batched_key_access)

네스티드 루프 조인(Nested Loop Join)
드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블(조인되는 테이블에서 드라이빙이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인을 수행하는 것

MySQL 서버에서 대부분의 조인은 네스티드 루프 조인(Nested Loop Join) 인데, 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용된다.

조인 처리 : MySQL 엔진이 담당
실제 레코드를 검색하고 읽는 부분 : 스토리지 엔진이 담당

이 때, 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 스토리지 엔진에서는 최적화 수행이 불가능하다.

MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과 조인을 즉시 실행하지 않고 버퍼링한다.

조인 버퍼에 레코드가 가득차면 버퍼에 있는 레코드를 스토리지 엔진으로 한 번에 요청한다. 이렇게 하면 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해 디스크나 InnoDB 버퍼풀(데이터 페이지가 메모리에 있는 경우) 읽기를 최소화한다.

이러한 방식을 MRR(Multi-Range Read)라 하며 이를 이용해 실행되는 조인 방식을 BKA(Batched Key Access)라 한다.

BKA 조인은 부가적인 정렬 작업이 필요해지는 등의 여러 단점들로 인해 기본적으로 비활성화되있는데, 특정 쿼리에 따라 BKA 조인이 큰 도움이 되는 경우가 있다.

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

다음 예제 쿼리를 살펴보자.

mysql> EXPLAIN
		  SELECT *
          FROM employees e
            INNER JOIN salaries s ON s.emp_no=e.emp_no
            		   AND s.from_date<=NOW()
                       AND s.to_date>=NOW()
            WHERE e.first_name='Amor';

employees 테이블에서 first_name 조건에 일치하는 레코드 1건을 찾아서 salaries 테이블의 일치하는 레코드를 찾는 형태의 조인을 실행한다.

이러한 형태의 조인은 이중 반복문을 사용하는 것 처럼 작동한다해서 네스티드 루프 조인이라 한다.

다음 의사 코드에서 알 수 있듯 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 그 즉시 드리븐 테이블의 레코드를 찾아서 반환한다.

for (row1 IN employees) {
	for (row2 IN salaries) {
    	if (condition_matched)
        	return (row1, row2);
    }
}

블록 네스티드 조인이 위와 다른 점은 다음과 같다.

조인 버퍼(join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용됨
조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐
조인 알고리즘에서 "Block" 이라는 단어가 있거나 조인 쿼리의 실행 계획의 Extra 컬럼에 "Using Join buffer" 문구가 표시되는 경우

조인은 드라이빙 테이블에서 일치하는 레코드 건수만큼 드리븐 테이블을 검색하면서 처리된다. 즉, 드라이빙 테이블을 한 번에 쭉 읽지만, 드리븐 테이블은 이 때마다 여러번 읽는다.

예를 들어 드라이빙 테이블에서 일치하는 레코드가 1,000건이였는데, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없다면 총 1,000번의 풀 테이블 스캔을 해야한다. 이는 매우 느리다.

더불어 어떤 방식으로도 테이블 풀 스캔이나 인덱스 풀 스캔을 피할 수 없다면 조인 버퍼를 사용하여 드라이빙 테이블에서 읽는 레코드를 캐싱한다.

다음 예제 쿼리를 보자.

mysql> SELECT *
	   FROM dept_emp de, employees e
       WHERE de.from_date>'1995-01-01' AND e.emp_no<109004;

실행 계획을 살펴보면 다음을 알 수 있다.

드라이빙 테이블 : dept_emp 테이블
employees 테이블을 읽을 때 조인 버퍼를 이용해 블록 네스티드 루프 조인을 한다.

dept_emp 테이블의 ix_fromdate 인덱스를 통해 de.from_date>'1995-01-01'를 만족하는 레코드를 검색
조인에 필요한 나머지 컬럼들을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장
employees 테이블의 PK를 이용해 e.emp_no<109004 조건을 만족하는 레코드를 검색
3번에서 검색된 결과에 2번의 캐싱된 조인 버퍼 레코드를 결합해서 반환

중요한 점을 조인 버퍼가 사용되는 쿼리에선 조인의 순서가 거꾸로인 것 처럼 실행된다.

실제 이 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 된다. 하지만 실제 드라이빙 테이블의 결과를 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에 일치하는 레코드를 찾는 방식으로 처리된다.

일반적으로 조인이 수행딘 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되는 반면 조인 버퍼가 사용되면 이 결과의 정렬 순서가 흐트러질 수 있음을 알아두자.

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

다음 쿼리를 살펴보자.

mysql> SELECT * FROM employees WHERE last_name='Acton' AND first_name LIKE '%sal';

이 쿼리가 인덱스 컨디션 푸시다운이 작동되지 않는다면 다음 순서로 작동한다

last_name='Acton' 조건을 찾는다.
이 조건들 중 first_name LIKE '%sal' 조건에 부합되는지 여부를 확인한다.

그러나, 만약 첫 번째 조건(last_name='Acton')의 레코드 건수가 100,000건인데 이들 중 두번째 조건을 만족하는 레코드가 1건이라면 어떨까? 우리는 하나의 레코드를 찾기 위해 99,999건의 쓸데없는 레코드를 추가로 읽은 꼴이 된다.

첫 번째 조건 비교와 두 번째 조건 비교는 각각 InnoDB 스토리지 엔진과 MySQL 엔진이 수행하는 작업이다. MySQL의 엔진은 스토리지 엔진에게 first_name 관련 조건이 인덱스를 범위 제한 조건으로 사용하지 못하는 것을 알기에 전달해주지 않았다.

MySQL에서는 핸들러 API가 이렇게 인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 포함된 컬럼 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있도록 해준다. 따라서, 첫 번째 조건을 인덱스를 통해 비교하면 불필요한 레코드를 조회할 일이 없어진다.

3.1.4 인덱스 확장(use_index_extensions)

이 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 PK를 활용할 수 있게 해주는 옵션이다.

다음과 같이 PK와 세컨더리 인덱스를 가진 테이블을 생성해보자.

mysql> CREATE TABLE dept_emp (
		 emp_no INT NOT NULL,
         dept_no CHAR(4) NOT NULL,
         from_date DATE NOT NULL,
         to_date DATE NOT NULL,
         PRIMARY KEY (dept_no, emp_no),
         KEY ix_fromdate (from_date)
       ) ENGINE=InnoDB;

PK는 (dept_no, emp_no) 이다.
세컨더리 인덱스는 ix_fromdate이다.
- 이는 from_date 컬럼만 포함

세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 PK에 속하는 컬럼들(dept_no, emp_no)을 순서대로 포함한다.

따라서, 최종적으로 ix_fromdate 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있게 된다.

MySQL 서버의 옵티마이저는 ix_fromdate의 마지막에 (dept_no, emp_no) 컬럼이 숨어있다는 것을 인지하고 실행 계획을 수립한다.

3.1.5 인덱스 머지(index_merge)

인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다. 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상된다면 MySQL 서버는 이 실행 계획을 선택한다.

인덱스 머지 실행 계획을 다음과 같이 3가지의 세부 실행 계획으로 나눌 수 있다.

index_merge_intersection
index_merge_sort_union
index_merge_union

이들은 모두 여러 개의 인덱스를 통해 결과를 가져온다는 것은 동일하나 각각의 결과를 어떤 방식으로 병합하는지에 따라 구분된다.

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

다음 쿼리르 살펴보자.

mysql> EXPLAIN SELECT *
	   FROM employees
       WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

employees 테이블은 WHERE 절의 두 컬럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있다.
즉, 2개 중 어떤 조건을 사용하더라도 인덱스를 사용할 수 있다.

이에 따라 옵티마이저는 ix_firstname와 PRIMARY를 모두 사용해서 쿼리를 처리하기로 결정한다. 물론, 각각의 조건에 일치하는 레코드 건수를 예측하여 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다면 이 실행 계획이 수립된다.

인덱스 머지 실행계획이 아니였다면 다음 2가지 방식으로 처리해야 한다.

`first_name='Georgi' 조건에만 인덱스를 사용한 경우

이를 만족하는 레코드 중 emp_no BETWEEN 10000 AND 20000 조건이 일치하는 레코드들만 반환하는 형태로 처리

emp_no BETWEEN 10000 AND 20000 조건만 인덱스를 사용한 경우

PK값을 통해 만족하는 레코드를 읽어온 후 이들 중 first_name='Georgi' 조건에 일치하는 레코드들만 반환하는 형태로 처리

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

다음 예제 쿼리를 살펴보자.

mysql> SELECT *
	   FROM employees
       WHERE first_name='Matt' OR hire_date='1987-03-31';

2개의 조건이 OR로 연결되있다는 것에 주의
employees 테이블은 first_name 컬럼과 hire_date 컬럼에 각각 ix_firstname 인덱스와 ix_hiredate 가 준비되어있다.

이 조건들로 레코드를 가져오면 합집합을 가져오는 것이므로 중복이 반드시 발생한다. 그러나, 이 쿼리의 결과에선 중복 출력이 되지 않는다.

OR 사이의 두 조건들 각각의 결과를 정렬해서 중복 레코드를 제거하진 않았다. 그러면 어떻게 할까?

MySQL 서버는 first_name 조건을 검색한 결과와 hire_date 컬럼을 검색한 결과가 PK로 이미 각각 정렬되있다는 것을 안다.

따라서, MySQL 서버는 두 집합에서 하나씩 가져와 서로 비교하면서 PK인 emp_no 컬럼의 값이 중복된 레코드들을 정렬없이 걸러낼 수 있었다. 두 결과를 가져와 하나로 합쳐주는 역할은 우선순위 큐(Priority Queue)가 담당한다.

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

정렬을 하지 않고 PK 값을 통해 우선순위 큐를 사용하여 합집합하는 경우와 달리 인덱스 머지 작업을 하는 도중 정렬이 필요한 경우도 있다.

다음 쿼리를 보자.

mysql> EXPLAIN
	   	 SELECT * FROM employees
         WHERE first_name='Matt'
         	OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'

BETWEEN 때문에 hire_date 관련 컬럼들을 정렬이 되있어야 한다. 따라서, 중복 제거를 위해 우선순위 큐를 사용하는 것은 불가능하다.

이러한 경우에는 강제로 정렬이 수행된다.

3.1.9 세미 조인(semijoin)

세미 조인
다른 테이블과 실제 조인하지 않고, 단지 다른 테이블에서 조건에 일치하는 레코드 존재 여부만 판단하는 형태의 쿼리

다음 쿼리를 보자.

mysql> SELECT *
	   FROM employees e
       WHERE e.emp_no IN
       	   (SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01');

WHERE 절의 서브 쿼리를 먼저 실행하는 것은 세미 조인 덕분이다.

만약, 세미 조인이 비활성화 되있다면 employees 테이블을 풀 스캔하면서 하나한 서브쿼리 조건에 일치하는지 비교한다.

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

테이블 풀 아웃
세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화

이는 서브쿼리 최적화가 도입되기 전 수동으로 쿼리를 튜닝하던 대표적인 방법이였다.

다음 쿼리를 보자.

mysql> EXPLAIN
	   SELECT * FROM employees e
       WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');

부서 번호가 d009 인 부서에 소속된 모든 사원을 조회하는 쿼리
IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태일 것으로 예상됨

dept_emp 테이블과 employees 테이블은 서브쿼리 형태가 아닌 조인으로 처리가 되었다.

물론, Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니다. 몇 가지 제안사항과 특성은 다음과 같다.

세미 조인 서브쿼리에서만 사용 가능
서브쿼리 부분이 UNIQUE 인덱스이나 PK 룩업으로 결과가 1건인 경우에만 사용가능
Table pullout이 적용되어도 기존 쿼리에서 가능했던 방법이 사용 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화를 최대한 적용
Table pullout은 서브쿼리의 테이블을 아우터 쿼리로 가져와 조인으로 풀어쓰는 최적화를 수행하는데, 만약 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어진다.

3.1.11 퍼스트 매치(firstmatch)

First Match 최적화 전략은 IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.

다음 예제 쿼리를 보자.

mysql> EXPLAIN SELECT *
	   FROM employees e WHERE e.first_name='Matt'
         AND e.emp_no IN (
           SELECT t.emp_no FROM titles t
           WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
           );

이름(first_name)이 'Matt' 인 사원들 중 1995년 1월 1일부터 30일 사이에 직급이 변경된 적이 있는 사원을 조회하는 용도의 쿼리이다.

이 쿼리의 실행 계획를 살펴보면 id 컬럼값이 모두 1로 표시되고 Extra 컬럼에는 "FirstMatch(e)" 라는 문구가 출력된다.

이들의 의미는 title 관련 서브 쿼리가 실행되지 않고, 단순히 employees 테이블의 레코드에서 titles 테이블에 일치하는 레코드 1건만 찾으면 된다는 뜻이다.

즉, FirstNatch는 서브쿼리 구문을 조인으로 풀어 실행하여 일치하는 첫 번째 레코드만 검색하여 최적화를 실행한다.

FirstMatch 최적화의 몇 가지 제한 사항과 특성은 다음과 같다.

서브 쿼리에서 하나의 레코드만 검색하면 더 이상 검색을 멈추는 단축 실행 경로(Short-cut path)이기 때문에 FirstMatch 최적화에서 서브 쿼리는 그 서브 쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 후 실행된다.
FirstMatch 최적화가 사용되면 실행 계획의 Extra 컬럼에는 "FirstMatch(table-N)" 문구가 표시된다.
상관 서브쿼리(Correlate subquery)에서도 사용될 수 있다.
GROUP BY 나 집합 함수가 사용된 서브 쿼리의 최적화에는 사용될 수 없다.
optimizer_switch 시스템 변수에서 semijoin 옵션과 firstmatch 옵션이 모두 ON 으로 설정되있어야 사용할 수 있다.
- 만일, FirstMatch 최적화만 비활성화 하기 위해선 firstmatch 만 OFF로 설정하자.

3.1.12 루스 스캔(loosescan)

다음 쿼리를 살펴보자.

mysql> EXPLAIN 
	   SELECT * FROM depertments WHERE e.first_name='Matt'
         AND e.emp_no IN (
           SELECT t.emp_no FROM titles t
           WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
           );

김민우

이전 포스트

옵티마이저와 힌트

MySQL

1. 개요

1.1 쿼리 실행 절차

1.2 옵티마이저의 종류

2. 기본 데이터 처리

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

InnoDB에서 풀 테이블 스캔 실행 과정

리드 어헤드 시작 시점 결정

풀 테이블 스캔, 풀 인덱스 스캔 적용 예시

2.2 병렬 처리

2.3 ORDER BY 처리(Using filesort)

소트 버퍼

정렬 알고리즘

싱글 패스

투 패스

정렬 처리 방법

인덱스를 이용한 정렬

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

임시 테이블을 이용한 정렬

정렬 처리 방법의 성능 비교

2.4 GROUP BY 처리

2.4.1 인덱스 스캔 사용 GROUP BY(타이트 인덱스 스캔)

2.4.2 루스 인덱스 스캔 사용 GROUP BY

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

2.5 DISTINCT 처리

2.5.1 SELECT DISTINCT

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

2.6 내부 임시 테이블 활용

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

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

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

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

3. 고급 최적화

3.1 옵티마이저 스위치 옵션

3.1.1 MRR과 배치 키 엑세스(mrr & batched_key_access)

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

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

3.1.4 인덱스 확장(use_index_extensions)

3.1.5 인덱스 머지(index_merge)

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

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

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

3.1.9 세미 조인(semijoin)

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

3.1.11 퍼스트 매치(firstmatch)

3.1.12 루스 스캔(loosescan)

데이터 암호화

0개의 댓글