인덱스 힌트

• 조인의 순서를 변경하는 것 다음으로 자주 사용된다.
• 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우, 옵티마이저가 가끔 실수를 하므로 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.
  • 대체로 MySQL 옵티마이저는 어떤 인덱스를 사용해야 할지를 무난하게 잘 선택하는 편이다.
• STRAIGHT_JOIN 힌트와는 달리 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.

인덱스 힌트의 종류

USE INDEX

• 가장 자주 사용되는 인덱스 힌트
• MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
• 대부분의 경우 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만, 항상 그 인덱스를 사용하는 것은 아니다.

FORCE INDEX

• USE INDEX와 비교해서 다른 점은 없으며, USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트
• USE INDEX 힌트만으로도 옵티마이저에 대한 영향력이 충분히 크기 때문에, 이 인덱스 힌트는 거의 사용할 필요가 없어 보인다.
• 대체로 USE INDEX 힌트를 부여했는데도 그 인덱스를 사용하지 않는 경우라면 FORCE INDEX 힌트를 사용해도 그 힌트를 사용하지 않았다.

IGNORE INDEX

• 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트
• 때로는 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 IGNORE INDEX 힌트를 사용할 수도 있다.

인덱스 힌트의 사용법

• 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용한다.
• 괄호 안에 아무것도 없거나 존재하지 않는 인덱스 이름을 사용할 경우 쿼리의 문법 오류로 처리된다.
• 별도로 사용자가 부여한 이름이 없는 프라이머리 키는 “PRIMARY”라고 명시하면 된다.

-- 테이블의 프라이머리 키를 이용해 쿼리를 처리
-- 아래 쿼리는 모두 동일한 실행 계획으로 처리된다.
SELECT * FROM employees WHERE emp_no = 10001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no = 10001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no = 10001;

-- 인덱스를 사용하지 못하게 힌트 추가 (풀 테이블 스캔으로 유도)
SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no = 10001;

-- 다른 인덱스를 사용하도록 힌트 추가
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no = 10001;

인덱스 힌트의 용도 명시

• 위의 인덱스 힌트 3가지 모두 용도를 명시해 줄 수 있다. (선택 사항)
• 특별히 인덱스 힌트에 용도가 명시되지 않으면(사용 가능한 경우) 주어진 인덱스를 3가지 용도로 사용한다.
• 인덱스의 용도까지는 크게 고려하지 않아도 된다.
  • ORDER BY나 GROUP BY 작업에서 인덱스를 사용할 수 있다면 나은 성능을 보장하며
  • 용도는 옵티마이저가 대부분 최적으로 선택하기 때문이다.

USE INDEX FOR JOIN

• 여기서 “JOIN”은 테이블 간의 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어다.
• MySQL 서버에서는 하나의 테이블로부터 데이터를 검색하는 작업도 JOIN이라고 표현하기 때문에 “FOR JOIN”이라는 이름이 붙었다.

USE INDEX FOR ORDER BY

• 명시된 인덱스를 ORDER BY 용도로만 사용할 수 있게 제한한다.

USE INDEX FOR GROUP BY

• 명시된 인덱스를 GROUP BY 용도로만 사용할 수 있게 제한한다.

주의 사항

• 전문 검색(Full Text search) 인덱스가 있는 경우, MySQL 옵티마이저는 전문 검색 인덱스를 선택하는 경우가 많다.
  • 다른 일반 보조 인덱스(B-Tree 인덱스)를 사용할 수 있는 상황이라고 하더라도 그렇다.
  • 옵티마이저는 프라이머리 키나 전문 검색 인덱스와 같은 인덱스에 대해서는 선택 시 가중치를 두고 실행 계획을 수립하기 때문이다.
• 인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘든 상황이라면 힌트를 사용해 강제로 옵티마이저의 실행 계획에 영향을 미치는 것은 피하는 것이 좋다.
  • 최적의 실행 계획은 데이터의 성격에 따라 시시각각 변하기 때문에, 가능하다면 그때그때 옵티마이저가 당시 통계 정보를 가지고 선택하게 하는 것이 가장 좋다.
• 가장 훌륭한 최적화는 그 쿼리를 서비스에서 없애 버리거나 튜닝할 필요가 없게 데이터를 최소화하는 것이다.
  • 혹은 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요치 않게 한다.
  • 어떤 방법도 없다면 그다음으로 힌트를 선택한다.

Reference

참고 서적

📔 Real MySQL 8.0