인덱스 컨디션 푸시다운의 목표는 불필요한 random I/O를 줄이는 일이며, 이는 조회 성능이 큰 영향을 미친다.
우선 MySQL 서버 구조를 보면 아래와 같다. MySQL엔진이 있고, 스토리지 엔진(InnoDB 등)으로 나뉘어져 있다.
select * from user where name=’doo’ and address like ‘%양천구%’; 위와 같은 쿼리를 수행해야 하고, 인덱스는 user_idx : (name, address)로 만들어져있다고 가정한다.
해당 쿼리를 수행하면, 이런 순서로 동작할 것이다.
- 스토리지 엔진에서 user_idx를 이용하여 name=’doo’인 1000개의 데이터를 디스크에서 조회하여 MySQL엔진으로 반환한다. (address는 인덱스를 탈 수 없으므로 조회 조건으로 사용하지 않는다.)
- MySQL엔진에선 address like ‘%양천구%’ 인 데이터를 filter하여 총 3개의 데이터를 클라이언트에게 최종 반환한다.
여기서 중요한 것은 실제로 클라이언트에게 반환된 최종 데이터는 3개이고, 스토리지 엔진에선 조건에 맞지 않는 997개의 데이터를 디스크에서 읽는 불필요한 작업(random I/O)을 했다는 것이다.
이런 일이 발생하는 이유는 MySQL 5.5 (MariaDB 5.2) 버전까지는 조회 조건(여기선 address)이 index로 사용할 수 없으면 스토리지 엔진으로 조회 조건을 전달할 수 없었기 때문이다.
→ 즉, 스토리지 엔진으로 name=’doo’ 조건만 전달되었을 뿐 address like ‘%양천구%’ 조건은 전달되지 않아서 불필요한 997개의 random I/O를 추가로 수행한 후 데이터를 MySQL 엔진으로 전달하였다.
- 그 이후 버전 (MySQL 5.6 / MariaDB 5.3) 부터는 인덱스 조건에 사용될 수 없어도, 인덱스에 포함된 조회 조건(컬럼)이라면 스토리지 엔진으로 조회 조건을 전달하여 최대한 스토리지 엔진에서 걸러낼 수 있도록 했다. (default 설정이
index_condition_pushdown=on이다.)- 인덱스 조건(index condition)을 스토리지 엔진으로 넘겨주기 때문에 index condition pushdown 이라고 부른다.
- random I/O는 데이터가 저장된 위치로 디스크 헤드를 움직여 데이터를 가져오는 것으로, 여러개의 데이터에 접근할 때 시간이 오래 걸리고 비효율적이다.
- 여러개의 데이터에 접근(읽기,쓰기)할 때 순차(sequential) I/O는 디스크 헤드를 한 번만 움직이지만, 랜덤(random) I/O는 디스크 헤더를 데이터의 개수만큼 움직여야한다.
- 디스크 작업 자체가 시간이 오래 걸리기도 하고, 디스크 헤드를 움직이는 시간은 디스크 작업 안에서 가장 많은 시간을 차지한다.
- DB에서 인덱스를 사용한 읽기는 주로 랜덤 I/O로 동작하고, 풀 테이블 스캔은 순차 I/O로 동작한다.
- 하지만 인덱스를 사용이 가능할 때에도 조회된 데이터가 많으면, 랜덤 I/O 방식은 오히려 시간이 더 많이 걸릴 수 있으므로 순차 I/O 방식으로 동작하는 풀 테이블 스캔을 선택할 수 있음
- select 할때 index에 있는 컬럼만 조회하면 디스크를 읽을 필요가 없어서 디스크 I/O가 발생하지 않아 시간이 많이 단축된다. → 커버링 인덱스
[ 참고자료 ]
안녕하세요