索引条件下推(ICP)是MySQL使用索引从表中检索行的一种优化。如果没有ICP,存储引擎将遍历索引以定位基表中的行,并将它们返回给计算在哪里
条件。启用了ICP,如果部分在哪里
条件可以通过仅使用索引中的列来评估,MySQL服务器推入在哪里
存储引擎的状态。存储引擎然后使用索引项来评估推入的索引条件,只有满足这个条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
下推优化的适用性取决于以下条件:
ICP用于
范围
,裁判
,eq_ref
,ref_or_null
需要访问全表行时的访问方法。为
InnoDB
表,ICP仅用于二级索引。ICP的目标是减少全行读取的次数,从而减少I/O操作。为InnoDB
集合索引时,完整记录已被读入InnoDB
缓冲区。在这种情况下使用ICP并不会减少I/O。在虚拟生成的列上创建二级索引不支持ICP。
InnoDB
支持虚拟生成列的二级索引。引用子查询的条件不能下推。
引用存储函数的条件不能下推。存储引擎无法调用已存储的函数。
触发条件不能下推。(有关触发条件的信息,请参见第8.2.2.3节,“使用EXISTS策略优化子查询”。)
要理解这种优化是如何工作的,首先考虑当索引条件下推没有使用时索引扫描是如何进行的:
获取下一行,首先通过读取索引元组,然后使用索引元组来定位和读取整个表行。
测试的部分
在哪里
适用于此表的条件。根据测试结果接受或拒绝行。
使用下推索引条件,扫描将像这样进行:
获取下一行的索引元组(但不是整个表行)。
测试的部分
在哪里
适用于此表的条件,并且只能使用索引列进行检查。如果条件不满足,则继续下一行的索引元组。如果条件满足,则使用index元组来定位和读取全表行。
测试的其余部分
在哪里
适用于此表的条件。根据测试结果接受或拒绝行。
解释
输出显示使用索引条件
在额外的
当使用索引条件下推时。它没有显示出来使用索引
因为当必须读取全表行时,这并不适用。
假设一个表包含关于人员及其地址的信息,并且该表有一个定义为的索引索引(邮编,姓,名)
。如果我们知道一个人的zipcode
值,但不确定姓氏,我们可以这样搜索:
SELECT * FROM邮政编码为95054,姓为%etrunia%,地址为%Main Street%;
MySQL可以用索引来扫描通过的人zipcode = ' 95054 '
。第二部分(lastname像' % etrunia % '
)不能用于限制必须扫描的行数,因此如果没有索引条件下推,该查询必须检索所有拥有索引条件的人的完整表行zipcode = ' 95054 '
。
索引条件下推,MySQL检查lastname像' % etrunia % '
部分在读取全表行之前。这样可以避免读取匹配的索引元组对应的完整行zipcode
条件,但不是姓
条件。
索引条件默认启用“下推”。它可以用optimizer_switch
系统变量index_condition_pushdown
国旗:
SET optimizer_switch = 'index_condition_pushdown=off';SET optimizer_switch = 'index_condition_pushdown=on';