인덱스 컨디션 푸시다운(ICP) 전략이 뭐야?

인덱스 컨디션 푸시다운(Index Condition Pushdown, ICP)은 MySQL 5.6부터 도입된 쿼리 최적화 기법으로, WHERE 절의 조건 중 인덱스에 포함된 컬럼에 대한 조건을 스토리지 엔진 레벨에서 평가합니다.

기존에는 MySQL 서버가 모든 레코드를 가져온 후 WHERE 조건을 평가했지만, ICP를 사용하면 스토리지 엔진이 인덱스를 읽는 단계에서 조건을 먼저 체크하여 불필요한 레코드 읽기를 줄입니다.

MySQL 아키텍처: 서버 레이어 vs 스토리지 엔진 레이어

• MySQL 서버 레이어: 쿼리 파싱, 최적화, WHERE 조건 평가 담당
• 스토리지 엔진 레이어: 실제 데이터 저장 및 인덱스 관리 (InnoDB, MyISAM 등)

전통적으로는 스토리지 엔진이 인덱스로 레코드를 찾아 MySQL 서버에 전달하면, 서버가 WHERE 조건을 평가했습니다. ICP는 이 평가 단계를 스토리지 엔진으로 "푸시다운(밀어넣기)" 합니다.

복합 인덱스와 인덱스 레인지 스캔

복합 인덱스 INDEX(a, b, c)에서 WHERE a=1 AND b LIKE '%test' 쿼리는:

• a=1: 인덱스 레인지 스캔 가능 (선두 컬럼)
• b LIKE '%test': 레인지 스캔 불가 (와일드카드가 앞에 있음)

ICP 없이는 a=1인 모든 레코드를 테이블에서 읽어야 하지만, ICP를 사용하면 인덱스에서 b 조건도 먼저 체크합니다.

ICP 없이 (기존 방식)

1. 스토리지 엔진: 인덱스에서 a=1인 PK 찾기
2. 스토리지 엔진: 모든 PK에 대해 테이블 레코드 읽기 (랜덤 I/O)
3. MySQL 서버: 읽어온 레코드에서 b LIKE '%test' 평가
4. 최종 결과 반환

ICP 적용 시

1. MySQL 서버: WHERE 조건을 스토리지 엔진에 전달
2. 스토리지 엔진: 인덱스에서 a=1인 레코드 찾기
3. 스토리지 엔진: 인덱스에서 b LIKE '%test' 조건 평가
4. 스토리지 엔진: 조건을 만족하는 레코드만 테이블에서 읽기
5. 최종 결과 반환

성능 개선 효과

• 테이블 랜덤 I/O 감소 (가장 큰 이득)
• MySQL 서버 ↔ 스토리지 엔진 간 데이터 전송량 감소
• CPU 사용량 감소 (서버 레벨 필터링 줄어듦)

실행 계획 확인

EXPLAIN SELECT * FROM employees
WHERE first_name = 'John' AND last_name LIKE '%son';

-- Extra 컬럼: Using index condition

ICP가 적용되는 조건

• 복합 인덱스에서 일부 컬럼만 레인지 스캔 가능할 때
• InnoDB, MyISAM 스토리지 엔진 지원
• range, ref, eq_ref 접근 타입
• 인덱스에 포함된 컬럼의 조건만 푸시다운 가능

ICP가 적용되지 않는 경우

• 커버링 인덱스 (이미 최적, 테이블 액세스 없음)
• 서브쿼리 조건
• 저장 프로시저/함수 참조

옵티마이저 스위치

-- ICP 상태 확인
SHOW VARIABLES LIKE 'optimizer_switch';

-- ICP 비활성화 (테스트용)
SET optimizer_switch='index_condition_pushdown=off';

-- ICP 활성화 (기본값)
SET optimizer_switch='index_condition_pushdown=on';

ICP는 조건 평가를 스토리지 엔진으로 밀어넣어 불필요한 테이블 액세스를 획기적으로 줄입니다.