Fork me on GitHub

MySQL之 索引下推

MySQL之 索引下推

1.开始

  • 索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加,用于优化数据的查询。
  • 当你不使用ICP,通过使用非主键索引(普通索引or二级索引)进行查询,存储引擎通过索引检索数据,然后返回给MySQL服务器,服务器再判断是否符合条件。
  • 使用ICP,当存在索引的列做为判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
  • 官方文档

2.适用场景

  • 当需要整表扫描,e.g.:range,ref,eq_ref....
  • 适用InnoDB引擎和MyISAM引擎查询(5.6版本不适用分区查询,5.7版本可以用于分区表查询)。
  • InnoDB引擎仅仅适用二级索引。(原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区)。
  • 子查询条件不能下推。触发条件不能下推,调用存储过程条件不能下推。

3.小示例

  • 当我们创建一个用户表(userinfo),其中有字段:id,name,age,addr。我们将name,age建立联合索引。

    当我们执行:select * from userinfo where name like "ming%" and age=20;
    
  • 对于MySQL5.6之前:我们在索引内部首先通过name进行查找,在联合索引name,age树形查询结果可能存在多个,然后再拿着id值去回表查询,整个过程需要回表多次。

  • 对于MySQL5.6之后:我们是在索引内部就判断age是否等于20,对于不等于20跳过。因此在联合索引name,age索引树只匹配一个记录,此时拿着这个id去主键索引树种回表查询全部数据,整个过程就回一次表。

  • 如下:

    当Extra值为:Using index condition.表示使用索引下推。
    
  • 通过索引下推对于非主键索引进行优化,可有效减少回表次数,从而提高效率。

  • 关闭索引下推命令

    set optimizer_switch='index_condition_pushdown=off';
    
  • MySQL的server层和存储引擎层是如何交互的

  • MySQL执行计划extra中的using index 和 using where using index 的区别

posted @ 2020-04-02 20:27  是阿凯啊  阅读(7437)  评论(0编辑  收藏  举报