当前位置：首页 > article >正文

MySQL 索引下推

article 2025/3/26 3:56:02

概念

索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

例子

假设我们有一个名为 user 的表，创建了联合索引(name, age)。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20)  NOT NULL,
  `high` int NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
);

SELECT * FROM user WHERE name = '张三' AND age > 3;

没有索引下推之前，即使 name字段利用索引可以帮助我们快速定位到 name = ‘张三’ 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 age > 3。
有了索引下推之后，存储引擎会在使用name字段索引查找name = ‘张三’ 的用户时，同时判断 age > 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

SET optimizer_switch = 'index_condition_pushdown=off';
explain SELECT * FROM user WHERE name = '张三' AND age > 3;

在这里插入图片描述

SET optimizer_switch = 'index_condition_pushdown=on';
explain SELECT * FROM user WHERE name = 'k哥' AND age > 3;

在这里插入图片描述

原理

先看下面这张 MySQL 简要架构图。
在这里插入图片描述

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。
索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

我们这里结合索引下推原理再对上面提到的例子进行解释。

没有索引下推之前：

存储引擎层先根据 name 索引字段找到所有 name = ‘k哥’ 的用户的主键 ID，然后二次回表查询，获取完整的用户数据；
存储引擎层把所有 name = ‘k哥’ 的用户数据全部交给 Server 层，Server 层根据 age > 3 这一条件再进一步做筛选。

有了索引下推之后：

存储引擎层先根据 name 索引字段找到所有 name = ‘k哥’ 的用户，然后直接判断age > 3，筛选出符合条件的主键 ID；
二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据；
存储引擎层把符合条件的用户数据全部交给 Server 层。
可以看出，除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。

总结

索引下推应用范围

适用于 InnoDB 引擎和 MyISAM 引擎的查询。
适用于执行计划是 range, ref, eq_ref, ref_or_null 的范围查询。
对于 InnoDB 表，仅用于非聚簇索引。
- 索引下推的目标是减少全行读取次数，从而减少 I/O 操作。
- 对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。
- 在这种情况下使用索引下推不会减少 I/O。
子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的。
存储过程不能使用索引下推，因为存储引擎无法调用存储函数。

查看全文

http://www.kler.cn/a/597880.html