TIL

9.4(1) 쿼리 힌트 (인덱스 힌트)

• 옵티마이저가 도출하는 쿼리 실행 계획은 많이 최적화되었지만 100% 완전하지는 않다.
  • 개발자나 DBA보다 부족한 실행 계획을 세울 때가 있다.
• 실행 계획을 어떻게 수립해야할 지 알려줄 수 있는 힌트가 제공된다.
  • 인덱스 힌트
  • 옵티마이저 힌트

9.4.1 인덱스 힌트

• 5.6 버전 이전부터 존재하던 STRAIGHT_JOIN과 USE INDEX 등의 인덱스 힌트는 모두 ANSI-SQL 표준 문법을 준수하지 못하는 단점이 있다.
• 5.6 버전 이후부터 추가된 옵티마이저 힌트들은 모두 MySQL이 아닌 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다.
• 즉 가능하면 옵티마이저 힌트를 사용할 것을 추천한다.
• 인덱스 힌트는 SELECT, UPDATE 명령에서만 사용할 수 있다.

STRAIGHT_JOIN

• STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드이다.
• STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.
  • 여러 테이블이 조인될 때 어느 테이블이 드라이빙 테이블이 될지는 옵티마이저가 그때그때 각 테이블의 통계 정보와 쿼리 조건을 기반으로 판단한다.
  • 레코드 건수가 가장 적은 테이블이 드라이빙 테이블로 선택될 것이다.
• 아래처럼 STRAIGHT_JOIN 힌트를 사용하면 FROM 절에 명시된 순서대로 조인을 수행한다.

SELECT STRAIGHT_JOIN
	e.first_name, e.last_name, de.dept_name
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no
	AND d.dept_no=de.empt_no;

• 다음 기준에 맞게 조인 순서가 결정되지 않는 경우 사용하면 좋다.
  • 임시 테이블과 일반 테이블의 조인인 경우 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다. 일반 테이블의 조인 칼럼에 인덱스가 없는 경우 레코드 건수가 적은 임시 테이블을 드라이빙으로 하는 것이 좋기 때문이다.
  • 임시 테이블끼리 조인하는 경우 둘 다 인덱스가 없기 때문에 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋다.
  • 일반 테이블끼리 조인하는 경우 양쪽 모두 조인 칼럼에 인덱스가 있거나 모두 없는 경우 레코드가 적은 테이블을 드라이빙으로 선택하는 것이 좋으며 그 외의 경우엔 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.
  • 여기서 언급한 레코드 건수는 전체가 아닌 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 조건을 만족하는 레코드 건수이다.
• STRAIGHT_JOIN과 비슷한 역할을 하는 옵티마이저 힌트
  • JOIN_FIXED_ORDER
  • JOIN_ORDER
  • JOIN_PREFIX
  • JOIN_SUFFIX

USE INDEX / FORCE INDEX / IGNORE INDEX

• 인덱스 힌트는 크게 3종류가 있으며 모두 키워드 뒤에 사용할 인덱스 이름을 괄호로 묶어 사용한다.
  • 프라이머리 키는 PRIMARY라고 명시
• USE IDEX
  • 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트로 대부분의 경우 옵티마이저는 힌트를 채택하지만 항상 그 인덱스를 사용하지는 않는다.
  • SELECT * FROM employees USE INDEX(primary) WHERE emp_no=’10001;
• FORCE INDEX
  • USE INDEX와 비슷하지만 옵티마이저에게 미치는 영향이 더 강한 힌트다. 하지만 거의 사용할 필요가 없으며 USE INDEX 힌트로도 그 인덱스를 사용하지 않은 경우라면 FORCE INDEX를 써도 마찬가지였다.
  • SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=’10001;
• IGNORE INDEX
  • 옵티마이저가 특정 인덱스를 사용하지 못하게 하는 용도로 사용되는 힌트다. 때로 풀 테이블 스캔을 유도하기 위해 사용할 수도 있다.
  • SELECT * FROM employees IGNOIRE INDEX(ix_firstname) WHERE emp_no=’10001;
• 3종류의 인덱스 힌트 모두 용도를 명시해줄 수 있다. (용도는 선택 사항)
  • USE INDEX FOR JOIN: 테이블 간 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어
  • USE INDEX FOR ORDER BY: 명시된 인덱스를 ORDER BY 용도로만 사용하도록 제한
  • USE INDEX FOR GROUP BY: 명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한
• 전문 검색(Full Text search) 인덱스가 있는 경우 옵티마이저는 다른 인덱스가 있어도 전문 검색 인덱스를 선택하는 경우가 많다.
• 더 좋은 실행 계획이 무엇인지 판단하기 힘든 상황이라면 힌트를 강제로 사용하는 것은 피하는 것이 좋다.
  • 지금 어떤 인덱스를 사용하는 것이 최선이더라도 시간이 지남에 따라 달라질 수도 있기 때문이다.

SQL_CALC_FOUND_ROWS

• SQL_CALC_FROUND_ROWS 힌트를 사용하면 LIMIT를 만족하는 수의 레코드를 찾았더라도 끝까지 검색을 수행한다.
  • 원래라면 LIMIT만큼 레코드를 찾으면 즉시 검색 작업을 멈춘다.
  • 반환하는 결과 레코드는 같다.
• SQL_CALC_FROUND_ROWS 힌트를 사용한 후 FOUND_ROWS() 함수를 이용해 LIMIT를 제외한 조건을 만족하는 레코드 수를 알 수 있다.

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

SELECT FOUND_ROWS() AS total_record_count;
|------------------|
|total_record_count|
|------------------|
|3000024           |
|------------------|

• 페이징 기능을 처리하기 위해 이 힌트를 고려할 수도 있지만 쿼리 튜닝을 적절히 했다면 따로 COUNT(*) 쿼리로 처리하는 것이 더 효율적이기 때문에 굳이 사용하지 않아도 되는 힌트이다.