Real MySQL 8.0 - 9.3 인덱스 컨디션 푸쉬다운

위 그림과 같이 인덱스 컨디션 푸쉬 다운을 활성화 하면,

복합인덱스 테이블에서 인덱스를 활용하지 못하는 조건이라도 인덱스 테이블 내에서 모든 체크 조건을 처리할 수 있으면, 불필요한 랜덤 디스크 I/O 읽기를 수행 하지 않는다.

 

인덱스 컨디션 푸쉬다운 활성화 여부 확인하기

optimizer_switch 시스템 변수 내 index_condition_pushdown 설정을 통해 index_condition_pushdown을 사용할지 아닐지를 제어할 수 있다.

set optimizer_switch='index_condition_pushdown=on';
show variables like 'optimizer_switch';

 

 

인덱스 컨디션 푸쉬다운 테스트 해보기

테스트용 테이블 준비 - 더미 csv 파일 import

https://media.githubusercontent.com/media/datablist/sample-csv-files/main/files/people/people-10000.zip

10,000 rows 준비

 

 

last_name, first_name 순으로 복합 인덱스를 생성 

ALTER TABLE people ADD INDEX ix_lastname_firstname (last_name, first_name);

 

index_condition_pushdown=off 결과

set optimizer_switch='index_condition_pushdown=off';
explain select * from people where last_name = 'Barrera' and first_name LIKE '%en';

 

index_condition_pushdown=on 결과

set optimizer_switch='index_condition_pushdown=on';
explain select * from people where last_name = 'Barrera' and first_name LIKE '%en';

인덱스 컨디션 푸쉬다운이 수행되었을 경우 실행계획의 Extra 컬럼에 Using index condition 이라는 항목이 표시됨을 확인 할 수 있었다.

 

 

인덱스 컨디션 푸쉬다운과 랜덤 디스크 I/O

최근 친절한 SQL 튜닝 강의를 수강 하면서, 쿼리 튜닝은 "랜덤 I/O를 줄이는 작업"이라는 말을 들었다.

 

분명히 인덱스를 제대로 설정해도, 막상 실행계획을 확인해보면 옵티마이저가 풀스캔 계획을 세우는 경우가 종종 있었다.

 

처음에는 왜 인덱스를 타지 않고 풀스캔을 하는 걸까? 데이터가 많아서 B-tree를 타는것(수직 탐색)이 오히려 오버헤드로 작동하는 걸까? 라고 막연히 생각했었다.

 

그런데 강의를 수강하면서 인덱스 테이블에서 실제 디스크로 부터 데이터를 1건 가져오는 작업은 랜덤 디스크 I/O고, 풀스캔의 경우 시퀀셜 I/O로 처음부터 끝까지 빠르게 가져온다는 것을 알게 되었다.

 

랜덤 디스크 I/O의 경우 이전 8장에서도 설명이 나왔듯이 디스크 헤드를 직접 다시 움직이는 작업이기 때문에 성능이 안좋다.

 

결국 아래 그림처럼 레인지 스캔의 범위가 크고, 인덱스 테이블에 없는 컬럼을 조회 한 경우 나머지 컬럼 데이터를 디스크에서 일일히 랜덤하게 가져와야 하기 때문에 랜덤 I/O가 아닌 시퀀셜 I/O로 처리되는 풀스캔으로 데이터를 처음부터 끝까지 전부 가져와 필요한 부분만 걸러내는 것이 더 빠를거라 판단했기 때문이다.

 

 

이러한 관점에서 보았을때, 인덱스 컨디션 푸쉬다운의 경우도 마찬가지로 이 랜덤 디스크 I/O를 자체를 직접적으로 줄여주기 때문에 성능 향상에 엄청난 이점이 있을 것 같다.

 

더 조사해 보고 싶은점

단순 실행계획 뿐만아니라 실제적으로 랜덤 디스크 I/O가 얼마나 발생했는지 확인해보고 싶다. MySQL에서는 어떤 방식으로 디스크로 부터 읽어온 블록수를 트레이스를 할 수 있는지 더 조사해봐야겠다.