Mysql索引下推

一、简介

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

二、具体分析

假设我们有一个名为 user 的表，其中包含 id, username, zipcode和 birthdate 4 个字段，创建了联合索引(zipcode, birthdate)。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `zipcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username_birthdate` (`zipcode`,`birthdate`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;


# 查询 zipcode 为 431200 且生日在 3 月的用户
# birthdate 字段使用函数索引失效
SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;

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

Extra=Using where的意思：MySQL在进行过滤操作时需要应用一个WHERE条件。即使使用了索引，MySQL可能仍然需要进行额外的过滤操作来满足WHERE子句的条件。“Using where”只是说明存在一个WHERE过滤条件，而不是直接指示索引是否被使用。要判断索引是否被使用，可以查看执行计划中的“key”列，如果有显示索引名称，就说明使用了索引。
Extra=Using index condition表示MySQL在使用索引进行查询时，还应用了索引条件下推（Index Condition Pushdown，ICP）技术。ICP允许MySQL在读取索引的同时应用WHERE条件，从而减少需要从表中读取的行数。这可以提高查询效率，因为它减少了在索引之外需要访问的数据量。

基本结构图如下：

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。
索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。
我们这里结合索引下推原理再对上面提到的例子进行解释。
没有索引下推之前：

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

有了索引下推之后：

• 存储引擎层先根据 zipcode 索引字段找到所有 zipcode = ‘431200’ 的用户，然后直接判断MONTH(birthdate) = 3，筛选出符合条件的主键 ID；
• 二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据；
• 存储引擎层把符合条件的用户数据全部交给 Server 层。

可以看出，除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。
最后，总结一下索引下推应用范围：

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

三、回表时机

在MySQL数据库中，回表（Look Up）指的是在进行索引查询时，首先通过索引定位到对应页，然后再根据行的物理地址找到所需的数据行。换句话说，回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。

下面是我偷的一张网图：很好的反应了回表和索引下推执行的时机