什么是索引下推？

今天讲讲mysql 索引下推

那么，什么是索引下推呢？

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

索引下推是一个mysql的优化技术，在5.6之后的版本引入，可以减少mysql回表的次数，提升查询速度，我们先看一下官方解释：

「当使用索引下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器」

看起来有点绕，别着急，让我来用一个例子给大家讲解一下，看完了，大家就理解啦。

首先，我们先做一些准备工作，在mysql中建一个数据表 t1：

/*
 Navicat Premium Data Transfer

 Source Server         : 本地-mysql
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 30/05/2022 11:28:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `a` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_abc`(`a`, `b`, `c`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (1, 'a1', 'b1', 'c1', 'd1');
INSERT INTO `t1` VALUES (2, 'a1', 'b1', 'c2', 'd2');
INSERT INTO `t1` VALUES (3, 'a1', 'b2', 'c1', 'd3');
INSERT INTO `t1` VALUES (4, 'a1', 'b2', 'c2', 'd4');
INSERT INTO `t1` VALUES (5, 'a2', 'b1', 'c1', 'd5');
INSERT INTO `t1` VALUES (6, 'a2', 'b1', 'c2', 'd6');
INSERT INTO `t1` VALUES (7, 'a2', 'b2', 'c1', 'd1');
INSERT INTO `t1` VALUES (8, 'a2', 'b2', 'c2', 'd1');
INSERT INTO `t1` VALUES (9, 'a3', 'b1', 'c1', 'd1');
INSERT INTO `t1` VALUES (10, 'a3', 'b1', 'c2', 'd2');

SET FOREIGN_KEY_CHECKS = 1;

表结构很简单，有id,a,b,c 三个字段，我使用了 a b c 三个字段建立了一个 联合索引   index_abc 并插入了一些数据。

首先，我们使用写一个查询语句：

SELECT * FROM `t1` where a = "a1" and b = "b1" and c = "c1"

可以很快查出数据：

然后我们使用 explain 看一下执行计划：

explain SELECT * FROM `t1` where a = "a1" and b = "b1" and c = "c1"

很显然，我们查询的时候使用到了刚刚创建的索引  index_abc。这个很简单。

但是，并不是所有情况下的查询条件都这么完美，我们这次使用到了  a b c 三个字段查询，有时候 可能只 用到了  a  或者  a b  又或者  a c  作为查询条件查询，这些条件能命中索引吗?

答案是会的，因为mysql 有一个最左匹配原则，只要使用到了 a 字段作为查询条件，那么就会走索引

比如我们看一下执行计划

explain SELECT * FROM `t1` where a > "a2" and c = "c1"

可以看到命中索引了，那么这和索引下推有什么关系吗？来，我们继续

在 extra 字段上面 有一个 「Using index condition」 这个就是 索引下推啦。

来解释一下使用索引下推和不使用索引下推的执行流程。因为最左匹配原则，只有 a 字段命中了索引

不使用索引下推

执行流程如下：

1. mysql 将根据最左匹配原则  拿到 > a2 的 所有索引  a3。
2. mysql 根据索引上面的id 值 也就是 9， 10，回表查询得到2行数据。

3. mysql服务器 判断，第10 行数据不符合查询条件，所以最终返回第9 行数据。

可以看到，mysql 进行了 2次回表操作， 分别查询 9 10 行数据，再判断条件符合并返回第9行。

使用索引下推

执行流程如下：

1. mysql 将根据最左匹配原则  拿到 > a2 的 所有索引  a3,
2. mysql  根据索引上面的id 值 也就是 9， 10 传给存储引擎，同时把 a > a2 和  c = c1 也同样传给mysql 存储引擎，mysql 存储引擎自行判断 10 行不符合条件，则返回 第9行数据。

可以看到，索引下推的优化就是，让存储引擎自己判断是否符合条件，减少回表操作，提升速度。有2张图可以比较清晰的说明这2种情况：

不使用索引下推：使用索引下推：

如果我们where条件中包含不是索引的字段,会使用索引下推吗？比如：

SELECT * FROM `t1` where a > "a2" and d = "d1"

答案也是可以的，原理同上

我们也可以通过命令来控制是否启用索引下推

#关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
#开启索引下推
set optimizer_switch='index_condition_pushdown=on';

关闭索引下推后

测试执行计划：

explain SELECT * FROM `t1` where a > "a2" and c = "c1"

总结：

1. mysql 索引下推是一项提升查询速度的技术。
2. 在5.6之后版本引入。
3. 原理是在查询时将查询条件传给存储引擎，由存储引擎判断，并将符合的数据返回，减少回表次数。

好啦，今天的分享就到这里啦。

喜欢这篇文章就给点个赞吧。