什么是索引下推

结论：如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，存储引擎会在取出索引的同时判断是否符合MySQL服务器传递的条件，即将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，在某些场景下，可以大大减少回表次数，从而提升整体性能。

什么是索引下推（Index Condition Pushdown，ICP）呢？假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

mysq> select * from tuser where name like '张 %' and age=10;

根据“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age字段的值是否符合。这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

不过在MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

ICP处理过程

存在复合索引idx_name_nickname(name,nickname)，以下是SQL语句在ICP关闭和开启时的执行流程：

select * from users where name = 'Silince' and nickname like '%RIN%'

未开启ICP时

通过explain分析执行计划，SQL语句在关闭CP特性的情况下，走的是复合索引idx_name_nickname，Extra=Using Where，首先通过复合索引 idx_name_nickname 前缀从存储引擎中读出 name = 'Silince' 的所有记录，然后在Server端用where 过滤 nickname like '%RIN%' 情况。

开启ICP特性时

开启ICP特性后，由于 nickname 的 like 条件可以通过索引筛选，存储引擎层通过索引与 where 条件的比较来去除不符合条件的记录，这个过程不需要读取记录，同时只返回给Server层筛选后的记录，减少不必要的IO开销。

使用ICP优化模糊查询

对于复合索引的优化

在开启ICP特性后，对于条件where name = 'Silince' and nickname like '%RIN%' 可以利用复合索引 (name,nickname) 减少不必要的数据扫描，提升SQL性能。 理由上文已详细说明。

对于单值索引的优化

尝试1:

但对于 where nickname like '%RIN%' 完全模糊匹配查询能否利用ICP特性提升性能？首先创建nickname上单列索引 idx_nickname。

查看执行计划发现type=ALL，Extra=Using where 走的是全部扫描，没有利用到ICP特性。

尝试2:

辅助索引 idx_nickname(nickname) 内部是包含主键id的，等价于(id，nickname)的复合索引，尝试利用覆盖索引特性将SQL改写为 select Id from users where nickname like '%RIN%' 。

从执行计划看到，type=index，Extra=Using where; Using index，索引全扫描，但是需要的数据都在索引列中能找到，不需要回表。

尝试3:

利用这个特点，将通过覆盖索引先获取对应数据的主键id，然后通过id跟原表进行连表查询，整个过程不涉及到回表查询。分析其执行计划。

select * from users a, (select id from users where nickname like '%RIN%') b where a.id = b.id;

从执行计划看，走了索引idx_nickname，不需要回表访问数据，type = index 说明没有用到ICP特性，但是可以利用 Using where; Using index 这种索引扫描不回表的方式(覆盖索引)减少资源开销来提升性能。

补充说明：Extra显示的索引扫描方式：

using where：查询使用索引的情况下，需要回表去查询所需的数据。
using index condition：查询使用了索引，但是需要回表查询数据。
using index：查询使用覆盖索引的时候会出现。查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。
using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

其他优化方式

生成列

MySQL 5.7开始支持生成列，生成列是由表达式的值计算而来，有两种模式：VIRTUAL和STORED，如果不指定默认是VIRTUAL。

VIRTUAL生成列用于复杂的条件定义，能够简化和统一查询，不占用空间，访问列是会做计算。
STORED生成列用作物化缓存，对于复杂的条件，可以降低计算成本，占用磁盘空间。
支持辅助索引的创建，分区以及生成列可以模拟函数索引。
不支持存储过程，用户自定义函数的表达式，NONDETERMINISTIC的内置函数，如NOW()， RAND()以及不支持子查询

对于where条件后的 like '%xxx' 是无法利用索引扫描，可以利用MySQL 5.7的生成列模拟函数索引的方式解决，具体步骤如下：

利用内置reverse函数将like ‘%RIN’反转为like ‘NIR%’，基于此函数添加虚拟生成列。
在虚拟生成列上创建索引。
将SQL改写成通过生成列like reverse(‘%RIN’)去过滤，走生成列上的索引。

添加虚拟生成列并创建索引。

alter table users add reverse_nickname varchar(200) generated always as (reverse(nickname));
alter table users add index idx_reverse_nickname(reverse_nickname);

全文索引

…

什么是索引下推ICP