您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL ICP (Index Condition Pushdown) 索引條件下推：原理、適用場景與實戰案例 (完整版)

編輯：MySQL 綜合教學

MySQL ICP (Index Condition Pushdown) 索引條件下推：原理、適用場景與實戰案例 (完整版)

一、ICP 技術背景與定位

MySQL 5.6 引入的索引優化技術，旨在減少二級索引範圍掃描時的回表次數與Server-Engine 層交互。在索引組織表 (InnoDB) 中，二級索引回表代價高於堆表，ICP 收益顯著。

二、WHERE 條件三層處理模型 (核心理論基礎)

Index Key (索引鍵)：確定索引掃描的連續範圍 (起始鍵 + 終止鍵)。拆分為 Index First Key / Index Last Key。
Index Filter (索引過濾)：範圍內可利用索引列直接判斷的條件，無需回表。
Table Filter (表過濾)：必須讀取完整行 (回表) 才能判斷的條件。

參考：何登成博士《MySQL 索引條件下推 (ICP) 原理分析》

三、ICP 本質：Index Filter 下推引擎層

5.6 前：範圍內所有索引條目全部回表 → Server 層統一過濾 (Index Filter + Table Filter 混合)。
5.6+：引擎層先應用 Index Filter → 僅滿足條件的行回表 → Server 層應用 Table Filter。
關鍵點：InnoDB 僅對二級索引生效；聚簇索引已讀完整行，無下推意義。

四、執行流程對比 (官方文檔)

步驟	無 ICP	有 ICP
1	讀索引元組 → 回表讀完整行	讀索引元組 (暫不回表)
2	Server 層過濾所有 WHERE	引擎層用索引列過濾 Index Filter 部分
3	—	通過則回表讀完整行
4	—	Server 層過濾剩餘 Table Filter 部分

EXPLAIN Extra: Using index condition 表示啟用。

五、官方示例：前導通配符 LIKE 的 Index Filter 化


-- 索引 (zipcode, lastname, firstname)
SELECT * FROM people
WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

zipcode → Index Key。
lastname LIKE ... → Index Filter (索引有 lastname，雖無法縮減範圍但可過濾)。
address LIKE ... → Table Filter (索引無 address)。
ICP 使 lastname 過濾在引擎層完成，避免大量無效回表。

六、實戰案例：跳過中間列的組合索引利用


-- 組合索引 (roleId, status, number)
EXPLAIN SELECT * FROM role_goods WHERE roleId=100000001 AND number=1;
+----+------+-----------+------+--------------+----------+---------+-------+------+-----------------------+
| id | type | table     | ...  | key          | key_len  | ref     | rows  | Extra                 |
+----+------+-----------+------+--------------+----------+---------+-------+------+-----------------------+
| 1  | ref  | role_goods| ...  | roleId_2     | 9        | const   | 14    | Using index condition |
+----+------+-----------+------+--------------+----------+---------+-------+------+-----------------------+

key_len=9 = roleId (BIGINT 8字節) + 1字節 NULL 標誌 → 僅用到 roleId 作為 Index Key (status 缺失導致最左前綴中斷)。
但 Extra: Using index condition 證明 number=1 作為 Index Filter 在引擎層生效，避免了 14 行全部回表。