[Real MySQL 9장] 옵티마이저와 힌트

정훈희·2023년 9월 11일

Real MySQL 옵티마이저 쿼리 힌트

Real MySQL

목록 보기

4/5

옵티마이저

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 결과를 만들어내는 방법은 다양한데, 다양한 방법 중 어떤 방법이 최적인지 실행 계획을 수립하는 작업을 담당하는 것을 “옵티마이저”라 한다.

EXPLAIN이라는 명령을 통해 쿼리의 실행 계획을 확인할 수 있다.

쿼리 실행 절차

SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리하여 SQL 파스 트리를 생성한다.
- 이 과정은 SQL 파서가 담당한다.
SQL 파스 트리를 확인하며 실행 계획 수립한다.
- 이 과정은 옵티마이저가 담당한다.
실행계획에 따라 스토리지 엔진에서 데이터를 가져온다.
- 이 과정은 MySQL 엔진과 스토리지 엔진이 함께 담당한다.

기본 데이터 처리

리드 어헤드
- 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해서 미리 디스크에서 읽어 버퍼 풀에 가져다 두는 것을 의미한다.
- 예를 들면 풀 테이블 스캔이 시작되면 처음 몇 개의 페이지는 포그라운드 스레드가 읽어오지만, 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 백그라운드 스레드는 한번에 4개 or 8개의 페이지를 읽어서 버퍼 풀에 저장해둔다. (읽어오는 페이지 수는 점점 증가한다.) 포그라운드 스레드는 버퍼 풀에 준비된 데이터를 가져다 사용하면 되므로 쿼리가 빠르게 처리된다.
- 풀 테이블 스캔, 풀 인덱스 스캔에서 사용된다.
풀 테이블 스캔 풀 테이블 스캔은 아래와 같은 경우에 쓰인다.
- 테이블의 레코드 건수가 너무 작아서 인덱스를 쓰지 않는 것이 더 빠른 경우
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 조건이 없는 경우
- 옵티마이저가 판단한 예상 레코드 건수가 너무 많아 인덱스 활용이 비효율적인 경우
풀 인덱스 스캔 풀 인덱스 스캔은 인덱스를 처음부터 끝까지 스캔하는 것을 의미한다.
병렬 처리 하나의 쿼리를 여러 스레드가 나누어 처리하는 것을 의미한다. 아무런 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

정렬 처리

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 “Filesort”라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

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

소트 버퍼
정렬을 수행하기 위해 할당받은 별도의 메모리 공간을 소트 버퍼라고 한다.

소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬할 레코드 크기에 따라 증가한다.

만약 소트 버퍼로 할당된 공간보다 정렬할 레코드 건수가 크다면 아래와 같은 방식으로 처리한다.

1. 정렬할 레코드를 여러 조각으로 나눠서 처리하며 결과를 디스크에 임시 저장한다.
2. 모든 레코드를 정렬했으면, 임시 저장한 결과들을 병합한다. (이 작업을 멀티 머지 라고 한다.)

이 작업들은 모두 디스크 I/O를 유발하며, 레코드 건수가 많을수록 반복 횟수가 많아진다.

![image](https://github.com/JeongHunHui/TIL/assets/108508730/159b6121-97a6-4697-bbd0-145276385357)

참고로, 위 그림에서 볼 수 있듯 소트 버퍼의 사이즈가 커진다고 성능이 무조건 빨라지는 것은 아니다.

대신, 디스크 I/O는 줄일 수 있으므로 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 좋을 수 있다.

하지만, 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 적절한 것이 좋다.

대량 데이터 정렬이 필요한 경우 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 좋다.

정렬 알고리즘

싱글 패스
레코드 정렬 시 레코드 전체를 소트 버퍼에 담는 정렬 방식을 말한다.
```
SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;
```
위 쿼리를 싱글 패스 방식으로 처리하는 절차는 아래와 같다.
1. 소트 버퍼 크기 만큼 SELECT절의 컬럼들(위 예시에선 emp_no, first_name, last_name)을 읽어와서 정렬 후 임시 저장한다.
2. 위 과정을 반복한 뒤 멀티 머지하여 쿼리의 결과를 반환한다.
  
  싱글 패스 방식은 불필요한 데이터 까지 읽어오므로 많은 소트 버퍼 공간이 필요하다.
투 패스
레코드 정렬 시 정렬 기준 컬럼과 PK만 소트 버퍼에 담는 정렬 방식을 말한다. 투 패스 방식은 테이블을 두 번 읽어야 한다. 최신 버전에서는 주로 싱글 패스 정렬 방식을 사용하지만, 다음의 경우 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용한다.
- 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때
  
  싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠르고, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.
  
  → SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고 모든 컬럼을 가져오도록 개발하게 되면 정렬 버퍼를 비효율적으로 사용하게된다. 또한, 임시 테이블이 필요한 쿼리에서도 영향을 미친다.
  
  → 불필요한 컬럼을 SELECT하지 않게 쿼리를 작성하자.

정렬 처리 방법

정렬 처리 방법	실행 계획의 Extra 내용	속도
인덱스를 이용한 정렬	별도 표기 없음	빠름
조인에서 드라이빙 테이블만 정렬	“Using filesort”	보통
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	“Using temporary; Using filesort”	느림

우선 인덱스를 사용할 수 있다면 인덱스를 순서대로 읽어서 결과를 반환한다. 인덱스를 사용할 수 없다면 조건에 일치하는 레코드를 검색해 버퍼에 저장하며 정렬을 처리한다. 이때 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 아래 두 방법 중 하나를 선택한다.

조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

인덱스를 이용한 정렬

ORDER BY 절에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
- 드라이빙 테이블: 조인 시 먼저 읽히는 테이블을 말하고, 쿼리에 따라 옵티마이저가 결정한다.
또한, WHERE절에 첫 번째로 읽는 테이블이 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
```
SELECT *
FROM users
WHERE name LIKE 'a%' and age >= 20
ORDER BY name, age;
// 복합 인덱스 name_age_idx(name, age)가 있는 상태
```
위 쿼리는 ORDER BY 절에 명시된 컬럼이 users 테이블에 속하고, 순서대로 생성된 인덱스가 있고, WHERE 절에서도 같은 인덱스를 사용할 수 있으므로 인덱스를 이용해 정렬할 수 있다.

여러 테이블이 조인되는 경우에는 Nested-loop 방식의 조인에서만 이 방식을 사용할 수 있다.

참고로, 위 경우 ORDER BY 절을 넣지 않아도 정렬되지만, ORDER BY 절을 넣는다고 불필요한 정렬을 한 번 더 하지 않고, 실행계획이 변경되면 버그로 이어질 수 있으므로 넣는 것이 좋다.
조인에서 드라이빙 테이블만 정렬

인덱스를 이용한 정렬이 불가능하다면 조인을 실행하기 전에 첫 번째로 읽히는 테이블(드라이빙 테이블)을 먼저 정렬한 다음 조인을 실행하는 것이 차선책이 될 것이다.

이 방법으로 정렬이 처리되려면 조인의 드라이빙 테이블의 컬럼만으로 ORDER BY 절을 작성해야 한다.
```
SELECT *
FROM users, posts
WHERE users.id = posts.user_id
	AND users.id BETWEEN 10000 AND 10010
ORDER BY users.name;
```
일단 위 쿼리에서는 아래 이유로 옵티마이저가 users 테이블을 드라이빙 테이블로 선택할 것이다.
- WHERE 절의 검색 조건 users.id BETWEEN 10000 AND 10010 은 users 테이블의 PK를 이용하면 작업량을 줄일 수 있다.
- 드리븐 테이블(posts)의 조인 컬럼인 users.id에 인덱스가 있다.
ORDER BY 절에 명시된 컬럼으로 생성된 인덱스가 없으므로 인덱스를 이용할 수 없다.

하지만, ORDER BY 절에 명시된 컬럼들이 드라이빙 테이블에 속해 있으므로 드라이빙 테이블만 먼저 정렬한 뒤 posts 테이블과 조인하는 식으로 처리할 수 있다.

즉, 아래와 같은 과정으로 실행된다.
1. 인덱스를 이용해 users.id BETWEEN 10000 AND 10010 를 만족하는 레코드를 검색
2. 검색 결과를 name 컬럼으로 정렬(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 posts 테이블과 조인을 수행해 결과를 가져옴

임시 테이블을 이용한 정렬

2번과 같은 패턴을 제외한 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.

이 방법은 다른 방법보다 정렬할 레코드 건수가 가장 많기 때문에 가장 느리다.

```sql
SELECT *
FROM users, posts
WHERE users.id = posts.user_id
	AND users.id BETWEEN 10000 AND 10010
ORDER BY posts.created_at;
```

위 쿼리는 2번 방법의 예시 쿼리에서 ORDER BY 절의 컬럼만 바꾼 쿼리이다.

위 쿼리는 정렬 기준 컬럼이 드리븐 테이블에 있으므로 조인을 한 뒤에 정렬해야한다.

위 3가지 정렬 방법을 아래 쿼리를 기준으로 비교해보자.

SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.c1=t2.c1
ORDER BY t1.c2
LIMIT 10;
// t1의 레코드는 100건, t2의 레코드는 1000건

→ 어떤 테이블이 먼저 드라이빙 되어 조인되는지, 어떤 정렬 방식으로 처리되는지에 따라서 큰 성능차이가 발생한다.

정렬 처리 방법의 성능 비교
ORDER BY 나 GROUP BY 작업은 LIMIT 가 있어도 정렬이나 그루핑 작업 후에 건수를 제한할 수 있다. → ORDER BY 나 GROUP BY 을 잘못 활용하면 쿼리가 느려진다. 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수밖에 없는지 알기 위해 쿼리가 처리되는 방법을 두 가지로 나눠서 알아보자.
- 스트리밍 방식
  조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다.
  
  위 방식으로 처리되는 쿼리에서 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 실행 시간을 상당히 줄여줄 수 있다.
- 버퍼링 방식
  ORDER BY 나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능 하게 한다.
```
왜냐하면 조건에 맞는 모든 레코드를 가져온 뒤 정렬하거나 그루핑해서 보내야 하기 때문이다.

MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.

그렇기 때문에 버퍼링 방식으로 처리되는 쿼리에선 `LIMIT` 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 큰 도움이 되지 않는다.

![image](https://github.com/JeongHunHui/TIL/assets/108508730/39402839-de66-4e00-b898-561ee651f4bd)
```
  인덱스를 사용하지 못하고 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT 조건을 걸면 모든 레코드를 정렬하는 것이 아니고, 정렬하다가 LIMIT 조건 만큼의 레코드가 정렬되면 바로 결과를 반환한다.
  
  하지만 MySQL은 정렬을 위해 퀵 소트와 힙 소트 알고리즘을 사용하는데, 즉 상위 10건의 데이터를 얻기 위해 대부분의 데이터를 살펴야할 수도 있다.
  
  → 인덱스를 사용하지 못하는 쿼리에 LIMIT 조건을 붙혀도 쿼리가 생각보단 많이 빨라지지 않는다.
정렬 관련 상태 변수 보는법
```
FLUSH STATUS;
SHOW STATUS LIKE 'Sort%';
```

GROUP BY 처리

GROUP BY 또한 ORDER BY와 같이 스트리밍 처리를 할 수 없다.

참고로 GROUP BY에 사용된 조건(HAVING 절)은 인덱스를 사용해서 처리될 수 없다.

GROUP BY도 인덱스를 사용하거나 못할 수 있다.

인덱스 스캔을 이용하는 GROUP BY
조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 이미 인덱스가 있다면 그 인덱스를 읽으며 그루핑 작업을 수행한다. 인덱스를 사용해서 처리하더라도 그룹 함수 등 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. 이러한 그루핑 방식을 사용하는 쿼리의 실행 계획의 Extra 컬럼에는 별도의 코멘트가 표시되지 않는다.
루스 인덱스 스캔을 이용하는 GROUP BY
이 방식을 사용할 때는 실행 계획의 Extra 컬럼에 “Using index for group-by” 코멘트가 표시된다.
```
// 인덱스는 (emp_no, from_date) 이렇게 생성된 상태
SELECT emp_no
FROM salaries
WHERE from_date='1985-03-01'
GROUP BY emp_no;
```
위 쿼리의 WHERE 절의 from_date는 복합 인덱스의 첫 컬럼이 아니므로 인덱스를 사용할 수 없다. 하지만 위 쿼리의 실행 계획을 보면 인덱스 레인지 스캔을 사용했고, GROUP BY 처리도 인덱스를 사용했다. 위 쿼리는 아래와 같은 과정으로 실행된다.
1. (emp_no, from_date) 인덱스를 스캔하며 emp_no의 첫 번째 유일한 값 “10001” 을 찾는다.
2. 인덱스에서 emp_no가 “10001”인 것 중 from_date 값이 “1985-03-01”인 레코드를 찾는다.
3. 다시 emp_no의 유일한 값을 찾은 뒤 2번을 반복하고 더 이상 유일한 값이 없으면 처리를 종료한다.
  
  즉 인덱스의 첫 번째 컬럼의 유니크한 값들 별로 WHERE 조건에 해당하는 레코드를 검색하는 것이다.
  
  이 방식에선 인덱스의 유니크한 값이 적을수록 성능이 향상된다.
임시 테이블을 사용하는 GROUP BY
GROUP BY의 기준 컬럼이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. MySQL 8.0에선 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

DISTINCT 처리

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

SELECT DISTINCT
GROUP BY와 동일한 방식으로 처리된다. 참고로 SELECT절의 DISTINCT는 SELECT절의 모든 컬럼들을 기준으로 유니크한 값들을 가져온다.
```
SELECT DISTINCT first_name, last_name FROM employees;
```
즉, 위 쿼리는 성이 같지만 이름이 다른 경우를 유니크한 값으로 판단하여 결과에 포함한다.
집합 함수와 함께 사용된 DISTINCT
```
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(DISRINCT s.salary)를 처리하기 위해 임시 테이블을 사용한다. 이때 임시 테이블의 salary 컬럼엔 유니크 인덱스가 생성되기 때문에 레코드 수가 많아지면 느릴 수 있다. 하지만 인덱스된 컬럼에 대해 DISTINCT 처리를 할 땐 인덱스를 이용하며 임시 테이블 없이 처리할 수 있다.

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받은 레코드를 정렬, 그루핑할 때는 내부적인 임시 테이블을 사용한다.

이는 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과는 다르다.

내부 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블
시스템 변수를 이용해 임시 테이블이 최대로 사용 가능한 메모리 공간의 크기를 정할 수 있다. 메모리에 저장되는 임시 테이블은 TempTable을, 디스크에 저장되는 임시 테이블은 InnoDB를 사용한다.
임시 테이블이 필요한 쿼리
어떤 쿼리에서 임시테이블을 사용하는지는 Extra 컬럼의 Using temporary 가 표시되는지 확인하면 된다. 대표적으로 아래와 같은 경우에 내부 임시 테이블을 생성한다.
- ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
- ORDER BY와 GROUP BY에 명시된 컬럼이 드라이빙 테이블이 아닌 쿼리
- DISTINCT와 ORDER BY가 동시에 존재하거나 DISTINCT가 인덱스로 처리되지 못하는 쿼리
- UNION이나 UNION DISTINCT가 사용된 쿼리
- 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
  
  위 경우 중 마지막 쿼리 패턴을 제외하고 전부 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다.
  
  일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 처리 성능이 느린편이다.

고급 최적화

옵티마이저는 최적의 실행 계획을 수립하기 위해 통계 정보와 옵티마이저 옵션을 결합하여 이용한다.

옵티마이저 옵션은 크게 조인 관련 옵티마이저 옵션과 옵티마이저 스위치로 구분된다.

옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용하여 제어하는데, 여러 옵션을 세트로 묶어 설정하는 방식으로 사용한다.

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용하여 제어하는데 옵션들은 아래와 같다.

옵티마이저 스위치 이름	기본값	설명
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 최적화 사용 여부
subquery_materialization_cost_based	on	비용 기반 Materialization 최적화 사용 여부

위 옵션은 글로벌, 현재 커넥션, 현재 쿼리에 대해서 등 다양한 범위로 설정할 수 있다.

네스티드 루프 조인 (Nested Loop Join)
가장 자주 사용되는 조인 방식이고, 이 방식은 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아 조인을 수행하는 방식이다. 아래와 같은 쿼리로 예시를 들어보자.
```
SELECT *
FROM employees e
INNER JOIN salaries s
	ON s.emp_no=e.emp_no;
```
위 쿼리는 employees 테이블이 드라이빙 테이블이 되어 순서대로 레코드를 한 건 읽고, 드리븐 테이블인 salaries 에서 조건에 만족하는 레코드를 찾아서 바로 반환한다. 이를 의사 코드로 표현하면 아래와 같다.
```
for(row1 IN employees) {
	for(row2 IN salaries) {
		if(row1.emp_no == row2.emp_no) return (row1, row2);
	}
}
```
위 코드에서 알 수 있듯이 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아 반환한다.
MRR과 배치 키 엑세스 (mrr & batched_key_access)
MySQL 서버에서 지원하던 조인 방식은 Nested Loop Join 방식인데 이 방식으로 레코드를 읽고 바로 조인을 하면 스토리지 엔진에서는 아무런 최적화를 할 수 없다. 이를 보완하기 위해 MySQL에서는 레코드를 읽고 바로 조인을 하지 않고 조인 버퍼에 버퍼링하고, 버퍼에 레코드가 가득 차면 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다. 이 과정에서 레코드를 읽을 때 디스크 및 버퍼 풀 접근을 최소화하여 성능을 향상시킬 수 있다. 이러한 읽기 방식을 MRR(Multi Range Read)라고 하며, MRR을 응용한 조인 방식을 BKA 조인이라 한다. BKA 조인은 쿼리에 따라 큰 도움이 되는 경우도 있지만, 부가적인 정렬로 인해 성능에 악영향을 미칠 수 있다.
블록 네스티드 루프 조인 (block_nested_loop)
네스티드 루프 조인과의 가장 큰 차이는 조인 버퍼 사용 여부와 테이블이 어떤 순서로 조인되느냐이다. 조인 알고리즘에서 “Block”이라는 단어가 사용되면 조인용으로 버퍼가 사용됐다는 것을 의미한다. 만약 드리븐 테이블의 레코드를 찾을 때 인덱스를 사용할 수 없다면 드라이빙 테이블의 레코드 수 만큼 드리븐 테이블을 풀 스캔하게된다. 만약 어떤 방식으로도 위 상황을 피할 수 없다면 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 뒤 드리븐 테이블과 메모리 캐시를 조인하는 형태로 처리한다. 이 때 사용되는 메모리의 캐시를 조인 버퍼라고 한다. 결국 블록 네스티드 루프 조인 방식은 조인 버퍼를 이용하여 조인을 하는데, 이 때 드라이빙 테이블의 결과를 조인 버퍼에 담아두고 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다. 주의할 점은 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 달라질 수 있다는 것이다.
인덱스 컨디션 푸시다운 (index_condition_pushdown)
인덱스 컨디션 푸시 다운은 WHERE 절의 조건 중 일부를 스토리지 엔진 레벨에서 평가하도록 "푸시 다운"하여, 필요하지 않은 레코드를 더 빠르게 걸러내는데 도움을 준다.
- 장점
  - 효율적인 레코드 필터링이 가능하다.
  - 불필요한 레코드를 걸러내기 위한 작업이 줄어들어 CPU 사용이 줄어든다.
- 단점
  - 특정 쿼리나 데이터셋에서는 성능 저하의 원인이 될 수 있다.
  - InnoDB, MEMORY 스토리지 엔진에서만 사용된다.
  
  → 대부분의 경우에선 활성화 하는 것이 좋다.
인덱스 확장 (use_index_extensions)
use_index_extensions 옵션은 세컨더리 인덱스에 추가된 PK를 활용여부를 결정하는 옵션이다.
인덱스 머지 (index_merge)
인덱스 머지는 하나의 테이블에 2개 이상의 인덱스를 이용해서 나온 결과를 병합하는 방식으로 처리된다.
인덱스 머지 - 교집합 (index_merge_intersection)
교집합의 경우는 2개 이상의 인덱스를 이용해서 나온 여러 결과들을 AND 연산자로 연결할 경우 실행된다. 실행 계획의 Extra 컬럼에 Using intersect(idx_1, PRIMARY) 라는 메시지가 있다면 idx_1와 PK로 검색한 두 결과를 교집합했다는 뜻이다. 만약 1개의 인덱스를 사용하는 것이 더 효율적이면 index_merge_intersection 최적화를 비활성화 하자.
인덱스 머지 - 합집합 (index_merge_union)
합집합의 경우는 2개 이상의 인덱스를 이용해서 나온 여러 결과들을 OR 연산자로 연결할 경우 실행된다.
```
// idx_1(first_name), idx_2(hire_date) 두 세컨더리 인덱스가 생성되어 있다.
SELECT * FROM employees WHERE first_name='Matt' OR hire_date='1987-03-31';
```
위와 같은 쿼리의 실행 계획에 Using union(idx_1, idx_2 이러한 메시지가 나왔다면 두 인덱스로 검색한 값을 합집합 했다는 것이다. 이때, 합집합 연산을 진행하며 중복되는 데이터가 있을 수 있는데, first_name='Matt'인 레코드와 hire_date='1987-03-31'인 레코드는 인덱스 스캔을 하게되면 PK로 정렬되어 있으므로 두 결과를 우선 순위 큐 알고리즘을 통해 합치면서 중복을 제거한다.
- 참고 1
  모든 세컨더리 인덱스는 클러스터링 인덱스인 PK를 포함하고 있으므로 같은 값 끼리는 PK로 정렬된다.
- 참고 2
  두 조건이 AND로 연결된 경우에는 두 조건 중 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 쿼리가 실행되지만 OR로 연결된 경우에는 둘 중 하나라도 인덱스를 사용하지 못하면 풀 테이블 스캔으로 처리된다.
인덱스 머지 - 정렬 후 합집합 (index_merge_sort_union)
만약 별도의 정렬이 필요한 경우에는 “Sort union”알고리즘을 사용한다. 이전 예제처럼 동등 조건을 사용하면 정렬이 필요 없지만, BETWEEN 같은 경우는 정렬이 필요하다. 이렇게 합집합 연산 전에 정렬을 해야하는 경우 Extra 컬럼에 Using sort_union 문구가 표시된다.
세미 조인 (semijoin)
실제 조인을 수행하진 않고 다른 테이블에 조건에 맞는 레코드가 있는지 체크하는 쿼리를 세미 조인이라 한다. 일단 세미조인 최적화 옵션은 ON으로 해놓자. 아래 쿼리가 세미 조인의 대표적인 예시이다.
```
SELECT *
FROM employees e
WHERE e.emp_no IN
	(SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01');
```
테이블 풀 아웃 (Table Pull-out)
테이블 풀 아웃 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태이다.
```
SELECT *
FROM employees e
WHERE e.emp_no IN
	(SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');
```
위 쿼리에 테이블 풀 아웃 최적화를 적용하면 아래와 같은 쿼리로 바뀐다.
```
SELECT e.*
FROM dept_emp de
	JOIN employees e
WHERE e.emp_no=de.emp_no AND de.dept_no='d009';
```
즉, 기존 서브쿼리를 조인으로 풀어서 사용한 형태이다. 테이블 풀 아웃 최적화는 서브쿼리 부분에서 유니크 인덱스나 PK로 검색하여 결과가 1건인 경우에만 사용가능하다. 또한, 테이블 풀 아웃은 다른 최적화와 함께 적용될 수 있으므로 가능하면 최대한 적용한다.
퍼스트 매치 (firstmatch)
퍼스트 매치 최적화는 IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
```
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'인 레코드들을 찾고 그 결과와 titles 테이블을 조인하는 식으로 실행된다. 조인하는 과정에서 e.emp_no가 1이고, e.first_name이 ‘Matt’인 레코드와 e.emp_no가 3이고, e.first_name이 ‘Matt’인 레코드를 찾은 상태라고 하자. t.emp_no가 1인 레코드들 중 t.from_date BETWEEN '1995-01-01' AND '1995-01-30' 인 레코드를 하나만 발견하면 더이상 emp_no가 1인 경우에 대해서는 탐색하지 않는다. 그런 뒤 emp_no가 2인 레코드에 대해서도 동일한 작업을 시행한다.
루스 스캔 (loosescan)
세미 조인 서브쿼리 최적화의 루스 스캔은 GROUP BY의 루스 인덱스 스캔과 비슷한 방식을 사용한다.
```
// departments 테이블의 레코드는 9건, dept_emp 테이블의 레코드는 33만건
SELECT *
FROM departments d
WHERE d.dept_no IN (
	SELECT de.dept_no FROM dept_emp de);
```
위 쿼리는 dept_emp 테이블에 존재하는 모든 부서 번호에 대해 부서 정보를 읽어 오는 쿼리다. dept_emp 테이블은 dept_no를 기준으로 그루핑하면 9건 밖에 없다. → dept_emp 테이블을 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 효율적 실행이 가능하다.
구체화 (materialization)
Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화하는 방식이다. 구체화는 쉽게 표현하면 내부 임시 테이블을 생성한다는 것을 의미한다. 보통 FirstMatch 최적화가 도움이 안되는 경우에 사용된다. 서브 쿼리의 결과로 임시 테이블을 만들고, 임시 테이블과 대상 테이블을 조인해서 결과를 반환하는 식이다. Materialization 최적화는 서브쿼리가 코릴레이트 서브쿼리가 아니여야 사용가능하고, GROUP BY나 집합 함수가 사용되어도 사용 가능하다.
중복 제거 (Duplicated Weed-out)
Duplicate Weedout은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다. 보통 이 방법으로 최적화되는 경우는 다른 최적화 방법이 많이 있다.
컨디션 팬아웃 (condition_fanout_filter)
condition_fanout_filter 최적화를 활성화하면 옵티마이저가 조건을 충족하는 레코드 수를 더 정확히 예측할 수 있다. 조인 시 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미치는데, 예상 레코드 수가 정확해 지면 옵티마이저가 더 효율적인 순서로 조인이 실행되도록 계획을 세울 수 있다. 대신 condition_fanout_filter 최적화를 활성화하면 계산을 위해 더 많은 자원을 사용한다. 그러므로 쿼리의 실행 계획이 잘못된적이 별로 없다면 별로 도움이 되지 않는다.
파생 테이블 머지 (derived_merge)
이전 버전 MySQL에선 FROM절에 사용된 서브쿼리는 먼저 실행해서 임시 테이블로 만드는 식으로 처리했다. 이 실행 계획은 임시 테이블을 생성하고 조건에 맞는 레코드를 읽어서 임시 테이블에 INSERT한다. 그리고 다시 임시 테이블을 읽으므로 오버헤드가 추가된다. 만약 임시 테이블의 크기가 메모리에 못 들어갈 정도로 커지면 성능은 많이 느려질 것이다. derived_merge최적화 옵션은 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합하는 식으로 최적화한다.
인비저블 인덱스 (use_invisible_indexes)
인덱스를 삭제하지 않으면서 옵티마이저가 사용하지 못하도록 할 수 있다. use_invisible_indexes 옵티마이저 옵션을 이용하면 INVISIBLE로 설정된 인덱스도 사용할 수 있다.
스킵 스캔 (skip_scan)
인덱스 스킵 스캔은 인덱스의 선행 컬럼이 아닌 컬럼을 조건으로 쓸 때 사용할 수 있는 최적화 방법이다. 만약 인덱스 선행 컬럼의 카디널리티가 크면 스킵 스캔최적화를 비활성화 하는 것이 좋다.
해시 조인 (hash_join)
해시 조인은 첫 레코드를 찾는데는 오래걸리지만, 마지막 레코드를 찾는 시점은 더 빠르다. 즉, 중첩 루프 조인은 첫 레코드는 빠르게 받기 때문에 최고 응답 속도 전략(OLTP에 유리)에 적합하고, 해시 조인은 최고 스루풋 전략(OLAP에 유리)에 적합하다. 해시 조인은 두 단계로 나뉘어 처리된다.
- 빌드 단계: 조인 대상 테이블 중 레코드 건수가 적은 테이블을 메모리에 해시 테이블을 생성한다.
- 프로브 단계: 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는다.
  
  보통 네스티드 루프 조인을 사용할 수 없는 경우 해시 조인이 사용된다.
인덱스 정렬 선호 (prefer_ordering_index)
```
SELECT *
FROM employees
WHERE hire_date BETWEEN '1985-01-01' AND '1985-02-01'
ORDER BY emp_no;
```
위 쿼리는 대표적으로 아래 2가지 실행 계획을 선택할 수 있다.
1. hire_date 컬럼으로 생성된 인덱스를 이용해 조건에 맞는 레코드를 찾고 emp_no로 정렬
2. PK를 정순으로 읽으며 조건에 맞는 레코드를 찾아서 반환
  
  일반적으로는 hire_date 컬럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적일 것이다.
  
  하지만, 옵티마이저가 ORDER BY절의 인덱스에 가중치를 너무 부여하여 2번이 선택될 수 있다.
  
  이렇게 자주 실수를 한다면 prefer_ordering_index 옵션을 OFF로 변경하자.

조인 최적화 알고리즘

Exhaustive 검색 알고리즘, Greedy 검색 알고리즘 두 가지가 있다.

조인 테이블 개수가 많아지면 실행 계획을 수립하는 데만 많은 시간이 걸린다.

쿼리 힌트

MySQL에서 사용 가능한 쿼리 힌트는 인덱스 힌트, 옵티마이저 힌트 두 가지로 구분할 수 있다.

쿼리 힌트는 옵티마이저에게 올바른 방향으로 실행 계획을 수립할 수 있도록 알려주는 역할을 한다.

인덱스 힌트

인덱스 힌트는 SELECT, UPDATE 문에서만 사용할 수 있고, 가능하면 옵티마이저 힌트를 사용할 것을 추천한다.

STRAIGHT_JOIN
STRAIGHT_JOIN은 SELECT STRAIGHT_JOIN ~ 이러한 형태로 쓰이고, FROM절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.
USE / FORCE / IGNORE INDEX
```
SELECT *
FROM table_1 USE INDEX [FOR ORDER BY/GROUP BY/JOIN](idx_1)
```
~ INDEX 힌트를 사용하기 위해선 위 쿼리와 같이 인덱스를 가지는 테이블 뒤에 힌트를 명시해야한다. FOR ORDER BY/GROUP BY/JOIN 를 붙혀 인덱스의 용도를 제한할 수 있다. USE는 인덱스 사용 권장, FORCE는 더 강하게 사용 권장(잘 안씀), IGNORE는 인덱스를 못 사용하게 한다.
SQL_CALC_FOUND_ROWS
LIMIT이 걸려있어도 끝까지 검색하여 결과를 반환하도록 하는 힌트이다.

옵티마이저 힌트

옵티마이저 힌트는 영향 범위에 따라 4가지로 나눌 수 있다.

인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
쿼리 블록: 힌트가 명시된 쿼리 블록에 대해서 영향을 미치는 옵티마이저 힌트
글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트

참고로 모든 인덱스 수준의 힌트는 아래와 같이 테이블명이 선행되어야 한다.

SELECT /*+ INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name='Matt';

MAX_EXECUTION_TIME
쿼리의 최대 실행 시간을 설정하는 힌트다. 밀리초 단위의 시간을 설정할 수 있으며, 지정된 시간을 초과하면 쿼리가 실패한다.
SET_VAR
MySQL 서버의 시스템 변수를 설정하는 힌트이다.
SEMIJOIN & NO_SEMIJOIN
SEMIJOIN(세미_조인_최적화_전략_이름) 힌트는 어떤 세미 조인 최적화 전략을 사용할지를 제어할 수 있다. 만약 세미 조인을 사용하고 싶지 않다면 NO_SEMIJOIN 힌트를 사용하면된다.
SUBQUERY
서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 방법으로 INTOEXISTS와 MATERIALIZATION 두 가지 방법이 있다.
BNL & NO_BNL & HASHJOIN & NO_HASHJOIN
해시 조인을 유도하고 싶으면 BNL(테이블1, 테이블2) 이렇게 힌트를 작성하고, 사용하지 않게 한다면 NO_BNL 힌트를 사용하면 된다.
JOIN 관련
JOIN 순서를 위한 4가지 힌트가 있다.
- JOIN_FIXED_ORDER(): FROM절의 테이블 순서대로 조인을 실행
- JOIN_ORDER(tb1,tb2, ...): 힌트에 명시된 테이블 순서대로 조인을 실행
- JOIN_PREFIX(tb1): 드라이빙 테이블을 설정
- JOIN_SUFFIX(tb1, tb2, …): 드리븐 테이블을 설정
MERGE & NO_MERGE
MERGE(서브쿼리_이름): 임시 테이블을 사용하지 않게 서브 쿼리를 외부 쿼리와 병합 NO_MERGE(서브쿼리_이름): 임시 테이블을 사용하도록 강제
INDEX_MERGE & NO_INDEX_MERGE
인덱스 머지를 강제하거나 사용하지 않도록 하는 힌트이다.
NO_ICP
인덱스 컨디션 푸시다운 최적화는 성능 향상에 도움이 되므로 사용하는 방향으로 실행 계획을 수립한다. 그런데 ICP로 인해 실행 계획 비용 계산이 잘못되어 잘못된 실행 계획이 수립될 수도 있다. 그래서 NO_ICP 힌트로 ICP를 비활성화할 수 있다.
SKIP_SCAN & NO_SKIP_SCAN
인덱스 스킵 스캔을 강제하거나 사용하지 않도록 하는 힌트이다.
INDEX & NO_INDEX
이전에 사용되던 인덱스 힌트를 대체하는 용도로 제공되는 힌트이다.

정훈희

DB를 사랑하는 백엔드 개발자입니다. 열심히 공부하고 열심히 기록합니다.