MySQL的索引条件推送（ICP）

这是今天在阅读高性能MySQL(第3版)这本书的时候注意到这么一个概念。因为这本书是基于MySQL5.5版本的，所以作者在讲解高性能索引时提到了MySQL5.6版本对某些功能的优化，原文如下：

上面提到的很多限制都是由于存储引擎API设计所导致的，目前的API设计不允许MySQL将过滤条件传到存储引擎层。如果MySQL在后续版本能够做到这一点，则可以把查询发送到数据上，而不是像现在这样只能把数据从存储引擎拉到服务器层，再根据查询条件过滤。在本书写作之际，MySQL 5.6版本（未正式发布）包含了在存储引擎API上所做的一个重要的改进，其被称为“索引条件推送（index condition pushdown）”。这个特性将大大改善现在的查询执行方式，如此一来上面介绍的很多技巧也就不再需要了。 ——《高性能MySQL》(第三版)

索引条件推送，简称ICP，是指MySQL在根据索引提取数据时非完全匹配索引的情况下可以使用索引中覆盖并且需要过滤的字段预先过滤掉不符合条件的数据，从而减少回表次数，提高性能。

假设有一张表person，该表存在字段id(主键)，存在name,age,create_time四个字段，我们建立age和name的联合索引idx_age_name,

现在我们查询age=18,name='杨林'的记录，SQL如下：

EXPLAIN SELECT * from person WHERE age=18 and name='杨林'；

我们来看一下MySQL生成的查询计划：

在这个查询计划中我们看到该SQL使用了我们添加的联合索引，但是在Extra并没有出现Using index condition，说明这条SQL并没有使用ICP，那什么情况下会使用ICP呢？我们把SQL变一下：

EXPLAIN SELECT * from person WHERE age=18 and name like '%杨林%';

查询计划如下：

注意Extra里面的值Using index condition,这就表示该SQL使用了ICP。我们做了什么才会出现这样的情况呢？其实我们只是把name从等值查询变成了模糊查询。我们来看一下如果关闭ICP会怎样：

SET optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * from person WHERE age=18 and `name` like '%杨林%'

查询计划如下：

Extra里面就变成了Using where，这表示查询条件的过滤是在服务器而不是存储引擎里面完成的。

MySQL在查询数据时会先根据索引树提取数据，比如这里的索引idx_age_name, 这是一个二级索引。我们知道二级索引的索引树的叶子结点存储的是主键值，因为我们查询的是所有字段，所以无法使用覆盖索引，需要回表查询所有字段数据。

SQL1：SELECT * from person WHERE age=18 and name='杨林';完全匹配了该索引，由索引可以直接回表提取数据，无需ICP。

SQL2: SELECT * from person WHERE age=18 and name like '%杨林%';注意name使用的是模糊匹配，所以无法完全匹配索引，但是该联合索引包含了name这个字段，在无法使用ICP或者关闭ICP的情况下，只能由索引树匹配age=18的数据，然后回表查询记录行，返回服务器后再进行模糊匹配name字段，在开启ICP的情况下，则可以在索引树中直接模糊匹配name字段，过滤后再回表查询，这两个操作有什么不同呢？很明显，前一个操作需要回表的数据可能会更多，因为它并没有经过另一个条件name的过滤，所以在大多数情况，特别是数据量巨大且name字段重复率低的情况下效率比不上后一个操作。