mysql, 옵티마이저(Optimizer)의 최적화 하기 - 옵티마이저 스위치와 쿼리 힌트

2024-06-25 • infoqoch •

mysql

•

mysql

옵티마이저의 최적화

MySQL의 버전이 올라갈 수록 옵티마이저의 성능은 개선되고 전반적인 성능이 개선된다.
하지만 옵티마이저가 기대하는 방향으로 동작하지 않을 수 있으며 상황에 따라 특정 동작을 유도해야 할 수 있다. 이런 경우 옵티마이저의 스위치나 힌트를 활용하여 변경할 수 있다.
옵티마이저는 옵티마이저 옵션에 따라 동작하는데, 옵션은 옵티마이저 스위치(optimizer_switch)를 사용하여 변경한다.
이번 글은 옵티마이저 옵션의 각 기능을 살펴보며 MySQL의 최적화 기법을 알아보는데 목적이 있다.

옵티마이저 스위치 옵션

optimizer_switch 시스템 변수를 이용하여 제어.
글로벌, 세션, 쿼리 힌트로 설정 가능하다.

// mysql 서버 전체적으로 설정
SET GLOBAL optimizer_switch='index_merge=on, index_merge_union=on  ...';

// 현재 커넥션의 옵티마이저 스위치만 설정
SET SESSION optimizer_switch='index_merge=on, index_merge_union=on  ...';

// 힌트를 사용하여 현재 쿼리에만 설정
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ ... FROM ...;

설정할 수 있는 스위치와 그 설명은 아래와 같다.

옵티마이저 스위치 이름	기본값	설명
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 최적화를 사용할지 여부 설정

MRR(Multi-Range Read)과 배치 키 엑세스(mrr & batched_key_access)

MySQL은 조인을 수행할 때, 일반적인 개발 언어에서 중첩 반복문에 대응하는 네스티드 루프 조인(Nested Loop Join)을 사용한다. MySQL 엔진이 드라이빙 테이블의 레코드를 한 건 읽고, 스토리지 엔진은 해당 레코드와 일치하는 레코드를 찾기 위하여 드리븐 테이블를 스캔한다.
이러한 네스티드 루프는 동작 방식으로 인해 성능 문제가 발생한다. 스토리지 엔진 입장에서는 레코드 한 건마다 드리븐 테이블을 검색하여 불필요하게 많은 요청과 I/O가 발생한다. 더불어 스토리지 엔진 차원에서의 최적화가 불가능했다.
이 단점을 보완하기 위하여 MySQL 서버는 조인을 바로 실행하지 않고 드라이빙 테이블의 레코드를 미리 버퍼링한다. 조인 버퍼가 가득 차면 버퍼링된 레코드를 정렬하고 스토리지 엔진에 한 번에 요청한다. 스토리지 엔진은 해당 요청 묶음에 대해 디스크 읽기를 최소화하도록 내부적으로 최적화한다. 이 방식을 MRR이라 한다.
MRR을 활용한 조인방식을 BKA(Bached Key Access)라 한다. BKA는 쿼리 특성에 따라 성능이 하락할 수 있으므로 기본적으로 비활성화 되어 있다.

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

단어 ‘block’은 버퍼를 사용한다는 의미와 같다. 블록 네스티드 루프 조인은 버퍼를 사용하는 방식이다.
실행계획에서 “Using Join Buffer”로 나온다.
일반적으로 드리븐 테이블에 인덱스가 있을 경우 특별한 버퍼 없이 네스티드 루프 조인으로 빠르게 조인을 수행한다. 하지만 드리븐 테이블을 인덱스를 사용할 수 없을 경우 조인 버퍼(join_buffer_size)를 사용한다. 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 조인 버퍼에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이로 인하여 드리븐 테이블과 드라이빙 테이블의 역할이 역전된다.
아래는 조인 버퍼를 사용한 블록 네스티드 루프 조인의 예제이다. 연결 고리 역할을 하는 조인 조건이 없는 카테시안 조인이다.

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

id	select_type	table	type	key	Extra
1	SIMPLE	de	range	ix_fromdate	Using index condition
1	SIMPLE	e	range	PRIMARY	Using join buffer (block nested loop)

조인 버퍼는 아래와 같이 사용된다.
1. dept_emp 테이블의 ix_fromdate 인덱스를 이용해(from_date>’1995-01-01’) 조건을 만족하는 레코드를 검색한다.
2. 조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다.
3. employees 테이블의 프라이머리 키를 이용해 (emp_no<109004) 조건을 만족하는 레코드를 검색한다.
4. 3번에서 검색된 결과(employees)에 2번의 캐시된 조인 버퍼의 레코드(dept_emp)를 결합해서 반환한다.
위 그림과 과정을 보면, 드라이빙 테이블인 dept_emp가 조인 버퍼에 저장되고, 드리븐 테이블인 employees이 조인 버퍼 dept_emp를 순회한 후 데이터를 병합한다.
이로 인하여 블록 네스티드 루프 조인의 정렬 순서는 흐트러질 가능성이 있다.
MySQL 8.0.20 버전부터 블록 네스티드 루프 조인은 사용되지 않고 해시 조인 알고리즘으로 대체되었다.

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

인덱스에서 처리할 수 있는 조건을 최대한 인덱스 레벨에서 필터링한 후, 필요한 경우에만 테이블에 접근하는 방식. MySQL 엔진에서 가능한 많은 조건을 정리하여 스토리지 엔진에서 최소한의 데이터에 접근하도록 유도.
이를 통해 불필요한 테이블 접근을 줄이고 쿼리 성능을 향상.
후술하는 index extension은 푸시다운의 한 방법.

ALTER TABLE employees ADD INDEX ix_lastname_firstname (last_name, first_name);
SELECT * FROM employees WHERE last_name = 'Action' AND first_name LIKE '%sal';

위 로직의 경우 5.6버전 이전에는 first_name LIKE '%sal'을 인덱스 테이블에서 처리할 수 없다고 판단. 인덱스 테이블에서는 last_name에 대해서만 필터링하고 나머지 작업은 테이블에서 처리. 이로 인하여 불필요한 레코드까지 접근하였다.
5.6 이후에는 인덱스 테이블에서 first_name까지 처리한다. 조건을 최소화하여 테이블에 접근하는 방식.
간단한 개선이지만 매우 큰 최적화가 이뤄졌다.

인덱스 확장(use_index_extensions)

InnoDB 스토리지 엔진의 세컨더리 인덱스는 프라이머리 키를 값으로 가진다. 그러므로 세컨더리 인덱스가 프라이머리 키를 칼럼으로부터 제외하더라도 결과적으로 마지막에 프라이머리 키를 칼럼으로 가진 것과 같다. 이런 원리를 사용하여 PK가 인덱스에 있는 것처럼 처리하는 방식이다.
- index(col_a, col_b)는 index(col_a, col_b, pk)와 같다.
- where col_a = 'a' and col_b = 'b' and pk = 'c';로 쿼리하였다면 pk = 'c' 검색 조건까지 세컨더리 인덱스로 최적화한다.
- where col_a = 'a' and col_b = 'b' order by pk;로 쿼리하였다면 order by pk 정렬 조건까지 세컨더리 인덱스로 최적화한다.

인덱스 머지(index_merge)

인덱스를 사용하는 경우, 일반적으로 옵티마이저는 테이블 당 하나의 인덱스만 사용하도록 실행 계획을 수립한다. 인덱스 머지의 경우 하나의 테이블에 대해 2개 이상의 인덱스를 사용한다. 이를 인덱스 머지라 한다.
인덱스 머지는 아래와 같이 세 가지 방식으로 동작한다.
- index_merge_intersection
- index_merge_sort_union
- index_merge_union

인덱스 머지 - 교집합

옵티마이저가 두 개의 조건(where)을 각각의 인덱스에서 찾은 후 교집합한다.
실행계획에서는 “Using intersect”로 나온다.

SELECT *
FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
-- Using intersect

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

where절의 or가 존재하며 각각의 인덱스의 결과를 합치는 방식이다.
실행계획에서는 “Using union”로 나온다.

SELECT *
FROM employees
WHERE first_name='Matt ' OR hire_date='1987-03-31';
-- Using union(ix_firstname,ix_hiredate); Using where

MySQL은 합집합 과정에서 priority queue를 사용한다.
위 쿼리의 경우 인덱스가 ix(hire_date)와 ix(first_name)으로 되어 있어 PK로 이미 정렬되어 있다. 그러므로 특별한 정렬 없이 큐를 사용하여 바로 병합한다.

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

앞서 합집합과 달리 정렬이 필요한 경우 정렬 후 합집합을 수행한다.
실행 계획에서는 “Using sort_union”로 나온다.

SELECT * FROM employees
WHERE first_name='Matt'
   OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'
-- Using sort_union(ix_firstname,ix_hiredate); Using where

hire_date BETWEEN '1987-03-01' AND '1987-03-31'의 경우 PK 정렬이 되어 있지 않다. 그러므로 hire_date를 pk로 정렬한 후 유니온을 실행한다.

우선순위 큐는 내부적으로 정렬을 지원한다. 정렬되지 않은 집합을 바로 큐에 넣지 않고 왜 정렬한 후 큐에 삽입할까? 시간복잡도의 측면에서 두 방식 모두 동일한 시간이 소요되지만, 일반적으로 배열의 정렬하는 것이 큐 내부에서 정렬하는 것보다 성능 상 유리하다고 본다. 이 부분은 가능하다면 차후 정리하겠음.

세미조인(Semi-Join)

세미 조인(Semi-Join)이란 다른 테이블과 실제 조인을 수행하지는 않고 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 뜻한다.
세미 조인은 일반적인 형태와 안티 세미 조인(Anti Semi-join) 형태로 구분된다.
일반적인 형태(=, IN)는 다음과 같다.
- 세미 조인 최적화
- IN-to-EXISTS 최적화
- MATERIALIZATION 최적화
안티 세미조인(<>, NOT INT)은 다음과 같다.
- IN-to-EXISTS 최적화
- MATERIALIZATION 최적화
MySQL 8.0 이후부터는 다음의 최적화가 도입되었다.
- Table Pull-Out
- Duplicate Weed-out
- First Match
- Loose Scan
- Materialization
Table Pull-Out은 항상 세미 조인보다 좋은 성능을 제공하므로 옵션이 제공하지 않는다. 나머지는 옵션으로 제공한다.

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

서브쿼리에 사용하는 테이블을 join(아우터 쿼리)으로 재작성하는 방식이다.
서브쿼리 최적화 이전에 사용하던 대표적인 쿼리 튜닝 방식이다. 반대로 테이블 풀-아웃으로 인하여 굳이 서브쿼리를 조인을 튜닝할 필요가 사라지게 되었다.
실행계획에서는 특별한 코멘트가 나오지 않는다. 다만 드리븐의 id칼럼이 1로 출력된다. 마찬가지로 이하 세미조인 전략에서 id 칼럼이 1인 경우 내부적으로는 조인을 실행했다고 유추할 수 있다.

-- 기존 쿼리
SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');

-- MySQL이 아래와 같이 튜닝
SELECT *
FROM dept_emp de
JOIN employees e
WHERE e.emp_no = de.emp_no
  AND de.dept_no = 'd009';

퍼스트 매치(firstmatch)

세미 조인을 EXISTS 형태로 튜닝한다.
실행계획에 FirstMatch(table name)로 나온다.

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

위 쿼리에서 titles은 PK(emp_no, from_date, title)을 인덱스로 가진다.
employees에서 first_name = 'Matt'로 필터링한 결과로서 emp_no 리스트를 추출한다. 이 값을 기준으로 titles는 emp_no와 from_date를 PK 인덱스를 사용하여 존재하는지의 여부만 판정하면 된다.
그러므로 이 쿼리는 exists(IN-TO-EXISTS) 절처럼 동작한다. 실제 처리는 join으로 변환되어 처리된다.

루스 스캔(loosescan)

group by의 최적화처럼 동작한다.
실행계획에서 “LooseScan”으로 나온다.
아래 쿼리에서는 in 절이 수행해야 할 작업은 dept_emp 테이블의 유일한 dept_no 값이 무엇인지를 찾는 것이다. 그러므로 dept_no의 카디널리티가 낮을 수록 성능이 좋다.

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

구체화(Materialization)

세미 조인에 사용된 서브쿼리를 임시 테이블로 생성(구체화)해서 최적화하는 방식.
실행계획의 select_type에 MATERIALIZED로 나온다.

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

위 쿼리에서 employees에 대한 조건이 pk 이외에는 없다. 그러므로 바로 풀스캔으로 레코드를 출력하면 된다.
dept_emp의 경우 from_date=’1995-01-01’에 해당하는 emp_no가 필요하다.
이런 경우 dept_emp에서 필터링된 emp_no를 기준으로 임시 테이블을 생성하고, 해당 임시 테이블과 employees을 조인하여 employees 테이블에서 바로 데이터를 출력하는 것이 효과적이다. 이런 경우 임시 테이블로 구체화하여 처리한다.

중복 제거(Duplicated Weed-out)

서브쿼리를 일반적인 inner join 쿼리로 바꿔서 결과를 만든 후, 마지막에 중복된 레코드를 제거하는 방법이다.
실행계획에서는 “start temporary”와 “end temporary”로 나온다.

-- 쿼리
SELECT * FROM employees e
WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary>150000);

-- 내부적인 변경
SELECT e.* FROM employees e, salaries s
WHERE e.emp_no=s.emp_no AND s.salary>150000
GROUP BY e.emp_no;

salaries는 pk(emp_no, from_date)를 가진다. 그러므로 employees 입장에서는 from_date로 인한 불필요한 중복이 발생하는데 이를 제거하기 위하여 마지막에 group_by의 형태로 최적화한다.
먼저 emp_no를 기준으로 두 개의 테이블을 조인하고 그 결과를 임시테이블에 저장한다. 임시테이블에서 group by를 실행한다.

컨디션 팬아웃(condition_fanout_filter)

옵티마이저가 실행 계획을 수립할 때 최적의 인덱스를 선택하기 위해 사용하는 기능이다. 실행계획에서 filtered 칼럼은 값의 분포도를 의미하며, 이 분포도는 실행 계획을 선정하는 데 중요한 역할을 수행한다. 컨디션 팬아웃은 filtered를 예측하는 기준이 되며, 이는 where절의 다른 조건에 대한 인덱스의 메타데이터를 활용한다.

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

결과적으로 위 쿼리는 ix_first_name(first_name) 인덱스를 선택했다. 하지만 이 쿼리를 사용할 경우 hire_date를 풀스캔할 때 얼마나 많은 데이터를 접근할지 예상할 수 없다. 이때, 옵티마이저는 ix_hire_date(hire_date) 인덱스를 메타데이터를 사용하여 이를 예측하는 데 도움을 준다. 실제로 condition_fanout_filter를 사용하거나 사용하지 않을 경우 실행 계획에서 filtered 값이 달라짐을 볼 수 있다.
이 동작이 정상적으로 이루어지려면 해당 칼럼에 1) 인덱스가 있어야 하며, 2) 메타데이터로 사용할 히스토그램이 필요하다.
실행 계획 수립 과정에서 추가 리소스를 사용하므로, 성능 향상에 도움이 되지 않는다고 판단되면 이 기능을 끌 수도 있다.

스킵 스캔(skip_scan)

어떤 인덱스의 선행 칼럼이 조건절에 적용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능한 방식이다.
ix(a, b, c)가 있으며 where b = 'b' and c = 'c';라 하더라도 해당 인덱스를 사용할 수 있다. 다만 선행 칼럼(a)의 카디널리티가 낮을 수록 유리하다.

해시 조인(hash_join)

해시 조인은 네스티드 루프 조인과 더불어 MySQL에서 조인을 처리하는 방식 중 하나이다. MySQL 8.0.18 버전부터 추가 지원되었으며 8.0.20부터 블록 네스티드 루프 조인을 대체하게 되었다.
네스티드 루프 조인은 해시 조인에 대비하여 첫 번째 레코드를 찾는 시간이 빠르다. 반대로 마지막 레코드를 찾는 시간은 느리다. 그러므로 빠른 응답은 전자가 처리량은 후자가 유리하다.
대용량 처리보다 온라인 트랜잭션을 목적으로 둔 MySQL은 빠른 응답을 제공하는 네스티드 루프 조인을 우선한다. 그러므로 해시 조인은 제한적으로 사용한다. 해시 조인을 사용하는 경우는 1) 인덱스가 없거나 2) 테이블의 레코드가 적은 경우 해쉬 조인을 사용한다. 그러므로 힌트 등을 사용하여 해시 조인으로 유도할 필요가 없다.
더불어 해시 조인은 ‘블록’ 네스티드 루프 조인을 대체하였다. 블록 네스티드 루프 조인은 한정된 공간인 조인 버퍼에서 대체로 큰 용량을 가진 테이블을 처리해야하는 문제가 있었. 해쉬 조인은 이런 제한적인 상황을 개선하기 위해 사용되었다. 그러므로 해시 조인은 ‘블록’ 네스티드 루프 조인을 대체하기 위하여 나왔으나, 애당초 잘 설계된 데이터베이스에서는 블록 네스티드 루프 조인을 보기 어려웠다.

SELECT *
FROM employees e IGNORE INDEX (PRIMARY, ix_hiredate) 
         INNER JOIN dept_emp de IGNORE INDEX (ix_empno_fromdate, ix_fromdate )
                    ON de.emp_no = e.emp_no AND de.from_date = e.hire_date;
-- index를 사용하지 못하자 hash join을 사용함을 확인할 수 있다.

해시 조인은 1) 빌드 단계(Build-phase)와 프로브 단계(Probe-phase)로 나뉘어 처리한다.
빌드단계에서는 조인 대상 테이블 중 레코드 건수가 적어서 해시 테이블로 만들기 용이한 테이블을 골라 메모리에 해시 테이블을 생성한다. 빌드 테이블이라고도 한다.
프로브 단계에서 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는다. 나머지 테이블을 프로브 테이블이라 한다. 실행계획은 최하단(들여쓰기가 가장 많이 된) 테이블이 가장 먼저 실행되며 해당 테이블이 빌디 테이블이 된다.
해시 테이블은 다른 조인과 동일하게 조인 버퍼를 사용한다. 만약 부족할 경우 다른 조인과 동일하게 빌드 청크와 프로브 청크를 디스크를 사용해 저장하고 호출하며 조인 결과를 출력한다.

인덱스 정렬 선호(prefer_ordering_index)

옵티마이저는 group by와 order by를 사용할 경우 인덱스에 가중치를 높게 둔다. 하지만 경우에 따라 pk를 기준으로 풀스캔을 하는 것이 더 효과적일 수 있다. 이런 경우 힌트를 활용하여 IGNORE INDEX를 사용하곤 하였다.
MySQL 8.0.21 버전부터는 ORDER BY를 위한 인덱스에 대한 너무 많은 가중치를 부여하지 않도록 prefer_ordering_index란 옵션을 추가하였다.

최선의 실행계획을 판정하기 위한 알고리즘

조인 쿼리의 실행 계획을 최적화하기 위한 알고리즘이 Exhaustive와 Greedy 알고리즘이 있다.
테이블 개수가 많아질 경우 실행계획을 수립하는 것만 몇 분을 넘어 몇 시간이 소요되는 경우가 있다. 이런 현상이 왜 발생하는지 파악하자.

Exhaustive 검색 알고리즘

MySQL 5.0과 그 이전 버전에서 사용하던 조인 최적화 기법이다. FROM절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다. 테이블이 20개라면 20!가 된다.

Greedy 검색 알고리즘

Exhaustive를 개선한 방법이다.

아래의 방식으로 동작한다.
1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 변수에 설정된 개수의 테이블로 가능한 조인 조합 생성. 위 예제는 값이 2라고 가정한다.
2. 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
3. 실행 계획의 첫 번째 테이블을 부분 실행 계획의 첫 번째 테이블로 선정
4. 3번에서 선택된 테이블(t3)을 제외하고 optimizer_search_depth 로 정의된 개수의 테이블로 가능한 조인 조합을 생성
5. 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행 계획에 대입해 실행 비용 계산
6. 5번의 비용계산 결과, 최적의 실행 계획에서 두 번째 테이블을 3번에서 생성된 부분 실행 계획의 두 번쨰 테이블로 선정
7. 남은 테이블이 모두 없어질 때까지 4~6번을 반복 실행하며 부분 실행 계획에 테이블의 조인 순서를 기록
8. 최종적으로 조인 순서 결정 됨
optimizer_search_depth의 기본값은 62이며 0~62 사이를 결정한다. 0은 MySQL이 그 갯수를 결정한다. 62는 너무 많은 자원을 소모하므로 대체로 4~5 정도로 설정한다.
optimizer_prune_level은 Heuristic 검색의 유무를 결정한다. 휴리스틱은 이미 계산된 값보다 큰 경우 생략하는 방식이다. 이 값은 특별한 이유가 없다면 1로 사용하는 것이 좋다.

쿼리 힌트

대체로 MySQL은 버전이 업그레이드되며 통계 정보나 옵티마이저의 최적화 방식이 발전한다. 그러나 옵티마이저가 완벽한 것은 아니라 직접 실행 계획에 영향을 줘야하는 경우가 있다. 이 경우 힌트를 사용한다.
힌트는 다음과 같이 분류한다.
- 인덱스 힌트
- 옵티마이저 힌트
- 그 외
테이블 구조나 상황은 계속 달라지므로 힌트는 차선이 되어야 한다. 최선은 힌트가 필요로한 쿼리를 사용하지 않거나 튜닝하거나 혹은 테이블 구조를 변경해야 한다. 그러나 이런 업무는 쉽지 않으므로 힌트를 쓰는 경우가 있다.

인덱스 힌트

“STRAIGHT_JOIN”과 “USE INDEX” 등을 포함한 인덱스 힌트는 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다. 이들은 모두 ANSI 표준 문법을 준수하지 못한다.
MySQL 5.6 이후 추가되는 옵티마이저 힌트는 다른 RDBMS에서는 주석으로 해석하도록 하여 ANSI 표준을 지킨다. 그러므로 옵티마이저 힌트를 권장한다.
인덱스 힌트는 SELECT과 UPDATE에만 사용 가능하나 옵티마이저 힌트는 CRUD 전체에 사용 가능하다.

SELECT STRAIGHT_JOIN a.col1, b.col2 ....; -- MySQL에 특화된 문법이다. 
SELECT /*! STRAIGHT_JOIN*/ a.col1, b.col2 ....; -- ANSI 표준을 지킨다!

STRAIGHT_JOIN, JOIN_FIXED_ORDER

쿼리에 작성한 조인의 순서대로 조인을 한다.
SELECT 다음에 작성한다.
옵티마이저 힌트 중 JOIN_FIXED_ORDER는 동일한 역할을 수행하며 JOIN_ORDER 외 이와 유사한 힌트가 있다.

USE INDEX / FORCE INDEX / IGNORE INDEX

테이블이 사용할 인덱스를 결정한다.
대체로 옵티마이저는 인덱스를 잘 선정하나 3-4개 이상 칼럼이 늘어나고 비슷한 인덱스가 많아질 경우 실수를 할 수 있다. 이런 경우 사용한다.
PK 인덱스를 사용할 경우 해당 인덱스의 이름은 “PRIMARY” 이다.
인덱스의 종류는 다음과 같다.
- USE INDEX: 특정 테이블의 인덱스를 사용하도록 권장. 사용하지 않을 수도 있다. 가장 많이 사용.
- FORCE INDEX: 실무에서는 USE INDEX에서 사용하지 못하는 인덱스는 FORCE INDEX로도 사용할 수 없다. 굳이 사용할 필요가 없음.
- IGNORE INDEX: 특정 인덱스를 사용하지 못하도록 한다. 대체로 풀테이블 스캔을 유도할 때 사용한다.
힌트의 용도를 결정할 수 있으며 없을 경우 전체를 고려한다.
- USE INDEX FOR JOIN: 조인, 레코드 검색
- USE INDEX FOR ORDER BY: 정렬
- USE INDEX FOR GROUP BY: 그룹핑
- 대체로 용도까지는 설정하지 않는다.

SQL_CALC_FOUND_ROWS

MySQL은 LIMIT을 사용하면 해당 숫자를 초과하여 레코드를 탐색하지 않는다. SQL_CALC_FOUND_ROWS 힌트를 사용할 경우 끝까지 검색을 수행한다.
보통 FOUND_ROWS() 함수를 이용하여 레코드 전체의 갯수를 파악하기 위해 사용한다.

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5; -- 5건만 출력
SELECT FOUND_ROWS( ); -- 60000건

웹 프로그래밍에서 자주 사용한다. 다만 아래의 상황에서는 사용을 주의해야 한다.

SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;
SELECT FOUND_ROWS() AS total_record_count;

위 쿼리는 first_name='Georgi'란 조건이 존재하며 ix(first_name)으로 조회한다. 20건을 조회하였고 남은 레코드가 200건일 경우, 인덱스 테이블은 200건을 선형으로 조회하더라도 select * 절 때문에 테이블에 200건의 랜덤 I/O가 발생한다.
그러므로 위 방식보단 아래와 같이 count를 사용하는 것이 훨씬 빠르다. count()는 커버링 인덱스로서 인덱스 테이블 탐색으로 모든 작업이 종료되기 때문이다.

SELECT count(1) FROM employees WHERE first_name='Georgi'; -- 커버링 인덱스. 매우 빠름.
SELECT * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;

그 외 union에서는 정확하게 동작하지 않는다.
결과적으로 정상적으로 튜닝한 경우 select count(1)를 사용한 커버링 인덱스가 빠르므로, 이런 경우가 아닌 이상 힌트를 사용하지 않는 것을 권장한다.

옵티마이저 힌트

옵티마이저 힌트는 영향 범위에 따라 4가지로 분류한다.
- 인덱스: 특정 인덱스 이름을 사용
- 테이블: 특정 테이블 이름을 사용
- 쿼리 블록: 특정 쿼리 블록에 사용. 해당 쿼리 블록에면 영향.
- 글로벌: 쿼리 전체에 영향

힌트 이름	설명	영향 범위
MAX_EXECUTION_TIME	쿼리의 실행 시간 제한	글로벌
RESOURCE_GROUP	쿼리 실행의 리소스 그룹 설정	글로벌
SET_VAR	쿼리 실행을 위한 시스템 변수 제어	글로벌
SUBQUERY	서브쿼리의 세미 조인 최적화(MATERIALIZATION과 INTOEXISTS) 전략 제어	쿼리 블록
BKA, NO_BKA	BKA(Batched Key Access) 조인 사용 여부제어	쿼리 블록, 테이블
BNL, NO_BNL	MySQL 8.0.18 이전: 블록 네스티드 루프 조인 사용 여부 제어 MySQL 8.0.20 부터: 해시 조인 사용 여부 제어	쿼리 블록, 테이블
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	외부 쿼리의 조건을 서브 쿼리로 옮기는 최적화 사용 여부 제어	쿼리 블록, 테이블
HASH_JOIN, NO_HASH_JOIN	해시 조인 사용 여부 제어 MySQL 8.0.18 버전에서만 사용 가능하다.	쿼리 블록, 테이블
JOIN_FIXED_ORDER	FROM 절에 명시된 테이블 순서대로 조인 실행	쿼리 블록
JOIN_ORDER	힌트에 명시된 테이블 순서대로 조인 실행	쿼리 블록
JOIN_PREFIX	힌트에 명시된 테이블을 조인의 드라이빙 테이블로 조인 실행	쿼리 블록
JOIN_SUFFIX	힌트에 명시된 테이블을 조인의 드리븐 테이블로 조인 실행	쿼리 블록
QB_NAME	쿼리 블록의 이름 설정을 위한 힌트	쿼리 블록
SEMIJOIN, NO_SEMIJOIN	서브쿼리의 세미 조인 최적화(DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION) 전략 제어	쿼리 블록
MERGE, NO_MERGE	FROM 절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는 최적화를 수행할지 여부 제어	테이블
INDEX_MERGE, NO_INDEX_MERGE	인덱스 병합 실행 계획 사용 여부 제어	테이블, 인덱스
MRR, NO_MRR	MRR(Multi-Range Read) 사용 여부 제어	테이블, 인덱스
NO_ICP	ICP(인덱스 컨디션 푸시다운) 최적화 전략 사용 여부 제어	테이블, 인덱스
NO_RANGE_OPTIMIZATION	인덱스 레인지 액세스를 비활성화 (특정 인덱스를 사용하지 못하도록 하거나, 쿼리를 풀 테이블 스캔 방식으로 처리)	테이블, 인덱스
SKIP_SCAN, NO_SKIP_SCAN	인덱스 스킵 스캔 사용 여부 제어	테이블, 인덱스
INDEX, NO_INDEX	GROUP BY, ORDER BY, WHERE 절의 처리를 위한 인덱스 사용 여부 제어	인덱스
GROUP_INDEX, NO_GROUP_INDEX	GROUP BY 절의 처리를 위한 인덱스 사용 여부 제어	인덱스
JOIN_INDEX, NO_JOIN_INDEX	WHERE 절의 처리를 위한 인덱스 사용 여부 제어	인덱스
ORDER_INDEX, NO_ORDER_INDEX	ORDER BY 절의 처리를 위한 인덱스 사용 여부 제어	인덱스