참조

• https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

---

종류

Global

MAX_EXECUTION_TIME

MAX_EXECUTION_TIME(10)
최대 실행 시간

# ms 단위
SELECT /*+ MAX_EXECUTION_TIME(500) */ *
FROM temp

RESOURCE_GROUP

SET_VAR

SET_VAR(optimizer_switch='index_merge=on')
시스템 변수 제어

SELECT /*+ SET_VAR(optimizer_switch='index_merge=on') */ *
FROM temp

Index

INDEX, NO_INDEX

INDEX(table_A idx_a)
USE|IGNORE INDEX 대체

GROUP_INDEX, NO_GROUP_INDEX

USE|IGNORE INDEX FOR GROUP BY 대체

ORDER_INDEX, NO_ORDER_INDEX

USE|IGNORE INDEX FOR ORDER BY 대체

JOIN_INDEX, NO_JOIN_INDEX

WHERE 절에서 사용/미사용할 인덱스

SELECT /*+ INDEX(t PRIMARY) */ emp_no
FROM employees t

SELECT /*+ NO_INDEX(t PRIMARY) */ emp_no
FROM employees t

Query block

JOIN_FIXED_ORDER

STRAIGHT_JOIN 동일

JOIN_ORDER

JOIN_ORDER(table_a, table_b, table_c)
조인 순서 선택

JOIN_PREFIX

JOIN_PREFIX(table_a, table_b)
시작(드라이빙) 테이블

JOIN_SUFFIX

JOIN_SUFFIX(table_c, table_d)
종료(드리븐) 테이블

SELECT /*+ JOIN_FIXED_ORDER() */ *
FROM salaries s
     INNER JOIN employees e
                ON s.emp_no = e.emp_no

SELECT /*+ JOIN_ORDER(e, s) */ *
FROM salaries s
     INNER JOIN employees e
                ON s.emp_no = e.emp_no
                
SELECT /*+ JOIN_PREFIX(e, s) */ *
FROM salaries s
     INNER JOIN employees e
                ON s.emp_no = e.emp_no
     INNER JOIN employee_name en
                ON en.emp_no = e.emp_no

QB_NAME

서브쿼리 이름 설정

SUBQUERY

SUBQUERY(DUPSWEEDOUT|FIRSTMATCH|LOOSESCAN|MATERIALIZATION)
서브쿼리에 직접 사용함

SEMIJOIN, NO_SEMIJOIN

QB_NAME을 이용해서 SUBQUERY에 힌트 사용함
SEMIJOIN(@subq1, DUPSWEEDOUT|FIRSTMATCH|LOOSESCAN|MATERIALIZATION)

SELECT /*+
         SEMIJOIN(@dept MATERIALIZATION)
         JOIN_SUFFIX(e)
     */e.*
FROM employees e
WHERE e.emp_no IN (
    SELECT /*+
             QB_NAME(dept)
         */e.emp_no
    FROM departments d
         INNER JOIN dept_emp e
                    ON d.dept_no = e.dept_no
    WHERE dept_name = 'Sales'
)

Query block + Table

BKA, NO_BKA

Batched Key Access Join 사용 여부

SELECT /*+
         SET_VAR(optimizer_switch='batched_key_access=on')
         JOIN_ORDER(e, de)
         BKA(e, de)
         */ *
FROM dept_emp de
     INNER JOIN employees e
                ON de.emp_no = e.emp_no

BNL, NO_BNL

Block Nested-Loop Join
8.0.20부터는 hash Join 사용 여부

SELECT /*+ NO_BNL() */ *
FROM employees e
     INNER JOIN employees e2
                ON e.birth_date = e2.birth_date

DERIVED_CONDITION_PUSHDOWN,NO_DERIVED_CONDITION_PUSHDOWN

derived table condition pushdown

HASH_JOIN, NO_HASH_JOIN

Hash join 사용 여부
8.0.19부터 BNL로 사용함

Table + Index

INDEX_MERGE, NO_INDEX_MERGE

index merge 사용여부

SELECT /*+ INDEX_MERGE(e) */ e.*
FROM employees e
WHERE e.first_name = 'Aamer'
  AND e.hire_date = '1996-02-15'

MERGE, NO_MERGE

서브쿼리, 뷰 병합 활성화 여부

MRR, NO_MRR

Multi Range Read 사용여부

SELECT /*+ MRR(e) */ e.*
FROM employees e
WHERE e.first_name BETWEEN 'Aamer' AND 'Abdelaziz'

NO_ICP

Index condition pushdown 사용여부

NO_RANGE_OPTIMIZATION

Index range scan 사용여부

SKIP_SCAN, NO_SKIP_SCAN

Index skip scan 사용여부