mysql之index condition pushdown(icp) 索引条件推

最新推荐文章于 2022-10-13 21:53:20 发布

DavidZhang_Java

最新推荐文章于 2022-10-13 21:53:20 发布

阅读量555

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签：索引 mysql 优化

本文链接：https://blog.youkuaiyun.com/zswjob126/article/details/50009789

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文深入解析了MySQL中索引条件推送优化（Index Condition Pushdown, ICP）的概念、原理及应用。通过对比ICP开启与关闭时的数据检索流程，阐述了ICP如何通过利用索引直接筛选数据，减少全表读取次数和I/O操作，提升查询效率。重点讨论了ICP在不同访问方法（如范围、引用、等值引用和或null）下的使用场景，特别是针对InnoDB和MyISAM表的优化效果，并指出ICP不适用于分区表的问题。文章还补充了一个详细的链接，提供进一步阅读资源。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引条件下推优化，先附上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 fields 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.

Index Condition Pushdown optimization is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows. This strategy can be used for InnoDB and MyISAM tables. (Note that index condition pushdown is not supported with partitioned tables in MySQL 5.6; this issue is resolved in MySQL 5.7.) For InnoDB tables, however, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-record reads and thereby reduce IO operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO.