乐趣区

关于数据库:MySql索引下推知识分享

作者:刘邓忠

Mysql 是大家最罕用的数据库,上面为大家带来 mysql 索引下推知识点的分享,以便坚固 mysql 基础知识,如有谬误,还请各位大佬们斧正。

1 什么是索引下推

索引下推 (Index Condition Pushdown,索引条件下推,简称 ICP),是 MySQL5.6 版本的新个性,它能够在对联结索引遍历过程中,对索引中蕴含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,能无效的缩小回表次数(目前咱们应用的 mysql 版本较高,个别大家可能感觉这是失常的,然而 mysql5.6 之前都不是这样实现的,上面会细细道来)。

1.1 实用条件

咱们先来理解一下索引下推的应用条件及限度:

  • 只反对 select。
  • 当须要拜访全表时,ICP 用于 range,ref,eq_ref 和 ref_or_null 拜访类型。
  • ICP 可用于 InnoDB 和 MyISAM 表,包含分区的 InnoDB 和 MyISAM 表。(5.6 版本不实用分区表查问,5.7 版本后能够用于分区表查问)。
  • 对于 InnDB 引擎只实用于二级索引(也叫辅助索引),因为 InnDB 的聚簇索引会将整行数据读到 InnDB 的缓冲区,这样一来索引条件下推的次要目标缩小 IO 次数就失去了意义。因为数据曾经在内存中了,不再须要去读取了。
  • 在虚构生成列上创立的辅助索引不反对 ICP(注:InnoDB 反对虚构生成列的辅助索引)。
  • 应用了子查问的条件无奈下推。
  • 应用存储过程或函数的条件无奈下推(因为因为存储引擎没有调用存储过程或函数的能力)。
  • 触发条件无奈下推。(无关触发条件的信息,请参阅官网材料:Section 8.2.2.3,“Optimizing Subqueries with the EXISTS Strategy”.。)

1.2 原理介绍

首先,咱们大抵回顾下 mysql 的根本架构:

MySQL 根本的架构示例图

MySQL 服务层次要负责 SQL 语法解析、生成执行打算等,并调用存储引擎层去执行数据的存储和查问。
索引下推的下推其含意就是指将局部下层(服务层)负责的事件,交给了上层(引擎层)去解决。
在 MySql 5.6 版本之前没有索引下推这个性能,从 5.6 版本后才加上了这个优化项。咱们先简略比照一下应用和未应用 ICP 两种状况下,MySql 的查问过程吧。

1) 未应用 ICP 的状况下:

  • 存储引擎读取索引记录;
  • 依据索引中的主键值,定位并读取残缺的行记录;
  • 存储引擎把记录交给 Server 层去检测该记录是否满足 WHERE 条件。

2) 应用 ICP 的状况下:

  • 存储引擎读取索引记录(不是残缺的行记录);
  • 判断 WHERE 条件局部是否用索引中的列来做查看,条件不满足,则解决下一行索引记录;
  • 条件满足,应用索引中的主键去定位并读取残缺的行记录(就是所谓的回表);
  • 存储引擎把记录交给 Server 层,Server 层检测该记录是否满足 WHERE 条件的其余部分。
     

2 具体示例

下面介绍了基本原理,上面应用示例,带大家更直观的进行了解(注:以下示例基于 InnoDB 存储引擎。)
首先,咱们新建一张用户表(jxc_user),设置 id 为主键索引,并创立联结索引(name, age)。

咱们先看一下该表主键索引的大抵构造示例:
 

主键索引构造示例图

而后咱们再看一下该表联结索引的大抵构造示例:
 

联结索引构造示例图

如果当初有一个需要,要求检索出表中名字第一个字是张,而且年龄等于 10 岁的所有用户。示例 SQL 语句如下:

 
  1. select id,name,age,tel,addr from jxc_user where name like '张 %' and age=10;

依据索引最左匹配准则,下面这个 sql 语句在查索引树的时候,只能用“张”,查到第一个满足条件的记录:id 为 1。
那接下来咱们具体看一下 应用与未应用 ICP 的状况。

2.1 未应用 ICP 的状况

在 MySQL 5.6 之前,存储引擎依据联结索引先找到 name like‘张 %’的主键 id(1、4),再逐个进行回表扫描,去聚簇索引找到残缺的行记录,返回 server 层,server 层拿到数据后,再依据条件 age=10 对拿到的数据进行筛选。大抵的示意图如下:

从上图,能够看到须要回表两次,存储引擎并不会去依照 age=10 进行过滤,相当于联结索引的另一个字段 age 在存储引擎层没有发挥作用,比拟节约。

2.2 应用 ICP 的状况

而 MySQL 5.6 当前,存储引擎会依据(name,age)联结索引,找到 name like‘张 %’,因为联结索引中蕴含 age 列,所以存储引擎间接再联结索引里依照条件 age=10 进行过滤,而后依据过滤后的数据再顺次进行回表扫描。大抵的示意图如下:

从上图,能够看到只是 id=1 的数据,回表了一次。

除此之外咱们还能够看一下执行打算,看到 Extra 一列里 Using index condition,就是用到了索引下推。

3 控制参数

Mysql 索引下推性能默认是开启的,能够用零碎参数 optimizer_switch 来管制是否开启。
查看状态命令:
select @@optimizer_switch;

敞开命令:set optimizer_switch=”index_condition_pushdown=off”;
开启命令:set optimizer_switch=”index_condition_pushdown=on”;

4 总结

回表操作:当所要查找的字段不在非主键索引树上时,须要通过叶子节点的主键值去主键索引上获取对应的行数据,这个过程称为回表操作。
索引下推:索引下推次要是缩小了不必要的回表操作。对于查找进去的数据,先过滤掉不符合条件的,其余的再去主键索引树上查找。

5 参考文献

  • https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
  • http://mysql.taobao.org/monthly/2015/12/08/
退出移动版