MySQL5.6 인덱스 조건문 푸시 다운 최적화

8.2.1.6 인덱스 조건문 푸시 다운 최적화

인덱스 조건문 푸시 다운 (ICP)는 MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우의 최적화입니다. ICP를 사용하지 않는 경우 스토리지 엔진은 인덱스를 통과하여베이스 테이블에서 행을 검색하고 MySQL Server에 반환하고 MySQL Server가 행에 WHERE 조건을 평가합니다. ICP를 사용하면 인덱스의 필드만을 사용하여 WHERE 조건의 부분을 평가할 수있는 경우 MySQL Server는이 WHERE 조건의 부분을 스토리지 엔진에 푸시 다운합니다. 스토리지 엔진은 인덱스 항목을 사용하여 푸시 된 인덱스 조건을 평가하고이 충족되는 경우에만 테이블에서 행을 읽습니다. ICP는 스토리지 엔진이 기본 테이블에 액세스해야하는 횟수와 MySQL 서버가 스토리지 엔진에 액세스해야하는 횟수를 줄일 수 있습니다.

인덱스 조건문 푸시 다운 최적화는 전체 테이블 행에 액세스 할 필요가있는 경우에, range , ref , eq_ref 및 ref_or_null 접근 방식에 사용됩니다. 이 전략은 InnoDB 테이블과 MyISAM 테이블에 사용할 수 있습니다. (인덱스 조건문 푸시 다운은 MySQL 5.6에서 파티션 된 테이블에서 지원되지 않습니다.이 문제는 MySQL 5.7에서 해결되어 있습니다.) 그러나 InnoDB 테이블의 경우 ICP 보조 인덱스에만 사용됩니다 . ICP의 목표는 전체 레코드 읽기의 횟수를 줄이고,이를 통해 IO 작업을 줄일 수 있습니다. InnoDB 의 클러스터 된 인덱스의 경우 전체 레코드가 이미 InnoDB 버퍼에 읽힌 있습니다. 이 경우 ICP를 사용하여 IO는 감소되지 않습니다.

이 최적화 구조를 확인하려면 먼저 인덱스 조건문 푸시 다운이 사용되지 않는 경우 인덱스 스캔이 어떻게 진행되는지를 고찰합니다.

먼저 인덱스 튜플을 읽고 다음 그 인덱스 튜플을 사용하여 전체 테이블 행을 찾아 읽을하여 다음 행을 가져옵니다.
이 테이블에 적용되는 WHERE 조건의 부분을 테스트합니다. 테스트 결과에 따라 행을 허용하거나 거부합니다.

인덱스 조건문 푸시 다운이 사용되는 경우, 대신 검사는 다음과 같이 진행됩니다.

다음 행의 인덱스 튜플을 가져옵니다 (그러나 전체 테이블 행이 없습니다).
이 테이블에 적용되어 인덱스 컬럼만을 사용하여 확인할 수 WHERE 조건의 부분을 테스트합니다. 조건이 충족 될 경우 다음 행의 인덱스 튜플로 이동합니다.
조건이 충족 된 경우 인덱스 튜플을 사용하여 전체 테이블 행을 찾아 읽습니다.
이 테이블에 적용되는 WHERE 조건의 나머지 부분을 테스트합니다. 테스트 결과에 따라 행을 허용하거나 거부합니다.

인덱스 조건문 푸시 다운이 사용되면, EXPLAIN 출력의 Extra 컬럼에 Using index condition 표시됩니다. 전체 테이블 행을 읽을 필요가있는 경우에 적용되지 않기 때문에 Index only 는 표시되지 않습니다.

사람과 주소 정보를 저장하는 테이블이 있고 그 테이블에 INDEX (zipcode, lastname, firstname) 로 정의 된 인덱스가 있다고합니다. 개인의 zipcode 값을 알고 있지만 이름이 확실하지 않을 경우 다음과 같이 검색 할 수 있습니다.

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

MySQL은 인덱스를 사용하여 zipcode='95054' 을 가진 사람을 검색합니다. 두 번째 부분 ( lastname LIKE '%etrunia%' )는 스캔 할 필요가있는 행수를 제한하는 데 사용할 수 없기 때문에 인덱스 조건문 푸시 다운을 사용하지 않는 경우이 쿼리는 zipcode='95054' 을 가진 사람의 전체 테이블 행을 검색해야합니다.

인덱스 조건문 푸시 다운을 사용하면 MySQL은 전체 테이블 행을 읽기 전에 lastname LIKE '%etrunia%' 부분을 체크합니다. 그러면 lastname 조건에 일치하지 않는 모든 인덱스 튜플에 해당하는 전체 행 읽기를 피할 수 있습니다.

인덱스 조건문 푸시 다운은 기본적으로 활성화됩니다. 이것은 optimizer_switch 시스템 변수에서 index_condition_pushdown 플래그를 설정하여 제어 할 수 있습니다. 섹션 8.8.5.2 "전환 가능한 최적화 제어" 를 참조하십시오.