mysql icp特性_MYSQL的Where条件及ICP(index condition pushdown)特性

关系型数据库中，数据组织涉及到两个最基本的结构：表与索引。

表中存储的是完整记录，一般有两种组织形式：①堆表(所有的记录无序存储)②聚簇索引表(所有的记录，按照记录主键进行排序存储)。

索引中存储的是完整记录的一个子集，用于加速记录的查询速度，索引的组织形式，一般均为B+树结构。

表结构：

索引：

例子：

Index Key

用于确定SQL查询在索引中的连续位置(起始位置Index First Key+结束位置Index Last Key)的查询条件，被称之为Index Key。

一个范围包含一个起始位置一个终止位置，所以Index Key 也被拆分为Index First Key和Index Last Key。

Index First Key

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

规则：从索引的第一个键值开始，检查其在where条件中是否存在，

1、若存在并且条件是=、>=，则将对应的条件加入Index First Key之中，继续读取索引的下一个键值，使用同样的提取规则；

2、若存在并且条件是>，则将对应的条件加入Index First Key中，同时终止Index First Key的提取；

3、若不存在，同样终止Index First Key的提取。

针对上面的SQL，应用这个提取规则，提取出来的Index First Key为(a >3)。由于c的条件为 >，提取结束。

Index Last Key

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

规则：

从索引的第一个键值开始，检查其在where条件中是否存在，

1、若存在并且条件是=、<=，则将对应条件加入到Index Last Key中，继续提取索引的下一个键值，使用同样的提取规则；

2、若存在并且条件是 < ，则将条件加入到Index Last Key中，同时终止提取；

3、若不存在，同样终止Index Last Key的提取。

针对上面的SQL，应用这个提取规则，提取出来的Index Last Key为(a <=20, b<14)，提取结束。

Index Filter

在完成Index Key的提取之后，我们根据where条件固定了索引的查询范围，但是此范围中的项，并不都是满足查询条件的项。

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

针对上面的用例SQL，索引第一列只包含 >、<= 两个条件，因此第一列可跳过，将余下的c、d两列加入到Index Filter中。因此获得的Index Filter为 b <14 and c !=7 。

Table Filter

Table Filter是最简单，最易懂，也是提取最为方便的。

【则是最后一道where条件的防线，用于过滤通过前面索引的层层考验的记录，此时的记录已经满足了Index First Key与Index Last Key构成的范围，

并且满足Index Filter的条件，回表读取了完整的记录，判断完整记录是否满足Table Filter中的查询条件，同样的，若不满足，跳过当前记录，继续读取索引的下一条记录，若满足，则返回记录，此记录满足了where的所有条件，可以返回给前端用户。】

规则：

1、所有不属于索引列的查询条件，均归为Table Filter之中。

针对上面的用例SQL，Table Filter就为 d = ‘name’。

ICP：

ICP就是index condition pushdown 就是Index Filter

在MySQL 5.6之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index Last Key范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤。

而在MySQL 5.6之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率。

ICP使用条件：

1、只能用于二级索引(secondary index)。

2、explain显示的执行计划中type值。(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。

3、ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表(5.7将会解决这个问题)