[MySQL] Index Condition Pushdown 사용 방법 및 예시

[MySQL Index Condition Pushdown(ICP) 최적화 이해하기]

소개
MySQL에서 쿼리 성능을 높이기 위해 제공되는 최적화 기법 중 하나가 Index Condition Pushdown(ICP)입니다. ICP는 MySQL 5.6부터 지원된 기능으로, 쿼리 처리 과정에서 불필요한 데이터 접근을 줄여 효율성을 높이는 역할을 합니다. 이 글에서는 ICP가 무엇인지 원리와 예시를 통해 간단하고 명확하게 살펴보겠습니다.

주요 특징
- 불필요한 테이블 데이터 접근 최소화
- 인덱스 필터링 조건을 스토리지 엔진으로 전달하여 바로 처리
- 성능 향상 및 쿼리 응답시간 단축

사용 예시
다음 테이블을 가정하여 ICP가 어떻게 적용되는지 살펴보겠습니다.

테이블 생성 예시

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    salary INT,
    INDEX idx_name_salary (last_name, salary)
);

ICP가 적용 가능한 쿼리 예시
다음 쿼리를 실행한다고 가정합니다.

SELECT * FROM employees
WHERE last_name = 'Kim' AND salary > 5000;

여기서 ICP가 없으면 MySQL은 인덱스 'idx_name_salary'를 통해 last_name = 'Kim' 조건을 만족하는 모든 데이터에 접근한 후, 각 행의 테이블에 다시 접근해 salary > 5000 조건의 필터링 여부를 확인합니다.

그러나 ICP가 활성화된 경우 MySQL은 인덱스 수준에서 두 조건(last_name = 'Kim' 및 salary > 5000)을 동시에 평가하여, 인덱스 필터링을 통과한 레코드에 대해서만 실제 테이블 데이터에 접근합니다. 결과적으로 테이블 데이터 접근량과 디스크 IO가 줄어들어 성능 개선 효과가 있습니다.

ICP 활성화 여부 확인 
ICP 적용 여부는 쿼리 실행 계획(EXPLAIN) 으로 확인할 수 있습니다.

EXPLAIN SELECT * FROM employees
WHERE last_name = 'Kim' AND salary > 5000;

Extra 항목에 Using index condition라는 문구가 있으면 ICP가 적용된 것입니다.

예시 결과:

+-+-+--++++++-++--+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+-+-+--++++++-++--+
| 1  | SIMPLE      | employees | NULL       | ref  | idx_name_salary  | idx_name_salary  | 402     | const | 100  | Using index condition |
+-+-+--++++++-++--+

장단점 분석
장점:
- 테이블 데이터 접근 횟수 감소로 인해 쿼리 성능 향상
- 디스크 IO 감소로 전반적인 시스템 성능 개선 효과

단점:
- ICP는 모든 쿼리에서 동작하지는 않으며, 다중 열(복합) 인덱스를 사용할 때 제한적으로 효과를 나타냄
- 인덱스 설계가 잘못된 경우에는 효과가 미미하거나 오히려 성능 저하 요인이 될 수 있음

결론
MySQL의 ICP(Index Condition Pushdown) 최적화 기법은 인덱스를 더욱 효율적으로 사용하여 검색 성능을 크게 향상시킬 수 있는 좋은 수단입니다. 인덱스를 설계할 때 ICP를 고려한 적절한 구성과 쿼리 조건을 설정하면 데이터 조회 속도와 효율성 개선 효과를 기대할 수 있습니다.

ICP 적용 여부를 자주 확인하여 최적의 성능을 지속적으로 유지하는 것이 중요합니다.

참고 자료
- MySQL 공식 문서 - ICP 최적화
[https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html]