MySQL Index Condition Pushdown

官方原理：

摘抄

http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:

1. Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
2. Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

When Index Condition Pushdown is used, the scan proceeds like this instead:

1. Get the next row’s index tuple (but not the full table row).
2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
3. If the condition is satisfied, use the index tuple to locate and read the full table row.
4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

Index Condition Pushdown (ICP)是MySQL 5.6 以上 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

a 当关闭ICP时,index 仅仅是data access 的一种访问方式，存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。

b 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.

优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数，提高查询语句性能

set optimizer_switch=’index_condition_pushdown=off';