TIL

9.3(6) 인비저블 인덱스, 스킵 스캔, 인덱스 정렬 선호

인비저블 인덱스(use_invisible_indexes)

• 8.0 버전부터 인덱스가 존재하더라도 사용하지 못하게 제어하는 기능을 제공한다.
  • 이전에는 인덱스를 삭제해야만 사용하지 않게 할 수 있었다.

-- // 옵티마이저가 인덱스를 사용하지 못하게 변경
ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;

-- // 옵티마이저가 인덱스를 사용할 수 있게 변경
ALTER TABLE employees ALTER INDEX ix_hiredate VISIBLE;

• use_invisible_indexes 옵티마이저 옵션을 이용하면 INVISIBLE로 설정된 인덱스도 옵티마이저가 사용할 수 있게할 수 있다.
  • 기본값은 off라서 옵션을 바꾸지 않는한 INVISIBLE 인덱스는 사용하지 않는다.

스킵 스캔(skip_scan)

• 인덱스 스킵 스캔 최적화를 활성화할지를 아래 코드로 제어할 수 있다.
• 인덱스 스킵 스캔은 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로 인덱스를 이용할 수 있는 최적화이다.
  • 자세한 내용은 8.3.4.4절 ‘인덱스 스킵 스캔’ 참조

-- // 현제 세션에서 인덱스 스킵 스캔 활성화
SET optimizer_switch='skip_can=on';

-- // 특정 테이블에 대해 인덱스 스킵 스캔을 사용하도록 힌트 사용
SELECT /*+ SKIP_SCAN(employees)*/ COUNT(*)
FROM employees
WHERE birth_date>='1965-02-01';

-- // 특정 테이블에 대해 인덱스 스킵 스캔을 사용하지 않도록 힌트 사용
SELECT /*+ NO_SKIP_SCAN(employees)*/ COUNT(*)
FROM employees
WHERE birth_date>='1965-02-01';

인덱스 정렬 선호(prefer_ordering_index)

• 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스로 처리 가능한 경우 실행 계획에서 이 인덱스 가중치를 높이 설정해서 실행된다.

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

• 위 쿼리는 2가지 실행 계획을 선택할 수 있다.
  1. ix_hiredate 인덱스로 WHERE 조건절에 해당하는 레코드를 찾은 다음 emp_no로 정렬해서 결과를 반환
  2. employees 테이블의 프라이머리 키가 emp_no이므로 프라이머리 키를 정순으로 읽으면서 WHERE 조건에 일치하는지 비교 후 결과 반환
• 일반적으로 hire_date 칼럼의 조건에 부합되는 레코드 수가 적다면 1번이 효율적이다.
  • employees의 전체 레코드 수가 많다면 2번 방법으로 모든 레코드를 읽는 것은 비효율적이다.
  • 그런데 옵티마이저가 가끔씩 비효율적인 실행 계획을 선택할 때가 있다.
• MySQL 8.0.20 버전까지는 효율적인 실행 계획을 사용하게 하기 위해 특정 인덱스를 사용하지 못하도록 IGNORE INDEX 힌트를 사용했다.
• MySQL 8.0.21 버전부터는 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션이 추가되었다.

-- // 현재 커넥션에서만 비활성화
SET SESSION optimizer_switch='prefer_ordering_index=OFF';

-- // 현재 쿼리에 대해서만 비활성화
SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=OFF') */
...
FROM
...