Index Condition Pushdown优化
1 概述
Index Condition Pushdown(ICP)是MySQL在使用索引获取数据时的一种优化手段，使用ICP可以在使用索引获取数据时避免读取那些不符合查询条件的数据。本文主要翻译自MySQL官方文档对该优化的介绍。
2 介绍
ICP主要用于在使用索引查询数据时进行优化。没有ICP优化时，存储引擎通过索引定位数据并读取这些被定位的数据返回给MySQL服务器，MySQL服务器会根据WHERE条件对这些数据进行过滤。当启用了ICP优化后，WHERE语句中那些仅仅使用优化器选中的索引列中的字段会被MySQL服务器推送给存储引擎，在存储引擎使用索引定位数据并根据索引中的字段进行WHERE条件过滤，然后在读取表中数据返回给MySQL服务器，MySQL服务器会对WHERE语句中剩下的条件进行过滤。有了ICP可以减少存储引擎访问表数据的次数，也减少了MySQL服务器访问存储引擎的次数。
3 启用条件
ICP优化必须满足如下条件：
-
ICP可以用于使用
range、ref、eq_ref、ref_or_null访问方法时并需要访问表中所有行时。 -
ICP可以用于
InnoDB、MyISAM表，包括InnoDB和MyISAM分区表。 -
对于
InnoDB，ICP只能用于二级索引。使用ICP的木器时为了减少完整表行的读取次数并较少IO操作，InnoDB一级索引（聚簇索引）已经包含了表行的所有列，并且已经位于InnoDB缓冲区内，使用ICP也就没有意义了。 -
InnoDB可以基于虚拟列建立二级索引，而ICP则不支持用于哪些建立在虚拟列上的二级索引。 -
基于子查询的条件没法下放给存储引擎进行优化。
-
基于函数的条件没法下放，因为存储引擎没法执行函数。
-
触发器中的条件没法下放。
4 优化过程
为了理解ICP优化如何工作的，先看下没有ICP优化时的索引扫描过程：
- 读取下一行数据。首先读取索引数据，并根据索引数据定位读取完整的一行表数据返回给MySQL服务器；
- MySQL服务器根据WHERE语句中的条件对返回的数据进行测试，根据测试结果选择接受或放弃改行数据。
如果启用了ICP优化，则索引扫描过程如下：
- 读取下一个索引数据（不是完整的表行数据）；
- 对于那些仅仅使用索引数据就可以进行测试的部分WHERE条件进行过滤，如果测试不通过则读取下一个索引数据；
- 如果测试通过，则根据此索引数据定位该行表数据并读取完整的一行表数据返回给MySQL服务器；
- MySQL服务器测试剩下的WHERE条件，基于测试结果选择接受或放弃改行数据。
如果查询优化器使用了ICP优化，那么在EXPLAIN的Extra会显示Using index condition。
例如：
表people包含了个人信息以及他们的地址，并且在表上定义了索引INDEX(zipcode, lastname, firstname)，如果我们知道一个人的zipcode，但是不知道他们的lastname，那么我们可以使用如下语句进行查询:
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
MySQL可以使用索引去定位那些zipcode='95054'的信息，但是第二个条件lastname LIKE '%etrunia%'却没法用于减少必须要扫描的表行，所以如果没有ICP优化，在执行此查询时必须读取所有zipcode='95054'的表行数据。
如果启用了ICP优化，因为MySQL使用了索引INDEX(zipcode, lastname, firstname)，并且WHERE语句中的第二部分lastname LIKE '%etrunia%'仅仅使用了索引中的列lastname，所以在读取完整表行数据前可以基于此过滤那些索引中lastname不符合条件的索引数据，这样就能避免对那些满足zipcode='95054'但是不满足条件lastname LIKE '%etrunia%'表行数据的访问。
ICP可以通过系统变量optimizer_switch中的index_condition_pushdown进行启用和关闭：
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
