详解索引下推（ICP）（示例与解释）

0、前言

        许久不看索引下推ICP相关知识，有些模糊，在网上查了查后，发现很多相关文章直接摆上一堆图、讲的云里雾里的，因此这里做一下简明易懂的总结，全文无废话，方便复习。
        本文适合具有B+树、数据库索引等基础知识的同学阅读哈！

---

1、索引下推（ICP）是什么

1.1 概念

        ICP是一种数据库的查询优化技术，利用索引来提高查询性能:

• 使用ICP前：在传统的查询流程中，数据库通常先读取满足条件的所有行，然后再通过排序、过滤等操作得到最终结果，这种方式的效率往往受限于内存和磁盘I/O速度等因素。
• 使用ICP后：当查询涉及到多个表格时，索引下推将过滤条件尽可能转换成对应表格上的索引条件，从而尽可能减少需要扫描的表格数和读取的数据量，提高查询速度。

1.2 说人话

        使用ICP之前，对于单个非聚簇索引（二级索引）、并且存在范围条件筛选的情况下，需要先回表，把数据IO出来，然后再进行筛选。
        而使用索引下推后，是先筛选好满足条件的主键id，再去数据库进行IO。
        对比而言 ，显然后者IO的数据量更小，IO次数更少！——这就是ICP的意义。

2、举例说明ICP

2.1 举例

        只讲概念可能还是有些模糊，我们举出一个例子：

假设有一个订单表 order_table，其中包含以下字段：

• order_id：订单ID，主键
• user_id：用户ID
• order_time：下单时间
• order_status：订单状态

为了提高查询效率，我们可以在 user_id 和 order_time 字段上分别创建索引。

 对于以上情况，我们执行以下SQL语句：

SELECT COUNT(*) FROM order_table WHERE user_id=123 AND order_time>='2023-01-01' AND order_time<='2023-01-31';

2.2 解释

        不使用索引下推，执行该语句需要扫描 user_id 索引来获取全部符合条件的订单，然后再对结果进行时间过滤匹配，由于表中数据量很大，会造成很大的IO开销。

        使用索引下推，则可以先在索引层级上过滤出满足条件的索引行：user_id=123 是一个等值查询条件，可以在 user_id 索引层级上进行过滤；order_time>='2023-01-01' 和 order_time<='2023-01-31' 则是时间范围条件，可以在 order_time 索引层级上进行过滤。
        MySQL会首先使用 user_id 索引查找符合条件的行，然后再使用 order_time 索引过滤出满足时间条件的索引行然后再到数据层级上获取订单数量，最后将减少了对磁盘的读取，从而提高了查询性能。

2.3 具体执行流程      

1. 首先，MySQL会使用 user_id 索引来查找所有满足 user_id=123 的订单ID，并将这些ID保存下来。
2. 然后，MySQL会使用 order_time 索引来查找所有满足 order_time>='2023-01-01' 和 order_time<='2023-01-31' 的订单ID，并将这些ID保存下来。
3. 接着，MySQL会将这两个ID列表进行比较，只保留同时在这两个列表中出现的订单ID。
4. 最后，MySQL会统计符合条件的订单数量，并返回结果。

3、关于索引下推的其他知识

        （1）索引下推是数据库优化器自动采取的策略。
        （2）并非适合所有场景（效率问题）
        （3）对于以上第2节的例子，可以采用user_id 和 order_time作为联合索引，这样效率比索引下推会更高。