关于 索引下推(ICP) 的梳理性笔记，非原创

参考

一、索引下推是什么？

索引下推(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）。
现在有一条sql

sql 
 select * from t_user where name like '张%' and age=10;

1. 没有使用icp(索引下推)

此时根据索引最左匹配原则。存储引擎根据通过联合索引找到name like ‘张%’ 的主键id。会根据id逐一进行回表扫描，去聚簇索引找到完整的行记录，server 层再对数据根据age=10进行筛选。

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了

为什么这里索引部分失效了？

这里联合索引是 (name, age)。

  • 最左匹配原则：MySQL 在使用联合索引时，必须从最左边的列开始匹配，并且如果遇到范围查询（如 LIKE '张%'、>、<、BETWEEN），后面的列就无法再利用索引进行精确查找了。

    联合索引是先按第一列排序，第一列相同的情况下，才按第二列排序。

    一旦第一列使用了范围查询（如 > 或 LIKE '张%'），第一列的数据就不再是一个确定的点，而是一个连续的区间。在这个区间内，第二列的数据是杂乱无章（无序）的，无法再满足二分查找或快速定位的要求

    举例说明

    假设有一个联合索引 (name, age)，数据在索引树中是这样存储的：

    name (第一列) age (第二列) 排序逻辑
    张三 10 name 都是“张”，age 排序
    张三 18 name 都是“张”，age 排序
    张五 12 name 都是“张”，age 排序
    李四 5 name 变了，重新排序
    李四 20 name 变了，重新排序

    场景分析：name like '张%'

    当你查找 name 以“张”开头的数据时：

    • 索引树行为：数据库锁定了所有 name 为“张…”的行（前三行）。
    • Age 的状态：请观察前三行的 age 列：10 -> 18 -> 12。
    • 结论：虽然 name 是有序的，但在 name 确定的这个范围内，age 并没有全局排序。既然 age 是无序的，数据库就无法利用索引树特性去快速查找 age=10，只能把符合条件的 name 行找出来，再逐一检查 age（这就是图片中说的“Server层筛选”）

在你的 SQL 中：

text 
select * from t_user where name like '张%' and age=10;
  1. name like '张%' 是一个范围查询：虽然它不是 > 或 <，但在 B+ 树索引中，前缀匹配（'张%'）本质上是在查找一个范围（即所有以“张”开头的字符串区间）。
  2. 遇到范围查询即停止：因为 name 是联合索引的第一列，且使用了范围匹配，MySQL 在索引树中定位到所有 name 为“张…”的记录后，就停止了索引的深度查找。
  3. age 列被“抛弃”：因为 name 已经把索引查找变成了范围扫描，所以联合索引中的第二列 age 就无法再被用来做快速的索引定位了。

2. 使用ICP

而MySQL 5.6 以后， 如果存在某些被索引的列的判断条件时，MySQL服务器会将这一部分判断条件传递给存储引擎，然后存储引擎在索引遍历过程中执行部分where 字句的判断条件、直接过滤掉不满足条件的记，只有符合条件的数据才会被检索出来 返回给MySQL服务器

例：存储引擎根据（name，age）联合索引，找到name like ‘张%’，由于联合索引中包含age列，所以存储引擎直接在联合索引里按照 age=10 过滤。按照过滤后的数据再一一进行回表扫描。

可以看到只回表了一次。
除此之外我们还可以看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。

js 
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

注意：索引下推是一种用于查询优化的技术，和查询使用索引不冲突。
索引下推的作用是在存储引擎层使用联合索引的时候通过多个查询条件提前过滤，从而减少服务层回表查询完整记录，减少回表查询。

四、总结索引下推的工作原理

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责 数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎

索引下推的 **下推** 其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理

1.传统的查询处理方式：

存储引擎首先根据索引读取数据并将其加载到内存中。
然后在 Server 层内存中回表查询、应用WHERE子句中的过滤条件，筛选出符合条件的数据行
这种方式可能导致大量的数据传输，尤其是当数据量较大时

2. 索引下推优化：

在存储引擎层使用WHERE子句中的过滤条件。
只有符合条件的数据才会被加载到 Server 层内存中进一步处理。
这样可以减少数据传输量，从而提高查询效率。

五、索引下推的优点

  • 减少了存储引擎层和 Server 层之间的数据传输量：只传输符合筛选条件的数据行，减少了网络带宽的消耗。
  • 提高查询速度：减少了回表次数、避免了不必要的数据加载和处理，尤其是在大数据集上效果显著。
  • 节省资源：减轻了内存和CPU的压力。

六、索引下推的使用条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

  • 只能用于 InnoDB 和 MyISAM 存储引擎及其分区表的查询；

  • 对 InnoDB 存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

    索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于 InnoDB 的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

  • 子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的

  • 存储过程不能使用索引下推，因为存储引擎无法调用存储函数