什么是索引下推？

索引下推（Index Condition Pushdown, ICP）是MySQL优化器的一项优化技术，旨在提高通过索引检索数据的效率，尤其是在InnoDB存储引擎中。这项技术在MySQL 5.6中引入，专门用于减少从表中读取不必要的行，提高查询性能。

工作原理

在没有索引下推的情况下，当查询使用复合索引时，MySQL可能需要访问主表来评估不能完全通过索引条件确定的行。例如，如果只有索引的部分条件在索引中能被使用，而其他条件需要读取实际行再进行筛选，传统方式可能会导致更多的全行读取。

通过索引下推优化，MySQL可以在索引级别应用更多的查询过滤条件，从而减少读取主表的行数。

示例

假设有一个包含许多列的表 products，并为复合索引 (category_id, price) 创建以下查询：

SELECT * FROM products WHERE category_id = 5 AND price < 100;

如果复合索引 (category_id, price) 存在：

• 无索引下推：
  • MySQL根据category_id = 5条件使用索引检索数据，然后访问主表的每一行以评估price < 100条件。这可能导致访问许多不满足价格条件的行。
• 有索引下推：
  • MySQL在索引层面直接评估price < 100的条件，只需将符合条件的行从表中提取。这减少了对表的I/O操作。

适用场景

索引下推非常适合于查询条件中涉及多个列的过滤，且这些列存在复合索引的情况下。它可以在索引扫描过程中应用额外的过滤条件来减少所需的行访问。

优势

• 减少I/O操作：因为避免了对不必要的表记录的读取，所以可以大幅减少I/O操作。
• 提高查询效率：直接在索引层面筛选数据，进一步减少了需要处理的数据量。
• 更快的响应时间：整体上更有效的索引应用提高了查询响应速度。

索引下推是MySQL智能优化器功能迅速发展的一个体现，熟练使用及理解该功能有助于优化复杂查询和提升性能。

更新: 2024-08-08 16:33:45
原文: https://www.yuque.com/tulingzhouyu/db22bv/hmenpg2g8ncqkciq

短视频

又有同学问我索引下推是个什么索引，我..... ....

索引下推它不是一种索引！ 它是一种数据库查询优化技术！它能够在查询过程中尽早地利用索引来过滤数据，减少不必要的数据读取，从而提高查询性能。

首先要知道当执行一条查询语句， 会通过服务层****解析SQL、优化索引， 再通过存储引擎层根据索引拿到数据， ** 再把数据再交给服务层条件过滤**。

在MySQL 5.6之前,,, 是没有索引下推的， 当查询条件使用了联合索引并且最左条件是范围： 存储引擎只会返回最左条件的范围数据 ， 然后扔给服务层再去过去其他的条件， 这样增加了服务层计算压力一级存储引擎层和服务层之间的的数据传输量。

如果用到了索引下推

同样查询条件使用了联合索引并且最左条件是范围： 那这个时候 存储引擎在过滤完范围数据后会再过滤其他联合索引的数据， 所以条件过滤这一步从服务层下推到了存储引擎层， 所以叫索引下推。 这样就减少了两层之间数据的传递，也减轻了服务层的数据计算压力，从而提高了数据检索性能。

如果听懂的话可以给我三连支持，像这样的高频面试题我给大家整理好了一份80万字的资料， 需要可以再评论区扣666，希望帮助大家拿到更多offer。

举个例子

我让媒婆给我找大长腿，18岁的女朋友， 媒婆一个电话联系婚介所告诉了条件， 婚介所只会把所有大长腿带给媒婆。

媒婆再把同时是大长腿和18岁的女孩过滤一遍交给我。

有没有觉得太费劲了。

其实媒婆就是服务层， 婚介就是存储引擎层，大长腿，18岁是联合索引， 媒婆如果让婚介所一次性把大长腿和18岁女孩都叫过来不是更省事吗？

所以应该把条件过滤这一步直接下推到存储引擎这一步去做， 放在服务层，会导致多次i/o操作而影响检索性能。

更新: 2024-05-16 13:59:56
原文: https://www.yuque.com/tulingzhouyu/db22bv/wkdh9p48ybgya6g4