[Mysql] Mysql ICP优化

Doctor_Doom
2,521 阅读4分钟

Mysql ICP优化是mysql 5.6+版本中添加的新特性. 是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数.

Mysql ICP存储引擎查询优化模型

1. 在没有使用ICP查询优化的时候，查询模型:

在没有使用ICP优化的时候,  Storage Engine只将满足index key条件的索引记录对应的整行记录取出，返回给server层.

 server层对返回的数据,使用后面的where条件过滤，最后返回给客户端.

2. 使用ICP优化的模型

storage层： 首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤 将满足的index filter条件的索引记录才去回表取出整行记录返回server层 不满足index filter条件的索引记录丢弃，不回表、也不会返回server层

 server 层： 对返回的数据，使用table filter条件做最后的过滤。

[上述描述来自于 Mysql索引ICP]

ICP使用条件: 

1. 当sql需要全表访问的时候,ICP优化策略可用于range,ref,eq_ref, ref_or_null类型的查询 

2. ICP可用于Innodb和MyIsam表 (ICP 在Mysql 5.6版本不支持分区表,5.7版本中解决了)

 3. 对于Innodb表,ICP只能用于二级索引(secondary index). ICP的目标就是减少完全行数据读取的数量,从而减少IO操作. 对于Innodb Cluster Index来说, 行数据已经读取到Innodb缓存中, 在这样的场景下使用ICP并不能减少IO. (ps: Innodb cluster index是索引组织表, cluster index的叶子节点就是行数据了) 

4. 并非所有的where条件都可以用ICP优化. 

  1. 子查询相关的where条件不能使用ICP; 
  2. 使用了存储函数的where条件不能使用ICP, 因为存储引擎不能调用存储函数; 
  3. 如果where条件不在select 查询采用的索引中, 还是得读取index过滤的所有数据到Server端做where过滤.
  4. 当sql使用覆盖索引的时候,不支持ICP优化 
  5. trigger condition ?? 

5. ICP加速效果取决于存储引擎通过ICP优化筛选的数据比例 

mysql ICP优化也是属于执行计划的一部分, 可以通过explain查看SQL执行计划,确定是否采用了ICP优化. 如果采用了ICP优化, Extra: Using Index Condition

FAQ: 

Q: 在不使用ICP优化的时候, where范围查询之后的条件不走index. 这句话是什么意思? 和执行计划相关, 能否解释? 

A: 

Q: 关于ICP优化底层结构的思考. 是不是只能在使用联合索引的时候才能进行ICP优化? 联合索引可以是Cluster index也可以是secondary index对ICP优化是否有影响? mysql在执行计划中可以有多个索引候选,执行的时候选择哪个索引过滤. 

 A: 

(关于mysql多个索引候选的回答) mysql SQL在执行的时候如果有多个索引候选项, mysql只会选择它认为最有效率的索引.一次查询只会使用到一个索引.

 (关于ICP底层结构的回答) ICP优化工作： 在没有使用ICP优化的时候,先在存储引擎中使用index过滤数据(index fileter), 然后将数据返回到Server进行where过滤(Table Filter); 在使用了ICP优化的时候, 先在存储引擎中使用index过滤数据(Index Filter), 然后使用索引中的其他列做ICP优化, 最后满足条件的数据再到Server层做where过滤(Table Filter). 

↑上面关于使用ICP优化的隐含了不少条件, SQL执行首先得命中了索引(expalin仅供分析,实际执行还有出入), 而且这个索引还得是联合索引, 只有联合索引才有多余的列的信息,才能进行筛选数据. 当然ICP优化的列where条件得再联合索引的列中. 

（关于联合索引是Cluster index还是secondary index对icp优化的影响的回答) 还是参考ICP使用条件,联合索引如果是Cluster index, ICP优化是不支持的.

复合索引使用规则 

1. 最左前缀匹配原则 (a,b,c) 复合索引在 a ,(a,b), (a,b,c)情况下可以使用 

索引使用原则 

1. !=查询不能使用 2. 列参与数学运算函数 3. like 左通配'%xx' 4. mysql发现全表扫描比索引快 5. 联合索引,前一个是范围查询后面即使是复合最左前缀原则,也不能使用复合索引 

参考文献:
1. [Mysql ICP优化官方文档]     dev.mysql.com/doc/refman/…
2. [HappyShare.Mysql Icp(Index Condition Pushdown)特性 ]   www.cnblogs.com/Terry-Wu/p/…
3. [木有sky.Mysql索引Icp]    www.jianshu.com/p/c6483ded0…

avatar
文章
阅读
粉丝