[DB] 인덱스 컨디션 푸시다운(Index Condition PushDown)

0. 학습 목적

• 인덱스 컨디션 푸시다운의 정의를 이해한다.
• 인덱스 컨디션 푸시다운이 활성화되었을 때와 비활성화 되었을 떄, 실행 계획이 어떻게 바뀌는지 이해한다.

1. 인덱스 컨디션 푸시 다운이란?

Index Condition Pushdwon(이하 ICP) 는 보조 인덱스를 활용하여 데이터를 가져오는 쿼리문에서 사용하는 최적화 전략이다.

다음과 같이 진행되는 최적화를 ICP라고 한다.

1. 옵티마이저가 '범위를 줄일 수 없는 조건 컬럼이더라도' 보조 인덱스의 구성 요소로 존재하는 컬럼이면, 스토리지 엔진에게 전부 전달해준다.
2. 스토리지 엔진은 보조 인덱스를 탐색할 때, 옵티마이저에게 받은 조건 컬럼들로 최대한 유효하지 않은 튜플들을 걸러낸다.
3. 이를 통해 스토리지 엔진이 본 테이블을 읽는 횟수를 줄인다.

[!info] MySQL 엔진과 스토리지 엔진의 담당 업무
MySQL엔진은 옵티마이저를 이용한 실행 계획을 세우기도 하지만, 스토리지 엔진이 읽어들인 데이터를 받아 재가공 하는 작업도 함께 진행한다. (재가공에는 where 절 비교, Order by, Group By 처리 등이 있다.)
스토리지 엔진은 인덱스를 활용해 유효하지 않은 데이터를 걸러내고 본 테이블에서 실제 데이터를 읽어오는 작업을 담당한다.

2. ICP의 존재 유무에 따른 쿼리 실행 차이 비교

(0) 세팅

다음과 같은 테이블이 존재하고, 다음과 같은 쿼리를 실행한다고 하자.

CREATE TABLE emplyoees (
    emp_no int (PRIMARY),
    last_name VARCHAR(20),
    first_name VARCHAR(20),
    hire_date DATE
    INDEX idx_last_name_first_name (last_name, first_name);
)engine='innodb'

SELECT * FROM emplyees WHERE last_name='신' AND first_name LIKE '%일'

해당 쿼리문은 last_name으로 탐색 범위를 줄일 수 있으므로, (last_name, first_name) 복합 인덱스를 활용할 것이다. 다만, first_name 부분은 컬럼 값의 뒤쪽에 대한 조건이라 범위 제한 용도로 쓸 수가 없을 것이다.

(1) ICP가 비활성화 된 상황

ICP가 비활성화 되면, 다음과 같이 MySQL 엔진이 행동한다.

1. 스토리지 엔진은 인덱스 비교로 범위 줄이기 및 데이터 읽기를 하는 친구니까, 오로지 '인덱스로 범위를 줄일 수 있는 조건 컬럼만' 전송한다.
2. 스토리지엔진은 받은 조건 컬럼으로만 인덱스 필터링을 거치고 이후 유효한 튜플 모두 본 테이블 읽기를 통해 데이터를 가져온다.
3. MySQL 엔진이 데이터들을 받아서 Where 조건문 후처리를 진행한다.

위에서 설정한 예시일 경우, last_name 조건은 스토리지 엔진에게 전달되지만, first_name의 경우 인덱스에서 조건 필터링에 쓸 수 있음에도 범위를 줄일 수 있는 조건이 아니기 떄문에 전달 되지 않는다. 따라서 스토리지 엔진은 last_name으로만 범위를 줄인 뒤에 남는 모든 튜플에 대해 본 테이블 읽기를 진행한다.

이렇게 되면 불필요한 본 테이블 읽기가 늘어난다. 예시에서는 3개 중 2개이지만, 만약 10만명 중 성이 신이고 이름이 일 로 끝나는 사람이 1명이라면 99,999 개의 필요 없는 레코드를 읽는 행위가 된다.

[!info] 실행 계획 상 표시
ICP가 작동하지 않은 채 MySQL엔진에서 Where 문에 대한 후처리 작업을 했다면 실행 계획의 Extra 부분에 Using Where 라고 표시된다.

Using Where 는 인덱스를 활용할 수 없는 Where 조건문에 대하여 MySQL엔진이 스토리지 엔진으로부터 데이터를 받은 후에 일괄 Where 후처리 작업을 진행했을 시 표시된다.

(2) ICP가 활용되는 상황

ICP 최적화 전략은 범위를 제한할 수 없는 조건 컬럼이더라도 보조 인덱스에 존재하면 스토리지 엔진에 함께 전달한다.

따라서 위의 예시에서는 스토리지 엔진이 인덱스 비교 과정을 거칠 때 last_name 뿐만 아니라 first_name으로도 유효하지 않은 튜플 필터링 작업을 할 수 있다.

[!info] 실행 계획 상 표시
ICP 를 활용했다면, Extra 부분에 Using index Condition 이라고 표시 된다.

3. 핵심 요약

1. 인덱스 컨디션 푸시 다운은 보조 인덱스를 활용해야 하는 쿼리문을 최적화하는 전략이다.
2. 인덱스 범위를 줄일 수 없는 조건 컬럼이더라도 보조 인덱스에 존재한다면 스토리지 엔진에게 전부 알려주어, 스토리지 엔진이 최대한 유효하지 않은 튜플을 필터링을 하여 본 테이블에 접근하는 횟수를 줄이게 만드는 최적화 전략이다.

---

부록

A. 모르는 단어 정리

B. 참고 문서

• mysql 공식문서 - Index Condition Pushdown Optimization