MySQL에서 옵티마이저가 최적화 되어 좋은 성능을 보이지만 간혹 우리가 의도한대로 동작하지 않는 경우가 있다. 이러한 경우를 위해 힌트를 제공하여 원하는 방향으로 쿼리가 실행되게 할 수 있다. 힌트의 종류는 인덱스 힌트와 옵티마이저 힌트가 있고 아래서 자세히 알아보자.

인덱스 힌트

인덱스 힌트는 옵티마이저 힌트가 보다 먼저 도입된 힌트로 특정 문법을 사용하기 때문에 ANSI-SQL 표준 문법에서 어긋나 다른 DBMS에서 사용하지 못한다는 점과 조회 또는 업데이트에서만 사용할 수 있다는 단점이 있다. 따라서 옵티마이저 힌트 방식을 사용하도록 하자.

STRAIGHT_JOIN

SELECT문에서 사용할 수 있는 힌트로 FROM절에 명시된 테이블 순서대로 조인 순서를 고정하는 방식이며 다음과 같은 방식으로 사용할 수 있다.

SELECT STRAIGHT_JOIN * 
FROM table1, table2, taable3
where ...

해당 힌트가 없는 일반적인 조회 쿼리에서는 레코드 수가 적은 테이블 순으로 조인되는데 다음과 같은 경우에 해당 힌트를 사용하는 것을 고려해봐야한다.

• 임시 테이블(인라인 뷰 또는 파생 테이블)을 사용하는데 드라이빙 테이블로 선정되지 않는 경우
• 일반 테이블끼리 조인일 경우 조인 컬럼이 인덱스가 아닌 테이블이 드라이빙 테이블로 선정되지 않는 경우
• 임시 테이블끼리 조인일 경우 레코드 수가 적은 테이블이 드라이빙 테이블로 선정되지 않는 경우

여기서 레코드 수는 테이블 전체 레코드가 아닌 조인되는 컬럼 조건에 해당하는 레코드 수 이다.

인덱스 힌트

테이블에서 특정 인덱스가 사용 또는 사용되지 안도록 옵티마이저에게 힌트를 주는 방식으로 테이블 뒤에 힌트를 작성한다. 아래와 같은 키워드들이 있으며 키워드 뒤에 ('인덱스명') 과 같은 방식으로 사용하면 된다. 프라이머리 키는 인덱스명으로 PRIMARY를 사용한다.

USE INDEX
옵티마이저에게 특정 인덱스를 사용하도록 권장하는 힌트이며 무조건 선택되는 것은 아니다.
FORCE INDEX
USE INDEX와 같은 동작을 하며 보다 더 강한 힌트라고 생각하면 되는데 USE INDEX로 선택되지 않는다면 FORXCE INDEX로도 안되는 경우가 대부분이여서 USE INDEX키워드를 사용하자.
IGNORE INDEX
옵티마이저에게 특정 인덱스를 사용하지 않도록 권장하는 힌트로 풀 테이블 스캔을 유도할 때 사용하기도 한다.

또한 인덱스 힌트는 용도에 따라서도 USE INDEX FOR 용도와 같이 사용할 수 있는데 용도까지는 크게 사용할 필요없으나 JOIN, ORDER BY, GROUP BY가 있다는 정도만 알아두고 JOIN은 실제 조인과 WHERE절인 조건절도 포함되는 옵션이다.

여기서 중요한 점은 강제로 실행계획을 정하는 것은 좋은 최적화가 아니다. 최적의 실행계획은 데이터의 데이터의 성격에 따라 계속해서 바뀜으로 옵티마이저가 통계데이터와 같은 부분을 활용한 실행계획이 보다 효율적인 경우가 많다.

만약 성능이 나쁜 쿼리를 의도적으로 좋은 최적화를 하고 싶다면 그 쿼리를 없애거나 튜닝할 필요가 없도록 데이터량을 최소화하거나 DB 구조를 단순화해서 튜닝이 필요없도록 하는것이다.

옵티마이저 힌트

옵티마이저 힌트는 사용 위치는 같으며 힌트 대상으로 명시할 수 있는 대상에 따라 영향 범위가 지정된다. /*+ 힌트 */ 와 같이 사용할 수 있다.

아래는 사용할 수 있는 힌트 대상이 되는 구분과 그에 대한 설명이다.

구분	설명
인덱스	특정 인덱스 이름으로 사용할 수 있는 힌트
테이블	특정 테이블 이름으로 사용할 수 있는 힌트
쿼리 블록	특정 쿼리 블록에 사용할 수 있는 힌트 블록명이 아닌 힌트가 명시된 쿼리 블록에 대해서 영향을 미치는 힌트
글로벌(쿼리 전체)	전체 쿼리에 대해 영향을 미치는 힌트

아래는 힌트 종류와 사용할 수 있는 구분이다.

힌트 이름	설명	영향 범위
MAX_EXECUTION_TIME	쿼리의 실행 시간 제한	글로벌
RESOURCE_GROUP	쿼리 실행의 리소스 그룹 설정	글로벌
SET_VAR	쿼리 실행을 위한 시스템 변수 제어	글로벌
SUBQUERY	서비쿼리의 세미 조인 최적화	쿼리블록
[NO_]BKA	BKA 조인 사용 여부 제어	쿼리블록, 테이블
[NO_]BNL	블록 네스티드 루프 조인, 해시 조인 사용여부 제어	쿼리블록, 테이블
[NO_]DERIVED_CONDITION_PUSHDOWN	외부 쿼리를 서브쿼리로 옮기는 최적화 사용 여부 제어	쿼리블록, 테이블
[NO_]HASH_JOIN	8.0.18 버전만 해시 조인 사용여부 제어	쿼리블록, 테이블
JOIN_FIXED_ORDER	FROM절에 명시된 테이블 순서대로 조인 실행	쿼리블록
JOIN_ORDER	힌트에 명시된 테이블 순서대로 조인 실행	쿼리블록
JOIN_PREFIX	힌트에 명시된 테이블을 조인의 드라이빙 테이블로 조인 실행	쿼리블록
JOIN_SUFFIX	힌트에 명시된 테이블을 조인의 드리븐 테이블로 조인 실행	쿼리블록
QB_NAME	쿼리 블록의 이름 설정을 위한 힌트	쿼리블록
[NO_]SEMIJOIN	서브 쿼리의 세미 조인 최적화 전략 제어	쿼리블록
[NO_]MERGE	FROM 절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는 최적화 사용 여부 제어	테이블
[NO_]INDEX_MERGE	인덱스 병합 실행계획 사용 여부 제어	테이블, 인덱스
[NO_]MRR	MRR(Multi Range Read) 사용 여부 제어	테이블, 인덱스
NO_ICP	인덱스 컨디션 푸시다운 최적화 사용 여부 제어	테이블, 인덱스
NO_RANGE_OPTIMIZATION	인덱스 레인지 액세스를 비활성화	테이블, 인덱스
[NO_]SKIP_SCAN	인덱스 스킵 스캔 사용 여부 제어	테이블, 인덱스
[NO_]INDEX	GROUP BY, ORDER BY, WHERE절의 처리를 위한 인덱스 사용 여부 제어	인덱스
[NO_]GROUP_INDEX	GROUP BY 절의 처리를 위한 인덱스 사용여부 제어	인덱스
[NO_]JOIN_INDEX	WHERE 절의 처리를 위한 인덱스 사용여부 제어	인덱스
[NO_]ORDER_INDEX	ORDER BY 절의 처리를 위한 인덱스 사용여부 제어	인덱스

아래에서는 사용할 수 있을거 같은 힌트들만 사용방법에 대해서 알아보도록 하자.

SET_VAR

시스템 변수들을 설정할 수 있는 방법으로 조인 버퍼의 크기 설정과 같은 방법을 통해 대용량 쿼리에 성능을 향상 시키는 용도로 사용할 수 있다. 다만 모든 시스템 변수를 힌트로 조정할 수 없음을 주의하자.

explain
select /*+ SET_VAR(optimizer_switch='index_merge_intersection=off' */ t.*
from employees t
where 1 = 1
and t.first_name = 'Georgi'
and t.emp_no between 10000 and 20000;