인덱스 정렬 선호

IGNORE INDEX 힌트

• MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우, 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행된다.
• 예를 들어, 다음 쿼리는 2가지 실행 계획을 선택할 수 있다.

  SELECT *
  FROM employees
  WHERE hire_date BETWEEN '1985-01-01' AND '195-02-01'
  ORDER BY emp_no;

  1. ix_hiredate 인덱스를 이용해 WHERE 절 조건에 일치하는 레코드를 찾은 다음, emp_no로 정렬해서 결과를 반환
  2. employees 테이블의 프라이머리 키(emp_no)를 정순으로 읽으면서 hire_date 칼럼의 조건에 일치하는지 비교 후 결과를 반환
• 일반적으로 체크해야 하는 레코드 건수가 많지 않다면 1번이 효율적일 것이다.
• 그러나 가끔 옵티마이저가 실수로 체크해야 하는 레코드 건수가 많음에도 잘못된 실행 계획(2번째 방법)을 선택하는 경우가 생긴다.
• MySQL 8.0.20 버전까지는 이 같은 옵티마이저의 실수가 자주 발생하면 다른 실행 계획을 사용하게 하기 위해 특정 인덱스(ORDER BY를 위한 인덱스)를 사용하지 못하도록 “IGNORE INDEX” 힌트를 사용하거나 했다.

Prefer Ordering Index

• MySQL 8.0.21 버전부터는 MySQL 서버 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 preper_ordering_index 옵티마이저 옵션이 추가되었다.
• preper_ordering_index 옵션의 기본값은 ON으로 설정되어 있다.
• 옵티마이저가 자주 실수를 한다면 preper_ordering_index 옵션을 OFF로 변경하면 된다.

  -- 현재 커넥션에서만 옵션을 비활성화
  SET SESSION optimizer_switch='prefer_ordering_index=OFF';
  
  -- 현재 쿼리에 대해서만 옵션을 비활성화
  SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=OFF') */ ...
  FROM ...

Reference

참고 서적

📔 Real MySQL 8.0