MySQL에서 쿼리 힌트 사용하기

티스토리 뷰

공부/MySQL

MySQL에서 쿼리 힌트 사용하기

JStack 2024. 7. 19. 23:30

옵티마이저는 비즈니스를 100% 이해하지 못하기 때문에 개발자나 DBA가 옵티마이저에게 더 좋은 계획을 알려줘야하는 경우가 있다.

이 때 다양한 힌트를 제공함으로써 보다 나은 계획을 세울 수 있도록 하는데, 힌트를 아래와 같이 두 가지로 나뉜다.

옵티마이저 힌트 : MySQL 5.6 버전부터 새롭게 추가되기 시작한 힌트
인덱스 힌트 : 그 외의 힌트

인덱스 힌트

MySQL 서버에 힌트가 도입되기 전부터 사용하던 힌트로 문법에 맞도록 작성해야만 한다.

기본적으로 ANSI-SQL 표준은 주석으로 힌트를 작성하는 것인데, 인덱스 힌트는 주석으로 작성되지 않는다.

또한 인덱스 힌트에서 제공되는 기능이 옵티마이저 힌트에서도 대부분 제공되므로 왠만하면 주석으로 작성되는 옵티마이저 힌트를 사용하는 것이 좋다.

STRAIGHT_JOIN

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드인데, 여러 테이블이 조인될 때 순서를 고정하는 역할을 한다.

mysql> EXPLAIN SELECT *
               FROM employees e, dept_emp de, departments d
               WHERE e.emp_no = de.emp_no
               AND d.dept_no = de.dept_no;

+----+-------------+-------+--------+-------------+-------+-------------+
| id | select_type | table | type   | key         | row   | Extra       |
+----+-------------+-------+--------+-------------+-------+-------------+
|  1 | SIMPLE      | d     | index  | ux_deptname |     9 | Using index |
|  1 | SIMPLE      | de    | ref    | PRIMARY     | 41392 | NULL        |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY     |     1 | NULL        |
+----+-------------+-------+--------+-------------+-------+-------------+

위 실행 계획을 보면 departments 테이블을 드라이빙 테이블로 선택하고 dept_emp, employees 테이블을 순서대로 읽었다.

일반적으로는 조인 칼럼의 인덱스 여부와 레코드 수로 조인 순서가 결정되기 때문이다.

mysql> EXPLAIN SELECT STRAIGHT_JOIN *
               FROM employees e, dept_emp de, departments d
               WHERE e.emp_no = de.emp_no
               AND d.dept_no = de.dept_no;

mysql> EXPLAIN SELECT /*! STRAIGHT_JOIN */  *
               FROM employees e, dept_emp de, departments d
               WHERE e.emp_no = de.emp_no
               AND d.dept_no = de.dept_no;

+----+-------------+-------+--------+-------------------+--------+-------------+
| id | select_type | table | type   | key               | row    | Extra       |
+----+-------------+-------+--------+-------------------+--------+-------------+
|  1 | SIMPLE      | e     | ALL    | NULL              | 300473 | NULL        |
|  1 | SIMPLE      | de    | ref    | ix_empno_fromdate |  41392 | Using index |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY           |      1 | NULL        |
+----+-------------+-------+--------+-------------------+--------+-------------+

위와 같이 STRAIGHT_JOIN 힌트를 걸면 쿼리에 명시한 테이블 순서대로 조인을 수행하도록 유도할 수 있다.

일반적으로 아래와 같은 경우에만 조인 순서를 조정해주는 것이 좋다.

임시 테이블과 일반 테이블의 조인: 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋음
임시 테이블끼리 조인: 크기가 작은 테이블을 드라이빙으로 선정
일반 테이블끼리 조인: 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선정

USE INDEX / FORCE INDEX / IGNORE INDEX

옵티마이저는 3~4개 이상의 칼럼을 가지는 인덱스가 많은 경우 실수할 때가 있어, 이 경우 특정 인덱스를 사용하게 강제할 필요가 있다.

USE INDEX: 왠만하면 특정 지은 인덱스를 사용
FORCE INDEX: USE INDEX보다 더 강력하게 인덱스를 사용하게 지정
IGNORE INDEX: 특정 인덱스를 사용하지 않게 함

위 힌트에는 아래와 같이 용도를 같이 지정할 수 있다. (일반적으로 고려하지 않아도 된다)

USE INDEX FOR JOIN: 테이블간 조인, 테이블 내 조회 시 사용
USE INDEX FOR ORDER BY: 인덱스를 정렬 용도로 사용
USE INDEX FOR GROUP BY: 인덱스를 그루핑 용도로 사용

mysql> SELECT * FROM employees WHERE emp_no='10001';
mysql> SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no='10001';
mysql> SELECT * FROM employees USE INDEX(primary) WHERE emp_no='10001';

mysql> SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no='10001';
mysql> SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no='10001';

인덱스의 사용법이나 좋은 실행 계획을 판단하기 힘들다면 힌트 사용을 지양하는 것이 좋다.

일단 MySQL 옵티마이저도 왠만한 최적화를 잘 처리한다.

또한 최적 실행 계획은 데이터에 따라 바뀌므로(CBO) 오늘의 최적화가 내일의 성능저하일 수도 있다.

따라서 가장 훌륭한 최적화는 데이터 최소화, 모델을 단순화하여 간결한 쿼리를 작성하는 것이다. (어렵다)

SQL_CALC_FOUND_ROWS

MySQL의 LIMIT을 사용하는 경우 LIMIT에 명시된 만큼 레코드를 찾으면 조회를 멈춘다.

그러나 SQL_CALC_FOUND_ROWS 힌트를 사용하면 끝까지 조회하기 때문에 이 힌트는 사용하면 안된다!

mysql> SELECT SQL_CALC_FOUND_ROWS *
       FROM employees
       WHERE first_name='Georgi'
       LIMIT 0, 20;

mysql> SELECT FOUND_ROWS() AS total_record_count;

+--------------------+
| total_record_count |
+--------------------+
|                253 |
+--------------------+

위 쿼리는 인덱스를 사용하여 20건의 조회만 하면 되는데 253건의 조회를 하게 된다.

이 힌트는 성능 향상이 아닌 개발자의 편의를 위해 만들어진 힌트이다.

옵티마이저 힌트

옵티마이저 힌트 종류

옵티마이저 힌트는 크게 4가지로 구분할 수 있다.

인덱스
- 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
테이블
- 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
쿼리 블록
- 특정 쿼리 블록의 이름을 사용할 수 있는 옵티마이저 힌트
- 힌트가 명시된 쿼리 블록에 영향을 끼침
글로벌
- 전체 쿼리에 영향을 끼침

힌트 이름	설명	영향 범위
MAX_EXECUTION_TIME	쿼리 실행시간 제한	글로벌
RESOURCE_GROUP	쿼리 실행 리소스 그룹 설정	글로벌
SET_VAR	쿼리 실행을 위한 시스템 변수 제어	글로벌
SUBQUERY	서브쿼리 세미조인 최적화 전략 제어	쿼리 블록
BKA, NO_BKA	BKA 조인 사용 여부 제어	쿼리 블록, 테이블
BNL, NO_BNL	블록 네스티드 루프 조인 사용 여부 제어	쿼리 블록, 테이블
DERIVED_CONDITION_PUSHDOWN
NO_DERIVED_CONDITION_PUSHDOWN	외부 쿼리 조건을 서브쿼리로 옮기는
최적화 사용 여부 제어	쿼리 블록, 테이블
HASH_JOIN, NO_HASH_JOIN	해시 조인 사용 여부 제어	쿼리 블록, 테이블
JOIN_FIXED_ORDER	FROM 절에 명시된 순서로 조인 실행	쿼리 블록
JOIN_ORDER	힌트에 명시된 순서대로 조인 실행	쿼리 블록
JOIN_PREFIX	힌트에 명시된 테이블을 드라이빙 테이블로 조인 실행	쿼리 블록
JOIN_SUFFIX	힌트에 명시된 테이블을 드리븐 테이블로 조인 실행	쿼리 블록
QB_NAME	쿼리 블록 이름 설정	쿼리 블록
SEMIJOIN, NO_SEMIJOIN	서브쿼리 세미 조인 최적화 전략 제어	쿼리 블록
MERGE, NO_MERGE	FROM절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는
최적화 사용 여부 제어	테이블
INDEX_MERGE, NO_INDEX_MERGE	인덱스 병합 실행 계획 사용 여부 제어	테이블, 인덱스
MRR, NO_MRR	MRR(Multi-Range Read) 사용 여부 제어	테이블, 인덱스
NO_ICP	ICP(Index Condition Pushdown) 최적화 전략 사용 여부 제어	테이블, 인덱스
NO_RANGE_OPTIMIZATION	인덱스 레인지 엑세스를 비활성화	테이블, 인덱스
SKIP_SCAN, NO_SKIP_SCAN	인덱스 스킵 스캔 사용 여부 제어	테이블, 인덱스
INDEX, NO_INDEX	인덱스 사용 여부 제어	인덱스
GROUP_INDEX, NO_GROUP_INDEX	GROUP BY절 처리를 위한 인덱스 사용 여부 제어	인덱스
JOIN_INDEX, NO_JOIN_INDEX	WHERE절 처리를 위한 인덱스 사용 여부 제어	인덱스
ORDER_INDEX, NO_ORDER_INDEX	ORDER BY절 처리를 위한 인덱스 사용 여부 제어	인덱스

QB_NAME

하나의 SQL 문장에서 여러 개의 SELECT 절이 존재하는 경우 각 SELECT 키워드로 시작되는 영역을 쿼리 블록이라고 한다.

옵티마이저 힌트는 각 쿼리 블록에서 사용되기도 하지만 외부에서 사용되기도 한다.

이 경우에는 해당 쿼리 블록의 이름을 QB_NAME 힌트로 명시해주어야 한다.

mysql> 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

지정된 시간을 초과하면 쿼리가 실패하게 하는 힌트이다.

mysql> SELECT /*+ MAX_EXECUTION_TIME(100) */
       FROM employees
       ORDER BY last_name
       LIMIT 1;

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

SET_VAR

특정 시스템 변수를 일시적으로 변경하는 힌트이다.

예를 들어 조인 버퍼 사이즈를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 등의 작업을 수행할 수 있다.

mysql> SELECT /*+ SET_VAR(join_buffer_size=4294967168) */
       FROM employees
       WHERE first_name='Georgi'
       AND emp_no BETWEEN 10000 AND 20000;

SEMIJOIN & NO_SEMIJOIN

SEMIJOIN 최적화의 어떤 세부 전략을 사용할지 제어할 수 있는 힌트이다.

Duplicate Weed-out : SEMIJOIN(DUPSWEEDOUT)
First Match : SEMIJOIN(FIRSTMATCH)
Loose Scan : SEMIJOIN(LOOSESCAN)
Materialization : SEMIJOIN(MATERIALIZATION)
Table Pull-out : 없음

mysql> EXPLAIN SELECT *
               FROM departments d
               WHERE d.dept_no IN (SELECT de.dept_no
                                   FROM dept_emp de);

+----+--------------+-------+--------+-------------+----------------------------+
| id | select_type  | table | type   | key         | Extra                      |
+----+--------------+-------+--------+-------------+----------------------------+
|  1 | SIMPLE       | d     | index  | ux_deptname | Using index                |
|  1 | MATERIALIZED | de    | ref    | PRIMARY     | Using index; FirstMatch(d) |
+----+--------------+-------+--------+-------------+----------------------------+

mysql> EXPLAIN SELECT *
               FROM departments d
               WHERE d.dept_no IN (SELECT /*+ SEMIJOIN(MATERIALIZATION) */ de.dept_no
                                   FROM dept_emp de);

+----+--------------+-------------+--------+---------------------+--------------------------+
| id | select_type  | table       | type   | key                 | Extra                    |
+----+--------------+-------------+--------+---------------------+--------------------------+
|  1 | SIMPLE       | d           | index  | ux_deptname         | Using where; Using index |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_distinct_key> | NULL                     |
|  2 | MATERIALIZED | de          | index  | ix_fromdate         | Using index              |
+----+--------------+-------------+--------+---------------------+--------------------------+

위 쿼리는 원래 FirstMatch 전략을 사용하던 쿼리였는데 힌트를 주어 구체화 전략으로 바꾸어 실행되는 결과를 볼 수 있다.

SUBQUERY

세미조인 최적화를 사용하지 못하는 경우 사용하는 힌트이다. 거의 사용하지 않는다.

IN-to-EXISTS : SUBQUERY(INTOEXISTS)
Materialization : SUBQUERY(MATERIALIZATION)

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

8.0.19 버전까지는 블록 네스티드 루프 조인 알고리즘을 사용했지만,

8.0.18 버전에 도입된 해시 조인 알고리즘이 8.0.20 버전부터 대체하여 사용되도록 개선되었다.

→ 8.0.18 버전에서만 HASHJOIN 힌트가 유효

→ 8.0.20 버전에서는 BNL 힌트를 쓰면 해시 조인으로 유도

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

조인 순서를 결정하는 힌트이다.

JOIN_FIXED_ORDER: FROM절 순서대로 조인 실행
JOIN_ORDER: 힌트에 명시된 순서대로 조인 실행
JOIN_PREFIX: 드라이빙 테이블만 강제
JOIN_SUFFIX: 드리븐 테이블만 강제

-- FROM 절에 나열된 순서대로 조인
mysql> SELECT /*+ JOIN_FIXED_ORDER() */ *
       FROM employees e
       INNER JOIN dept_emp de 
       ON de.emp_no = e.emp_no
       INNER JOIN departments d 
       ON d.dept_no = de.dept_no;

-- 일부 테이블에 대한 조인 순서 지정
mysql> SELECT /*+ JOIN_ORDER(d, de) */ *
       FROM employees e
       INNER JOIN dept_emp de 
       ON de.emp_no = e.emp_no
       INNER JOIN departments d 
       ON d.dept_no = de.dept_no;

-- 드라이빙 테이블에 대해서만 순서 지정
mysql> SELECT /*+ JOIN_PREFIX(e, de) */ *
       FROM employees e
       INNER JOIN dept_emp de 
       ON de.emp_no = e.emp_no
       INNER JOIN departments d 
       ON d.dept_no = de.dept_no;

-- 드리븐 테이블에 대해서만 순서 지정
mysql> SELECT /*+ JOIN_SUFFIX(de, e) */ *
       FROM employees e
       INNER JOIN dept_emp de 
       ON de.emp_no = e.emp_no
       INNER JOIN departments d 
       ON d.dept_no = de.dept_no;

MERGE & NO_MERGE

옵티마이저는 서브쿼리를 임시 테이블로 만들지 않게 서브 쿼리와 메인 쿼리를 병합하는 최적화를 수행하게 되는데 이를 수행하게하는 힌트이다.

mysql> SELECT /*+ MERGE(sub) */ *
       FROM (SELECT *
             FROM employees
             WHERE first_name = 'Matt') sub
       LIMIT 10;

INDEX_MERGE & NO_INDEX_MERGE

옵티마이저가 여러 인덱스를 사용해서 교집합 혹은 합집합을 구해 쿼리를 처리하는 최적화를 인덱스 머지라고 한다. 이를 사용하는 힌트이다.

mysql> SELECT /*+ INDEX_MERGE(employees ix_firstname, PRIMARY) */ *
       FROM employees
       WHERE first_name = 'Georgi'
       AND emp_no BETWEEN 10000 AND 20000;

NO_ICP

INDEX_CONDITION_PUSHDOWN 최적화는 항상 성능 향상에 도움이 되기 때문에 옵티마이저는 최대한 ICP를 사용한다.

따라서 ICP 힌트는 존재하지 않는다.

그러나 ICP 때문에 문제가 발생하는 경우도 있다.

예를 들어 인덱스 A, B 중 하나를 선택하는 상황에서 A는 ICP를 사용가능한 인덱스이다.

그러나 실제 서비스에서 B 인덱스를 사용하는 것이 성능 향상에 더 큰 도움이 될 수도 있다.

그렇다고 A 인덱스를 삭제할 수 없으니 NO_ICP 힌트를 주어서 더 유연하게 최적화할 수 있다.

SKIP_SCAN & NO_SKIP_SCAN

SKIP_SCAN은 인덱스 구성 컬럼 중 누락된 선행 컬럼이 있더라도 해당 인덱스를 사용할 수 있게 해주는 최적화 전략이다.

그러나 누락된 선행 컬럼의 카디널리티가 높으면 오히려 성능이 떨어질 수 있다.

이러한 경우에 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

mysql> SELECT /*+ INDEX(employees ix_firstname) */
       FROM employees
       WHERE first_name='Matt';

'공부 > MySQL' 카테고리의 다른 글

MySQL에서 옵티마이저가 쿼리 최적화하는 방법 (0)	2024.07.17
MySQL에서 옵티마이저 동작하는 원리 (0)	2024.07.17
MySQL에서의 트랜잭션과 잠금의 개념 (0)	2024.07.16
MySQL 스토리지 엔진 아키텍처 (0)	2024.07.15
MySQL 엔진 아키텍쳐 (0)	2024.07.15

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/05 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

글 보관함

티스토리 뷰