MySQL Index Condition Pushdown (ICP) 深度解析：原理、實測與適用條件

前言：ICP 是「下推」給存儲引擎的過濾器

Index Condition Pushdown (ICP) 是 MySQL 5.6+ 引入的優化：將 WHERE 條件中**能用索引列計算的部分**下推到存儲引擎層，在讀取完整行之前先過濾，減少「回表」與「Server-Engine 交互」次數。本文結合官方 employees 測試庫，通過 EXPLAIN、SHOW PROFILES 實測量化效果，並梳理適用邊界。

一、ICP 工作原理對比圖解

無 ICP（傳統模式）

1. 存儲引擎遍歷二級索引 → 取得主鍵值。
2. **回表**讀取完整行 → 返回 Server 層。
3. Server 層評估完整 WHERE 條件 → 不滿足則丟棄。

有 ICP（下推模式）

1. Server 層分析 WHERE，將**僅涉及索引列的條件**打包下推。
2. 存儲引擎遍歷索引 → **在索引元組上直接評估下推條件**。
3. **僅滿足條件的索引元組才回表讀取完整行** → 返回 Server。
4. Server 層評估剩餘條件（涉及非索引列）。

二、實測環境與數據

• 測試庫：employees（30 萬行）。
• 表 employees：PK emp_no，二級索引 index_bh (birth_date, hire_date)。
• 查詢：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 對比：關鍵在 Extra 欄位

四、SHOW PROFILES 量化效果

SET profiling=1;
-- ICP OFF
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%';
-- Duration: 0.123 s

SET optimizer_switch='index_condition_pushdown=on';
-- ICP ON
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%';
-- Duration: 0.074 s

提速 ~40%，來源於：索引列條件下推後，存儲引擎直接過濾掉大量不滿足 DATEDIFF 的行，**避免了回表與 Server-Engine 交互**。

五、ICP 適用條件（四大硬性門檻）

1. 僅限二級索引（Secondary Index），聚簇索引（PK）不適用。
2. EXPLAIN 的 type 必須為 range、ref、eq_ref、ref_or_null，且**需要回表**（非覆蓋索引）。
3. 支援引擎：**InnoDB、MyISAM**；**不支援分區表**（5.7 已修復）。
4. 加速效果取決於**存儲引擎內下推條件過濾掉的行比例**；若下推條件過濾率低，收益微乎其微。

六、開啟/關閉 ICP