본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

---

9.4 쿼리 힌트

9.4.1 인덱스 힌트

인덱스 힌트는 옵티마이저 힌드가 도입되기 전에 사용되던 기능들로, ANSI-SQL 표준 문법을 준수하지 못하는 단점이 있기 때문에 가능하다면 옵티마이저 힌트를 사용하자.

STRAIGHT_JOIN

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드로 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

STRAIGHT_JOIN 키워드의 사용 예시는 아래와 같으며, 두 쿼리는 힌트의 표기법만 조금 다르게 했을 뿐 동일한 쿼리다. SELECT 키워드 바로 뒤에 사용되었다는 것을 주의하자.

mysql> SELECT STRAIGHT JOIN
			e.first_name, e.last_name, d.dept_name 
        FROM employees e, dept_emp de, departments d
        WHERE e.emp_no=de.emp_no
        	AND d.dept_no=de.dept_no;
            
mysql> SELECT /*! STRAIGHT JOIN */
			e.first_name, e.last_name, d.dept_name 
        FROM employees e, dept_emp de, departments d
        WHERE e.emp_no=de.emp_no
        	AND d.dept_no=de.dept_no;

USE INDEX / FORCE INDEX / IGNORE INDEX

대체로 MySQL 옵티마이저는 인덱스를 잘 선택하지만, 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개가 존재하는 경우 실수를 할 수 있어, 이런 경우 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.

인덱스 힌트는 크게 다음과 같이 3종류가 있으며, 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어 사용한다.

• USE INDEX
  MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트

• FORCE INDEX
USE INDEX와 동일하지만 옵티마이저에게 미치는 영향이 더 강한 힌트

• IGNORE INDEX
  특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트

최적의 실행 계획은 데이터의 성격에 따라 시시각각 변하므로, 현재의 좋은 계획이 나중에는 달라질 가능성이 있다. 따라서 가능하다면 그때그때 옵티마이저가 당시 통계 정보를 가지고 선택하게 하는 것이 가장 좋다.

SQL_CALC_FOUND_ROWS

MySQL의 LIMIT를 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다.

하지만 SQL_CALC_FOUND_ROW 힌트가 포함된 쿼리의 경우 LIMIT을 만족하는 수만큼 레코드를 찾아도 끝까지 검색을 수행하며, 사용자는 LIMIT에 제한된 수만큼만 결과 레코드를 반환받는다.

웹 프로그램의 페이징 기능에 적용하기 위해 이를 검토하거나 사용하는 경우가 존재하지만, SQL_CALC_FOUND_ROWS는 조건을 만족하는 레코드의 수만큼 랜덤 I/O가 발생하게 되어 굉장히 느려지게 된다. 따라서 해당 힌트를 사용하지 않고 레코드 카운트용 쿼리와 데이터를 조회하는 쿼리를 분리하는 것이 보통 더 효율적이다.

9.4.2 옵티마이저 힌트

옵티마이저 힌트 종류

옵티마이저 힌트는 영향 범위에 따라 4개의 그룹으로 나누어 볼 수 있다.

• 인덱스
  특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트

• 테이블
  특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트

• 쿼리 블록
  특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트

• 글로벌(쿼리 전체)
  전체 쿼리에 대해서 영향을 미치는 힌트

하나의 SQL 문장에서 SELECT 키워드는 여러 번 사용될 수 있는데, 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록이라고 한다. 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내부와 외부 모두 사용될 수 있다.

특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 QB_NAME() 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다. 다음은 특정 쿼리 블록(서브쿼리)에 대해 "subq1"이라는 이름을 부여하고, 그 쿼리 블록을 힌트에 사용하는 예제다.

mysql> EXPLAIN
		SELECT /*+ JOIN_ORDER(e, s@subq1) */
        	COUNT(*)
        FROM employees e
        WHERE e.first_name='Matt'
        	AND e.emp_no IN (SELECT /* QB_NAME(subq1) */ s.emp_no
            					FROM salaries s
                                WHERE s.salary BETWEEN 50000 AND 50500);

MAX_EXECUTION_TIME

옵티마이저 힌트 중 유일하게 쿼리의 실행 계획에 영향을 미치지 않는 힌트로, 단순히 쿼리의 최대 실행 시간을 설정하는 힌트다.

SET_VAR

SET_VAT 힌트는 시스템 변수를 제어할 수 있어, 실행 계획을 바꾸는 용도 및 조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 쿼리의 성능을 향상시키는 용도로도 사용할 수 있다.

SEMIJOIN & NO_SEMIJOIN

SEMIJOIN 힌트는 어떤 세부 전략을 사용할지 제어하는 데 사용할 수 있다.

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

Table Pull-out 최적화 전략은 항상 더 나은 성능을 보장하기 때문에 별도로 힌트를 사용할 수 없다.

SUBQUERY

서크붜리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법으로, 다음 2가지 형태로 최적화할 수 있다.

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

서브쿼리 최적화 힌트는 주로 안티 세미 조인 최적화에 사용되며, 서브쿼리에 힌트를 사용하거나 서브쿼리에 쿼리 블록 이름을 지정해서 외부 쿼리 블록에서 최적화 방법을 명시하면 된다.

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인이 사용되지 않지만 BNL 힌트와 NO_BNL 힌트를 사용해 해시 조인을 사용하도록 유도하는 용도로 사용한다.

HASHJOIN과 NO_HASHJOIN 힌트는 MySQL 8.0.18 버전에서만 유효하며, 그 이후 버전에서는 효력이 없다.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

MySQL 서버에서 조인 순서를 결정하기 위해 사용하던 STRAIGHT_JOIN 힌트의 단점을 보완하기 위해 다음과 같은 4개의 힌트를 제공한다.

• JOIN_FIXED_ORDER
STRAIGHT_JOIN 힌트와 동일하게 FROM 절의 테이블 순서대로 조인을 실행하게 하는 힌트

• JOIN_ORDER
FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트

• JOIN_PREFIX
  조인에서 드라이빙 테이블만 강제하는 힌트

• JOIN_SUFFIX
  조인에서 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제하는 힌트

MERGE & NO_MERGE

MySQL 옵티마이저가 내부 쿼리를 외부 쿼리와 병합하는 것과 임시 테이블을 생성하는 것 중 최적의 방법을 선택하지 못할 때 MERGE 또는 NO_MERGE 옵티마이저 힌트를 사용하면 된다.

INDEX_MERGE & NO_INDEX_MERGE

인덱스 머지 실행 계획은 성능 향상에 도움이 되지만 항상 그렇지는 않으므로, 이를 제어하고자 할 때 INDEX_MERGE와 NO_INDEX_MERGE 옵티마이저 힌트를 이용할 수 있다.

NO_ICP

인덱스 컨디션 푸시다운 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로 MySQL 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립하므로 ICP 힌트는 제공되지 않는다.

그러나 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수 있으므로, 조금 더 유연하고 정확하게 실행 계획을 선택하게 하고 싶은 경우 NO_ICP 옵티마이저 힌트를 사용해 인덱스 컨디션 푸시다운 최적화를 비활성화할 수 있다.

SKIP_SCAN & NO_SKIP_SCAN

MySQL 옵티마이저가 유니크한 값의 개수를 제대로 분석하지 못하거나 잘못된 경로로 인해 비효율적인 스킵 스캔을 선택하는 경우가 발생한다. 따라서 인덱스 스킵 스캔 여부를 SKIP_SCAN 및 NO_SKIP_SCAN 옵티마이저 힌트를 이용해 제어할 수 있다.

INDEX & NO_INDEX

INDEX와 NO_INDEX 옵티마이저 힌트는 이전 인덱스 힌트를 대체하는 용도로, 다음과 같이 제공된다.

인덱스 힌트	옵티마이저 힌트
USE INDEX	INDEX
USE INDEX FOR GROUP BY	GROUP_INDEX
USE INDEX FOR ORDER BY	ORDER_INDEX
IGNORE INDEX	NO_INDEX
IGNORE INDEX FOR GROUP BY	NO_GROUP_INDEX
IGNORE INDEX FOR ORDER BY	NO_ORDER_INDEX