)
文章目录一、索引下推是什么二、回表查询Table Lookup是什么聚集索引和非聚集索引如何减少回表查询小结三、索引下推如何减少回表查询次数1. 没有使用icp(索引下推)2. 使用ICP四、总结索引下推的工作原理1. 传统的查询处理方式2. 索引下推优化五、索引下推的优点六、索引下推使用条件一、索引下推是什么索引下推(Index Condition Pushdown简称ICP)是MySQL5.6版本的新特性它允许数据库存储引擎在存储层直接应用WHERE子句中的过滤条件而不是先将所有匹配的数据行返回给查询处理层(server层)再进行过滤。因此它能在使用索引时减少回表查询次数提高查询效率。二、回表查询Table Lookup是什么在没有索引下推的情况下如果一个查询涉及到复合索引但查询条件只覆盖了索引的一部分字段那么数据库引擎可能会先通过索引找到符合条件的记录然后再回到主表即“回表”去获取完整的记录。这是因为索引中可能只包含了部分字段的信息而完整的记录需要从主表中获取。聚集索引和非聚集索引为了更好地理解回表查询首先需要了解MySQL中的两种主要索引类型聚集索引Clustered Index和非聚集索引Non-Clustered Index 或 Secondary Index。聚集索引决定了数据在物理磁盘上的存储顺序。对于InnoDB存储引擎如果没有显式定义聚集索引那么主键Primary Key就会自动成为聚集索引。如果表没有主键InnoDB会选择一个唯一的非空索引作为聚集索引。如果没有这样的索引InnoDB会隐式创建一个内部的、隐藏的聚集索引。非聚集索引不改变表中记录的物理顺序而是创建一个独立于表数据文件的结构。非聚集索引的叶节点中存储的是索引字段值和对应行的主键值或行指针。聚集索引的叶子节点就是数据节点也就是说索引和数据行在一起反之如果叶子节点没有存储数据行那么就是非聚集索引。注意InnoDB和myisam均用到非聚簇索引但是他们有不同的实现。myisam的非聚簇索引指向对应数据块的指针而对于innodb的非聚簇索引实现data指向的是主键值通过主键值去聚簇索引进行索引操作(回表查询)找到叶子节点数据在该叶子节点上。详情可以看这篇文章聚簇索引(聚集索引)和非聚簇索引如何减少回表查询使用覆盖索引确保索引中包含查询所需的所有列这样就可以直接从索引中获取所有需要的数据避免回表查询。优化查询尽量减少查询中涉及的列数特别是避免使用SELECT *只选择真正需要的列。合理设计索引将查询中最常使用的列或选择性高的列放在索引的前面以提高索引的有效性。在MySQL5.6以上版本中当使用复合索引(A B)如A字段模糊查询时会直接判断B字段的条件是不是满足条件如果不满足则不会进行回表。(详情在章节3)小结当使用非聚集索引进行查询时如果查询所需要的列数据完全可以在索引中找到那么MySQL可以直接从索引中获取数据这种情况下索引被称为覆盖索引Covering Index。但是如果查询需要的某些列数据不在非聚集索引中MySQL就必须使用索引中存储的主键值或行指针来访问表中的数据行以获取那些不在索引中的列的数据。这个过程被称为回表查询。三、索引下推如何减少回表查询次数如现在有用户表t_user表里创建联合索引name, age。现在有一条sqlselect*fromt_userwherenamelike张%andage10;1. 没有使用icp(索引下推)此时根据索引最左匹配原则。存储引擎根据通过联合索引找到name like ‘张%’ 的主键id。会根据id逐一进行回表扫描去聚簇索引找到完整的行记录server层再对数据根据age10进行筛选。可以看到需要回表两次把我们联合索引的另一个字段age浪费了。2. 使用ICP而MySQL 5.6 以后 存储引擎根据nameage联合索引找到name like ‘张%’由于联合索引中包含age列所以存储引擎直接再联合索引里按照age10过滤。按照过滤后的数据再一一进行回表扫描。可以看到只回表了一次。除此之外我们还可以看一下执行计划看到Extra一列里 Using index condition这就是用到了索引下推。--------------------------------------------------------------------------------------------------------------------------|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|--------------------------------------------------------------------------------------------------------------------------|1|SIMPLE|tuser|NULL|range|na_index|na_index|102|NULL|2|25.00|Using index condition|--------------------------------------------------------------------------------------------------------------------------注意索引下推是一种用于查询优化的技术和查询使用索引不冲突。索引下推的作用是在存储引擎层使用联合索引的时候通过多个查询条件提前过滤从而减少服务层回表查询完整记录减少回表查询。四、总结索引下推的工作原理1. 传统的查询处理方式存储引擎首先根据索引读取数据并将其加载到内存中。然后在(Server层)内存中应用WHERE子句中的过滤条件筛选出符合条件的数据行。这种方式可能导致大量的数据传输尤其是当数据量较大时。2. 索引下推优化在存储层使用WHERE子句中的过滤条件。只有符合条件的数据才会被加载到内存中进一步处理。这样可以减少数据传输量从而提高查询效率。五、索引下推的优点减少数据传输只传输符合筛选条件的数据行减少了网络带宽的消耗。提高查询速度减少了不必要的数据加载和处理尤其是在大数据集上效果显著。节省资源减轻了内存和CPU的压力。六、索引下推使用条件只能用于range、 ref、 eq_ref、ref_or_null访问方法只能用于InnoDB和 MyISAM存储引擎及其分区表对InnoDB存储引擎来说索引下推只适用于二级索引也叫辅助索引;索引下推的目的是为了减少回表次数也就是要减少IO操作。对于InnoDB的聚簇索引来说数据和索引是在一起的不存在回表这一说。引用了子查询的条件不能下推引用了存储函数的条件不能下推因为存储引擎无法调用存储函数。参考文章五分钟搞懂MySQL索引下推什么是索引下推
一文看懂MySQL索引下推(ICP)
发布时间：2026/7/26 14:25:13
文章目录一、索引下推是什么二、回表查询Table Lookup是什么聚集索引和非聚集索引如何减少回表查询小结三、索引下推如何减少回表查询次数1. 没有使用icp(索引下推)2. 使用ICP四、总结索引下推的工作原理1. 传统的查询处理方式2. 索引下推优化五、索引下推的优点六、索引下推使用条件一、索引下推是什么索引下推(Index Condition Pushdown简称ICP)是MySQL5.6版本的新特性它允许数据库存储引擎在存储层直接应用WHERE子句中的过滤条件而不是先将所有匹配的数据行返回给查询处理层(server层)再进行过滤。因此它能在使用索引时减少回表查询次数提高查询效率。二、回表查询Table Lookup是什么在没有索引下推的情况下如果一个查询涉及到复合索引但查询条件只覆盖了索引的一部分字段那么数据库引擎可能会先通过索引找到符合条件的记录然后再回到主表即“回表”去获取完整的记录。这是因为索引中可能只包含了部分字段的信息而完整的记录需要从主表中获取。聚集索引和非聚集索引为了更好地理解回表查询首先需要了解MySQL中的两种主要索引类型聚集索引Clustered Index和非聚集索引Non-Clustered Index 或 Secondary Index。聚集索引决定了数据在物理磁盘上的存储顺序。对于InnoDB存储引擎如果没有显式定义聚集索引那么主键Primary Key就会自动成为聚集索引。如果表没有主键InnoDB会选择一个唯一的非空索引作为聚集索引。如果没有这样的索引InnoDB会隐式创建一个内部的、隐藏的聚集索引。非聚集索引不改变表中记录的物理顺序而是创建一个独立于表数据文件的结构。非聚集索引的叶节点中存储的是索引字段值和对应行的主键值或行指针。聚集索引的叶子节点就是数据节点也就是说索引和数据行在一起反之如果叶子节点没有存储数据行那么就是非聚集索引。注意InnoDB和myisam均用到非聚簇索引但是他们有不同的实现。myisam的非聚簇索引指向对应数据块的指针而对于innodb的非聚簇索引实现data指向的是主键值通过主键值去聚簇索引进行索引操作(回表查询)找到叶子节点数据在该叶子节点上。详情可以看这篇文章聚簇索引(聚集索引)和非聚簇索引如何减少回表查询使用覆盖索引确保索引中包含查询所需的所有列这样就可以直接从索引中获取所有需要的数据避免回表查询。优化查询尽量减少查询中涉及的列数特别是避免使用SELECT *只选择真正需要的列。合理设计索引将查询中最常使用的列或选择性高的列放在索引的前面以提高索引的有效性。在MySQL5.6以上版本中当使用复合索引(A B)如A字段模糊查询时会直接判断B字段的条件是不是满足条件如果不满足则不会进行回表。(详情在章节3)小结当使用非聚集索引进行查询时如果查询所需要的列数据完全可以在索引中找到那么MySQL可以直接从索引中获取数据这种情况下索引被称为覆盖索引Covering Index。但是如果查询需要的某些列数据不在非聚集索引中MySQL就必须使用索引中存储的主键值或行指针来访问表中的数据行以获取那些不在索引中的列的数据。这个过程被称为回表查询。三、索引下推如何减少回表查询次数如现在有用户表t_user表里创建联合索引name, age。现在有一条sqlselect*fromt_userwherenamelike张%andage10;1. 没有使用icp(索引下推)此时根据索引最左匹配原则。存储引擎根据通过联合索引找到name like ‘张%’ 的主键id。会根据id逐一进行回表扫描去聚簇索引找到完整的行记录server层再对数据根据age10进行筛选。可以看到需要回表两次把我们联合索引的另一个字段age浪费了。2. 使用ICP而MySQL 5.6 以后 存储引擎根据nameage联合索引找到name like ‘张%’由于联合索引中包含age列所以存储引擎直接再联合索引里按照age10过滤。按照过滤后的数据再一一进行回表扫描。可以看到只回表了一次。除此之外我们还可以看一下执行计划看到Extra一列里 Using index condition这就是用到了索引下推。--------------------------------------------------------------------------------------------------------------------------|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|--------------------------------------------------------------------------------------------------------------------------|1|SIMPLE|tuser|NULL|range|na_index|na_index|102|NULL|2|25.00|Using index condition|--------------------------------------------------------------------------------------------------------------------------注意索引下推是一种用于查询优化的技术和查询使用索引不冲突。索引下推的作用是在存储引擎层使用联合索引的时候通过多个查询条件提前过滤从而减少服务层回表查询完整记录减少回表查询。四、总结索引下推的工作原理1. 传统的查询处理方式存储引擎首先根据索引读取数据并将其加载到内存中。然后在(Server层)内存中应用WHERE子句中的过滤条件筛选出符合条件的数据行。这种方式可能导致大量的数据传输尤其是当数据量较大时。2. 索引下推优化在存储层使用WHERE子句中的过滤条件。只有符合条件的数据才会被加载到内存中进一步处理。这样可以减少数据传输量从而提高查询效率。五、索引下推的优点减少数据传输只传输符合筛选条件的数据行减少了网络带宽的消耗。提高查询速度减少了不必要的数据加载和处理尤其是在大数据集上效果显著。节省资源减轻了内存和CPU的压力。六、索引下推使用条件只能用于range、 ref、 eq_ref、ref_or_null访问方法只能用于InnoDB和 MyISAM存储引擎及其分区表对InnoDB存储引擎来说索引下推只适用于二级索引也叫辅助索引;索引下推的目的是为了减少回表次数也就是要减少IO操作。对于InnoDB的聚簇索引来说数据和索引是在一起的不存在回表这一说。引用了子查询的条件不能下推引用了存储函数的条件不能下推因为存储引擎无法调用存储函数。参考文章五分钟搞懂MySQL索引下推什么是索引下推
