MySQL 8.0 옵티마이저 힌트에 대하여

MySQL 8.0 부터 추가된 옵티마이저 힌트에 대하여 알아보자.
먼저 옵티마이저 힌트를 어떻게 사용하는 지 살펴보도록 하자.

옵티마이저 힌트 구문
옵티마이저 힌트는 /*+...*/ 를 statement 안에 기술한다.
SELECT, UPDATE, DELETE 등의 DML 키워드 뒤에 힌트를 기술하면 서버가 인식하여 처리하게 된다.

mysql> SELECT /*+ hint */ ...
mysql> UPDATE /*+ hint */ ...

지정한 힌트가 유효한 지 확인하려면, EXPLAIN 후에 SHOW WARNINGS 를 실행하면 된다.
예를 들어, 아래는 NO_RANGE_OPTIMIZATION 힌트를 추가하여 실시한 결과이다. 이 힌트는 지정한 테이블 및 인덱스를 사용한 레인지 스캔을 하지 않도록 제어하였다.
힌트가 유효하면, SHOW WARNINGS 에서 사용된 힌트를 확인할 수 있다.

mysql> EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t PRIMARY) */ * FROM t WHERE id BETWEEN 1 AND 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 39528 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ NO_RANGE_OPTIMIZATION(`t`@`select#1` `PRIMARY`) */ `d`.`t`.`id` AS `id`,`d`.`t`.`k` AS `k`,`d`.`t`.`c` AS `c`,`d`.`t`.`pad` AS `pad` from `d`.`t` where (`d`.`t`.`id` between 1 and 10) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

무효한 힌트를 지정한 경우는 아래와 같이 표시된다.

mysql> EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t idx_t) */ * FROM t WHERE id BETWEEN 1 AND 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3128 | Unresolved name `t`@`select#1` `idx_t` for NO_RANGE_OPTIMIZATION hint                                                                                      |
| Note    | 1003 | /* select#1 */ select `d`.`t`.`id` AS `id`,`d`.`t`.`k` AS `k`,`d`.`t`.`c` AS `c`,`d`.`t`.`pad` AS `pad` from `d`.`t` where (`d`.`t`.`id` between 1 and 10) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQL 8.0 옵티마이저 힌트
이제부터 MySQL 8.0 에서 새롭게 추가된 옵티마이저 힌트에 대하여 알아보도록 하자.

JOIN 힌트
조인의 순서를 컨트롤 할 수 있게 되었다.

• JOIN_FIXED_ORDER
  : 강제적으로 조인하는 순서를 FROM 절의 지정 순서대로 실시하도록 한다
• JOIN_ORDER
  : 지정한 순서대로 표를 조인하도록 옵티마이저에게 알려준다
• JOIN_PREFIX
  : 지정한 테이블을 먼저 조인하도록 옵티마이저에게 알려준다
• JOIN_SUFFIX
  : 지정한 테이블을 나중에 조인하도록 옵티마이저에게 알려준다

Join 힌트를 이용하는 경우는, 조인하는 SELECT 뒤에 기술해야 한다.
예를 들어, t1, t2 테이블에 대하여 JOIN_PREFIX 를 이용하는 경우는 아래와 같이 기술해야 한다.

SELECT /*+ JOIN_PREFIX(t1) */ t2.id FROM t2 LEFT JOIN t1 on t1.id = t2.id;

기존의 STRAIGHT_JOIN 은 강제적으로 조인하는 순서를 지정했지만 JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX 은 강제로 하지않고, 옵티마이저가 판단하여 cost가 높을 경우 선택되지 않을 가능성이 있다.

SET_VAR 힌트
statement 안에 시스템 변수의 세션 값을 일시적으로 설정할 수 있게 되었다.
구문은 SET_VAR(var_name = value) 와 같이 기술한다. 예를 들어, index_condition_pushdown 을 비활성화 하는 경우는 아래와 같이 기술해야 한다.

mysql> SELECT /*+ SET_VAR(optimizer_switch='index_condition_pushdown = off') */ ...

INDEX_MERGE, NO_INDEX_MERGE 힌트
지정된 테이블 또는 인덱스의 인덱스 머지를 활성화 또는 비활성화 할 수 있게 되었다.
예를 들어, t1 테이블의 i_a, i_b, i_c 인덱스를 머지하는 경우는 아래와 같이 기술해야 한다.

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

HASH_JOIN, NO_HASH_JOIN 힌트
MySQL 8.0.18 에서 추가된 HASH_JOIN 을 활성화 또는 비활성화 할 수 있게 되었다.
이 힌트는 MySQL 8.0.18 에서만 유효하고, MySQL 8.0.19 부터는 효과가 없기 때문에 주의가 필요하다.
참고로 MySQL 8.0.19 에서 HASH_JOIN 을 비활성화 시키려면 NO_BNL 힌트로 비활성화 되는 걸 확인할 수 있었다.

mysql> EXPLAIN FORMAT=tree SELECT /*+ NO_HASH_JOIN(t,t2) */ * FROM t JOIN t2 USING(c);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t.c = t2.c)  (cost=4105.41 rows=3953)
    -> Table scan on t  (cost=547.54 rows=39528)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=tree SELECT /*+ NO_BNL(t,t2) */ * FROM t JOIN t2 USING(c);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=4105.40 rows=3953)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Filter: (t.c = t2.c)  (cost=547.53 rows=3953)
        -> Table scan on t  (cost=547.53 rows=39528)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

이외 에도 MERGE 힌트, SKIP_SCAN 힌트 등이 추가 되었으므로, 상세한 내용은 공식 사이트를 참고하면 좋을 거 같다.
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html