乐趣区

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

jiezi

作者：刘邓忠

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/
退出移动版