一文看懂MySQL索引下推(ICP)
一文看懂MySQL索引下推(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
select * from t_user where name like '张%' and age=10;
1. 没有使用icp(索引下推)
此时根据索引最左匹配原则。存储引擎根据通过联合索引找到name like ‘张%’ 的主键id。会根据id逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。
可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。
2. 使用ICP
而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like ‘张%’，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。
可以看到只回表了一次。
除此之外我们还可以看一下执行计划，看到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索引下推
- 什么是索引下推？