锐客网

  1. 首页 > 睿知 > it技术 > >

MYSQL ICP 索引下推为什么他行，你不行（）

IT知识mysql为什么

农村四月闲人少，勤学苦攻把名扬。这篇文章主要讲述MYSQL ICP 索引下推为什么他行，你不行？相关的知识，希望能为你提供帮助。





mysql 的ICP 估计大家也都知道，Index condition pushdown,但这个东西怎么用，有什么用，什么时候用，估计能答得上来的人就不多了。
其实这篇文字写的有点费劲，我大约一天的时间，没有得到答案，到底什么时候能走ICP。所以下面是我通过大约一天的测试后得到的结果。


  索引条件下推(ICP)是MySQL使用索引从表中检索行的一种优化。如果没有ICP，存储引擎将遍历索引来定位基表中的行，并将它们返回给MySQL服务器，MySQL服务器将计算这些行的WHERE条件。启用了ICP，如果只使用来自索引的列就可以评估WHERE条件的一部分，那么MySQL服务器将这部分WHERE条件下推到存储引擎。然后，存储引擎通过使用索引项来评估推入的索引条件。并且能用到的查询类型 range ref eq_ref 等类型。 


上面是比较官方的说法，如果用大白话来说明，一句话，减少在使用二级索引查询中因为二级索引中不包含某些字段，而造成的部分不再INNODB 引擎层处理的数据上行到 SERVER 层，造成的I/O消耗。


1 我们使用下面的表来做一个实验，下面的两张图说明的表结构，数据量，以及查询到底有么有走ICP，显然是走了ICP











【MYSQL ICP 索引下推为什么他行，你不行（）】显然看上去走ICP 是一件很简单的事情，实际上我们看下边的表









上图明显的符合最上面的走ICP的条件，为什么上面的查询没有走ICP ，而仅仅是走了索引扫描。



为啥，人家的查询走了ICP ，你的没有走ICP ，
我们翻过来看官方文档，关于在什么时候走ICP 的条件



translation
1  首先你的查询type 的是range ,ref,eq_ref, 要是const 抱歉走不了，也没有必要走 (但上边的图上的查询类型符合ref,为啥不走)
2  数据库引擎要INNODB OR MYISAM， （数据库引擎是 INNODB，为啥还不走）
3  ICP 仅仅服务于二级索引，主键查询时走不了ICP的，（问题是我查询时差的非主键，使用的也是二级索引，为啥还不走）
4  ICP 不支持在虚拟列上创建的二级索引 （我是实体列，为啥不走）
5  条件是子查询的走不了 （我不是子查询，为啥不走）
6  条件是函数，也不能走 （我不是函数，我给了具体的值，为啥还不走）
7  触发条件的不能走 （我不是，为啥还不走）


上面的符合5.7 上列出来不能走ICP的条件，上图中的查询也符合走ICP的条件，为什么不走呢。


我们稍微变化一下查询，我们可以看到，即使查询中没有结果，还是走了ICP



那么问题来了，到底怎么才能走ICP ， 大家稍微可以注意，凡是走ICP 的时候，大部分情况下都是通过 INDEX 获取的数据的范围，与索引之外的其他条件相比，不能快速界定要查找的数据。（估计这样说，我也看不明白我说什么，画一张图），通过图可以看到，一般走ICP的时候，大部分情景都是非索引的条件，比走索引更能定位要查询最终的结果，同时走索引还是可以排除一大部分数据的情况下，否则就走全表扫描了（注意：是大部分情景）



在这样的情况下，去走ICP ，所以在都符合官7条的那些数据的情况下，为什么不走ICP ，大部分原因是通过非索引包含的条件并不比单纯走索引定位的数据量少。


注：查看是否走ICP 的详情，通过  SELECT NAME, COUNT_RESET FROM  information_schema.INNODB_METRICS WHERE NAME LIKE icp%;
部分字段含义
icp_:评估ICP的行数
icp_no_match:与推入位置条件不完全匹配的行数
icp_out_of_range:所检查的不在有效扫描范围内的行数
icp_match:完全匹配推入位置条件的行数
如果都为0 则说明没走ICP ,EXPLAIN 中没有 index condition 也是没有走ICP。


其实本篇文字，还有一点想说的是，有的时候索引的建立应该是找更能界定查找数据范围的字段，如果走了ICP ，其实是不是也可以考虑，你目前的索引建立的与你当前的查询不大匹配的可能性。






    推荐阅读


    上一篇：Mongodb模式设计案例一例

    下一篇：MYSQL如果索引优化仅仅是添加索引，too young to simple