本文问题
- 什么是索引下推（
Index Condition Pushdown ICP）？索引下推的过程是怎样的？ - 在哪些情况下可以使用索引下推，索引下推有什么限制？
- 如何启用或禁用索引下推优化？
Index Condition Pushdown(ICP)
当MySQL使用一个索引来检索表中的行时，可以使用ICP作为一种优化方案。不使用ICP时，存储引擎通过索引检索基础表中的行并将符合WHERE条件中的行返回给客户端。启用ICP后，如果只需要通过索引中的列就能够评估行是否符合WHERE中的一部分条件，MySQL将这部分WHERE条件下推到存储引擎中，然后存储引擎评估使用索引条目来评估下推的索引条件，并只从表中读取符合条件的行。ICP可以减少存储引擎访问基础表的次数以及MySQL访问存储引擎的次数。
ICP适用情景
- ICP可以用于需要访问整个表中的行的
range,ref,eq_ref,ref_or_null访问模式。 - ICP可以用于
InnoDB和MyISAM，包括分区表。
ICP限制
- 对于
InnoDB表，ICP只适用于辅助索引。因为ICP的目标是减少全表读的数量从而减少I/O操作。对于InnoDB聚集索引，完整的记录已经被读取到InnoDB缓冲区，在这种情况下使用ICP不能降低I/O -
ICP不支持在虚拟生成列上创建的索引。InnoDB存储引擎支持在虚拟生成列上创建索引 - 与子查询相关的条件不能下推
- 与存储函数相关的条件不能下推。
- 触发条件不能下推
ICP优化过程
要理解ICP优化如何工作，首先考虑一下，在不使用索引下推的情况下，索引扫描是如何进行的：
- 获取下一行，首先读取索引元组，然后通过索引元组检索并读取整行数据。
- 对表中的数据测试是否符合
WHERE条件，基于测试结果接收或拒绝行
使用索引下推时，扫描过程如下： - 获取下一行的索引元组（不需要获取整行数据）
- 测试能否只通过索引中的行确认数据是否符合
WHERE条件中的一部分。如果不符合条件，继续获取下一个索引元组。 - 如果符合条件，通过索引元组来检索并读取整行数据
- 测试数据是否符合
WHERE条件中的其他部分。基于测试结果接收或拒绝行。
ICP的EXPLIAN输出
在Extra列中显示Using index condition。
示例
假设有一个表包括人员和地址数据，在表上具有一个索引INDEX (zipcode, lastname, firstname).如果我们知道一个人的zipcode值但是不确定他的名称，我们可以使用以下查询：
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
MySQL可以使用索引检索zipcode='95054'的人。第二部分不能用来限制必须扫描的行数量（lastname LIKE '%etrunia%' 因为LIKE条件以%开头），当不使用索引下推时，这个查询必须检索所有zipcode='95054'的整行数据
使用索引下推，MySQL在读取整行数据前，先检查lastname LIKE '%etrunia%'.这样就不用读取那些符合zipcode条件但是不符合lastname条件的行数据了。
控制ICP是否启用
可以使用iptimizer_switch变量的index_condition_pushdown标志来控制ICP是否启用
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
问题答案
- 什么是索引下推（
Index Condition Pushdown（ICP)）？索引下推的过程是怎样的？
索引下推就是在查询时，首先只读取索引元组（而不是整行数据），通过索引元组来判断数据是否符合WHERE语句中的条件，然后只对其中符合条件的行再读取整行数据来进行其他WHERE条件的判断。
过程：
- 读取索引元组
- 根据索引元组判断该行是否符合
WHERE中的其他条件（在索引中的其他列的条件部分），如果不符合，直接读取下一个索引元组，不读取该行数据 - 如果符合条件，通过索引元组检索并读取整行数据
- 判断数据是否符合
WHERE中的其他条件（不在索引中的其他列的条件部分）。
- 在哪些情况下可以使用索引下推，索引下推有什么限制？
-
ICP可以用于MyISAM和InnoDB表，包括分区表。对于InnoDB表，ICP只适用于辅助索引。 -
ICP可以用于range,ref,eq_ref,ref_or_not类型的查询。
限制： - 不适用于在虚拟生成列上创建的索引。
- 不适用于与子查询/存储函数/触发条件相关的条件
- 如何查看一个语句是否使用了索引下推？
查看EXPLAIN的Extra列是否显示Using index condition - 如何启用或禁用索引下推优化？
禁用：SET optimizer_switch = 'index_condition_pushdown=off';
启用：SET optimizer_switch = 'index_condition_pushdown=on';
