mysql索引下沉_MySQL 5.6 索引条件下推优化

索引下推优化是MySQL5.6版本中新加的功能。

索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP，存储引擎会遍历索引以查找基表中的行，并将它们返回给MySQL服务器，由server层再做一波筛选。启用ICP后，如果只使用索引中的列来评估WHERE条件的某些部分，MySQL服务器会将WHERE条件的这一部分推送到存储引擎。然后，存储引擎使用索引条目评估推送的索引条件，并且仅当满足该条件时才从表中读取行。

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

ICP的适用性受以下条件限制：

当需要访问全表行时，ICP用于range，ref，eq_ref和ref_or_null访问方法。

ICP可用于InnoDB和MyISAM表。(例外：MySQL 5.6中的分区表不支持ICP; MySQL 5.7中已解决此问题。)

对于InnoDB表，ICP仅用于二级索引。 ICP的目标是减少全行读取的数量，从而减少IO操作。对于InnoDB聚簇索引，完整记录已经读入InnoDB缓冲区。在这种情况下使用ICP不会降低IO.

引用子查询的条件无法下推。

无法推下涉及存储函数的条件。存储引擎无法调用存储的函数。

触发条件无法下推。

要了解此优化的工作原理，请首先考虑在不使用索引条件下推时索引扫描的进度：

获取下一行，首先读取索引元组，然后使用索引元组找到并读取整个表行。

测试适用于此表的WHERE条件的一部分。根据测试结果接受或拒绝该行。

使用索引条件下推，扫描会像这样进行：

获取下一行的索引元组(但不是完整的表行)。

测试适用于此表的WHERE条件的一部分，并且只能使用索引列进行检查。如果不满足条件，则继续下一行的索引元组。

如果满足条件，请使用索引元组来查找并读取整个表行。

测试适用于此表的WHERE条件的剩余部分。根据测试结果接受或拒绝该行。

EXPLAIN输出显示使用索引条件下推时在Extra列中使用索引条件。它不显示使用索引，因为当必须读取完整的表行时，这不适用。

假设一个表包含有关人员及其地址的信息，并且该表的索引定义为INDEX(zipcode，lastname，firstname)。如果我们知道一个人的邮政编码值但不确定姓氏，我们可以这样搜索：

SELECT * FROM people

WHERE zipcode='95054'

AND lastname LIKE '%etrunia%'

AND address LIKE '%Main Street%';

复制代码

MySQL可以使用索引来扫描zipcode='95054'的人。第二部分(姓氏LIKE'％etrunia％')不能用于限制必须扫描的行数，因此如果没有ICP，此查询必须为所有拥有zipcode ='95054'的人检索完整的表行。

使用ICP后，MySQL在读取整个表行之前检查姓氏LIKE'％etrunia％'部分。这样可以避免读取与zipcode条件匹配的索引元组对应的完整行，但不会读取lastname条件。

默认情况下启用索引条件下推。可以使用optimizer_switch系统变量通过设置index_condition_pushdown标志来控制它：

SET optimizer_switch = 'index_condition_pushdown=off';

SET optimizer_switch = 'index_condition_pushdown=on';

复制代码