MySQL索引下推讲解

一、什么是索引下推

• 索引条件下推（Index Condition Pushdown，ICP）是MySQL 5.6版本后引入的一项新特性。它通过减少回表的次数来提高数据库的查询效率。

• 在不使用ICP的情况下，当使用非主键索引（也称为普通索引或二级索引）进行查询时，存储引擎会通过索引检索到数据，然后将数据返回给MySQL服务器，服务器再判断数据是否符合查询条件。

• 而在使用ICP的情况下，如果查询语句中存在某些索引列的判断条件，MySQL服务器将这部分条件传递给存储引擎。存储引擎会根据这些条件判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时，存储引擎才会将数据检索出来并返回给MySQL服务器。

• 通过使用ICP，数据库可以在存储引擎层级对索引进行条件判断，减少不必要的回表操作，从而提高查询效率。这样可以大大减少从磁盘读取数据的次数，加快查询速度。

• 需要注意的是，ICP的效果取决于具体的查询语句和索引的使用情况。并不是所有的查询都适合使用ICP，有时可能会导致性能下降。因此，在使用ICP时需要进行合理的测试和评估，确保其能够带来性能的提升。

二、MySQL架构图

三、DEMO演示过程

MySQL版本如下：

SELECT VERSION();

下面是创建一个名为user的表，并插入一些测试数据的SQL语句：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL COMMENT '用户名',
  `age` int(8) NOT NULL COMMENT '年龄',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '是否删除，默认否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`, `age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO t_user(name, age, address) VALUES ('李四', 22, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李五', 22, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李六', 23, '中国');
INSERT INTO t_user(name, age, address) VALUES ('张三', 24, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李期', 24, '中国');

在Navicat上执行上述SQL如下：

插入测试数据：

接下来，我们创建一个联合索引来测试索引下推的场景：

CREATE INDEX idx_user_name_age ON user (name, age);

然后，我们可以使用EXPLAIN语句来演示索引下推的效果：

EXPLAIN SELECT * FROM t_user WHERE name LIKE '李%' AND age = 22;

执行以上语句后，输出的结果中，可以观察到Extra列中有一个"Using index"的标记，表示索引下推被成功应用。

总结下SQL执行过程：

1. 解析SQL语句，确定查询的表和条件。
2. 根据查询条件选择合适的索引，如果存在适用的联合索引，会优先选择联合索引。
3. 使用索引检索数据，将满足条件的索引行找出来。
4. 将索引行传递给MySQL服务器。
5. MySQL服务器根据剩余的条件进行判断，只有满足所有条件的索引行才会被返回。
6. 返回结果给客户端。

在使用索引下推的场景下，步骤4中的索引行会根据剩余的条件进行判断，只有满足条件的索引行才会被返回给MySQL服务器，从而减少了不必要的数据传输和判断操作，提高了查询效率。

💎索引下推💎 是 mysql 5.6 优化查询回表的功能，在5.6之前都不支持索引下推，我这里使用的是8.0

关闭索引下推：

SET SESSION optimizer_switch='index_condition_pushdown=off';

再次执行：EXPLAIN SELECT * FROM t_user WHERE name LIKE ‘李%’ AND age = 22;

• 具体来说，Using where表示查询执行时需要在查询引擎层级使用WHERE子句对结果进行过滤。这意味着在查询执行期间，MySQL将扫描表中的每一行，并根据WHERE条件进行过滤，以返回满足条件的结果集。

• 当Using where出现在EXPLAIN执行结果的Extra列时，表示查询过程中需要在查询引擎层级（Server层）执行额外的过滤操作，可能会导致查询性能下降。

• 相比之下，如果EXPLAIN执行结果中出现了Using index condition，那么意味着查询过程中已经使用了索引下推（index condition pushdown），即查询条件已经在存储引擎层级进行了过滤，不需要在查询引擎层级再进行过滤操作。