你是否曾經為 MySQL 查詢性能苦惱？或者聽説過"索引下推"，但不太明白它是如何工作的？今天我就和大家聊聊 MySQL 中一個強大卻常被忽視的特性 - 索引下推（Index Condition Pushdown，簡稱 ICP）。這個特性從 MySQL 5.6 版本引入，能有效提升查詢性能，減少 IO 操作，但很多開發者對它的理解還不夠深入。

索引下推是什麼？

索引下推是由 MySQL 優化器決定，並在存儲引擎層面實現的一種優化策略。簡單來説，它允許存儲引擎在檢索到索引列後，直接利用索引中的信息過濾掉不符合 WHERE 條件的記錄，然後再回表獲取需要的數據。

回表是指通過二級索引（非主鍵索引）查詢到主鍵值後，再根據主鍵值查詢聚簇索引獲取完整行數據的過程。聚簇索引是 MySQL 中 InnoDB 存儲引擎的主鍵索引，數據行按主鍵順序物理存儲，一個表只能有一個聚簇索引。回表過程通常需要額外的 IO 操作，是查詢性能的瓶頸之一。

傳統索引查詢 vs 索引下推

graph TB
    subgraph "傳統索引查詢"
    A1[根據索引找到滿足索引條件的記錄] --> B1[回表查詢完整記錄]
    B1 --> C1[服務器層應用WHERE條件過濾]
    end
    subgraph "索引下推"
    A2[根據索引找到記錄] --> B2[在存儲引擎層直接應用索引列的WHERE條件]
    B2 --> C2[只對滿足條件的記錄回表]
    end

舉個生活中的例子：傳統索引查詢就像你去圖書館找書，先通過索引卡片找到所有姓"張"的作者的書架位置，然後一本一本地去書架上查看是否是"張三"寫的書。而索引下推則像是索引卡片上除了作者姓氏，還有全名，你直接在卡片上就篩選出"張三"的書，只需要去拿那幾本書就行了。

為什麼索引下推能提升性能？

核心優勢在於減少了回表操作。在大型數據庫中，減少 IO 操作對性能提升至關重要。

flowchart LR
    A[減少回表次數] --> B[降低IO操作]
    B --> C[提高查詢速度]
    C --> D[減輕服務器負擔]

實戰案例：員工信息查詢優化

假設我們有一個員工表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    position VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

-- 創建聯合索引
CREATE INDEX idx_name_age ON employees(name, age);

案例分析

假設我們要查詢名字以"Zhang"開頭且年齡在 25 到 30 歲之間的員工：

SELECT * FROM employees
WHERE name LIKE 'Zhang%' AND age > 25 AND age < 30;

沒有索引下推時

sequenceDiagram
    MySQL服務器->>存儲引擎: 使用索引找到name LIKE 'Zhang%'的記錄
    存儲引擎->>MySQL服務器: 返回所有匹配記錄的完整數據
    MySQL服務器->>MySQL服務器: 過濾age > 25 AND age < 30的記錄

這種情況下，存儲引擎會把所有名字以"Zhang"開頭的員工記錄都回表查詢出來，然後 MySQL 服務器再根據年齡條件過濾。如果有 100 個姓 Zhang 的員工，但只有 10 個在 25-30 歲之間，我們仍需要回表查詢 100 次。

啓用索引下推後

sequenceDiagram
    MySQL服務器->>存儲引擎: 使用索引找到name LIKE 'Zhang%'的記錄
    存儲引擎->>存儲引擎: 在索引中直接過濾age > 25 AND age < 30
    存儲引擎->>MySQL服務器: 只返回同時滿足name和age條件的記錄

有了索引下推，存儲引擎可以直接利用索引中的 age 字段進行過濾，只有同時滿足名字和年齡條件的記錄才會被回表查詢。在上面的例子中，只需要回表 10 次，大大減少了 IO 操作。

如何驗證索引下推是否生效？

使用 EXPLAIN 命令分析查詢計劃，查看 Extra 列是否包含"Using index condition"：

EXPLAIN SELECT * FROM employees
WHERE name LIKE 'Zhang%' AND age > 25 AND age < 30;

如果看到"Using index condition"，説明查詢使用了索引下推。在 EXPLAIN 結果中，rows字段代表存儲引擎預計需要掃描的索引記錄數，該值越小，説明索引下推的過濾效果越好。索引下推由optimizer_switch中的index_condition_pushdown參數控制，默認是開啓的。

索引下推的適用條件

1. 必須使用二級索引（非聚簇索引，即非主鍵索引）
2. 當 WHERE 條件中的部分字段雖在索引中，但僅靠索引的初始篩選（如前綴匹配、範圍查詢）無法過濾所有條件時，剩餘條件可在索引層下推處理
3. 目前支持的存儲引擎包括 InnoDB 和 MyISAM

MyISAM 和 InnoDB 的索引下推實現有細微差別：MyISAM 的 ICP 在索引掃描時直接過濾條件，而 InnoDB 在二級索引掃描時利用索引中的列數據（包括主鍵）過濾。雖然基本原理相同，但因為 InnoDB 使用聚簇索引結構而 MyISAM 不是，這可能導致兩者在特定場景下性能表現略有不同。

索引下推與覆蓋索引的關係

覆蓋索引是指索引包含了查詢所需的所有字段，無需回表就能獲取所需數據。在覆蓋索引場景下，索引已包含所有查詢字段，無需回表，因此索引下推的優化目標（減少回表）已不存在。此時EXPLAIN可能顯示Using index（覆蓋索引）而非Using index condition。

覆蓋索引性能通常優於索引下推，因為完全避免了回表操作。

性能測試對比

讓我們來做個簡單測試，假設 employees 表中有 100 萬記錄：

-- 不使用索引下推
SET optimizer_switch='index_condition_pushdown=off';
SELECT * FROM employees WHERE name LIKE 'Zhang%' AND age > 25 AND age < 30;

-- 使用索引下推
SET optimizer_switch='index_condition_pushdown=on';
SELECT * FROM employees WHERE name LIKE 'Zhang%' AND age > 25 AND age < 30;

測試結果對比：

• 不使用索引下推：

  • 執行時間 2.3 秒
  • 掃描記錄數約 15000（EXPLAIN 中的 rows 值）
  • 回表操作次數約 15000

• 使用索引下推：

  • 執行時間 0.8 秒
  • 掃描記錄數約 3000（EXPLAIN 中的 rows 值明顯減少）
  • 回表操作次數約 3000

性能提升非常明顯！尤其是在大表上，索引下推的優勢更加突出。這種提升在索引選擇性較高（重複值少）的情況下尤為顯著。

索引下推的失效場景

雖然索引下推很強大，但也有一些限制：

1. 對索引列使用函數：例如YEAR(created_at) = 2023，會導致存儲引擎無法直接利用索引值過濾。對索引列使用函數會破壞索引的有序性，存儲引擎無法利用索引快速定位，只能將索引值全部返回服務器層處理。

   -- 索引下推失效示例
   SELECT * FROM employees WHERE SUBSTRING(name, 1, 5) = 'Zhang' AND age > 25;

2. 非索引列的條件：例如position = 'Engineer'，因為position不在索引中，只能在服務器層過濾

   -- position不在索引中，無法使用索引下推
   SELECT * FROM employees WHERE name LIKE 'Zhang%' AND position = 'Engineer';

3. 使用 OR 連接索引列與非索引列：例如name LIKE 'Zhang%' OR position = 'Engineer'，可能導致索引下推部分失效
4. 無法在索引數據上直接計算的條件：如跨索引列的計算、複雜的非等值條件組合等

實際應用建議

1. 對於頻繁查詢且 WHERE 條件包含多個索引列的場景，考慮創建聯合索引
2. 使用 EXPLAIN 分析查詢，確保索引下推正常工作
3. 在數據量大的表上，索引下推的效果更明顯
4. 合理設計索引，使索引列能覆蓋更多的查詢條件
5. 優先考慮能否通過覆蓋索引完全避免回表
6. 索引下推是輔助優化手段，不能替代合理的索引設計

總結

以下表格總結了索引下推的關鍵點：

索引下推是 MySQL 查詢優化的重要武器，合理利用它可以大幅提升應用性能。希望這篇文章對你有所幫助，讓你的 MySQL 查詢飛起來！