⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 juejin.cn/post/7442722732335628342 「超爱吃士力架」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

索引下推

索引下推（Index Condition Pushdown，简称 ICP）是一种提高查询效率、减少回表操作的技术。它允许 MySQL 在使用联合索引查找数据时，将部分查询条件“下推”到存储引擎层进行过滤。这样可以减少从表中读取的数据行，降低 I/O 操作的开销，从而提高查询性能。

需要注意的是，索引下推仅适用于联合索引，且它通过将本应由服务器层处理的操作交给存储引擎来执行，从而实现性能优化。

通俗一点就是通过二级索引査到主键id后回表完再进行where条件过滤改为 =>二级索引査到数据后直接where过滤一遍再进行回表来减少回表的次数

实战

1.1 使用前后对比

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。
启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
- 好处: ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
- 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

30多款 IntelliJ IDEA 必备插件：让你笑着提升开发效率![1]

例子：

key 有索引

EXPLAIN SELECT *FROM s WHERE key>'z'AND key LIKE '%a';

这里条件like '%a' 其实可以在索引里面，算出来哪些符合条件。。。。过滤出符合条件的，再回表。这样回表的数据可以减少很多。还有一个好处，没有索引下推，就需要把数据都回表查出来，，这些数据可能在不同的页当中，又会产生IO

条件下推，下推到下一个条件符不符合。

1.2 ICP的开启/关闭

默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制:index_condition_pushdown

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off ' ;
#打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on ' ;

当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为Using index condition 。

1.3 ICP使用案例

建表

CREATE TABLE`people` (
    `id`INTNOTNULL AUTO_INCREMENT,
    `zipcode`VARCHAR ( 20 ) COLLATE utf8_bin DEFAULTNULL,
    `firstname`varchar(20)COLLATE utf8_bin DEFAULTNULL,
    `lastname`varchar(20) COLLATE utf8_bin DEFAULTNULL,
    `address`varchar (50)COLLATE utf8_bin DEFAULTNULL,
    PRIMARY KEY ( `id`),
KEY`zip_last_first`( `zipcode` , `lastname`, `firstname`)
)ENGINE=InnoDB AUTO_INCREMENT=5DEFAULTCHARSET=utf8mb3 COLLATE=utf8_bin;

插入数据

INSERT INTO `people` VALUES
( '1', '000001','三','张','北京市'),
 ( '2', '000002 ','四','李','南京市'),
 ( '3', '000003', '五','王','上海市'),
 ( '4 ', '000001','六','赵','天津市');

为该表定义联合索引zip_last_first (zipcode，lastname，firstname)。如果我们知道了一个人的邮编，但是不确定这个人的姓氏，我们可以进行如下检索:

SELECT *FROM people
WHERE zipcode= '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

执行查看SQL的查询计划，Extra中显示了Using index condition，这表示使用了索引下推。另外，Usingwhere表示条件中包含需要过滤的非索引列的数据，即address LIKE ‘%北京市%’这个条件并不是索引列，需要在服务端过滤掉。

1.4 开启和关闭ICP的性能对比

创建存储过程，主要目的就是插入很多000001的数据，这样查询的时候为了在存储引擎层做过滤，减少IO，也为了减少缓冲池（缓存数据页，没有IO）的作用。

DELIMITER //
CREATEPROCEDURE insert_people( max_num INT )
BEGIN
DECLARE i INTDEFAULT0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERTINTo people ( zipcode, firstname , lastname , address ) VALUES ( '000001','六', '赵','天津市');

    UNTIL i = max_num
    ENDREPEAT;
    COMMIT;
END //
DELIMITER ;

调用存储过程

call insert_people(1000000);

首先打开profiling。

#查看
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
set profiling=1 ;

执行SQL语句，此时默认打开索引下推。

SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%张%';

再次执行sQL语句，不使用索引下推

SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

查看当前会话所产生的所有profiles

show profiles\G ;

结果如下。

多次测试效率对比来看，使用ICP优化的查询效率会好一些。这里建议多存储一些数据效果更明显。

1.5 使用前后的扫描过程

在不使用ICP索引扫描的过程：

storage层： 只将满足index key条件的索引记录对应的整行记录取出，返回给server层

server 层： 对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP扫描的过程：

storage层： 首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。

server 层： 对返回的数据，使用table filter条件做最后的过滤。

使用前后的成本差别使用前，存储层多返回了需要被index filter过滤掉的整行记录使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。 ICP的 加速效果 取决于在存储引擎内通过 ICP筛选掉的数据的比例。

1.6 ICP的使用条件

如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP
ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表
对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/o操作。
当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O。
- 索引覆盖不能使用，一个原因是，索引覆盖，不需要回表。。ICP作用是减小回表，ICP需要回表
相关子查询的条件不能使用ICP

参考资料

[1] https://juejin.cn/post/7439649869343522870: https://juejin.cn/post/7439649869343522870

芋道源码 —— 纯源码解析博客

MySQL性能飙升300%！揭秘让查询快到飞起的索引黑科技