MySQL 5.6 ICP 特性深度解析：Index Condition Pushdown 如何減少回表 IO

什麼是 ICP？

Index Condition Pushdown (ICP) 是 MySQL 5.6 引入的存儲引擎層優化：將部分 WHERE 條件「下推」到存儲引擎，利用索引列在引擎內部過濾，**減少回表次數與 Server-Engine 交互**。

對比：無 ICP vs 有 ICP

實戰驗證：employees 表測試

-- 索引 idx_emp_fnln (first_name, last_name)
SELECT * FROM employees 
WHERE first_name='Anneke' AND last_name LIKE '%sig';

最左前綴原則：first_name 可用索引，last_name LIKE '%sig' 無法用索引範圍掃描。

開啟 ICP（默認）

SET optimizer_switch='index_condition_pushdown=on';
-- 耗時 0.00060275s
-- Sending data: 0.000189s
-- EXPLAIN Extra: Using index condition

關閉 ICP

SET optimizer_switch='index_condition_pushdown=off';
-- 耗時 0.00097000s (慢 61%)
-- Sending data: 0.000735s (慢 3.9倍)
-- EXPLAIN Extra: Using where

案例分析

• 關閉 ICP：引擎按 first_name='Anneke' 讀 225 行 → 全部回表 → Server 層過濾 last_name LIKE '%sig' → 僅 1 行符合。**224 次無效回表**。
• 開啟 ICP：引擎讀索引元組，直接在引擎層對比 last_name LIKE '%sig'，**僅 1 行回表**。IO 與交互大幅下降。

ICP 使用限制（必須知曉）

1. 僅支援 range/ref/eq_ref/ref_or_null 訪問類型
2. 支援 InnoDB、MyISAM
3. 僅限二級索引，主鍵聚簇索引不適用
4. WHERE 條件中**非索引列**仍需 Server 層過濾（虛線 Using where）
5. 加速效果取決於「引擎層過濾掉的比例」
6. MySQL 5.6 不支援分區表 ICP，5.7+ 支援
7. 覆蓋索引場景不觸發 ICP（Extra 顯示 Using where; Using index）

驗證覆蓋索引不觸發 ICP

-- 非覆蓋：觸發 ICP
EXPLAIN SELECT * FROM employees WHERE first_name='Anneke' AND last_name='Porenta';
-- Extra: Using index condition

-- 覆蓋索引：不觸發 ICP
EXPLAIN SELECT first_name,last_name FROM employees WHERE first_name='Anneke' AND last_name='Porenta';
-- Extra: Using where; Using index

最佳實踐建議

1. 確保 optimizer_switch='index_condition_pushdown=on'（默認開啟）
2. 為高頻查詢設計**包含過濾列的複合索引**，讓更多條件下推
3. 避免在 ICP 生效關鍵查詢上強行使用覆蓋索引（除非覆蓋索引收益更大）
4. 定期 EXPLAIN 檢查 Using index condition 是否出現