MySQL索引下推

什么是索引下推

索引下推（Index Condition Pushdown，简称 ICP）是 MySQL5.6 引入的一种优化技术，通过将部分查询条件“下推”到索引扫描阶段，从而减少不必要的行访问和回表操作，提高查询性能。

如何学习索引下推

要知道索引下推首先要知道在没有使用索引下推的时候是如何进行查询的，然后再对比着进行学习索引下推是如何对查询进行优化的。

无索引下推查询数据流程

在没有使用索引下推的情况下，MySQL首先会找到符合条件的记录，然后回表读取所需要的字段，最后再应用其他过滤条件来判断这条记录是否复符合查询条件。

使用索引下推查询数据流程

使用了索引下推时，在索引扫描阶段，将部分查询条件直接应用于索引记录，从而减少回表操作。只有索引中满足所有条件的记录才会被回表读取其完整数据，最后再通过其他where条件进行过滤。

概念比较抽象，以下是一个具体的例子。

案例

准备工作

现有一张表，创建语句如下：

CREATE TABLE stu(
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10),
	`age` INT,
	`salary` INT,
	INDEX name_age_index (`name`, `age`)
);

表中记录如图所示：

现在执行以下查询语句进行查询：

EXPLAIN SELECT * FROM stu WHERE `name` LIKE '张%' AND `age` = 20 AND `salary` > 7000;

无索引下推查询过程

根据组合索引最左前缀原则，在查询到名字姓张的记录后就会回表操作，然后通过其他的where条件过滤数据。

使用索引下推查询过程

如图所示，在使用了索引下推后，在name_age_index索引树上，同时对name和age两个条件进行过滤，然后再回表通过其他where条件过滤数据。这样做的目的就是为了减少回表次数。

通过在查询语句前添加 EXPLAIN关键字可以查看是否使用了索引下推：

后面的 Using wgere是因为回表后还对非索引salary过滤。

控制是否使用索引下推

MySQL中默认开启了索引下推，如果想要开启或关闭可执行以下语句：

-- 关闭索引下推
SET optimizer_switch="index_condition_pushdown=off";
-- 开启索引下推
SET optimizer_switch="index_condition_pushdown=on";

索引下推的使用条件

1. 使用了索引列，且索引没有失效，如模糊查询时like '%张%'会导致索引失效，组合索引不遵守最左前缀原则会失效，索引列使用函数会失效；

2. 查询不涉及复杂的JOIN子查询；

3. 只能用于InnoDB和MyISAM存储引擎；

4. 只对非聚簇索引有效，即会进行回表操作的查询。

索引下推的好处

1. 减少回表操作：通过将更多的条件在索引扫描阶段应用，索引下推减少了不必要的回表操作，减少了 I/O 开销。

2. 特别适合组合索引：在使用复合索引（多个列的联合索引）时，索引下推的优化效果尤为明显。