什么是索引下推

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

回顾下mysql的架构分层，连接层、server层、引擎层：

分析索引下推

索引下推是 MySQL 5.6引入了一种优化技术，默认开启，查看开启情况

select @@optimizer_switch

使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引SELECT * FROM people WHERE zipcode=’200030′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

我们先来创建这个表

CREATE TABLE people (`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',zipcode VARCHAR(10),lastname VARCHAR(50),firstname VARCHAR(50),address VARCHAR(200),PRIMARY KEY (`id`)
);
CREATE INDEX idx_zc_people_names ON people(zipcode,lastname, firstname);

再初始化一些数据

-- 常见姓名 + 地址 + 邮编
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('100000', '王', '伟', '北京市海淀区中关村大街1号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('200030', '李', '娜', '上海市徐汇区淮海中路1234弄56号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('510000', '张', '强', '广州市天河区体育西路789号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('310012', '刘', '洋', '杭州市西湖区文三路456号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('518000', '陈', '静', '深圳市福田区深南大道101号');-- 复姓 + 详细地址
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('266071', '欧阳', '晨曦', '青岛市市南区香港中路10号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('215006', '司马', '浩然', '苏州市姑苏区干将东路288号');-- 少数民族姓名 + 地区特色地址
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('830000', '阿依', '古丽', '乌鲁木齐市天山区解放南路22号');
INSERT INTO people (zipcode, lastname, firstname, address) VALUES ('550001', '扎西', '多吉', '拉萨市城关区北京中路12号');

我们执行下这个语句，分析下开启索引下推和不开启的区别

EXPLAIN select zipcode from people where zipcode='100000' and lastname like '%刘%' and address like '%海淀区%';

不开启索引下推

如果没有使用索引下推技术，则MySQL会通过zipcode=’200030’从存储引擎中查询对应的数据，返回到MySQL服务层，然后MySQL服务层基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断数据是否符合条件。

关闭索引下推：SET optimizer_switch = 'index_condition_pushdown=off';

Extra的内容为：Using where ，代表着服务层的条件过滤 。

开启索引下推

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’来判断索引是否符合条件。

如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

开启索引下推：SET optimizer_switch = 'index_condition_pushdown=on';

Extra的内容为：

Using index condition ，使用了索引下推 。

Using where ，代表着服务层的条件过滤（address字段没有在组合索引里面，所以需要在进行一次条件过滤） 。

当一条SQL使用到索引下推时，explain的执行计划中的extra字段中内容为：Using index condition

索引下推的作用

有了索引下推的优化，在满足一定条件下，存储 引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。对于InnoDB的聚簇索引来说，完整的行记录已经加载到缓存区了，索引下推也就没什么意义了。

附录

mysql的架构图

找了一圈都没找到5.7的架构图，只在官网找到了8.0的https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

mysql5.7的innodb的架构图

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

1. 内存中结构

• 缓冲池（Buffer Pool）：缓存频繁访问的数据，减少磁盘I/O操作，提高查询性能。

• 变更缓冲区（Change Buffer）：缓存对二级索引页的更改，当这些页不在缓冲池中时，以避免耗时的I/O操作。

• 自适应哈希索引（Adaptive Hash Index）：为某些读操作提供快速的内存中查找机制，加速对频繁查询索引页的访问。

• 日志缓冲区（Log Buffer）：保存要写入事务日志的更改，通过先写入内存再定期刷新到磁盘上的重做日志，来提高性能。

2. 磁盘上结构

•系统表空间（System Tablespace）：存储变更缓冲区，InnoDB使用一个或多个数据文件来存储系统表空间。

• 各个表的独立表空间（File-per-table Tablespaces）：每个InnoDB表都可以有自己的表空间。

• 通用表空间（General Tablespaces）：可以容纳多个表的表空间。

• 撤销表空间（Undo Tablespaces）：存储撤销日志，这些日志记录了事务进行中必须保留的旧数据版本。

• 临时表空间（Temporary Tablespaces）：存储临时数据，如排序操作或哈希索引创建过程中的数据。

• 双写缓冲区（Doublewrite Buffer）：保护数据不因崩溃而损坏，通过先写入双写缓冲区再写入表空间文件。

• 重做日志（Redo Log）：记录数据变更，以便在系统崩溃后恢复数据。

• 撤销日志（Undo Logs）：记录了事务进行中必须保留的旧数据版本，以支持事务回滚和MVCC。

资料参考：

1.https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

2.MySQL 架构_mysql架构图-CSDN博客

原创不易，若有问题，还请批评指正，感谢！