个人对于“索引下推”的理解

索引下推？
MySQL的架构层次

答：
MySQL架构可以简单分为Server层和存储引擎这两层。Server层上接和客户端的交互，下接与存储引擎的数据交互，存储引擎则负责数据的存储和读取。
举例：
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;
然后执行查询
SELECT * FROM user WHERE zipcode = ‘431200’ AND MONTH(birthdate) = 3;
注意这里用了日期函数，索引会失效。

没有索引下推：存储引擎现根据zipcode索引字段从那个联合索引底层的B+树的叶子节点中找到所有zipcode = ‘431200’ 的用户的主键 ID，然后再回表查询获得完整用户数据；接着存储引擎把所有zipcode = '431200’的用户数据全部交给Server层，Server层根据MONTH(birthdate) = 3这一条件进一步再做数据筛选。
有索引下推：存储引擎现根据zipcode索引字段从那个联合索引底层的B+树的叶子节点中找到所有zipcode = ‘431200’ 的用户数据，然后直接根据MONTH(birthdate) = 3这一条件做数据筛选，筛选出符合两个条件的主键ID，然后也要回表查询获得完整用户数据；接着存储引擎把符合两个条件的用户数据交给Server层。

索引下推的下推其实是把部分上层（Server层）负责的事情，交给了下层（存储引擎层）去处理，对于上面的例子来说，部分上层（Server层）负责的事情 就是根据第二个条件MONTH(birthdate) = 3筛选数据。更总结一点来说，索引下推是索引优化功能，允许存储引擎在索引遍历过程中，执行部分where的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。
个人理解：本来MONTH(birthdate) = 3是会失效的，但是沾了zipcode的光，先根据zipcode来从B+树的叶子节点的索引中找出zipcode符合条件的索引，有四个，然后重点来了！索引上(B+树的叶子节点)是有zipcode、birthdate和主键id的，然后再根据MONTH(birthdate) = 3从那四个索引里找出MONTH(birthdate) = 3的索引，那这样就只是一条索引符合条件了，然后再根据索引上存在着的主键id去回表即可。

可以看出索引下推的作用：减少回表次数（减少全行读取次数，减少I/O操作），还减少了存储引擎层和Server层的数据传输量。

索引下推适用于InnoDB引擎和MyISAM引擎的查询，对于InnoDB表，仅用于非聚簇索引。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用索引下推 不会减少 I/O。
实验代码：
CREATE TABLE suoYinXiaTui1 (
id int NOT NULL AUTO_INCREMENT,
username varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
zipcode varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
birthdate date NOT NULL,
PRIMARY KEY (id),
KEY idx_username_birthdate (zipcode,birthdate),
KEY idx_username_zipcode (username,zipcode)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

INSERT INTO suoYinXiaTui1 (username, zipcode, birthdate) VALUES
(‘张三’, ‘100001’, ‘1990-05-15’),
(‘李四’, ‘200002’, ‘1985-11-22’),
(‘王五’, ‘300003’, ‘1992-08-07’),
(‘赵六’, ‘400004’, ‘1988-03-30’),
(‘陈七’, ‘500005’, ‘1995-12-10’);

MONTH(birthdate) = 8 birthdate是一个可以在索引中进行过滤的条件，因为 birthdate 也在这个索引中

本来用了日期函数会让索引失效

索引的第一部分 zipcode 是完全匹配的，所以 MySQL 可以有效地使用索引来定位记录。

索引下推允许在索引中进行 MONTH(birthdate) 的计算，而不需要访问表中的实际行。

执行步骤：↓

1. MySQL 首先使用 zipcode = ‘300003’ 在索引中定位记录。

2. 然后，对于匹配 zipcode 的索引条目，MySQL 直接在索引中计算 MONTH(birthdate)。

3. 只有同时满足这两个条件的记录才会被进一步处理。

explain SELECT * FROM suoYinXiaTui1 WHERE zipcode = ‘300003’ AND MONTH(birthdate) = 8; # 使用索引下推

这个查询的条件完全匹配索引的所有列（username 和 zipcode）

explain SELECT * FROM suoYinXiaTui1 WHERE username = ‘王五’ AND zipcode = ‘300003’; # 使用普通索引

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

INSERT INTO suoYinXiaTui2 (username, zipcode, birthdate) VALUES
(‘张三’, ‘100001’, ‘1990-05-15’),
(‘李四’, ‘200002’, ‘1985-11-22’),
(‘王五’, ‘300003’, ‘1992-08-07’),
(‘赵六’, ‘400004’, ‘1988-03-30’),
(‘陈七’, ‘500005’, ‘1995-12-10’);

explain SELECT * FROM suoYinXiaTui2 WHERE zipcode = ‘300003’ AND MONTH(birthdate) = 8; # 用到了idx_username_birthdate索引
explain SELECT * FROM suoYinXiaTui2 WHERE username = ‘王五’ AND zipcode = ‘300003’; # 用到了idx_username_birthdate索引
explain SELECT * FROM suoYinXiaTui2 WHERE username = ‘王五’ OR zipcode = ‘300003’; # 全表扫描