索引下推介紹
索引下推（INDEX CONDITION PUSHDOWN，簡稱 ICP）是在 MySQL 5.6 針對掃描二級索引的一項優化改進。總的來說是通過把索引過濾條件下推到存儲引擎，來減少 MySQL 存儲引擎訪問基表的次數以及 MySQL 服務層訪問存儲引擎的次數。ICP 適用于 MYISAM 和 INNODB，本篇的內容只基于 INNODB。
在講這個技術之前你得對mysql架構有一個簡單的認識，見下圖：
● MySQL 服務層：也就是 SERVER 層，用來解析 SQL 的語法、語義、生成查詢計劃、接管從 MySQL 存儲引擎層上推的數據進行二次過濾等等。
● MySQL 存儲引擎層：按照 MySQL 服務層下發的請求，通過索引或者全表掃描等方式把數據上傳到 MySQL 服務層。
● MySQL 索引掃描：根據指定索引過濾條件，遍歷索引找到索引鍵對應的主鍵值后回表過濾剩余過濾條件。
● MySQL 索引過濾：通過索引掃描并且基于索引進行二次條件過濾后再回表。
實戰數據準備
delete from user1;
drop table user1;CREATE TABLE `user1` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`age` tinyint(4) NOT NULL,`address` varchar(50) NOT NULL,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `user1` (`name`, `age`, `address`) VALUES
('Alice', 40, 'address1'),
('Amy', 23, 'address2'),
('Tom', 18, 'address3'),
('Mike', 22, 'address4');explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 23;# 查看索引下推是否開啟
select @@optimizer_switch
# 開啟索引下推
set optimizer_switch="index_condition_pushdown=on";
# 關閉索引下推
set optimizer_switch="index_condition_pushdown=off";
索引下推實戰
- 不使用索引下推實現
Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
- 使用索引下推實現
Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
索引下推的使用條件
● ICP目標是減少全行記錄讀取，從而減少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表數據，也就不存在下推一說。
● 只能用于range、 ref、 eq_ref、ref_or_null訪問方法；
● where 條件中是用 and 而非 or 的時候。
● ICP適用于分區表。
● ICP不支持基于虛擬列上建立的索引，比如說函數索引
● ICP不支持引用子查詢作為條件。
● ICP不支持存儲函數作為條件，因為存儲引擎無法調用存儲函數。
參考《圖解｜索引覆蓋、索引下推以及如何避免索引失效》
