• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 索引条件下推优化

    索引条件下推(ICP)是针对MySQL使用索引从表中检索行的情况的一种优化。如果没有ICP,则存储引擎将遍历索引以在基表中定位行,并将其返回给MySQL服务器,该MySQL服务器将评估WHERE行的条件。启用ICP后,如果WHERE可以仅使用索引中的列来评估部分条件,则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后,存储引擎通过使用索引条目来评估推送的索引条件,并且只有满足此条件的情况下,才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

    索引条件下推式优化的适用性取决于以下条件:

    • ICP用于rangerefeq_ref,和ref_or_null访问方法时,有一个需要访问的全部表行。
    • ICP可用于InnoDBMyISAM表,包括分区表InnoDBMyISAM表。
    • 对于InnoDB表,ICP仅用于二级索引。ICP的目标是减少全行读取的次数,从而减少I / O操作。对于InnoDB聚集索引,完整的记录已被读入InnoDB缓冲区。在这种情况下使用ICP不会减少I / O。
    • 在虚拟生成的列上创建的二级索引不支持ICP。InnoDB支持虚拟生成的列上的二级索引。
    • 引用子查询的条件不能下推。
    • 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。
    • 触发条件不能下推。(有关触发条件的信息,请参见“使用 EXISTS 策略优化子查询”)。

    要了解此优化的工作原理,请首先考虑在不使用“索引条件下推”的情况下如何进行索引扫描:

    1. 获取下一行,首先读取索引元组,然后使用索引元组查找并读取整个表行。
    2. 测试WHERE适用于此表的部分条件。根据测试结果接受或拒绝该行。

    使用“索引条件下推”,扫描将像这样进行:

    1. 获取下一行的索引元组(而不是整个表行)。
    2. 测试WHERE适用于此表的部分条件,并且只能使用索引列进行检查。如果不满足条件,请转到下一行的索引元组。
    3. 如果满足条件,则使用索引元组来定位和读取整个表行。
    4. 测试WHERE适用于此表的条件的其余部分。根据测试结果接受或拒绝该行。

    EXPLAIN当使用“索引条件下推”时,输出将显示Using index conditionExtr 列中。它不会显示,Using index因为在必须读取完整表行时,该方法不适用。

    假设一个表包含有关人员及其地址的信息,并且该表的索引定义为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%')不能用于限制必须扫描的行数,因此,如果没有“索引条件下推”,此查询必须为所有具有的人员检索完整的表行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';
    

    请参见“可切换的优化”。