小白也能看懂的索引下推（看不懂来找我）

什么是索引下推：

索引下推（Index Condition Pushdown）是MySQL中的一个优化技术，可以通过将WHERE条件从server层下推到存储引擎层来提高查询性能。在MySQL 5.6及以上版本中，索引下推可以显著提高查询的性能，尤其是在查询大量数据的情况下。

MySQL的服务器可以分为server层和存储引擎层:

举例：

下面我们来看一个具体的例子来理解索引下推的实现过程。

假设我们有以下一张表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `key1` varchar(10) NOT NULL,
  `key2` varchar(10) NOT NULL,
   key3 varchar(10) not null,
  PRIMARY KEY (`id`),
  KEY `test_pk` (`key1`，`key2`)
) ENGINE=InnoDB;

其中，id是表的主键，key1和key2形成了一个联合索引，key3是普通的字段，现有如下查询语句；

SELECT * FROM test WHERE key1 > 'John' AND key2 >= 'Jack';

在传统的查询过程中，MySQL会按照如下的过程执行该查询：

1. key1是范围，存储引擎根据最左前缀原则，因此只能用到联合索引中的第一个索引key1定位到一条符合条件的记录；
2. 由于联合索引是二级索引，在叶子节点上可以找到主键id， 根据主键进行回表操作查到完整的一条记录，将该记录返回给server层；
3. server层判断key2 >='Jack’这个条件是不是成立，如果成立，则将结果发送给客户端，否则忽略该记录继续向存储引擎要下一条记录；
4. 跳到步骤一继续执行，直到在联合索引中找到所有key1 > 'John’的记录。

从上面的过程可以看出来，每查询到一条联合索引记录就会进行一次回表操作，回表操作会将至少一个聚簇索引的页面加载到内存中，比较耗时。同时我们也发现了key1 和key2都是联合索引的子项，是否可以在存储引擎层直接完成过滤呢？这样就不用频繁执行回表操作了，那这个就是索引下推的作用，有了索引下推之后，上面的操作会按照如下过程执行：

1. 存储引擎根据最左前缀原则，key1是范围，使用联合索引中的key1定位到第一条符合key1> 'John’的记录；
2. 存储引擎此时没有立即进行回表查询，而是找到当前联合索引涉及到的所有条件即：key1> ‘John’ and key2 >= ‘Jack’ ，如果当前记录不满足该条件则直接跳过这个联合索引记录，如果满足条件才执行回表操作，最终将一条符合条件的记录返回给server层；
3. server层再判断其他的条件（一些不能使用该联合索引判断的条件）是否成立，如果成立，发送给客户端，若不成立，跳过；
4. 跳到步骤一继续执行，直到找到所有key1> ‘John’ and key2 >= 'Jack’的记录;

如果在查询语句使用了‘索引下推’这个特性，在执行计划的Extra列中会显示Using Index condition，如下所示：

索引下推只适用于二级索引，目的是减少回表操作的次数；对于聚簇索引，叶子节点包含了所有的数据，直接可以在存储引擎层判断所有的条件。

问题：

看了上面的讲述，估计有同学会有如下的疑问：

索引下推是不是只适用于联合索引，毕竟只有联合索引才会包含多个索引？

答案并不是的，如果把test表的联合索引删除，对key1单独创建一个普通二级索引，那么如下的这个例子，也是用了索引下推，它分析的思路和上面是一样的；

SELECT * FROM test WHERE key1 > 'John' AND key1 like '%tom';

注意点：

还有一点，如果有同学在创建上面的表时只创建了三个键，id，key1, key2这三个，在执行

SELECT * FROM test WHERE key1 > 'John' AND key2 >= 'Jack';

的时候会发现并没有索引下推，而是using index；这是因为联合索引中已经包含了所有的字段，不再需要回表查询，而是使用覆盖索引进行了查询。

校验：大敦敦

参考文章：掘金小册《MySQL是怎样运行的：从根上理解MySQL》