MySQL 5.6 ICP (Index Condition Pushdown) 索引下推：原理、實測與使用限制全解析

Index Condition Pushdown (ICP) 是 MySQL 5.6 引入的存儲引擎層優化特性：將部分 WHERE 條件「下推」到存儲引擎 (InnoDB/MyISAM) 內部利用索引過濾，**減少回表次數與 Server 層交互**，顯著提升篩選比例高的查詢性能。本文通過原理圖解、Profile 實測、EXPLAIN 特徵識別、七大使用限制，全方位解析 ICP。

核心原理：Server 層 vs 引擎層 過濾對比

傳統模式 (ICP 關閉)

1. 引擎按索引讀取索引元組 (Index Tuple)。
2. 引擎根據行指針 **回表讀取完整行數據**。
3. 引擎將完整行傳遞給 Server 層。
4. Server 層評估 WHERE 條件，決定保留/丟棄。

**缺點**：大量不滿足條件的行仍需 **回表 + 跨層傳輸**，I/O 與 CPU 雙重浪費。

ICP 模式 (ICP 開啟)

1. 引擎讀取索引元組 (不回表)。
2. 引擎內部評估 **可用索引列的 WHERE 條件** (如 first_name='Anneke'、last_name LIKE '%sig' 中的 last_name 列在索引中)。
3. 條件不滿足 → **直接丟棄索引元組，不回表，不傳輸**。
4. 條件滿足 → 回表讀取完整行 → 傳遞 Server 層 → Server 評估 **剩餘無法下推條件**。

**收益**：**回表次數 = 滿足下推條件的行數**，大幅降低 I/O 與層間交互。

實測案例：employees 表 (30 萬行)

環境

• MySQL 5.6.16
• 關閉 Query Cache
• 測試表 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 可下推過濾**。

Profile 對比 (關鍵指標：Sending data 耗時)

**結論**：ICP 開啟後總耗時降為 **62%**，Sending data 階段 (含引擎回表+傳輸) 耗時降為 **26%**。索引條件下推效果顯著。

EXPLAIN 識別特徵

-- ICP ON
EXPLAIN SELECT * FROM employees WHERE first_name='Anneke' AND last_name LIKE '%sig';
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | employees | ref  | idx_emp_fnln  | idx_emp_fnln | 44      | const |  224 | Using index condition |  <-- 關鍵標識
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+

-- ICP OFF
... | Extra | Using where |  <-- 無 index condition

ICP 適用訪問類型與限制 (七大條件)

覆蓋索引與 ICP 互斥驗證

-- 非覆蓋索引 (SELECT *)，觸發 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  (無 index condition)

最佳實踐建議

1. 默認開啟：MySQL 5.6+ optimizer_switch='index_condition_pushdown=on' 為預設，**無需手動干預**。
2. 索引設計配合**：為高頻篩選查詢建立 **包含篩選列的聯合索引**，使更多條件可下推。例：WHERE a=1 AND b>10 AND c LIKE 'foo%' → 索引 (a, b, c) 可下推全部三列條件。
3. 避免全表掃描**：ICP 不適用 type=ALL/index，確保查詢有合適索引 (ref/range)。
4. 監控驗證**：慢查詢 EXPLAIN 檢查 Using index condition，結合 Handler_read_next、Innodb_rows_read 觀察回表減少情況。
5. 特殊場景關閉**：極少數 Bug (如 5.6 早期版本特定條件下推導致錯誤結果) 可會話級關閉：SET optimizer_switch='index_condition_pushdown=off'。

ICP 是 MySQL 5.6 最實用的優化特性之一，**零代碼改動、零配置成本、自動生效**，對「索引命中但回表篩選率高」的查詢效果立竿見影。理解其原理與邊界，能指導更精準的索引設計與慢查詢分析。