持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第1天，点击查看活动详情
❝
大家好呀，我是小羊，如果大家喜欢我的文章的话，就关注我一起学习进步吧~
❞
今天讲讲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 字段命中了索引
不索引下推
执行流程如下：
- mysql 将根据最左匹配原则 拿到 > a2 的 所有索引 a3,
- mysql 根据索引上面的id 值 也就是 9， 10，回表查询得到2行数据
- mysql服务器 判断，第10 行数据不符合查询条件，所以最终返回第9 行数据。
可以看到，mysql 进行了 2次回表操作， 分别查询 9 10 行数据，再判断条件符合并返回第9行。
使用索引下推
执行流程如下：
- mysql 将根据最左匹配原则 拿到 > a2 的 所有索引 a3,
- 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"
总结：
- mysql 索引下推是一项提升查询速度的技术
- 在5.6之后版本引入
- 原理是在查询时将查询条件传给存储引擎，由存储引擎判断，并将符合的数据返回，减少回表次数。
好啦，今天的分享就到这里啦。
喜欢这篇文章就给点个赞吧。
