2023-03-16 13:07 湖南科技大学后端发布于湖南

关注

MySQl 索引之道

前言

SQL 写不好加班少不了日常工作中SQL 是必不可少的一项技术但是很多人不会过多的去关注SQL问题一是数据量小二是没有意识到索引的重要性本文主要是整理 SQL失效场景如果里面的细节你都知道那你一定是学习能力比较好的人膜拜写完这篇文章我感觉自己之前知道的真的是 “目录” 没有明白其中的内容如果你能跟着节奏看完文章一定会有收获至少我写完感觉思维通透很多以后百分之九十的 SQl索引问题和面试这方面问题都能拿捏

文章概要

索引失效整理

基础数据准备

准备一个数据表作为数据演示这里面一共创建了三个索引

联合索引 sname, s_code, address
主键索引 id
普通索引 height

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `height` double NULL DEFAULT NULL,
  `classid` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `联合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '学生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2, '学生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3, '变成派大星', 3, '京东', 185, 3, '2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4, '学生4', 4, '联通', 190, 4, '2022-11-02 20:44:25');
复制代码

问题思考

上面的SQL 我们已经创建好基本的数据在验证之前先带着几个问题

我们先从上往下进行验证

最左匹配原则

写在前面：我很早之前就听说过数据库的最左匹配原则，当时是通过各大博客论坛了解的，但是这些博客的局限性在于它们对最左匹配原则的描述就像一些数学定义一样，往往都是列出123点，满足这123点就能匹配上索引，否则就不能。最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，我们上面建立了联合索引可以用来测试最左匹配原则 sname, s_code, address

请看下面SQL语句进行思考是否会走索引

-- 联合索引 sname,s_code,address

1、select create_time from student where sname = "变成派大星"  -- 会走索引吗？

2、select create_time from student where s_code = 1   -- 会走索引吗？

3、select create_time from student where address = "上海"  -- 会走索引吗？

4、select create_time from student where address = "上海" and s_code = 1 -- 会走索引吗？

5、select create_time from student where address = "上海" and sname = "变成派大星"  -- 会走索引吗？

6、select create_time from student where sname = "变成派大星" and address = "上海"  -- 会走索引吗？

7、select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海"  -- 会走索引吗？
复制代码

凭你的经验哪些会使用到索引呢？可以先思考一下在心中记下数字

走索引例子

EXPLAIN  select create_time from student where sname = "变成派大星"  -- 会走索引吗？
复制代码

未走索引例子

EXPLAIN select create_time from student where address = "上海" and s_code = 1 -- 会走索引吗？
复制代码

走的全表扫描 rows = 4

如果不知道EXPLAIN 是什么的或者看不懂分析出来的数据的话建议去看看另一篇文章分析命令EXPLAIN超详解

如果你内心的答案没有全部说对就接着往下看

最左匹配原则顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。

同时遇到范围查询(>、<、between、like)就会停止匹配

。

例如：s_code = 2 如果建立(sname, s_code)顺序的索引，是匹配不到(sname, s_code)索引的;

但是如果查询条件是sname = "变成派大星" and s_code = 2或者a=1(又或者是s_code = 2 and sname = "变成派大星" )就可以，因为优化器会自动调整sname, s_code的顺序。再比如sname = "变成派大星" and s_code > 1 and address = "上海" address是用不到索引的，因为s_code字段是一个范围查询，它之后的字段会停止匹配。

不带范围查询索引使用类型

带范围使用类型

根据上一篇文章的讲解可以明白 ref 和range的含义级别还是相差很多的

思考

为什么左链接一定要遵循最左缀原则呢？

验证

看过一个比较好玩的回答

你可以认为联合索引是闯关游戏的设计
例如你这个联合索引是state/city/zipCode
那么state就是第一关 city是第二关， zipCode就是第三关
你必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关

这样描述不算完全准确但是确实是这种思想

要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建文字比较抽象我们看一下

加入我们建立 A,B 联合索引他们在底层储存是什么样子呢？

橙色代表字段 A
浅绿色代表字段B

图解：

我们可以看出几个特点

A 是有顺序的 1，1，2，2，3，4
B 是没有顺序的 1，2，1，4，1，2 这个是散列的
如果A是等值的时候 B是有序的例如（1，1），（1，2）这里的B有序的（2，1）,(2,4) B 也是有序的

这里应该就能看出如果没有A的支持 B的索引是散列的不是连续的

再细致一点我们重新创建一个表

DROP TABLE IF EXISTS `leftaffix`;

CREATE TABLE `leftaffix`  (

  `a` int(11) NOT NULL AUTO_INCREMENT,

  `b` int(11) NULL DEFAULT NULL,

  `c` int(11) NULL DEFAULT NULL,

  `d` int(11) NULL DEFAULT NULL,

  `e` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  PRIMARY KEY (`a`) USING BTREE,

  INDEX `联合索引`(`b`, `c`, `d`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of leftaffix
-- ----------------------------
INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');

INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');

INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');

INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');

INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');

INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');

INSERT INTO `leftaffix` VALUES (7, 8, 8, 8, '7');
SET FOREIGN_KEY_CHECKS = 1;
复制代码

在创建索引树的时候会对数据进行排序根据最左缀原则会先通过 B 进行排序也就是如果出现值相同就根据 C 排序如果 C相同就根据D 排序排好顺序之后就是如下图：

索引的生成就会根据图二的顺序进行生成我们看一下生成后的树状数据是什么样子

解释一些这个树状图首先根据图二的排序我们知道顺序是 1111a 2222b 所以在第三层我们可以看到 1111a 在第一层 2222b在第二层因为 111 < 222 所以 111 进入第二层然后得出第一层

简化一下就是这个样子

但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以B=2这种查询条件没有办法利用索引。

看到这里还可以明白一个道理为什么我们建立索引的时候不推荐建立在经常改变的字段因为这样的话我们的索引结构就要跟着你的改变而改动所以很消耗性能

补充

评论区老哥的提示最左缀原则可以通过跳跃扫描的方式打破简单整理一下这方面的知识

这个是在 8.0 进行的优化

MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。比如我们使用的联合索引是 bcd 但是b中字段比较少我们在使用联合索引的时候没有使用 b 但是依然可以使用联合索引 MySQL联合索引有时候遵循最左前缀匹配原则，有时候不遵循。

小总结

前提如果创建 b,c,d 联合索引面

如果我where 后面的条件是c = 1 and d = 1为什么不能走索引呢如果没有b的话你查询的值相当于 *11 我们都知道*是所有的意思也就是我能匹配到所有的数据
如果我 where 后面是 b = 1 and d =1 为什么会走索引呢？你等于查询的数据是 1*1 我可以通过前面 1 进行索引匹配所以就可以走索引
最左缀匹配原则的最重要的就是第一个字段

我们接着看下一个失效场景

select *

思考

这里是我之前的一个思维误区 select * 不会导致索引失效之前测试发现失效是因为where 后面的查询范围过大导致索引失效并不是Select * 引起的但是为什么不推荐使用select *

解释

增加查询分析器解析成本。
增减字段容易与 resultMap 配置不一致。
无用字段增加网络消耗，尤其是 text 类型的字段。在阿里的开发手册中，大面的概括了上面几点。

在使用Select * 索引使用正常

虽然走了索引但是也不推荐这种写法为什么呢？

首先我们在上一个验证中创建了联合索引我们使用B=1 会走索引但是与直接查询索引字段不同使用SELECT*,获取了不需要的数据，则首先通过辅助索引过滤数据，然后再通过聚集索引获取所有的列，这就多了一次b+树查询，速度必然会慢很多，减少使用select * 就是降低回表带来的损耗。

也就是 Select * 在一些情况下是会走索引的如果不走索引就是 where 查询范围过大导致MySQL 最优选择全表扫描了并不是Select * 的问题

上图就是索引失效的情况

范围查找也不是一定会索引失效下面情况就会索引生效就是级别低生效的原因是因为缩小了范围

小总结

select * 会走索引
范围查找有概率索引失效但是在特定的情况下会生效范围小就会使用也可以理解为返回结果集小就会使用索引
mysql中连接查询的原理是先对驱动表进行查询操作，然后再用从驱动表得到的数据作为条件，逐条的到被驱动表进行查询。
每次驱动表加载一条数据到内存中，然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低，所以mysql中可以指定一个缓冲池的大小，缓冲池大的话可以同时加载多条驱动表的数据进行比较，放的数据条数越多性能io操作就越少，性能也就越好。所以，如果此时使用select * 放一些无用的列，只会白白的占用缓冲空间。浪费本可以提高性能的机会。
按照评论区老哥的说法 select * 不是造成索引失效的直接原因大部分原因是 where 后边条件的问题但是还是尽量少去使用select * 多少还是会有影响的

使用函数

使用在Select 后面使用函数可以使用索引但是下面这种做法就不能

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

这种写法我没使用过感觉情况比较少也比较容易注意到这种写法

计算操作

这个情况和上面一样之所以会导致索引失效是因为改变了索引原来的值在树中找不到对应的数据只能全表扫描

因为索引保存的是索引字段的原始值，而不是 b - 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

下面这种计算方式就会使用索引

Java比较熟悉的可能会有点疑问，这种对索引进行简单的表达式计算，在代码特殊处理下，应该是可以做到索引扫描的，比方将 b - 1 = 6 变成 b = 6 - 1。是的，是能够实现，但是 MySQL 还是偷了这个懒，没有实现。

小总结

总而言之言而总之只要是影响到索引列的值索引就是失效

Like %

这个真的是难受哦因为经常使用这个所以还是要小心点在看为什么失效之前我们先看一下 Like % 的解释

%百分号通配符: 表示任何字符出现任意次数(可以是0次).
_下划线通配符: 表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,

SELECT * FROM products WHERE products.prod_name like '1000';
复制代码

2.匹配包含"Li"的记录(包括记录"Li") :

SELECT* FROM products WHERE products.prod_name like '%Li%';
复制代码

3.匹配以"Li"结尾的记录(包括记录"Li",不包括记录"Li ",也就是Li后面有空格的记录,这里需要注意)

SELECT * FROM products WHERE products.prod_name like '%Li';
复制代码

在左不走在右走

右：虽然走但是索引级别比较低主要是模糊查询范围比较大所以索引级别就比较低

左：这个范围非常大所以没有使用索引的必要了这个可能不是很好优化还好不是一直拼接上面的

小总结

索引的时候和查询范围关系也很大范围过大造成索引没有意义从而失效的情况也不少

使用Or导致索引失效

这个原因就更简单了

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效举个例子，比如下面的查询语句，b 是主键，e 是普通列，从执行计划的结果看，是走了全表扫描。

优化

这个的优化方式就是在Or的时候两边都加上索引

就会使用索引避免全表扫描

in使用不当

首先使用In 不是一定会造成全表扫描的 IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描

in 在结果集大于30%的时候索引失效

not in 和 In的失效场景相同

order By

这一个主要是Mysql 自身优化的问题我们都知道OrderBy 是排序那就代表我需要对数据进行排序如果我走索引索引是排好序的但是我需要回表消耗时间另一种我直接全表扫描排序不用回表也就是

走索引 + 回表
不走索引直接全表扫描

Mysql 认为直接全表扫面的速度比回表的速度快所以就直接走索引了在Order By 的情况下走全表扫描反而是更好的选择

子查询会走索引吗

答案是会但是使用不好就不会

大总结

减少回表优化思路

这个对于SQL有研究的人可能是比较了解的但是对于工作时长不久的会比较陌生的词语但是这个是非常有意思且重要的

在这个索引问题上面还有一个细节的东西其中印象比较深刻的是回表会造成效率下降但是在我们日常工作中是比较常用单列索引联合索引对于新手来说不是很常用但是单列索引在一些情况下肯定不是最优解例如 like % 问题会造成索引问题近期了解到一个 ICP 知识我之前都没有关注过不知道大家对这个了解多少我这里就进行一些整理

首先我们ICP 全称是 Index Condition Pushdown 中文可以说成是索引下推主要的作用解决数据查询回表的问题但是前提是和联合索引进行使用才能发挥出来功效接下来不了解的小伙伴可以认真看一下这一点个人感觉还是比较有意思的东西