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‘;