8.2.1.6-索引条件下推优化

索引条件下推（ICP）是MySQL通过索引从表上查询数据场景下的一个优化。没有条件下推的话，存储引擎会通过索引找到记录，回表查询数据，然后将其返回给server层，再由server层来进行条件过滤。有了条件下推，如果where中的部分条件可以只通过索引就能判断出来，那么server层会将这部分where条件下推到存储引擎。存储引擎使用索引来比较下推的条件，如果匹配，才会回表查询。ICP优化可以减少存储引擎回的次数和server层访问存储引擎层的次数。

索引条件下推优化适用于以下条件：

ICP适用于range，ref，eq_ref，和ref_or_null访问方法，这些访问方法需要回表查询。
ICP可以用于InnoDB和MyISAM表，包括InnoDB分区表和MyISAM分区表。
对于InnoDB表来说，ICP只适用于二级索引。ICP的目的是为了减少回表次数，降低磁盘I/O操作次数，对于InnoDB的聚簇索引来说，完整的记录已经被读取到了InnoDB缓存中。这种情况下，使用ICP不能降低I/O。
ICP不适用于虚拟列上建立的二级索引。InnoDB支持在虚拟列上建立二级索引。
子查询相关的条件不可以下推。
存储函数相关的条件不可以下推。存储引擎不可以调用存储的函数。（？）
被触发的条件不能被下推。（想了解更多信息，请查阅章节8.2.2.3 “Optimizing Subqueries with the EXISTS Strategy”）。
（MySQL 8.0.30及以后）不能将条件下推到包含对系统变量的引用的派生表。

想要理解这个优化是怎么工作的，要先理解在没有ICP时，一个索引扫描是怎样进行的：

获取下一行，先读取索引元组，再根据索引元组定位并回表读取完整的记录。
判断where条件。符合则接受，否则丢弃。

使用ICP，扫描处理过程如下：

获取下一行的索引元组（不是完整的表记录）。
比较可以通过索引判断的那一部分where条件。如果不符合，继续寻找下一个索引元组。
如果条件满足，使用索引元组定位记录，回表读取完整的记录。
比较where条件的剩余部分。符合则使用，否则丢弃。

当使用了ICP时，explain输出中的Extra列会显示 Using index condition。这时不会显示 Using index，因为必须要回表读取完整的记录。

假设有一张包含了人员信息和地址的表，这张表上顶一个了一个索引 INDEX(zipcode, lastname, firstname)。如果我们知道一个人的zipcode，但是不确定lastname，我们可以这样查询：

select * from people
    where zipcode='95054'
    and last name like '%etrunia%'
    and address like '%Main Street%';

MySQL可以使用条件zipcode=‘95054’在二级索引中取扫描。第二个条件(lastname like ‘%etrunia%’)不能减少扫描的记录数。如果没有条件下推，这个查询必须回表扫描所有符合zipcode=‘95054’条件的记录。

有了ICP，MySQL会在回表查询之前校验条件 lastname like ‘%etrunia%’。这避免了符合zipcode条件但是不符合lastname条件的那部分记录的回表。

ICP是默认开启的。可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来开启和关闭：

set optimizer_switch = 'index_condition_pushdown=off';
set optimizer_switch = 'index_condition_pushdown=on';

见章节8.9.2 “Switchable Optimizations”。