[MySQL] Index Condition Pushdown 최적화

ICP(Index Condition Pushdown)는 MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우를 위한 최적화입니다. ICP가 없으면 스토리지 엔진은 인덱스를 탐색하여 기본 테이블에서 행을 찾아 행의 WHERE 조건을 평가하는 MySQL 서버로 리턴합니다. ICP가 활성화 된 상태에서 인덱스의 컬럼만 사용하여 WHERE 조건의 일부를 평가할 수 있으면 MySQL 서버는 WHERE 조건의 이 부분을 스토리지 엔진으로 푸시 다운합니다. 그런 다음 스토리지 엔진은 인덱스 항목을 사용하여 푸시된 인덱스 조건을 평가하며, 이 조건을 만족하는 경우에만 테이블에서 읽은 행이 됩니다. ICP는 스토리지 엔진이 기본 테이블에 액세스해야하는 횟수와 MySQL 서버가 스토리지 엔진에 액세스해야하는 횟수를 줄일 수 있습니다.

 

인덱스 조건 푸시 다운 최적화의 적용에는 다음 조건이 적용됩니다.

-ICP는 전체 테이블 행에 액세스해야 할 때 range, ref, eq_ref 및 ref_or_null 액세스 방법에 사용됩니다.

-파티션된 InnoDB 및 MyISAM 테이블을 포함하여 InnoDB 및 MyISAM 테이블에 ICP를 사용할 수 있습니다.

-InnoDB 테이블의 경우 ICP는 보조 인덱스에만 사용됩니다. ICP의 목표는 전체 행 읽기수를 줄이고 따라서 I/O 작업을 줄이는 것입니다. InnoDB 클러스터형 인덱스의 경우 전체 레코드는 이미 InnoDB 버퍼로 읽 힙니다. 이 경우 ICP를 사용해도 I/O가 줄어들지 않습니다.

-ICP는 가상 생성 컬럼에서 생성된 보조 인덱스에서 지원되지 않습니다. InnoDB는 가상 생성 컬럼에서 보조 인덱스를 지원합니다.

-서브 쿼리를 참조하는 조건은 푸시 다운 될 수 없습니다.

-저장된 기능을 참조하는 조건은 푸쉬다운 할 수 없습니다. 스토리지 엔진은 저장된 기능을 호출 할 수 없습니다.

-트리거 된 조건을 푸시 다운 할 수 없습니다

 

이 최적화의 작동 방식을 이해하려면 먼저 인덱스 조건 푸시 다운을 사용하지 않을때 인덱스 스캔이 진행되는 방법을 고려합니다.

1. 먼저 인덱스 튜플을 읽은 다음 인덱스 튜플을 사용하여 전체 테이블 행을 찾아서 다음 행을 가져옵니다.

2.이 표에 적용되는 WHERE 조건의 일부를 테스트합니다. 테스트 결과에 따라 행을 승인하거나 거부하니다.

 

인덱스 조건 푸시 다운을 사용하면 스캔이 다음과 같이 대신 진행됩니다.

1. 다음 행의 인덱스 튜플을 가져옵니다 (전체 테이블 행은 아님).

2.이 테이블에 적용되며 인덱스 컬럼만 사용하여 확인할 수 있는 WHERE 조건 부분을 테스트합니다. 조건이 만족되지 않으면 다음 행의 인덱스 튜플로 진행합니다.

3. 조건이 만족되면 인덱스 튜플을 사용하여 전체 테이블 행을 찾아서 읽습니다.

4.이 표에 적용되는 WHERE 조건의 나머지 부분을 테스트합니다. 테스트 결과에 따라 행을 승인하거나 거부합니다.

 

EXPLAIN 출력은 인덱스 조건 푸시 다운이 사용될 때 추가 컬럼에 인덱스 조건 사용을 표시합니다. 전체 테이블 행을 읽어야 할 때 적용되지 않으므로 인덱스 사용이 표시되지 않습니다.

 

테이블에 사람과 주소에 대한 정보가 있고 테이블에 INDEX (우편 번호, 성, 이름)로 정의된 색인이 있다고 가정합니다. 개인의 우편 번호 값을 알고 있지만 성에 대해 잘 모르면 다음과 같이 검색 할 수 있습니다.

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

 

MySQL은 인덱스를 사용하여 zipcode='95054'인 사람들을 검색할 수 있습니다. 두번째 부분 (성 LIKE '% etrunia %')을 사용하여 스캔해야 하는 행 수를 제한할 수 없으므로 인덱스 조건 푸시 다운이 없으면 이 쿼리는 zipcode = '95054'인 모든 사람에 대한 전체 테이블 행을 검색해야 합니다.

 

인덱스 조건 푸시 다운을 통해 MySQL은 전체 테이블 행을 읽기 전에 LIKE '% etrunia %'부분을 확인합니다. 이렇게하면 우편 번호 조건과 일치하지만 성 조건과 일치하지 않는 색인 튜플에 해당하는 전체 행을 읽지 않아도 됩니다.

 

인덱스 조건 푸시 다운은 기본적으로 활성화되어 있습니다. index_condition_pushdown 플래그를 설정하여 optimizer_switch 시스템 변수로 제어 할 수 있습니다.

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';