mysql-ICP

ICP（index condition pushdown）的作用：

ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine。

ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

ICP过程：

the storage engine traverses the index to locate rows in the base table， if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. 

存储引擎将遍历索引来查找基表中的行，如果WHERE只能使用索引中的列来评估条件的某些部分 ，MySQL服务器会将这部分WHERE条件下降到存储引擎。然后，存储引擎使用索引条目评估推送索引条件，并且只有满足时才从表中读取该行。

使用限制：

ICP用于range，ref，eq_ref，和ref_or_null访问方法时，需要访问完整的表行

ICP可用于innodb和myisam表，包括分区innodd和myisam表

对于innodb表，ICP仅用于二级索引。（ICP的目的是减少全行读取的次数，从而减少IO操作），对于innodb聚集索引，完整的记录已被读入到innodb缓冲区，在这种情况下，ICP不会减少io

ICP不支持在虚拟生成的列上创建二级索引，innodb支持。

引用子查询的条件不能被下推

涉及存储功能的条件不能被下推，存储引擎不能调用存储函数

触发条件不能被pushdown

在不使用ICP索引扫描的过程：

1.首先读取索引元组，然后使用索引元组来定位并读取整个表格行。

2.测试适用于该表where的部分条件。根据测试结果接受或拒绝该行。

使用ICP扫描的过程：

1.获取下一行的索引元组（但不是全表行）

2.只使用索引列检查适用此表的where部分条件，如果不符合，就转到下一行的索引元组

3.如果满足条件，则使用索引元组来定位并读取整个表格行

4.测试适用于该表where的部分条件。根据测试结果接受或拒绝该行

当使用ICP时，通过explain后查看执行计划中Extra会显示 Using index condition，当在读取全表行时就不会显示

例如：

二级索引：（zipcode,lastname,firstname）

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

说明：不使用ICP，mysql通过zipcode='95054'来扫描，lastname LIKE '%etrunia%'不会用于限制必须扫描的行数

使用ICP后，mysql会在读取整个行之前会通过lastname LIKE '%etrunia%'进行筛选。

可以通过SET optimizer_switch = 'index_condition_pushdown=off/on';来关闭或开启ICP

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结