当MySQL使用一个索引来检索表中的行时,可以使用ICP
作为一种优化方案。不使用ICP
时,存储引擎通过索引检索基础表中的行并将符合WHERE
条件中的行返回给客户端。启用ICP
后,如果只需要通过索引中的列就能够评估行是否符合WHERE
中的一部分条件,MySQL将这部分WHERE
条件下推到存储引擎中,然后存储引擎评估使用索引条目来评估下推的索引条件,并只从表中读取符合条件的行。ICP
可以减少存储引擎访问基础表的次数以及MySQL访问存储引擎的次数。
range,ref,eq_ref,ref_or_null
访问模式。InnoDB
和MyISAM
,包括分区表。InnoDB
表,ICP
只适用于辅助索引。因为ICP
的目标是减少全表读的数量从而减少I/O
操作。对于InnoDB
聚集索引,完整的记录已经被读取到InnoDB
缓冲区,在这种情况下使用ICP
不能降低I/O
ICP
不支持在虚拟生成列上创建的索引。InnoDB
存储引擎支持在虚拟生成列上创建索引要理解ICP优化如何工作,首先考虑一下,在不使用索引下推的情况下,索引扫描是如何进行的:
WHERE
条件,基于测试结果接收或拒绝行
使用索引下推时,扫描过程如下:WHERE
条件中的一部分。如果不符合条件,继续获取下一个索引元组。WHERE
条件中的其他部分。基于测试结果接收或拒绝行。使用一张用户表t_user,创建联合索引(name,age)
如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户
。那么,SQL语句是这么写的:
select * from T.t_user where name like '张%' and age=10;
那么就知道这个语句在搜索索引树的时候,只能用 张
,找到的第一个满足条件的记录id为1。
接下来分为两种
在MySQL5.6之前,存储引擎会根据联合索引找到name like ‘张%’的主键id(1、4),逐一进行回表扫描,去聚集索引找到完整的行记录,然后再根据age=10进行筛选
可以看到需要两次回表,把我们的联合索引另一个字段==age==浪费了
而MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到name like '张%'
,由于联合索引中包含age
列,所以存储引擎直接再联合索引里按照age=10
过滤。按照过滤后的数据再一一进行回表扫描。
可以看到只回表了一次。