MySQL 5.6 新功能之 Index Condition Pushdown （ICP）

http://www.cnblogs.com/zhoujinyi/archive/2013/04/16/3016223.html
怎么理解Index_Condition_Pushdown？

        Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。如果禁用ICP，引擎层会穿过索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。ICP启用，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层。存储引擎通过使用索引条目，然后推索引条件进行评估，使用这个索引把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数。关于到底是哪个filer下推，可以看SQL中的where条件，在数据库中提取与应用浅析

       ICP的优化用于range, ref, eq_ref, and ref_or_null访问方法，当这些需要访问全表的行。这个策略可以用于INNODB和MyISAM表。
请看下面的例子：
Index：idx_zip_com_add (`zipcode`,`company`,`address`(255))
不用ICP：

复制代码
set @@optimizer_switch = "index_condition_pushdown=off"

root@192.168.200.202 : test 03:45:19>explain ... where zipcode = 843000 and company like '%医院%' and address like '%新疆%';
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+-------------+
| id | select_type | table           | type | possible_keys   | key             | key_len | ref   | rows   | Extra       |
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | uc_user_offline | ref  | idx_zip_com_add | idx_zip_com_add | 4       | const | 905734 | Using where |
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+-------------+

root@192.168.200.202 : test 03:40:05>show session status like '%handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 2      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 0      |
| Handler_read_key           | 1      |
| Handler_read_last          | 0      |
| Handler_read_next          | 499998 |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 0      |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+

root@192.168.200.202 : test 03:40:37>show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000191 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000049 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000076 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000040 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000196 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000043 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 1.435768 | 1.448091 |   0.000000 |          248 |             0 |
| end                  | 0.000073 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000068 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000060 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
复制代码
使用ICP：

复制代码
set @@optimizer_switch = "index_condition_pushdown=on"

root@192.168.200.202 : test 03:45:47>explain ... where zipcode = 843000 and company like '%医院%' and address like '%新疆%';
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+------------------------------------+
| id | select_type | table           | type | possible_keys   | key             | key_len | ref   | rows   | Extra                              |
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+------------------------------------+
|  1 | SIMPLE      | uc_user_offline | ref  | idx_zip_com_add | idx_zip_com_add | 4       | const | 905734 | Using index condition; Using where |
+----+-------------+-----------------+------+-----------------+-----------------+---------+-------+--------+------------------------------------+

root@192.168.200.202 : test 03:46:35>show session status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 31619 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

root@192.168.200.202 : test 03:47:21>show profile cpu,block io for query 21;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000107 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000105 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000038 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000676 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000074 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000044 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000064 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
复制代码
分析：
通过上面的例子看出使用ICP比禁用ICP提升很多。
在没有ICP之前它是这样执行的
1. 从索引里面取出下一条zipcode=的记录，然后利用主键字段读取整个行。
2. 然后对这个完整的行利用其余的条件这个进行判断看是否符合条件，在Server层进行过滤和处理。

在ICP之前，Mysql在复合索引中，第一列是范围查询，第二列通常是无法使用索引的，建议第一列是=,<=>,is null。而在ICP出来之后，就没有了这个限制。

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

      从上面的SQL的执行计划看出，他们利用的索引都是一样的，都是用到他们的第一列（key_len=4）,所以ICP并不是影响优化器选择哪个索引，而是在于怎么利用这个索引。在上面的两个执行计划可以看出无论是否ICP两个都是利用idx_zip_com_add索引的zipcode列，两者使用的索引完全相同，只是处理方式不同。
      在没有ICP前，由于优化器只能只能使用前缀索引来过滤满足条件的查询，那么mysql只能够利用索引的第一个字段zipcode，来扫描XX表满足zipcode = 843000条件的记录，而后面的company和address由于使用了模糊查询，而不能在索引中继续过滤满足条件的记录，这样就导致了Server层对XX表的扫描增加了许多；
      有了ICP，mysql在读取XX表前，继续检查满足company和address条件的记录,这个行为在引擎层完成。直接把过滤好的返回给Server层，就减少了Server层的操作。总之是把之前在SERVER层的下推到引擎层去处理。

      从上面的测试当中看到，系统变量Handler%提升了很多，还有其他的变量也提升了（innodb_buffer_pool_read%,innodb_data_read%d等)，这样让Mysql在性能上面有很大的提升：一方面提升了查询性能，使得联合索引的范围查询速度得到很大的提升，另一方面节省了BP的内存空间。



总结：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
但是需要注意的是：
1. 如果索引的第一个字段的查询就是没有边界的比如 key_part1 like '%xxx%'，那么不要说ICP，就连索引都会没法利用。
2. 如果select的字段全部在索引里面，那么就是直接的index scan了，没有必要什么ICP。





ICP的使用限制



1 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref,  ref_or_null 类型的访问数据方法 。
2 支持InnoDB和MyISAM表。
3 ICP只能用于二级索引，不能用于主索引。
4 并非全部where条件都可以用ICP筛选。
   如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
5 ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
6 5.6 版本的不支持分表的ICP 功能，5.7 版本的开始支持。
7 当sql 使用覆盖索引时，不支持ICP 优化方法。