Index Condition Pushdown: ICP가 적용되는 조건과 효과
MySQL Index Condition Pushdown이 보조 인덱스 탐색과 테이블 row 접근 비용을 줄이는 원리와 적용 조건을 정리한다.
Index Condition Pushdown: ICP가 적용되는 조건과 효과
Index Condition Pushdown은 MySQL 실행 엔진이 인덱스를 읽은 뒤 모든 후보 row를 즉시 테이블 row로 가져오지 않고, 인덱스 안에 들어 있는 컬럼 조건을 스토리지 엔진 단계에서 먼저 평가하게 하는 최적화다. EXPLAIN의 Extra에 Using index condition이 보이면 이 최적화가 적용된 것이다.
운영 관점에서 ICP는 “인덱스를 더 잘 타게 한다”는 표현보다 조금 더 정확히 이해해야 한다. ICP는 접근 범위 자체를 무조건 줄이는 기능이 아니다. 범위 탐색으로 이미 선택된 인덱스 엔트리들 중에서, 테이블 row를 읽기 전에 버릴 수 있는 후보를 스토리지 엔진이 먼저 걸러 내는 기능이다. 따라서 효과는 특히 보조 인덱스의 range scan에서 크게 나타난다. InnoDB 보조 인덱스는 인덱스 엔트리만 읽은 뒤 필요하면 clustered index로 다시 가서 전체 row를 읽어야 하므로, 그 두 번째 접근을 줄일 수 있으면 I/O와 buffer pool 압박이 줄어든다.
이 글에서는 ICP가 SQL 실행 경로에서 어느 위치에 있는지, 어떤 조건에서 적용되는지, EXPLAIN에서 어떻게 확인하는지, 그리고 운영자가 어떤 오해를 피해야 하는지를 정리한다. 예제는 MySQL 8.0 기준으로 검증 가능한 형태를 사용한다.
1. ICP는 옵티마이저와 스토리지 엔진 사이의 필터링 위치를 바꾼다
MySQL은 SQL을 파싱하고 옵티마이저가 실행 계획을 선택한 뒤, executor가 스토리지 엔진 API를 호출해 row를 읽는다. ICP가 없으면 보조 인덱스에서 후보 엔트리를 찾은 뒤, 조건 평가에 필요한 컬럼이 테이블 row에 있을 수 있으므로 먼저 row를 가져오고 서버 계층에서 조건을 평가하는 방식이 된다.
ICP가 적용되면 조건식 중 “인덱스 엔트리만 보고 판단할 수 있는 부분”이 스토리지 엔진 쪽으로 내려간다. 스토리지 엔진은 보조 인덱스 엔트리를 읽으면서 해당 조건을 먼저 평가하고, 통과한 엔트리에 대해서만 clustered index lookup을 수행한다.
graph TD
A[Executor가 보조 인덱스 range scan 요청] --> B[InnoDB가 보조 인덱스 엔트리 읽기]
B --> C{인덱스 컬럼만으로 평가 가능한 조건인가}
C -- ICP 적용 --> D[스토리지 엔진에서 index condition 평가]
D -- 조건 불일치 --> E[clustered index lookup 생략]
D -- 조건 일치 --> F[PK로 clustered index row 읽기]
C -- ICP 불가 --> F
F --> G[서버 계층에서 나머지 WHERE 평가]
G --> H[결과 반환]
여기서 핵심은 “조건 전체가 내려가는 것이 아니라, 인덱스 컬럼만으로 평가 가능한 조건 일부가 내려간다”는 점이다. 예를 들어 보조 인덱스가 (status, created_at, customer_id)이고 쿼리가 status, created_at, customer_id, amount를 조건에 사용한다면, amount가 인덱스에 없을 때 그 조건은 ICP로 내려갈 수 없다. 반면 customer_id 조건은 인덱스 엔트리에 있으므로 range scan 과정에서 미리 평가될 수 있다.
2. ICP가 특히 의미 있는 실행 경로
ICP는 모든 쿼리에 같은 가치를 주지 않는다. 다음 조건이 맞을수록 운영상 효과가 커진다.
| 조건 | ICP 효과 |
|---|---|
| 보조 인덱스 range scan이 발생한다 | 후보 인덱스 엔트리 중 일부를 테이블 row 읽기 전에 버릴 수 있다 |
| 조건 컬럼이 인덱스에 포함되어 있다 | 스토리지 엔진이 인덱스 엔트리만으로 조건을 평가할 수 있다 |
| select list 또는 다른 조건 때문에 테이블 row 접근이 필요하다 | clustered index lookup 절감 효과가 생긴다 |
| range 조건 뒤쪽 key part에 필터 조건이 있다 | range 범위 자체는 넓어도 후속 key part로 후보를 줄일 수 있다 |
| 후보 row 중 실제 통과 비율이 낮다 | 불필요한 row fetch가 많이 줄어든다 |
반대로 covering index만으로 쿼리가 끝나면 이미 테이블 row를 읽지 않으므로 ICP의 체감 이익이 작거나 Using index로 표현될 수 있다. 또한 PK clustered index 접근에서는 row 자체가 이미 인덱스와 같이 있으므로 보조 인덱스에서 PK로 다시 가는 비용 절감이라는 ICP의 전형적 장점이 줄어든다.
3. 적용 조건을 예제로 확인하기
다음 예제는 주문 테이블을 만들고, (status, created_at, customer_id) 보조 인덱스를 사용한다. 쿼리는 status와 created_at으로 범위를 잡고, customer_id를 추가 조건으로 둔다. amount는 인덱스에 없기 때문에 결과를 만들려면 최종적으로 테이블 row 접근이 필요하다. 이때 customer_id 조건은 인덱스 엔트리에 들어 있으므로 ICP 후보가 된다.
DROP TABLE IF EXISTS icp_orders;
CREATE TABLE icp_orders (
id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
memo VARCHAR(100) NULL,
KEY idx_status_created_customer (status, created_at, customer_id)
) ENGINE=InnoDB;
INSERT INTO icp_orders VALUES
(1, 101, 'PAID', '2026-01-01 10:00:00', 120.00, 'normal order'),
(2, 102, 'PAID', '2026-01-02 10:00:00', 80.00, 'normal order'),
(3, 103, 'PAID', '2026-01-03 10:00:00', 95.00, 'normal order'),
(4, 104, 'PAID', '2026-01-04 10:00:00', 150.00, 'target order'),
(5, 105, 'PAID', '2026-01-05 10:00:00', 60.00, 'normal order'),
(6, 104, 'PAID', '2026-02-01 10:00:00', 210.00, 'target order'),
(7, 104, 'PENDING', '2026-02-02 10:00:00', 70.00, 'not paid'),
(8, 106, 'PAID', '2026-02-03 10:00:00', 50.00, 'normal order'),
(9, 104, 'CANCELLED', '2026-02-04 10:00:00', 30.00, 'cancelled'),
(10, 107, 'PAID', '2026-02-05 10:00:00', 45.00, 'normal order');
ANALYZE TABLE icp_orders;
EXPLAIN
SELECT id, amount, memo
FROM icp_orders
WHERE status = 'PAID'
AND created_at >= '2026-01-01'
AND created_at < '2026-03-01'
AND customer_id = 104;
DROP TABLE icp_orders;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE icp_orders (...);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO icp_orders VALUES (...);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE icp_orders;
+----------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| mysql_tech_note.icp_orders | analyze | status | OK |
+----------------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id, amount, memo FROM icp_orders ...;
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | icp_orders | NULL | range | idx_status_created_customer | idx_status_created_customer | 79 | NULL | 8 | 10.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE icp_orders;
Query OK, 0 rows affected (0.00 sec)
위 예제에서 중요한 부분은 EXPLAIN의 Extra다. 환경과 minor version에 따라 비용 수치와 rows 추정값은 달라질 수 있지만, ICP가 적용되면 Using index condition이 표시된다. 이 표시는 MySQL 서버 계층이 아니라 스토리지 엔진 단계에서 인덱스 조건을 평가한다는 의미다.
4. Using where, Using index, Using index condition의 차이
EXPLAIN Extra는 짧은 문구라서 오해하기 쉽다. 특히 다음 세 표현은 서로 다르다.
| Extra 표현 | 의미 | 운영 해석 |
|---|---|---|
Using where |
서버 계층에서 WHERE 조건을 평가한다 | 조건이 있다는 뜻이지, 나쁜 계획이라는 뜻은 아니다 |
Using index |
covering index로 테이블 row 접근 없이 처리한다 | 보통 I/O 관점에서 유리하지만 인덱스 폭과 쓰기 비용도 고려해야 한다 |
Using index condition |
인덱스 조건 일부를 스토리지 엔진에서 먼저 평가한다 | 보조 인덱스 후보 중 불필요한 row fetch를 줄일 수 있다 |
중요한 점은 Using index condition이 Using index와 같은 뜻이 아니라는 것이다. Using index는 필요한 컬럼이 모두 인덱스에 있어 테이블 row를 읽지 않는 covering plan을 뜻한다. Using index condition은 테이블 row를 읽을 필요가 있지만, 읽기 전에 인덱스 엔트리 조건으로 일부 후보를 제거한다는 뜻이다.
다음 쿼리는 ICP 관련 최적화 스위치가 켜져 있는지 확인한다. 운영 환경에서는 세션 단위 테스트로만 바꾸고, 글로벌 변경은 변경 관리 절차를 거쳐야 한다.
SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_enabled,
SUBSTRING_INDEX(
SUBSTRING_INDEX(@@session.optimizer_switch, 'index_condition_pushdown=', -1),
',',
1
) AS icp_setting;
실행 결과(MySQL 8.0.x):
mysql> SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_enabled,
-> SUBSTRING_INDEX(
-> SUBSTRING_INDEX(@@session.optimizer_switch, 'index_condition_pushdown=', -1),
-> ',',
-> 1
-> ) AS icp_setting;
+-------------+-------------+
| icp_enabled | icp_setting |
+-------------+-------------+
| 1 | on |
+-------------+-------------+
1 row in set (0.00 sec)
optimizer_switch에는 여러 최적화 플래그가 들어 있다. 위 쿼리는 전체 값을 길게 출력하지 않고 ICP 항목만 확인한다. ICP는 기본적으로 켜져 있으며, 특정 쿼리 분석 중 비교가 필요할 때만 세션에서 일시적으로 끄고 EXPLAIN 차이를 확인하는 방식이 안전하다.
5. ICP 적용 여부를 비교할 때의 주의점
ICP의 효과를 확인하려고 SET optimizer_switch='index_condition_pushdown=off'로 끄고 비교하는 경우가 있다. 이 방법은 학습과 진단에는 유용하지만 운영 변경의 결론으로 바로 연결하면 안 된다. ICP를 끄면 단순히 한 최적화만 사라지는 것처럼 보이지만, 옵티마이저 비용 계산과 plan 선택이 함께 바뀔 수 있다. 비교하려면 같은 세션, 같은 통계, 같은 데이터 분포, 같은 바인드 값 조건을 맞추어야 한다.
다음 예제는 ICP 스위치가 실제로 세션에서 변경되는지 확인하는 최소 검증이다. 운영 SQL을 직접 실행하지 않고도 현재 세션의 비교 실험 준비가 되었는지 볼 수 있다.
SET @old_optimizer_switch = @@session.optimizer_switch;
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=off%' AS icp_disabled;
SET SESSION optimizer_switch = @old_optimizer_switch;
SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_restored;
실행 결과(MySQL 8.0.x):
mysql> SET @old_optimizer_switch = @@session.optimizer_switch;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION optimizer_switch = 'index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=off%' AS icp_disabled;
+--------------+
| icp_disabled |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SET SESSION optimizer_switch = @old_optimizer_switch;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_restored;
+--------------+
| icp_restored |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
이 예제는 스위치 조작 자체를 보여주기 위한 것이다. 실제 성능 비교는 EXPLAIN, EXPLAIN ANALYZE, Handler_read% 상태값, Performance Schema statement 통계를 함께 봐야 한다. 단일 SQL의 실행 시간이 짧거나 buffer pool에 이미 데이터가 올라와 있으면 ICP 효과가 실행 시간으로는 잘 드러나지 않을 수 있다.
6. Aurora MySQL에서의 해석
Aurora MySQL은 MySQL 호환 optimizer와 SQL 계층을 제공하므로 ICP 자체의 의미와 EXPLAIN 표시는 일반 MySQL과 같은 방식으로 이해할 수 있다. 다만 운영 해석에서는 Aurora의 분산 스토리지 구조와 replica 활용 방식을 고려해야 한다.
첫째, Aurora에서도 불필요한 row fetch를 줄이는 것은 buffer cache와 스토리지 요청량을 낮추는 데 도움이 된다. 특히 넓은 보조 인덱스 range scan에서 조건 선택도가 낮으면 ICP가 없는 계획보다 page 접근과 CPU 사용이 줄 수 있다. 둘째, Aurora read replica에서 분석 쿼리를 실행할 때도 ICP는 replica의 SQL 실행 경로에 영향을 준다. primary 부하를 피하려고 replica로 보냈더라도, 나쁜 range scan은 replica lag와 캐시 오염을 만들 수 있다. 셋째, Aurora는 Performance Insights와 CloudWatch 지표로 wait와 SQL 부하를 관찰할 수 있으므로, EXPLAIN의 Using index condition만 보고 충분하다고 판단하지 말고 실제 대기 이벤트, rows examined, digest 단위 지표를 함께 확인해야 한다.
7. 장애와 성능 리스크에서 자주 나오는 오해
7.1 ICP가 인덱스 설계를 대신하지 않는다
ICP는 이미 선택된 인덱스 엔트리 범위 안에서 후보를 줄이는 기능이다. 선행 컬럼 선택도가 나쁘고 range 범위가 지나치게 넓다면, ICP가 적용되어도 많은 인덱스 페이지를 읽어야 한다. 예를 들어 (status, created_at, customer_id) 인덱스에서 status='PAID'가 전체의 대부분이고 날짜 범위가 1년이면, customer_id 조건이 ICP로 내려가더라도 긴 range scan 자체는 남는다. 이런 경우에는 조건 패턴에 맞춰 (customer_id, status, created_at) 같은 다른 인덱스가 더 적절할 수 있다.
7.2 Using index condition이 항상 빠르다는 뜻은 아니다
Using index condition은 최적화 적용 여부를 보여줄 뿐, 충분한 성능을 보장하지 않는다. 후보 범위가 매우 크면 여전히 느릴 수 있다. 또한 ICP 평가 자체도 CPU를 사용한다. 보통 row fetch 절감 이익이 더 크지만, 매우 작은 테이블이나 거의 모든 후보가 통과하는 조건에서는 체감 차이가 작다.
7.3 함수와 표현식 조건은 sargability와 함께 보아야 한다
조건식이 인덱스 컬럼을 함수로 감싸면 range 접근 자체가 어려워질 수 있다. ICP가 일부 조건을 내려줄 수 있더라도, 선행 접근 조건이 sargable하지 않으면 큰 이득을 기대하기 어렵다. 예를 들어 DATE(created_at) = '2026-01-01'보다 created_at >= '2026-01-01' AND created_at < '2026-01-02'가 인덱스 range 접근에 더 적합하다.
7.4 covering index와 ICP는 선택지가 다르다
쿼리 빈도가 높고 select list가 좁다면 covering index가 더 큰 효과를 낼 수 있다. 그러나 covering index를 위해 많은 컬럼을 추가하면 인덱스 크기, buffer pool 점유, DML 비용이 증가한다. ICP는 covering index처럼 row fetch를 완전히 없애지는 못하지만, 인덱스 폭을 과도하게 키우지 않고도 일부 row fetch를 줄일 수 있는 절충점이 될 수 있다.
8. 운영 진단 절차
ICP가 성능 문제와 관련되어 보일 때는 다음 순서로 판단한다.
- 문제가 되는 SQL의 실제 바인드 값과 빈도를 확인한다.
EXPLAIN으로type,key,key_len,rows,filtered,Extra를 확인한다.Extra에Using index condition이 있는지 확인하되, 그것만으로 결론을 내리지 않는다.- 조건 컬럼이 선택된 인덱스의 어느 key part에 있는지 확인한다.
- range 조건이 너무 넓은지, 후속 key part 조건이 ICP로만 처리되고 있는지 본다.
EXPLAIN ANALYZE를 실행할 수 있는 환경이면 추정 rows와 actual rows 차이를 확인한다.- 필요하면 세션 단위로 ICP on/off를 비교하되, 운영 트래픽에서 글로벌로 끄지 않는다.
- 인덱스 순서 변경, 별도 인덱스 추가, covering index, 쿼리 조건 재작성 중 어느 선택지가 쓰기 비용과 읽기 이익의 균형이 맞는지 검토한다.
9. 점검 체크리스트
-
EXPLAIN Extra의Using index condition을Using index
10. 결론
Index Condition Pushdown은 MySQL이 보조 인덱스 range scan에서 불필요한 테이블 row 접근을 줄이기 위해 사용하는 중요한 최적화다. 그러나 ICP는 인덱스 설계의 대체물이 아니며, 넓은 range scan을 근본적으로 좁혀 주는 기능도 아니다. Using index condition을 발견했다면 “좋다” 또는 “나쁘다”로 단정하기보다, 어떤 조건이 접근 범위를 만들고 어떤 조건이 스토리지 엔진 필터로 내려갔는지를 구분해야 한다.
다음 단계의 인덱스 튜닝에서는 ICP, covering index, composite index key order, histogram, cost model을 함께 보아야 한다. 특히 실제 운영에서는 EXPLAIN 문구 하나보다 rows examined, actual rows, wait event, DML 비용까지 포함한 균형 판단이 더 중요하다.