MySQL 索引下推 (Index Condition Pushdown, ICP) 技術原理與實戰優化

MySQL 索引下推 (Index Condition Pushdown, ICP) 技術原理與實戰優化

索引下推 (ICP) 是 MySQL 5.6 引入的存儲引擎層優化，將部分 WHERE 條件下推至存儲引擎內部過濾，減少回表次數，顯著提升範圍查詢效能。本文對比無 ICP 與有 ICP 的掃描流程，並以實例演示效果。

一、無 ICP 的掃描流程

1. 讀取索引元組
2. 根據索引元組定位並讀取完整表行 (回表)
3. 在 Server 層評估 WHERE 條件，決定保留或丟棄

二、有 ICP 的掃描流程

1. 讀取索引元組 (不回表)
2. 在存儲引擎層評估「僅涉及索引列」的 WHERE 條件
3. 條件不滿足 → 直接丟棄，繼續下一個索引元組
4. 條件滿足 → 根據索引定位並讀取完整表行
5. 在 Server 層評估剩餘條件 (非索引列)，最終決定保留或丟棄

三、實戰案例

表結構與索引

CREATE TABLE people (
    id INT PRIMARY KEY,
    zipcode VARCHAR(10),
    lastname VARCHAR(50),
    firstname VARCHAR(50),
    address VARCHAR(200),
    INDEX idx_zip_ln_fn (zipcode, lastname, firstname)
) ENGINE=InnoDB;

查詢語句

SELECT * FROM people
WHERE zipcode = '95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

無 ICP 執行

• 通過 zipcode='95054' 定位索引範圍
• 每個匹配索引元組 → 回表讀取完整行
• Server 層評估 lastname LIKE '%etrunia%' 和 address LIKE '%Main Street%'
• 若 address 不匹配，回表 I/O 白費

有 ICP 執行

• 通過 zipcode='95054' 定位索引範圍
• 存儲引擎層直接評估 lastname LIKE '%etrunia%' (lastname 在索引中)
• 不滿足 → 直接跳過，**避免回表**
• 滿足 → 回表讀取完整行，Server 層評估 address LIKE '%Main Street%' (address 不在索引)

四、ICP 生效條件

五、驗證 ICP 是否生效

EXPLAIN SELECT * FROM people
WHERE zipcode='95054' AND lastname LIKE '%etrunia%';
# 輸出 Extra 欄顯示 "Using index condition" 表示 ICP 生效

六、效能影響分析

• 減少回表次數：索引列過濾率越高，收益越大
• 降低 I/O：避免無效行的隨機讀取
• CPU 權衡：存儲引擎層額外評估條件，CPU 微增，通常可忽略
• 不適用場景：條件列不在索引中、LIKE 以通配符開頭無法利用索引

七、最佳實踐

1. 為高頻篩選列建立聯合索引，將高選擇性列前置
2. 查詢盡量覆蓋索引列，讓更多條件下推
3. 使用 EXPLAIN 確認 Using index condition
4. 結合 ICP + MRR (Multi-Range Read) 進一步優化範圍掃描

ICP 是「將計算推向數據」思想在存儲引擎層的經典實踐。合理設計索引與查詢，讓 ICP 最大化發揮作用，是 SQL 調優的必修課。