Skip to content
hyoguoo.log
Mysql

Index Condition Pushdown

- Last update:
hyoguoo
Backend Engineer

인덱스 컨디션 푸시다운(ICP)은 MySQL 5.6부터 도입된 옵티마이저의 최적화 기능으로, 인덱스 스캔 효율을 극대화하는 기술이다.

  • 테이블의 실제 데이터까지 접근하기 전에 인덱스만으로 WHERE 절의 일부 조건을 필터링하는 기능
  • 불필요한 테이블 데이터 접근(Random I/O)과 MySQL 서버 - 스토리지 엔진 간의 데이터 전송량을 줄여 쿼리 성능을 향상

ICP의 원리

Section titled “ICP의 원리”

일반적으로 MySQL에서 쿼리를 실행할 때, WHERE 절의 조건을 평가하기 위해 스토리지 엔진은 다음과 같은 과정을 거친다.

  1. 스토리지 엔진이 인덱스를 통해 레코드의 위치를 찾은 후 해당 레코드 전체를 읽어와 서버 계층으로 전달
  2. 이후 서버 계층에서 WHERE 절의 나머지 조건들을 평가

ICP를 적용하면, 스토리지 엔진이 인덱스에 저장된 컬럼 정보만으로 WHERE 절의 조건을 자체적으로 평가하게 되면서 다음과 같은 이유로 성능이 향상된다.

  1. 자체적으로 WHERE 절의 조건을 평가
  2. 해당 레코드의 실제 데이터를 읽지 않음
  3. I/O 비용 절감
  4. 서버 계층으로 전달되는 데이터 양 감소
  5. 데이터 전송 비용 절감

ICP 비활성화 시 동작 방식

Section titled “ICP 비활성화 시 동작 방식”
  1. 인덱스 탐색 (스토리지 엔진): 인덱스를 사용해 WHERE 조건의 첫 부분과 일치하는 레코드의 위치(PK 또는 주소) 탐색
  2. 테이블 접근 및 전송 (스토리지 엔진 -> 서버): 찾은 위치를 기반으로 테이블에서 전체 레코드를 읽어 MySQL 서버 계층으로 전송
  3. 조건 필터링 (서버): MySQL 서버는 전송받은 전체 레코드에 대해 WHERE 절의 나머지 조건들을 평가하여 최종 결과를 필터링

이 방식은 인덱스 조건에 부합하는 후보 데이터가 많을 경우, 최종적으로 필터링되어 버려질 데이터를 불필요하게 디스크에서 읽고 서버로 전송하여 성능 저하를 유발할 수 있다.

ICP 활성화 시 동작 방식

Section titled “ICP 활성화 시 동작 방식”

ICP는 스토리지 엔진이 더 많은 역할을 수행하도록 WHERE 조건의 일부를 내려보낸다(Pushdown).

  1. 인덱스 탐색 및 조건 필터링 (스토리지 엔진): 인덱스를 탐색하면서, 인덱스 내에 포함된 컬럼에 대한 조건까지 함께 평가
  2. 테이블 접근 (스토리지 엔진): 인덱스 단계에서 모든 조건을 만족시킨 레코드에 대해서만 테이블 데이터에 접근
  3. 최종 데이터 전송 (스토리지 엔진 -> 서버): 최종적으로 필터링된 레코드만 MySQL 서버 계층으로 전송

결과적으로, 스토리지 엔진이 데이터 접근 자체를 최소화하므로 I/O 비용과 데이터 전송 비용이 크게 절감된다.

예시 쿼리

Section titled “예시 쿼리”
-- `employees` 테이블에 `(last_name, first_name)` 순서의 복합 인덱스 존재
SELECT *
FROM employees
WHERE last_name = 'Ogu'
  AND first_name LIKE 'J%';
  • ICP 비활성화 시:
    1. 스토리지 엔진은 인덱스를 통해 last_name = 'Ogu'인 모든 레코드(예: 1,000건) 조회
    2. 이 1,000건의 레코드 전체를 테이블에서 읽어 MySQL 서버로 전송
    3. MySQL 서버는 전달받은 1,000건의 데이터에 대해 first_name LIKE 'J%' 조건을 적용하여 최종 결과 반환(990건의 불필요한 I/O와 데이터 전송 발생)
  • ICP 활성화 시:
    1. 스토리지 엔진은 인덱스를 통해 last_name = 'Ogu'인 범위를 스캔
    2. 스캔하는 동시에, 인덱스에 포함된 first_name 컬럼의 값을 보고 first_name LIKE 'J%' 조건까지 함께 평가
    3. 두 조건을 모두 만족하는 10건의 레코드에 대해서만 테이블 접근을 수행
    4. 최종 10건의 데이터만 MySQL 서버로 전송(990건의 불필요한 I/O와 데이터 전송 방지)
    5. MySQL 서버는 전달받은 10건의 데이터에 대해 추가 조건이 없으므로 바로 최종 결과 반환

실행 계획의 Extra 컬럼에 ‘Using index condition’ 문구가 표시되면 ICP가 활성화된 것이다.

EXPLAIN
SELECT *
FROM employees
WHERE last_name = 'Ogu'
  AND first_name LIKE 'J%';


-- 결과의 Extra 컬럼
+-----------------------+
| Extra                 |
+-----------------------+
| Using index condition |
+-----------------------+

Last updated:

MySQL