TIL

9.4(2) 옵티마이저 힌트

9.4.2 옵티마이저 힌트

MySQL 8.0버전에서 사용 가능한 힌트는 매우 다양하며 미치는 영향 범위도 다양하다.
옵티마이저 힌트는 영향 범위에 따라 4그룹으로 나누어 볼 수 있다.
- 인덱스: 특정 인덱스의 이름을 사용할 수 있는 힌트
- 테이블: 특정 테이블의 이름을 사용할 수 있는 힌트
- 쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 힌트로서 힌트가 명시된 쿼리 블록에 대해서만 영향을 미침
  - SELECT 키워드로 시작하는 서브 쿼리 영역을 쿼리 블록이라고 한다.
- 글로벌(쿼리 전체): 전체 쿼리에 대해 영향을 미치는 힌트
모든 인덱스 수준의 힌트는 반드시 테이블명이 선행돼야 한다.

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

MAX_EXECUTION_TIME

옵티마이저 힌트 중 유일하게 실행 계획에 영향을 미치지 않는 힌트
단순히 쿼리의 최대 실행 시간을 설정하는 힌트다.
밀리초 단위의 시간을 설정해서 초과하면 쿼리는 실패하게 된다.
- ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

SELECT /*+ MAX_EXECUTION_TIME(100) */ *
FROM employees
ORDER BY last_name LIMIT 1;

SET_VAR

옵티마이저 힌트뿐만 아니라 MySQL 서버의 시스템 변수들 또한 쿼리 실행 계획에 상당한 양향을 미친다.
SET_VAR 힌트를 통해 optimizer_switch 시스템 변수를 제어해야할 수도 있다.

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

실행 계획을 바꾸는 것뿐만 아니라 조인 버퍼나 소트 버퍼 크기를 일시적으로 증가시켜 쿼리 성능을 향상시킬 수 있다.

SEMIJOIN & NO_SEMIJOIN

SEMIJOIN 힌트는 세미 조인 최적화의 어떤 세부 전략을 사용할지를 제어할 수 있게 해준다.
- Table Pull-out 최적화는 사용 가능하다면 항상 더 나은 성능을 보장하기 때문에 힌트를 사용할 필요도 없고 별도의 힌트도 없다.
- 다른 최적화 전략들은 상황에 따라 다른 전략으로 우회하는 것이 더 나은 경우가 많기에 NO_SEMIJOIN 힌트도 제공되는 것이다.

최적화 전략	힌트
Duplicate Weed-out	SEMIJOIN(DUPSWEEDOUT)
First Match	SEMIJOIN(FIRSTMATCH)
Loose Scan	SEMIJOIN(LOSSESCAN)
Materialization	SEMIJOIN(MATERIALZATION)
Table Pull-out	없음

세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브 쿼리에 명시해야 한다.

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

SUBQUERY

서브 쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법이다.

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

주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인 최적화에는 사용될 수 없다.
서브 쿼리 최적화 전략은 사용할 기회가 많지 않다고 한다.

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

MySQL 8.0.20 버전 이후부터 BNL 힌트를 사용하면 해시 조인을 사용하도록 유도하는 힌트로 용도가 변경되었다.
HASHJOIN과 NO_HASHJOIN 힌트는 8.0.18 버전에서만 유효하며 그 이후 버전에서는 효력이 없다.
8.0.20 버전 이후부터는 해시 조인을 유도하거나 사용하지 않게 하고자 한다면 BNL과 NO_BNL 힌트를 사용해야 한다.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

STRAIGHT_JOIN 힌트를 사용하면 FROM 절에 명시된 테이블 순서대로 조인 순서를 조정할 수 있지만 일부는 조인 순서를 강제하고 일부는 옵티마이저에게 순서를 결정하게 맡기는 것이 불가능했다.
위 단점을 보완하기 위해 다음의 4가지 힌트를 제공한다.
- JOIN_FIXED_ORDER: STRAIGHT_JOIN 힌트와 동일한 기능
- JOIN_ORDER: 힌트에 명시된 테이블 순서대로 조인을 실행하는 힌트
- JOIN_PREFIX: 조인에서 드라이빙 테이블만 강제하는 힌트
- JOIN_SUFFIX: 조인에서 가장 마지막에 조인돼야 할 드리븐 테이블만 강제하는 힌트

-- // FROM 절에 명시된 순서대로 조인
SELECT /*+ JOIN_FIXED_ORDER() */ *
FROM employees e
	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
	INNER JOIN departments d ON d.dept_no=de.dept_no;

-- // 일부 테이블에 대해서만 조인 순서를 나열
SELECT /*+ JOIN_ORDER(d, de) */ *
FROM employees e
	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
	INNER JOIN departments d ON d.dept_no=de.dept_no;

-- // 조인의 드라이빙 테이블에 대해서만 조인 순서 나열
SELECT /*+ JOIN_PREFIX(e, de) */ *
FROM employees e
	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
	INNER JOIN departments d ON d.dept_no=de.dept_no;

-- // 조인의 드리븐 테이블에 대해서만 조인 순서 나열
SELECT /*+ JOIN_SIFFIX(de, e) */ *
FROM employees e
	INNER JOIN dept_emp de ON de.emp_no=e.emp_no
	INNER JOIN departments d ON d.dept_no=de.dept_no;

MERGE & NO_MERGE

FROM 절에 사용된 서브 쿼리는 내부 임시 테이블(파생 테이블)을 생성할 수도 있는데 이는 불필요한 자원 소모를 유발한다.
MySQL 5.7과 8.0 버전에서는 가능하면 임시 테이블을 사용하지 않도록 서브 쿼리를 외부 쿼리와 병합하는 최적화를 도입했다.
하지만 상황에 따라 병합하는 것이 나을 수도 있고 임시 테이블을 만드는 것이 나울 수도 있다.
MERGE 또는 NO_MERGE 힌트를 통해 이를 제어할 수 있다.
- MERGE: 외부 쿼리와 통합
- NO_MERGE: 임시 테이블 생성

SELECT /*+ MERGE(sub) */ *
FROM (SELECT * FROM employees WHERE first_name='Matt') sub
LIMIT 10;

INDEX_MERGE & NO_INDEX_MERGE

하나의 인덱스만으로 검색 대상 범위를 좁힐 수 없을 때 여러 인덱스로 검색하여 교집합 또는 합집합 결과를 반환하는데 이를 인덱스 머지라고 한다.
인덱스 머지 실행 계획 사용 여부를 제어하고자 할 때 INDEX_MERGE와 NO_INDEX_MERGE 힌트를 사용할 수 있다.

SELECT /*+ NO_INDEX_MERGE(employees PRIMARY) */ *
FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

NO_ICP

인덱스 컨디션 푸시다운 최적화는 거의 항상 성능 향상에 도움이 되기에 옵티마이저는 인덱스 컨디션 푸시다운을 사용하는 방향으로 실행 계획을 수립한다.
- 그래서 ICP 힌트는 제공되지 않는다.
그런데 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용이 잘못 계산된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수도 있다.
- A 인덱스(인덱스 컨디션 푸시다운 가능)와 B 인덱스(인덱스 컨디션 푸시다운 불가능)가 있을 때 옵티마이저는 A 인덱스를 거의 항상 선택할 것이다.
- 하지만 B 인덱스가 더 효율적일 수도 있는데 옵티마이저는 A를 선택하게 된다.
NO_ICP 힌트를 통해 옵티마이저가 좀 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있다.

SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵 스캔은 인덱스의 선행 칼럼에 대한 조건이 없어도 인덱스를 사용할 수 있게 해주는 최적화 기능이다.
하지만 누락된 선행 칼럼의 유니크 값의 개수가 많아진다면 성능은 오히려 떨어진다.
성능이 떨어지는 경우에도 옵티마이저가 스캡 스캔을 선택하려고 한다면 NO_SKIP_SCAN을 사용할 수 있다.

SELECT /*+ NO_SKIP_SCAN(employees ix_gender_birthdate) */ gender, birth_date
FROM employees
WHERE birth_date>='1965-02-01';

INDEX & NO_INDEX

INDEX와 NO_INDEX 옵티마이저 힌트는 예전에 사용되던 인덱스 힌트를 대체하는 용도로 제공된다.
- INDEX, GROUP_INDEX, ORDER_INDEX, NO_ONDEX, NO_GROUP_INDEX, NO_ORDER_INDEX
옵티마이저 힌트는 인덱스 힌트와는 다르게 힌트에 테이블 명과 인덱스 이름을 함께 명시해야 한다.

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