索引条件下推优化
索引条件下推(ICP)是针对MySQL使用索引从表中检索行的情况的一种优化。如果没有ICP,则存储引擎将遍历索引以在基表中定位行,并将其返回给MySQL服务器,该MySQL服务器将评估WHERE
行的条件。启用ICP后,如果WHERE
可以仅使用索引中的列来评估部分条件,则MySQL服务器会将这部分条件压入WHERE
条件下降到存储引擎。然后,存储引擎通过使用索引条目来评估推送的索引条件,并且只有满足此条件的情况下,才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
索引条件下推式优化的适用性取决于以下条件:
- ICP用于
range
,ref
,eq_ref
,和ref_or_null
访问方法时,有一个需要访问的全部表行。 - ICP可用于
InnoDB
和MyISAM
表,包括分区表InnoDB
和MyISAM
表。 - 对于
InnoDB
表,ICP仅用于二级索引。ICP的目标是减少全行读取的次数,从而减少I / O操作。对于InnoDB
聚集索引,完整的记录已被读入InnoDB
缓冲区。在这种情况下使用ICP不会减少I / O。 - 在虚拟生成的列上创建的二级索引不支持ICP。
InnoDB
支持虚拟生成的列上的二级索引。 - 引用子查询的条件不能下推。
- 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。
- 触发条件不能下推。(有关触发条件的信息,请参见“使用 EXISTS 策略优化子查询”)。
要了解此优化的工作原理,请首先考虑在不使用“索引条件下推”的情况下如何进行索引扫描:
- 获取下一行,首先读取索引元组,然后使用索引元组查找并读取整个表行。
- 测试
WHERE
适用于此表的部分条件。根据测试结果接受或拒绝该行。
使用“索引条件下推”,扫描将像这样进行:
- 获取下一行的索引元组(而不是整个表行)。
- 测试
WHERE
适用于此表的部分条件,并且只能使用索引列进行检查。如果不满足条件,请转到下一行的索引元组。 - 如果满足条件,则使用索引元组来定位和读取整个表行。
- 测试
WHERE
适用于此表的条件的其余部分。根据测试结果接受或拒绝该行。
EXPLAIN
当使用“索引条件下推”时,输出将显示Using index condition
在Extr
列中。它不会显示,Using index
因为在必须读取完整表行时,该方法不适用。
假设一个表包含有关人员及其地址的信息,并且该表的索引定义为INDEX(zipcode, lastname, firstname)
。如果我们知道一个人的zipcode
价值,但不确定姓氏,可以这样搜索:
SELECT *FROM peopleWHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
MySQL可以使用索引扫描具有的人员zipcode='95054'
。第二部分(lastname LIKE '%etrunia%'
)不能用于限制必须扫描的行数,因此,如果没有“索引条件下推”,此查询必须为所有具有的人员检索完整的表行zipcode='95054'
。
通过“索引条件下推”,MySQL lastname LIKE '%etrunia%'
在读取整个表行之前会检查该部分。这样可以避免读取与zipcode
条件而不是lastname
条件匹配的索引元组对应的完整行。
默认情况下,索引条件下推处于启用状态。可以optimizer_switch
通过设置index_condition_pushdown
标志使用系统变量进行控制:
SET optimizer_switch = 'index_condition_pushdown=off';SET optimizer_switch = 'index_condition_pushdown=on';
请参见“可切换的优化”。