概念
索引下推（Index Condition Pushdown，簡稱 ICP） 是 MySQL 5.6 版本中提供的一項索引優化功能，它允許存儲引擎在索引遍歷過程中，執行部分 WHERE字句的判斷條件，直接過濾掉不滿足條件的記錄，從而減少回表次數，提高查詢效率。
例子
假設我們有一個名為 user 的表，創建了聯合索引(name, age)。
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`high` int NOT NULL,`age` int NOT NULL,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`)
);
SELECT * FROM user WHERE name = '張三' AND age > 3;
- 沒有索引下推之前，即使 name字段利用索引可以幫助我們快速定位到 name = ‘張三’ 的用戶，但我們仍然需要對每一個找到的用戶進行回表操作，獲取完整的用戶數據，再去判斷 age > 3。
- 有了索引下推之后，存儲引擎會在使用name字段索引查找name = ‘張三’ 的用戶時，同時判斷 age > 3。這樣，只有同時滿足條件的記錄才會被返回，減少了回表次數。
SET optimizer_switch = 'index_condition_pushdown=off';
explain SELECT * FROM user WHERE name = '張三' AND age > 3;
SET optimizer_switch = 'index_condition_pushdown=on';
explain SELECT * FROM user WHERE name = 'k哥' AND age > 3;
原理
先看下面這張 MySQL 簡要架構圖。
- MySQL 可以簡單分為 Server 層和存儲引擎層這兩層。Server 層處理查詢解析、分析、優化、緩存以及與客戶端的交互等操作，而存儲引擎層負責數據的存儲和讀取，MySQL 支持 InnoDB、MyISAM、Memory 等多種存儲引擎。
- 索引下推的下推其實就是指將部分上層（Server 層）負責的事情，交給了下層（存儲引擎層）去處理。
我們這里結合索引下推原理再對上面提到的例子進行解釋。
沒有索引下推之前：
- 存儲引擎層先根據 name 索引字段找到所有 name = ‘k哥’ 的用戶的主鍵 ID，然后二次回表查詢，獲取完整的用戶數據；
- 存儲引擎層把所有 name = ‘k哥’ 的用戶數據全部交給 Server 層，Server 層根據 age > 3 這一條件再進一步做篩選。
有了索引下推之后：
- 存儲引擎層先根據 name 索引字段找到所有 name = ‘k哥’ 的用戶，然后直接判斷age > 3，篩選出符合條件的主鍵 ID；
- 二次回表查詢，根據符合條件的主鍵 ID 去獲取完整的用戶數據；
- 存儲引擎層把符合條件的用戶數據全部交給 Server 層。
可以看出，除了可以減少回表次數之外，索引下推還可以減少存儲引擎層和 Server 層的數據傳輸量。
總結
索引下推應用范圍
- 適用于 InnoDB 引擎和 MyISAM 引擎的查詢。
- 適用于執行計劃是 range, ref, eq_ref, ref_or_null 的范圍查詢。
- 對于 InnoDB 表，僅用于非聚簇索引。
- 索引下推的目標是減少全行讀取次數，從而減少 I/O 操作。
- 對于 InnoDB 聚集索引，完整的記錄已經讀入 InnoDB 緩沖區。
- 在這種情況下使用索引下推不會減少 I/O。
- 子查詢不能使用索引下推，因為子查詢通常會創建臨時表來處理結果，而這些臨時表是沒有索引的。
- 存儲過程不能使用索引下推，因為存儲引擎無法調用存儲函數。
