先看一下官方文档的概述
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
索引条件下推(ICP)是针对MySQL使用索引从表中检索行的情况进行的优化。如果没有ICP，存储引擎将遍历索引以定位基表中的行，并将它们返回给MySQL服务器，MySQL服务器为这些行计算WHERE条件。在启用ICP的情况下，如果WHERE条件的某些部分只能通过使用索引中的列来求值，MySQL服务器就会将WHERE条件的这部分下推到存储引擎。然后，存储引擎通过使用索引条目计算推入的索引条件，只有当满足这一条件时，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
要想理解索引下推必须先了解索引是如何关联数据的。
索引是如何关联数据的？
使用如下SQL
CREATE TABLE T (
ID INT PRIMARY KEY,
k INT NOT NULL DEFAULT 0,
s VARCHAR(16) NOT NULL DEFAULT '',
INDEX k (k)
) ENGINE=INNODB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
执行 select * from T where k between 3 and 5的流程：
- 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
- 再到 ID 索引树查到 ID=300 对应的 R3；
- 在 k 索引树取下一个值 k=5，取得 ID=500；
- 再回到 ID 索引树查到 ID=500 对应的 R4；
- 在 k 索引树取下一个值 k=6，不满足条件，循环结束。
索引下推的案例
表 people 有 索引 idx_zipcode_lastname_firstname(zipcode, lastname, firstname).
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
没有索引下推的话，执行步骤如下：
- 读取索引idx_zipcode_lastname_firstname的一行
- 判断当前索引是否符合zipcode='95054' ，如果不符合则跳到1。如果符合则根据主键查询出一行数据。
- 判断 lastname 和address 是否符合条件。如果符合，找到。
- 跳到1。
使用索引条件下推，执行步骤如下：
- 读取索引idx_zipcode_lastname_firstname的一行
- 判断当前索引是否符合 zipcode='95054' 和 lastname LIKE '%etrunia%'。如果不符合跳到1。如果符合则根据主键查询出一行数据。
- 判断address 是否符合条件。如果符合，找到。
- 跳到1。
总结
可以看到 索引下推 可以减少了根据主键查询出数据的次数。索引下推以后，只是多进行了一次判断。
参考资料
极客时间 MySQL专栏 深入浅出索引
MySQL 8.0 Reference Manual __ 8.2.1.6 Index Condition Pushdown Optimization
