옵티마이저와 힌트

진성대·2025년 3월 13일

mysql

SQL

목록 보기

9/10

RealMySQL 책을 읽고 노션에 정리한 글을 옮긴 것입니다.

1. 개요

1.1 쿼리 실행 절차

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

사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

1.2 옵티마이저 종류

비용 기반 최적화(Cost-based optimizer, CBO)
- 테이블의 레코드 건수나 칼럼값의 분포도를 통해서 통계 정보를 만들어내고 이를 통해 실행 계획별 비용을 산출한다.
- 이렇게 산출된 실행 방법별로 비용이 최소로 드는 방법을 채택해서 실행한다.
규칙 기반 최적화(Rule-based optimizer, RBO)
- 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다.
- 이 방식은 통계 정보를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행방법을 만들어 낸다.
- 현재는 거의 사용하지 않고 있다.

2. 기본 데이터 처리

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

풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업
풀 테이블 스캔을 사용하는 경우
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(20 ~25% 넘어가는 경우)
InnoDB 에서의 풀 테이블 스캔
- 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 실행된다.
- 리드 어헤드(Read ahead) : 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해서 요청이 오기 전에 미리 디스크에 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
- 처음에는 포그라운드 스레드가 디스크에서 데이터를 가져오다가 리드 어헤드 작업을 통해서 백그라운드 스레드가 데이터를 가져오게 되는 것이다.
리드 어헤드는 풀 테이블 스캔뿐 아니라 풀 인덱스 스캔에서도 사용 가능하다.

mysql> SELECT COUNT (*) FROM employees

레코드 건수를 조회하고 있으므로 풀 테이블 스캔을 사용할 것 같지만 풀 인덱스 스캔을 사용한다.
풀 인덱스 스캔이 풀 테이블 스캔보다 비용이 적게 들기 때문이다

mysql> SELECT * FROM employees

이 경우는 모든 레코드를 다 읽어와야 하기 때문에 풀 테이블 스캔을 사용해야 한다.

2.2 병렬 처리

아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.
cpu 코어보다 많은 스레드를 장착하면 성능이 떨어질 수 있다.

2.3 ORDER BY 처리(Using filesort)

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

Filesort 이용 하는 경우

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

2.3.1 소트 버퍼
정렬을 수행하기 위한 별도의 메모리 공간, 가변적이며 정렬이 필요한 경우에만 할당된다.
소트 버퍼 공간보다 정렬해야할 레코드가 많다면 레코드를 여러 조각으로 분할 한 후, 정렬 한 후 그 결과를 디스크에 저장하고 정렬하기를 반복한다. 임시로 디스크에 저장한 레코드를 병합하는데 이를 멀티 머지(Multi-merge)라고 표현한다.
소트 버퍼 크기가 클수록 성능이 좋아지는 것이 아니다(56kb ~ 1MB) 가 적당하다.
소트버퍼는 글로벌 메모리 영역이 아닌 세션 메모리 영역이다. 고로 클라이언트가 많아지면 많아질 수록 소비되는 메모리 공간이 커진다.

2.3.2 정렬 알고리즘
싱글 패스(Single-pass) 알고리즘
- 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬 수행
- 처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다.
- 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트에게 넘겨준다.
- <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 고정 사이즈로 메모리 저장
- <sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 가변 사이즈로 메모리 저장
투 패스(Two-pass) 알고리즘
- 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 정렬 방식이다.
- 싱글 패스 방식이 도입되기 전 방식
- 테이블을 두 번 읽어야 하기 때문에 상당히 불합리 하다. 하지만 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요하다.
- 투 패스 정렬 방식을 사용하는 조건
  1. 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  2. BLOB 이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될때
- <sort_key, rowid> : 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식
싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라 볼 수 있다.

2.3.3 정렬 처리 방법

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

정렬 처리 방법	실행 계획의 Extra 칼럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort” 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	“Using temporary; Using filesort” 메시지가 표시됨

인덱스를 이용한 정렬
- ORDER BY에 사용된 컬럼이 드라이빙 테이블(첫 번째 읽는 테이블)에 속해야 한다.
- WHERE 절과 ORDER BY가 같은 인덱스를 활용할 수 있어야 한다.
  - ORDER BY 대상 칼럼의 순서와 동일한 B-Tree 인덱스가 존재해야 인덱스를 활용할 수 있다.
    - 인덱스가 정렬을 활용할 수 있는 경우
```
CREATE INDEX idx_status_created_at ON payments (status, created_at);
```
```
SELECT * FROM payments WHERE status = 'completed' ORDER BY created_at;
```
    - WHERE status = 'completed' 로 인해 status를 먼저 검색
    - 이후 created_at 기준으로 이미 인덱스에서 정렬된 상태이므로 추가적인 정렬 과정(Filesort)이 필요 없음.
    - 인덱스가 정렬을 활용할 수 없는 경우
```
SELECT * FROM payments ORDER BY created_at, status;
```
    - (created_at, status) 인덱스가 없고, (status, created_at)만 있는 경우 인덱스를 사용할 수 없다.
    - ORDER BY 대상 컬럼의 순서가 인덱스 순서와 일치해야 함.
      
      ⇒ 해결 방법
```
CREATE INDEX idx_created_at_status ON payments(created_at, status);
```
  - WHERE 절에서 사용하는 인덱스와 ORDER BY가 같은 인덱스를 사용할 수 있어야 인덱스를 활용한 정렬이 가능하다.
    - 인덱스를 활용한 정렬이 가능한 경우
```
CREATE INDEX idx_status_created_at ON payment(status, created_at);
```
```
SELECT * FROM payments WHERE status = 'completed' ORDER BY created_at;
```
    - WHERE status = 'completed' 을 만족하는 데이터만 조회.
    - ORDER BY created_at 이 인덱스 순서를 그대로 따르므로 추가적인 정렬이 필요 없음.
    - 인덱스를 활용한 정렬이 불가능한 경우
```
SELECT * FROM payments WHERE amount > 100 ORDER BY created_at;
```
    - WHERE amount > 100 에 대한 인덱스가 없거나.
    - ORDER BY created_at 과 같은 인덱스를 사용할 수 없는 경우 추가적인 정렬 과정(Filesort)이 발생
      
      ⇒ 해결 방법
```
CREATE INDEX idx_amount_created_at ON payments(amount, created_at);
```
      ⇒ WHERE 와 ORDER BY 가 같은 인덱스를 사용하도록 최적화 가능
- B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없다. (R-Tree도 특성상 인덱스 정렬을 사용할 수 없다.)
- 여러 테이블이 조인되는 경우에는 네스티드-루프 (Nested-loop) 방식의 조인에서만 이 방식을 사용할 수 있다.
- 네스티드-루프(Nested-loop)
  - MySQL 에서 조인을 수행하는 가장 기본적인 방식이며, 하나의 테이블(드라이빙 테이블)의 각 행을 반복하면서 다른 테이블(드리븐 테이블)과 매칭하는 방식이다.
  - 동작 방식
    - 예제 테이블
```
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    total_price DECIMAL(10,2),
    INDEX idx_customer_id (customer_id)  -- 인덱스 생성
);
```
    - 조인 쿼리
```
SELECT c.customer_id, c.name, o.order_id, o.total_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
```
    - Nested Loop Join 동작 과정
    1. 드라이빙 테이블(Outer Table, customers)을 하나씩 읽음
    2. 각 행에 대해, 드리븐 테이블(Inner Table, orders)에서 매칭되는 행을 찾음.
    3. 매칭된 행이 있으면 결과로 반환
    4. 반복적으로 모든 customers 테이블의 행을 확인하면서 orders 테이블과 비교
      
      ⇒ 즉, 바깥 루프(드라이빙 테이블)에서 행을 하나 가져오고, 안쪽 루프 (드리븐 테이블)에서 해당 행과 매칭되는 데이터를 찾는 구조
- 인덱스의 값이 정렬돼 있기 때문에 MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 하지 않는다. 그렇다고 ORDER BY 없이 쿼리를 작성한다고 하면 예외 상황에서 버그가 발생할 수 있다.
- ORDER BY 를 사용한다고 추가적인 작업이 발생하는것이 아니다.
- 조인이 사용된 쿼리의 실행계획에서 조인 버퍼가 사용되면 정렬된 순서가 흐트러질 수 있기 때문에 주의해야 한다.
조인의 드라이빙 테이블만 정렬
- 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것
- 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 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;
```
- employees 가 드라이빙 테이블이 되는 조건
  - WHERE 절의 검색 조건(”emp_no BETWEEN 10001 AND 100010”)은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
  - 드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 있다.
드라이빙 테이블 정렬 후 조인하는 과정
1. 인덱스를 이용해 “emp_no BETWEEN 100001 AND 100010” 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 칼럼으로 정렬을 수행 (Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴
임시 테이블을 이용한 정렬
- 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있다.
- 조인의 드라이빙 테이블만 정렬 외 의 상황에서는 임시 테이블을 사용한다.
- 이 방법은 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.
```
mysql> 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;
```
- ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 salary 칼럼이다.
- 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 조인된 데이터를 가지고 정렬을 해야한다.
정렬 처리 방법의 성능 비교
- LIMIT 를 ORDER BY, GROUP BY 랑 같이 사용하면 ORDER BY, GROUP BY 가 된 상태에서 LIMIT 해서 값을 가져온다.
1. 스트리밍 방식
- 서버 쪽에서 처리할 데이터가 얼마인지 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다.
- 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해준다.
- 스트리밍 방식으로 처리되는 쿼리에서 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여준다. 하지만 LIMIT, OFFSET 일 경우는 다르다.
2. 버퍼링 방식
- ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다.
- MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 못한다.
- 결과를 모은다음 MySQL 서버에서 일괄 가공해야한다.
- 버퍼링 방식으로 처리되는 쿼리는 LIMIT 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에는 별로 도움이 되지 않는다.
```
> JDBC 라이브러리는 MySQL 서버에서 스트리밍 처리해서 보내는 데이터를 버퍼에 담아뒀다가 한번에 클라이언트로 반환한다.  그 이유는 전체 처리 시간이 짧고 MySQL 서버와의 통신 횟수가 적어 자원 소모가 줄어들기 때문이다.
> 
- 인덱스 정렬만 스트리밍 형태이며 나머지 두가지는 버퍼링된 후에 정렬된다.
```
2.3.4 정렬 관련 상태 변수

Sort_merge_pass : 멀티 머지 처리 횟수
Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_rows : 지금까지 정렬한 전체 레코드 건수

2.4 GROUP BY 처리

Group By 절에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없다.
인덱스를 사용하는 경우(인덱스 스캔)와 사용하지 못하는 경우(루스 인덱스 스캔) 두가지 경우로 나뉜다. 인덱스를 사용하지 못하는 경우에는 임시 테이블을 사용한다.
2.4.1 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
- 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑 할때 GROUP BY 칼럼으로 인덱스가 있다면 인덱스를 이용해서 결과를 처리한다.
- GROUP BY가 인덱스를 사용해서 결과를 처리한다고 해도 그룹함수(Aggregation function) 등 의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
- 그루핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 칼럼에 별도로 GROUP BY 관련 코멘트 (”Using index for group-by”)나 임시 테이블 사용 또는 정렬 관련 코멘트(”Using temporary, Using filesort”)가 표시되지 않는다.
  
  2.4.2 루스 인덱스 스캔을 이용하는 GROUP BY
- 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 “Using index for group-by” 코멘트가 표시된다.
```
mysql> EXPLAIN SELECT emp_no FROM salaries WHERE from_date='1985-03-01' GROUP BY emp_no;
```
- WHERE 조건이 범위가 아니기 때문에 레인지 스캔 접근 방식으로 사용 할 수 없는 쿼리지만 이 쿼리는 인덱스 레인지 스캔 (Range 타입) 을 이용했다.
```
+---+---------------+-------------+----------+----------------------------------------+
| id|     table     |   type      |  key     |  Extra                                 |
+---+---------------+-------------+----------+----------------------------------------+
| 1 |    salaries   |   range     |  PRIMARY | Using where; Using index for group - by|
+---+---------------+-------------+----------+----------------------------------------+
```
1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번쨰 유일한(그룹 키) “10001”을 찾아낸다.
2. (emp_no, from_date) 인덱스에서 emp_no 가 ‘10001’ 인 것 중에서 from_date 값이 ‘1985-03-01’ 인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 ‘10001’ 값과 쿼리의 WHERE 절에 사용된 “from_date=’1985-03-01’” 조건을 합쳐서 “emp_no=10001 AND from_date=’1985-03-01’” 조건으로 (emp_no, from_date) 인덱스를 검색하는 것과 거의 흡사하다.
3. (emp_no, from_date) 인덱스에서 emp_no 의 그 다음 유니크한(그룹키) 값을 가져온다.
4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.
- 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. JOIN TABLE 에서는 사용 할 수 없다.
- 프리픽스 인덱스는 루스 인덱스를 사용 할 수 없다.
- 루스 인덱스 스캔은 인덱스 레인지 스캔과는 반대로 유니크한 값이 적을수록 성능이 향상된다.
- 루스 인덱스 스캔을 사용할 수 있는 쿼리
```
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;
```
- 루스 인덱스 스캔을 사용할 수 없는 쿼리
```
--// 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 일 경우는 임시 테이블을 사용해서 쿼리 결과를 도출한다.
- GROUP BY 만 있을 시에는 “Using temporary” 메시지만 표시되고 ORDER BY 가 있어야 “Using Filesort”를 사용한다.
  
  MySQL 8.0 부터는 GROUP BY 에 ORDER BY를 같이 사용하지 않으면 따로 정렬을 하지 않는다.

2.5 DISTINCT 처리

특정 칼럼의 유니크한 값만 조회 하려면 DISTINCT 를 사용해야 한다.

집합 함수와 같이 사용하는 DISTINCT 는 DISTINCT 가 인덱스를 사용하지 못하면 임시 테이블을 사용한다. 하지만 실행계획에서는 “Using temporary” 메시지가 출력 되지 않는다.

2.5.1 SELECT DISTINCT

ORDER BY 를 사용하지 않는 GROUP BY 랑 내부적으로 같은 작업을 수행한다.

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

DISTINCT는 SELECT 하는 레코드 자체를 유니크하게 해준다.
```
mysql> SELECT DISTINCT first_name, last_name FROM employees;
```
first_name 만 유니크하게 하는 것이 아닌 (first_name, last_name) 조합 전체가 유니크한 레코드가 된다.

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

COUNT(), MAX(), MIN() 같은 집합 함수와 같이 사용 하는 경우는 일반 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;

+-----+-----------+---------+-----------+-------------------------------------------+
| id  | table     | type    | key       | rows Extra                                |
+-----+-----------+---------+-----------+-------------------------------------------+
|   1 |  e        | range   | PRIMARY   | 100  Using where; Using Index             |
|   2 |  s        | ref     | PRIMARY   |  10  NULL                                 |
+-----+-----------+---------+-----------+-------------------------------------------+

이 쿼리는 COUNT(DISTINCT s.salary) 를 처리하기 위해 임시 테이블을 사용한다.

하지만 MySQL 의 실행계획 에서는 “Using Temporary”를 표시하지 않는다.

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

위의 쿼리 처럼 COUNT(DISTINCT e.last_name) 을 추가 한 경우 임시 테이블이 하나 더 생성된다.

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

집합 함수를 사용하지만 임시 테이블 없이 처리되는 경우는 DISTINCT 를 처리할 때 사용하는 것이 인덱스로 되어있으면 풀 스캔이나 레인지 스캔을 통해 임시테이블 없이 최적화로 처리 될 수 있다.

2.6 내부 임시 테이블 활용

스토리지 엔진으로 부터 받아온 레코드를 정렬하거나 그루핑 할때 내부적으로 임시테이블이 생성된다.
- ORDER BY, GROUP BY, DISTINCT 등 사용할때
CREATE TEMPORARY TABLE 명령으로 만들어진 임시 테이블과는 다르다. 쿼리가 종료되면 자동으로 삭제된다.
2.6.1 메모리 임시 테이블과 디스크 임시 테이블
- 기존에는 임시 테이블이 메모리를 사용할때는 MEMORY 스토리지 엔진을 사용하고, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용한다.
- 8.0 버전 부터는 메모리는 TempTable 이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다.
- temptable_max_ram 최대 사용 가능한 공간의 메모리 크기를 제어할 수 있는데 기본값이 1GB 로 되어있다. 만약 1GB 를 넘어가게 되면 디스크에 임시 테이블이 저장되고 이는 InnoDB 스토리지 엔진을 사용한다.
- 디스크 저장방법은 MMAP 파일로 디스크에 기록, InnoDB 테이블로 기록 두가지 방법이 있다.
- MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적다.
- 임시 테이블이 메모리로 처음부터 생성되는게 아니라 바로 디스크로 생성되는 경우도 있다.
  
  2.6.2 임시 테이블이 필요한 쿼리
- ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
- ORDER BY 나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
- DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
- UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
- 쿼리의 실행 계획에서 select_type이 DERIVED 인 쿼리
  
  2.6.3 임시 테이블이 디스크에 생성되는 경우
- UNION 이나 UNION ALL 에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
- GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
- 메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우
  
  2.6.4 임시 테이블 관련 상태 변수
- Using temporary 표시가 되어있다고 임시 테이블이 메모리에서 처리됐는지, 디스크에서 처리 됐는지, 한번만 사용됐는지 여러번 사용 됐는지 알 수 없다.
- 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인하기 위해서 SHOW SESSION STATUS LIKE 'Created_tmp%'; 를 사용해보면 된다.
  - Created_tmp_tables : 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값이다. 이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지 구분하지 않고 모두 누적한다.
  - Creawted_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값이다.

3. 고급 최적화

3.1 옵티마이저 스위치 옵션

옵티마이저 스위치 이름	기본값	설명
batched_key_access	off	BKA 조인 알고리즘을 사용할지 여부 설정
block_nested_loop	on	BLOCK Nested Loop 조인 알고리즘을 사용할지 여부 설정
engine_condition_pushdown	on	Engine Condition Pushdown 기능을 사용할지 여부 설정
index_condition_pushdown	on	Index Condition Pushdown 기능을 사용할지 여부 설정
use_index_extensions	on	Index Extension 최적화를 사용할지 여부 설정
index_merge	on	Index Merge 최적화를 사용할지 여부 설정
index_merge_intersection	on	Index Merge Intersection 최적화를 사용할지 여부 설정
index_merge_sort_union	on	Index Merge Sort Union 최적화를 사용할지 여부 설정
index_merge_union	on	Index Merge Union 최적화를 사용할지 여부 설정
mrr	on	MRR 최적화를 사용할지 여부 설정
mrr_cost_based	on	비용 기반의 MRR 최적화를 사용할지 여부 설정
semijoin	on	세미 조인 최적화를 사용할지 여부 설정
firstmatch	on	FirstMatch 세미 조인 최적화를 사용할지 여부 설정
loosescan	on	LooseScan 세미 조인 최적화를 사용할지 여부 설정
materialization	on	Materialization 최적화를 사용할지 여부 설정(Materialization 세미 조인 최적화 포함)
subquery_materialization_cost_based	on	비용 기반의 Materialization 최적화를 사용할지 여부 설정

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

MRR : Multi-Range Read를 줄여서 부르는 이름이다.
기존 네스티드 루프 조인(Nested Loop Join)을 보완하기 위해 고안됐는데 기존 방식은 드라이빙 레코드와 드리븐 레코드를 1:1로 비교를 해서 I/O 가 개별적으로 발생을 했다. JOIN 할 레코드가 많게 된다면 성능저하가 발생할 수 있기 때문에 이를 대체 하기 위해서 조인 버퍼 공간에 드라이빙 레코드를 일정 넣어놓는다.
조인 버퍼 에 있는 레코드가 일정 이상 차면 드리븐 레코드와 기존에 버퍼에 저장된 레코드와 한번에 JOIN 이 된다. 예를 들어 기존 방식은 1000개의 드라이빙 레코드가 있어서 JOIN 할때 I/O가 1000번 일어났다면 조인버퍼에 1000개를 한번에 넣고 I/O 를 발생시키면 JOIN 이 1번 발생하는거다.
조인 버퍼의 역할
1. 드라이빙 테이블의 레코드를 즉시 드리븐 테이블과 조인하지 않고 조인 버퍼에 저장한다.
2. 일정량(버퍼 크기)이 쌓이면, 버퍼에 있는 모든 레코드를 한 번에 드리븐 테이블과 조인한다.
3. 드리븐 테이블을 배치 조회(Batch Processing) 방식으로 접근하여 성능을 향상시킨다.
이를 응용해서 실행하는 조인 방식을 BKA(Batched Key Access)라고 한다. BKA는 부가적인 정렬 작업이 필요해지면서 성능에 안좋은 영향이 발생할 수 도 있다.

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

조건에 사용되는 컬럼이 두 테이블의 인덱스일 경우 조인 방식이 사용될 수 있다.

 SELECT * FROM employees e JOIN salaries s ON e.emp_no = s.emp_no

위의 경우 e의 emp_no 와 s 의 emp_no 모두 인덱스여야 한다.
네스티드 루프 조인과의 차이점은 조인버퍼 사용여부와, 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되는지 이다.
옵티마이저는 드리븐테이블에 인덱스를 사용하기 위해서 최대한 노력하지만 인덱스를 사용할 수 없는 경우에는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 메모리 캐시를 조인하는 형태로 처리된다. 이때 메모리의 캐시를 조인 버퍼 라고 한다.

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인덱스를 이용해 (from_date>’1995-01-01’) 조건을 만족하는 레코드를 검색한다.
조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로 읽어서 조인 버퍼에 저장한다.
employees 테이블의 프라이머리 키를 이용해 (emp_no<109004) 조건을 만족하는 레코드를 검색한다.
3번에서 검색된 결과 (employees)에 2번의 캐시된 조인 버퍼의 레코드(dept_emp)를 결합해서 반환한다.

위의 조인 버퍼가 사용되는 쿼리에서 조인의 순서가 거꾸로인 것처럼 실행되는데 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있다.

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

SQL 5.6 버전부터 도입된 기능이다.
옵티마이저 스위치를 조정해서 인덱스 컨디션 푸시다운 기능을 비활성화

mysql> ALTER TABLE employees ADD INDEX ix_lastname_firstname (last_name, first_name);

mysql> SET optimizer_switch='index_condition_pushdown=off';
mysql> SHOW VARIABLES LIKE 'optimizer_switch'

Variable_name : optimizer_switch
				VALUE : ..., index_condition_pushdown=off,...

SQL 5.6 이전 버전 (인덱스 컨디션 푸시다운 기능 비활성화)

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

위의 쿼리를 실행할때 last_name=’Action’ 조건은 위에 생성한 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용할 수 있다.
first_name LIKE ‘%sal’ 조건은 인덱스 레인지 스캔으로는 범위를 좁힐 수 없다. ‘%sal’ 조건을 찾기 위해서는 체크조건 또는 필터링 조건을 사용해서 찾아야 한다.

last_name=’Action’ 조건으로 인덱스 레인지 스캔을 하고 테이블의 레코드를 읽은 후 first_name LIKE ‘%sal’ 조건에 부합되는지 여부를 비교한다.
3건의 레코드를 가져와서 1건만 first_name LIKE ‘%sal’ 조건에 일치했지만, 만약 last_name=’Action’ 조건에 일치하는 레코드가 10만 건이 되고 그중에 1건만 일치하게 된다면 99,999건을 불필요하게 읽게 된다.
MySQL 엔진은 테이블의 레코드에서 first_name 같이 조건을 비교하는 작업을 진행, InnoDB 에서 실제 인덱스를 비교하는 작업을 진행
인덱스를 가져와서 읽을때 한번에 읽지 않고 저렇게 2번 으로 나눠서 비교 읽게 된 이유는 SQL 5.6 버전 이전에는 SQL 엔진이 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name 조건은 InnoDB 로 보내지 않았다.
그렇기 때문에 스토리지 엔진에서는 값을 모르기 때문에 불필요하게 테이블 읽기를 실행할 수 밖에 없었다.

5.6 이후 부터는 MySQL 엔진에서 사용하지 못하는 인덱스 까지 같이 보내주는 걸로 수정이 되었기 때문에 InnoDB 에서는 인덱스를 비교 못하는 인덱스 까지 받기 때문에 비교 한 후에 테이블을 읽을 수 있게 되었다.

3.1.4 인덱스 확장(use_index_extensions)

인덱스에 프라이머리 키가 같이 있다.

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;

from_date 인덱스는 프라이머리 키도 함께 있기 때문에 결과적으로는 (from_date, dept_no, emp_no) 이렇게 존재하는 것이다.
예전 SQL 버전에서는 세컨더리 인덱스 자체만 활용하고 프라이머리 키는 함께 활용 할 수 없었지만, 업그레이드 되면서 옵티마이저는 세컨더리 인덱스에서 (from_date, dept_no, emp_no) 가 같이 존재하는 것을 인지하고 실행계획을 세운다.

mysql> EXPLAIN SELECT COUNT(*) FROM dept_emp WHERE from_date='1987-07-25' 
AND dept_no='d001'

+----+--------------+---------+----------+------------+-----------+------------+
| id | select_type  | table   | type     | key        |  key_len  | ref        |
+----+--------------+---------+----------+------------+-----------+------------+
| 1  |    SIMPLE    |dept_mep | ref      | ix_fromdate| 19        | const,const|
+----+--------------+---------+----------+------------+-----------+------------+

실행 계획의 key_len 은 from_date 만으로 봤을때 3바이트지만 dept_emp 인 16바이트 까지 사용 했다는 것을 알 수 있다.

mysql> EXPLAIN SELECT COUNT(*) FROM dept_emp WHERE from_date='1987-07-25';

+----+--------------+---------+----------+------------+-----------+------------+
| id | select_type  | table   | type     | key        |  key_len  | ref        |
+----+--------------+---------+----------+------------+-----------+------------+
| 1  |    SIMPLE    |dept_mep | ref      | ix_fromdate| 3         | const      |
+----+--------------+---------+----------+------------+-----------+------------+

dept_no를 같이 검색 하지 않을 시 key_len 길이가 3바이트 나온것을 알 수 있다.
InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함돼 있으므로 정렬 작업도 인덱스를 활용해서 처리하는 장점이 있다.

mysql> EXPLAIN SELECT COUNT(*) FROM dept_emp WHERE from_date='1987-07-25'
ORDER BY dept_no;

+----+--------------+---------+----------+------------+-----------+------------+
| id | select_type  | table   | type     | key        |  key_len  | EXTRA      |
+----+--------------+---------+----------+------------+-----------+------------+
| 1  |    SIMPLE    |dept_mep | ref      | ix_fromdate| 3         | NULL       |
+----+--------------+---------+----------+------------+-----------+------------+

3.1.5 인덱스 머지(index_merge)

보통은 한 쿼리에 인덱스 하나만을 사용해서 실행계획을 새우는데, 인덱스 머지는 인덱스를 2개 이상 사용할때 사용한다.
쿼리에서 한 테이블에 대한 WHERE 조건이 여러개 있다 하더라도 한 인덱스로 가져온 데이터들을 WHERE 에 있는 값들과 비교하면서 사용하는것이 일반적이다.
하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을때 인덱스 머지를 실행한다.
인덱스 머지는 인덱스로 가져온 값들을 어떻게 병합할지 에 따라 3가지 방식으로 분류된다.
인덱스 머지 - 교집합(index_merge_intersection)
```
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Georgi' 
AND emp_no BETWEEN 10000 AND 20000;

+-------------+--------------------+---------+---------------------------------------------------+
| type        | key                | key_len | Extra                                             |
+-------------+--------------------+-------------------------------------------------------------+
| index_merge |ix_firstname,PRIMARY| 62, 4   |Using intersect(ix_firstname,PRIMARY); Using where |
+-------------+--------------------+---------+---------------------------------------------------+
```
- 각 인덱스에서 조건을 만족하는 ROW ID(레코드) 검색
  - ix_first_name 인덱스에서 first_name = 'Georgi' 조건을 만족하는 ROW ID 목록 조회
  - PRIMARY (emp_no) 인덱스에서 emp_no BETWEEN 10000 AND 20000 조건을 만족하는 ROW ID 목록 조회
```
ix_first_name 인덱스:  (ROW ID) {10005, 10010, 10015, 15000, 18000, 20005}
PRIMARY (emp_no) 인덱스: (ROW ID) {10010, 10015, 12000, 15000, 18000}
```
- 두 인덱스의 ROW ID 리스트 교집합 연산
  - ix_first_name 에서 찾은 ROW ID : {10005, 10010, 10015, 15000, 18000, 20005}
  - PRIMARY 에서 찾은 ROW ID : {10010, 10015, 12000, 15000, 18000}
  - 두 리스트의 교집합: {10010, 10015, 15000, 18000}
- 인덱스 머지 실행 계획이 아닌 방식으로 처리 했을 경우
```
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+-----------+
| COUNT(*)  |
+-----------+
|       253 |
+-----------+

mysql> SELECT COUNT(*) FROM employees WHERE emp_no BETWEEN 10000 AND 20000;
+-----------+
| COUNT(*)  |
+-----------+
|     10000 |
+-----------+
```
- “first_name=’Georgi’” 조건만 인덱스를 사용했다면 일치하는 레코드 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드들만 반환하는 형태로 처리돼야 한다.
- “emp_no BETWEEN 10000 AND 20000” 조건만 인덱스를 사용했다면 프라이머리 키를 이용해 10,000 건을 읽어와서 “first_name=’Georgi’” 조건에 일치하는 레코드만 반환하는 형태로 처리돼야 한다.
```
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Georgi' 
AND emp_no BETWEEN 10000 AND 20000;

+-----------+
| COUNT(*)  |
+-----------+
|        14 |
+-----------+
```
- ix_firstname 인덱스 만 사용했으면 253번의 데이터 페이지 읽기를 하지만 실제로는 14번만 의미있는 작업이였고, PRIMARY 키만 사용했다면 10,000건을 읽어서 9,986건은 버리는 작업을 했다.
  
  인덱스 머지 - 합집합(index_merge_union)
- Using union 은 WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화다.
```
mysql> SELECT * FROM employees WHERE first_nmae='Matt' OR hire_date='1987-03-31';

+-------------+------------------------+---------+---------------------------------------+
| type        | key                    | key_len | Extra                                 |
+-------------+------------------------+-------------------------------------------------+
| index_merge |ix_firstname,ix_hiredate| 58, 3   |Using union(ix_firstname,ix_hiredate); |
+-------------+------------------------+---------+---------------------------------------+
```
- ix_firstname 인덱스 에서 가져온 레코드랑 ix_hiredate 인덱스에서 가져온 레코드랑 중복된 값이 있으면 어떻게 될까?
- 두 인덱스는 세컨더리 인덱스 이기 때문에 프라이머리 키를 가지고 있다. 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 제거한다. 이 제거를 우선순위 큐(Priority Queue) 에서 진행한다.
  💡
  AND 조건일 경우 2 개의 조건중 한 조건이 인덱스를 사용하지 못하면 인덱스 레인지 스캔
  
  OR 조건일 경우 2개의 조건중 한 조건이 인덱스를 사용하지 못하면 풀 테이블 스캔
  
  인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)
- 인덱스 머지시 정렬되어 있는 PRIMARY KEY 를 이용하기 때문에 따로 정렬하지 않고 머지를 진행했었다. 하지만 정렬이 필요한 경우도 있다.
```
mysql> EXPLAIN SELECT * FROM employees WHERE first_name='Matt' 
OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'

mysql> SELECT * FROM employees WHERE first_name='Matt';
mysql> SELECT * FROM employees WHERE hire_date BETWEEN '1987-03-01' AND '1987-03-31'
```
- 첫번째 쿼리는 결과가 emp_no로 정렬되지만 두번째 쿼리는 emp_no로 정렬되지 않는다.
- 두 집합의 결과에 중복을 제거하기 위해 각 집합을 emp_no 칼럼으로 정렬한 다음 중복 제거를 수행한다.
- 인덱스 머지 최적화에서 중복 제거를 위해 강제로 정렬을 수행해야 하는 경우에는 실행 계획의 Extra 칼럼에 “Using sort_union”문구가 표시된다.

3.1.6 세미 조인(semijoin)

다른 테이블과 실제 조인을 수행하지 않고, 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인(Semi-Join)이라고 한다.
오른쪽 테이블의 데이터를 직접 반환하지 않는다.
MySQL 서버에 세미 조인 최적화 도입 되기전 쿼리 처리

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');

+------+------------------+---------+-------+-------------+----------------------+
| id   | select_type      | table   | type  | key         | rows                 |
+------+------------------+---------+-------+-------------+----------------------+
|    1 | PRIMARY          |     e   |  ALL  | NULL        | 300363               |
|    2 | SUBQUERY         |    de   |  ref  | ix_fromdate | 57                   |
+------+------------------+---------+-------+-------------+----------------------+

employees 테이블을 풀 스캔하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교했다.
57건만 읽으면 될 쿼리를 30만건 넘게 읽어서 처리되는걸 알 수 있다.
세미 조인 쿼리의 성능을 개선하기 위한 최적화 전략이 5가지 있다.
1. Table Pull-out
2. Douplicate Weed-out
3. First Match
4. Loose Scan
5. Materialization
  
  테이블 풀-아웃(Table Pull-out)
- 세미 조인(EXISTS, IN)의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어 낸 후에 쿼리를 조인 쿼리로 재작성하는 방식
```
mysql> EXPLAIN SELECT * FROM employees e WHERE e.emp_no IN 
(SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');

+------+---------------+-------+---------+--------+--------+-----------------+
|   id | select_type   | table | type    | key    | rows   | Extra           |
+------+---------------+-------+---------+--------+--------+-----------------+
|   1  | SIMPLE        | de    | ref     | PRIMARY| 46012  | Using index     |
|   1  | SIMPLE        | e     | eq_ref  | PRIMARY|     1  | NULL            |
+------+---------------+-------+---------+--------+--------+-----------------+
```
- Table pull out 은 실행계획에 따로 Using Table pullout 이 작성되지 않는다.
- 그래서 Table pullout 최적화가 됐는지 확인해보려면 id 칼럼이 같은지를 비교하면 된다.
- 더 확실하게 알기 위해서는 EXPLAIN 로 실행계획을 실행한 직후 SHOW WARNINGS 명령어로 옵티마이저가 재작성한 쿼리를 확인하면 된다.
- Table pullout 은 모든 형태의 서브쿼리에서 사용되는게 아니라 특정한 사항에서만 작용한다
  - Table pullout 최적화는 세미 조인 서브쿼리에서만 사용 가능하다.
  - Table pullout 최적화는 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용가능하다.
  - Table pullout이 적용된다고 하더라도 기존 쿼리에서 가능했던 최적화 방법이 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화를 최대한 적용한다.
```
    (Table pullout + 기존에 사용했던 최적화 방법) 이렇게 사용할 수 있게 된것
    
- Table pullout 최적화는 서브쿼리의 테이블을 아우터 쿼리로 가져와서 조인으로 풀어쓰는 최적화를 수행하는데, 만약 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어진다.
- MySQL 에서는 “최대한 서브쿼리를 조인으로 풀어서 사용해라”라는 튜닝 가이드가 많은데, Table pullout 최적화는 사실 이 가이드를 그대로 실행한 것이다. 이제부터는 서브쿼리를 조인으로 풀어서 사용할 필요가 없다.
```
  퍼스트 매치(firstmatch)
- 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');

+------+-------+---------+--------------+--------+-----------------------------------------+
|   id | table | type    | key          | rows   | Extra                                   |
+------+-------+---------+--------------+--------+-----------------------------------------+
|   1  | e     | ref     | ix_firstname |    233 | NULL                                    |
|   1  | t     | ref     | PRIMARY      |      1 | Using where; Using index; FirstMatch(e) |            |
+------+-------+---------+--------------+--------+-----------------------------------------+
```
- 실행 계획의 id 칼럼의 값이 모두 1로 표시된 것은 titles 테이블이 서브쿼리 패턴으로 실행되지 않고, 조인으로 처리됐다는것을 알 수 있다.
- “FirstMatch(e)” 문구는 employees 테이블의 레코드에 대해서 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않는다는 것을 의미한다. 의미적으로는 EXISTS(subquery)와 동일하지만 서브쿼리가 아니라 조인으로 처리된다.
- ix_firstname 인덱스를 통해서 ‘Matt’ 를 인덱스 레인지 스캔을 통해서 읽고 titles 테이블 과 Join 을 한다. 이때 일치하는 레코드가 있으면 다음을 읽지 않고 값을 반환한다.
- First Match 최적화는 SQL 5.5 에서 사용하던 최적화 방법인 IN-to-EXISTS 변환과 거의 비슷한 처리방식이다. 하지만 IN-to-EXISTS 보다 더 장점이 잇는데 이는 아래와 같다
  - 가끔은 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 한다. 기존 IN-to-EXISTS 최적화에서는 이러한 동등조건 전파(Equality propagation)가 서브쿼리 내에서만 가능했지만 FirstMatch에서는 조인 형태로 처리되기 때문에 서브쿼리뿐만 아니라 아우터 쿼리의 테이블까지 전파될 수 있다. 최종적으로 FirstMatch 최적화가 실행되면 더 많은 조건이 주어지는 것이므로 더 나은 실행 계획을 수립할 수 있다.
  - IN-to-EXISTS 변환 최적화 전략에서는 아무런 조건 없이 변환이 가능한 경우에는 무조건 그 최적화를 수행했다. 하지만 FirstMatch 최적화에서는 서브쿼리의 모든 테이블에 대해 FirstMatch 최적화를 수행할지 아니면 일부테이블에 대해서만 수행할지 취사선택할 수 있다는것이 장점이다.
- FirstMatch 최적화의 몇가지 제한 사항과 특성
  - FirstMatch 가 진행되면 결과 값을 바로 찾는 상황이기 때문에 서브쿼리가 참조하고 있는 모든 아우터 테이블이 모두 조회 된 후 마지막에 진행을 한다.
  - FirstMatch 최적화가 실행되면 실행 계획의 Extra 칼럼에는 “FirstMatch(table-N)” 문구가 표시된다.
  - Firstmatch 최적화는 상관 서브쿼리(Correlated suquery) 에서도 사용될 수 있다
  - FirstMatch 최적화는 GROUP BY 나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없다.
  
  루스 스캔(loosescan)
- GROUP BY 최적화에서 사용하는 “Using index for group-by” 의 루스 인덱스 스캔과 읽기 방식이 비슷하다.
```
mysql> EXPLAIN SELECT * FROM departments d WHERE d.dept_no IN (
SELECT de.dept_no FROM dept_emp de);

+--------+---------+--------------+------------+--------+-----------------------+
|   id   | table   | type         | key        | rows   | Extra                 |
+--------+---------+--------------+------------+--------+-----------------------+
|   1    | de      | index        | PRIMARY    | 331143 | Using idnex; LooseScan|
|   1    | d       | eq_ref       | PRIMARY    |      1 | NULL                  |
+--------+---------+--------------+------------+--------+-----------------------+
 
```
- departments 테이블의 레코드 건수는 9건 밖에 되지 않지만 dept_emp 테이블의 레코드 건수는 무려 33만건 가까이 저장돼 있다. (IN 안에 있는 테이블 > 일반 테이블)
- dept_emp TABLE : PRIMARY KEY = (dept_no + emp_no)
- dept_emp table 의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no 만 읽어주면 아주 효율적으로 서브쿼리 부분을 실행할 수 있다.
- 서브쿼리에 사용된 dept_no 테이블이 드라이빙 테이블로 실행되며, dept_emp 테이블의 프라이머리 키를 dept_no 부분에서 유니크하게 한 건씩만 읽고 있다는 것을 보여준다.
- LooseScan 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 가드음으로 아우터 테이블을드리븐으로 사용해서 조인을 수행한다.
- 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는데 그 조건은 아래와 같다
```
SELECT ... FROM .. WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)
SELECT ... FROM .. WHERE expr IN (SELECT keypart2 FROM tab 
WHERE keypart1='상수'...)
```
  구체화(Materialization)
- 세미조인에 사용된 서브쿼리를 통째로 내부 임시테이블을 생성해서 쿼리를 최적화 한다는 의미
- 1995년 1월 1일 조직이 변경된 사원들의 목록을 조회하는 쿼리
```
mysql> EXPALIN SELECT * FROM employees e WHERE e.emp_no IN 
(SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01');

+--------+--------------+-------------+--------+-------------+--------------------+
|   id   | select_type  | table       | type   | key         | ref                |
+--------+--------------+-------------+--------+-------------+--------------------+
|   1    | SIMPLE       | <subquery2> | ALL    | NULL        | NULL               |
|   1    | SIMPLE       | e           | eq_ref | PRIMARY     | <subquery2>.emp_no |
|   2    | MATERIALIZED | de          | ref    | ix_fromdate | const              |
+--------+--------------+-------------+--------+-------------+--------------------+
 
```
- 실행 계획 마지막 라인의 select_type 칼럼에 간단하게 “MATERIALIZED” 라고 표시 됐다.
- 이 쿼리에서 사용하는 테이블은 2개인데 실행 계획은 3개 라인이 출력되어 있으므로 임시테이블이 생성됐다는것을 추론할 수 있다.
- dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어 그 결과로 임시 테이블 ()이 만들어졌다.
- 그리고 최종적으로 서브쿼리가 구체화된 임시 테이블()과 employees 테이블을 조인해서 결과를 반환한다.
- Materialization 최적화는 다른 서브쿼리 최적화와는 달리, 다음 쿼리와 같이 서브쿼리 내에 GROUP BY 절이 있어도 이 최적화 전략을 사용할 수 있다.
```
mysql > EXPLAIN SELECT * FROM employees e WHERE e.emp_no IN
(SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01' 
GROUP BY de.dept_no);
```
- Materialization 최적화가 사용될 수 있는 형태의 쿼리에서도 몇가지 제한 사항이 있다
  - IN(subquery) 에서 서브쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 한다.
  - 서브쿼리는 GROUP BY 나 집합 함수들이 사용돼도 구체화를 사용할 수 있다.
  - 구체화가 사용된 경우에는 내부 임시테이블이 사용된다.
  
  중복 제거(Duplicated Weed-out)
- 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제가하는 방법으로 처리되는 최적화 알고리즘이다.
```
mysql > EXPLAIN SELECT * FROM employees e WHERE e.emp_no
 IN (SELECT s.emp_no FROM salaries s WHERE s.salary>150000);
 
 +------+-------------+-------+-------+-----------+-------------------------------------------+
 | id   | select_type | table | type  | key       | Extra                                     |
 +------+-------------+-------+-------+-----------+-------------------------------------------+
 | 1    | SIMPLE      | s     | range | ix_salary | Using where; Using index; Start temporary |
 | 1    | SIMPLE      | e     | eq_ref| PRIMARY   | End temporary                             |
 +------+-------------+-------+-------+-----------+-------------------------------------------+  
```
- salaries table : PRIMARY KEY = (emp_no + from_date) 이므로 salaries 테이블에서 조회하면 결과가 중복된 emp_no 가 발생할 수 있다. 이 쿼리를 다음과 같이 재작성해서 GROUP BY 절을 넣어주면 위의 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있다.
```
mysql> SELECT e.* FROM employees e, salaries s WHERE e.emp_no=s.emp_no
AND s.salary>150000 GROUP BY e.emp_no
```
- Duplicate Weedout 최적화 알고리즘은 원본 쿼리를 위와 같이 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다.
1. salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000 보다 큰 사원을 검색해 employees 테이블 조인을 실행
2. 조인된 결과를 임시 테이블에 저장
3. 임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
4. 중복을 제거하고 남은 레코드를 최종적으로 반환
- 실행 계획에서는 “Duplicate Weedout”이라는 문구가 별도로 표시되진 않는다.
- Extra 칼럼에 “Start temporary” 와 “End temporary” 문구가 별도로 표시된다.
- 그림 9.15 처리과정에서 1번 2번 과정은 반복적으로 실행되는 과정이다.
- Duplicate Weedout 최적화는 다음과 같은 장점과 제약 사항이 있다.
  - 서브쿼리가 상관 서브쿼리라고 하더라도 사용할 수 있는 최적화다.
  - 서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용할 수 없다.
  - Duplicate Weedout 은 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화 할 수 있는 방법이 많다.

3.1.7 컨디션 팬아웃(condition_fanout_filter)

조인을 실행할때 레코드의 수가 적은 테이블을 드라이빙 테이블로 세우는게 유리하다 그래서 옵티마이저는 여러 테이블이 조인될 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
Nested Loop Join과 드라이빙 테이블의 선택 Case 1 : B가 드라이빙 테이블 (B → A)
1. B 테이블에서 10건을 가져온다.
2. 각 B의 레코드에 대해 A 테이블을 검색한다 (A를 10번만 읽음)
  
  즉, A 테이블이 인덱스가 잘 설계되어 있다면, 각 B의 레코드에 대해 A에 필요한 데이터만 빠르게 찾을 수 있다.
  👉 A 테이블 전체(1만 건)를 탐색하는 것이 아니라, B의 10건에 대해 필요한 만큼만 조회하면 된다.
  
  Case 2 : A가 드라이빙 테이블 (A → B)
3. A 테이블에서 10,000 건을 가져온다.
4. 각 A의 레코드에 대해 B 테이블을 검색한다 (B를 10,000번 읽음).
  
  👉 B 테이블이 인덱스가 없거나 비효율적인 구조라면, B 테이블을 최대 10,000번 반복해서 읽어야 한다.
  
  👉 특히 B 테이블의 데이터가 적다고 해도,
  
  반복 접근 횟수가 많아지므로 성능이 급격히 떨어질 수 있다.
다음은 employees 테이블에서 이름이 ‘Matt’ 이면서 입사 일자가 ‘1985-11-21’ 부터 ‘1986-11-21’ 일 사이인 사원을 검색해 해당 사원의 급여를 조회하는 쿼리다.

mysql> SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no 
WHERE e.first_name='Matt' AND e.hire_date BETWEEN '1985-11-21' AND '1986-11-21';

condition_fanout_filter 옵티마이저 옵션을 비활성화 한 경우

+------+---------+------+--------------+-------+------------+----------------+
|   id | table   | type | key          | rows  | filtered   | Extra          |
+------+---------+------+--------------+-------+------------+----------------+
|   1  | e       | ref  | ix_firstname | 233   | 100.00     | Using where    |
|   1  | s       | ref  | PRIMARY      | 10    | 100.00     | NULL           |
+------+---------+------+--------------+-------+------------+----------------+

다음과 같은 절차를 거쳐서 처리된다.

employees 테이블에서 ix_firstname 인덱스를 이용해 first_name=’Matt’ 조건에 일치하는 233건의 레코드를 검색한다.
검색된 233건의 레코드 중에서 hire_date가 ‘1985-11-21’ 부터 ‘1986-11-21’ 일 사이인 레코드만 걸러내는데, 이 실행계획에서는 filtered 칼럼의 값이 100인 것은 옵티마이저가 233건 모두 hire_date 칼럼의 조건을 만족할 것으로 예측했다는 것을 의미한다.
employees 테이블을 읽은 결과 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽는다. 이때 MySQL 옵티마이저는 employees 테이블의 레코드 한 건당 salaries 테이블의 레코드 10건이 일치할 것으로 예상했다.

condition_fanout_filter 옵티마이저 옵션을 활성화 한 경우

+------+---------+------+--------------+-------+------------+----------------+
|   id | table   | type | key          | rows  | filtered   | Extra          |
+------+---------+------+--------------+-------+------------+----------------+
|   1  | e       | ref  | ix_firstname | 233   | 23.20      | Using where    |
|   1  | s       | ref  | PRIMARY      | 10    | 100.00     | NULL           |
+------+---------+------+--------------+-------+------------+----------------+

rows 칼럼의 값은 233으로 동일하지만 filter 칼럼의 값이 100% → 23.2% 로 병경됐다.
condition_fanout_filter 최적화가 활성화 되면서 MySQL 옵티마이저는 인덱스를 사용할 수 있는 first_name 칼럼 조건 이외의 나머지 조건(hire_date 칼럼의 조건) 에 대해서도 얼마나 조건을 충족할지 고려했다는 뜻이다.
condition_fanout_filter 최적화가 filtered 칼럼의 값을 예측하는 방법
1. WHERE 조건절에 사용된 칼럼에 대해 인덱스가 있는 경우
2. WHERE 조건절에 사용된 칼럼에 대해 히스토그램이 존재하는 경우
employees 테이블의 hire_date 칼럼의 인덱스가 없었다면 MySQL 옵티마이저는 first_name 칼럼의 인덱스를 이용해 hire_date 칼럼의 분포도를 살펴보고 filtered 칼럼의 값을 예측한다.

💡

MySQL 옵티마이저가 실행 계획을 수립할 때 테이블이나 인덱스의 통계 정보만 사용하는 것이 아니라 다음 순서대로 사용 가능한 방법을 선택한다.

레인지 옵티마이저(Range Optimizer)를 이용한 예측
히스토그램을 이용한 예측
인덱스 통계를 이용한 예측
추측에 기반한 예측(Guesstimates)

레인지 옵티마이저는 실제 인덱스의 데이터를 소량으로 빠르게 읽어보고 예측한다.

레인지 옵티마이저에 의한 예측은 인덱스를 이용하는 쿼리일때만 사용할 수 있다.

레인지 옵티마이저를 이용한 예측이 히스토그램이나 인덱스 통계 정보보다 우선순위가 높기 때문에 실행 계획에 표시되는 레코드 건수가 테이블이나 인덱스의 통계, 히스토그램 정보와 다른 값이 표시될 수 있다.

3.1.8 파생 테이블 머지(derived_merge)

예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다.

mysql> EXPLAIN SELECT * FROM ( SELECT * FROM employees WHERE first_name='Matt') 
derived_table WHERE derived_table.hire_date='1986-04-03';

+----+-------------+------------+------+--------------------+
| id | select_type | table      | type | key                |
+----+-------------+------------+------+--------------------+
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>        |
|  2 | DERIVED     | employees  | ref  | ix_firstname       |
+----+-------------+------------+------+--------------------+

쿼리의 실행 계획을 보면 employees 테이블에서 first_name 칼럼의 값인 ‘Matt’ 인 레코드만 읽어서 임시 테이블에 Insert 하였고(Derived Table), 그 임시 테이블에서 hire_date 칼럼의 값이 ‘1986-04-03’ 인 레코드만 걸러내서 반환했다.
FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table) 이라고 부른다.
MySQL 5.7 버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐는데, derived_merge 최적화 옵션은 이러한 임시 테이블 최적화를 활성화할지 여부를 결정한다.

+----+-------------+------------+-------------+--------------------------+
| id | select_type | table      | type        | key                      |
+----+-------------+------------+-------------+--------------------------+
|  1 | PRIMARY     | employees  | index_merge | ix_hiredate,ix_firstname |
+----+-------------+------------+-------------+--------------------------+

select_type 칼럼이 DERIVED 였던 라인이 없어지고, 서브쿼리 없이 employees 테이블을 조회하던 형태의 단순 실행 계획으로 바뀌었다.
서브쿼리 부분이 어떻게 외부 쿼리로 병합됐는지 확인할 수 있다.

SELECT employees.employees.emp_no AS emp_no,
			employees.employees.birth_date AS birth_date,
			employees.employees.first_name AS first_name,
			employees.employees.last_name AS last_name,
			employees.employees.gender AS gender,
			employees.employees.hire_date AS hire_date
FROM employees.employees
WHERE ((employees.employees.hire_date = DATE'1986-04-03') 
AND (employees.employees.first_name = 'Matt'))

모든 서브쿼리가 외부쿼리와 머지되는 건 아니다. 아래와 같은 경우는 수작업으로 병합 쿼리를 작성해주어야 한다.
- SUM() 또는 MIN(), MAX() 같은 집계 함수와 윈도우 함수(Window Function)가 사용된 서브쿼리
- DISTINCT가 사용된 서브쿼리
- GROUP BY나 HAVING이 사용된 서브쿼리
- LIMIT이 사용된 서브쿼리
- UNION 또는 UNION ALL 을 포함하는 서브쿼리
- SELECT 절에 사용된 서브쿼리
- 값이 변경되는 사용자 변수가 사용된 서브쿼리

3.1.9 인비저블 인덱스(use_invisible_indexes)

인덱스를 삭제하지 않고 사용하지 못하게 제어하는 기능

-- // 옵티마이저가 ix_hiredate 인덱스를 사용하지 못하게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;

-- // 옵티마이저가 ix_hiredate 인덱스를 사용할 수 있게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate VISIBLE;

3.1.10 스킵 스캔(skip_scan)

인덱스가 (A, B, C) 형태로 되어있을때 B, C 만 인덱스를 사용할 수 없는데 이것을 스킵스캔이 보완해준다.

mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

mysql> SELECT * FROM employees WHERE birth_date >= '1965-02-01';

위의 경우 gender 컬럼을 사용하지 않기 때문에 원래는 인덱스를 활용해서 효율을 낼 수 없지만 스킵 스캔을 통해서 gender 가 있는 것처럼 옵티마이저가 인지하고 진행하도록 한다.
gender 의 경우의 수가 여러가지 라면 성능이 떨어지지만 G , M 두가지 경우일 경우나 적을 경우에는 효율을 발휘할 수 있다.

3.1.11 해시 조인(hash_join)

해시조인 : 쿼리가 시작하고 맨 처음 레코드를 찾는 것에는 시간이 네스티드 루프 조인보다 오래걸리지만, 마지막 레코드를 찾는데는 오래 걸리지 않는다. 최고 스루풋(Best Throughput) 전략에 적합하다. 분석과 같은 서비스에 적합
네스티드 루프 조인 : 해시 조인 보다 맨 처음 레코드를 찾는 것에는 시간이 빠르지만, 마지막 레코드를 찾는데는 오래 걸린다. 최고 응답 속도(Best Response-time) 전략에 적합하다. 웹 서비스에 적합
기존에 사용하던 블록 네스티드 루프 조인이 사용될 경우인 상황이지만, 조인 버퍼의 용량을 무조건 많이 늘릴 수 없는 경우에는 대부분 해시 조인을 사용하도록 변화하는 추세

mysql> EXPLAIN SELECT * FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
INNER JOIN dept_emp de IGNORE(ix_empno_fromdate, ix_fromdate) ON de.emp_no=e.emp_no
AND de.from_date=e.hire_date;

+----+-------------+--------+---------+--------------------------------------------+
| id | select_type | table  | type    | Extra                                      |
+----+-------------+--------+---------+--------------------------------------------+
|  1 | SIMPLE      | de     | ALL     | NULL                                       |
|  1 | SIMPLE      | e      | ALL     | Using where; Using join buffer (hash join) |
+----+-------------+--------+---------+--------------------------------------------+

빌드 단계 (Build-phase) : 조인 대상 테이블 중에 레코드 건수가 적은 테이블을 해시 테이블로 만든다.
프로브 단계(Probe-phase) : 나머지 테이블의 레코드를 읽어서해시 테이블과 일치 레코드를 찾는다.
dept_emp : 빌드 테이블 (기준이 되는 테이블)
employees : 프로브 테이블 (outer table)
위의 경우는 조인 버퍼에 저장시키는 해시 테이블의 크기가 dept_emp 해시 테이블을 담을 수 있는 경우
해시 테이블의 크기가 큰 경우 빌드 테이블과, 프로브 테이블을 청크 단위로 잘라서 해시 조인을 처리한다. 아래의 경우 1차 조인

2 차 조인

3.1.12 인덱스 정렬 선호(prefer_ordering_index)

mysql> EXPLAIN SELECT * FROM employees WHERE hire_date BETWEEN '1985-01-01'
AND '1985-02-01'; ORDER BY emp_no;

+----+-----------+-------+---------+--------+-------------+
| id | table     | type  | key     | rows   | Extra       |
+----+-----------+-------+---------+--------+-------------+
|  1 | employees | index | PRIMARY | 300252 | Using where |
+----+-----------+-------+---------+--------+-------------+

위의 쿼리 경우 옵티마이저는 두가지 방식중에 한가지를 택해서 쿼리를 실행한다.
1. ix_hiredate 인덱스를 통해서 hire_date BETWEEN ‘1985-01-01’ AND ‘1985-01-31’ 을 찾은 다음에 ORDER BY emp_no 를 하는 방법
2. PRIMARY KEY 인 emp_no 를 통해서 클러스터링 인덱스에서 hire_date BETWEEN ‘1985-01-01’ AND ‘1985-01-31’ 를 하나하나 비교해서 찾는 방법
레코드의 건수가 많음에도 PRIMARY TREE 를 활용해서 건수를 하나 하나 인덱스 풀 스캔을 해서 비교하는건 비 효율적이지만 옵티마이저는 가끔 그런 선택을 한다.
그 이유는 ORDER BY나 GROUP BY 에 있는 인덱스의 가중치를 높게 치기 때문인데 이를 SQL 8.0.21 버전 부터는 가중치를 비활성화 하는 방법이 생겼다.

-- // 현재 커넥션에만 prefer_ordering_index 옵션을 비활성화
mysql> SET SESSION optimizer_switch='prefer_ordering_index=OFF';

-- // 현재 쿼리에 대해서만 prefer_ordering_index 옵션을 비활성화
mysql> SELECT /*+ SET_VAR (optimizer_switch='prefer_ordering_index=off') */
		...
		FROM
		...

3.2 조인 최적화 알고리즘

mysql> SELECT * FROM t1, t2, t3, t4 WHERE ...

3.2.1 Exhaustive 검색 알고리즘

초기 조인 최적화 기법으로 모든 테이블을 조합해 가장 최적의 비용이 드는 한 조합의 경우를 찾는 알고리즘이다.
조인의 모든 경우의 수를 찾는 거기 때문에 테이블이 많으면 많을 수 록 시간이 오래 걸린다.

3.2.2 Greedy 검색 알고리즘

전체 N 개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
2번에서 선정된 실행 계획의 첫 번째 테이블을 부분 실행 계획의 첫 번째 테이블로 선정
전체 N-1 개의 테이블 중(3번에서 선택된 테이블 제외)에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 “부분 실행 계획”에 대입해 실행 비용을 계산
5번의 비용 계산 결과, 최적의 실행 계획에서 두 번쨰 테이블을 3번에서 생성된 “부분 실행 계획”의 두 번째 테이블 로 선정
남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복 실행하면서 “부분 실행 계획”에 테이블의 조인 순서를 기록
최종적으로 “부분 실행 계획”이 테이블의 조인 순서로 결정됨

4. 쿼리 힌트

아직 까지 RDBMS 는 서비스의 비지니스 이해도가 떨어지기 때문에 옵티마이저가 쿼리를 실행할때 도움을 줘야 한다.

4.1 인덱스 힌트

SELECT 명령과 UPDATE 명령에서 사용할 수 있따.
인덱스 힌트보다 옵티마이저 힌트를 사용할 것을 추천한다.
4.1.1 STRAIGHT_JOIN
- SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블을 조인할때 순서를 고정시켜준다.
  
  4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX
- 조인이 여러게 발생하거나 할때 제대로 INDEX 처리가 안될 수 있기 때문에 강제 하게 하는것
  
  4.1.3 SQL_CALC_FOUND_ROWS
- COUNT(*) 로 읽어 오는 것이 더 효율적
- LIMIT 시에 사용하는데 LIMIT 로 가져오는것 제외 하고 전체 값을 읽어온 다음 그중에 LIMIT 값을 사용자에게 반환하는 방식

4.2 옵티마이저 힌트

4.2.1 옵티마이저 힌트 종류

4.2.2 MAX_EXECUTION_TIME

쿼리의 최대 실행 시간을 설정하는 힌트다. 이 시간 내에 실행을 못하면 실패한다.

4.2.3 SET_VAR

MySQL 서버의 시스템 변수들을 쿼리 안에 일시적으로 값을 변경해서 사용할 수 있게 해준다.

mysql> EXPLAIN SELECT /*+ SET_VAR (optimizer_switch='index_merge_intersection=off') */*
FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

4.2.4 SEMIJOIN & NO_SEMIJOIN

세미 조인의 최적화 하기 위한 여러가지 세부 전략들이 있는데 이것을 쿼리 내에서 강제해서 사용할 수 있게 해준다.

mysql EXPLAIN SELECT * FROM departments d WHERE d.dept_no IN
(SELECT /*+ SEMIJOIN(MATERIALIZATION) */ de.dept_no FROM dept_emp de);

4.2.5 SUBQUERY

서브쿼리 최적화는 세미 조인 최적화가 작동하지 않을때 사용하는 최적화 방법이다.

최적화 방법	힌트
IN-to-EXISTS	SUBQUERY(INTOEXISTS)
Materialization	SUBQUERY(MATERIALIZATION)

4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

MySQL 8.0.20 버전 이후부터는 BNL 힌트는 해시 조인으로 작동을 한다.

4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

STRAIGHT_JOIN 힌트는 쿼리의 FROM 절에 사용된 테이블의 순서를 강제하게 하는데 그렇게 되면 일부는 강제하고 일부는 옵티마이저 힌트를 사용하는 것이 불가능했다. 이를 보완하기 위해서 4개의 힌트가 추가되었다.
JOIN_FIXED_ORDER : STRAIGHT_JOIN 힌트와 동일하게 FROM 절의 테이블 순서대로 조인을 실행하게 하는 힌트
JOIN_ORDER : FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
JOIN_PREFIX : 조인에서 드라이빙 테이블만 강제하는 힌트
JOIN_SUFFIX : 조인에서 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제하는 힌트

4.2.8 MERGE & NO_MERGE

FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입했는데 이를 강제하기 위한 옵티마이저 힌트

4.2.9 INDEX_MERGE & NO_INDEX_MERGE

MySQL 서버는 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 하지만 그럴 수 없는 경우도 있다. 한 테이블에 여러 인덱스를 사용하는것을 인덱스 머지라고 하는데 이를 사용하는 옵티마이저 힌트다.

4.2.10 NO_ICP

인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화를 사용하지 않기 위한 옵티마이저 힌트다.

4.2.11 SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵스캔을 사용하는 옵티마이저 힌트다.

4.2.12 INDEX & NO_INDEX

INDEX사용하는 옵티마이저 힌트다.

인덱스 힌트	옵티마이저 힌트
USE INDEX	INDEX
USE INDEX FOR GROUP BY	GROUP_INDEX
USE INDEX FOR ORDER BY	ORDER_INDEX
IGNORE INDEX	NO_INDEX
IGNORE INDEX FOR GROUP BY	NO_GROUP_INDEX
IGNORE INDEX FOR ORDER BY	NO_ORDER_INDEX

진성대

주니어 개발자

이전 포스트

인덱스

다음 포스트

옵티마이저와 힌트

SQL

1. 개요

1.1 쿼리 실행 절차

1.2 옵티마이저 종류

2. 기본 데이터 처리

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

2.2 병렬 처리

2.3 ORDER BY 처리(Using filesort)

2.3.1 소트 버퍼

2.3.2 정렬 알고리즘

2.3.3 정렬 처리 방법

2.3.4 정렬 관련 상태 변수

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 임시 테이블 관련 상태 변수