쿼리 힌트
언제 사용하는가?
옵티마이저가 우리가 사용하는 서비스의 비즈니스를 잘 이해하지 못하고 실행 계획을 수립할 때 사용
MySQL에서 사용 가능한 쿼리 힌트는 인덱스 힌트, 옵티마이저 힌트 두 가지로 구분할 수 있다.
쿼리 힌트는 옵티마이저에게 올바른 방향으로 실행 계획을 수립할 수 있도록 알려주는 역할을 한다.
크게 아래 2가지 힌트 방식이 존재한다.
•
Index Hint
•
Optimizer Hint
MySQL 5.6이전에는 Index Hint를 사용, 이후부터는 Optimizer Hint 사용
서비스에서 필요한 데이터를 줄이거나 쿼리를 없애서 최적화하는 것이 가장 좋다
이게 어렵다면, 데이터 모델을 간단하게 만들어서 쿼리를 짧게 만들고, 힌트를 쓸 필요가 없게 하는 것이 좋다. 실제로 이런 일을 하려면 시간과 실력이 많이 필요해서, 대부분은 힌트를 많이 쓴다고 함
인덱스 힌트
•
인덱스 힌트는 SELECT, UPDATE 문에서만 사용할 수 있고, 가능하면 옵티마이저 힌트를 사용할 것을 추천한다.
•
STRAIGHT_JOIN과 USE INDEX 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들
•
STRAIGHT_JOIN
◦
STRAIGHT_JOIN은 SELECT STRAIGHT_JOIN ~ 이러한 형태로 쓰임
◦
STRAIGHT_JOIN을 사용하여 JOIN의 순서를 제어하고 인덱스 힌트를 제공하고자 할 때, MySQL에서는 특정 조인 순서를 강제하고, 인덱스를 사용하도록 명시
◦
STRAIGHT_JOIN 힌트는 한 번 사용되면 FROM 절의 모든 테이블에 대해 조인 순서가 결정되는 효과를 낸다.
▪
STRAIGHT_JOIN은 MySQL의 조인 최적화 과정을 오버라이드하며, 테이블을 명시된 순서대로 처리 (조인의 순서를 MySQL 옵티마이저가 변경하지 못하도록 고정)
◦
STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 힌트
▪
나머지 4개의 옵티마이저 힌트
•
JOIN_FIXED_ORDER / JOIN_ORDER / JOIN_PREFIX / JOIN_SUFFIX
•
(JOIN_ORDER와 JOIN_PREFIX, 그리고 JOIN_SUFFIX)는 STRAIGHT_JOIN과는 달리 일부 테이블의 조인 순서에 대해서만 제안하는 힌트
•
JOIN_FIXED_ORDER 옵티마이저 힌트는 STRAIGHT_JOIN 힌트와 동일한 효과를 낸다.
•
USE / FORCE / IGNORE INDEX
SELECT *
FROM table_1 USE INDEX [FOR ORDER BY/GROUP BY/JOIN](idx_1)
SQL
복사
◦
[USE / FORCE / IGNORE] INDEX 힌트를 사용하기 위해선 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.
◦
용도 : 인덱스 강제 사용을 지정
◦
FOR ORDER BY/GROUP BY/JOIN 를 붙혀 인덱스의 용도를 제한할 수 있다.
▪
USE는 인덱스 사용 권장
▪
FORCE는 더 강하게 사용 권장(잘 안씀)
▪
IGNORE는 인덱스를 못 사용하게 한다.
예시
EXPLAIN SELECT
*
FROM
press_list pl
STRAIGHT_JOIN
draft_article da USE INDEX (PRIMARY) ON pl.press_list_id = da.draft_article_id
STRAIGHT_JOIN
article a USE INDEX (PRIMARY) ON da.draft_article_id = a.article_id
;
SQL
복사
◦
일반 조인과 달리 생각보다 원하는 결과가 잘 나오지 않는다
•
SQL_CALC_FOUND_ROWS
◦
MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많더라도 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색을 멈춘다
◦
SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리는 LIMIT을 만족하는 수만큼 레코드를 찾았더라도 끝까지 검색을 수행한다
select SQL_CALC_FOUND_ROWS * from employeeswhere first_name='Georgi' limit 0, 20;
select found_rows() as total_record_count;
SQL
복사
◦
위의 경우 LIMIT 조건으로 20건만 가져오도록 했지만 SQL_CALC_FOUND_ROWS 힌트 때문에 전체 레코드를 전부 읽어야 한다. (랜덤 I/O 발생)
select count(*) from employeeswhere first_name = 'Georgi';
select * from employeeswhere first_name='Georgi' limit 0, 20;
SQL
복사
◦
위의 쿼리 2번을 수행하면 전체 건수는 실제 레코드를 찾는 랜덤 I/O가 아닌 커버링 인덱스쿼리로 가져올 수 있다.
MySQL 인덱스 힌트들은 ANSI-SQL 표준 문법을 준수하지 못한다.
•
옵티마이저 힌트를 더 사용하는 이유
•
MySQL 5.6 버전부터 추가되기 시작한 옵티마이저 힌트들은 ANSI-SQL 표준을 준수하여 다른 RDBMS에서는 주석으로 해석된다.
옵티마이저 힌트
옵티마이저 힌트는 영향 범위에 따라 4가지로 나눌 수 있다.
•
인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
→ 참고로 모든 인덱스 수준의 힌트는 아래와 같이 테이블명이 선행되어야 한다.
SELECT /*+ INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name='Matt';
SQL
복사
•
테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
•
쿼리 블록: 힌트가 명시된 쿼리 블록에 대해서 영향을 미치는 옵티마이저 힌트
•
글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트
특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만 외부 쿼리 블록에서 사용할 수도 있다.
이처럼 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 QB_NAME() 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다.
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);
SQL
복사
•
MAX_EXECUTION_TIME
◦
쿼리의 최대 실행 시간을 설정하는 힌트.
▪
실행 계획에 영향을 미치지 않음
◦
밀리초 단위의 시간을 설정할 수 있으며, 지정된 시간을 초과하면 쿼리가 실패.
▪
ERROR 3024 : Query execution was interrupted, maximum statement execution time exceeded 발생
•
SET_VAR
◦
MySQL 서버의 시스템 변수를 설정하는 힌트이다.
▪
모든 시스템 변수는 아님
◦
실행 계획을 바꾸는 용도뿐만 아니라 조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있다.
•
SEMIJOIN & NO_SEMIJOIN
◦
세미 조인 복습
개념
◦
SEMIJOIN(세미_조인_최적화_전략_이름) 힌트
▪
어떤 세미 조인 최적화 전략을 사용할지를 제어할 수 있다.
▪
세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브쿼리에 명시해야 한다.
목록
◦
만약 세미 조인을 사용하고 싶지 않다면 NO_SEMIJOIN 힌트를 사용하면된다.
•
SUBQUERY
◦
서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 방법
▪
IN-to-EXISTS : SUBQUERY(INTOEXISTS)
▪
materialization : SUBQUERY(materialization)
•
BNL & NO_BNL & HASHJOIN & NO_HASHJOIN
◦
8.0.19 버전까지는 블록 네스티드 루프 조인 알고리즘이 사용되었지만 그 후부터는 해시 조인 알고리즘으로 대체
▪
BNL, NO_BNL 힌트로 여전히 사용 가능
◦
해시 조인을 유도하고 싶으면 BNL(테이블1, 테이블2) 이렇게 힌트를 작성하고, 사용하지 않게 한다면 NO_BNL 힌트를 사용하면 된다.
•
JOIN 관련
◦
STRAIGHT_JOIN 힌트 단점
▪
우선 쿼리의 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움
▪
한 번 사용되면 FROM절에 명시된 모든 테이블의 조인 순서가 결정되기 때문에 일부는 조인 순서를 강제하고 일부는 옵티마이저에게 순서를 결정하게 맡기는 것이 불가능
◦
STRAIGHT_JOIN 힌트의 단점을 보완한 옵티마이저 힌트
▪
JOIN 순서를 위한 4가지 힌트가 있다.
▪
JOIN_FIXED_ORDER(): FROM절의 테이블 순서대로 조인을 실행
▪
JOIN_ORDER(tb1,tb2, ...): 힌트에 명시된 테이블 순서대로 조인을 실행
▪
JOIN_PREFIX(tb1): 드라이빙 테이블을 설정 및 강제
▪
JOIN_SUFFIX(tb1, tb2, …): 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제
•
MERGE & NO_MERGE
◦
FROM 절에 사용된 서브쿼리를 내부 임시 테이블로 생성할지 외부 쿼리와 병합하는 최적화를 수행할지를 정하는 힌트
◦
MERGE(서브쿼리_이름): 임시 테이블을 사용하지 않게 서브 쿼리를 외부 쿼리와 병합
◦
NO_MERGE(서브쿼리_이름): 임시 테이블을 사용하도록 강제
•
INDEX_MERGE & NO_INDEX_MERGE
◦
INDEX_MERGE: 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용하는 것
▪
MySQL 서버는 가능하면 테이블당 하나의 인덱스 만을 이용해 쿼리를 처리
▪
하지만 검색 대상을 충분히 좁힐 수 없다면 여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합을 구해서 그 결과를 반환
◦
인덱스 머지를 강제하거나 사용하지 않도록 하는 힌트이다.
•
NO_ICP
◦
인덱스 컨디션 푸시다운
간략
◦
인덱스 컨디션 푸시다운 최적화는 성능 향상에 도움이 되므로 사용하는 방향으로 실행 계획을 수립한다. ⇒ 따라서 ICP 힌트는 제공되지 않는다.
◦
그런데 ICP로 인해 실행 계획 비용 계산이 잘못되어 잘못된 실행 계획이 수립될 수도 있다.
◦
NO_ICP 힌트로 ICP를 비활성화할 수 있다.
•
SKIP_SCAN & NO_SKIP_SCAN
◦
인덱스 스킵 스캔을 강제하거나 사용하지 않도록 하는 힌트이다.
◦
인덱스의 선행 칼럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용할 수 있도록 해주는 최적화 기능 제공
▪
선행 칼럼이 가지는 유니크한 값의 개수가 많아지면 인덱스 스킵 스캔의 성능은 오히려 떨어짐
◦
MySQL 옵티마이저가 유니크한 값의 개수를 잘 분석하지 못하거나 비효율적인 인덱스 스킵 스캔을 선택 시 NO_SKIP_SCAN 옵티마이저 힌트로 인덱스 스킵 스캔을 사용하지 않게 할 수 있음.
•
INDEX & NO_INDEX
◦
이전에 사용되던 인덱스 힌트를 대체하는 용도로 제공되는 힌트이다
◦
인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열
◦
옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.
-- // 인덱스 힌트 사용
select *
from employees USE INDEX(ix_firstname)
where first_name='Matt';
-- // 옵티마이저 힌트 사용
select /*+ INDEX(employees ix_firstname) */ *
from employees
where first_name='Matt';
SQL
복사