[Real MySQL] 9-3. 옵티마이저와 힌트: 힌트

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

1. 인덱스 힌트

STRAIGHT_JOIN

조인 순서를 고정하는 역할을 담당함

• from 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도함
• 기본적으로 여러 개의 테이블이 조인되는 경우, 어떤 테이블이 드라이빙 테이블이 될지 모름 (통계 정보 기반으로 수립됨)

 

옵티마이저 힌트인 동시에 조인 키워드

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

• 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하도록 하는 힌트
• USE INDEX FOR JOIN: 명시된 인덱스를 조인 + 검색 목적으로만 쓰기
• USE INDEX FOR ORDER BY: 명시된 인덱스를 ORDER BY 목적으로만 쓰기
• USE INDEX FOR GROUP BY: 명시된 인덱스를 GROUP BY 목적으로만 쓰기

 

FORCE INDEX

• USE INDEX와 효과는 같으며, 옵티마이저에게 더 미치는 영향이 큼

 

IGNORE INDEX

• 특정 인덱스를 사용하지 못하게 하는 용도
• 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용

 

SQL_CALC_FOUND_ROWS

• 기본적으로 LIMIT 구문을 사용할 경우, 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춤
• SQL_CALC_FOUND_ROWS 힌트를 사용할 경우 끝까지 검색을 수행함

 

FOUND_ROWS()

• LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지 알아낼 수 있음

 

2. 옵티마이저 힌트

MAX_EXECUTION_TIME

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

• 쿼리의 최대 실행 시간 설정
• 쿼리가 지정된 시간을 초과하면 쿼리는 실패함
• 실행 계획에 영향을 미치지 않음

 

SET_VAR

SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */
  FROM employees
 WHERE first_name='George' AND emp_no BETWEEN 10000 AND 20000;

• 현재 실행 계획에서 시스템 변수 값을 일시적으로 적용
• 단, 모든 시스템 변수를 조정할 수는 없음

 

SEMIJOIN

explain
select *
  from departments d
 where d.dept_no in 
     (select /*+ SEMIJOIN(MATERIALIZATION) */ de.dept_no FROM dept_emp de);

• 세미 조인의 최적화 전략을 수정할 수 있음

 

NO_SEMIJOIN

explain
select *
  from departments d
 where d.dept_no in 
     (select /*+ NO_SEMIJOIN(DUPSWEEDOUT, FIRSTMATCH) */ de.dept_no FROM dept_emp de);

• 특정 세미 조인의 최적화 전략을 사용하지 않도록 할 수 있음

 

SUBQUERY

• 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법
• SUBQUERY(INTOEXISTS)
• SUBQUERY(MATERIALIZATION)

 

BNL & NO_BNL

explain
select * /*+ BNL(e, de) */
  from employees e
 inner join depth_emp de on de.emp_no = e.emp_no;

• 해시 조인을 유도하는 힌트

 

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• 조인 순서를 결정하는 힌트

 

JOIN_FIXED_ORDER

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;

• FROM 절의 테이블 순서대로 조인을 실행 (STRAIGHT_JOIN 힌트와 동일)

 

JOIN_ORDER

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;

• 힌트에 명시된 테이블의 순서대로 조인 실행

 

JOIN_PREFIX

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;

• 드라이빙 테이블만 강제

 

JOIN_SUFFIX

select /*+ JOIN_SUFFIX(de, d) */ *
  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

• 서브 쿼리를 처리할 때, 임시 테이블을 사용할 지 외부 쿼리와 병합할 지 결정할 수 있음

select /*+ merge(sub) */ * 
  from (select * from employees where first_name='Matt') sub 
  LIMIT 10;

select /*+ no_merge(sub) */ * 
  from (select * from employees where first_name='Matt') sub 
  LIMIT 10;

 

NO_ICP

• 인덱스 컨디션 푸쉬다운을 끄는 힌트

select /*+ no_icp(employees ix_lastname_firstname) */ * 
  from employees
  where last_name='Action' and first_name like '%sal';

 

SKIP_SCAN & NO_SKIP_SCAN

• 인덱스 스킵 스캔은 선행 칼럼에 대한 조건이 없어도 해당 인덱스를 사용하게 해줌
• 단, 선행 칼럼이 가지는 유니크한 값의 개수가 많아지면 오히려 성능이 떨어짐
• 인덱스 스킵 스캔을 활성화 or 비활성화 할 수 있음

select /*+ no_skip_scan(employees ix_gender_birthdate) */ gender, birth_date
  from employees
 where birth_date>='1995-01-01';

 

INDEX & NO_INDEX

select /*+ index(employees ix_firstname) */ *
  from employees
 where first_name='Matt';

• 테이블명과 인덱스 이름을 함께 명시