9.4 쿼리 힌트

쿼리 힌트

쿼리의 실행 계획 최적화가 많이 성숙해졌지만, 여전히 MYSQL 서버는 우리가 서비스하는 비즈니스를 100% 이해하지 못한다.
이런 경우에 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할 지 알려줄 수 있는 방법이 필요하다.
그러나, 인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘들다면 힌트를 사용해 강제로 옵티마이저의 실행 계획에 영향을 미치는 것은 피하는 것이 좋다.
MYSQL 서버에서 사용 가능한 쿼리 힌트는 2가지로 구분된다.

• 인덱스 힌트
• 옵티마이저 힌트

인덱스 힌트는 예전 버전의 MYSQL 서버에서 사용되어 오던 USE INDEX같은 힌트를 의미하며,
옵티마이저 힌트는 5.6 버전부터 새롭게 추가되기 시작한 힌트를 말한다.

인덱스 힌트

STRIGHT JOIN과 USE INDEX등을 포함한 힌트들은 MYSQL 서버에 옵티마이저 힌트가 도입되기 전에 사용된 기능으로,
ANSI-SQL 표준 문법을 준수하지 못한다.
MYSQL 5.6 버전부터 추가되기 시작한 옵티마이저 힌트는 MYSQL 서버를 제외한 다른 RDBMS에서는 주석으로 해석되기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다.
그래서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트를 사용해라.
또한 인덱스 힌트는 SELECT 명령과 UPDATE명령에서만 사용 가능 하다.

STRAIGHT_JOIN

STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정한다.
다음 쿼리는 3개의 테이블을 조인하지만, 어떤 테이블이 드라이빙 테이블이 되고, 드리븐 테이블이 되는지 알 수 없다.
옵티마이저가 각 테이블의 통계 정보와 쿼리의 조건을 기반으로 가장 최적이라고 판단되는 순서로 조인한다.

select count(*) from departments; (9)
select count(*) from dept_emp; (331603)
select count(*) from employees; (300024)

위 실행 계획을 확인해보면, departments 테이블을 드라이빙 테이블로 선택하고, 두번쨰로 dept_emp 테이블을 읽은 뒤 employees 테이블을 읽었다.
일반적으로, 조인을 하기 위한 인덱스 여부로 조인의 순서가 결정되며, 조인 칼럼의 인덱스가 아무런 문제가 없을 경우에는 레코드가 적은 테이블을 드라이빙 테이블로 선택한다.


이 쿼리의 조인 순서를 변경하려는 경우에는 STRAIGHT_JOIN 힌트를 사용할 수 있다.

이 쿼리의 실행 계획을 보면 FROM절에 명시한 테이블의 순서대로 조인을 수행한다.

주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

• 임시 테이블(인라인 뷰)과 일반 테이블의 조인 : 거의 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다. 일반 테이블의 조인 칼럼에 인덱스가 없는 경우에는 레코드 건수가 작은 쪽을 먼저 읽도록 드라이빙으로 선택하는 것이 좋은데, 대부분 옵티마이저가 적절한 조인 순서를 선택하기 때문에 쿼리를 작성할 떄부터 힌트를 사용할 필요는 없다. 옵티마이저가 실행 계획을 제대로 수립하지 못해서 심각한 성능 저하가 있을 경우에 힌트를 사용하면 된다.
• 임시 테이블끼리 조인 : 임시테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무방하기 때문에 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋다.
• 일반 테이블끼리 조인 : 양쪽 테이블 모두 조인 칼럼에 인덱스가 있거나 둘다 없을 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택하는 것이 좋고, 그 이외라면 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋음.

레코드 건수라는 건 인덱스를 WHERE조건을 만족하는 레코드를 의미하는 것이다.(전체 레코드건수가 아님!)
STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트 :
A. JOIN_FIXED_ORDER : STRAIGHT_JOIN 구문을 대체하며, 이는 조인순서를 강제한다.
B. JOIN_ORDER : 가능하다면, 나열된 join 순서로 조인할것을 권고한다. (USE INDEX와 비슷하게 아주 불합리할 경우 사용하지 않는다.)
C. JOIN_PREFIX : 처음의 조인순서를 권고한다.
D. JOIN_SUFFIX: : 마지막의 조인순서를 권고한다.

USE INDEX/ FORCE INDEX/ IGNORE INDEX

인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야한다.
대체로 옵티마이저는 어떤 인덱스를 사용해야 할지를 무난하게 잘 선택한다.
하지만 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우, 옵티마이저가 실수를 하는데 이런 경우에 강제로 특정 인덱스를 사용하도록 힌트를 줄 수 있다.
인덱스 힌트는 크게 3종류가 있다.

• USE INDEX : 가장 자주 사용되는 인덱스 힌트로, 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트이다. 옵티마이저는 사용자의 힌트를 채택하지만 그 인덱스를 항상 사용하는 것은 아니다.(?)
• FORCE INDEX : USE INDEX와 비교해 다른점은 없고, USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트이다. USE INDEX 힌트를 부여했는데도 그 인덱스를 사용하지 않는 경우라면 FORCE INDEX 힌트를 사용해도 그 인덱스를 사용하지 않았다.(??)
• INGNORE INDEX : 특정 인덱스를 사용하지 못하게 하는 용도로 사용한다. 때로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 IGNORE INDEX 힌트를 사용할 수 있다.

위 3종류의 인덱스 힌트 모두 용도를 명시할 수 있다.
용도는 선택사항이며, 인덱스 힌트에 용도가 명시되지 않으면 주어진 인덱스를 3가지 용도로 사용한다.

• USE INDEX FOR JOIN : JOIN이라는 키워드는 테이블 간의 조인 뿐 아니라 레코드를 검색하기 위한 용도까지 포함하는 용아. (하나의 테이블 검색 포함)
• USE INDEX FOR ORDER BY : 명시된 인덱스를 ORDER BY 용도로만 사용할 수 있게 제한.
• USE INDEX FOR GROUP BY : 명시된 인덱스를 GROUP BY 용도로만 사용할 수 있게 제한.

용도는 보통 옵티마이저가 대부분 최적으로 선택하기 때문에 용도까지는 크게 고려하지 않아도 된다.

인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘들다면 힌트를 사용해 강제로 옵티마이저의 실행 계획에 영향을 미치는 것은 피하는 것이 좋다. 최적의 실행 계획은 데이터의 성격에 따라 시시각각 변하므로, 지금 PK를 사용하는 것이 좋은 계획이었다고 하더라도 내일은 달라질 수 있기 떄문에 그때그때 옵티마이저가 당시 통계 정보를 가지고 선택하게 하는 것이 가장 좋다. 가장 훌륭한 최적화는 그 쿼리를 서비스에서 없애 버리거나 튜닝할 필요가 없게 데이터를 최소화 하는 것이며, 그것이 어렵다면 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요치 않게 하는 것. 어떠한 방법도 없다면 그다음으로는 힌트를 선택하는 것인데, 일반적으로 실무에서는 앞쪽의 작업들에 상당한 시간과 작업 능력이 필요하기 때문에 힌트에 의존하는 경우가 많다.

옵티마이저 힌트

MYSQL 8.0 버전에서 사용 가능한 힌트는 종류가 매우 다양하며, 옵티마이저 힌트가 미치는 영향 범위도 매우 다양하다.
옵티마이저 힌트는 영향 범위에 따라 4개의 그룹으로 나우어 볼 수 있다.

• 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 힌트
• 테이블 : 특정 테이블의 이름을 사용할 수 있는 힌트
• 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 힌트로서, 특정 블록의 이름을 ㅁ여시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미침
• 글로벌 : 전체 쿼리에 대해 영향을 미치는 힌트
  이 구분으로 인해 힌트의 사용 위치가 달라지는 것은 아니다.

인덱스 수준의 힌트는 반드시 테이블 명이 선행되어야 한다.
EX> SELECT /*+ INDEX(employees ix_firstname) */ * FROM employees WHERE first_name ='MATT'

• MAX_EXECUTION_TIME
  옵티마이저 힌트 중 유일하게 실행 게획에 영향을 미치지 않는 힌트.
  쿼리의 최대 실행시간을 설정하고 쿼리가 지정된 시간을 초과하면 쿼리를 실패하게 한다.
  

• SET_VAR
  실행 계획을 바꾸는 용도뿐만 아니라 MYSQL 시스템 변수 값을 일시적으로 변경한다.
  조인 버퍼나 정렬용 버퍼의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로 사용할 수 있다.