MySQL索引下推

发表于 | 评论数： | 阅读次数：

什么是索引下推

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

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

索引下推解决了什么问题

where条件有多个时，如果使用索引进行定位，原来的模式下需要将所有满足条件的行返回给MySQL服务器，由服务器根据其他条件筛选和评估数据，此时可能因为前面的条件过滤数据不够多而造成多次回表

什么时候触发索引下推

默认情况下，索引条件下推处于启用状态。可以optimizer_switch通过设置index_condition_pushdown标志使用系统变量 进行控制 ：

1
2
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
  1. 有一个条件需要访问全部表行
  2. 适用于分区表
  3. 对于InnoDB，适用于二级索引
  4. 虚拟列上的二级索引不存在ICP，但是InnoDB实现了这个功能

什么时候不能触发索引下推

  1. 子查询的条件无法下推
  2. 涉及到存储功能的条件无法下推，存储引擎无法调用存储的功能
  3. 触发条件不能下推

索引下推产生的变化

1
2
3
4
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条件匹配的索引元组对应的完整行 。