Coding/개발 서적

[Real MySQL 8.0 V1] 09장. 옵티마이저와 힌트

kangplay 2025. 5. 16. 22:42

이번 장에서는 MySQL 서버가 사용자의 요청을 처리하기 위해 데이터를 가공하는 기본 절차와 빠른 성능을 보장하기 위해 수행하는 최적화에 대해 살펴본다.

9.1 데이터 처리

9.1.1 쿼리 실행 절차

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

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

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

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

  • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
  • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다. 또한 인덱스 레인지 스캔과는 다르게 순차 I/O로 이루어지기 때문에 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배정도 비용이 더 많이 드는 작업인 것으로 예측한다.

9.1.3 ORDER BY 처리(Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다. 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리를 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

하지만 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능하다. 즉, 인덱스를 사용할 수 없거나, ORDER BY 순서가 인덱스와 맞지 않을 때 MySQL은 별도의 정렬 단계를 수행한다.

소트 버퍼 

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다. 정렬해야할 레코드가 아주 소량이어서 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 정렬이 처리될 것이다. 

정렬해야할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 어떨까? 이때 MySQL은 정렬해야할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다. 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둔다. 그리고 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다. 이 병합 작업을 멀티 머지라고 표현하고, 이 작업들은 모두 디스크의 읽기와 쓰기를 유발한다.  

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글 패스와 투 패스 2가지 정렬 모드로 나눌 수 있다.

Single-pass (1-pass) 정렬 대상 컬럼 + 필요한 데이터가 메모리 안에 모두 있음 → 메모리에서 바로 정렬
Two-pass (2-pass) 먼저 정렬 대상 키만 모아 정렬한 뒤, 정렬된 키를 기준으로 다시 테이블에서 나머지 컬럼 조회

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

SELECT 쿼리에서 꼭 필요한 칼럼만 조회하도록 쿼리를 작성하는 것이 좋다고 권장하는 것은 바로 이런 이유 때문이다. 특히 정렬이 필요한 SELECT는 불필요한 칼럼을 SELECT하지 않게 쿼리를 작성하는 것이 효율적이다. 

 

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

 

조인과 ORDER BY가 함께 사용될 때, 인덱스를 사용할 수 있다면 별도의 "Filesort" 과정 없이 인덱스를 순서대로 읽어서 결과를 반환하지만, 사용할 수 없다면 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택한다. 

드라이빙 테이블만 정렬 조인 전에 선행 테이블(드라이빙 테이블)을 정렬한 뒤, 그 순서대로 조인 진행
조인 결과 전체 정렬 조인을 다 수행한 후, 결과 전체를 임시 테이블에 저장하고 정렬(filesort)

 

일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다. 이 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY절을 작성해야한다.

 

다음은 employees 테이블과 salaries을 조인하고, e.last_name을 기준으로 정렬하는 과정이다.

임시 테이블을 이용한 정렬

 앞에서 살펴본 "조인의 드라이빙 테이블만 정렬"은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않는다. 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다. 이 방법은 정렬의 3가지 방법 가운데 정렬해야할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다. 

 

만약 employees 테이블과 salaries을 조인하고, s.salary를 기준으로 정렬한다면 어떻게 될까? 이 쿼리도 employees 테이블이 드라이빙 테이블로 사용되며, salaries 테이블이 드리븐 테이블로 사용될 것이다. 하지만, ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 칼럼이다. 즉 정렬이 수행되기 전에 salaries 테이블을 읽어야하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수밖에 없다. 따라서, 조인 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미한다. 

드라이빙 테이블은 실행 계획에서 윗 줄에 있는 테이블이다.

따라서, ORDER BY를 드라이빙 테이블에 있는 속성을 기준으로 하자.

😒 LIMIT는 성능을 높여줄까?
일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL이 처리해야할 작업량을 줄이는 역할을 한다. 그런데 Order by나 Group by 같은 작업은 Where 조건을 만족하는 레코드를 Limit 건수만큼만 가져와서는 처리할 수 없다. 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 Limit로 건수를 제할할 수 있기 때문이다.

9.1.3 GROUP BY 처리

ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹함수 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없다.

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

위에 쿼리는 where 조건에 있는 from_date를 기준으로 인덱스를 사용할 수는 없지만, 아래와 같이 인덱스 레인지 스캔을 이용하며, GROUP BY 처리까지 인덱스를 사용했다는 것을 알 수 있다.

이는 다음과 같이 루스 인덱스 스캔 방식으로 처리했기 때문이다. 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 또한, 유니크한 값의 수가 적을 수록 성능이 향상된다. 이때는 별도의 임시 테이블이 필요하지 않다.

다음 쿼리는 GROUP BY 처리 시, 임시 테이블이 필요한 경우이다.

//단일 테이블
SELECT first_name, COUNT(*) 
FROM employees 
GROUP BY first_name;

//조인 테이블
SELECT e.last_name, AVG(s.salary)
FROM employess e, salaries s
WHERE s.emp_no=e.emp_no
GROUP BY e.last_name;

GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. GROUP BY는 인덱스가 없는 경우 정렬과 집계를 임시 테이블에서 하기 때문이다. 

Using temporary: 임시테이블 사용, Using filesort가 표시되지 않은 이유는, GROUP BY는 있지만 ORDER BY절이 없는 쿼리에 대해서는 묵시적인 정렬을 실행하지 않기 때문이다.

9.1.4 DISTINCT 처리

DISTINCT는 SELECT하는 레코드(튜플)을 유니크하게 SELECT해준다. 단, 특정 칼럼만 유니크하게 조회하는 것이 아니다. 즉, 다음 쿼리에서 SELECT하는 결과는 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name,last_name) 조합 전체가 유니크한 레코드를 가져오는 것이다.

SELECT DISTINCT first_name, last_name FROM employees;
집합 함수와 함께 사용된 DISTINCT

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

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

이 쿼리는 내부적으로는 COUNT(DISTINCT s.salary)를 처리하기 위해 임시 테이블을 사용한다. 즉, employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다.

s.salary 중복 제거 처리를 위한 임시테이블이 필요하다. 만약 두 개 속성에 대해 DISTINCT가 적용된다면 두 개의 임시 테이블이 필요하다.

9.1.5 내부 임시테이블 사용

사용자가 생성한 임시 테이블(CREATE TEMPORARY TABLE)과는 달리 내부적인 임시 테이블은 다른 세션에서 볼 수 없으며 쿼리의 처리가 완료되면 자동으로 삭제된다.

 

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

  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
    -> 정렬과 그룹핑을 한 번에 못 해서, 두 번에 나눠 처리해야한다.
  • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서 상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINC가 사용된 쿼리
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

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

MySQL 서버의 상태 변수
Using temporary보다 임시 테이블의 갯수라던지, 디스크인지 메모리인지 등 더 자세한 정보를 알기 위해서는 SHOW SESSION STATUS LIKE 'Created_tmp%' 를 통해 MySQL 서버의 상태 변수를 확인해보면 된다.

9.2 고급 최적화

9.2.1 Nested Loop Join과 MRR(Multi-Range Read)

MySQL 서버에서 지원하던 조인 방식은 드라이빙 테이블의 레코드를 한 건 읽어서 일치하는 레코드를 드리븐 테이블에서 찾아 조인을 수행하는 것이었다. 이를 네스티드 루프 조인(Nested Loop Join)이라고 한다. 드리븐 테이블에 인덱스 없으면 랜덤 I/O 많아서 매우 느리다는 단점이 있다. 

 

이 같은 단점을 보완하기 위해 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것이다. 이를 Multi-Range Read 라고 한다.

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

MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인인데, 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이다. 다음의 예제는 first_name 조건에 일치하는 레코드 1건을 employees 테이블에서 찾아 salaries 테이블의 일치하는 레코드를 찾는 형태의 조인을 실행한다.

만약 어떠한 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이때 사용되는 메모리의 캐시를 조인 버퍼(Join buffer)라고 한다.

실제 이 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 된다. 하지만 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 잃고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다. 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과를 정렬 순서가 흐트러질 수 있음을 기억해야한다.

9.2.3 인덱스 컨디션 푸시다운(index_condition_pushdown)

인덱스 컨디션 푸시다운은 인덱스를 이용해 최대한 필터링까지 완료해서 꼭 필요한 레코드 1에 대해서만 테이블 읽기를 수행할 수 있게 해준다. 다음은 인덱스 컨디션 푸시다운이 적용되지 않은 예제이다.

SELECT * 
FROM employees 
WHERE last_name='Action' AND first_name LIKE '%sal';

위 실행계획의 Using where을 보면, last_name에 대한 조건에 대해 인덱스를 통해 처리하고, %sal에 대한 처리는 인덱스를 사용하지 않고 조건에 일치하는 검사하는 과정(체크 조건)인 것을 알 수 있다.

다음은 인덱스 컨디션 푸시 다운이 작동하지 않을 때 과정을 그림으로 표현한 것이다.

만약 last_name='Action' 조건에 일치하는 레코드가 10만 건이나 되는데, 그 중에서 단 1건만 first_name LIKE '%sal' 조건에 일치했다면 어땠을까? 이 경우에는 99,999건의 레코드 읽기가 불필요한 작업이 되어버린다.

 

하지만 first_name LIKE '%sal' 조건을 처리하기 위해 이미 한 번 읽은 ix_lastname_firstname 인덱스의 first_name 칼럼을 이용한다면, 불필요한 2건의 레코드는 테이블에서 읽지 않아도 된다. MySQL 5.6버전부터는 이렇게 인덱스를 이용해 최대한 필터링까지 완료해서 꼭 필요한 레코드 1건에 대해서만 테이블 읽기를 수행할 수 있게 된다.

아래는 인덱스 컨디션 푸시다운을 이용한 실행계획이다.

9.2.4 인덱스 확장(use_index_extensions)

use_index_extensions 옵티마이저 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스(pk 아닌 인덱스)에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.

실행계획의 key_len 칼럼은 이 쿼리가 인덱스를 구성하는 칼럼 중에서 어느 칼럼까지 사용했는지를 바이트 수로 보여주는데, 이 예제에서 19 바이트가 표시된 것을 보면 dept_no 까지 사용했다는 것을 알 수 있다. (만약 dept_no='d001' 조건을 제거하면 key_len 칼럼의 값이 3으로 표시된다.)

아래와 같은 정렬 예제에서도 인덱스 확장 옵션이 활용된다. Using Filesort가 표시되지 않았다는 것은 별도의 정렬 작업 없이 인덱스 순서대로 레코드를 읽기만 함으로써 ORDER BY dept_no 를 만족했다는 것을 의미한다.

9.2.5 인덱스 머지(index_merge)

인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립한다.(하나는 인덱스를 검색, 나머지는 체크하는 형태로만 사용) 이처럼 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 테이블 별로 하나의 인덱스만 활용하는 것이 효율적이다. 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것이라고 예상될 때 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리할 수 수 있다. 

 

인덱스 머지 실행 계획은 다음과 같이 3개의 세부 실행 계획으로 나누어 볼 수 있다. 

  • index_merge_intersection
  • index_merge_sort_union
  • index_merge_union
인덱스 머지 - 교집합(index_merge_intersection)
SELECT *
FROM employees
WHERE fist_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

 

위의 쿼리를 처리하기 위해, 2개의 조건 중 어떤 조건을 사용하더라도 인덱스를 사용할 수 있다. 

Using_intersect라고 표시된 것은 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미한다.

(만약 두 칼럼의 조건 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있었다면 옵티마이저는 2개의 인덱스를 모두 사용하는 실행 계획을 사용하지 않았을 것이다.)

인덱스 머지 - 합집합

Using union은 WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화다.

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

🚗 Union 알고리즘의 중복 처리
두 결과 집합에서 중복을 제거하기 위해서는 정렬 작업이 필요했을 것이다. 하지만 실제 실행 계획에는 정렬했다는 표시가 없다. 그 이유는 두 조건의 결과가 프라이머리 키로 이미 각각 정렬되어있고, MySQL 서버는 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있기 때문이다.


❗사실 SQL 문장에서 AND 연산과 OR 연산은 큰 차이를 보인다. AND 의 경우 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 쿼리가 실행되지만, OR 연산인 경우에는 하나라도 제대로 인덱스를 사용하지 못하면 항상 풀 테이블 스캔으로밖에 처리하지 못한다. 
인덱스 머지 - 정렬 후 합집합

만약 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 MySQL 서버는 인덱스 머지 최적화의 Sort union 알고리즘을 사용한다.

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

위에서 두번 째 쿼리에 해당하는 BETWEEN 쿼리의 결과는 emp_no으로 정렬되지 않는다. (BETWEEN은 인덱스 여러 개가 포함되므로, 인덱스가 아닌 PK로 정렬할 수 없다.)

따라서 위와 같이 각 집합은 emp_no으로 정렬한 다음에 중복 제거를 수행한다. 

9.2.6 세미조인(semijoin)

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

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

세미조인 최적화 기능이 없었을 때에는 위의 세미 조인 쿼리의 실행 계획은 다음과 같았다.

서브 쿼리 부분이 먼저 실행되고 일치하는 레코드만 검색하는 것이 아닌, employees 테이블을 풀 스캔하면서 서브쿼리의 조건에 일치하는지 비교하고 있다.

 

세미 조인 최적화를 세미 조인 형태의 쿼리와 안티 세미 조인 형태의 쿼리로 나누어서 살펴보자.

세미 조인 쿼리 (= 형태 혹은 IN 형태)
  • 세미 조인 최적화
  • IN-to_EXISTS 최적화
  • MATERIALIZATION 최적화
안티 세미 조인 쿼리 (<> 형태 혹은 NOT IN 형태)
  • IN-to-EXISTS 최적화
  • MATERIALIZATION 최적화

세미 조인 최적화에 대해 다섯 가지 전략이 있는데, 이에 대해 더 자세히 살펴보자.

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

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

위와 같이 IN 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리이다. 

가장 중요한 부분은 id 칼럼의 값이 모두 1이라는 것이다. 여기서 이 값이 동일한 값을 가진다는 것은 두 테이블이 서브쿼리 형태가 아니라 조인으로 처리됐음을 의미한다. 이는 서브쿼리 부분이 Unique 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에나 사용 가능하다.

 

MySQL에서는 최대한 서브 쿼리를 조인으로 풀어서 사용해라라는 튜닝 가이드가 많은데, Table pullout 최적화는 이 가이드를 그대로 실행하는 것이다. 

9.2.6.2 퍼스트 매치(firstmatch)

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

위의 쿼리를 실행하면 다음과 같이 실행 계획이 나온다.

실행 계획의 id 칼럼의 값이 모두 1로 표시된 것으로 봐서 FirstMatch 최적화 에제에서 titles 테이블이 서브 쿼리 패턴으로 실행되지 않고, 조인으로 처리됐다는 것을 알 수 있다.

Firstmatch는 서브 쿼리에서 하나의 레코드만 검색되면 더 이상의 검색을 멈추는 단축 실행 경로이기 때문에 FirstMatch 최적화에서 서브 쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.

9.2.6.3 루스 스캔(loosescan)

다음은 dept_emp 테이블에 존재하는 모든 부서 번호에 대해 부서 정보를 읽어오기 위한 쿼리이다.

SELECT * FROM departments d WHERE d.dept_no IN (
	SELECT de.dept_no FROM dept_emp de
)

departments 테이블의 레코드 건수는 9건밖에 되지 않지만, dept_emp 테이블의 레코드 건수는 무려 33만 건 가까이 저장되어 있다. 그런데 dept_emp 테이블에는 dept_no만으로만 그루핑해서 보면 결국 9건밖에 없다. 그렇다면 dept_emp 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 아주 효율적으로 서브쿼리 부분을 실행할 수 있다.

LooseScan 최적화는 루스 인덱스 스캔으로 서브 쿼리 테이블을 읽고, 그 다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다.