mysql icp_试释MySQL ICP的部分疑点

MySQL在5.6这个版本引入了ICP(Index Condition Pushdown)查询优化。其对ICP简要的介绍如下：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 theWHEREcondition for the rows. With ICP enabled, and if parts of theWHEREcondition can be evaluated by using only columns from the index, the MySQL server pushes this part of theWHEREcondition 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对主键索引的查询没有优化作用是确定的，但是没开启ICP时，对二级索引键值的where条件判断有没有产生回表操作(根据二级索引存的主键键值读取整行数据)？

ICP对update...where有没有效？

对于以上疑问，本文暂时以最新的MySQL 8.0下的InnoDB数据引擎的测试的结果为准。准备好的测试数据如下：

测试用的表的索引信息如下(字段id为主键索引，字段value为普通二级索引)：

ICP默认开启的情况下测试结果如下：

ICP关闭的情况下测试结果如下：

从测试结果而知，ICP开启和关闭，在MySQL8.0的InnoDB环境下都没有产生额外的回表操作。开关前后，select操作的row始终都只有2行(按逻辑，不管ICP开启与否，这两行肯定都会各回表一次)。加锁也只有2个主键记录加记录锁，2个索引记录加Next-key锁。也就是说在没开启ICP的情况下，有两行在判断value二级索引所处的where条件时，直接跳过了后续回表查value1的过程。也没有文档"所说"的，自己产生的回表。那么官方文档如下描述，在InnoDB中，在此种情况下应该是不精确的，有误导的成分：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.

此种情况下，ICP优化的是什么？其实是the number of times the MySQL server must access the storage engine.

ICP开启后，在此种测试情况下，不用Server层每次去操作InnoDB读一个索引记录出来，然后在Server层check condition。而是InnoDB根据push进来的Index filter一次性读出所有符合condition的索引记录，统一返回给Server层。Server层再根据返回的索引记录和Table filter做回表相关的逻辑，读取并过滤相应的行数据。

通过上图，我们还可以得知，官方文档的这句话也是耐人寻味的：With ICP enabled, and if parts of the WHERE condition can be evaluated by using onlycolumns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine.

因为在value1字段上还没有索引的情况下，通过explain指令我们可以看到，测试用的select语句的Extra显示了Using index condition。此处，也有可能是的MySQL的查询优化器，将Where condition分成了2个步骤，第一个步骤查询value上的index，第二个步骤回表查询value1上的值是否符合筛选条件。结果就是在此种情况下，不管ICP开启与否，value上的index查询“好像”已经提前过滤了大部分回表操作，ICP"好像"跟二级索引的回表操作无关。

剩下的一个疑问就是，ICP对update...where操作有没有优化？

结果就是：“好像”没有！！！连delete也没有生效！为什么update和delete的condition都不优化？本人没详细看过MySQL的源码，故先在此先猜一个原因——可能是这两个操作在此种情况下需要给主键加上记录锁的缘故(换一种情况可能就是加Next-key锁，此处不详谈)，横竖都要access次base table，还望懂的大大指点迷津。所以MySQL的官方文档的如下描述，也是容易误解的：BesidesWHEREclauses in

以上所有观点，都是本人没看源码的“猜测”，请批判性看待。如有错误，还望各位大大斧正，感激涕零，无以为报，唯有以身相许。

Reference：