인덱스 컨디션 푸시다운(index_condition_pushdown)

인덱스 컨디션 푸시다운

인덱스 컨디션 푸시다운이란 인덱스 조건(Codition)을 스토리지 엔진으로 전달해주는 기능이다.

MySQL 5.6 버전부터 해당 기능이 도입되었는데, 어떠한 이점을 가져오는지 알아보도록 하자.

 

테이블 및 인덱스 생성

우선 인덱스 컨디션 푸시다운을 테스트하기 위해 employees 테이블을 생성해서 100건 정도의 데이터를 넣고, 아래와 같은 인덱스를 생성해주도록 한다.

CREATE TABLE employees (emp_no bigint not null primary key, first_name varchar(255), last_name varchar(255), hire_date datetime);
ALTER TABLE employees ADD INDEX temp_idx (last_name, first_name);

 

인덱스 컨디션 푸시다운 비교

우선 MySQL 옵티마이저 스위치 옵션에서 인덱스 컨디션 푸시다운 옵션을 껐을 때의 쿼리 실행 계획을 살펴보도록 하자.

SET optimizer_switch='index_condition_pushdown=off';
SHOW VARIABLES LIKE 'optimizer_switch';
Variable_name      Value
optimizer_switch   ..., index_condition_pushdown=off,...

그리고 다음과 같은 쿼리를 실행할 때 스토리지 엔진이 몇 건의 레코드를 읽는 지 알아보도록 하자.

SELECT * FROM employees WHERE last_name='Taylor' AND first_name LIKE '%Winston';

앞서 (last_name, first_name) 으로 인덱스를 생성하였기 때문에 WHERE 절의 last_name='Taylor' 조건은 temp_idx 인덱스를 레인지 스캔으로 사용할 수 있다. 하지만 first_name LIKE '%Winston' 조건은 와일드카드로 시작되는 LIKE 절이므로 인덱스가 적용되지 않기 때문에 위 쿼리는 last_name='Taylor' 조건으로 레코드들을 조회한 뒤, first_name LIKE '%Winston' 조건에 맞는 레코드인지 하나씩 비교해보아야 한다.

따라서 last_name='Talyor' 인 레코드들을 모두 읽어와야 한다는 것을 알 수 있다.

 

실제로 쿼리의 실행 계획을 확인해보면 다음과 같다.

EXPLAIN SELECT * FROM employees WHERE last_name='Taylor' AND first_name LIKE '%Winston';

Extra 컬럼에 "Using where" 이 나타나는 것을 알 수 있는데, 이는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미한다.

여기서는 InnoDB 스토리지 엔진이 반환해준 last_name='Taylor' 인 레코드들 중에서 first_name LIKE '%Winston' 조건에 맞는 레코드를 검사하는 과정을 의미하게 된다.

 

그런데 만약 last_name='Taylor' 인 레코드가 1만 건이고, 그 중 단 1건만 first_name LIKE '%Winston' 조건에 일치한다면 어떨까? 이 경우에는 9,999건의 레코드 읽기가 불필요한 작업이 된다.

또 여기서 한 번 더 생각해보면 first_name LIKE '%Winston' 조건을 처리하기 위해 이미 last_name='Taylor' 조건을 통해 읽은 temp_idx 인덱스의 first_name 컬럼을 이용하지 않고, 테이블의 레코드를 읽어서 비교하는 이유가 궁금해질 수 있다.

 

이는 MySQL 5.5 버전까지는 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name LIKE '%Winston' 과 같은 조건을 MySQL 엔진이 스토리지 엔진으로 아예 전달해주지 않았기 때문이다. 그래서 스토리지 엔진에서는 last_name='Taylor' 조건에 맞는 레코드를 모두 읽을 수 밖에 없게 되는 것이다.

 

하지만 MySQL 5.6 버전부터는 이렇게 범위 제한 조건으로 사용하지 못하는 조건이라도 인덱스에 포함된 컬럼이라면 모두 스토리지 엔진에 전달해서 꼭 필요한 레코드들만 읽을 수 있도록 개선되었다. (인덱스 컨디션 푸시다운의 도입)

 

이번에는 인덱스 컨디션 푸시다운 옵션을 키고, 동일한 쿼리의 실행 계획을 살펴보도록 하자.

SET optimizer_switch='index_condition_pushdown=on';
SHOW VARIABLES LIKE 'optimizer_switch';
Variable_name      Value
optimizer_switch   ..., index_condition_pushdown=on,...

EXPLAIN SELECT * FROM employees WHERE last_name='Taylor' AND first_name LIKE '%Winston';

위와 같이 옵티마이저 스위치에서 index_condition_pushdown을 활성화하고 쿼리의 실행 계획을 살펴보면 Extra 컬럼에 "Using where" 가 아닌 "Using index condition" 이 나타나는 것을 확인할 수 있다.

이제는 인덱스 조건을 이용해 스토리지 엔진에서 최대한 필터링한 데이터만 MySQL 엔진으로 전달할 수 있게 된다.

 

Reference

Real MySQL