索引下推优化官方文档说明 (Section 8.2.1.5)：https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
1. 什么是索引下推？
\qquad 索引条件下推 (Index Condition Pushdown，ICP) 是 MySQL 在使用索引从表中检索行时的一种优化方法。在没有 ICP 的情况下，存储引擎遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由服务器对行进行 WHERE 条件的评估。在启用 ICP 的情况下，并且如果 WHERE 条件的某些部分可以仅使用索引列进行评估，则 MySQL 服务器将此部分 WHERE 条件下推给存储引擎。ICP 可以减少存储引擎访问基表的次数和 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.
2. 索引下推优化有什么约束条件？
\qquad 索引条件下推优化的适用性受以下条件的约束：
- 当需要访问完整的表行时 (SELECT *) 时，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 支持在虚拟生成列上创建二级索引)。
- 涉及子查询的条件无法被下推。
- 涉及存储函数的条件无法被下推 (存储引擎无法调用存储函数)。
- 触发条件无法下推 (关于触发条件的信息，参见 8.2.2.3 节，“使用 EXISTS 策略优化子查询”)。
Applicability of the Index Condition Pushdown optimization is subject to these conditions:
- ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.
- ICP can be used for InnoDB and MyISAM tables, including partitioned InnoDB and MyISAM tables.
- For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
- ICP is not supported with secondary indexes created on virtual generated columns. InnoDB supports secondary indexes on virtual generated columns.
- Conditions that refer to subqueries cannot be pushed down.
- Conditions that refer to stored functions cannot be pushed down. Storage engines cannot invoke stored functions.
- Triggered conditions cannot be pushed down. (For information about triggered conditions, see Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.)
3. 索引下推优化如何工作？
\qquad 为了理解 (索引下推) 优化如何工作，首先考虑当 ICP 未使用时索引的扫描过程：
- 获取下一行，首先通过读取索引元组，然后使用索引元组来定位并读取完整的表行 (根据索引元祖保存的主键回表)。
- 对应用于该表的 WHERE 条件部分进行测试，根据测试结果接受或拒绝该行。
\qquad 使用 ICP，扫描流程则如下所示：
- 获取下一行的索引元组 (但不获取完整的表行)。
- 对应用于该表且仅使用索引列即可进行检验的 WHERE 条件部分进行测试，如果条件不满足，则继续下一行的索引元组。
- 如果条件满足，则使用索引元组来定位并读取完整的表行 (回表，但是由于索引下推过滤了索引元祖故减少了回表的数量)。
- 对适用于该表的剩余 WHERE 条件部分进行测试，根据测试结果接受或拒绝该行。
To understand how this optimization works, first consider how an index scan proceeds when Index Condition Pushdown is not used:
- Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
- Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
Using Index Condition Pushdown, the scan proceeds like this instead:
- Get the next row’s index tuple (but not the full table row).
- Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
- If the condition is satisfied, use the index tuple to locate and read the full table row.
- Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
4. 如何判断 SQL 是否会走索引下推优化？
\qquad 当使用索引条件下推时，EXPLAIN 的输出在 Extra 列会显示 “Using index condition”，它不显示 " Using index" 是因为当必须读取整个表行时 ICP 不适用。
EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.
\qquad 总之就是，explain + sql 的打印的表信息的 Extra 列显示 “Using index condition” 时，则使用了索引下推。
5. 索引下推的官方例子？
\qquad 假设某个表包含有关人员及其地址的信息，并且该表具有定义为 INDEX (zipcode, lastname, firstname) 的索引。如果我们知道一个人的邮政编码值但不确定姓氏，我们可以像这样搜索：文章来源：https://www.toymoban.com/news/detail-564381.html
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
\qquad
MySQL 可以使用索引扫描 zipcode=‘95054’ 的人，第二部分 (姓氏 LIKE ‘%etrunia%’) 不能用于限制必须扫描的行数，因此如果没有索引条件下推，此查询必须检索 zipcode=‘95054’ 的所有用户的全表行。
\qquad
使用索引条件下推，MySQL 在读取整个表行之前检查姓氏 LIKE ‘%etrunia%’ 部分，这样可以避免读取与邮政编码条件匹配但与姓氏条件不匹配的索引元组对应的整行。
\qquad
索引下推优化默认开启，它可以通过使用 optimizer_switch 系统变量设置 index_condition_pushdown 的标识控制 (是否启用)：文章来源地址https://www.toymoban.com/news/detail-564381.html
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
到了这里，关于MySQL 参考文档：SQL 语句优化（SELECT 语句优化）之索引条件下推（索引下推）优化的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
![[MySQL]SQL优化之sql语句优化](https://imgs.yssmx.com/Uploads/2024/02/764856-1.png)
