쿼리 힌트

• MySQL 서버가 부족한 실행 계획을 수립해야 할 경우, 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하다.
• 일반적인 RDBMS에서는 이런 목적으로 힌트가 제공된다.
• MySQL 서버에서 사용 가능한 쿼리 힌트는 다음 두 가지로 구분할 수 있다.
  • 인덱스 힌트
  • 옵타마이저 힌트
• 이 이외에도, 여기에 포함되지 않은 STRAIGHT_JOIN 과 같은 힌트들도 있다.
  • 이 책에서는 옵티마이저 힌트가 아닌 것들은 모두 모아 인덱스 힌트 절로 분류해서 살펴본다.

인덱스 힌트

• 예전 버전의 MySQL 서버에서 사용되어 오던 힌트를 의미한다.
  • 옵타마이저 힌트가 도입되기 전에 사용되던 기능들이다.
  • USE INDEX , STRAIGHT_JOIN 등
• SQL의 문법에 맞게 사용해야 하기 때문에, 사용하게 되면 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있다.
  • 옵티마이저 힌트와 같이 주석 형태로 표기할 수도 있지만, 일반적으로 주석 형태보다는 SQL의 일부 형태로 자주 사용된다.
  • 따라서 ANSI-SQL 표준을 준수하는 옵티마이저 힌트를 사용하는 것을 권고한다.
• 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 사용할 수 있다.

인덱스 힌트 종류

인덱스 힌트의 종류는 다음과 같다.

• STRAIGHT_JOIN
• USE INDEX
• FORCE INDEX
• IGNORE INDEX
• SQL_CALC_FOUND_ROWS

옵티마이저 힌트

• MySQL 5.6 버전부터 새롭게 추가되기 시작한 힌트들을 지칭한다.
• MySQL 서버를 제외한 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다.
• MySQL 8.0 버전에서 사용 가능한 힌트는 종류가 매우 다양하며, 옵티마이저 힌트가 미치는 영향 범위도 매우 다양하다.

옵티마이저 힌트 종류

옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나눌 수 있다. 이 구분으로 인해 힌트의 사용 위치가 달라지는 것은 아니다.

• 인덱스
  • 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
  • 힌트에 인덱스 이름이 명시될 수 있는 경우, 인덱스 수준의 힌트로 구분한다.
• 테이블
  • 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
  • 힌트에 테이블 이름까지만 명시될 수 있는 경우, 테이블 수준의 힌트로 구분한다.
• 쿼리 블록
  • 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트
  • 힌트가 명시된 쿼리 블록에 대해서만 영향을 미친다.
• 글로벌(쿼리 전체)
  • 전체 쿼리에 대해서 영향을 미치는 옵티마이저 힌트

인덱스 수준과 테이블 수준의 힌트

• 테이블과 인덱스의 이름을 모두 명시할 수도 있지만 인덱스의 이름을 명시하지 않고 테이블 이름만 명시하는 경우, 인덱스와 테이블 수준의 힌트가 된다.
• 모든 인덱스 수준의 힌트는 반드시 테이블명이 선행되어야 한다.
  • SELECT /*+ INDEX(테이블명 인덱스명)*/

쿼리 블록

• 하나의 SQL 문장에서 SELECT 키워드는 여러 번 사용될 때, 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록이라고 한다.
• 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서도 사용될 수 있지만 외부 쿼리 블록에서 사용할 수도 있다.
  • QB_NAME() 힌트를 이용해 해당 쿼리 블록에 이름을 부여하면 외부 쿼리 블록에서 사용할 수 있다.

옵티마이저 힌트 표

힌트 이름	설명	영향 범위
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 절의 처리를 위한 인덱스 사용 여부 제어	인덱스

Reference

참고 서적

📔 Real MySQL 8.0