MySQL Index Condition Pushdown (ICP) 索引條件下推優化原理與實戰

MySQL Index Condition Pushdown (ICP) 深度解析

Index Condition Pushdown (ICP) 是 MySQL 5.6 引入的重要優化特性，針對使用二級索引查詢的場景，將部分 WHERE 條件下推至儲存引擎層過濾，大幅減少回表次數與網路交互。

一、ICP 核心原理

未啟用 ICP 時：儲存引擎遍歷索引 → 定位基表讀取完整行 → 回傳 Server 層 → Server 評估 WHERE 條件 → 不滿足則丟棄。

啟用 ICP 後：Server 將可由索引欄位評估的條件下推 → 儲存引擎讀取索引項 → 直接用索引欄位判斷 → 僅滿足條件時才回表讀取完整行 → 回傳 Server。

二、實戰測試與效能對比

使用官方 employees 測試資料庫，建立複合索引 index_bh (birth_date, hire_date)：

ALTER TABLE employees ADD INDEX index_bh (birth_date, hire_date);

查詢條件：

SELECT * FROM employees 
WHERE birth_date BETWEEN '1955-01-01' AND '1955-12-31' 
  AND DATEDIFF(hire_date, birth_date) > 12300 
  AND first_name LIKE 'S%b%';

EXPLAIN 對比：

• ICP 關閉：Extra: Using where，耗時 0.123 秒
• ICP 開啟：Extra: Using index condition; Using where，耗時 0.074 秒

效能提升約 40%，DATEDIFF(hire_date, birth_date) > 12300 可直接在索引層過濾，避免大量回表。

三、ICP 適用條件與限制

1. 僅適用於二級索引
2. 執行計畫 type 為 range、ref、eq_ref、ref_or_null
3. 查詢需回表（非覆蓋索引）
4. 支援 MyISAM 與 InnoDB，不支援分區表（5.7 已修復）
5. 加速效果取決於儲存引擎層透過 ICP 過濾掉的資料比例

四、啟用與控制

-- 檢查狀態
SELECT @@optimizer_switch LIKE '%index_condition_pushdown%';

-- 動態開啟/關閉
SET optimizer_switch = 'index_condition_pushdown=on|off';

預設為開啟，建議生產環境保持開啟，除非特定場景經測試證明關閉更優。