TIL

9.3(2) 인덱스 컨디션 푸시 다운과 인덱스 확장

인덱스 컨디션 푸시 다운

• MySQL 5.6버전부터 인덱스를 범위 제한 조건으로 사용하지 못하더라도 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선됐다. 이를 인덱스 컨디션 푸시 다운이라고 한다.
• employees 테이블에 (last_name, first_name) 인덱스가 생성되어 있을 때 아래 쿼리가 인덱스 컨디션 푸시 다운 유무에 따라 어떻게 작동하는지 살펴보자.

SELECT * FROM employees
WHERE last_name='Action' AND first_name LIKE '%SAL';

인덱스 컨디션 푸시 다운 비활성화

id	table	type	key	key_len	Extra
1	SIMPLE	employees	ix_lastname_firstname	66	Using where

• Extra의 Using where을 통해 InnoDB 스토리지 엔진이 읽어 반환한 레코드를 MySQL 엔진이 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사했다는 것을 알 수 있다.
  • 인덱스를 이용해 last_name=’Action’인 레코드를 스토리지 엔진이 읽어서 반환
  • first_name LIKE ‘%SAL’ 조건(인덱스를 사용할 수 없는 조건)은 MySQL이 last_name=’Action’인 레코드 중에서 검색
• first_name의 조건은 인덱스를 사용할 수 없는 조건이지만 (last_name, first_name) 인덱스에는 first_name이 존재하기 때문에 스토리지 엔진에서부터 조건에 맞는 레코드를 반환할 수 있지만, 그러지 않고 인덱스를 사용할 수 있는 조건에서만 검색 후 더 많은 레코드를 읽어 반환하는 문제가 있다.
  • 이 경우 last_name=’Action’인 레코드가 10만건이고 그 중 1건만 first_name LIKE ‘%SAL’ 조건을 만족한다면 99,999건의 불필요한 읽기가 발생하는 상황이다.

인덱스 컨디션 푸시 다운 활성화

id	table	type	key	key_len	Extra
1	SIMPLE	employees	ix_lastname_firstname	66	Using index condition

• first_name LIKE ‘%SAL’ 조건이 인덱스를 사용하지 못하는 조건이지만 MySQL 엔진은 스토리지 엔진으로 이 조건까지 전달한다.
• 덕분에 InnoDB 스토리지 엔진에서부터 최대한 필터링을 해서 두 조건을 만족하는 레코드를 반환하게 된다.
• Extra에 Using index conditon을 확인할 수 있다.
• 쿼리의 성능이 몇 배에서 몇십 배로 향상될 수도 있는 중요한 기능이다.

인덱스 확장(use_index_extensions)

• use_index_extensions 옵션은 InnoDB 스토리지 엔진에서 세컨더리 인덱스에 존재하는 프라이머리 키를 활용할 수 있게 할지 결정하는 옵션이다.
  • 세컨더리 인덱스는 클러스터링 키를 프라이머리 키로 생성한다.
  • 때문에 모든 세컨더리 인덱스는 프라이머리 키를 가진다.
• 그래서 세컨더리 인덱스와 프라이머리 키를 조건으로 검색 또는 정렬하는 경우 인덱스를 적극 활용할 수 있게 된다.