MySQL ICP(Index Condition Pushdown)特性

一、SQL的where条件提取规则

在ICP(Index Condition Pushdown,索引条件下推)特性之前,必须先搞明白根据何登成大神总结出一套放置于所有SQL语句而皆准的where查询条件的提取规则:所有SQL的where条件,均可归纳为3大类:Index Key (First Key & Last Key),Index Filter,Table Filter。

接下来,简单说一下这3大类分别是如何定义,以及如何提取的,详情请看:SQL语句中where条件在数据库中提取与应用浅析

Index Key(Fist key & Last Key),Index Filter,Table Filter

Index First Key

只是用来定位索引的起始范围,因此只在索引第一次Search Path(沿着索引B+树的根节点一直遍历,到索引正确的叶节点位置)时使用,一次判断即可;

Index Last Key

用来定位索引的终止范围,因此对于起始范围之后读到的每一条索引记录,均需要判断是否已经超过了Index Last Key的范围,若超过,则当前查询结束;

Index Filter

用于过滤索引查询范围中不满足查询条件的记录,因此对于索引范围中的每一条记录,均需要与Index Filter进行对比,若不满足Index Filter则直接丢弃,继续读取索引下一条记录;

Table Filter

则是最后一道where条件的防线,用于过滤通过前面索引的层层考验的记录,此时的记录已经满足了Index First Key与Index Last Key构成的范围,并且满足Index Filter的条件,回表读取了完整的记录,判断完整记录是否满足Table Filter中的查询条件,同样的,若不满足,跳过当前记录,继续读取索引的下一条记录,若满足,则返回记录,此记录满足了where的所有条件,可以返回给前端用户。

二、ICP特性介绍

Index Condition Pushdown (ICP)是MySQL 5.6版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

我对Using index condition的理解是,首先mysql server和storage engine是两个组件,server负责sql的parse,执行; storage engine去真正的做数据/index的读取/写入。以前是这样:server命令storage engine按index key把相应的数据从数据表读出,传给server,然后server来按where条件(index filter和table filter)做选择。

而在MySQL 5.6加入ICP后,Index Filter与Table Filter分离,Index Filter下降到InnoDB的索引层面进行过滤,如果不符合条件则无须读数据表,减少了回表与返回MySQL Server层的记录交互开销,节省了disk IO,提高了SQL的执行效率。

原理

a. 当关闭ICP时,index仅仅是data access的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤,也就是做index filter和table filter。

b. 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server会把这部分下推到引擎层,可以利用index filter的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤。

优化效果:ICP能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数,减少io次数,提高查询语句性能。

三、测试ICP优化SQL

本文选用MySQL官方文档中提供的示例数据库之一:employees。这个数据库关系复杂度适中,且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册):

MySQL ICP(Index Condition Pushdown)特性

MySQL官方文档中关于此数据库的页面为:https://dev.mysql.com/doc/employee/en,里面详细介绍了此数据库,并提供了下载地址和导入方法,如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。

可以选择下载测试数据:https://github.com/datacharmer/test_db

关闭缓存(最好关闭后重启MySQL)

 导入employees库,需要自己手动创建一个联合索引。 

其表结构如下:

当开启ICP时(默认开启)

此时情况下根据MySQL的最左前缀原则,irst_name 可以使用索引,last_name采用了like 模糊查询,不能使用索引。

当关闭ICP时

当开启ICP时,查询在sending data环节时间消耗是 0.00108900s

当关闭ICP时,查询在sending data环节时间消耗是 0.00231650s

从上面的profile可以看出ICP开启时整个sql 执行时间是未开启的2/3,sending data 环节的时间消耗前者仅是后者的1/4。

ICP 开启时的执行计划 含有 Using index condition 标示 ,表示优化器使用了ICP对数据访问进行优化。ICP关闭时的执行计划显示use where。

四、案例分析

 

以上面的查询为例,关闭ICP时,存储引擎通前缀index first_name(视为index key)访问表中数据,并在MySQL server层根据where条件last_name like ‘%nta’(视为index filter)进行过滤。

开启ICP时,MySQL server把index filter(last_name like ‘%nta’)推到存储引擎层,在存储引擎内部通过与where条件last_name like ‘%nta’的对比,直接过滤掉不符合条件的数据,然后返回最终数据给MySQL server层。该过程减少了回表操作,只访问符合条件的1条记录并返回给MySQL Server ,有效的减少了io访问和各层之间的交互。

ICP关闭时 ,仅仅使用索引作为访问数据的方式。

MySQL ICP(Index Condition Pushdown)特性

ICP 开启时 ,MySQL将在存储引擎层 利用索引过滤数据,减少不必要的回表。

MySQL ICP(Index Condition Pushdown)特性

注意虚线的using where表示如果where条件中含有没有被索引的字段,则还是要经过MySQL Server 层过滤。

五、ICP的使用限制

1. 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法 。

2. 支持InnoDB和MyISAM表。

3. ICP只能用于二级索引,不能用于主索引。

4. 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。

5. ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

6. MySQL 5.6版本的不支持分表的ICP功能,5.7版本的开始支持。

7. 当sql使用覆盖索引时,不支持ICP优化方法。

  

原文链接:http://blog.itpub.net/22664653/viewspace-1210844/

posted @ 2018-07-06 13:49  HappyDeveloper  阅读(3515)  评论(0编辑  收藏  举报