一起学习Mysql索引三（ICP,索引条件下推）

上一篇文章一起学习Mysql索引二（索引的高性能策略）中我们提到了Mysql5.7版本的一个改进："索引条件下推"（index condition pushdown）。那么，今天就让我们来揭开它的神秘面纱。

从ICP（index condition pushdown）的字面意思来看，大家疑惑的点应该就是这个"pushdown"--下推了。什么是下推，下推到哪里，有什么用？带着疑问，我们先从关闭和开启ICP对一些sql语句的影响，然后再进一步的解答提出的问题。

首先，我们可以通过如下语句开启或关闭Myslq的ICP特性：

SET optimizer_switch = 'index_condition_pushdown=off'; //关闭
SET optimizer_switch = 'index_condition_pushdown=on';  //开启

Mysql 新版本默认开启该特性，然后我们准备一张表：

CREATE TABLE `demo` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(11) DEFAULT '0',
`nid` int(5) DEFAULT NULL,
`country` varchar(10) DEFAULT '',
`name` varchar(10) DEFAULT '',
`status` tinyint(1) DEFAULT '1',
`num` int(10) DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
KEY `ix_pnnc` (`pid`,`nid`,`name`,`country`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

其中表中的数据量如下图所示：

不使用ICP的情况

关闭ICP:

SET optimizer_switch = 'index_condition_pushdown=off';

然后调用如下语句进行三次查询：

select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%';

通过show profiles查看三条语句的查询时间分别是：

+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------+
|       740 | 0.07347475 | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
|       741 | 0.07267875 | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
|       742 | 0.072476   | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
+----------+------------+-------------------------------------------------------------------------------------------------+

使用ICP的情况

开启ICP：

SET optimizer_switch = 'index_condition_pushdown=on';

还是调用上面那条sql语句三次，然后show profiles查看查询时间：

+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------+
|       723 | 0.02307625 | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
|       724 | 0.0218665  | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
|       725 | 0.02217525 | select * from demo where pid =14 and nid like '%2%' and country like '%a%' and name like '%a%' |
+----------+------------+-------------------------------------------------------------------------------------------------+

我们看到，在开启ICP后，速度提升了3倍之多。

通过explain我们看看两者的区别：

关闭ICP情况：

开启ICP情况：

我们可以看到，在Extra字段上，关闭ICP时，用的是Using where，而在开启ICP时，使用的是Using index condition。

通过上面的对比我们可以看到，在使用了ICP之后，确实提升了我们的查询性能，可是ICP是不是针对所有的sql语句都有效果呢？我们在来看执行下面的语句后，三次查询时间的差别：

select * from demo where pid =14 and num > 0;

关闭ICP：

+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|       100| 0.07347475 | select * from demo where pid =14 and num > 0 |
|       101| 0.07788525 | select * from demo where pid =14 and num > 0 |
|       102| 0.07912425 | select * from demo where pid =14 and num > 0 |
+----------+------------+-----------------------------------------------+

开启ICP:

+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|       117| 0.07916325 | select * from demo where pid =14 and num > 0 |
|       118| 0.08040075 | select * from demo where pid =14 and num > 0 |
|       119| 0.07897455 | select * from demo where pid =14 and num > 0 |
+----------+------------+-----------------------------------------------+

我们发现，对于这条语句，开启或关闭ICP并不会对查询有任何性能提升。

那么下面就和强哥一起来看看是为什么吧。

怎么理解ICP

Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。如果禁用ICP，引擎层会穿过索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。ICP启用，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层。存储引擎通过使用索引条目，然后推索引条件进行评估，使用这个索引把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。

用两张图解释下：

关闭ICP：

此时，索引符合之前推文提过的最左前缀原理，当多列索引的某一列是范围查询后，之后的字段便不会走索引。

开启ICP:

开启ICP后，查询同样符合最左前缀规则，但是当多列索引的某一列是范围查询后，之后的字段还是会被下推到存储引擎（Storage Engine）层进行条件判断，过滤出符合条件的数据后再返回给Server层。而由于在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数。从而提升了性能。

 

分析试验数据提升原因

关闭ICP是这样的：
1. 从索引里面取出下一条pid=14的记录，然后利用主键字段读取整个行。
2. 然后对这个完整的行利用其余的条件这个进行判断看是否符合条件，在Server层进行过滤和处理。

开启ICP是这样的：

1. 从索引里面取出下一条pid=14的记录，然后利用这个索引的其他字段条件进行判断，如果条件成立，执行第2步。在引擎层上进行过滤和处理。
2. 在上一步中筛选出来符合条件的才会利用主键索引里面找到这个完整行，返回。

也就是说： 在没有ICP前，由于优化器只能只能使用前缀索引来过滤满足条件的查询，那么mysql只能够利用索引的第一个字段pid，来扫描demo表满足pid=14条件的记录，而后面的nid和country由于使用了模糊查询，而不能在索引中继续过滤满足条件的记录，这样就导致了Server层对demo表的扫描增加了许多；

有了ICP，mysql在读取demo表前，继续检查满足nid和country条件的记录,这个行为在引擎层完成。直接把过滤好的返回给Server层，就减少了Server层的操作。总之是把之前在SERVER层的下推到引擎层去处理。

这里也就解释了ICP（索引条件下推）其实就是将索引条件下推到引擎层啦。

ICP的使用限制

ICP虽然挺好用的，但是并不是所有的sql都能够通过ICP得到性能提升，就如我们上面的第二条sql就无法通过ICP减少查询时间。因为如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。

这里列出几点ICP的相关限制：

• 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref,  ref_or_null 类型的访问数据方法 。

• 支持InnoDB和MyISAM表。

• ICP只能用于二级索引，不能用于主索引。

• 并非全部where条件都可以用ICP筛选。如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。

• ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

• 5.6 版本的不支持分表的ICP 功能，5.7 版本的开始支持。

• 当sql 使用覆盖索引时，不支持ICP 优化方法。

结论：

ICP的优化在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数，提升了性能。

需要index condition pushdown 的query通常索引的字段出现where子句里面都是范围查询。比如：

select * from tb where tb.key_part1 < x and tb.key_part2 = y       
select * from tb where tb.key_part1 = x andtb.key_part2 like '%yyyy%'
select * from tb where tb.key_part1 > x and tb.key_part1 < y and tb.key_part1 > xx and tb.key_part2 < yy

但是需要注意的是：

如果索引的第一个字段的查询就是没有边界的比如 key_part1 like '%xxx%'，那么不要说ICP，就连索引都会没法利用。

如果select的字段全部在索引里面，那么就是直接的index scan了，没有必要什么ICP。

参考：

https://www.cnblogs.com/zhoujinyi/archive/2013/04/16/3016223.html

http://blog.codinglabs.org/articles/index-condition-pushdown.html

关注公众号获取更多内容，有问题也可在公众号提问哦：

 

强哥叨逼叨

叨逼叨编程、互联网的见解和新鲜事