索引下推

索引下推

Index Condition Pushdown(ICP)

当MySQL使用一个索引来检索表中的行时，可以使用ICP作为一种优化方案。不使用ICP时，存储引擎通过索引检索基础表中的行并将符合WHERE条件中的行返回给客户端。启用ICP后，如果只需要通过索引中的列就能够评估行是否符合WHERE中的一部分条件，MySQL将这部分WHERE条件下推到存储引擎中，然后存储引擎评估使用索引条目来评估下推的索引条件，并只从表中读取符合条件的行。ICP可以减少存储引擎访问基础表的次数以及MySQL访问存储引擎的次数。

ICP适用情景

• ICP可以用于需要访问整个表中的行的range,ref,eq_ref,ref_or_null访问模式。
• ICP可以用于InnoDB和MyISAM，包括分区表。

ICP限制

• 对于InnoDB表，ICP只适用于辅助索引。因为ICP的目标是减少全表读的数量从而减少I/O操作。对于InnoDB聚集索引，完整的记录已经被读取到InnoDB缓冲区，在这种情况下使用ICP不能降低I/O
• ICP不支持在虚拟生成列上创建的索引。InnoDB存储引擎支持在虚拟生成列上创建索引
• 与子查询相关的条件不能下推
• 与存储函数相关的条件不能下推。
• 触发条件不能下推

ICP优化过程

要理解ICP优化如何工作，首先考虑一下，在不使用索引下推的情况下，索引扫描是如何进行的：

• 获取下一行，首先读取索引元组，然后通过索引元组检索并读取整行数据。
• 对表中的数据测试是否符合WHERE条件，基于测试结果接收或拒绝行 使用索引下推时，扫描过程如下：
• 获取下一行的索引元组（不需要获取整行数据）
• 测试能否只通过索引中的行确认数据是否符合WHERE条件中的一部分。如果不符合条件，继续获取下一个索引元组。
• 如果符合条件，通过索引元组来检索并读取整行数据
• 测试数据是否符合WHERE条件中的其他部分。基于测试结果接收或拒绝行。

索引下推具体实践

使用一张用户表t_user,创建联合索引（name,age）

image-20220428200252819

image-20220428200319733

如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：

select  * from T.t_user where name like '张%' and age=10;

那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。

image-20220428200708899

接下来分为两种

没有使用ICP

在MySQL5.6之前，存储引擎会根据联合索引找到name like ‘张%’的主键id（1、4），逐一进行回表扫描，去聚集索引找到完整的行记录，然后再根据age=10进行筛选

image-20220428204523690

可以看到需要两次回表，把我们的联合索引另一个字段==age==浪费了

使用ICP

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

image-20220428204951371

可以看到只回表了一次。