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 的区别