[DB] 인덱스를 내 의도대로 설정하자

옵티마이저는 SQL의 실행계획을 만들어주는 Database의 핵심 엔진이다.

이 옵티마이저를 통해 우리는 조회 시, 최적의 속도로 결과를 얻을 수 있게 되는데,

종종 이 옵티마이저가 우리의 의도대로 동작하지 않는 경우가 있다.

 

예를들어 특정 select 쿼리를 수행하기 위해 analyze를 실행했다고 하자(MariaDB 기준)

사정상 키는 모자이크 처리

 

분명히 사용 가능한 키가 있음에도 테이블 full scan이 발생하고 있는 것을 확인할 수 있고,

조회 대상인 row도 1만이 넘어가고 있다.

이럴 때 강제로 사용가능한 index를 수행하도록 지정할 수 있는데, 이를 Index Hint 라고 한다.

---

Index Hint

 

특정 인덱스를 사용하지 않길 원하면 ignore 를, 사용하길 원하면 use 또는 force 를 사용할 수 있다.

• USE INDEX
  • 옵티마이저에게 지정한 인덱스를 사용하도록 "권장"한다.
  • 하지만 옵티마이저가 판단하여 Table Scan 이 더 빠르다면 인덱스 대신 Table Scan 을 수행한다.
• FORCE INDEX
  • 무조건 인덱스를 사용하도록 "강제"한다.

---

지금 예시의 경우에는 옵티마이저가 사용 가능한 index가 있음에도 불구하고, Table Full Scan을 하고 있기 때문에,

force index 옵션을 준다. 적용 예시는 아래와 같다.

SELECT * FROM table FORCE INDEX(idx_some_key) WHERE key between 0 and 100;

사정상 키는 모자이크 처리

 

다시 analyze의 결과를 보면, index를 사용하는 range 조회로 바뀌고, 대상 row 수도 줄어든 것을 확인할 수 있다.

---

참고

https://bcp0109.tistory.com/374

MySQL Optimizer 와 USE INDEX vs FORCE INDEX

https://coding-factory.tistory.com/743

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여